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:
Connectivity problems to the single host where the test ran would make it look as though replication was destroyed. Overall, this monitoring approach has been fragile to numerous error conditions.
Nagios has no ability to benefit from the “cleverness” of automatically exploring the set of nodes. You need to set up a Nagios monitoring rule for each and every node being monitored.
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.
In an inventory system, that might be the order number for the most recently processed order.
In a domain registry, that might be the name of the most recently created domain.
An instance of the script will need to be run for each node that is to be monitored; that is the way Nagios works.
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
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:
Checking sl_listen for some “analytically determinable” problems. It lists paths that are not covered.
Providing a summary of events by origin node
If a node hasn't submitted any events in a while, that likely suggests a problem.
Summarizes the “aging” of table sl_confirm
If one or another of the nodes in the cluster hasn't reported back recently, that tends to lead to cleanups of tables like sl_log_1 and sl_seqlog not taking place.
Summarizes what transactions have been running for a long time
This only works properly if the statistics collector is
configured to collect command strings, as controlled by the option
stats_command_string = true
in postgresql.conf
.
If you have broken applications that hold connections open, this will find them.
If you have broken applications that hold connections open, that has several unsalutory effects as described in the FAQ.
The script does some diagnosis work based on parameters in the script; if you don't like the values, pick your favorites!
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, “ launch_clusters.sh ” and Section 19.1.20, “slon_watchdog” 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.