These are just some scripts I cooked up on my own. They work fine for me, your mileage may vary.

Please let me know if you find these script useful. It may encourage me to work on them. :-)

Tony Wasson (ajwasson@gmail.com)

check_pgconn.pl - Shows percentage of connections available. It uses
"SELECT COUNT(*) FROM pg_stat_activity" / "SHOW max_connections". It
can also alert when less than a certain number of connections are
available.

check_pgqueries.pl - If you have query logging enabled this summarizes
the types of queries running (SELECT ,INSERT ,DELETE ,UPDATE ,ALTER
,CREATE ,TRUNCATE, VACUUM, COPY) and warns if any queries have been
running longer than 5 minutes (configurable).

check_pglocks.pl - Look for locks that block and for baselining lock activity.

check_pgtime.pl - Makes sure that postgresql's time is in sync with
the monitoring server.

check_pgwaiting_queries.pl - Whines if any queries are in the "waiting" state.
Requires waiting-query-check installed on each DB server.

waiting-query-check - supporting script for check_pgwaiting_queries.pl. It needs to run 
on each postgresql database server.  It uses SNMP exec functionality. To use it put 
something like this in your DB server(s) /etc/snmpd.conf file:

  exec 1.3.6.1.4.1.2021.8.2.100 waiting-query-check /usr/local/bin/waiting-query-check

*****
Here's a sample for your checkcommands.cfg file in nagios


#compare time via postgresql
define command {
        command_name    check_pgtime
        command_line    $USER1$/check_pgtime.pl $HOSTADDRESS$ template1 postgres
}

#Get # of active connecctions
define command {
        command_name    check_pgconn
        command_line    $USER1$/check_pgconn.pl $HOSTADDRESS$ template1 postgres
}

#Get # of db locks
define command {
        command_name    check_pglocks
        command_line    $USER1$/check_pglocks.pl $HOSTADDRESS$ template1 postgres
}

#Find any queries waiting
define command{
        command_name    check_pgwaiting
        command_line    $USER1$/check_pgwaiting_queries.pl -H $HOSTADDRESS$
        }

#Find slow or abnormal queries running
define command{
        command_name    check_pgqueries
        command_line    $USER1$/check_pgqueries.pl $HOSTADDRESS$ template1 postgres
}