#! /usr/bin/perl -w use strict; use Getopt::Long; use vars qw($opt_V $opt_h $opt_D $opt_H1 $opt_H2 $opt_u1 $opt_u2 $opt_p1 $opt_p2 $opt_P1 $opt_P2 $opt_w $opt_c $opt_T $opt_C $opt_K $opt_I $PROGNAME); use lib "/usr/local/libexec/nagios" ; use utils qw(%ERRORS &print_revision &support &usage); use DBI; $PROGNAME = "check_postgres_repl_sorbs"; sub print_help (); sub print_usage (); $ENV{'PATH'}=''; $ENV{'BASH_ENV'}=''; $ENV{'ENV'}=''; Getopt::Long::Configure('bundling'); GetOptions ("V" => \$opt_V, "version" => \$opt_V, "h" => \$opt_h, "help" => \$opt_h, "D=s" => \$opt_D, "database=s" => \$opt_D, "M=s" => \$opt_H1, "hostname1=s" => \$opt_H1, "P=s" => \$opt_P1, "port1=s" => \$opt_P1, "U=s" => \$opt_u1, "username1=s" => \$opt_u1, "X=s" => \$opt_p1, "password1=s" => \$opt_p1, "m=s" => \$opt_H2, "hostname2=s" => \$opt_H2, "p=s" => \$opt_P2, "port2=s" => \$opt_P2, "u=s" => \$opt_u2, "username2=s" => \$opt_u2, "x=s" => \$opt_p2, "password2=s" => \$opt_p2, "c=s" => \$opt_c, "critical=s" => \$opt_c, "w=s" => \$opt_w, "warning=s" => \$opt_w, "T=s" => \$opt_T, "table=s" => \$opt_T, "C=s" => \$opt_C, "column=s" => \$opt_C, "K=s" => \$opt_K, "pkcol=s" => \$opt_K, "I=s" => \$opt_I, "key=s" => \$opt_I, ); if ($opt_V) { print_revision($PROGNAME,'$Revision: 3308 $'); exit $ERRORS{'OK'}; } if ($opt_h) {print_help(); exit $ERRORS{'OK'};} ($opt_H1) || usage("Host name/address not specified for master server\n"); my $host1 = $1 if ($opt_H1 =~ /([-.A-Za-z0-9]+)/); ($host1) || usage("Invalid host: $opt_H1\n"); ($opt_H2) || usage("Host name/address not specified for slave server\n"); my $host2 = $1 if ($opt_H2 =~ /([-.A-Za-z0-9]+)/); ($host2) || usage("Invalid host: $opt_H2\n"); ($opt_w) || usage("Warning threshold not specified\n"); my $warning = $1 if ($opt_w =~ /([0-9]{1,5}|100)+/); ($warning) || usage("Invalid warning threshold: $opt_w\n"); ($opt_c) || usage("Critical threshold not specified\n"); my $critical = $1 if ($opt_c =~ /([0-9]{1,5}|100)/); ($critical) || usage("Invalid critical threshold: $opt_c\n"); if (!defined($opt_D)) { $opt_D = $ENV{PGDB}; } my $database = $opt_D; if (!defined($opt_P1)) { $opt_P1 = "5432"; } my $port1 = $opt_P1; if (!defined($opt_P2)) { $opt_P2 = "5432"; } my $port2 = $opt_P2; my $username1 = $opt_u1; if (!defined($username1)) { $username1 = "pgsql"; } my $username2 = $opt_u2; if (!defined($username2)) { $username2 = "pgsql"; } my $password1 = $opt_p1; if (!defined($password1)) { $password1 = $ENV{PGPASS}; } my $password2 = $opt_p2; if (!defined($password2)) { $password2 = $ENV{PGPASS}; } my $table = $opt_T; if (!defined($table)) { $table = "bucardocheck"; } my $column = $opt_C; if (!defined($column)) { $column = "ts"; } my $pkcolumn = $opt_K; if (!defined($pkcolumn)) { $pkcolumn = "pk"; } my $key = $opt_I; if (!defined($key)) { $key = 0; } my $dbh1 = DBI->connect("dbi:Pg:dbname=$database;host=$host1",$username1,$password1, {RaiseError => 0}); if (!defined($dbh1)) { print "Unable to connect the master database [$host1/$database]!\n"; exit $ERRORS{'CRITICAL'}; } #$dbh1->do("SET TIMEZONE GMT;"); my $dbh2 = DBI->connect("dbi:Pg:dbname=$database;host=$host2",$username2,$password2, {RaiseError => 0}); if (!defined($dbh2)) { print "Unable to connect the slave database [$host2/$database]!\n"; exit $ERRORS{'CRITICAL'}; } #$dbh2->do("SET TIMEZONE GMT;"); my $sth1 = $dbh1->prepare("UPDATE $table SET $column = NOW() WHERE $pkcolumn = ?;"); my $sth1m = $dbh1->prepare("SELECT EXTRACT(EPOCH FROM $column)::INTEGER AS time FROM $table WHERE $pkcolumn = ? AND $column > NOW() - INTERVAL '15 minutes';"); my $sth2s = $dbh2->prepare("SELECT EXTRACT(EPOCH FROM $column)::INTEGER AS time FROM $table WHERE $pkcolumn = ?;"); if (!defined($sth1)) { print "Unabled to prepare UPDATE statement!\n"; exit $ERRORS{'CRITICAL'}; } if (!defined($sth1m)) { print "Unabled to prepare master SELECT statement!\n"; exit $ERRORS{'CRITICAL'}; } if (!defined($sth2s)) { print "Unabled to prepare slave SELECT statement!\n"; exit $ERRORS{'CRITICAL'}; } unless ($sth1m->execute($key)) { print "Unabled to execute master SELECT statement!\n"; exit $ERRORS{'CRITICAL'}; } unless ($sth2s->execute($key)) { print "Unabled to execute slave SELECT statement!\n"; exit $ERRORS{'CRITICAL'}; } unless ($sth1->execute($key)) { print "Unabled to execute UPDATE statement!\n"; exit $ERRORS{'CRITICAL'}; } my ($reftime, $slavetime) = (0, 0); unless ($sth1m->rows()) { print "Reference entry is too old or not present on $host1!\n"; exit $ERRORS{'CRITICAL'}; } else { while($_ = $sth1m->fetchrow_hashref()) { $reftime = $_->{$column}; } } unless ($sth2s->rows()) { print "Reference entry cannot be found on $host2!\n"; exit $ERRORS{'CRITICAL'}; } else { while($_ = $sth2s->fetchrow_hashref()) { $slavetime = $_->{$column}; } } $dbh1->disconnect(); $dbh2->disconnect(); my $lag = $reftime - $slavetime; print "Lag from $host1 to $host2: $lag seconds\n"; exit $ERRORS{'CRITICAL'} if ($lag > $critical); exit $ERRORS{'WARNING'} if ($lag > $warning); exit $ERRORS{'OK'}; sub print_usage () { print "Usage: $PROGNAME -H [-P ] [-u ] [-p ] -c -w \n"; } sub print_help () { print_revision($PROGNAME,'$Revision: 3308 $'); print "Copyright (c) 2011 Proofpoint by Michelle Sullivan This plugin reports the replication status in a postgres server. "; print_usage(); print " -D, --database=STRING Name of database to run the query against (default: sorbs) -M --hostname1=HOST Name or IP address of master host to check -m, --hostname2=HOST Name or IP address of slave host to check -P, --port1=INTEGER Port of the PostgreSQL server you are connecing to (default: 5432) -p, --port2=INTEGER Port of the PostgreSQL server you are connecing to (default: 5432) -U, --username1=STRING User name to use when connecting to the master PostgreSQL server (default: pgsql) -u, --username2=STRING User name to use when connecting to the slave PostgreSQL server (default: pgsql) -X, --password1=STRING Password to use when connecting to the master PostgreSQL server -x, --password2=STRING Password to use when connecting to the slave PostgreSQL server -T, --table=STRING Table name you want to check lag against (should have a primary key and timestamp column default: bucardocheck) -C, --column=STRING Column name that records the timestamps (should be timestamptz default: ts) -K, --pkcol=STRING Column name that is the primary key for the table (default: pk) -I, --key=STRING Index/Key of row that will be used for the replication check. (default: 0) -w, --warning=INTEGER Minimum seconds lag which a WARNING status will result -c, --critical=INTEGER Minimum seconds lag which a CRITICAL status will result "; support(); }