Using PostgreSQL with Supervisor on Ubuntu 10.10

For my latest project, Auto Swatch, I needed a process management system to handle the availability of critical services, like nginx, memcached, PostgreSQL, and the application's Gunicorn processes. We use Supervisor at Dwaiter, so this was my natural choice.

"Supervisor is a client/server system that allows its users to monitor and control a number of processes on UNIX-like operating systems."

Steve Losh said that the trick to getting anything working with Supervisor is to make sure it doesn't daemonize. But, alas, there are other tricks.

This blog post concerns getting PostgreSQL working with Supervisor.

First, install PostgreSQL normally. On Ubuntu 10.10, the default way of running PostgreSQL is via Unix socket. This socket lives at/var/run/postgresql. Now, believe it or not, this creates a problem when trying to run PostgreSQL in foreground mode, as required by Supervisor. More on that later.

To understand the problem, you have to understand how PostgreSQL runs normally. Typically, you run PostgreSQL via init script, like/etc/init.d/postgresql start. This script runs a bunch of code, some of which actually creates the dir/var/run/postgresqland sets the proper permissions.

Problem: when you run PostgreSQL in foreground mode with Supervisor, it tries to open the socket in/var/run/postgresql. This will work if you've previously run PostgreSQL with the init script, because the init script created and set the permissions for that dir. However, after reboot, that dir will be gone, and PostgreSQL won't be able to open the socket there, resulting in failure. So, we either need to move the socket (meaning a config change with your app), or make PostgreSQL run on a TCP port instead. TCP port it is.

To get things running with Supervisor:

• Stop PostgreSQL: sudo /etc/init.d/postgresql stop
• Move the init script somewhere safe: sudo mv /etc/init.d/postgresql ~/somewhere-safe (so it won't startup in daemon mode)
• Edit the PostgreSQL config: sudo vim /etc/postgresql/8.4/main/postgresql.conf
• Line 49: comment out external_pid_file (not needed for TCP mode)
• Line 63: comment out port (unless you want to change the default port of 5432)
• Line 68: change unix_socket_directory to /tmp

With this config, PostgreSQL will default to using TCP port 5432 instead of a Unix socket.

Here's what my Supervisor conf looks like for PostgreSQL:

[program:postgresql]
user=postgres
command=/usr/lib/postgresql/8.4/bin/postmaster -D "/var/lib/postgresql/8.4/main"
process_name=%(program_name)s
stopsignal=INT
autostart=true
autorestart=true
redirect_stderr=true

Now, you might also get an error about PostgreSQL not being able to read the config file at/var/lib/postgresql/8.4/main/postgresql.conf.

I had to symlink:

sudo ln -s /etc/postgresql/8.4/main/postgresql.conf /var/lib/postgresql/8.4/main/postgresql.conf


Your mileage may vary. Good luck.