Monitor Postgres Table Activity with check_postgres.pl

Its fairly straight forward to monitor a postgresql table’s activity with the wonderful script check_postgres.pl created by Greg Sabino Mullane. This script is useful for so many things I will surely be writing about other ways to use this script with Nagios and Cacti.

1. Download Script: Download the check_postgres.pl script here.
2. Install:place the check_postgres.pl script in /usr/local/nagios/libexec or your Nagios libexec folder.
3. Create Nagios Command:You will need to create a Nagios command to run your custom query. In the example below many variables are configurable such as changing the amount of time there is activity in the table, etc. It is actually recommended to use a .pgpass file for storing your postgres username and password for the below script. You can read about doing so in the check_postgres.pl documentation here. Make sure to replace all of the items below which inclued PASSWORD, DATABASE_NAME, and TABLENAME with your information. Add the below code to your check_commands.cfg file located in your Nagios etc directory.
bash


1. define command {
2. command_name check_pgsql_table_activity
3. command_line $USER1$/check_postgres.pl --host $HOSTADDRESS$ --dbuser=postgres --dbpass=PASSWORD -db DATABASE_NAME --action custom_query --critical=1 --query="SELECT COUNT(*) AS count FROM TABLENAME WHERE timestamp_server > NOW() - INTERVAL '15 minutes'" --reverse
4. }

The above code will select everything from a postgres table but only return the number of rows that are less than 15 minutes old. This allows you to warn if there hasn’t been activity in a table for more than 15 minutes.

4. Create Nagios Service:Attach the command using service to a host. In the below command you will want to replace SERVER_HOST with your servers hostname. More than likely you will also want to add the contact_groups statement below so you are notified when the postgres table doesn’t have any activity. Add the below to your services.cfg file located in the Nagios etc directory.
bash


1. define service {
2. service_description postgres_check_table_activity
3. use generic-service
4. host_name SERVER_HOST
5. check_command check_pgsql_table_activity
6. }
5. Check Nagios Config:Check your Nagios configuration by issuing the below command from your Linux shell.
bash


1. [root@server etc]#/usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg
6. Restart Nagios:Once all of your additions are completed restart Nagios by issuing the below command.
bash


1. [root@server etc]#/etc/init.d/nagios restart

I definitely recommend using Monarch for Nagios configuration file management. You can read all about Monarch in an article we published. Also remember that check_postgres.pl can be used for so many things that reading all of the documentationprovided with it will really be beneficial.