Environment

• Zabbix version: 5.0.12
• PostgreSQL version: 13.6

Monitoring Objectives

• Track PostgreSQL service availability (non-critical business, primarily detecting outages)
• Monitor streaming replication status

Alerts are triggered when anomalies are detected.

Implementation Steps

Step 1: Create Monitoring User and Configure Acccess

Connect to PostgreSQL and create a dedicated monitoring account with appropriate privileges:

postgres=# CREATE USER zbx_monitor WITH PASSWORD 'secure_pass_2024' INHERIT;
CREATE ROLE
postgres=# GRANT pg_monitor TO zbx_monitor;
GRANT ROLE

Update the pg_hba.conf file to allow local authentication for the monitoring user:

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust

Step 2: Download Monitoring Templates

Obtain the official PostgreSQL monitoring templates from the Zabbix repository:

https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql?at=refs%2Ftags%2F5.0.12

Download the templates/db/postgresql directory. The package contains:

• template_db_postgresql.xml - Zabbix server template for import
• template_db_postgresql.conf - Zabbix agent configuration file
• postgresql/ - Directory containing SQL scripts for various metrics
• README.md - Reference documentation

Step 3: Deploy Scripts and Configure Agent

Assuming Zabbix agent was installed via package manager, the configuration directory is /etc/zabbix. Place the downloaded components as follows:

/etc/zabbix/
├── postgresql/
│   ├── pgsql.bgwriter.sql
│   ├── pgsql.cache.hit.sql
│   ├── pgsql.config.hash.sql
│   ├── pgsql.connections.prepared.sql
│   ├── pgsql.connections.sql
│   ├── pgsql.connections.sum.sql
│   ├── pgsql.dbstat.sql
│   ├── pgsql.dbstat.sum.sql
│   ├── pgsql.discovery.db.sql
│   ├── pgsql.frozenxid.sql
│   ├── pgsql.locks.sql
│   ├── pgsql.ping.time.sql
│   ├── pgsql.query.time.sql
│   ├── pgsql.replication.lag.sql
│   ├── pgsql.replication.recovery_role.sql
│   ├── pgsql.replication.status.sql
│   ├── pgsql.scans.sql
│   ├── pgsql.transactions.sql
│   ├── pgsql.uptime.sql
│   └── pgsql.wal.stat.sql
├── zabbix_agentd.conf
└── zabbix_agentd.d/
    └── template_db_postgresql.conf

Modify the agent configuration to match the deployed script paths. The default conifguration references /var/lib/zabbix/postgresql, but the scripts are located in /etc/zabbix/postgresql:

cd /etc/zabbix/zabbix_agentd.d/
sed -i 's#/var/lib/zabbix#/etc/zabbix#g' template_db_postgresql.conf

Since PostgreSQL was compiled from source and the Zabbix user has no login shell, update the configuration to use absolute paths for the psql binary:

UserParameter=pgsql.bgwriter[*], /opt/pgsql/13/bin/psql -qtAX -h "$1" -p "$2" -U "$3" -d "$4" -f "/etc/zabbix/postgresql/pgsql.bgwriter.sql"

Apply proper ownership:

chown -R zabbix:zabbix /etc/zabbix

Step 3: Import Template into Zabbix Server

Navigate to the Zabbix web interface and import template_db_postgresql.xml via Configuration → Templates → Import.

Step 4: Assign Template to Host

Link the imported template (Template DB PostgreSQL) to the PostgreSQL database server host in the Zabbix interface under Configuration → Hosts.

Step 5: Restart Zabbix Agent

Apply the configuration changes:

systemctl restart zabbix-agent.service

Verification

Monitor the Items section for the PostgreSQL host to verify data collection. Common issues include:

• psql command not found: Ensure the absolute path to the PostgreSQL binary is configured in the agent parameters
• Permission denied: Verify file ownership and PostgreSQL user grants

If certain metrics are unnecessary, modify the template to remove specific items through the Zabbix web interface.