Install OPM (Open PostgreSQL Monitoring)

Install OPM (Open PostgreSQL Monitoring) – A free software suite designed to help you manage your PostgreSQL servers. It’s a flexible tool that will follow the activity of each instance. It can gather stats, display dashboards and send warnings when something goes wrong. The long-term goal of the project is to provide similar features to those of Oracle Grid Control or SQL Server Management Studio.

To install OPM, you need

• a PostgreSQL 9.3+ cluster,
• standard compiling tools,
• nagios and
• pg_config

The PostgreSQL cluster and Nagios can be installed either on same server or different servers.

Prerequisites:

apt-get update

# Install required packages
apt-get -y install gcc make build-essential libgd2-xpm-dev openssl libssl-dev xinetd apache2-utils unzip

# Install Apache server
apt-get –y install apache2 apache2-utils

# Install PHP
apt-get –y install php5 libapache2-mod-php5 php5-mcrypt php5-curl

# Install PostgreSql
apt-get –y install postgresql postgresql-contrib postgresql-server-dev-all postgresql-common

# Install pg_config
apt-get –y install libpq-dev python-dev pgtune

apt-get update

Click here to see Nagios Installation Guide

Install OPM Core

Run below commands as root user to install and configure opm-core:

# Change directory to /usr/local/src
cd /usr/local/src

# Create new directory opm and enter into it
mkdir opm
cd opm

# Download & unzip OPM
wget https://github.com/OPMDG/opmdg.github.io/releases/download/REL_2_3/OPM_2_3.zip
unzip OPM_2_3.zip

# Rename the extracted directories
mv opm-core-REL_2_3 opm-core
mv opm-wh_nagios-REL_2_3 opm-wh_nagios
mv check_pgactivity-REL1_13 check_pgactivity

# Install opm-core
cd opm-core/pg
make install

# Change to postgres user & login to postgresql database
su postgres
psql -u postgres -p 5432

postgres@postgres=# CREATE DATABASE opm;
postgres@postgres=# \c opm
postgres@opm=# CREATE EXTENSION opm_core;
postgres@opm=# SELECT create_admin('admin1', 'admin1');
postgres@opm=# \q
postgres:/usr/local/src/opm# exit
root:/usr/local/src/opm#

admin1 is the user you will be using to log on to the OPM user interface.

wh_nagios

To install wh_nagios module run below commands as root user:

# change to appropriate directory
cd /usr/local/src/opm/opm-wh_nagios/pg
make install

# Change to postgres user & login to postgresql database
su postgres
psql -u postgres -p 5432
postgres@opm=# CREATE EXTENSION hstore;
postgres@opm=# CREATE EXTENSION wh_nagios;

Then, create a crontab that will process incoming data and dispatch them.

* * * * * psql -c 'SELECT wh_nagios.dispatch_record()' opm  # trigger it every minute

This crontab can belong to any user, as long as it can connect to the PostgreSQL opm database with any PostgreSQL role. To allow a PostgreSQL role to import data in a warehouse, you need to call public.grant_dispatch. For instance, if the PostgreSQL role is user1 and the warehouse is wh_nagios:

postgres@opm=# SELECT grant_dispatcher('wh_nagios', 'user1');

Nagios & nagios_dispatcher

The dispatcher nagios_dispatcher is aimed to dispatch perfdata from Nagios files to the wh_nagios warehouse. nagios_dispatcher requires the DBD::Pg perl module.

# Install DBD::Pg perl
# For Debian
apt-get install libdbd-pg-perl

# For CentOS
yum install perl-DBD-Pg

Now you need to setup Nagios to create its perfdata files that nagios_dispatcher will poll and consume. As user root, create the required command file and destination folder:

mkdir -p /var/lib/nagios4/spool/perfdata/
chown nagios:nagios /var/lib/nagios4/spool/perfdata/
chown -R nagios:nagios /var/lib/nagios4/
cat <<'EOF' >> /usr/local/nagios/etc/objects/commands.cfg
define command{
    command_name    process-service-perfdata-file
    command_line    /bin/mv /var/lib/nagios4/service-perfdata /var/lib/nagios4/spool/perfdata/service-perfdata.$TIMET$
}
define command{
    command_name    process-host-perfdata-file
    command_line    /bin/mv /var/lib/nagios4/host-perfdata /var/lib/nagios4/spool/perfdata/host-perfdata.$TIMET$
}
EOF

Then, in Nagios main configuration file (nagios.cfg), append following parameters accordingly:

process_performance_data=1
host_perfdata_file=/var/lib/nagios4/host-perfdata
service_perfdata_file=/var/lib/nagios4/service-perfdata
host_perfdata_file_processing_command=process-host-perfdata-file
service_perfdata_file_processing_command=process-service-perfdata-file
host_perfdata_file_template=DATATYPE::HOSTPERFDATA\tTIMET::$TIMET$\tHOSTNAME::$HOSTNAME$\tHOSTPERFDATA::$HOSTPERFDATA$\tHOSTCHECKCOMMAND::$HOSTCHECKCOMMAND$\tHOSTSTATE::$HOSTSTATE$\tHOSTSTATETYPE::$HOSTSTATETYPE$\tHOSTOUTPUT::$HOSTOUTPUT$
service_perfdata_file_template=DATATYPE::SERVICEPERFDATA\tTIMET::$TIMET$\tHOSTNAME::$HOSTNAME$\tSERVICEDESC::$SERVICEDESC$\tSERVICEPERFDATA::$SERVICEPERFDATA$\tSERVICECHECKCOMMAND::$SERVICECHECKCOMMAND$\tHOSTSTATE::$HOSTSTATE$\tHOSTSTATETYPE::$HOSTSTATETYPE$\tSERVICESTATE::$SERVICESTATE$\tSERVICESTATETYPE::$SERVICESTATETYPE$\tSERVICEOUTPUT::$SERVICEOUTPUT$
host_perfdata_file_mode=a
service_perfdata_file_mode=a
host_perfdata_file_processing_interval=15
service_perfdata_file_processing_interval=15

and then create the dispatcher configuration file:

cd
mkdir -p /usr/local/etc/
cat <<EOF > /usr/local/etc/nagios_dispatcher.conf
daemon=1
directory=/var/lib/nagios4/spool/perfdata/
frequency=5
db_connection_string=dbi:Pg:dbname=opm host=127.0.0.1
db_user=YOUR_USER
db_password=YOUR_PASS
debug=0
syslog=1
hostname_filter = /^$/ # Empty hostname. Never happens
service_filter = /^$/ # Empty service
label_filter = /^$/ # Empty label
EOF

# Change the ownership of nagios_dispatcher file to nagios
chown nagios:nagios /usr/local/etc/nagios_dispatcher.conf

# Install the nagios_dispatcher.pl file into the /usr/local/bin/ directory:
cp /usr/local/src/opm/opm-wh_nagios/bin/nagios_dispatcher.pl /usr/local/bin/

Now, If your operating system uses inittab , add following line at the end of the /etc/inittab file:

d1:23:respawn:/usr/bin/perl -w /usr/local/bin/nagios_dispatcher.pl --daemon --config /usr/local/etc/nagios_dispatcher.conf

Reload the /etc/inittab file:

init q

And, if your operating system uses upstart , create the file /etc/init/nagios_dispatcher.conf, with following content:

# This service maintains nagios_dispatcher

start on stopped rc RUNLEVEL=[2345]
stop on starting runlevel [016]

respawn
exec /usr/local/bin/nagios_dispatcher.pl -c /usr/local/etc/nagios_dispatcher.conf

And start the job:

initctl start nagios_dispatcher

User interface

The default user interface is based on the web framework Mojolicious. You need to install:

• Perl (5.10 or above)
• Mojolicious (4.63 or above, less than 5.0)
• Mojolicious::Plugin::I18N (version 0.9)
• DBD::Pg perl module
• PostgreSQL (9.3 or above)
• A CGI/Perl webserver

You can install Mojolicious using CPAN or your Linux distribution package system if available. Here is an example with CPAN:

curl -L cpanmin.us | perl - Mojolicious@4.99
curl -L cpanmin.us | perl - Mojolicious::Plugin::I18N@0.9
curl -L cpanmin.us | perl - DBI
curl -L cpanmin.us | perl - DBD::Pg

To install the UI plugin wh_nagios (or any other UI plugin), from your opm directory as user root:

cd /usr/local/src/opm/opm-core/ui/modules
ln -s /usr/local/src/opm/opm-wh_nagios/ui wh_nagios

Then, on OPM database side, you need to create an opm user for the UI:

postgres@opm=# CREATE USER opmui WITH ENCRYPTED PASSWORD 'opmui';
postgres@opm=# SELECT * from grant_appli('opmui');

Finally, in the directory /usr/local/src/opm/opm-core/ui, copy the opm.conf-dist file to opm.conf, and edit it to suit our needs, for instance:

{
    ...
    "database" : {
        "dbname"   : "opm",
        "host"     : "127.0.0.1",
        "port"     : "5432",
        "user"     : "opmui",
        "password" : "opmui"
    },
    ...
    "plugins" : [ "wh_nagios" ]
}

This user is only needed for the connection between the UI and the database. You only have to use it in the opm.conf file

To test the web user interface quickly, you can use either morbo or hypnotoad, both installed with Mojolicious. Example with Morbo:

cd /usr/local/src/opm/opm-core/ui
morbo script/opm
[Sun Jan 31 13:45:41 2016] [debug] Reading configuration file "/usr/local/src/opm/opm-core/ui/opm.conf".
[Sun Jan 31 13:45:41 2016] [debug] Helper "url_for" already exists, replacing.
[Sun Jan 31 13:45:41 2016] [info] Listening at "http://*:3000".
Server available at http://127.0.0.1:3000.

Using hypnotoad, which suit better for production:

user:/usr/local/src/opm/ui/opm/opm-core$ hypnotoad -f script/opm

Removing -f”/code> makes it daemonize.

Configure PostgreSql services with Nagios:

cd ~
wget http://bucardo.org/downloads/check_postgres.tar.gz
cp check_postgres.pl /usr/local/nagios/libexec/
cd /usr/local/nagios/libexec/
perl ../check_postgres.pl --symlinks

cd /usr/local/nagios/etc/objects

vi pgcommands.cfg # add the postgresql commands 
vi pgservices.cfg # add the postgresql services

vi /usr/local/nagios/etc/nagios.cfg # include the path of above 2 files.

Sample Postgresql command:

define command {
command_name check_edb_bloat
command_line $USER1$/check_postgres.pl --host $HOSTADDRESS$ --dbuser=pgmonitor--dbpass=password -db postgres -p 5444 --action bloat
}

Sample Postgresql Service:

define service {
    use                     generic-service
    host_name               localhost
    service_description     Postgres bloat
    is_volatile             0
    check_period            24x7
    max_check_attempts      3
    normal_check_interval   5
    retry_check_interval    1
    contact_groups          admins
    notification_interval   120
    notification_period     24x7
    notification_options
    check_command           check_edb_bloat!3000000!9000000!flr
}

Note: OPM heavily depends on Nagios so you ought to be comfortable with nagios services and commands. To monitor postgreSql database, you need to install and configure check_postgres plugin from bucardo.

Reference: NRPE_PostgreSQL_check
monitor postgresql with nagios
Install and configure nagios for postgresql PPAS on linux

Your comments/suggestions will be highly appreciated.