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 vexes me is PostgreSQL. It seems like every minor version upgrades fine, but then fails to start, and because I start the Postgres process on login and run it in the background as a launch agent, it’s never immediately obvious that the upgrade didn’t work or why it didn’t work. It’s also always been conveniently just long enough since the last time I upgraded Postgres 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