check_pgsql - NRPE PostgreSQL check

By default NRPE includes a plugin (you need to enable it in the make config screen when installing net-mgmt/nagios-plugins) to check a PostgreSQL server. Follow the instructions on this page to enable it.

Configuring Nagios

Define a new service on the Nagios server, in /usr/local/etc/nagios/objects/services.cfg:

define service{
        use                             generic-service
        hostgroup_name                  postgresql-servers
        service_description             nrpe_check_pgsql
        check_command                   check_nrpe2!check_pgsql
}

The FreeBSD servers I monitor that has PostgreSQL installed are all members of the hostgroup postgresql-servers.

Configuring NRPE

Add the following line to /usr/local/etc/nrpe.cfg:

command[check_pgsql]=/usr/local/libexec/nagios/check_pgsql -H 10.16.255.65

Replacing 10.16.255.65 with the IP address the PostgreSQL server is listening on.

Remember to restart NRPE after changing the config:

sudo /usr/local/etc/rc.d/nrpe2 restart

Configuring PostgreSQL

The Nagios plugin needs to be able to connect to the database server. Test the plugin by running it from the Nagios server like so:

[tykling@nagios ~]$ /usr/local/libexec/nagios/check_nrpe2 -H dbserver.skabet.cn.dom -c check_pgsql
CRITICAL - no connection to 'template1' (FATAL:  no pg_hba.conf entry for host "10.16.255.65", user "nagios", database "template1", SSL off 
).

The error message from Postgres is pretty clear. I add the following line to the bottom of /usr/local/pgsql/data/pg_hba.conf:

host    template1   nagios      10.16.255.65/32       trust

... and restart Postgres:

sudo /usr/local/etc/rc.d/postgresql restart

Then I go add a nagios user to Postgres, this is done by the system pgsql user like all other Postgres management:

[tykling@dbserver ~]$ sudo su pgsql -c bash
[pgsql@dbserver /usr/home/tykling]$ cd
[pgsql@dbserver ~]$ createuser
Enter name of role to add: nagios
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
[pgsql@dbserver ~]$ exit
[tykling@dbserver ~]$

After doing this, the check works from the Nagios server:

[tykling@nagios ~]$ /usr/local/libexec/nagios/check_nrpe2 -H dbserver.skabet.cn.dom -c check_pgsql
 OK - database template1 (0 sec.)|time=0.000000s;2.000000;8.000000;0.000000
[tykling@nagios ~]$