Homebrew Postgresql on OS X though only tested myself on Lion (10.7.4)

config_postgres.sh

#!/usr/bin/env bash
# Expects one argument for the DATADIR
# A work in progress; only to be run on a fresh Homebrew installation.
# TODO Add in logic to check for bad postgresql init-ed directories and clean up

[ -z $1 ] && echo "Must supply DATADIR as first argument" && exit

function _install_postgresql() {
  brew install postgresql
}

function _init_postgresql() {
  SHMALL=$(sysctl -n kern.sysv.shmall)
  [ "$SHMALL" != "65536" ] && sudo sysctl -w kern.sysv.shmall=65536
  SHMMAX=$(sysctl -n kern.sysv.shmmax)
  [ "$SHMMAX" != "16777216" ] && sudo sysctl -w kern.sysv.shmmax=16777216
  initdb -D $1

  echo "Make sure to set kern.sysv.shmall (65536) and kern.sysv.shmmax (16777216) in /etc/sysctl.conf for permanent setting"
}

brew doctor && brew update
[ -z $? ] && _install_postgresql || echo "Problem with Homebrew doctor or update"
[ -z $? ] && _init_postgresql $1 || echo "Problem with postgresql Homebrew install"

POSTGRESQL_ON_OS_X.md

PostgreSQL on Lion

Much to my dismay, PostgreSQL on OS X is not as trouble-free to install and configure as my experiences with it on a variety of Linux distributions. This Gist documents how to overcome these issues using the Homebrew installation of postgresql.

This is an effort to document how to get setup to a coworker who was having troubles with Postgres on Lion recently.

Installation

This is usually the easy part:

brew install postgresql

This (of course) assumes that you already have Homebrew setup. If you receive errors during this process there are a few things you can do to troubleshoot such as:

• Run brew doctor to see what parts of your environment might be causing Homebrew to misbehave. It could be a user environment variable setting, to libraries not being able to be loaded any more. Read the warnings and notices and act on them accordingly.
• Run brew update
• Run brew remove postgresql to uninstall previous versions of postgresql that might be interfering with your new installation
• Run brew install postgresql (or brew upgrade postgresql if you didn't want to run the previous remove step).

I didn't have any issue in the installation phase so hopefully the above troubleshooting suggestions will be unnecessary for you too.

Initialization of PostgreSQL Database

So after you install PostgreSQL you need to initialize the data directory for the server to use. I typically choose the path /usr/local/var/postgres as this location os OS X. You can choose whatever location you prefer. Some Linux distributions use paths like /var/data/postgres and other /var variants. Whatever floats your boat, but note that I have hardly ever had a problem initializing data directories on Linux distributions with PostgreSQL's initdb.

So to initialize you can typically get away with just initdb -D $DATADIR where $DATADIR is set to your data directory path (e.g. /usr/local/var/postgres). Other options can be passed in to initdb as well. Please consult the initdb man page for more information about this.

Troubleshooting 'FATAL: could not create shared memory segment: Cannot allocate memory'

This is the error I got recently. The fix is to update a file called /etc/sysctl.conf (if it exists - don't worry if it doesn't) with the following contents:

kern.sysv.shmall=65536
kern.sysv.shmmax=16777216

Now you should be able to run initdb successfully.

Create databases

Now for each of your applications you will need to create a database. This is done by using the createdb DBNAME command. Again more options can be supplied to the created if you need to deviate from the defaults. If you are not sure then it is likely the defaults will be fine for your needs (at least for a development environment).

Launching Postgres server

OS X Launch Agents

Ok, so there are a few ways you can launch your postgres server. One would be to launch upon login via a user-level launch agent. If you want to do this follow the instructions given for the version of postgresql that Homebrew installs, but is typically something like this for a fresh postgresql installation with no previous launch agents installed:

cp /usr/local/Cellar/postgresql/$PGVERSION/org.postgresql.postgres.plist ~/Library/LaunchAgents
launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

If upgrading from a previous version you will do something like this:

launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
cp /usr/local/Cellar/postgresql/$PGVERSION/org.postgresql.postgres.plist ~/Library/LaunchAgents
launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

$PGVERSION is the version of the PostgreSQL Homebrew installation you want to use locally.

Foreman Procfile entry

Now this doesn't fit everyone's needs. I instead prefer to run my database server instance in my per-project Procfile. Procfile is a file that typically sits in the root of your project tree that a Ruby-based tools called foreman runs each entry in the file on start. This allows you to make sure all processes needed by your application are running after executing foreman start.

The entry would look something like this:

postgres: postgres -D /usr/local/var/postgres

1. Note: You will be able to spawn off only the entries in the Procfile on Heroku that you want, so having extra entries isn't a big problem!:)
2. Note: You will need to use the foreground command of postgres in the Procfile and not the backgrounding control script of pg_ctl as foreman requires foreground processes and reads its STDOUT for a consolidated log view of all supporting processes for your application

This strategy has been most helpful to me when some of my projects were still using PostgreSQL 8.4 and some were on the newer PostgreSQL 9.x series. I could pass in the appropriate DATADIR to the -D option in the Procfile for each project.

Manual starting

If you want to start on demand instead then I suggest using the Postgres control script:

pg_ctl -D $DATADIR -l logfile start

Feedback

Hope you found this useful. I will be adding a script to automate most of this for you on a clean Homebrew installation of Lion.

Alternatives

Heroku has recently released an app for OS X. It is called Postgres App. I tried this recently, but preferred my approach above. I should note that I don't need the GIS or extended features that are automatically enabled in this app version by Heroku, so you may prefer using this app instead.