Installation of Postgresql on MacOS X using Macports

Installing and configuring postgresql using Macports is a nightmare.  Documentation is very difficult to find.  Trawling the blogosphere, I have assembled the following procedure that I have had to follow to make it all happen, as it should.

To install postgresql84 on Snow Leopard (10.6) using Macports, execute the following commands:

sudo port install postgresql84-server
sudo mkdir -p /opt/local/var/db/postgresql84/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql84/defaultdb
sudo -u postgres /opt/local/lib/postgresql84/bin/initdb -D /opt/local/var/db/postgresql84/defaultdb

Now to make sure that the postgres user account can be executed using the su command, we need to give postgres a shell:

sudo dscl . -create /Users/postgres UserShell /bin/sh

Then merge the Sys V Shared Memory Kernel parameters from the /etc/sysctl.conf.pg file with your existing /etc/sysctl.conf file (if you already have one):

sudo sh -c "cat /etc/sysctl.conf.pg >>/etc/sysctl.conf"

You can reboot at this point so that the new Sys V shared memory configuration in /etc/sysctl.conf is loaded by the kernel.  Alternately, being a UNIX you can execute the following command to get you going right away:

sudo sysctl -w `cat /etc/sysctl.conf.pg`

Next, we need to create the logfile directory and make it writable by the postgres user:

sudo mkdir -p /opt/local/var/log/postgresql84
sudo chown postgres:postgres /opt/local/var/log/postgresql84

Next, add to the /opt/local/lib/postgresql84/bin directory to your path variable in either /etc/profile or ~/.profile and you should be set

export PATH=/opt/local/bin:/opt/local/sbin:/opt/local/lib/postgresql84/bin:$PATH

Now create yourself a postgres database user account

$ createuser `whoami`
Shall the new role be a superuser? (y/n) y

To make the database server accessible via a TCP socket, edit the /opt/local/var/db/postgresql84/defaultdb/pg_hba.conf file and add the following two lines:

host    all         all         127.0.0.1/32          md5
host    all         all         0.0.0.0/0                md5

Edit the /opt/local/var/db/postgresql84/defultdb/postgresql.conf file and add the following line:

listen_addresses = '*'

Now, we should be right to start the server.  We use MacOS’ launchctl command:

sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql84-server.plist

Now you can connect to the database server with:

psql template1
psql (8.4.7)
Type "help" for help.

template1=#

Hopefully this has been useful to someone else, as I have spent over 2 hours *&^%*&% around with this to get it working.

 

Damien.