There are a couple of monitoring tools around these days making your daily life easier.
One of the most popular monitoring tools around is Nagios. It is widely used and supports a variety of software components.
To use Nagios to monitor your PostgreSQL cluster, it is necessary to install a plugin capable of running tests relevant to replication. Such plugins are also available for PostgreSQL and can be freely downloaded from http://bucardo.org/wiki/Check_postgres. The Burcardo plugins for Nagios are not just able to test replication but are also a standard software component to monitor PostgreSQL as a whole.
Once you have downloaded the plugin from the Bucardo website, it is easy to install the plugin. The first step is to extract the .tar archive:
tar xvfz check_postgres.tar.gz
Now you can enter the newly created directory and run the Perl Makefile:
perl Makefile.PL
Finally you can compile and install the code:
make make install
The last step must be performed as root user because otherwise you will most likely not have enough permissions to deploy the code on your system.
In our case the binaries have been installed at /usr/local/bin. We can easily check if the installation has been successful by running:
/usr/local/bin/check_postgres.pl --help
Starting check_postgres.pl directly is also the way to call those plugins from the command-line prompt and check if the results make sense.
We want to focus your attention on the custom_query functionality. If there are checks missing, which are needed but not available, custom_query will always be there to help you.
People often ask which of these countless Nagios checks that are available they should use to configure their database systems. For us, the answer to this question can only be: It depends. If you happen to run a large analysis database, which will only be used by a handful of people, checking for the number of open database connections might be of no use. If you happen to run a high-performance OLTP system serving thousands of users, checking for open connections might be a very good idea.
It really depends on the type of application you are running, so you have to think yourself and come up with a reasonable set of checks and thresholds. Logically the same applies to any other monitoring software you can potentially think of. The rules are always the same: Think about what your application does and consider things that can go wrong. Based on this information you can then select proper checks. A list of all available checks can be found at http://bucardo.org/check_postgres/check_postgres.pl.html.