PostgreSQL is the big, powerful, full-featured relational database management system.

Of course, PostgreSQL is the very antithesis of the cdb database paradigm we'll most often see on our djb servers. But when you have a big job to do, this is the RDBMS to do it. (The PostgreSQL project mascot is an elephant!)

A PostgreSQL installation provides its own connection listening daemon, called postmaster. By default, postmaster listens on a local domain socket, servicing client connections from the local host. But it may also be configured to service TCP/IP connections over the network, using port 5432 by default.

All the many benefits of daemontools apply to a PostgreSQL service: consistent and convenient startup; easy and reliable signaling; tai-stamped and auto-rotated multilogging. Although the PostgreSQL package ships with its own pg_ctl utility script, the usual daemontools svc service control program provides an excellent interface to the PostgreSQL server.

Before setting up the service, first prepare a database "cluster" by running initdb under a PostgreSQL "superuser" account; "postgres" is a common choice. After adding the "postgres" user to the system, the database cluster initialization goes something like this:

# mkdir /var/postgresql/data
# chown postgres /var/postgresql/data
# su - postgres
$ initdb -D /var/postgresql
$ exit

Now we are ready to configure a daemontools service. Here's the "run" script for /var/svc.d/postgresql/run:

#!/bin/sh
# postgresql/run
# daemontools run script for postgresql
# ===
CLUSTER=/var/postgresql/data

exec 2>&1

echo "*** Starting postgresql on data ${CLUSTER}"
exec \
  setuidgid postgres \
    postmaster -D ${CLUSTER}

### that's all, folks!

Notice the simplicity here, especially in comparison to the platform-specific installation instructions you will otherwise find in the documentation. This is a very easy service to put up under daemontools.

By default, postmaster runs in the foreground and logs to stdout. All we do is run it under setuidgid with the "superuser" defined for PostgreSQL's cluster initialization, and the -D option to tell it where to find its data directory. Everything else is done in the postgresql.conf configuration file.

The usual run script for the multilogger goes in /var/svc.d/postgresql/log/run:

#!/bin/sh
# postgresql/log/run
exec setuidgid multilog multilog t /var/multilog/postgresql

### that's all, folks!

Setup the log directory in /var/multilog:

# mkdir -p /var/multilog/postgresql
# chown multilog:nofiles /var/multilog/postgresql

Make sure the "run" scripts are executable, then link into /service to start everything up:

# ln -s /var/svc.d/postgresql /service/postgresql

PostgreSQL should be up and serving! su to the "postgres" user account and try a psql monitor session:

# su -l postgres
$ psql template1
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type  \copyright for distribution terms
      \h for help with SQL commands
etc.

Check out your logs as usual, and work on fine-tuning the configuration file for your particular installation.

PostgreSQL responds to a number of signals, accessible with the svc utility:

Read the postmaster(1) man page for more information on signals and shutdown procedures. In particular, note that SIGKILL is strongly discouraged; use the other terminations instead.

The "nicest" shutdown is svc -d. This uses the SIGTERM signal, and waits for clients to terminate. Otherwise, use svc -id to force immediate client termination and keep the server down. The QUIT signal is available if you have applied the daemontools-0.76.sigq12.patch patch for daemontools, and as suggested in our installation instructions.