DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=uSmZ8B4m08yj2Ts1NZtwAIr9HBvNWT2dnnPtkM/+o1hXnWt8vJWcI+0JzkfXO1QV9VZEiVLr0FL267nCOh/t+IRKd3DfpCnZhUvEoNKFPwbvRZgqtqQ0JdcilvkvzV35H5AZPF00UEVsXQZLOrRUK927UbWWINArR1D9RN/Gv3o=
Message-ID: <6d8daee30605021126o7b44e255q552dcdc676c565bf@mail.gmail.com>
Date: Tue, 2 May 2006 11:26:04 -0700
From: "Tony Wasson" <ajwasson@gmail.com>
To: pgsql-performance@postgresql.org
Subject: postgresql transaction id monitoring with nagios
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_Part_7984_19334869.1146594364394"

------=_Part_7984_19334869.1146594364394
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Ever since I started working with PostgreSQL I've heard the need to
watch transaction IDs. The phrase "transaction ID wraparound" still
gives me a shiver. Attached it a short script that works with the
monitoring system Nagios to keep an eye on transaction IDs. It should
be easy to adapt to any other monitoring system.

It runs the textbook query below and reports how close you are to wraparoun=
d.
          SELECT datname, age(datfrozenxid) FROM pg_database;

The script detects a wrap at 2 billion. It starts warning once one or
more databases show an age over 1 billion transactions. It reports
critical at 1.5B transactions. I hope everyone out there is vacuuming
*all* databases often.

Hope some of you can use this script!
Tony Wasson

------=_Part_7984_19334869.1146594364394
Content-Type: application/x-perl; name=check_pg_transactionids.pl
Content-Transfer-Encoding: 7bit
X-Attachment-Id: f_emqkknl8
Content-Disposition: attachment; filename="check_pg_transactionids.pl"

#!/usr/bin/perl -w
use strict;
use DBI;

my $dbhost=$ARGV[0] || die "Pass the IP or hostname of a postgresql server to check.";
my $dbname=$ARGV[1] || 'template1';
my $dbuser=$ARGV[2] || 'postgres';
my $dbpass=$ARGV[3] || '';

# 2000M = 2 Billion. Too many zeros hurt my eyes.
my $wrap_xid_M=2000; # Record wrap at 2 Billion
my $warn_xid_M=1000; # Warn at 1.0 Billion
my $crit_xid_M=1500; # Critical at 1.5 Billion

my $status=3;        # default to an UNKNOWN status
my $max_xid_pct=-99; # default to a negative percentage
my $max_xid_M=-9999; # default to negative XID #
my $max_xid_datname="NOT SET (script broke)";

my $Con = "DBI:Pg:dbname=$dbname;host=$dbhost";
my $Dbh = DBI->connect($Con, $dbuser, $dbpass, {RaiseError =>1}) || die "Unable to access Database $dbname on host $dbhost as user $dbuser.\nError returned was: ". $DBI::errstr;

my $sql="SELECT datname, age(datfrozenxid) FROM pg_database;";
my $sth = $Dbh->prepare($sql);
$sth->execute();
while (my ($datname,$max_xid) = $sth->fetchrow()) {
  my $xid_used_M=sprintf('%1d',($max_xid/1000000));
  my $xid_used_pct=sprintf('%1d', ($xid_used_M/$wrap_xid_M)*100);
  #find the database with the highest transaction id
  if ($xid_used_M > $max_xid_M)
  {
    $max_xid_pct=$xid_used_pct;
    $max_xid_M=$xid_used_M;
    $max_xid_datname=$datname;
  }
}
$Dbh->disconnect;

# 3 UNKNOWN, 2 CRITICAL, 1 WARNING, 0 OK
if ($max_xid_M >= $crit_xid_M)
{
    $status=2;
}
elsif ($max_xid_M >= $warn_xid_M)
{
    $status=1;
}
elsif ($max_xid_M < $warn_xid_M && $max_xid_M > 0 )
{
    $status=0;
}
else
{
    #This means something is wrong with the query or math above
    $status=3;
}

print "Transaction IDs are $max_xid_pct% used ($max_xid_M M) in database $max_xid_datname.\n";
exit $status;


------=_Part_7984_19334869.1146594364394--