PostgreSQL Monitoring through Nagios

Prerequisites :

Refer :  https://dptsource.blogspot.in/2017/03/install-and-configure-nagios-on-centos.html

Refer : http://dptsource.blogspot.in/2017/10/installing-postgresql-94-and-phppgadmin.html

1. Download and Install PostgreSQL Plugin 

Download and Install 

2. Setup the PostgreSQL user ‘nagios’ 

su - postgres
psql

SELECT usename FROM pg_user;

CREATE USER nagios WITH PASSWORD 'some_password';

ALTER USER nagios WITH SUPERUSER;

\du         # List the user and permissions 

\q
exit # back to root

3. Setup OS user nagios ( optional)

To keep the Postgres password from preying eyes, a password file is is required in the home directory of OS user “nagios”:

vi ~nagios/.pgpass
# insert:
localhost:5432:template1:nagios:some_password
# insert end (EOF)
# save and exit vi
chown nagios:nagios ~nagios/.pgpass
chmod 600 ~nagios/.pgpass

4. Test the connectivity locally 

$ check_postgres.pl --host=prodserver11 --port=5432 --dbuser=nagios --dbpass=some_password  --action=connection --db=pagila
$ check_postgres.pl --host=prodserver11 --port=5432 --dbuser=nagios --dbpass=some_password  --action dbstats   --db=pagila
$ check_postgres.pl --host=prodserver11 --port=5432 --dbuser=nagios --dbpass=some_password  --action dbstats   --db=postgres
$ check_postgres.pl --host=prodserver11 --port=5432 --dbuser=nagios --dbpass=some_password   --action=database_size --warning='30 GB' --critical='35 GB'
$ check_postgres.pl --host=prodserver11 --port=5432 --dbuser=nagios --dbpass=some_password   --action=checkpoint  --datadir /var/lib/pgsql/9.4/data --assume-prod
$ check_postgres.pl --host=prodserver11 --port=5432 --dbuser=nagios --dbpass=some_password   --action=version  --warning=9.1 --datadir=/var/lib/pgsql/9.4/data --assume-standby-mode
$ check_postgres_archive_ready --host=prodserver11 --port=5432 --dbuser=nagios --dbpass=some_password --critical=10
$ check_postgres.pl --host=prodserver11 --port=5432 --dbuser=nagios --dbpass=some_password   --action=archive_ready --critical=10
$ check_postgres.pl --host=prodserver11,prodserver22 --port=5432,5432 --dbuser=nagios --dbpass=some_password   --action=hot_standby_delay  --critical='10 min'

5. Enter the nagios user details in pg_hba.conf file 

host    all             nagios          192.168.202.131/32      trust
host    all             nagios          192.168.202.151/32      trust
host    all             nagios          192.168.202.128/32      trust

Since we are Configuring replication parameter add the nagios and host details in pg_hba file of slave server also

6. Append the following lines to nrpe configuration file 

vi /usr/local/nagios/etc/nrpe.cfg 

command[check_pg_connection]=/usr/local/nagios/libexec/check_postgres.pl --host=prodserver11 --port=5432 --dbuser=nagios --dbpass=some_password  --action=connection --db=pagila

7. Navigate to Nagios Server 

Check the connectivity using below command 

/usr/local/nagios/libexec/check_nrpe -H prodserver11 -c check_pg_connection

Now NRPE commands  definition needs to be created in commands.cfg file.

vi /usr/local/nagios/etc/objects/commands.cfg

############Postgresql commands #############

define command{
command_name check_pg_connection
command_line $USER1$/check_postgres.pl --host=$ARG1$ --port=$ARG2$ --dbuser=$ARG3$ --dbpass=$ARG4$ --action=$ARG5$ --db=$ARG6$
}

define command{
command_name check_pg_dbstats
command_line $USER1$/check_postgres.pl --host=$ARG1$ --port=$ARG2$ --dbuser=$ARG3$ --dbpass=$ARG4$ --action=$ARG5$ --db=$ARG6$
}


define command{
command_name hot_standby_delay
command_line $USER1$/check_postgres.pl --host=$ARG1$ --port=$ARG2$  --dbuser=$ARG3$ --dbpass=$ARG4$ --action=$ARG5$ --critical=$ARG7$
}

Configure Monitoring hosts and monitoring details  in clienthost.cfg 

vi /usr/local/nagios/etc/objects/clienthost.cfg

###PostgreSQL#######

define service{
        use generic-service
        host_name prodserver11
        service_description PostgreSQL database connection
        contact_groups                  dba-admins
        check_command check_pg_connection!prodserver11!5432!nagios!some_password!connection!pagila!
}

define service{
        use generic-service
        host_name prodserver11
        service_description PostgreSQL database stats
        contact_groups                  dba-admins
        check_command check_pg_dbstats!prodserver11!5432!nagios!some_password!dbstats!pagila!
}



define service{
        use generic-service
        host_name prodserver22
        service_description PostgreSQL database connection
        contact_groups                  dba-admins
        check_command check_pg_connection!prodserver22!5432!nagios!some_password!connection!pagila!
}

define service{
        use generic-service
        host_name prodserver22
        service_description PostgreSQL database stats
        contact_groups                  dba-admins
        check_command check_pg_dbstats!prodserver22!5432!nagios!some_password!dbstats!pagila!
}

define service{
        use generic-service
        host_name prodserver22
        service_description PostgreSQL Hot Standby Delay
        contact_groups                  dba-admins
        check_command hot_standby_delay!prodserver11,prodserver22!5432!nagios!some_password!hot_standby_delay!postgres!10!
}

Note :

If the system returns “ERROR: Could not find a suitable psql executable”, your Postgres installation may not use the default location. Normally this should be solved by setting the environment variable “PGBINDIR” to the path to psql, and adding this path to the PATH variable (e.g. in /etc/profile.d/), but for some reasons this doesn’t work. You have to modify “check_postgres.pl”

vi /usr/local/nagios/libexec/check_postgres.pl

… locate the line starting with “$PGBINDIR =”

… and replace it with

$PGBINDIR = ‘/usr/local/pgsql/bin’;

… or wherever your psql binary is installed.