Upgrading from PostgreSQL 9.1.x to 9.2.x with Homebrew

Update: In later versions of Homebrew they added a command that automagically handles the upgrade for you!

brew postgresql-upgrade-database

I use Homebrew to manage my packages on macOS. It makes installing and keeping them up-to-date easy. But the one package that regularly gets me is PostgreSQL.

Every minor version seems to require this manual upgrade dance, and since I start the Postgres process automatically on login and run it in the background as a launch agent, nothing seems amiss with the upgrade until days later when I restart my computer, and at that point it’s been so long that I’ve completely forgotten that I upgraded Postgres.

It’s also always been conveniently just long enough since the last upgrade that I can’t quite remember how to do it, so I’m documenting it here for myself and anyone else.

Substitute x.x.x for the old version number and y.y.y for the new version number.

brew update
brew upgrade postgresql
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
mv /usr/local/var/postgres /usr/local/var/postgres_old
initdb /usr/local/var/postgres -E utf8
pg_upgrade \\
  -b /usr/local/Cellar/postgresql/x.x.x/bin \\
  -B /usr/local/Cellar/postgresql/y.y.y/bin \\
  -d /usr/local/var/postgres_old \\
  -D /usr/local/var/postgres
cp /usr/local/Cellar/postgresql/y.y.y/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

This will upgrade to the latest version of Postgres, stop the process, move your database to a new location, initialize a new database, port your data from the old database to the new database, create a new launch agent, and start the process.

After you’ve confirmed that Postgres has started, everything is working properly, and your old data has been properly converted you can remove the original database.

rm -rf /usr/local/var/postgres_old