db-pgstat.pl

#!/bin/env perl

# This program was designed to work with Zabbix to keep monitored PostgreSQL databases.
# Copyright (C) 2012 Wilmer Jaramillo M. <wilmer@fedoraproject.org>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>

use strict;
use DBI;
use Getopt::Long;

my ($host, $database, $user, $password);

my %queries = (
    'size'                     => qq{SELECT SUM(pg_database_size(datid)) as total_size from pg_stat_database},
    'threads'                  => qq{SELECT COUNT(*) FROM pg_stat_activity},
    'activeconn'               => qq{SELECT SUM(numbackends) FROM pg_stat_database},
    'tupreturned'              => qq{SELECT SUM(tup_returned) FROM pg_stat_database},
    'tupfetched'               => qq{SELECT SUM(tup_fetched) FROM pg_stat_database},
    'tupinserted'              => qq{SELECT SUM(tup_inserted) FROM pg_stat_database},
    'tupupdated'               => qq{SELECT SUM(tup_updated) FROM pg_stat_database},
    'tupdeleted'               => qq{SELECT SUM(tup_deleted) FROM pg_stat_database},
    'xactcommit'               => qq{SELECT SUM(xact_commit) FROM pg_stat_database},
    'xactrollback'             => qq{SELECT SUM(xact_rollback) FROM pg_stat_database},
    'exclusivelock'            => qq{SELECT COUNT(*) FROM pg_locks WHERE mode='ExclusiveLock'},
    'accessexclusivelock'      => qq{SELECT COUNT(*) FROM pg_locks WHERE mode='AccessExclusiveLock'},
    'accesssharelock'          => qq{SELECT COUNT(*) FROM pg_locks WHERE mode='AccessShareLock'},
    'rowsharelock'             => qq{SELECT COUNT(*) FROM pg_locks WHERE mode='RowShareLock'},
    'rowexclusivelock'         => qq{SELECT COUNT(*) FROM pg_locks WHERE mode='RowExclusiveLock'},
    'shareupdateexclusivelock' => qq{SELECT COUNT(*) FROM pg_locks WHERE mode='ShareUpdateExclusiveLock'},
    'sharerowexclusivelock'    => qq{SELECT COUNT(*) FROM pg_locks WHERE mode='ShareRowExclusiveLock'},
    'checkpoints_timed'        => qq{SELECT checkpoints_timed FROM pg_stat_bgwriter},
    'checkpoints_req'          => qq{SELECT checkpoints_req FROM pg_stat_bgwriter},
    'buffers_checkpoint'       => qq{SELECT buffers_checkpoint FROM pg_stat_bgwriter},
    'buffers_clean'            => qq{SELECT buffers_clean FROM pg_stat_bgwriter},
    'maxwritten_clean'         => qq{SELECT maxwritten_clean FROM pg_stat_bgwriter},
    'buffers_backend'          => qq{SELECT buffers_backend FROM pg_stat_bgwriter},
    'buffers_alloc'            => qq{SELECT buffers_alloc FROM pg_stat_bgwriter}
);

GetOptions(
    'host=s'                   => \$host,
    'database=s'               => \$database,
    'user=s'                   => \$user,
    'password=s'               => \$password,
    'size'                     => sub { print query_database($queries{size}) },
    'threads'                  => sub { print query_database($queries{threads}) },
    'activeconn'               => sub { print query_database($queries{activeconn}) },
    'tupreturned'              => sub { print query_database($queries{tupreturned}) },
    'tupfetched'               => sub { print query_database($queries{tupfetched}) },
    'tupinserted'              => sub { print query_database($queries{tupinserted}) },
    'tupupdated'               => sub { print query_database($queries{tupupdated}) },
    'tupdeleted'               => sub { print query_database($queries{tupdeleted}) },
    'xactcommit'               => sub { print query_database($queries{xactcommit}) },
    'xactrollback'             => sub { print query_database($queries{xactrollback}) },
    'exclusivelock'            => sub { print query_database($queries{exclusivelock}) },
    'accessexclusivelock'      => sub { print query_database($queries{accessexclusivelock}) },
    'accesssharelock'          => sub { print query_database($queries{accesssharelockery}) },
    'rowsharelock'             => sub { print query_database($queries{rowsharelock}) },
    'rowexclusivelock'         => sub { print query_database($queries{xactcommit}) },
    'shareupdateexclusivelock' => sub { print query_database($queries{shareupdateexclusivelock}) },
    'sharerowexclusivelock'    => sub { print query_database($queries{sharerowexclusivelock}) },
    'checkpoints_timed'        => sub { print query_database($queries{checkpoints_timed}) },
    'checkpoints_req'          => sub { print query_database($queries{checkpoints_req}) },
    'buffers_checkpoint'       => sub { print query_database($queries{buffers_checkpoint}) },
    'buffers_clean'            => sub { print query_database($queries{buffers_clean}) },
    'maxwritten_clean'         => sub { print query_database($queries{maxwritten_clean}) },
    'buffers_backend'          => sub { print query_database($queries{buffers_backend}) },
    'buffers_alloc'            => sub { print query_database($queries{buffers_alloc}) }
);

sub query_database {
    my $query = shift(@_);
    my $dbh = DBI->connect("dbi:Pg:dbname=$database; host=$host", $user, $password);
    my $sth = $dbh->prepare($query) or die $|;
    $sth->execute;

    while (my @array = $sth->fetchrow_array)  {
        return @array[0];
    }
    $sth->disconnect
}