PostgreSQL Monitoring through Nagios

Nagios is a powerful monitoring system and here we will learn how to monitor PostgreSQL  through Nagios.


Servers :

 prodserver11            192.168.202.131/32    (Master)

 prodserver22           192.168.202.128/32    (Slave)

 nagiosserv                 192.168.202.151/32    (Nagios Server)

  

Prerequisites :

  • Create a Nagios Monitoring Server on CentOS 6.8  
  • Install a PostgreSQL database Installed, configured and located on a remote system on the same network as that of the Nagios Server.

1. Download and Install PostgreSQL Plugin 

Download and Install 

cd /tmp
wget http://bucardo.org/downloads/check_postgres-2.21.0.tar.gz
tar -xzf check_postgres*.tar.gz
cd check_postgres-*
cp check_postgres.pl /usr/local/nagios/libexec/

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

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

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!
}

For more details visit :

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: