PostgreSQL (often called Postgres) is the Other Major Database for Drupal. It has one of the same strange characteristics that Drupal does: its adherents swear that it is the best thing since sliced bread. In this article, we'll examine how to get PostgreSQL installed, then get a Drupal 6 installation running on top of it.

Assumptions: XCode Tools from your Mac OS X DVD (also available from Apple Developer Connection) are installed. Your copy of PHP has support for Postgres (I used MAMP and it worked out of the box).

Getting PostgreSQL

There are several places to get Postgres. You could compile it yourself, or use Dru Satori's PostgreSQL for Mac, or Mark Liyanage's PostreSQL Installer. I used MacPorts to install it because (1) I wanted to use the latest version of Postgres (8.3.3) and Dru's installer had 8.3.1 and Mark's installer had 8.2.3 and (2) I already had MacPorts installed, and this gave me an excuse to try out Porticus.

After ensuring that MacPorts and Porticus were installed, I selected and installed the postgresql83 package (that is, the PostgreSQL client) and the postgresql83-server package (the PostgreSQL server).

Creating a Place for Data

The next step is to create the directory to hold Postgres data. The MacPorts port recommends:

You might be thinking, "but why do I want my database data hidden away under /opt? I want it right here in my home directory where I can look at it!" Ah, but because of the following command, you won't be able to look at the files directly anyway. (You can dump out the database contents as a backup and store that in your home directory if you want.)

This transfers ownership of the directory we just created to the postgres user (which was created during the pre-destroot phase of the install -- thanks, MacPorts!).

Creating the Database Cluster

Postgres keeps groups of databases together in a cluster. The following command means, "By the powers bestowed in me (the OS X root user) become the postgres user. As that user, create a new PostgreSQL database cluster in the directory /opt/local/var/db/postgresql83/defaultdb":

To which you should get the response:

Yay! PostgreSQL is now installed.

Configuration Files

Postgres has squirreled away its configuration files deep inside /opt/local/var/db/postgresql8/defaultdb, which is a pain to remember. These configuration files have the following permissions:

So, if you're a Unix junkie, that means you can su to the postgres user and edit the configuration files in vi or emacs. Me, I'd rather use a nice lickable editor like BBedit (please pause while I stroke my long, grey beard) or Textmate. So one approach is to place a copy of the postgresql.conf file in my home directory, where it will be backed up regularly. Then, the original postgresql.conf file will be replaced with a symlink to the real file in my home directory.

First, make a directory to store the configuration file in.

Now, copy the file from the Postgres data directory, preserving permissions (so postgres will still be able to read it):

Then add your group and give read and write access to it. My username is jvandyk, so:

Now the file can be easily edited to change various settings by double-clicking it in the Finder and using your favorite GUI editor.

Changing the Logging Detail

Hopefully one of the reasons you want to install Postgres is to help with Drupal development. It would be nice to be able to see when Drupal is asking Postgres to do something odd, and to see the angry mutterings from Postgres that result. Let's turn up to log level on Postgres to find out what's going on deep inside. Find the section of postgresql.conf that looks like this:

and set the log_min_messages option to info:

log_min_messages = info

Or, if you want to see every time the autovacuum process runs, you can use debug1 instead of info.

Starting and Stopping Postgres

The easiest approach is to just set Postgres to launch at startup, so it's "always on". The MacPorts install includes a launchd item for this, which can be installed with the following command:

This will also start the server immediately.

If you change your mind and want to have Postgres NOT launch at startup:

In that case, you will need to start PostgreSQL whenever you want to use it. You can do that with the pg_ctl command. But there's a catch. Remember when we created the initial database cluster, and we had to use sudo su postgres to execute the command as the postgres user? We have to do that here, too, which makes the commands for starting and stopping rather verbose. So we'll use the magic of shell functions to make ourselves two easy commands, one to start and one to stop Postgres. Here's how to do it in the Terminal, assuming you're using the bash shell, which is the default shell in Leopard:

And here's one more convenience function. Typing reloadpg will reload the configuration files.

Now when you type startpg in the Terminal, you will be prompted for your computer's administrative password (just like when you install software) and PostgreSQL will start. To stop PostgreSQL, just type stoppg. Easy!

To have these handy shortcuts available whenever you log in, add the lines of code that we used to create the shell functions to ~/.bash_profile.

Seeing What's Going On

You will want to be able to see what is going on with PostgreSQL. The log is at /opt/local/var/log/postgresql83/postgres.log but you might want to monitor it in real time for troubleshooting. I find it handy to create a shell function called Tail Postgres, abbreviated as tpg.

Now all you have to do is type tpg at the command line and output from the log will be shown in real time:

Again, add the line of code that we used to create the alias to ~/.bash_profile in order to have the alias "tpg" available permanently.

Creating a user for Drupal

Let's create a database user for Drupal to use. In the following command, we say "As the postgres user (that is, the postgres superuser account) create a new role named drupalesque. Prompt now for the password to use. Encrypt the password inside Postgres instead of storing it in clear text. This user is not a superuser and may not create databases or roles."

Creating a Database for Drupal from the Command Line

Now it's time to create the database that our Drupal install will use. We'll create a database named drupaldb that is owned by the database user drupalesque with the following:

Postgres will use the default encoding that was set when we ran initdb, which is UTF-8.

Installing Drupal

Install Drupal just like normal. On the database setup screen, choose pgsql as the database type, enter drupaldb as the database name, drupalesque as the database username, and boingechev as the database password. Using MAMP, this worked for me without a hitch.

Communicating with Postgres

Now that PostgreSQL is installed and started, how do you talk to it? Easy. Use the psql command. This command starts pgsql as the user we created and connects it to the database we created:

and this one starts it as the postgres superuser:

Note: if you don't want to type the entire path to the Postgres commands (e.g., you want to type psql instead of /opt/local/lib/postgresql83/bin/psql) you can add the path to the Postgres binaries to the paths that Leopard looks in for commands. You can do that with this command:

You'll need to restart for that to take effect.

Summary

We have only scratched the surface here. There are so many more questions. Is there something similar to phpMyAdmin for Postgres? Is there a quick tweak or two to improve performance, similar to turning on MySQL's query cache? And why didn't Postgres prompt us for a password when we started psql as the superuser? Regrettably, those intriguing questions will have to wait. No, this article is only meant to get you up and running with Drupal on Postgres on OS X 10.5 Leopard. I hope it does that.

References

- ShiftEleven: Installing PostgreSQL on Leopard using MacPorts
- PostgreSQL 8.3 Manual
- #postgresql irc channel on FreeNode. Special thanks to RhodiumToad and dru_.