Saturday, July 30, 2011

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.

11 comments:

  1. Many thanks! i was in trouble to setup PostGreSQL in Lion client and was looking for a how to like this!
    I had to modify all occurences of : -arch i386 x86_64 by -arch x86_64 to have it working but else it was perfect. Best regards and have a good day!

    ReplyDelete
  2. If you're doing this against Lion client, don't forget to re-enable the _postgres account which initially has a shell of /bin/false which means that you can't fire up Postgres. I fixed mine by issuing the following command (don't use "append" which may just add /bin/zsh to the already existing /bin/false which will still not work)

    #dscl . -create /Users/_postgres UserShell /bin/zsh
    #dscl . -read /Users/_postgres
    [ ...]
    PrimaryGroupID: 216
    RealName:
    PostgreSQL Server
    PostgreSQL
    RecordName: _postgres
    RecordType: dsRecTypeStandard:Users
    UniqueID: 216
    UserShell: /bin/zsh

    Enjoy and Thanks!

    ReplyDelete
  3. Yes, the _postgres user must have a UserShell. Besides dscl command, we can use Workgroup Manager from Server Tools which is more simple.
    Many thanks for the comment.

    ReplyDelete
  4. Run database initialization :
    # sudo su - _postgres -c initdb -D /usr/local/pgsql/data

    The above command on my upgraded MacBook Air shows the following error:

    initdb: no data directory specified
    You must identify the directory where the data for this database system
    will reside. Do this with either the invocation option -D or the
    environment variable PGDATA.

    I had to to quote this way:
    sudo su - _postgres -c 'initdb -D /usr/local/pgsql/data'

    After this the initialization runs properly. Thanks for all!

    ReplyDelete
  5. add the following lines to /etc/profile or /etc/bashrc :


    PGDATA=/usr/local/pgsql/data
    export PGDATA

    ReplyDelete
  6. I'm running on lion, and couldn't get the startup script to work until I removed the "-" in "sudo su - _postgres" ....

    hope that helps somebody (and thanks to Haerul for the original post!!)

    ReplyDelete
  7. FYI, Im using /bin/bash as my shell on _postgres user :D

    ReplyDelete
  8. As far as changing the shell for the _postgres user is concerned: sudo doesn't require you to have a login shell to execute a command as another user.

    I had success running the following:

    sudo -u _postgres initdb -D /usr/local/pgsql/data

    And the sudo commands in the PostgreSQL SystemStarter script can be simply changed to:

    sudo -u _postgres pg_ctl start -D /usr/local/pgsql/data -l /usr/local/pgsql/data/postgresql.log -o -i

    and

    sudo -u _postgres pg_ctl stop -D /usr/local/pgsql/data

    respectively

    ReplyDelete
  9. Does this procedure overwrites the client in the system? If so, is there a way to install the server part only?

    ReplyDelete
  10. Why is the plist a png file and not text? That's just cruel.

    ReplyDelete
  11. Not so far I have found new cool tool to work with postgresql on mac os x - Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview
    You can install Valentina Studio (FREE) directly from Mac App Store: https://itunes.apple.com/us/app/valentina-studio/id604825918?ls=1&mt=12

    ReplyDelete