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