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
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: (homebrew.mxcl.postgresql) Exited with code: 1 9/21/13 10:36:37.864 AM com.apple.launchd.peruser.501: (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 (
-b), the new data and binaries (
-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:
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:
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:
delete_old_cluster.shThese two scripts were generated by the
pg_upgradecommand and should be in the directory where you first ran the command.
/usr/local/var/postgres9.2.4Since 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.