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.
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:
brew doctorto 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.
brew remove postgresqlto uninstall previous versions of postgresql that might be interfering with your new installation
brew install postgresql(or
brew upgrade postgresqlif 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
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:
Now you should be able to run
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
The entry would look something like this:
postgres: postgres -D /usr/local/var/postgres
- 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!:)
- Note: You will need to use the foreground command of
postgresin the Procfile and not the backgrounding control script of
pg_ctlas 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.
If you want to start on demand instead then I suggest using the Postgres control script:
pg_ctl -D $DATADIR -l logfile start
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.
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.