Running PostgreSQL with Supervisord

Question

I want to run PostgreSQL 9.1 using Supervisor on Ubuntu 10.04. At the moment, I manually start PostgreSQL using the init script:

/etc/init.d/postgresql start

According to this post: http://nicksergeant.com/using-postgresql-with-supervisor-on-ubuntu-1010/, I need to modify the PostgreSQL config to make it run on TCP port instead of Unix socket, in order to make PostgreSQL work with Supervisor.

I have two questions regarding this approach:

1. Considering this is more of hack, is there any implication (e.g. security/permissions, performance, etc) of doing this?

2. Why cannot we just run the same init script postgresql in Supervisor config? Instead, as shown in the link above, it runs postmaster?

UPDATE:

Thanks to the useful suggestions from both answers below, I have setup a script for Supervisor to invoke PostgreSQL directly:

#!/bin/sh

# This script is run by Supervisor to start PostgreSQL 9.1 in foreground mode

if [ -d /var/run/postgresql ]; then
    chmod 2775 /var/run/postgresql
else
    install -d -m 2775 -o postgres -g postgres /var/run/postgresql
fi

exec su postgres -c "/usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf"

I also set the config: /etc/postgresql/9.1/main/start.conf to manual so that PostgreSQL does not start automatically on boot (however, it's not clear to me whether this config is loaded). And then I setup the Supervisor config for postgres as:

[program:postgres]
user=root
group=root
command=/usr/local/bin/run_postgresql.sh
autostart=true
autorestart=true
stderr_logfile=/home/www-data/logs/postgres_err.log
stdout_logfile=/home/www-data/logs/postgres_out.log
redirect_stderr=true
stopsignal=QUIT

So now, I can start PostgreSQL in supervisorctl by doing start postgres, which runs fine. However, after I issue stop postgres, although supervisorctl declares postgres is stopped, the server apparently is still running as I can psql into it.

I wonder if this is a Supervisor config issue, or a PostgreSQL issue. Any suggestion welcome!

Answer 1

The blog post is rather badly written. There is no "TCP mode": the post's suggested method will still listen on a Unix socket, just in a different directory. Comments in the post such as "external pid file - not needed for TCP mode" are very misleading.

postmaster is the traditional name for the postgresql executable (to distinguish the master dispatching process from the backend slaves). For some time now there has been no separate executable, and now it is installed as simply "postgres".

Assuming that Supervisor is broadly simliar to the qmail/daemontools supervise scheme, it would be entirely possible (in fact, quite normal) to have it run a script that sets up the directories and environment, and then execs postgres with the requisite arguments (or propagates arguments given to the wrapper script, which would be unusual with supervise but makes more sense when you have a config file to put arguments into).

The way supervise worked (and I'm going to continue to assume "Supervisor" is the same) is to have the supervisor process run a subprocess as specified, and simply relaunch a new subprocess if it exits. This is based on the idea that the process being launched is a long-lived daemon process that only exits when something goes badly wrong, and that simply restarting it is a valid fix. By contrast, init scripts such as in /etc/init.d run the subprocess and detach it, and return control to their caller- if the subprocess exits, nothing special happens, and it must be restarted manually. If you tried to simply run /etc/init.d/postgresql start from supervise, it would continuously keep spawning postgresql daemons, as the return from the init script would be interpreted as the daemon process having exited, when in fact it had been started and detached.

Answer 2

I am trying to make both tomcat and postgres run under supervisor, and found some hints here : https://serverfault.com/questions/425132/controlling-tomcat-with-supervisor

Here is my modified run_postgresql.sh, using bash :

#!/bin/bash

# This script is run by Supervisor to start PostgreSQL 9.1 in foreground mode

function shutdown()
{
    echo "Shutting down PostgreSQL"
    pkill postgres
}

if [ -d /var/run/postgresql ]; then
    chmod 2775 /var/run/postgresql
else
    install -d -m 2775 -o postgres -g postgres /var/run/postgresql
fi

# Allow any signal which would kill a process to stop PostgreSQL
trap shutdown HUP INT QUIT ABRT KILL ALRM TERM TSTP

exec sudo -u postgres /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main --config-file=/etc/postgresql/9.1/main/postgresql.conf

With this script postgresql stops correctly after supervisorctl stop postgres.

Answer 3

To avoid auto-starting the service with the /etc/init.d scripts, the package for postgresql 9.1 provides a file /etc/postgresql/9.1/main/start.conf that contains:

# Automatic startup configuration
# auto: automatically start/stop the cluster in the init script
# manual: do not start/stop in init scripts, but allow manual startup with
#         pg_ctlcluster
# disabled: do not allow manual startup with pg_ctlcluster (this can be easily
#           circumvented and is only meant to be a small protection for
#           accidents).

auto

This is the file to modify to avoid auto-start as opposed to moving away /etc/init.d/postgresql as the blog post suggests.

Also, changing unix sockets parameters for lack of /var/run/postgresql doesn't look like the best idea, because it's the default for any program linked with libpq, and because there's no difficulty in creating that directory with the proper permissions, just like it's done by the package's start sequence in /usr/share/postgresql-common/init.d-functions:

# create socket directory
if [ -d /var/run/postgresql ]; then
    chmod 2775 /var/run/postgresql
else
install -d -m 2775 -o postgres -g postgres /var/run/postgresql
fi

And although the default value shouldn't cause a problem, note that whether postmaster ultimately stays in foreground or forks and runs in background is controlled by the silent_mode parameter in postgresql.conf. Make sure that it is off.