PostgreSQL and Nagios (UPDATED)
The following describes how to setup PostgreSQL monitors using the check_postgres.pl script within Nagios. There is an excellent article on configuring network hosts and other important Nagios parameters in the August 2010 edition of BSD magazine. Links are available at the bottom of this post. The below examples are running on the following: Nagios 3.2.1, FreeBSD 8.1, and PostgreSQL 8.4.4
Nagios and nrpe configuration files for monitoring PostgreSQL databases are posted to the svn repo on this site. Also note, there are several other useful services that are defined - smartmon, for monitoring the SMART status of your drive(s) (including temperature). The PostgreSQL service definitions use the highly useful check_postgres script available on bucardo.org. All of this is running on FreeBSD 8.1 so you should be able to easily install everything from ports, except for check_postgres. Just drop the config files in /usr/local/etc/nagios, setup rc.conf for the nrpe daemon, config an apache vhost and you should be good to go. Run it on localhost and port forward in if you want to keep the web interface private. Also, you'll need to run check_postgres.pl --symlinks from /usr/local/libexec/nagios after creating a directory called postgres in that same location.
Services for the following:
*note: warn and crit thresholds will need modification according to your needs
- PostgreSQL Table Bloat
- PostgreSQL Index Bloat
- PostgreSQL Autovacuum Freeze
- PostgreSQL Backend Connections
- PostgreSQL Connection Test
- PostgreSQL Database stats
- PostgreSQL Database size(s)
- PostgreSQL Locks
- PostgreSQL Wal Files
- PostgreSQL Index Size
- PostgreSQL Last Analyze
- PostgreSQL Last Vacuum
- PostgreSQL Version
- PostgreSQL check_postgres Version
- PostgreSQL Age of Prepared Transactions
- PostgreSQL Length of Running Queries
- PostgreSQL Table Size for a Specified Table
- PostgreSQL Time Sync (local system time vs postgreSQL time)
- PostgreSQL Length of "Idle in Transaction" Queries
- PostgreSQL Length of Open Transactions
- PostgreSQL Transaction Wraparound Time
- Swap Usage
- Zombie Processes
- Total Processes
- Current Load
- PF States
- Disk Usage by partition
- Current Users
- ICMP
Configuration files for these services
Network monitoring with Nagios and OpenBSD
Another thing you can do is write custom queries using the custom_query action - for instance reading the pg stats tables to calculate cache hit ratios on your indexes and tables, setting thresholds on those ratios, and then feeding the perfdata to rrdtool for a nice graph.
If you find these useful and end up adding to them, feel free to drop me an e-mail with the changes and I'll push the updates to the svn repo so everyone can use. b r y a n ( a t ) b r y a n h i n t o n ( d o t ) c o m
