Logo Devoh

Recompiling PostgreSQL in Snow Leopard

If you had previously installed PostgreSQL from source, you might want to recompile after upgrading to Mac OS X 10.6 (Snow Leopard) to gain the performance advantage of having 64-bit binaries. This process is straightforward with one exception: you must dump all of your data before upgrading, and then reload it after the 64-bit version has been installed.

Restoring the postgres User

At least for me, the Snow Leopard upgrade removed the postgres user from my system, but left the postgres group in place. See the PostgreSQL installation guide for Leopard for instructions on how to recreate this user.

Backup First

It's always a good idea to do regular backups, and doubly so before performing any sort of software upgrade. In this case however, the backup is a prerequisite to the upgrade process. Luckily, PostgreSQL's included tools make it easy.

pg_dumpall -U postgres > ~/32-bit-dump.sql

This will prompt for the postgres password for each table in your database, but in the end you'll have a single SQL script to restore all of your data.

After the backup is complete, you should shutdown the running daemon.

sudo launchctl unload /Library/LaunchDaemons/org.postgresql.postmaster.plist

Recompile the Binaries

The default compile target in Snow Leopard is x86_64, so recompiling is as simple as following the standard source compilation process. If you've kept your source tree from the original installation, you'll likely want to run make clean first to ensure all of the old 32-bit binaries are cleared out.

./configure --enable-thread-safety --with-bonjour
sudo make install

Rebuild the Data Files

Next, we'll have to remove and reinitialize the data files since the data format in PostgreSQL isn't architecture independent. This will wipe all of the data in your database, so please make sure you've followed the backup instructions above before continuing.

cd /usr/local/pgsql
sudo mv data /tmp
sudo -u postgres ./initdb -D /usr/local/pgsql/data -U postgres -W -A md5

This will prompt you for the new superuser password; just enter your old one to keep it the same. Now the old data can be loaded in again.

psql -U postgres -f ~/32-bit-dump.sql

Updating the Ruby Gem

If you use the postgres gem, you'll need to recompile it against the new shared libraries.

sudo env ARCHFLAGS="-arch x86_64" gem pristine postgres

Reverting to 32-bit Binaries

If you happened to recompile before backing up, you'll need to revert back to the 32-bit version before you'll be able to restart the database and make a dump. Just follow the steps above for recompiling, but add the appropriate flag to specify the 32-bit target architecture.

CFLAGS="-arch i386" ./configure --enable-thread-safety --with-bonjour

Now you can restart the daemon and follow the instructions from the beginning of this guide.