Installing PostgreSQL 9.0 on Mac OS X 10.6.8 via MacPorts

While installing PostgreSQL 9.0 on Mac OS X again, I had to figure out all these steps yet again. So I'm documenting this process for my own sake because I have been through this now twice on two computers recently, having to hunt down all of these commands each time. I'm hopeful that this will help others as well.

Use the MacPorts command port to install PostgreSQL 9.0, then create and own a data directory and a logs directory:

$ sudo port install postgresql90-server
$ sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb
$ sudo chown -R postgres:postgres /opt/local/var/db/postgresql90
$ sudo mkdir -p /opt/local/var/log/postgresql90
$ sudo chown -R postgres:postgres /opt/local/var/log/postgresql90

Now you need to initialize the database using the data directory that was created above:

$ sudo -u postgres /opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb

I prefer to change the postgres user's shell to bash:

$ sudo dscl . -create /Users/postgres UserShell /bin/bash

View the postgres user account just to make sure it all looks OK:

$ dscl . -read /Users/postgres
AppleMetaNodeLocation: /Local/Default
GeneratedUID: 5B38F583-CBBF-4082-A32D-C17947394A27
NFSHomeDirectory: /opt/local/var/db/postgresql90
Password: *
PrimaryGroupID: 501
RealName:
 PostgreSQL-90 Server
RecordName: postgres
RecordType: dsRecTypeStandard:Users
UniqueID: 502
UserShell: /bin/bash

Also check the postgres group:

$ dscl . -read /Groups/postgres
AppleMetaNodeLocation: /Local/Default
GeneratedUID: 715FEB22-D0F1-443F-BC93-55896210DB44
Password: *
PrimaryGroupID: 501
RealName: postgres
RecordName: postgres
RecordType: dsRecTypeStandard:Groups

Now edit the /opt/local/var/db/postgresql92/defaultdb/pg_hba.conf file to add the appropriate permissions:

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

This should allow you to connect easily using the psql utility.

I prefer to use the pg_ctl command to start and stop PostgreSQL. This is simply because I learned PostgreSQL on Linux and that's all there was. To prevent having to retype the full command every time I want to start or stop the database, create a start script and a stop script in the postgres user's home directory named pg_start and pg_stop. Below are the contents of the pg_start file. Make sure to create these files as the postgres user in the home directory:

$ sudo su - postgres 
$ vim ./pg_start 
#!/bin/sh 
/opt/local/lib/postgresql90/bin/pg_ctl -D /opt/local/var/db/postgresql90/defaultdb -l /opt/local/var/log/postgresql90/postgres.log start &

Below are the contents of the pg_stop file:

$ vim ./pg_stop 
#!/bin/sh 
/opt/local/lib/postgresql90/bin/pg_ctl -D /opt/local/var/db/postgresql90/defaultdb -l /opt/local/var/log/postgresql90/postgres.log stop

Don't forget to make them executable:

$ chmod +x ./pg_start 
$ chmod +x ./pg_stop

(There is a Mac OS X way of starting PostgreSQL using launchctl but I don't tend to use that because I'm used to the standard pg_ctl command.)

Now use the pg_start script to start up PostgreSQL. Execute it as the postgres user (sudo su - postgres) I tend to cat the log file just to make sure it's running correctly:

$ ./pg_start 
$ server starting

$ cat ../../log/postgresql90/postgres.log 
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

Looks good so we'll create my user:

$ /opt/local/lib/postgresql90/bin/createuser bsnyder
Shall the new role be a superuser? (y/n) y

Because I made my user a superuser, I can create my own db schema, so log out of the postgres user account and back to my own account first:

$ exit
$ /opt/local/lib/postgresql90/bin/createdb
CREATE DATABASE

The createdb command automatically uses my username as the schema name.

The only thing left to do before starting up the database is edit your ~/.profile or ~/.bash_profile to put the path to the PosgreSQL bin directory into the PATH:

export PATH=/opt/local/lib/postgresql90/bin:$PATH

Now just log into the PostgreSQL server using psql to make sure we're ready to roll:

bsnyder@skunk [~] $ . ~/.bash_profile
bsnyder@skunk [~] $ /opt/local/lib/postgresql90/bin/psql 
psql (9.0.4)
Type "help" for help.

bsnyder=# select version();
                                                                 version                                                                  
------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.0.4 on x86_64-apple-darwin10.8.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit
(1 row)

bsnyder=#

And we're good to go!

UPDATE: For those who want a nice seamless experience stopping/starting Postgres via a Mac OS X preference pane, check out Preference Pane for administering PostgreSQL on Mac OS X . It is configurable so there is no restriction on where PosgreSQL is installed.