acts_as_blog

Building PostgreSQL on Mac OS X Leopard

Some time ago I wrote a german article on how to install PostgreSQL on Leopard. It seems I struck a chord with many people. So I decided to make this article accessible to a wider audience by translating it to English. I modified and updated parts of it where appropriate. So here it is without further ado.

In the course of this article I will install PostgreSQL from source. While I tried to describe all the steps along the way, you should have at least some basic experience with the terminal. Be also aware that you will be required to make changes to vital system settings. Mistakes can render your system unusable if not completely dead. Don’t say I didn’t warn you! Having said that let me reassure you that most readers of the original article have succeeded in installing PostgreSQL.

To build PostgreSQL on your system you will need a C compiler. If you haven’t already done so please install the Xcode tools that came on your Leopard install DVD or grab a fresh copy from Apple’s Developer Connection. I will be installing PostgreSQL 8.3.1 which is the latest version at the moment but these instructions should work with later versions, too.

Grab the latest sources from http://www.postgresql.org/ftp/latest/. The file you will need is usually named postgresql-8.x.x.tar.gz or postgresql-8.x.x.tar.bz2. Extract the contents of that archive to some place convenient. Open the terminal and change into the directory containing the extracted sources. From there start the build process with this command.

./configure --prefix=/Library/PostgreSQL --enable-thread-safety \
--with-krb5 --with-bonjour --with-openssl --with-gssapi \
&& make && sudo make install

NB Even though Leopard and PostgreSQL now support DTrace you currently will not be able to build PostgreSQL with DTrace support as this only works on Solaris right now. Later versions of PostgreSQL might get fixed but no luck so far.

PostgreSQL usually runs with privileges of a special user. I chose user and group id 183 since it was unused on my system. To make sure this user/group id not already taken on your system use the following two commands to list all users/groups on your system and pick some other unused ids between 100 and 500 if neccesary.

dscl . -list /Users UniqueID
dscl . -list /Groups PrimaryGroupID

Unlike other Unix systems Leopard uses Open Directory to store user accounts. I’m going to be using dscl to create a user and group for PostgreSQL. (Adjust user and group ids if neccesary)

sudo dscl . -create /Groups/_postgres 
sudo dscl . -create /Groups/_postgres RealName 'PostgreSQL Server'
sudo dscl . -create /Groups/_postgres PrimaryGroupID 183          
sudo dscl . -create /Groups/_postgres UserShell /usr/bin/false
sudo dscl . -create /Groups/_postgres Password '*'
sudo dscl . -create /Groups/_postgres NFSHomeDirectory /var/empty

sudo dscl . -create /Users/_postgres   
sudo dscl . -create /Users/_postgres Password '*'
sudo dscl . -create /Users/_postgres RealName 'PostgreSQL Server'
sudo dscl . -create /Users/_postgres PrimaryGroupID 183
sudo dscl . -create /Users/_postgres UniqueID 183      
sudo dscl . -create /Users/_postgres UserShell /usr/bin/false
sudo dscl . -create /Users/_postgres NFSHomeDirectory /Users/Shared/PostgreSQL

If no errors occurred you should be able to test that newly created user by typing id _postgres. The response should look like this:

uid=183(_postgres) gid=183(_postgres) groups=183(_postgres)

Since I installed everything into /Library/PostgreSQL there’s some extra work needed to have all command line tools conveniently available. Either add /Library/PostgreSQL/bin to the shell’s $PATH or symlink all those tools to a directory that is already in the $PATH. I opted for the latter. Since Leopard comes with a pristine /usr/local directory it might be neccessary to create some directories first. Something like sudo mkdir -p /usr/local/{bin,man/man1,man/man7} should do the trick. To create all those symlinks you can use this little script.

#!/bin/sh

POSTGRES_DIR=/Library/PostgreSQL
DEST_DIR=/usr/local
SYMLINK_DIRS="bin man/man1 man/man7"

for CUR_DIR in $SYMLINK_DIRS; do
  for CUR_FILE in $POSTGRES_DIR/$CUR_DIR/*; do
    sudo ln -nfs $CUR_FILE $DEST_DIR/$CUR_DIR${CUR_FILE#$POSTGRES_DIR/$CUR_DIR}
  done
done

A quick check with which psql should reveal if everything went smoothly. Zsh users might need to rehash.

I decided to store the database in /Users/Shared/PostgreSQL. As this directory usually does not exist it must be created first.

sudo mkdir -p /Users/Shared/PostgreSQL
sudo chown _postgres:_postgres /Users/Shared/PostgreSQL

Now the database can be initialized. During the initialization process you will be asked to enter a password for the database superuser. Pick any password just don’t forget it. To initialize the database use this command.

sudo -u _postgres initdb -U dba -A md5 -W -E UTF8 \
-D /Users/Shared/PostgreSQL/data

A successful initialization looks similar to this:

The files belonging to this database system will be owned by user "_postgres".
This user must also own the server process.

The database cluster will be initialized with locale de_DE.UTF-8.
The default text search configuration will be set to "german".

creating directory /Users/Shared/PostgreSQL/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers/max_fsm_pages ... 2400kB/20000
creating configuration files ... ok
creating template1 database in /Users/Shared/PostgreSQL/data/base/1 ... ok
initializing pg_authid ... ok
Enter new superuser password: 
Enter it again: 
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

Success. You can now start the database server using:

    postgres -D /Users/Shared/PostgreSQL/data
or
    pg_ctl -D /Users/Shared/PostgreSQL/data -l logfile start

I intend to use PostgreSQL on my machine for development with Ruby on Rails so I prefer logging all commands sent to the database. In a production environment that might impact performance negatively so be careful how much logging you really need.

Create in /var/log a directory for the log files.

sudo mkdir /var/log/postgres

I want PostgreSQL to send all log messages to the syslog daemon which in turn will write them to disk. Add to /etc/syslog.conf this line:

local3.*                                /var/log/postgres/postgres.log

To prevent the log file from growing until it fills up your whole disk add this to /etc/newsyslog.conf:

/var/log/postgres/postgres.log          644  5     *    @T00  J

Now create an empty log file and restart the syslog daemon.

sudo touch /var/log/postgres/postgres.log
sudo kill -HUP $(cat /var/run/syslog.pid)

Of course PostgreSQL must be told to use syslog. These two lines in /Users/Shared/PostgreSQL/data/postgresql.conf will take care of that:

log_destination = 'syslog'
syslog_facility = 'LOCAL3'

While you are editing this file you should tell PostgreSQL what to log. As I mentioned before, I want it all. (see PostgreSQL documentation: Error Reporting and Logging)

log_line_prefix = '%u@%h:/%d '
log_statement = 'all'

PostgreSQL is now ready to be launched. But it would be tedious to always start it by hand. I created a launchd script to have PostgreSQL start automatically at boot time.

<?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>UserName</key>
    <string>_postgres</string>
    <key>OnDemand</key>
    <false/>
    <key>ProgramArguments</key>
    <array>
        <string>/Library/PostgreSQL/bin/postmaster</string>
        <string>-D</string>
        <string>/Users/Shared/PostgreSQL/data</string>
        <string>-c</string>
        <string>log_connections=YES</string>
    </array>
    <key>ServiceDescription</key>
    <string>PostgreSQL Server</string>
</dict>
</plist>

Save this script as org.postgresql.postmaster.plist in /Library/LaunchDaemons and have it read by launchd using sudo launchctl load /Library/LaunchDaemons/org.postgresql.postmaster.plist. This is the moment of truth. Now you finally get to know if you screwed up somewhere along the way. This is what you should get. (Or at least something resembling that)

Venus:~ fastjack$ ps ax | grep postmaster
 5315   ??  Ss     0:00.31 /Library/PostgreSQL/bin/postmaster -D /Users/Shared/PostgreSQL/data -c log_connections=YES

The database should accept connections:

Venus:~ fastjack$ psql -U dba template1
Password for user dba: 
Welcome to psql 8.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# 

As you might have guessed from the name, this is a blog about Ruby on Rails. This article would not be complete without describing how to install the Postgres gem so you can actually use the database to do something useful. Since I installed PostgreSQL in an unconventional location the Postgres gem will not be able to find it automatically. This command should take care of that:

sudo gem install postgres -- --with-pgsql-lib-dir=/Library/PostgreSQL/lib \
--with-pgsql-include-dir=/Library/PostgreSQL/include

If you have an Intel mac you will probably have to change that line to:

sudo env ARCHFLAGS='-arch i386' gem install postgres -- \
--with-pgsql-lib-dir=/Library/PostgreSQL/lib \
--with-pgsql-include-dir=/Library/PostgreSQL/include

That’s it. Now you’re ready to start hacking with Ruby on Rails