OS X Lion #2 : PostgreSQL as default database

PostgreSQL is a free object relational database management system which available in most Operating Systems. When Apple released 10.7, one of the new feature is that PostgreSQL already built in as client on OS X Lion, and the server version on OSX Lion Server and this is quite suprising thing. The question is how to use it, if we are the one of PostgreSQL developers and not running on OS X Lion Server.

This built in PostgreSQL installed client has 9.0.4 version and Apple leaves their own configuration to make this work. Remember that OS X is a unix-based OS and of course we can built the server version on OS X Lion according to the built in client version. This post is about how to make it work.

Building PostgreSQL Server on OS X Lion

• Install XCode 4.1, it's free from App Store.
• Get PostgreSQL Source Code (installed version is 9.0.4) 
• Open the Terminal.App and login as root (enter your password)

$ sudo su

• Copy and extract the downloaded source code to /usr/local/src/ (this is optional if you know what you do) and  create the directory if not exist :

# mkdir -p /usr/local/src/
# cp ~/Downloads/postgresql-9.0.4.tar.bz2 /usr/local/src/
# cd /usr/local/src/
# tar -jvxf postgresql-9.0.4.tar.bz2

• Now it's time to compile the source according to the built-in configuration :

# cd /usr/local/src/postgresql-9.0.4

# ./configure --mandir=/usr/share/man --infodir=/usr/share/info --prefix=/usr --sbindir=/usr/libexec --sysconfdir=/private/etc --localstatedir=/var/pgsql --htmldir=/Library/WebServer/Documents/postgresql --enable-thread-safety --enable-dtrace --with-tcl --with-perl --with-python --with-gssapi --with-krb5 --with-pam --with-ldap --with-bonjour --with-openssl --with-libxml --with-libxslt --with-system-tzdata=/usr/share/zoneinfo CFLAGS="-arch i386 x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations" LDFLAGS="-arch i386 x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations" LDFLAGS_EX="-mdynamic-no-pic"

• After the configuration is finish then you ready to build and install :

# make 
# make install

• Next is define the data directory for _postgres user (ex. /usr/local/pgsql/data)

# mkdir -p /usr/local/pgsql/data
# chown -R _postgres:_postgres /usr/local/pgsql

• Run database initialization :

# sudo su - _postgres -c initdb -D /usr/local/pgsql/data

• Now you can edit the configuration files on /usr/local/pgsql/data such as pg_hba.conf, postgresql.conf etc.

• Add the following lines to your /etc/profile or /etc/bashrc :

PGDATA=/usr/local/pgsql/data

export PGDATA

• Create a start up script on /Library/StartupItems :

# mkdir /Library/StartupItems/PostgreSQL
# cd /Library/StartupItems/PostgreSQL/

• Create this two files under /Library/StartupItems/PostgreSQL/ directory :

# touch PostgreSQL
# chmod +x PostgreSQL
# vim PostgreSQL

-------------- begin here --------------

#!/bin/sh


##
# Start up the PostgreSQL database server on Mac OS X
##


. /etc/rc.common


StartService () 
{ 
if [ "${POSTGRES:=-YES-}" = "-YES-" ]; then 
ConsoleMessage "Starting PostgreSQL database server" 
sudo su - _postgres -c 'pg_ctl start -D /usr/local/pgsql/data -l /usr/local/pgsql/data/postgresql.log -o -i' 
fi 
} 


StopService() 
{ 
ConsoleMessage "Stopping PostgreSQL database services" 
sudo su - _postgres -c 'pg_ctl stop -D /usr/local/pgsql/data'
x=`/bin/ps axc | /usr/bin/grep postgres` 
if /bin/test "$x" 
then 
set $x 
kill -9 $x 
fi
} 


RestartService () 
{ 
StopService 
StartService 
} 


RunService "$1"

-------------- end here --------------


# touch StartupParameters.plist

# vim StartupParameters.plist

-------------- begin here --------------

-------------- end here --------------

# exit

• Now the PostgreSQL service will run automatically on start up and you can test with :

$ sudo SystemStarter start "PostgreSQL"

You can download PgAdmin3 or Navicat Lite to manage the database :

Host : localhost

User : _postgres

Password : postgres

Good luck.