PostgreSQL DB / EDB Monitoring

Skip Prerequisites, Web and LPAR2RRD tabs in case of configuring Virtual Appliance, Docker or a Container

HW sizing


Consider usage of our brand new full-stack infrastructure monitoring tool XorMon Next Generation as LPAR2RRD replacement.
It brings a new level of infrastructure monitoring by relying on a modern technology stack.
In particular, reporting, exporting, alerting and presentation capabilities are unique on the market.

Follow installation procedure for your operating system platform
LPAR2RRD obtains data with via ODBC (perl-DBI and perl-DBD-Pg modules).

Prerequisites

  • Add this line to pg_hba.conf on PostgreSQL host:
    host   all   lpar2rrd   monitoring machine IP/hostname   md5
    
    Running this command on your DB can help you locate pg_hba.conf
    SHOW hba_file;
    
    After you complete this step restart the PostgresSQL service

  • Make sure you have installed perl modules: perl-DBI and perl-DBD-Pg on LPAR2RRD server
    $ rpm -q perl-DBI perl-DBD-Pg
      perl-DBI-1.627-4.el7.x86_64
      perl-DBD-Pg-2.19.3-4.el7.x86_64
    
    Install them if they are missing under root user
    • Linux RedHat, CentOS
      # yum install perl-DBD-Pg
      
    • Linux Debian, Ubuntu
      # apt-get install libdbd-pg-perl
      
    • AIX

    Connectivity check

      Allow access from the LPAR2RRD host to PostgreSQL host on port 5432
      Check connectivity:
      $ perl /home/lpar2rrd/lpar2rrd/bin/conntest.pl 192.168.1.1 5432
        Connection to "192.168.1.1" on port "5432" is ok
      

    Create DB user for LPAR2RRD

      Create database user ("lpar2rrd" in this example) with these privileges on each database on host machine.
      CREATE ROLE lpar2rrd WITH PASSWORD 'your_passw0rd' LOGIN;
      GRANT SELECT ON TABLE pg_stat_bgwriter TO lpar2rrd;
      GRANT SELECT ON TABLE pg_stat_database TO lpar2rrd;
      GRANT SELECT ON TABLE pg_stat_activity TO lpar2rrd;
      GRANT SELECT ON TABLE pg_locks TO lpar2rrd;
      GRANT SELECT ON TABLE pg_cursors TO lpar2rrd;
      GRANT SELECT ON TABLE pg_stat_progress_vacuum TO lpar2rrd;
      GRANT SELECT ON TABLE pg_stat_replication TO lpar2rrd;
      GRANT SELECT ON TABLE pg_stat_wal_receiver TO lpar2rrd;
      

    Attach PostgreSQL

    • As PostgreSQL gives us info about every DB on host machine we suggest that you use the machine hostname as "Host alias" and choose one DB for initial connect. LPAR2RRD UI ➡ Settings icon ➡ PostgreSQL DB ➡ New :
      PostgreSQL DB configuration
      In case of error: SCRAM authentication requires libpq version 10 or above
      Either upgrade libpq to version 10+ or change users password authentication to md5 and generate new password

    • Make sure cron job for PostgreSQL is in place (upgrade script might do it for you automatically)
      Skip this step if you install the Virtual Appliance - it is already taken care of.
      $ crontab -l | grep "load_postgres.sh"
      $
      
      Add following lines to crontab if necessary
      $ crontab -e
      
      # PostgreSQL support
      0,5,10,15,20,25,30,35,40,45,50,55 * * * *  /home/lpar2rrd/lpar2rrd/load_postgres.sh > /home/lpar2rrd/lpar2rrd/load_postgres.out 2>&1
      
    • Wait 30 minutes and then go to the web UI: http://<your web server>/lpar2rrd/
      Use Ctrl-F5 to refresh the web browser cache.


Install LPAR2RRD server (all under lpar2rrd user)

  • Download the latest LPAR2RRD server
    Upgrade your already running LPAR2RRD instance.

  • Install it:
    # su - lpar2rrd
    $ tar xvf lpar2rrd-7.XX.tar
    $ cd lpar2rrd-7.XX
    $ ./install.sh
    $ cd /home/lpar2rrd/lpar2rrd
    
  • Make sure all Perl modules are in place
    cd /home/lpar2rrd/lpar2rrd
    . etc/lpar2rrd.cfg; $PERL bin/perl_modules_check.pl
    
    If there is missing "LWP::Protocol::https" then check this docu to fix it

  • Enable Apache authorisation
    su - lpar2rrd
    umask 022
    cd /home/lpar2rrd/lpar2rrd
    cp html/.htaccess www
    cp html/.htaccess lpar2rrd-cgi
    
  • Schedule to run it from lpar2rrd crontab (it might already exist there)
    $ crontab -l | grep load.sh
    $
    
    Add if it does not exist as above
    $ crontab -e
    
    # LPAR2RRD UI
    0,30 * * * * /home/lpar2rrd/lpar2rrd/load.sh > /home/lpar2rrd/lpar2rrd/load.out 2>&1 
    
    Assure there is just one such entry in crontab.

  • You might need to add lpar2rrd user into /etc/cron.allow (Linux) or /var/adm/cron/cron.allow (AIX) if 'crontab -e' command fails
    Allow it for lpar2rrd user as root user.
    # echo "lpar2rrd" >> /etc/cron.allow
    
  • Assure you have a cron jobs for Oracle DB in place (upgrade script might do it automatically) Skip it on the Virtual Appliance, it is already there.
    $ crontab -l | grep "load_postgres.sh"
    $
    
    Add it if it does not exist like above
    $ crontab -e
    
    # PostgreSQL support
    0,5,10,15,20,25,30,35,40,45,50,55 * * * *  /home/lpar2rrd/lpar2rrd/load_postgres.sh > /home/lpar2rrd/lpar2rrd/load_postgres.out 2>&1
    
  • Initial start from cmd line:
    $ cd /home/lpar2rrd/lpar2rrd
    $ ./load.sh
    
  • Go to the web UI: http://<your web server>/lpar2rrd/
    Use Ctrl-F5 to refresh the web browser cache.

Troubleshooting

  • If you have any problems with the UI then check:
    (note that the path to Apache logs might be different, search apache logs in /var)
    tail /var/log/httpd/error_log             # Apache error log
    tail /var/log/httpd/access_log            # Apache access log
    tail /var/tmp/lpar2rrd-realt-error.log    # STOR2RRD CGI-BIN log
    tail /var/tmp/systemd-private*/tmp/lpar2rrd-realt-error.log # STOR2RRD CGI-BIN log when Linux has enabled private temp
    
  • Test of CGI-BIN setup
    umask 022
    cd /home/lpar2rrd/lpar2rrd/
    cp bin/test-healthcheck-cgi.sh lpar2rrd-cgi/
    
    go to the web browser: http://<your web server>/lpar2rrd/test.html
    You should see your Apache, LPAR2RRD, and Operating System variables, if not, then check Apache logs for connected errors