PostgreSQL transaction stats to MRTG

The following perl fragment does "SELECT sum(xact_commit) FROM pg_stat_database;" and returns four lines suitable for use with mrtg (data1, data2, uptime, hostname):


0
12765

db1.example.tld


We don't have access to uptime, so we ignore it. The transaction count is in the second data field for purely aesthetic reasons.

Try that the script works before configuring mrtg:


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

my $dbhost='db1.example.tld';
my $dbname='template1';
my $dbuser='monitor';
my $dbpass='secretword';

my $connstring = "DBI:Pg:dbname=$dbname;host=$dbhost";
my $connection = DBI->connect($connstring, $dbuser, $dbpass, {RaiseError => 0, PrintError => 0}) || die "Unable to access database: ". $DBI::errstr ."";

my $query = "select sum(xact_commit) + sum(xact_rollback) from pg_stat_database;";
my $stmt = $connection->prepare($query);
$stmt->execute() || die "Running query failed: ". $DBI::errstr ."";
my $row = $stmt->fetchrow();

print "0\n";
print $row . "\n";
print "\n";
print $dbhost . "\n";


I use the following MRTG fragment:

Target[sql.xacts]: `/etc/mrtg-sql-tps.pl`
MaxBytes[sql.xacts]: 1250000
Title[sql.xacts]: PostgreSQL transactions per second
PageTop[sql.xacts]: PostgreSQL transactions per second
YLegend[sql.xacts]: Xacts / second
ShortLegend[sql.xacts]: xact/s
LegendO[sql.xacts]: Transactions per second
Options[sql.xacts]: integer,nopercent,noinfo,nobanner,noi


noi option causes mrtg to ignore the first data field (Input) and LegendO sets description for the second data field we use (Output for MRTG).

Remeber to make a new index page with indexmaker.