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!
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.
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