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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s