Getting fancy with PostgreSQL and Homebrew

Reading time ~2 minutes

In our previous post, we installed PostgreSQL via Homebrew and got our own little cluster up and running under our userid. That is probably good enough for 90% of the users out there who just want to play with or devel on PostgreSQL, but for those of us who need a little more flexibility in our installs, we’re going to take the next step.

First, if you have any data in your existing cluster that you want to preserve, let’s backup it up. We’re just going to use pg_dumpall here since it’s quick-n-dirty:

Now, we can stop our cluster:

And uninstall the current PostgreSQL:

And remove the remnants of our cluster:

Now we’ve got a clean slate. So, let’s tell Homebrew to stop using the default PostgreSQL and use Peter Eisentraut’s version (if you don’t know, Peter actually contributes to PostgreSQL):

As of this writing, Peter makes PostgreSQL 8.3 and above available. However, only PostgreSQL 9.2 and above are officially supported by the PostgreSQL community. So let’s install the supported versions:

As it says in the output, these are ‘keg-only’, which has the nice side effect that they are automatically installed in side-by-side directories /usr/local/opt/postgresql-9.1/ etc.

Peter configures PostgreSQL with just about all the options turned on:

and you get all the extensions that are in ‘contrib’ as well. However, if you find that you need to install another extension, I advocate using pex. You would install it like this:

Sadly, it will reinstall the default PostgreSQL brew recipe, but we’ll simply pretend that it’s not there going forward. Once installed, use pex like so:

As you can see, the ip4r extension was installed into my PostgreSQL 9.4 instance.

Now, you might be wondering how to deal with all these concurrent versions. If you type pg_dump which one willl you get? How can you force it to use a specific version? Right now, you won’t get any version, since it’s not in your path. You will need to specify exactly what you want:

doug@Douglass-MacBook-Pro ~ » /usr/local/opt/postgresql-9.6/bin/psql --version
psql (PostgreSQL) 9.6.1
doug@Douglass-MacBook-Pro ~ »

which is a huge pita. So, let’s make a little convenience function in our shell. Edit your .zshrc or your .bashrc (or whatever) and add a function like this:

and then simply call pg 9.5 to set PostgreSQL 9.5 as your ‘active’ instance:

oug@Douglass-MacBook-Pro ~ » pg 9.5
Stopping PostgreSQL 9.4... done!
Activating PostgreSQL 9.5... done!
Initializing PostgreSQL 9.5 cluster... done!
Starting PostgreSQL 9.5... done!
doug@Douglass-MacBook-Pro ~ »

Restore your data:

and there you be. Ain’t it beautiful? :)

When you cannot get there from here

Connecting to a PostgreSQL instance isn't hard generally, but sometimes you can run into issues. Sometimes a port isn't open on a firewal...… Continue reading

Installing pgBackRest on OSX

Published on June 14, 2017

Goodbye, Loui boy

Published on February 16, 2017