5. Monitoring

5.1. Nagios Replication Checks

The script in the tools directory called pgsql_replication_check.pl represents some of the best answers arrived at in attempts to build replication tests to plug into the Nagios system monitoring tool.

A former script, test_slony_replication.pl, took a "clever" approach where a "test script" is periodically run, which rummages through the Slony-I configuration to find origin and subscribers, injects a change, and watches for its propagation through the system. It had two problems:

The new script, pgsql_replication_check.pl, takes the minimalist approach of assuming that the system is an online system that sees regular "traffic," so that you can define a view specifically for the replication test called replication_status which is expected to see regular updates. The view simply looks for the youngest "transaction" on the node, and lists its timestamp, age, and some bit of application information that might seem useful to see.

An instance of the script will need to be run for each node that is to be monitored; that is the way Nagios works.

5.2. Monitoring Slony-I using MRTG

One user reported on the Slony-I mailing list how to configure mrtg - Multi Router Traffic Grapher to monitor Slony-I replication.

... Since I use mrtg to graph data from multiple servers I use snmp (net-snmp to be exact). On database server, I added the following line to snmpd configuration:

exec replicationLagTime  /cvs/scripts/snmpReplicationLagTime.sh 2
where  /cvs/scripts/snmpReplicationLagTime.sh looks like this:
#!/bin/bash
/home/pgdba/work/bin/psql -U pgdba -h 127.0.0.1 -p 5800 -d _DBNAME_ -qAt -c
"select cast(extract(epoch from st_lag_time) as int8) FROM _irr.sl_status
WHERE st_received = $1"

Then, in mrtg configuration, add this target:

Target[db_replication_lagtime]:extOutput.3&extOutput.3:public at db::30:::
MaxBytes[db_replication_lagtime]: 400000000
Title[db_replication_lagtime]: db: replication lag time
PageTop[db_replication_lagtime]: <H1>db: replication lag time</H1>
Options[db_replication_lagtime]: gauge,nopercent,growright

5.3. test_slony_state

This script does various sorts of analysis of the state of a Slony-I cluster.

You specify arguments including database, host, user, cluster, password, and port to connect to any of the nodes on a cluster. You also specify a mailprog command (which should be a program equivalent to Unix mailx) and a recipient of email.

You may alternatively specify database connection parameters via the environment variables used by libpq, e.g. - using PGPORT, PGDATABASE, PGUSER, PGSERVICE, and such.

The script then rummages through sl_path to find all of the nodes in the cluster, and the DSNs to allow it to, in turn, connect to each of them.

For each node, the script examines the state of things, including such things as:

The script does some diagnosis work based on parameters in the script; if you don't like the values, pick your favorites!

5.4. search-logs.sh

This script is constructed to search for Slony-I log files at a given path (LOGHOME), based both on the naming conventions used by the Section 19.3 and Section 19.1.20 systems used for launching slon processes.

Errors, if found, are listed, by log file, and emailed to the specified user (LOGRECIPIENT); if no email address is specified, output goes to standard output.

LOGTIMESTAMP allows overriding what hour to evaluate (rather than the last hour).

An administrator might run this script once an hour to monitor for replication problems.