Here's the nagios plugins I have hacked up over the past few months and what 
they do. Please let me know if you find these scripts useful. It may 
encourage me to work on them. :-)

Tony Wasson (ajwasson@gmail.com)

2006-08-29 - I've made all the scripts use the pg_stat_activity() function. 
This is using security definer, so you can run your monitoring as a 
non-super-user. See the INSTALL for extra details.


* check_pg_connections.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_pg_queries.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_pg_waiting_queries.pl - It whines if any queries are in the "waiting" 
state. It does this by joining pg_locks with pg_stat_activity(). 

* check_pg_lock_status.pl - Look for Exclusive locks that can block. This is 
just testing if a blocking/waiting condition can occur. pg_waiting_queries is 
actually reporting that a blocking/waiting state IS occuring.

* check_pg_time.pl - Makes sure that postgresql's time is in sync with the 
monitoring server. It also shows the version currently running.

* check_pg_max_xid.pl - "With the standard freezing policy, the age column will 
start at one billion for a freshly-vacuumed database." So essentially for any 
large or high transaction volume database, 1B is normal. The script warns 
before a wrap could happen at 2 billion transaction IDs. It starts warning once 
one or more databases show an age over 1.5 billion transactions. It reports 
critical at 1.75B transactions.

If you are using autovacuum on 8.1, you should not have any trouble with 
transaction ids. The code in 8.1 is also much better than 7.4 or 8.0 for dealing 
with a wraparound. You may choose not to use this check, depending on your 
comfort level.


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


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

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

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

#Find any queries waiting
define command{
        command_name    check_pg_waiting_queries
        command_line    $USER1$/check_pg_waiting_queries.pl $HOSTADDRESS$ template1 postgres
        }

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

#Check postgresql transaction IDs
define command{
        command_name    check_pg_transactionids
        command_line    $USER1$/check_pg_max_xid.pl $HOSTADDRESS$ template1 postgres
}