Devoh

Installing PostgreSQL on Mac OS X 10.5 Leopard

Some Background

I'm a big fan of the compile-it-yourself approach to installing UNIX software under Mac OS X. I like the control it affords, and the ability to know exactly what I'm installing and where it is being installed.

This tutorial is an attempt to provide a comprehensive guide to the installation of PostgreSQL from source on the Mac. I've used this process on my own system, though software has a tendency to change quickly, so if some part of it fails to produce the expected outcome, please let me know.

Download and Compile the Source

Since we'll be compiling from source, you'll need to have the Mac OS X developer tools installed before proceding. If needed, they can found on your Mac OS X installation disc, or on Apple's developer site.

First, download and extract the PostgesSQL source code.

curl -O http://ftp7.us.postgresql.org/pub/postgresql/source/v8.3.4/postgresql-8.3.4.tar.gz
tar xzvf postgresql-8.3.4.tar.gz

Then, run the following from within the root of the source directory to configure, build, and install it:

cd postgresql-8.3.4
./configure --enable-thread-safety --with-bonjour
make
sudo make install

This will install PostrgreSQL 8.3.4, which was the latest version at the time of this writing. You may want to check for a more recent version and use that instead.

Create a User and Group

Leopard makes use of directory services for its user accounts and groups. While this is a powerful feature, it also makes some simple tasks—like creating new users—into a somewhat painful process.

First, you'll need to find an unused user and group ID. Use the following commands to list the IDs for the users and groups on your system.

dscl . -list /Groups PrimaryGroupID | awk '{print $2}' | sort -n
dscl . -list /Users UniqueID | awk '{print $2}' | sort -n

For the purposes of this tutorial, let's assume an ID of 113 for both the user and the group.

Since the Leopard convention is to prefix system accounts with an underscore, use the following commands to create a user called _postgres:

sudo dscl . create /Users/_postgres UniqueID 113
sudo dscl . create /Users/_postgres PrimaryGroupID 113
sudo dscl . create /Users/_postgres NFSHomeDirectory /usr/local/pgsql/
sudo dscl . create /Users/_postgres RealName "PostgreSQL Server"
sudo dscl . create /Users/_postgres Password "*"
sudo dscl . append /Users/_postgres RecordName postgres

Then, create the _postgres group:

sudo dscl . create /Groups/_postgres
sudo dscl . create /Groups/_postgres PrimaryGroupID 113
sudo dscl . append /Groups/_postgres RecordName postgres
sudo dscl . create /Groups/_postgres RealName "PostgreSQL Users"

Initialize the Database

While PostgreSQL has been installed at this point, it still needs to be initialized with an empty database and some default configuration files.

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

This will prompt you for the superuser password and initialize the authentication method to MD5.

Create a launchd Launch Agent

With Tiger, Apple introduced launchd as a replacement for init and xinetd in Mac OS X. In this case, the change makes our lives a little easier. In order to ensure that PostgreSQL is started up during boot, create the file /Library/LaunchDaemons/org.postgresql.postmaster.plist with the following contents:

<?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>

Issuing this command will 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

Make Sure it Works

We'll now attempt to connect to the database. Enter your password when prompted.

/usr/local/pgsql/bin/psql -U postgres template1

You should then see a prompt that looks like this:

template1=# 

Typing \q will exit the console.

Create a User and a Database

Creating users and databases can be done from the command line.

/usr/local/pgsql/bin/createuser -U postgres -S -d -R -P myuser
/usr/local/pgsql/bin/createdb -U myuser mydb

This will create a user named myuser that may create new databases, but is not a superuser and may not create new roles, and a new database named mydb that is owned by myuser.

Install the Ruby Gem

If you're a Ruby developer you may want to install the postgres gem, as well.

sudo env ARCHFLAGS='-arch i386' gem install postgres

The arch flag values should be set to ppc if you're not using an Intel Mac.

Use It

That should wrap things up. You now have a fully functional PostgreSQL installation that's ready for whatever schema you may throw at it next.