diff options
Diffstat (limited to 'files/plugins/pg__connections')
| -rwxr-xr-x | files/plugins/pg__connections | 142 |
1 files changed, 142 insertions, 0 deletions
diff --git a/files/plugins/pg__connections b/files/plugins/pg__connections new file mode 100755 index 0000000..ca95f56 --- /dev/null +++ b/files/plugins/pg__connections @@ -0,0 +1,142 @@ +#!/usr/bin/perl -w +# Plugin for monitor postgres connections. +# +# Licenced under GPL v2. +# +# Usage: +# +# Symlink into /etc/munin/plugins/ and add the monitored +# database to the filename. e.g.: +# +# ln -s /usr/share/munin/plugins/pg__connections \ +# /etc/munin/plugins/pg_<databasename>_connections +# This should, however, be given through autoconf and suggest. +# +# If required, give username, password and/or Postgresql server +# host through environment variables. +# +# You must also activate Postgresql statistics. See +# http://www.postgresql.org/docs/7.4/interactive/monitoring-stats.html +# for how to enable this. Specifically, the following lines must +# exist in your postgresql.conf: +# +# stats_start_collector = true +# stats_block_level = true +# +# +# Parameters: +# +# config (required) +# +# Config variables: +# +# dbhost - Which database server to use. Defaults to +# 'localhost'. +# dbname - Which database to use. Defaults to template1 +# dbuser - A Postgresql user account with read permission to +# the given database. Defaults to +# 'postgres'. Anyway, Munin must be told which user +# this plugin should be run as. +# dbpass - The corresponding password, if +# applicable. Default to undef. Remember that +# pg_hba.conf must be configured accordingly. +# +# Magic markers +#%# family=auto +#%# capabilities=autoconf + +use strict; +use DBI; + +my $dbhost = $ENV{'dbhost'} || '127.0.0.1'; +my $dbname = $ENV{'dbname'} || 'template1'; +my $dbuser = $ENV{'dbuser'} || 'postgres'; +my $dbuserx = $ENV{'dbuserx'} || ''; +my $dbport = $ENV{'dbport'} || '5432'; +my $dbpass = $ENV{'dbpass'} || ''; + +# Check for DBD::Pg +if (! eval "require DBD::Pg;") { + print "requires DBD::Pg\n"; + exit 1; +} + +my $dsn = "DBI:Pg:dbname=$dbname;host=$dbhost;port=$dbport"; +#print "$dsn\n"; +my $dbh = DBI->connect ($dsn, $dbuser, + $dbpass, + {RaiseError =>1}) || die ""; + + + +if (exists $ARGV[0]) { + if ($ARGV[0] eq 'autoconf') { + # Check for DBD::Pg + if (! eval "require DBD::Pg;") { + print "no (DBD::Pg not found)"; + exit 1; + } + if ($dbh) { + print "yes\n"; + exit 0; + } else { + print "no Unable to access Database $dbname on host $dbhost as user $dbuser.\nError returned was: ". $DBI::errstr; + exit 1; + } + } + + if ($ARGV[0] eq "config") { + my $sql_max = "SHOW max_connections;"; + my $sth_max = $dbh->prepare($sql_max); + $sth_max->execute(); + my ($max_connections) = $sth_max->fetchrow(); + my $warning = int ($max_connections * 0.7); + my $critical = int ($max_connections * 0.8); + print "graph_title PostgresSQL active connections\n"; + print "graph_args -l 0 --base 1000\n"; + print "graph_vlabel Connections\n"; + print "graph_category Postgresql\n"; + print "graph_info Shows active Postgresql connections from $dbname\n"; + + + my $sql = "select datname, count(*) from pg_stat_activity group by datname"; + my $sth = $dbh->prepare($sql); + $sth->execute(); + my $setarea = "yes"; + while ( my ($datname,$curr_conn) = $sth->fetchrow_array ) { + print "$datname.label $datname active connections\n"; + print "$datname.info $datname active connections\n"; + print "$datname.type GAUGE\n"; + if ($setarea eq "yes") { + print "$datname.draw AREA\n"; + $setarea=""; + } else { + print "$datname.draw STACK\n"; + } + } + + print "max_connections.label Max. connections\n"; + print "max_connections.info Max. connections\n"; + print "max_connections.type GAUGE\n"; + + print "warning $warning\n"; + print "critical $critical\n"; + exit 0; + } +} + + +# select datname, count(*) from pg_stat_activity group by datname +my $sql_max = "SHOW max_connections;"; +my $sth_max = $dbh->prepare($sql_max); +$sth_max->execute(); +my ($max_connections) = $sth_max->fetchrow(); + +#my $sql = "SELECT COUNT (*) FROM pg_stat_activity;"; +my $sql = "select datname, count(*) from pg_stat_activity group by datname"; +my $sth = $dbh->prepare($sql); +$sth->execute(); +while ( my ($datname,$curr_conn) = $sth->fetchrow_array ) { + print "$datname.value $curr_conn\n"; +} +print "max_connections.value $max_connections\n"; |