Upgrading Postgres 9.3.4 to 9.4.1 Using Homebrew
This morning I didn't have a lot going on, and I decided to upgrade my laptop from Posgres 9.3.4 to 9.4.1 as there are a few little things in 9.4 that are nice, and I've got 9.4 on my work laptop, and I figured this would be an easy upgrade - like super easy... I was mistaken.
The rules about automatic upgrades for Postgres is a bug release version change. I thought it was a minor release. So I was expecting to simply shut down the server, upgrade the packages with Homebrew, and then start it back up. The code would detect that it was the next minor version, and automatically update the data. Sadly, that's not the case. It's a big upgrade, and that means that I might as well do a complete dump/load.
Sadly, I didn't do a dump, so I'd have to live with an older version. Not a tragedy, but annoying when I'm in the middle of the upgrade process only to learn that it's not going to work. So it goes...
So here's what I had to do - in the right order to get things working. Not bad, but it's basically the instructions for a dump/load, so I'll assume we know this going in.
First, create a complete dump of the database. Assuming that all these things are installed on Mac OS X, and using Homebrew, the paths are not important - they are all fixed with Homebrew, anyway.
$ pg_dumpall > dump_file
Next, shut down the running server, update Homebrew, and then upgrade postgres within Homebrew. Just to be safe, let's re-link the launchctl file because in this case, it has changed, and better safe than sorry.
$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist $ brew update $ brew upgrade postgresql $ ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
At this point I need to move the old database data to the side, and then initialize the database with the new codebase. Once that's done, we can then restart it with the re-linked launchctl file.
$ cd /usr/local/var $ mv postgres postgres.old $ initdb -D /usr/local/var/postgres $ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Finally, we need to load up the entire database from the dump file we made in the first step.
$ psql -d postgres -f /path/to/dump_file
Check and make sure that everything looks OK and then you can easily remove the old database directory:
$ rm -rf /usr/local/var/postgres.old
That's it.