Install postgresql with brew and actually make it work

Want to install postgres and want it to, actually, work? Me too. I figured it out today. Only took an hour.

Here is a very terse and simple guide (as long as you know how to use a terminal) for installing and priming a postgresqlsetup on Mac OS X (Mountain Lion). Make sure you have brew installed on your machine. Start easy:

brew update
brew install postgresql

This will work for a few minutes, install postgresql and finish by leaving you with a load of instructions, which you can follow. Or not. I didn't feel it covered what I really needed for setting up a working database and getting inside of it.

You need to initialize the database with this command before you can really do anything else:

initdb /usr/local/var/postgres

Create our first empty database:

createdb database_name

Done. We're installed and initialized and our first empty database is waiting to be filled. From here you should be able to start your new postgresql server daemon and connect to it with any tool you want. I used the psql command line interface.

Here is where things got tricky. I was receiving the following error when attempting to connect to the database:

psql: could not connect to server: No such file or directory
Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

The cause of this was that my Mac OS X Mountain Lion already had a version of postgresql installed (comes with ML developer tools) and the standard path setup will use the built-in psql binary instead of your brew one. This binary was completely unable to find my brew installed postgresql server and therefore throwing a connection error.

A simple fix for this path problem is to update your local users' path in your ~/.profile settings. Edit that file and add the following line somewhere:

PATH=/usr/local/bin:$PATH

This command will set your path to be your current full path, with the brew binary directory of /user/local/bin first. This provides precedence to those binaries and thus all brew installations will be first. This should be done when using any brew apps, not just postgresql.

Some important quick reference commends include.

#start postgres
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

#stop postgres
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop