PostgreSQL agent script for CE

[lebogan]

Any plans on updating for psql v10+
Thanks

[theblujuice]

#!/usr/bin/env perl

PostgreSQL stats script for Observium

use warnings;
use strict;
use DBI;

--- Configuration ---

my $DEBUG = 1; # Enable debug output
my $confFile = 'postgresql.conf';

--- Variables ---

my ($drvName, $hostName, $dbUser, $dbPass, $dbName, $conn, $query, $all, $version, $cmd);
my $idle = 0;
my $select = 0;
my $update = 0;
my $delete = 0;
my $other = 0;
my $cCount = 0;
my (@ddbs, @Dusr, @dhst, @dup, @list);
my %seen;

--- Functions ---

Debugging and error handling

sub debug {
my $errMsg = shift;
print STDERR "[DEBUG] $errMsg\n" if $DEBUG;
exit(1) if $errMsg =~ /Error/; # Exit only on critical errors
}

Find duplicates in arrays

sub findDup {
@list = @;
%seen = ();
for (@list) { $seen{$}++ };
@dup = grep { $seen{$_} > 0 } keys %seen;
return @dup;
}

Execute an SQL command and return a single array result

sub sqlArray {
$cmd = shift;
debug("Running SQL (array): $cmd");
$query = $conn->prepare($cmd) or debug("Query preparation failed: $DBI::errstr");
$query->execute() or debug("Query execution failed: $DBI::errstr");
$all = $query->fetchrow_array();
return $all;
}

Execute an SQL command and return a hash reference

sub sqlHashRef {
$cmd = shift;
debug("Running SQL (hashref): $cmd");
$query = $conn->prepare($cmd) or debug("Query preparation failed: $DBI::errstr");
$query->execute() or debug("Query execution failed: $DBI::errstr");
$all = $query->fetchrow_hashref();
return $all;
}

--- Main Script ---

Load configuration from postgresql.conf

open(my $confFH, '<', $confFile) or debug("Error opening $confFile: $!");
while (<$confFH>) {
if (/^db_driver=(.?)$/) { $drvName = $1; }
if (/^db_host=(.?)$/) { $hostName = $1; }
if (/^db_user=(.?)$/) { $dbUser = $1; }
if (/^db_pass=(.?)$/) { $dbPass = $1; }
if (/^db_name=(.*?)$/) { $dbName = $1; }
}
close($confFH);

debug("Parsed Config: Driver=$drvName, Host=$hostName, User=$dbUser, DB=$dbName");

Connect to the PostgreSQL database

$conn = DBI->connect("DBI:$drvName:dbname=$dbName;host=$hostName", $dbUser, $dbPass, {
InactiveDestroy => 1,
PrintError => 0
}) or debug("Cannot connect to database: $dbName. Error: $DBI::errstr");

debug("Connected to database: $dbName");

Get PostgreSQL version

$cmd = "SELECT version()";
$all = sqlArray($cmd);
debug("Raw version string: $all");
$all =~ /PostgreSQL (\d+.\d+)/;
$version = $1;

Fallback if version is not detected

if (!$version) {
debug("Failed to detect PostgreSQL version. Output: $all");
$version = "unknown";
}

debug("PostgreSQL Version: $version");

Match major versions like 8.x, 9.x, 10.x through 14.x, ignoring minor versions

if ($version =~ /^([89]|1[0-4]).\d+/) {
$cmd = "SELECT datname, usename, client_addr, state FROM pg_stat_activity";
} else {
debug("Unsupported PostgreSQL version: $version");
}

Collect activity stats

$all = sqlHashRef($cmd);
while ($all = $query->fetchrow_hashref()) {
$cCount++;
push(@ddbs, $all->{datname}) if $all->{datname};
push(@Dusr, $all->{usename}) if $all->{usename};
push(@dhst, $all->{client_addr}) if $all->{client_addr};

if (defined $all->{state}) {
    if ($all->{state} eq 'idle') {
        $idle++;
    } elsif ($all->{state} eq 'active' && defined $all->{query}) {
        if ($all->{query} =~ /^select/i) {
            $select++;
        } elsif ($all->{query} =~ /^update/i) {
            $update++;
        } elsif ($all->{query} =~ /^delete/i) {
            $delete++;
        } else {
            $other++;
        }
    }
}

}

debug("Collected stats: Connections=$cCount, Idle=$idle, Select=$select, Update=$update, Delete=$delete, Other=$other");

Collect database stats

$cmd = "SELECT SUM(xact_commit) AS xact_commit, SUM(xact_rollback) AS xact_rollback, SUM(blks_read) AS blks_read,
SUM(blks_hit) AS blks_hit, SUM(tup_returned) AS tup_returned, SUM(tup_fetched) AS tup_fetched,
SUM(tup_inserted) AS tup_inserted, SUM(tup_updated) AS tup_updated, SUM(tup_deleted) AS tup_deleted
FROM pg_stat_database";
$all = sqlHashRef($cmd);

Clean up

$query->finish();
$conn->disconnect();

debug("Disconnected from database");

Print results

print "<<>>\n";
print "version:$version\n";
print "cCount:$cCount\n";
print "tDbs:" . scalar(findDup(@ddbs)) . "\n";
print "tUsr:" . scalar(findDup(@Dusr)) . "\n";
print "tHst:" . scalar(findDup(@dhst)) . "\n";
print "idle:$idle\n";
print "select:$select\n";
print "update:$update\n";
print "delete:$delete\n";
print "other:$other\n";
print "xact_commit:" . ($all->{xact_commit} // "0") . "\n";
print "xact_rollback:" . ($all->{xact_rollback} // "0") . "\n";
print "blks_read:" . ($all->{blks_read} // "0") . "\n";
print "blks_hit:" . ($all->{blks_hit} // "0") . "\n";
print "tup_returned:" . ($all->{tup_returned} // "0") . "\n";
print "tup_fetched:" . ($all->{tup_fetched} // "0") . "\n";
print "tup_inserted:" . ($all->{tup_inserted} // "0") . "\n";
print "tup_updated:" . ($all->{tup_updated} // "0") . "\n";
print "tup_deleted:" . ($all->{tup_deleted} // "0") . "\n";