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$
}
EOFThen, 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.
