Posted on by Alderete

Install PostgreSQL Server and Ruby gem on Mac OS X

I recently needed to install the PostgreSQL database server on my Mac for development with Ruby on Rails. I ran into a few gotchas, and while I was eventually able to Google and guess my way to success, it took a while. This installation guide is as much for me, 6 months from now, as it is for others who may wish to also install Postgres.

These instructions were current as of April 22, 2009, and were executed on Mac OS X 10.5.6 with Rubygems 1.3.2 and the Postgres 8.3.7 distribution. The shelf life of these instructions is probably brief, though they should work at least as a rough guide until the next major release of Mac OS X, or of Postgres.

Preferences and Biases

There are other Postgres + Rails instructions which recommend the use of either MacPorts or Fink. I personally dislike these solutions; part of it is having used Fink in its early days and having it screw up royally, and part of it is a sense that the efforts are misplaced. (Why not make your Open Source contribution of how to get a project properly compiled and installed on Mac OS X directly in the project itself?) In any event, I refuse to use either…though I agree that they have a place for people less comfortable compiling software directly from source code.

I also prefer not to use pre-compiled binary installations of Open Source tools, unless the maintainer has demonstrated a long-term commitment and expertise in building and maintaining the package. Marc Liyanage did a terrific job with PHP 5 for Mac OS X releases up through “Tiger.” MySQL is doing a great job creating Mac OS X installer packages for the MySQL database. But many binary installations either make odd decisions about where to put things, or don’t get maintained, or make module/configuration/packaging decisions different from what I need, or all three.

So I generally prefer to compile from source code, and install my additions to Mac OS X in /usr/local. Dan Benjamin explains the logic and benefits of /usr/local in exhausting detail, I won’t go into it further. Either you agree, and these instructions may be useful to you, or you don’t, and you should keep Googling.

Prerequisites and Preconditions

  • These instructions are for Mac OS X 10.5 “Leopard” only.
  • You need to be comfortable, or better yet confident, on the command line.
  • You need to have Apple’s developer tools installed.
  • You need to have current versions of Ruby on Rails and Rubygems installed.
  • You need to understand what it means to update your $PATH environment, preferrably in ~/.bash_login. You do have a ~/.bash_login file, right?

But mostly this document assumes that you’ve already gone through the outstanding instructions for installing a complete Ruby on Rails development suite provided at Hivelogic. Indeed, if you didn’t use those instructions, at least mostly, you probably shouldn’t use mine either.

The Instructions

There are two things that need to be installed, the Postgres server and the Ruby gem for the Postgres database driver. You will also want to add a launchd task for starting and stopping the database server.

Installing the PostgreSQL Database Server

Postgres traditionally runs as a “daemon,” or background process, and manages all control to its files, especially its data. This requires that you create a dedicated user account and group, which only Postgres will use. While you could do this in the Accounts preference pane, it’s better to do this from the command line, so that the system won’t treat the Postgres user like a normal user. This is done with the Directory Service command line utility, dscl.

Before creating the Postgres user you need to pick out internal ID numbers for the user and group. You need to find a number that is not currently in use, so start by seeing what is in use. View the list of user accounts and ID numbers (note that some output is elided):

$ dscl . -list /Users UniqueID
_amavisd           83
_appowner          87
_appserver         79
[...]
_mysql             74
[...]
alderete           501
daemon             1
nobody             -2
root               0

Next the groups:

$ dscl . -list /Groups PrimaryGroupID
_amavisd                       83
_appowner                      87
_appserveradm                  81
[...]
_mysql                         74
[...]
tty                            4
utmp                           45
wheel                          0

Within these lists you want to find an ID number under 500 that is not in use. My primary source for these instructions recommending adding 100 to the ID for MySQL, which seems reasonable. It’s also probably helpful to find a number that is available for both the user and the group ID, so you don’t have to worry about which is which. From the output above, I chose 174, which met both criteria.

With chosen user and group IDs, create the user. Here we are creating a user with a $HOME of /usr/local/pgsql, the location where we will be installing Postgres. We’re also creating the user in such a way that it’s useful only as a daemon; it is not possible to log in to the system as this user, either at the normal login screen or via ssh.

$ sudo dscl . -create /Users/postgres UniqueID 174
$ sudo dscl . -create /Users/postgres PrimaryGroupID 174
$ sudo dscl . -create /Users/postgres HomeDirectory /usr/local/pgsql
$ sudo dscl . -create /Users/postgres UserShell /usr/bin/false
$ sudo dscl . -create /Users/postgres RealName "PostgreSQL Administrator"
$ sudo dscl . -create /Users/postgres Password \*

Now verify that the user was created as expected, by reading back the values from the system:

$ dscl . -read /Users/postgres
AppleMetaNodeLocation: /Local/Default
GeneratedUID: 0024E028-00A9-48FB-B14A-68026D442870
HomeDirectory: /usr/local/pgsql
Password: *
PrimaryGroupID: 174
RealName:
 PostgreSQL Administrator
RecordName: postgres
RecordType: dsRecTypeStandard:Users
UniqueID: 174
UserShell: /usr/bin/false

Follow a similar procedure to create and verify the group:

$ sudo dscl . -create /Groups/postgres PrimaryGroupID 174
$ sudo dscl . -create /Groups/postgres Password \*
$ dscl . -read /Groups/postgresAppleMetaNodeLocation: /Local/Default
GeneratedUID: 7A7F8760-455F-4315-84DD-2DA330AD9845
Password: *
PrimaryGroupID: 174
RecordName: postgres
RecordType: dsRecTypeStandard:Groups

With the dedicated user and group created, you can now download, compile, and install Postgres. The source code distribution can be downloaded from one of the Posgresql.org mirrors. If you use Safari to download it, be sure you’ve turned off the %(ui)Open “safe” files after downloading% option. (And leave it off, it is a known security risk.)

Move the archive to your preferred location (such as /usr/local/src), start a Terminal session there, and:

$ tar zxvf postgresql-8.3.7.tar.gz
$ cd postgresql-8.3.7
$ ./configure
[...]
$ make
[...]
$ sudo make install
[...]

Note: There may be options you want to enable with the configure script, such as encryption or authentication methods. Use ./configure --help to get more details.

Installing the software does not actually create a location to store the database data, or initialize the system databases. Do that next:

$ sudo mkdir /usr/local/pgsql/data
$ sudo chown postgres:postgres /usr/local/pgsql/data
$ sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
Password:
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
[...]
Success. You can now start the database server using:
    /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
or
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

You’ll want to create a Postgres user (distinct from the system user for Postgres), but before you can do that, you need to start the server. Below I describe how you can have Postgres start automatically when your computer boots, but for now, let’s just start it up manually:

$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start

Now create that Postgres user, and a default database for it to use. This is the account that you will eventually use in the database.yml file of your Rails applications. And finally, use the psql command line tool to verify that we’ve successfully created the user and database.

$ sudo -u postgres createuser alderete
$ createdb
$ psql
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
[...]
alderete=# \l
               List of databases
           Name           |  Owner   | Encoding 
--------------------------+----------+----------
 alderete                 | alderete | UTF8
 postgres                 | postgres | UTF8
 template0                | postgres | UTF8
 template1                | postgres | UTF8
(4 rows)
alderete=# \q

Note: The above illustrates creating a database user and new database, both named for my Unix account name, “alderete”. Using the Unix account name simplifies using Postgres’s command line tools, you won’t have to enter the Postgres account name every time. But that will only work if your name is “alderete” too. ;-) Instead, use your Unix (or “short”) account name instead! This will likely be visible in your command line prompt, but it will also appear in the list of users you generated at the beginning of these instructions.

Finally, add important Postgres directories to your various system paths. Add the following lines to your ~/.bash_login file:

export PATH="$PATH:/usr/local/pgsql/bin"
export MANPATH="$MANPATH:/usr/local/pgsql/man"
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/usr/local/pgsql/lib"

Be sure to source this file to activate the new environment settings:

$ . ~/.bash_login

Install the Postgres Ruby Gem

Normally installing a Ruby gem is pretty simple, but the Postgres database driver has native (compiled) components, and these need to be linked against the Postgres binary. The problem is that by default Postgres is only built for the hardware architecture of your computer (PowerPC or Intel), while the gem appears to try to link against both architectures. This causes an error, and the helpful suggestion that appears with the error message does not in fact work, producing the exact same error message.

The trick to getting past this is to set the preferred architecture right on the gem install command, like so:

$ sudo env ARCHFLAGS='-arch i386' gem install \
  postgres -- --with-pgsql-dir=/usr/local/pgsql
Password:
Building native extensions.  This could take a while...
Successfully installed postgres-0.7.9.2008.01.28
1 gem installed

(If you’re still using a PowerPC-based computer, change “i386” to “ppc”.)

This should allow the gem to install successfully.

Automatically Starting Postgres at System Startup

On my development systems I’m using the database constantly, and so I have it set to start up automatically, as part of my computer’s boot process. The “Leopard” way to do this is to add a launchd task to your system. This is a text file that you’ll save on your desktop, and then move to /Library/LaunchDaemons/org.postgresql.dbms.plist. The following was copied from my primary source (see below):

<?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.dbms</string>
    <key>UserName</key>
    <string>postgres</string>
    <key>GroupName</key>
    <string>postgres</string>
    <key>ProgramArguments</key>
    <array>
      <string>/usr/local/pgsql/bin/postmaster</string>
      <string>-D</string>
      <string>/usr/local/pgsql/data</string>
    </array>
    <key>StandardErrorPath</key>
    <string>/usr/local/pgsql/logs/postgresql.log</string>
    <key>RunAtLoad</key>
    <true/>
</dict>
</plist>

Once this file is in place, you can load it by restarting your computer, or with this command:

$ sudo launchctl load /Library/LaunchDaemons/org.postgresql.dbms.plist

If you can believe it, you’re done. Pour a cold one, or get right to it with rails mynewapp -d postgresql. Good luck!

Sources, Citations, and Credits

By far the most complete posting on this topic was Working Software’s Building and Installing PostgreSQL on Mac OS X, which covered installing Postgres from source completely. While the instructions above are copy/pasted from my Terminal windows, I was copying lines directly from this article to paste into Terminal in the first place.

The one thing that wasn’t covered in the above article was installing the Ruby gem for Postgres, which is required to use Postgres in Rails. The normal gem install [gemname] produced errors, and most suggestions I found didn’t work any better. What finally did work was including the “-arch i382” option directly on the gem installation line, which appears to be orginally published towards the bottom of PostgreSQL on Leopard for Rails: A few notes. (A few other posts repeat this advice, so I’m not sure who first published it.)

Finally, although it’s quite dated, the Apple Developer Connection article PostgreSQL on Mac OS X provides useful background information, and notes about using Postgres from other languages besides Ruby.