Opinionated OS X

Why opinionated?

There are many different ways to install and use PostgreSQL on OS X. That variety is good in that the different installations have advantages and disadvantages, but it makes starting with PostgreSQL on OS X much more complex than it needs to be, particularly for beginners who just want to get a database up and running. The different installations are subtly incompatible - the clients from one won't connect to the server from another without additional configuration - and which server will "win" and start up correctly on a reboot isn't terribly well defined. If the user installs two conflicting installations that can lead to PostgreSQL mysteriously breaking, or appearing to lose data after a reboot.

This document intends to make that process simpler, by offering a single good solution rather than many alternatives.

Who is this for?

• You want to learn SQL using PostgreSQL
• You're developing web applications that use PostgreSQL as a database
• You're developing desktop applications intended to connect to PostgreSQL
• You want a solid database running on your laptop for data analysis
• You want to use PostGIS for Geographical Information work

If any of these describe you, and you use OS X, keep reading.

Who is this not for?

• You're deploying a production (as opposed to development or QA) database
• You need your database to be available when you're not logged in
• You want to modify core PostgreSQL code
• You're developing extensions to PostgreSQL
• You want to use embedded languages other than PL/pgSQL or PL/v8 Javascript
• You want to use third party extensions

If any of these describe you, this isn't what you want. (You might find that running something like Ubuntu, possibly in a virtual machine such as VMWare Fusion or VirtualBox more convenient.)

Getting started

If you've installed PostgreSQL previously, uninstall it. If you installed via homebrew, run brew remove postgresql. If via macports, run sudo port uninstall postgres. If you used the EnterpriseDB installer, find and run uninstall-postgres.app.

Make sure that there are no existing installations of PostgreSQL running. Open a terminal window and run:

 ps auxw | grep postgres

The result should look something like this:

 satsuke:~$ ps auxw | grep postgres
 dyld: DYLD_ environment variables being ignored because main executable (/bin/ps) is setuid or setgid
 steve          16241   0.0  0.0  2432768    624 s000  R+    7:04PM   0:00.00 grep postgres

If there are more results than that, you need to find the existing PostgreSQL installation and make sure it is shut down.

If you've ever installed PostgreSQL before you might want to reboot and check again, to make sure it's not starting at boot. If something is starting at boot, it's probably located in a LaunchAgents folder you can see with the `ps auxw | grep postgres`. Navigate to that folder and manually delete the Postgres apps in it. Restart your computer to kill other processes.

Installing Postgres.app

Download Postgres.app from http://postgresapp.com/ , double-click on it to unzip it, then drag Postgres.app to your Applications folder.

To start the PostgreSQL server run Postgres.app from your Applications folder. Not much will seem to happen, but a small elephant will appear in your menu bar. The elephant opens a menu that lets you shut down the server, or configure it to start automatically when you log in.

Use the right clients

Next you need to configure your PATH such that you use the Postgres.app provided client tools (such as psql or createdb) rather than the ones that are installed as part of OS X. Open or create the file .profile in your home directory with your favorite text editor. To the end of the file add this:

export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"

Open a new terminal and check that your path is correct by running which psql. It should show something like /Applications/Postgres.app/Contents/Versions/latest/bin/psql.

Create and access a database

To create a new database called mydb, open a terminal and run

createdb mydb

To log in to the database using psql, open a terminal and run

psql mydb

From there you can follow the postgresql tutorial.

Accessing the database from other applications

There are some connection string examples in the Installing Postgres.app and How to connect sections but for applications that are running under your user id all you need to enter is the hostname ("localhost"), the username (the username you're logged in as) and your database name.

If you need to connect from applications not running as you, you'll need to set up authentication as discussed in the PostgreSQL manual.

Developing with Postgres.app

To build database access libraries (such as DBD::Pg for perl) you may need to know the directories where include files and shared libraries are installed.

Include files: /Applications/Postgres.app/Contents/MacOS/include

Shared libraries: /Applications/Postgres.app/Contents/MacOS/lib

If you're building C or C++ code a more portable way to configure include and library directories is using pg_config - "pg_config --includedir" and "pg_config --libdir".

Maintenance

Backup and restore

To back up a single database mydb to a file run this:

pg_dump --format=custom --file=mydb.dump mydb

To restore the dump, destroying any existing content of the mydb database run this:

pg_restore --dbname=mydb --clean mydb.dump

For more details about backups, see the PostgreSQL manual.

(If you're repeatedly restoring the same data as part of your development or QA environment you might want to look at the TEMPLATE option to the CREATE DATABASE command as an alternative.)

Version upgrades

TODO