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";