Upgrading PostgreSQL 9.2 to 9.3 with Homebrew

I have been happily running PostgreSQL 9.2, installed via homebrew. This morning a brew upgrade duly installed PostgreSQL 9.3, which uses an incompatible data format. Here’s how I migrated my databases from 9.2 to 9.3.

Note: These instructions assume you have already installed PostgreSQL 9.3 using brew upgrade postgresql. Don’t uninstall PostgreSQL 9.2.4 yet: it’s required for the upgrade process!

Identifying the problem

I didn’t notice the problem at first, since my old version of PostgreSQL was still running. Everything continued operating as normal. But when I tried restarting PostgreSQL, the new 9.3 version never started up, and I found these errors in /usr/local/var/postgres/server.log:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 9.3.0.

I also found these messages in the Console app, as launchd stubbornly tried to keep restarting the server:

9/21/13 10:36:37.864 AM com.apple.launchd.peruser.501[239]: (homebrew.mxcl.postgresql[96943]) Exited with code: 1
9/21/13 10:36:37.864 AM com.apple.launchd.peruser.501[239]: (homebrew.mxcl.postgresql) Throttling respawn: Will start in 10 seconds

Solution? Turn off launchd, migrate the data directory to the new 9.3 format, and start it back up.

1. Shut down PostgreSQL

Since my PostgreSQL was installed via homebrew, it was being managed by Apple’s launchd system. The first step is to tell launchd to stop the old version of PostgreSQL:

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

2. Create a new PostgreSQL 9.3 data directory

The upgrade process involves migrating data from an old data directory to a new one, so I had to create the new data directory first. Since homebrew had already upgraded and linked the new version of the PostgreSQL 9.3 binaries, the initdb in this command is creating a 9.3 data directory.

initdb /usr/local/var/postgres9.3 -E utf8

3. Run the pg_upgrade command

Luckily PostgreSQL ships with a command to migrate to the new data directory format, as explained here. Using pg_upgrade is straightforward: specify the old data and binaries (-d and -b), the new data and binaries (-D and -B), and it does its thing.

pg_upgrade \
-d /usr/local/var/postgres \
-D /usr/local/var/postgres9.3 \
-b /usr/local/Cellar/postgresql/9.2.4/bin/ \
-B /usr/local/Cellar/postgresql/9.3.0/bin/ \
-v

Upon completion, you should see the following message:

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh

You don’t need to run either of these scripts. The first script just does a progressive vacuumdb on the new database (which you can run manually if needed).

The second script just deletes the old 9.2.x PGDATA directory, which I decided to do manually (see step 5 below).

4. Change kernel settings if necessary

My upgrade failed at first, due to PostgreSQL requesting more memory than the OS X kernel allowed. In the pg_upgrade_server.log file in the current directory, I found this error message:

waiting for server to start....FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=50432001, size=3809280, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space, or exceeded your kernel's SHMALL parameter.

The solution was to change my kernel parameters, as explained in this mailing list post:

sudo sysctl -w kern.sysv.shmall=65536
sudo sysctl -w kern.sysv.shmmax=16777216

I also ran sudo vi /etc/sysctl.conf and entered the same values:

kern.sysv.shmall=65536
kern.sysv.shmmax=16777216

Then I re-ran the pg_upgrade command in step 3, which completed without errors. Success!

5. Move the new data directory into place

After the upgrade completed, I needed to move the new 9.3 data directory into place at /usr/local/var/postgres, where the server will find it when starting up. I moved the existing 9.2 data directory out of the way, renaming it to postgres9.2.4 just in case I need to come back to it later.

cd /usr/local/var
mv postgres postgres9.2.4
mv postgres9.3 postgres

6. Start the new version of PostgreSQL

I started PostgreSQL up with launchd in the usual way (this also ensures PostgreSQL will start automatically on reboot):

launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

And then verified that the database server was running and was indeed upgraded to 9.3:

psql postgres -c "select version()"

Lastly, I double-checked that all my databases were still intact:

psql -l

7. Cleanup

If you want, you can issue the following command to generate the optimizer statistics for the new databases:

vacuumdb --all --analyze-only

You may want to remove the files and directories left over from the upgrade process:

• analyze_new_cluster.sh and delete_old_cluster.sh These two scripts were generated by the pg_upgrade command and should be in the directory where you first ran the command.
• /usr/local/var/postgres9.2.4 Since we verified that all is well with the upgrade, we can remove our old data.

Uninstall PostgreSQL 9.2.4:

brew cleanup postgresql

8. If using Rails, rebuild the pg gem

Finally, a new version of PostgreSQL means new versions of the native libraries that are used by the Ruby pg gem. To get my Rails apps to work, I needed to force the pg gem to recompile against the new native libraries:

gem uninstall pg
# Choose to remove "all versions"
# Re-install to force a recompile
gem install pg

Whew! Now my Rails apps all work with PostgreSQL 9.3. All done.