Installing PostgreSQL on Lion

Lion comes with a _postgres system account and PostgreSQL 9.0.4 client tools already installed. Lion Server includes PostgreSQL server, which replaces the MySQL server distributed with earlier versions of OS X. If you want to add PostgreSQL server to a regular Lion installation the easiest way to do this is probably using the EnterpriseDB installer. However, I was inspired by a couple of posts at devoh.com to compile and install it manually:

curl -O http://ftp.postgresql.org/pub/source/v9.0.4/postgresql-9.0.4.tar.bz2
tar xzvf postgresql-9.0.4.tar.bz2
cd postgresql-9.0.4
./configure --enable-thread-safety --with-bonjour
make
sudo make install

To create a database:

sudo mkdir /usr/local/pgsql/data
sudo chown _postgres:_postgres /usr/local/pgsql/data
cd /usr/local/pgsql/bin
sudo -u _postgres ./initdb -D /usr/local/pgsql/data -U postgres -W -A md5 -E UNICODE

To manage PostgreSQL server with OS X launchctl create /Library/LaunchDaemons/org.postgresql.postmaster.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
  <dict>
    <key>Label</key>
    <string>org.postgresql.postmaster</string>

    <key>RunAtLoad</key>
    <true />

    <key>UserName</key>
    <string>_postgres</string>

    <key>GroupName</key>
    <string>_postgres</string>

    <key>EnvironmentVariables</key>
    <dict>
      <key>PGDATA</key>
      <string>/usr/local/pgsql/data</string>
    </dict>

    <key>ProgramArguments</key>
    <array>
      <string>/usr/local/pgsql/bin/postmaster</string>
    </array>

    <key>StandardOutPath</key>
    <string>/usr/local/pgsql/logfile</string>

    <key>StandardErrorPath</key>
    <string>/usr/local/pgsql/logfile</string>
  </dict>
</plist>

chmod 644 /Library/LaunchDaemons/org.postgresql.postmaster.plist

To load the new launch agent and start up the server for the first time:

sudo launchctl load /Library/LaunchDaemons/org.postgresql.postmaster.plist

If you need to restart the daemon, use the following commands:

sudo launchctl stop org.postgresql.postmaster
sudo launchctl start org.postgresql.postmaster