User Tools

Site Tools


scripts:perl:check-database-replication-psql-9.1-zenoss.pl
check-database-replication-psql-9.1-zenoss.pl
#!/usr/bin/perl
# Written by Thomas York
#
# This plugin is for Zenoss, even though it's written in Nagios format. DO NOT USE WITH NAGIOS
# This script isn't modular. You need a seperate copy for every master/slave pair you wish to check
#
# This script compares the streaming replication in Postgres 9.1 between master/slave
 
# Use
# Use
use strict;
use warnings;
use DBI;
use bignum; # This is needed for xlog_location_numeric, as it can return a HUGE integer (theoretically)
 
# Configuration
my $MASTERADDR	=	"master.domain.tld";
my $SLAVEADDR	=	"slave.domain.tld";
my $PSQLUSER	=	"postgres";
my $PSQLPW	=	"zomgpassword";
 
 
# Main program execution
 
# Start up Postgres connections
my $dbhMaster = DBI->connect("DBI:Pg:dbname=postgres;host=" . $MASTERADDR, $PSQLUSER, $PSQLPW) or die ("Could not connect to " . $MASTERADDR . " : " . $DBI::errstr . "\n");
my $dbhSlave = DBI->connect("DBI:Pg:dbname=postgres;host=" . $SLAVEADDR, $PSQLUSER, $PSQLPW) or die ("Could not connect to " . $MASTERADDR . " : " . $DBI::errstr . "\n");
 
# Prep SQL
my $sthMasterPosition = $dbhMaster->prepare("SELECT xlog_location_numeric();");
my $sthSlavePosition = $dbhSlave->prepare("SELECT xlog_location_numeric();");
 
# Get positions from master and slave
$sthMasterPosition->execute;
$sthSlavePosition->execute;
 
# Retrieve data from queries
my $MasterPosition = $sthMasterPosition->fetchrow_array();
my $SlavePosition = $sthSlavePosition->fetchrow_array();
 
# Release queries
$sthMasterPosition->finish;
$sthSlavePosition->finish;
 
# Compare values
if ($MasterPosition == $SlavePosition) { # They match!
	print "REPLICATION OK|backlog=0\n";
} else {
	print "REPLICATION WARNING - " . ($MasterPosition - $SlavePosition) . " records backlogged!|backlog=" . ($MasterPosition - $SlavePosition) . "\n";
}
 
# Close Postgres connections
$dbhMaster->disconnect;
$dbhSlave->disconnect;
 
exit 0;
/usr/local/www/vhost/www.fuhell.com/data/pages/scripts/perl/check-database-replication-psql-9.1-zenoss.pl.txt · Last modified: 2014/09/01 15:00 (external edit)