Homebrew and PostgreSQL 9.4

December 20, 2014

Edit 2016/1/9 I have updated these instructions for upgrading from PostgreSQL 9.4 to 9.5.

As you may know, I am a big PostgreSQL user and fan. I also use Homebrew to manage 3rd party software packages on my Mac. PostgreSQL 9.4 was just released a couple days ago with some really cool features – a binary-format JSON datatype for speed and flexibility (indexes on JSON keys? Of course.), and some really good performance improvements. Read the release blog post and release notes for more information.

However, if you’ve used PostgreSQL before, you know that upgrading can be a little difficult. Here’s what you have to do to upgrade your Homebrew-installed PostgreSQL 9.3 to 9.4. Keep in mind, these steps are for a standard Homebrew installation – as long as you haven’t configured custom data directory paths, it should work.

1. Turn PostgreSQL off first, just in case:

   $ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
   

2. Update PostgreSQL itself:

   $ brew update && brew upgrade postgresql
   

3. Make a new, pristine 9.4 database:

   $ initdb /usr/local/var/postgres9.4 -E utf8
   

4. Migrate the data to the new 9.4 database:

   $ pg_upgrade \
     -d /usr/local/var/postgres \
     -D /usr/local/var/postgres9.4 \
     -b /usr/local/Cellar/postgresql/9.3.5_1/bin/ \
     -B /usr/local/Cellar/postgresql/9.4.0/bin/ \
     -v
   

5. Move 9.4 data directory back to where PostgreSQL expects it to be:

   $ mv /usr/local/var/postgres /usr/local/var/postgres9.3
   $ mv /usr/local/var/postgres9.4 /usr/local/var/postgres
   

6. Start PostgreSQL back up!

   $ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
   

Note: If you’re using the pg gem for Rails, you should recompile:

$ gem uninstall pg
$ gem install pg