Postgres crontab on active/passive clusters

My first blog post…

If you’ve had to deal with PostgreSQL on an active/passive cluster using Pacemaker or some other clustering software, you’ve probably discovered that setting up local cron jobs can be a little tricky.

The problem with active/passive DB nodes there isn’t a simple way for your cron job to determine if the current node is the active node where Postgres is currently running.

You could just run it on every node but then you’d get error emails from the passive nodes that didn’t have a database running.

You could write a shell wrapper that checks cluster configs and statuses, or run psql and check connectivity, or modify the cluster config to switch crontabs along with the resources.  If you want Pacemaker to control the crontab, see this article Managing Cron Jobs With Pacemaker.

But I use a simple check that works well for me:  I just look for the postgres socket file as part of the cron command.  It’s very fast and doesn’t require any special permissions.

Below are example crontabs running pg_dumpall at midnight daily.

On Red Hat distros like Centos:

0 0 * * * test -S /tmp/.s.PGSQL.5432 && pg_dumpall > dump.sql

On Debian distros like Ubuntu:

0 0 * * * test -S /var/run/postgresql/.s.PGSQL.5432 && pg_dumpall > dump.sql

The socket file should only exists on the active PostgreSQL node (unless Postgres crashed hard).  The “test -S” checks if the socket file exists and returns a success if found, or failure if not found. Then the double-ampersand runs the next command only if the test command found the socket.

I like the simple solutions.