NAME
Slony Postgres Replication Check
VERSION
2.0
SYNOPSIS
This check connects directly to the database. It is designed to check to see how far behind Slony replication is on the database servers based on the last transaction.
Sample view definition: CREATE VIEW replication_status AS SELECT customer_name AS object_name, transaction_date, (date_part('epoch'::text, now() - transaction_date) / 60::double precision)::integer AS age FROM customer_orders ORDER BY id DESC LIMIT 1;
Modify the view for your environment. The idea is that you pick a table which has frequent write transactions and contains some kind of time stamp. Then create a replication_status view on the table which returns some kind of identifier (like a customer name), the time of the last transaction and the age of the last transaction in minutes.
For security reasons, you should create a nagios postgres user and only grant it select privileges on this view.
Query is as follows: SELECT * FROM replication_status
The results displayed: object_name | transaction_date | age -------------+------------------------+----- "B.A." Baracus | 2005-05-10 15:09:57+00 | 0
The age column displays age of the last transaction in minutes. We have tentatively set the warning threshold in Nagios to 20 minutes and the critical threshold to 40 minutes.
USAGE
USAGE: psql_replication_check.pl -h <host> -d <db> -p <port> -U <username>
psql_replication_check.pl = script name -h = hostname of database server -d = databse -p = database port -U = username