Our friends at Bucardo created a useful, general purpose PostgreSQL checking utility. The check_postgres
tool currently has an inventory of more than 50 checks to monitor PostgreSQL servers.
While this is an exceptionally useful tool, integrating it into our overall stack is necessary to fully take advantage of its capabilities. This recipe will cover the basic usage and integration with Nagios for easy PostgreSQL monitoring of large database clusters.
Though some Linux distributions package the check_postgres
utility for easy installation, the versions that are included are usually very old. We recommend that you obtain a copy of the latest check_postgres
source code. At the time of writing this book, the latest version is 2.21.0, released on September 24, 2013. Obtain the latest copy of the check_postgres
source code from http://bucardo.org/wiki/Check_postgres.
As we want to use Nagios to execute the check_postgres
, please follow the steps in the Configuring Nagios to monitor a database host recipe to produce a working installation with a basic database host configuration. We will be making further modifications to the db_conf.cfg
file introduced there.
Install check_postgres
by following these steps:
check_postgres
source and enter the source directory:tar -xzf check_postgres-2.21.0.tar.gz cd check_postgres-2.21.0/
perl Makefile.PL make sudo make install
As the postgres
user on a PostgreSQL server, try using these commands to obtain database information:
check_postgres.pl --action=database_size -w 100MB -c 200MB
postgres
user in the postgres
database:CREATE TABLE bigtable AS SELECT generate_series(1,1000000) AS vals;
check_postgres.pl --action=table_size -w 10MB -c 20MB
Integrate check_postgres.pl
into Nagios by following these steps:
command
section in the db_conf.cfg
file with this content:define command { command_name check_pg command_line /usr/local/bin/check_postgres.pl -H $HOSTADDRESS$ --action $ARG1$ -w $ARG2$ -c $ARG3$ }
service
section in the db_conf.cfg
file that looks like this:define service { use generic-service hostgroup_name pg-servers service_description PostgreSQL Database Size check_command check_pg!database_size!100MB!200MB }
sudo service nagios3 reload
sudo service nagios reload
This recipe comes in three parts because we're doing three distinctly different things. Installing check_postgres
itself is actually very easy. The entirety of the utility is contained within a single file, so we can simply move check_postgres.pl
to a suitable location in our PATH
environment setting. However, we suggest that you use the standard installation process as we did.
Next, we try a couple of basic commands to ensure that check_postgres
works. The first command makes use of the database_size
action and alerts us if our database is larger than the warning (-w
) or critical (-c
) thresholds that we set. The table_size
action performs a similar task but applies the thresholds to every table in the database. By default, check_postgres
connects to the postgres
database, so we placed a large table there to trigger a critical alert. The output is very large as it lists every table, but it should begin like this:
POSTGRES_TABLE_SIZE CRITICAL: DB "postgres" (host:192.168.56.10) largest table is "public.bigtable": 35 MB
As we have verified that the check works, we want Nagios to invoke it instead. This removes the need to create ad hoc invocations and allows us to search for large tables on all the database servers that Nagios is monitoring.
We will start the process by adding a command to Nagios in the db_conf.cfg
file we created for our single test server. Remember where check_postgres.pl
was installed, because we need to specify the full path to the script, just in case it's not part of the standard PATH
environment. We will set the first argument to set the action we want to perform and reserve the second and third for the warning and critical levels respectively. By making our check_pg
command so generic, we can use it for every action that check_postgres
supports. Otherwise, we would have needed a separate command
section for each check.
Then, we will add a service
check. We will need to add one of these for each check_postgres
action that we want to enact. In our example, we only enabled the database_size
check and applied the same thresholds that we used when manually invoking the script. By reloading the Nagios configuration files, it will incorporate the new PostgreSQL database size check and apply it to any server that we have in the pg-servers
group.
Though the documentation explains all the actions available for check_postgres
, it may be inconvenient to refer to it regularly. Though the check_postgres.pl
script accepts the usual --help
parameter, it has a notable ability as well. If we specify the --man
parameter instead, check_postgres
will actually display the entire manual. This is similar to investigating the check_postgres
man page like this:
man check_postgres
Sometimes, man pages don't get installed properly or are not available for one reason or another. The --man
parameter should always work on any system that also contains the perl
documentation package.
As check_postgres
is developed by Bucardo, their site contains various resources related to its operation. We recommend these links for more information:
18.118.0.248