We are often asked which monitoring solution we recommend for PostgreSQL, by people attending our PostgreSQL workshops. We usually answer: What do you have in place already? If a solution already is in place, the most obvious way is trying to integrate into the existing solution. If nothing is there already, you have plenty of choices. You can find some of them on this blog, but the list is quite old. Usually, PostgreSQL is not the only component to monitor, so a proper solution needs to come with agents/integrations/extensions, whatever you call it, for all the components which are critical for your environment. This makes the decision quite hard, as you have plenty of requirements and choices. That’s why I thought, that starting a little blog series about open source monitoring solutions that might give the right hints for choosing a solutions that fits for you, is good idea.

One of the well established, more than 20 years old, solution you might want to look at, is Zabbix. Zabbix, like most of the tools, comes with a server part and an agent that will be be deployed on the targets. You can download it pre-packaged for most of the well known Linux distributions from here. Another option is to install Zabbix from source code, which can be downloaded from here. Zabbix uses a database to store it’s data, and of course I’ll use PostgreSQL for that. Other options are MySQL, Oracle and TimescaleDB.

For PostgreSQL there is not much to do. Initialize a new cluster:

[email protected]:/home/postgres/ [pg141] mkdir -p /u02/pgdata/14/zabbix
[email protected]:/home/postgres/ [pg141] initdb /u02/pgdata/14/zabbix/

Create a systemd service definition so the instance will start automatically when the node comes up:

[email protected]:/home/postgres/ [pg141] cat /etc/systemd/system/postgres-zabbix.service 
[Unit]
Description=PostgreSQL for Zabbix
After=tuned.service

[Service]
User=postgres
Type=notify
ExecStart=/u01/app/postgres/product/14/db_1/bin/postgres -D /u02/pgdata/14/zabbix/
ExecReload=/u01/app/postgres/product/14/db_1/bin/pg_ctl -D /u02/pgdata/14/zabbix/ reload
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

That’s it, once the service is started this is the status you should see:

[email protected]:/etc/systemd/system/ [pg141] systemctl status postgres-zabbix.service
● postgres-zabbix.service - PostgreSQL for Zabbix
     Loaded: loaded (/etc/systemd/system/postgres-zabbix.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2021-11-28 14:57:48 CET; 4s ago
   Main PID: 1311 (postgres)
      Tasks: 9 (limit: 1133)
     Memory: 19.0M
        CPU: 80ms
     CGroup: /system.slice/postgres-zabbix.service
             ├─1311 /u01/app/postgres/product/14/db_1/bin/postgres -D /u02/pgdata/14/zabbix/
             ├─1312 postgres: zabbix: logger
             ├─1314 postgres: zabbix: checkpointer
             ├─1315 postgres: zabbix: background writer
             ├─1316 postgres: zabbix: walwriter
             ├─1317 postgres: zabbix: autovacuum launcher
             ├─1318 postgres: zabbix: archiver
             ├─1319 postgres: zabbix: stats collector
             └─1320 postgres: zabbix: logical replication launcher

As Zabbix should use it’s own database and user, create both:

[email protected]:/home/postgres/ [pg141] psql -c "create user zabbix with login password 'zabbix'"
CREATE ROLE
[email protected]:/home/postgres/ [pg141] psql -c "create database zabbix with owner=zabbix"
CREATE DATABASE
[email protected]:/home/postgres/ [pg141] psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 zabbix    | zabbix   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

Nothing more to do for the PostgreSQL, except loading the Zabbix schema later on.

Usually Zabbix gets installed with it’s one operating system user and group. For simplicity, I’ll do everything as the “postgres” user. Before we can install Zabbix from source code, we need to get it and then prepare the PostgreSQL database:

[email protected]:/home/postgres/ [pg141] wget https://cdn.zabbix.com/zabbix/sources/stable/5.4/zabbix-5.4.7.tar.gz
[email protected]:/home/postgres/ [pg141] tar axf zabbix-5.4.7.tar.gz 
[email protected]:/home/postgres/ [pg141] cd zabbix-5.4.7/
[email protected]:/home/postgres/zabbix-5.4.7/ [pg141] ls database/postgresql/
data.sql  double.sql  images.sql  Makefile.am  Makefile.in  schema.sql  timescaledb.sql
[email protected]:/home/postgres/zabbix-5.4.7/ [pg141] psql -f database/postgresql/schema.sql -U zabbix zabbix
CREATE TABLE
CREATE INDEX
CREATE TABLE
...
ALTER TABLE
ALTER TABLE
ALTER TABLE
[email protected]:/home/postgres/zabbix-5.4.7/ [pg141] psql -f database/postgresql/images.sql -U zabbix zabbix
INSERT 0 1
INSERT 0 1
INSERT 0 1
...
INSERT 0 1
INSERT 0 1
[email protected]:/home/postgres/zabbix-5.4.7/ [pg141] psql -f database/postgresql/data.sql -U zabbix zabbix
START TRANSACTION
INSERT 0 1
INSERT 0 1
INSERT 0 1
...
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT

Next, configure (this is a Debian 11 system, package names might be different in your case):

[email protected]:/home/postgres/zabbix-5.4.7/ [pg141] sudo apt install libsnmp-dev libopenipmi-dev libevent-dev libcurl4-openssl-dev libpcre3-dev
[email protected]:/home/postgres/zabbix-5.4.7/ [pg141] ./configure --prefix=/u01/app/postgres/product/zabbix_5.4.7 --enable-server --enable-agent --with-postgresql --enable-ipv6 --with-net-snmp --with-libcurl --with-libxml2 --with-openipmi
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for gawk... gawk
...
config.status: creating src/zabbix_java/Makefile
config.status: creating man/Makefile
config.status: creating include/config.h
config.status: include/config.h is unchanged
config.status: executing depfiles commands


Configuration:

  Detected OS:           linux-gnu
  Install path:          /u01/app/postgres/product/zabbix_5.4.7
  Compilation arch:      linux

  Compiler:              cc
  Compiler flags:         -g -O2  

  Library-specific flags:
    database:                -I/u01/app/postgres/product/14/db_0/include 
    libXML2:               -I/usr/include/libxml2
    Net-SNMP:               -I/usr/local/include -I/usr/lib/x86_64-linux-gnu/perl/5.32/CORE -I. -I/usr/include
    OpenIPMI:              -I/usr/include

  Enable server:         yes
  Server details:
    With database:         PostgreSQL
    WEB Monitoring:        cURL
      SSL certificates:      /u01/app/postgres/product/zabbix_5.4.7/share/zabbix/ssl/certs
      SSL keys:              /u01/app/postgres/product/zabbix_5.4.7/share/zabbix/ssl/keys
    SNMP:                  yes
    IPMI:                  yes
    SSH:                   no
    TLS:                   no
    ODBC:                  no
    Linker flags:           -L/usr/lib/x86_64-linux-gnu    -L/u01/app/postgres/product/14/db_0/lib     -L/usr/lib       -rdynamic     
    Libraries:                -lpq    -lnetsnmp   -lOpenIPMI -lOpenIPMIposix -lz -lpthread -levent    -lcurl -lm -ldl  -lresolv -lxml2   -lpcre 
    Configuration file:    /u01/app/postgres/product/zabbix_5.4.7/etc/zabbix_server.conf
    External scripts:      /u01/app/postgres/product/zabbix_5.4.7/share/zabbix/externalscripts
    Alert scripts:         /u01/app/postgres/product/zabbix_5.4.7/share/zabbix/alertscripts
    Modules:               /u01/app/postgres/product/zabbix_5.4.7/lib/modules

  Enable proxy:          no

  Enable agent:          yes
  Agent details:
    TLS:                   no
    Modbus:                no
    Linker flags:                -rdynamic     
    Libraries:              -lz -lpthread    -lcurl -lm -ldl  -lresolv -lxml2   -lpcre 
    Configuration file:    /u01/app/postgres/product/zabbix_5.4.7/etc/zabbix_agentd.conf
    Modules:               /u01/app/postgres/product/zabbix_5.4.7/lib/modules

  Enable agent 2:        no

  Enable web service:    no

  Enable Java gateway:   no

  LDAP support:          no
  IPv6 support:          yes

***********************************************************
*            Now run 'make install'                       *
*                                                         *
*            Thank you for using Zabbix!                  *
*                                  *
***********************************************************

All fine, compile and install:

[email protected]:/home/postgres/zabbix-5.4.7/ [pg141] make install
Making install in src
make[1]: Entering directory '/home/postgres/zabbix-5.4.7/src'
Making install in libs
make[2]: Entering directory '/home/postgres/zabbix-5.4.7/src/libs'
Making install in zbxcrypto
make[3]: Entering directory '/home/postgres/zabbix-5.4.7/src/libs/zbxcrypto'
...
make[2]: Entering directory '/home/postgres/zabbix-5.4.7'
make[2]: Nothing to be done for 'install-exec-am'.
make[2]: Nothing to be done for 'install-data-am'.
make[2]: Leaving directory '/home/postgres/zabbix-5.4.7'
make[1]: Leaving directory '/home/postgres/zabbix-5.4.7'
[email protected]:/home/postgres/zabbix-5.4.7/ [pg141] 

Nothing really complicated and easy to prepare. Before we can startup the Zabbix server there needs to be a minimal configuration for it:

[email protected]:/home/postgres/zabbix-5.4.7/ [pg141] cd
[email protected]:/home/postgres/ [pg141] vi /u01/app/postgres/product/zabbix_5.4.7/etc/zabbix_server.conf
[email protected]:/home/postgres/ [pg141] egrep -v "^$|^#"  /u01/app/postgres/product/zabbix_5.4.7/etc/zabbix_server.conf
SourceIP=192.168.100.173
LogFile=/u01/app/postgres/local/dmk/log/zabbix_server.log
LogFileSize=10
PidFile=/u01/app/postgres/local/dmk/tmp/zabbix_server.pid
SocketDir=/u01/app/postgres/local/dmk/tmp/
DBHost=localhost
DBName=zabbix
DBSchema=public
DBUser=zabbix
DBPassword=zabbix
Timeout=4
LogSlowQueries=3000
StatsAllowedIP=StatsAllowedIP=192.168.100.0/24

If the configuration is fine, the Zabbix server should start up without any issues:

[email protected]:/home/postgres/ [pg141] /u01/app/postgres/product/zabbix_5.4.7/sbin/zabbix_server 
[email protected]:/home/postgres/ [pg141] ps -ef | grep zabbix
...
postgres   41541    1311  0 15:27 ?        00:00:00 postgres: zabbix: zabbix zabbix [local] idle
postgres   41606       1  0 15:32 ?        00:00:00 /u01/app/postgres/product/zabbix_5.4.7/sbin/zabbix_server
postgres   41615   41606  0 15:32 ?        00:00:00 /u01/app/postgres/product/zabbix_5.4.7/sbin/zabbix_server: configuration syncer [synced configuration in 0.051028 sec, idle 60 sec]
postgres   41616    1311  0 15:32 ?        00:00:00 postgres: zabbix: zabbix zabbix ::1(36310) idle
postgres   41618   41606  0 15:32 ?        00:00:00 /u01/app/postgres/product/zabbix_5.4.7/sbin/zabbix_server: alert manager #1 [sent 0, failed 0 alerts, idle 5.104997 sec during 5.105235 sec]
...

Starting the Zabbix server after the PostgreSQL instance automatically with systemd is just a matter of this ( we usually do “current” links so we can easily switch between versions ):

[email protected]:/home/postgres/ [pg141] ln -s /u01/app/postgres/product/zabbix_5.4.7/ /u01/app/postgres/product/zabbix-current
[email protected]:/home/postgres/ [zabbix] cat /etc/systemd/system/zabbix.service
[Unit]
Description=Zabbix server service
After=network.target,postgres-zabbix.service

[Service]
User=postgres
Type=notify
ExecStart=/u01/app/postgres/product/zabbix-current/sbin/zabbix_server -f
Restart=always
RestartSec=10s
LimitNOFILE=40000

[Install]
WantedBy=multi-user.target

[email protected]:/home/postgres/ [zabbix] sudo systemctl daemon-reload
[email protected]:/home/postgres/ [zabbix] sudo systemctl enable zabbix.service
[email protected]:/home/postgres/ [zabbix] sudo reboot

Done, for the Zabbix server. What you usually want to have in addition, is a graphical user interface and Zabbix comes web application written in PHP. The easiest way for me was to install the Apache webserver with PHP support:

[email protected]:/home/postgres/ [pg141] sudo apt install apache libapache2-mod-php
[email protected]:/home/postgres/ [pg141] systemctl status apache2.service
● apache2.service - The Apache HTTP Server
     Loaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2021-11-28 15:52:54 CET; 34s ago
       Docs: https://httpd.apache.org/docs/2.4/
   Main PID: 8527 (apache2)
      Tasks: 6 (limit: 1133)
     Memory: 12.2M
        CPU: 83ms
     CGroup: /system.slice/apache2.service
             ├─8527 /usr/sbin/apache2 -k start
             ├─8530 /usr/sbin/apache2 -k start
             ├─8531 /usr/sbin/apache2 -k start
             ├─8532 /usr/sbin/apache2 -k start
             ├─8533 /usr/sbin/apache2 -k start
             └─8534 /usr/sbin/apache2 -k start
[email protected]:/home/postgres/ [pg141] cd zabbix-5.4.7/ui/
[email protected]:/home/postgres/zabbix-5.4.7/ui/ [pg141] sudo mkdir /var/www/html/zabbix/
[email protected]:/home/postgres/zabbix-5.4.7/ui/ [pg141] sudo cp -a . /var/www/html/zabbix/

From now on you should be able to access the Zabbix console ( http://192.168.100.173/zabbix, in my case ):

Going to the next screen will trigger a prerequisite check and some stuff fails for me:

The whole list of checks in text format is this:

                                                Curent value |  Required    Result
-----------------------------------------------------------------------------------
PHP version                                     7.4.25          7.2.0       OK
PHP option              "memory_limit"          128M            128M        OK
PHP option              "post_max_size"         8M              16M         Fail
PHP option              "upload_max_filesize"   2M              2M          OK
PHP option              "max_execution_time"    30              300         Fail
PHP option              "max_input_time"        60              300         Fail
PHP databases support    off                                                Fail
PHP bcmath               off                                                Fail
PHP mbstring             off                                                Fail
PHP sockets	             on                                                 OK
PHP gd	                 unknown                                 2.0        Fail
PHP gd PNG support       off                                                Fail
PHP gd JPEG support      off                                                Fail
PHP gd GIF support       off                                                Warning
PHP gd FreeType support	 off                                                Fail
PHP libxml               2.9.10                                 2.6.15      OK
PHP xmlwriter            off                                                Fail
PHP xmlreader            off                                                Fail
PHP LDAP	             off                                                Warning
PHP OpenSSL              on                                                 OK
PHP ctype                on                                                 OK
PHP session	             on                                                 OK
PHP option               "session.auto_start" off off	                    OK
PHP gettext              on                                                 OK
PHP option               "arg_separator.output"                             OK

To fix the failed checks, I had to tweak the PHP configuration and to install additional packages:

[email protected]:/home/postgres/ [pg141] sudo vi /etc/php/7.4/apache2/php.ini
[email protected]:/home/postgres/ [pg141] egrep "max_input_time|max_execution_time|post_max_size" /etc/php/7.4/apache2/php.ini
; max_input_time
max_execution_time = 300
max_input_time = 300
post_max_size = 16M
[email protected]:/home/postgres/ [pg141] sudo apt install php7.4-ldap php7.4-mbstring php7.4-pgsql php7.4-bcmath php7.4-gd php7.4-xml
[email protected]:/home/postgres/ [pg141] sudo chown -R www-data:www-data /var/www/html/zabbix

The rest of the configuration is self explaining:





One last point to fix for the server part: As you can see in the last screenshot, the server node itself is reported as down. This is not a surprise as we did not configure the agent yet, but this is as well very simple:

[email protected]:/home/postgres/ [pg141] egrep -v "^$|^#" /u01/app/postgres/product/zabbix-current/etc/zabbix_agentd.conf
PidFile=/u01/app/postgres/local/dmk/tmp/zabbix_agentd.pid
LogFile=/u01/app/postgres/local/dmk/log/zabbix_agentd.log
SourceIP=127.0.0.1
Server=192.168.100.173
ServerActive=127.0.0.1
Hostname=Zabbix server
[email protected]:/home/postgres/ [pg141] cat /etc/systemd/system/zabbix-agent.service 
[Unit]
Description=dbi services Zabbix agent service
After=network.target,postgres-zabbix.service

[Service]
User=postgres
Type=notify
ExecStart=/u01/app/postgres/product/zabbix-current/sbin/zabbix_agentd -f
Restart=always
RestartSec=10s
LimitNOFILE=40000

[Install]
WantedBy=multi-user.target

After the agent is started up, all is green in the console:

In the next we’ll install and configure the agent on three Patroni nodes and configure Zabbix to monitor that.


Thumbnail [60x60]
by
Daniel Westermann