Brilliant Corners

Since I’ve read so much about PostgreSQL being the other great open-source database server, I thought I’d try it out on a new Rails application I’m starting up. Right now, MySQL still seems to be the darling of the Rails development folks, so it’s a little more difficult to find information on installing and getting PostgreSQL working with Rails. So, here are some pointers I’ve collected to get PostgreSQL up and running.

Please note: These instructions were used to build PostgreSQL on my MacBook Pro (Core 2 Duo), so some of the below may not apply to anyone else. I am not responsible for any mess you get yourself into.

Getting PostgreSQL

First, of course, you’ll need to download PostgreSQL, which you can do here: http://www.postgresql.org/ftp/source/ (Follow the link with the highest version number, and choose the postgresql-8.X.X.tar.gz file).

Building PostgreSQL

Next, you need to build the source. I have a ‘tmp’ directory just off my personal directory for just this sort of thing.

 billturner:~/tmp bill$ tar zxvf postgresql-8.X.X.tar.gz
 billturner:~/tmp/ bill$ cd postgresql-8.X.X/

If you’ve already run through Dan Benjamin’s wonderful instructions on getting Ruby, Rails, MySQL, etc. running on a Mac, then you’re already set with the necessary Readline libraries. If not, jump over to that tutorial and see how to get the readline libraries installed.

I’ll assume that you have readline installed in /usr/local like Dan suggests. If not, change the file paths below on the configure command.

billturner:~/tmp/postgresql-8.X.X bill$ ./configure \
> --with-includes=/usr/local/include \
> --with-libraries=/usr/local/lib
billturner:~/tmp/postgresql-8.X.X bill$ make
billturner:~/tmp/postgresql-8.X.X bill$ sudo make install

Building the Ruby gem for PostgreSQL

Here’s where I initially ran into problems. The postgres ruby gem just wouldn’t compile or install. After some searching online, I found a solution (this comment) that worked.

First, let’s attempt to install the gem:

billturner:~ bill$ sudo gem install postgres

And if you have a Mac similar to mine, you may get an error like the following:

Building native extensions.  This could take a while...
postgres.c: In function 'pgconn_s_escape_bytea':
postgres.c:222: warning: pointer targets in passing argument 1 of 'PQescapeBytea' differ in signedness

It will actually say “Successfully installed postgres-0.X.X” at the end, but if you get that error, it wasn’t actually installed.

If you do not get this error, then hooray for you and you can skip the following instructions. If you do get that error, you’ll need to do some fixin’.

Fixing the Ruby gem for PostgreSQL

You’ll need to get into the directory where “gem” put the source.

billturner:~ bill$ cd /usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X

Now, run the configuration again, since the Makefile wasn’t created when running “gem install postgres”.

billturner:/usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X bill$ sudo ruby extconf.rb \
> --with-pgsql-include-dir=/usr/local/pgsql/include \
> --with-pgsql-lib-dir=/usr/local/pgsql/lib

Then you need to open up the file “postgres.c” in a text editor (most likely with the sudo command):

billturner:/usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X bill$ sudo mate postgres.c

Jump to line 222, and change it from this:

to = (char *)PQescapeBytea(from, from_len, &to_len);

to this:

to = (char *)PQescapeBytea((unsigned char *)from, from_len, &to_len);

Save that and we can now compile and install.

billturner:/usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X bill$ sudo make
billturner:/usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X bill$ sudo make install

Setting up PostgreSQL

If you take a look through this helpful tutorial on the Apple.com website, about a page and a half down, you’ll see a section with a screenshot explaining how to create a user for the PostgreSQL server. It mentions in that article that the user needs to be an “administrative user,” but I haven’t found that to be the case. I created a basic “postgres” user, and that’s it.

Now that you’ve created the system user for the database server (we’ll assume it’s “postgres” from here on out), let’s get the data directory set up for the initial data:

billturner:~ bill$ sudo mkdir /usr/local/pgsql/data
billturner:~ bill$ sudo chown postgres /usr/local/pgsql/data

Now, “su” into the postgres account to set things up the rest of the way with this initializing command:

billturner:~ bill$ su -l postgres
billturner:~ postgres$ /usr/local/pgsql/bin/initdb -E UTF-8 -D /usr/local/pgsql/data

Here, there’s a possibility you may get another error at this step. The first time I ran the initdb command I left off the “-E UTF-8” option and it ran without a problem. Since, I’ve decided to specify UTF-8 encoding. When I added the “-E UTF-8” option, I received an error similar to this:

FATAL: could not create shared memory segment: Cannot allocate memory
DETAIL: Failed system call was shmget(key=1, size=1081344, 03600).
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1081344 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter
(currently 10).

If you get this error, you’ll have to make a change to the system that requires a reboot. Thanks to some Google searches you’ll need to edit, the /etc/rc file (you’ll need to use “sudo”). Find a line that looks like this:

sysctl -w kern.sysv.shmmax=4194304 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024

and change the value of “kern.sysv.shmmax” to 167772160 and “kern.sysv.shmall” to 65536. The line should now look like this:

sysctl -w kern.sysv.shmmax=167772160 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=65536

Save the file and reboot. Once your Mac comes back, you’ll need to run the “su -l postgres” and the “initdb” commands above this error fix. The initdb command should complete without errors this time.

If you don’t feel comfortable making this change, just omit the “-E UTF-8” from the last command above, and you shouldn’t have any problems.

Starting PostgreSQL

It appears that there are loads of different ways to start the PostgreSQL daemon. Here’s the one I went with:

billturner:~ bill$ su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start'

Wallah! PostgreSQL is now running. However, once you shut down or reboot your Mac, it won’t start up on its own. Yet.

Launching PostgreSQL on demand

I’m including a section below on how to have PostgreSQL start up automatically when rebooting, but until I get a little further into developing with this database, I’ll just be able to run it when I want it. So, in order to make starting and stopping the server easier, I added the following two lines to the bottom of my ~/.bash_login file:

alias pgstart="su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start'"
alias pgstop="su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop'"

Note that in each case, you’ll be prompted for the “postgres” user’s password. You can avoid something like this if you set up PostgreSQL to automatically launch at boot.

Launching PostgreSQL automatically at reboot

Here I’ve ran into so many, many different approaches, I’m not sure which one to highlight, so I’ll just tell you what I’ve done. Since MySQL can now install a StartupItem (that is handled by the launchd service on the Mac), I went to find an appropriate one for PostgreSQL.

As a base, I downloaded the StartupItem installer package from Marc Liyanage’s excellent resource. Here’s a direct link to the package (that may or may not work in the future). From my searching, this seemed to be the simplest, and closest approach to what I needed.

Once you install the StartupItem, it won’t work as installed. It puts the right files in the right place, but the command directives point to the wrong locations. So, with your favorite text editor open the StartupItem command file:

 billturner:~ bill$ sudo vi /Library/StartupItems/PostgreSQL/PostgreSQL

Find the line that starts the server(in the StartService() section), which looks like this:

su - postgres -c '/usr/local/bin/pg_ctl start -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile -o -i'

and change it to this:

su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start'

Next, find the line that stops the server (in the StopService() section), which looks like this:

/usr/local/bin/pg_ctl stop -D /usr/local/pgsql/data

and change it to this:

su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop'

If you’d like to try another approach, this search should be a decent starting point.

Some PostgreSQL applications

• pgAdminIII - a nice, multi-platform application for PostgreSQL management
• phpPgAdmin - a somewhat similar web application to the popular phpMyAdmin tool for MySQL databases.
• PostgreSQL Tools for MacOS X - several GUI tools (haven’t tried these yet)

Some helpful links

• http://developer.apple.com/internet/opensource/postgres.html
• http://www.oreillynet.com/pub/a/mac/2002/06/07/postgresql.html
• http://blog.invisible.ch/2006/01/10/start-postgres-with-launchd-on-os-x/
• http://www.robbyonrails.com/articles/2006/05/29/install-ruby-rails-and-postgresql-on-osx
• http://www.entropy.ch/software/macosx/postgresql/
• http://www.4ied.net/articles/show/23
• http://www.postgresql.org/docs/8.2/static/index.html

Conclusion

Whew! Okay, that’s a lot to take in, as I know it was a lot to write out. Hopefully these instructions have helped someone, that like me, was stuck at various steps in getting PostgreSQL installed on my Mac.

If you have some pointers or tips for me, I’d be glad to see them.