Available solutions

Template DB PostgreSQL
Template DB PostgreSQL Agent 2
3rd party solutions

This template is for Zabbix version: 6.4

Also available for: 6.2 6.0 5.4 5.0

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql?at=release/6.4

PostgreSQL by Zabbix agent

Overview

This template is designed for the effortless deployment of PostgreSQL monitoring by Zabbix via Zabbix agent and doesn't require any external scripts.

Requirements

Zabbix version: 6.4 and higher.

Tested versions

This template has been tested on:

PostgreSQL 10-15

Configuration

Setup

Install Zabbix agent and create a read-only zbx_monitor user with proper access to your PostgreSQL server.

For PostgreSQL version 10 and above:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

For PostgreSQL version 9.6 and below:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>';
GRANT SELECT ON pg_stat_database TO zbx_monitor;

-- To collect WAL metrics, the user must have a `superuser` role.
ALTER USER zbx_monitor WITH SUPERUSER;

Copy postgresql/ to Zabbix agent home directory /var/lib/zabbix/. The postgresql/ directory contains the files needed to obtain metrics from PostgreSQL.
Copy template_db_postgresql.conf to Zabbix agent configuration directory /etc/zabbix/zabbix_agentd.d/ and restart Zabbix agent service.
Edit pg_hba.conf to allow connections from Zabbix agent https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Add rows (for example):
```
host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5
```
Import template file to Zabbix and link it to the target host
Set {$PG.HOST}, {$PG.PORT}, {$PG.USER}, {$PG.PASSWORD} and {$PG.DB} macros values.

Macros used

Name	Description	Default
{$PG.CACHE_HITRATIO.MIN.WARN}	`90`
{$PG.CHECKPOINTS_REQ.MAX.WARN}	`5`
{$PG.CONFLICTS.MAX.WARN}	`0`
{$PG.CONN_IDLE_IN_TRANS.MAX.WARN}	`5`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	`90`
{$PG.CONN_WAIT.MAX.WARN}	`0`
{$PG.DB}	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	`0`
{$PG.FROZENXID_PCT_STOP.MIN.HIGH}	`75`
{$PG.HOST}	`127.0.0.1`
{$PG.LLD.FILTER.DBNAME}	`(.*)`
{$PG.LOCKS.MAX.WARN}	`100`
{$PG.PING_TIME.MAX.WARN}	`1s`
{$PG.PORT}	`5432`
{$PG.QUERY_ETIME.MAX.WARN}	`30`
{$PG.REPL_LAG.MAX.WARN}	`10m`
{$PG.SLOW_QUERIES.MAX.WARN}	`5`
{$PG.TRANS_ACTIVE.MAX.WARN}	`30s`
{$PG.TRANS_IDLE.MAX.WARN}	`30s`
{$PG.TRANS_WAIT.MAX.WARN}	`30s`
{$PG.USER}	`zbx_monitor`
{$PG.PASSWORD}	Please set user's password in this macro.

Items

Name	Description	Type	Key and additional info
Bgwriter: Buffers allocated per second	Number of buffers allocated	Dependent item	pgsql.bgwriter.buffers_alloc.rate Preprocessing JSON Path: `$.buffers_alloc` Change per second
Bgwriter: Buffers written directly by a backend per second	Number of buffers written directly by a backend	Dependent item	pgsql.bgwriter.buffers_backend.rate Preprocessing JSON Path: `$.buffers_backend` Change per second
Bgwriter: Buffers backend fsync per second	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	Dependent item	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing JSON Path: `$.buffers_backend_fsync` Change per second
Bgwriter: Buffers written during checkpoints per second	Number of buffers written during checkpoints	Dependent item	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing JSON Path: `$.buffers_checkpoint` Change per second
Bgwriter: Buffers written by the background writer per second	Number of buffers written by the background writer	Dependent item	pgsql.bgwriter.buffers_clean.rate Preprocessing JSON Path: `$.buffers_clean` Change per second
Bgwriter: Requested checkpoints per second	Number of requested checkpoints that have been performed	Dependent item	pgsql.bgwriter.checkpoints_req.rate Preprocessing JSON Path: `$.checkpoints_req` Change per second
Bgwriter: Scheduled checkpoints per second	Number of scheduled checkpoints that have been performed	Dependent item	pgsql.bgwriter.checkpoints_timed.rate Preprocessing JSON Path: `$.checkpoints_timed` Change per second
Bgwriter: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	Dependent item	pgsql.bgwriter.checkpoint_sync_time Preprocessing JSON Path: `$.checkpoint_sync_time` Custom multiplier: `0.001` Change per second
Bgwriter: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	Dependent item	pgsql.bgwriter.checkpoint_write_time Preprocessing JSON Path: `$.checkpoint_write_time` Custom multiplier: `0.001` Change per second
Bgwriter: Max written per second	Number of times the background writer stopped a cleaning scan because it had written too many buffers	Dependent item	pgsql.bgwriter.maxwritten_clean.rate Preprocessing JSON Path: `$.maxwritten_clean` Change per second
PostgreSQL: Get bgwriter	Statistics about the background writer process's activity	Zabbix agent	pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Cache hit ratio %	Cache hit ratio	Zabbix agent	pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Config hash	PostgreSQL configuration hash	Zabbix agent	pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing Discard unchanged with heartbeat: `1h`
Connections sum: Active	Total number of connections executing a query	Dependent item	pgsql.connections.sum.active Preprocessing JSON Path: `$.active`
Connections sum: Idle	Total number of connections waiting for a new client command	Dependent item	pgsql.connections.sum.idle Preprocessing JSON Path: `$.idle`
Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	Dependent item	pgsql.connections.sum.idle_in_transaction Preprocessing JSON Path: `$.idle_in_transaction`
Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	Dependent item	pgsql.connections.sum.prepared Preprocessing JSON Path: `$.prepared`
Connections sum: Total	Total number of connections	Dependent item	pgsql.connections.sum.total Preprocessing JSON Path: `$.total`
Connections sum: Total %	Total number of connections in percentage	Dependent item	pgsql.connections.sum.total_pct Preprocessing JSON Path: `$.total_pct`
Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	Dependent item	pgsql.connections.sum.waiting Preprocessing JSON Path: `$.waiting`
PostgreSQL: Get connections sum	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	Zabbix agent	pgsql.connections.sum["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	Zabbix agent	pgsql.dbstat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	Zabbix agent	pgsql.locks["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Ping time	Zabbix agent	pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing Regular expression: `Time:\s+(\d+\.\d+)\s+ms \1` Custom multiplier: `0.001`
Status: Ping	Zabbix agent	pgsql.ping["{$PG.HOST}","{$PG.PORT}"] Preprocessing JavaScript: `The text is too long. Please see the template.` Discard unchanged with heartbeat: `1h`
PostgreSQL: Get queries	Collect all metrics by query execution time	Zabbix agent	pgsql.queries["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{$PG.QUERY_ETIME.MAX.WARN}"]
Replication: standby count	Number of standby servers	Zabbix agent	pgsql.replication.count["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Replication: lag in seconds	Replication lag with Master in seconds	Zabbix agent	pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Replication: recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	Zabbix agent	pgsql.replication.recovery_role["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Replication: status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	Zabbix agent	pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Transactions: Max active transaction time	Current max active transaction time	Dependent item	pgsql.transactions.active Preprocessing JSON Path: `$.active`
Transactions: Max idle transaction time	Current max idle transaction time	Dependent item	pgsql.transactions.idle Preprocessing JSON Path: `$.idle`
Transactions: Max prepared transaction time	Current max prepared transaction time	Dependent item	pgsql.transactions.prepared Preprocessing JSON Path: `$.prepared`
Transactions: Max waiting transaction time	Current max waiting transaction time	Dependent item	pgsql.transactions.waiting Preprocessing JSON Path: `$.waiting`
PostgreSQL: Get transactions	Collect metrics by transaction execution time	Zabbix agent	pgsql.transactions["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Uptime	Zabbix agent	pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Version	PostgreSQL version	Zabbix agent	pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing Discard unchanged with heartbeat: `1d`
WAL: Segments count	Number of WAL segments	Dependent item	pgsql.wal.count Preprocessing JSON Path: `$.count`
PostgreSQL: Get WAL	Master item to collect WAL metrics	Zabbix agent	pgsql.wal.stat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
WAL: Bytes written	WAL write in bytes	Dependent item	pgsql.wal.write Preprocessing JSON Path: `$.write` Change per second

Triggers

Name	Description	Expression	Severity	Dependencies and additional info
PostgreSQL: Required checkpoints occurs too frequently	Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint. At checkpoint time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log file.https://www.postgresql.org/docs/current/wal-configuration.html	`last(/PostgreSQL by Zabbix agent/pgsql.bgwriter.checkpoints_req.rate) > {$PG.CHECKPOINTS_REQ.MAX.WARN}`	Average
PostgreSQL: Failed to get items	Zabbix has not received any data for items for the last 30 minutes.	`nodata(/PostgreSQL by Zabbix agent/pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],30m) = 1`	Warning	Depends on: PostgreSQL: Service is down
PostgreSQL: Cache hit ratio too low	`max(/PostgreSQL by Zabbix agent/pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) < {$PG.CACHE_HITRATIO.MIN.WARN}`	Warning
PostgreSQL: Configuration has changed	`last(/PostgreSQL by Zabbix agent/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#1)<>last(/PostgreSQL by Zabbix agent/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#2) and length(last(/PostgreSQL by Zabbix agent/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]))>0`	Info
PostgreSQL: Total number of connections is too high	`min(/PostgreSQL by Zabbix agent/pgsql.connections.sum.total_pct,5m) > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	Average
PostgreSQL: Response too long	`min(/PostgreSQL by Zabbix agent/pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) > {$PG.PING_TIME.MAX.WARN}`	Average	Depends on: PostgreSQL: Service is down
PostgreSQL: Service is down	`last(/PostgreSQL by Zabbix agent/pgsql.ping["{$PG.HOST}","{$PG.PORT}"]) = 0`	High
PostgreSQL: Streaming lag with {#MASTER} is too high	`min(/PostgreSQL by Zabbix agent/pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) > {$PG.REPL_LAG.MAX.WARN}`	Average
PostgreSQL: Replication is down	`max(/PostgreSQL by Zabbix agent/pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m)=0`	Average
PostgreSQL: Service has been restarted	PostgreSQL uptime is less than 10 minutes.	`last(/PostgreSQL by Zabbix agent/pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]) < 10m`	Info
PostgreSQL: Version has changed	`last(/PostgreSQL by Zabbix agent/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#1)<>last(/PostgreSQL by Zabbix agent/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#2) and length(last(/PostgreSQL by Zabbix agent/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]))>0`	Info

LLD rule Database discovery

Name	Description	Type	Key and additional info
Database discovery	Zabbix agent	pgsql.discovery.db["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]

Item prototypes for Database discovery

Name	Description	Type	Key and additional info
DB {#DBNAME}: Database size	Database size	Zabbix agent	pgsql.db.size["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{#DBNAME}"]
DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	Dependent item	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blks_hit` Change per second
DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	Dependent item	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blks_read` Change per second
DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	Dependent item	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].conflicts` Change per second
DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	Dependent item	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].deadlocks` Change per second
DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	Dependent item	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].temp_bytes` Change per second
DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	Dependent item	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].temp_files` Change per second
DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	Dependent item	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_deleted` Change per second
DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	Dependent item	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_fetched` Change per second
DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	Dependent item	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_inserted` Change per second
DB {#DBNAME}: Tuples returned per second	Total number of rows updated by queries in this database	Dependent item	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_returned` Change per second
DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	Dependent item	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_updated` Change per second
DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	Dependent item	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].xact_commit` Change per second
DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	Dependent item	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].xact_rollback` Change per second
DB {#DBNAME}: Frozen XID before avtovacuum %	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	Dependent item	pgsql.frozenxid.prc_before_av["{#DBNAME}"] Preprocessing JSON Path: `$.prc_before_av`
DB {#DBNAME}: Frozen XID before stop %	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	Dependent item	pgsql.frozenxid.prc_before_stop["{#DBNAME}"] Preprocessing JSON Path: `$.prc_before_stop`
DB {#DBNAME}: Get frozen XID	Zabbix agent	pgsql.frozenxid["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
DB {#DBNAME}: Locks total	Total number of locks in the database	Dependent item	pgsql.locks.total["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].total`
DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	Dependent item	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_slow_count`
DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	Dependent item	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_time_max`
DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	Dependent item	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_time_sum`
DB {#DBNAME}: Queries slow query count	Slow query count	Dependent item	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_slow_count`
DB {#DBNAME}: Queries max query time	Max query time	Dependent item	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_time_max`
DB {#DBNAME}: Queries sum query time	Sum query time	Dependent item	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_time_sum`
DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	Dependent item	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_slow_count`
DB {#DBNAME}: Queries max transaction time	Max transaction query time	Dependent item	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_time_max`
DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	Dependent item	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_time_sum`
DB {#DBNAME}: Index scans per second	Number of index scans in the database	Dependent item	pgsql.scans.idx.rate["{#DBNAME}"] Preprocessing JSON Path: `$.idx` Change per second
DB {#DBNAME}: Sequential scans per second	Number of sequential scans in the database	Dependent item	pgsql.scans.seq.rate["{#DBNAME}"] Preprocessing JSON Path: `$.seq` Change per second
DB {#DBNAME}: Get scans	Number of scans done for table/index in the database	Zabbix agent	pgsql.scans["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]

Trigger prototypes for Database discovery

Name	Description	Expression	Severity
DB {#DBNAME}: Too many recovery conflicts	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them.https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`min(/PostgreSQL by Zabbix agent/pgsql.dbstat.conflicts.rate["{#DBNAME}"],5m) > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	Average
DB {#DBNAME}: Deadlock occurred	`min(/PostgreSQL by Zabbix agent/pgsql.dbstat.deadlocks.rate["{#DBNAME}"],5m) > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	High
DB {#DBNAME}: VACUUM FREEZE is required to prevent wraparound	Preventing Transaction ID Wraparound Failureshttps://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	`last(/PostgreSQL by Zabbix agent/pgsql.frozenxid.prc_before_stop["{#DBNAME}"])<{$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"}`	Average
DB {#DBNAME}: Number of locks is too high	`min(/PostgreSQL by Zabbix agent/pgsql.locks.total["{#DBNAME}"],5m)>{$PG.LOCKS.MAX.WARN:"{#DBNAME}"}`	Warning
DB {#DBNAME}: Too many slow queries	`min(/PostgreSQL by Zabbix agent/pgsql.queries.query.slow_count["{#DBNAME}"],5m)>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	Warning

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide feedback, discuss the template, or ask for help at ZABBIX forums

This template is for Zabbix version: 6.2

Also available for: 6.4 6.0 5.4 5.0

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql?at=release/6.2

PostgreSQL by Zabbix agent

Overview

For Zabbix version: 6.2 and higher
Templates to monitor PostgreSQL by Zabbix. This template was tested on PostgreSQL versions 9.6, 10 and 11 on Linux and Windows.

Setup

Install Zabbix agent and create a read-only zbx_monitor user with proper access to your PostgreSQL server.

For PostgreSQL version 10 and above:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

For PostgreSQL version 9.6 and below:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>';
GRANT SELECT ON pg_stat_database TO zbx_monitor;

-- To collect WAL metrics, the user must have a `superuser` role.
ALTER USER zbx_monitor WITH SUPERUSER;

Copy postgresql/ to Zabbix agent home directory /var/lib/zabbix/. The postgresql/ directory contains the files needed to obtain metrics from PostgreSQL.
Copy template_db_postgresql.conf to Zabbix agent configuration directory /etc/zabbix/zabbix_agentd.d/ and restart Zabbix agent service.
Edit pg_hba.conf to allow connections from Zabbix agent https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Add rows (for example):
```
host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5
```
Import template file to Zabbix and link it to the target host
Set {$PG.HOST}, {$PG.PORT}, {$PG.USER}, {$PG.PASSWORD} and {$PG.DB} macros values.

Zabbix configuration

If PostgreSQL is installed from the PGDG repository, then add the path to pg_isready to the PATH environment variable for zabbix user.

Macros used

Name	Description	Default
{$PG.CACHE_HITRATIO.MIN.WARN}	-	`90`
{$PG.CHECKPOINTS_REQ.MAX.WARN}	-	`5`
{$PG.CONFLICTS.MAX.WARN}	-	`0`
{$PG.CONN_IDLE_IN_TRANS.MAX.WARN}	-	`5`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	-	`90`
{$PG.CONN_WAIT.MAX.WARN}	-	`0`
{$PG.DB}	-	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	-	`0`
{$PG.FROZENXID_PCT_STOP.MIN.HIGH}	-	`75`
{$PG.HOST}	-	`127.0.0.1`
{$PG.LLD.FILTER.DBNAME}	-	`(.*)`
{$PG.LOCKS.MAX.WARN}	-	`100`
{$PG.PASSWORD}	Please set user's password in this macro.	``
{$PG.PING_TIME.MAX.WARN}	-	`1s`
{$PG.PORT}	-	`5432`
{$PG.QUERY_ETIME.MAX.WARN}	-	`30`
{$PG.REPL_LAG.MAX.WARN}	-	`10m`
{$PG.SLOW_QUERIES.MAX.WARN}	-	`5`
{$PG.TRANS_ACTIVE.MAX.WARN}	-	`30s`
{$PG.TRANS_IDLE.MAX.WARN}	-	`30s`
{$PG.TRANS_WAIT.MAX.WARN}	-	`30s`
{$PG.USER}	-	`zbx_monitor`

Template links

There are no template links in this template.

Discovery rules

Name Description Type Key and additional info

Database discovery

Name	Description	Type	Key and additional info
Database discovery	-	ZABBIX_PASSIVE	pgsql.discovery.db["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Filter: - {#DBNAME} MATCHES_REGEX `{$PG.LLD.FILTER.DBNAME}`

ZABBIX_PASSIVE

pgsql.discovery.db["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]

Filter:

- {#DBNAME} MATCHES_REGEX {$PG.LLD.FILTER.DBNAME}

Items collected

Group	Name	Description	Type	Key and additional info
PostgreSQL	Bgwriter: Buffers allocated per second	Number of buffers allocated	DEPENDENT	pgsql.bgwriter.buffers_alloc.rate Preprocessing: - JSONPATH: `$.buffers_alloc` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written directly by a backend per second	Number of buffers written directly by a backend	DEPENDENT	pgsql.bgwriter.buffers_backend.rate Preprocessing: - JSONPATH: `$.buffers_backend` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers backend fsync per second	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	DEPENDENT	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing: - JSONPATH: `$.buffers_backend_fsync` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written during checkpoints per second	Number of buffers written during checkpoints	DEPENDENT	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing: - JSONPATH: `$.buffers_checkpoint` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written by the background writer per second	Number of buffers written by the background writer	DEPENDENT	pgsql.bgwriter.buffers_clean.rate Preprocessing: - JSONPATH: `$.buffers_clean` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Requested checkpoints per second	Number of requested checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_req.rate Preprocessing: - JSONPATH: `$.checkpoints_req` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Scheduled checkpoints per second	Number of scheduled checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_timed.rate Preprocessing: - JSONPATH: `$.checkpoints_timed` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	DEPENDENT	pgsql.bgwriter.checkpoint_sync_time Preprocessing: - JSONPATH: `$.checkpoint_sync_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	DEPENDENT	pgsql.bgwriter.checkpoint_write_time Preprocessing: - JSONPATH: `$.checkpoint_write_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Max written per second	Number of times the background writer stopped a cleaning scan because it had written too many buffers	DEPENDENT	pgsql.bgwriter.maxwritten_clean.rate Preprocessing: - JSONPATH: `$.maxwritten_clean` - CHANGE_PER_SECOND
PostgreSQL	Status: Cache hit ratio %	Cache hit ratio	ZABBIX_PASSIVE	pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Status: Config hash	PostgreSQL configuration hash	ZABBIX_PASSIVE	pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing: - DISCARD_UNCHANGED_HEARTBEAT: `1h`
PostgreSQL	Connections sum: Active	Total number of connections executing a query	DEPENDENT	pgsql.connections.sum.active Preprocessing: - JSONPATH: `$.active`
PostgreSQL	Connections sum: Idle	Total number of connections waiting for a new client command	DEPENDENT	pgsql.connections.sum.idle Preprocessing: - JSONPATH: `$.idle`
PostgreSQL	Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	DEPENDENT	pgsql.connections.sum.idle_in_transaction Preprocessing: - JSONPATH: `$.idle_in_transaction`
PostgreSQL	Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	DEPENDENT	pgsql.connections.sum.prepared Preprocessing: - JSONPATH: `$.prepared`
PostgreSQL	Connections sum: Total	Total number of connections	DEPENDENT	pgsql.connections.sum.total Preprocessing: - JSONPATH: `$.total`
PostgreSQL	Connections sum: Total %	Total number of connections in percentage	DEPENDENT	pgsql.connections.sum.total_pct Preprocessing: - JSONPATH: `$.total_pct`
PostgreSQL	Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	DEPENDENT	pgsql.connections.sum.waiting Preprocessing: - JSONPATH: `$.waiting`
PostgreSQL	Status: Ping time	-	ZABBIX_PASSIVE	pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing: - REGEX: `Time:\s+(\d+\.\d+)\s+ms \1` - MULTIPLIER: `0.001`
PostgreSQL	Status: Ping	-	ZABBIX_PASSIVE	pgsql.ping["{$PG.HOST}","{$PG.PORT}"] Preprocessing: - JAVASCRIPT: `return value.search(/accepting connections/)>0 ? 1 : 0` - DISCARD_UNCHANGED_HEARTBEAT: `1h`
PostgreSQL	Replication: standby count	Number of standby servers	ZABBIX_PASSIVE	pgsql.replication.count["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Replication: lag in seconds	Replication lag with Master in seconds	ZABBIX_PASSIVE	pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Replication: recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	ZABBIX_PASSIVE	pgsql.replication.recovery_role["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Replication: status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	ZABBIX_PASSIVE	pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Transactions: Max active transaction time	Current max active transaction time	DEPENDENT	pgsql.transactions.active Preprocessing: - JSONPATH: `$.active`
PostgreSQL	Transactions: Max idle transaction time	Current max idle transaction time	DEPENDENT	pgsql.transactions.idle Preprocessing: - JSONPATH: `$.idle`
PostgreSQL	Transactions: Max prepared transaction time	Current max prepared transaction time	DEPENDENT	pgsql.transactions.prepared Preprocessing: - JSONPATH: `$.prepared`
PostgreSQL	Transactions: Max waiting transaction time	Current max waiting transaction time	DEPENDENT	pgsql.transactions.waiting Preprocessing: - JSONPATH: `$.waiting`
PostgreSQL	Status: Uptime	-	ZABBIX_PASSIVE	pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Status: Version	PostgreSQL version	ZABBIX_PASSIVE	pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing: - DISCARD_UNCHANGED_HEARTBEAT: `1d`
PostgreSQL	WAL: Segments count	Number of WAL segments	DEPENDENT	pgsql.wal.count Preprocessing: - JSONPATH: `$.count`
PostgreSQL	WAL: Bytes written	WAL write in bytes	DEPENDENT	pgsql.wal.write Preprocessing: - JSONPATH: `$.write` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Database size	Database size	ZABBIX_PASSIVE	pgsql.db.size["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	DEPENDENT	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_hit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	DEPENDENT	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_read` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	DEPENDENT	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].conflicts` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	DEPENDENT	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].deadlocks` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	DEPENDENT	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_bytes` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	DEPENDENT	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_files` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	DEPENDENT	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_deleted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	DEPENDENT	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_fetched` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	DEPENDENT	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_inserted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples returned per second	Total number of rows updated by queries in this database	DEPENDENT	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_returned` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	DEPENDENT	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_updated` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	DEPENDENT	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_commit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	DEPENDENT	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_rollback` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Frozen XID before avtovacuum %	reventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	DEPENDENT	pgsql.frozenxid.prc_before_av["{#DBNAME}"] Preprocessing: - JSONPATH: `$.prc_before_av`
PostgreSQL	DB {#DBNAME}: Frozen XID before stop %	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	DEPENDENT	pgsql.frozenxid.prc_before_stop["{#DBNAME}"] Preprocessing: - JSONPATH: `$.prc_before_stop`
PostgreSQL	DB {#DBNAME}: Locks total	Total number of locks in the database	DEPENDENT	pgsql.locks.total["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].total`
PostgreSQL	DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	DEPENDENT	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_slow_count`
PostgreSQL	DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	DEPENDENT	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_max`
PostgreSQL	DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	DEPENDENT	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_sum`
PostgreSQL	DB {#DBNAME}: Queries slow query count	Slow query count	DEPENDENT	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_slow_count`
PostgreSQL	DB {#DBNAME}: Queries max query time	Max query time	DEPENDENT	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_max`
PostgreSQL	DB {#DBNAME}: Queries sum query time	Sum query time	DEPENDENT	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_sum`
PostgreSQL	DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	DEPENDENT	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_slow_count`
PostgreSQL	DB {#DBNAME}: Queries max transaction time	Max transaction query time	DEPENDENT	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_max`
PostgreSQL	DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	DEPENDENT	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_sum`
PostgreSQL	DB {#DBNAME}: Index scans per second	Number of index scans in the database	DEPENDENT	pgsql.scans.idx.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$.idx` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Sequential scans per second	Number of sequential scans in the database	DEPENDENT	pgsql.scans.seq.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$.seq` - CHANGE_PER_SECOND
Zabbix raw items	PostgreSQL: Get bgwriter	Statistics about the background writer process's activity	ZABBIX_PASSIVE	pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix raw items	PostgreSQL: Get connections sum	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	ZABBIX_PASSIVE	pgsql.connections.sum["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix raw items	PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	ZABBIX_PASSIVE	pgsql.dbstat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix raw items	PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	ZABBIX_PASSIVE	pgsql.locks["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix raw items	PostgreSQL: Get queries	Collect all metrics by query execution time	ZABBIX_PASSIVE	pgsql.queries["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{$PG.QUERY_ETIME.MAX.WARN}"]
Zabbix raw items	PostgreSQL: Get transactions	Collect metrics by transaction execution time	ZABBIX_PASSIVE	pgsql.transactions["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix raw items	PostgreSQL: Get WAL	Master item to collect WAL metrics	ZABBIX_PASSIVE	pgsql.wal.stat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix raw items	DB {#DBNAME}: Get frozen XID	-	ZABBIX_PASSIVE	pgsql.frozenxid["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
Zabbix raw items	DB {#DBNAME}: Get scans	Number of scans done for table/index in the database	ZABBIX_PASSIVE	pgsql.scans["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]

Triggers

Name	Description	Expression	Severity	Dependencies and additional info
PostgreSQL: Required checkpoints occurs too frequently	Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint. At checkpoint time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log file. https://www.postgresql.org/docs/current/wal-configuration.html	`last(/PostgreSQL by Zabbix agent/pgsql.bgwriter.checkpoints_req.rate) > {$PG.CHECKPOINTS_REQ.MAX.WARN}`	AVERAGE
PostgreSQL: Cache hit ratio too low	-	`max(/PostgreSQL by Zabbix agent/pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) < {$PG.CACHE_HITRATIO.MIN.WARN}`	WARNING
PostgreSQL: Configuration has changed	-	`last(/PostgreSQL by Zabbix agent/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#1)<>last(/PostgreSQL by Zabbix agent/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#2) and length(last(/PostgreSQL by Zabbix agent/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]))>0`	INFO
PostgreSQL: Total number of connections is too high	-	`min(/PostgreSQL by Zabbix agent/pgsql.connections.sum.total_pct,5m) > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	AVERAGE
PostgreSQL: Response too long	-	`min(/PostgreSQL by Zabbix agent/pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) > {$PG.PING_TIME.MAX.WARN}`	AVERAGE	Depends on: - PostgreSQL: Service is down
PostgreSQL: Service is down	-	`last(/PostgreSQL by Zabbix agent/pgsql.ping["{$PG.HOST}","{$PG.PORT}"]) = 0`	HIGH
PostgreSQL: Streaming lag with {#MASTER} is too high	-	`min(/PostgreSQL by Zabbix agent/pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) > {$PG.REPL_LAG.MAX.WARN}`	AVERAGE
PostgreSQL: Replication is down	-	`max(/PostgreSQL by Zabbix agent/pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m)=0`	AVERAGE
PostgreSQL: Service has been restarted	PostgreSQL uptime is less than 10 minutes	`last(/PostgreSQL by Zabbix agent/pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]) < 10m`	INFO
PostgreSQL: Version has changed	-	`last(/PostgreSQL by Zabbix agent/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#1)<>last(/PostgreSQL by Zabbix agent/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#2) and length(last(/PostgreSQL by Zabbix agent/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]))>0`	INFO
DB {#DBNAME}: Too many recovery conflicts	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them. https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`min(/PostgreSQL by Zabbix agent/pgsql.dbstat.conflicts.rate["{#DBNAME}"],5m) > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	AVERAGE
DB {#DBNAME}: Deadlock occurred	-	`min(/PostgreSQL by Zabbix agent/pgsql.dbstat.deadlocks.rate["{#DBNAME}"],5m) > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	HIGH
DB {#DBNAME}: VACUUM FREEZE is required to prevent wraparound	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	`last(/PostgreSQL by Zabbix agent/pgsql.frozenxid.prc_before_stop["{#DBNAME}"])<{$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"}`	AVERAGE
DB {#DBNAME}: Number of locks is too high	-	`min(/PostgreSQL by Zabbix agent/pgsql.locks.total["{#DBNAME}"],5m)>{$PG.LOCKS.MAX.WARN:"{#DBNAME}"}`	WARNING
DB {#DBNAME}: Too many slow queries	-	`min(/PostgreSQL by Zabbix agent/pgsql.queries.query.slow_count["{#DBNAME}"],5m)>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	WARNING
PostgreSQL: Failed to get items	Zabbix has not received data for items for the last 30 minutes	`nodata(/PostgreSQL by Zabbix agent/pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],30m) = 1`	WARNING	Depends on: - PostgreSQL: Service is down

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide feedback, discuss the template or ask for help with it at ZABBIX forums.

This template is for Zabbix version: 6.0

Also available for: 6.4 6.2 5.4 5.0

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql?at=release/6.0

PostgreSQL by Zabbix agent

Overview

This template is designed for the effortless deployment of PostgreSQL monitoring by Zabbix via Zabbix agent and doesn't require any external scripts.

Requirements

Zabbix version: 6.0 and higher.

Tested versions

This template has been tested on:

PostgreSQL 10-15

Configuration

Setup

Install Zabbix agent and create a read-only zbx_monitor user with proper access to your PostgreSQL server.

For PostgreSQL version 10 and above:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

For PostgreSQL version 9.6 and below:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>';
GRANT SELECT ON pg_stat_database TO zbx_monitor;

-- To collect WAL metrics, the user must have a `superuser` role.
ALTER USER zbx_monitor WITH SUPERUSER;

Copy postgresql/ to Zabbix agent home directory /var/lib/zabbix/. The postgresql/ directory contains the files needed to obtain metrics from PostgreSQL.
Copy template_db_postgresql.conf to Zabbix agent configuration directory /etc/zabbix/zabbix_agentd.d/ and restart Zabbix agent service.
Edit pg_hba.conf to allow connections from Zabbix agent https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Add rows (for example):
```
host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5
```
Import template file to Zabbix and link it to the target host
Set {$PG.HOST}, {$PG.PORT}, {$PG.USER}, {$PG.PASSWORD} and {$PG.DB} macros values.

Macros used

Name	Description	Default
{$PG.CACHE_HITRATIO.MIN.WARN}	`90`
{$PG.CHECKPOINTS_REQ.MAX.WARN}	`5`
{$PG.CONFLICTS.MAX.WARN}	`0`
{$PG.CONN_IDLE_IN_TRANS.MAX.WARN}	`5`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	`90`
{$PG.CONN_WAIT.MAX.WARN}	`0`
{$PG.DB}	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	`0`
{$PG.FROZENXID_PCT_STOP.MIN.HIGH}	`75`
{$PG.HOST}	`127.0.0.1`
{$PG.LLD.FILTER.DBNAME}	`(.*)`
{$PG.LOCKS.MAX.WARN}	`100`
{$PG.PING_TIME.MAX.WARN}	`1s`
{$PG.PORT}	`5432`
{$PG.QUERY_ETIME.MAX.WARN}	`30`
{$PG.REPL_LAG.MAX.WARN}	`10m`
{$PG.SLOW_QUERIES.MAX.WARN}	`5`
{$PG.TRANS_ACTIVE.MAX.WARN}	`30s`
{$PG.TRANS_IDLE.MAX.WARN}	`30s`
{$PG.TRANS_WAIT.MAX.WARN}	`30s`
{$PG.USER}	`zbx_monitor`
{$PG.PASSWORD}	Please set user's password in this macro.

Items

Name	Description	Type	Key and additional info
Bgwriter: Buffers allocated per second	Number of buffers allocated	Dependent item	pgsql.bgwriter.buffers_alloc.rate Preprocessing JSON Path: `$.buffers_alloc` Change per second
Bgwriter: Buffers written directly by a backend per second	Number of buffers written directly by a backend	Dependent item	pgsql.bgwriter.buffers_backend.rate Preprocessing JSON Path: `$.buffers_backend` Change per second
Bgwriter: Buffers backend fsync per second	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	Dependent item	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing JSON Path: `$.buffers_backend_fsync` Change per second
Bgwriter: Buffers written during checkpoints per second	Number of buffers written during checkpoints	Dependent item	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing JSON Path: `$.buffers_checkpoint` Change per second
Bgwriter: Buffers written by the background writer per second	Number of buffers written by the background writer	Dependent item	pgsql.bgwriter.buffers_clean.rate Preprocessing JSON Path: `$.buffers_clean` Change per second
Bgwriter: Requested checkpoints per second	Number of requested checkpoints that have been performed	Dependent item	pgsql.bgwriter.checkpoints_req.rate Preprocessing JSON Path: `$.checkpoints_req` Change per second
Bgwriter: Scheduled checkpoints per second	Number of scheduled checkpoints that have been performed	Dependent item	pgsql.bgwriter.checkpoints_timed.rate Preprocessing JSON Path: `$.checkpoints_timed` Change per second
Bgwriter: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	Dependent item	pgsql.bgwriter.checkpoint_sync_time Preprocessing JSON Path: `$.checkpoint_sync_time` Custom multiplier: `0.001` Change per second
Bgwriter: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	Dependent item	pgsql.bgwriter.checkpoint_write_time Preprocessing JSON Path: `$.checkpoint_write_time` Custom multiplier: `0.001` Change per second
Bgwriter: Max written per second	Number of times the background writer stopped a cleaning scan because it had written too many buffers	Dependent item	pgsql.bgwriter.maxwritten_clean.rate Preprocessing JSON Path: `$.maxwritten_clean` Change per second
PostgreSQL: Get bgwriter	Statistics about the background writer process's activity	Zabbix agent	pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Cache hit ratio %	Cache hit ratio	Zabbix agent	pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Config hash	PostgreSQL configuration hash	Zabbix agent	pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing Discard unchanged with heartbeat: `1h`
Connections sum: Active	Total number of connections executing a query	Dependent item	pgsql.connections.sum.active Preprocessing JSON Path: `$.active`
Connections sum: Idle	Total number of connections waiting for a new client command	Dependent item	pgsql.connections.sum.idle Preprocessing JSON Path: `$.idle`
Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	Dependent item	pgsql.connections.sum.idle_in_transaction Preprocessing JSON Path: `$.idle_in_transaction`
Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	Dependent item	pgsql.connections.sum.prepared Preprocessing JSON Path: `$.prepared`
Connections sum: Total	Total number of connections	Dependent item	pgsql.connections.sum.total Preprocessing JSON Path: `$.total`
Connections sum: Total %	Total number of connections in percentage	Dependent item	pgsql.connections.sum.total_pct Preprocessing JSON Path: `$.total_pct`
Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	Dependent item	pgsql.connections.sum.waiting Preprocessing JSON Path: `$.waiting`
PostgreSQL: Get connections sum	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	Zabbix agent	pgsql.connections.sum["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	Zabbix agent	pgsql.dbstat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	Zabbix agent	pgsql.locks["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Ping time	Zabbix agent	pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing Regular expression: `Time:\s+(\d+\.\d+)\s+ms \1` Custom multiplier: `0.001`
Status: Ping	Zabbix agent	pgsql.ping["{$PG.HOST}","{$PG.PORT}"] Preprocessing JavaScript: `The text is too long. Please see the template.` Discard unchanged with heartbeat: `1h`
PostgreSQL: Get queries	Collect all metrics by query execution time	Zabbix agent	pgsql.queries["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{$PG.QUERY_ETIME.MAX.WARN}"]
Replication: standby count	Number of standby servers	Zabbix agent	pgsql.replication.count["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Replication: lag in seconds	Replication lag with Master in seconds	Zabbix agent	pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Replication: recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	Zabbix agent	pgsql.replication.recovery_role["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Replication: status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	Zabbix agent	pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Transactions: Max active transaction time	Current max active transaction time	Dependent item	pgsql.transactions.active Preprocessing JSON Path: `$.active`
Transactions: Max idle transaction time	Current max idle transaction time	Dependent item	pgsql.transactions.idle Preprocessing JSON Path: `$.idle`
Transactions: Max prepared transaction time	Current max prepared transaction time	Dependent item	pgsql.transactions.prepared Preprocessing JSON Path: `$.prepared`
Transactions: Max waiting transaction time	Current max waiting transaction time	Dependent item	pgsql.transactions.waiting Preprocessing JSON Path: `$.waiting`
PostgreSQL: Get transactions	Collect metrics by transaction execution time	Zabbix agent	pgsql.transactions["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Uptime	Zabbix agent	pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Status: Version	PostgreSQL version	Zabbix agent	pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing Discard unchanged with heartbeat: `1d`
WAL: Segments count	Number of WAL segments	Dependent item	pgsql.wal.count Preprocessing JSON Path: `$.count`
PostgreSQL: Get WAL	Master item to collect WAL metrics	Zabbix agent	pgsql.wal.stat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
WAL: Bytes written	WAL write in bytes	Dependent item	pgsql.wal.write Preprocessing JSON Path: `$.write` Change per second

Triggers

Name	Description	Expression	Severity	Dependencies and additional info
PostgreSQL: Required checkpoints occurs too frequently	Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint. At checkpoint time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log file.https://www.postgresql.org/docs/current/wal-configuration.html	`last(/PostgreSQL by Zabbix agent/pgsql.bgwriter.checkpoints_req.rate) > {$PG.CHECKPOINTS_REQ.MAX.WARN}`	Average
PostgreSQL: Failed to get items	Zabbix has not received any data for items for the last 30 minutes.	`nodata(/PostgreSQL by Zabbix agent/pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],30m) = 1`	Warning	Depends on: PostgreSQL: Service is down
PostgreSQL: Cache hit ratio too low	`max(/PostgreSQL by Zabbix agent/pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) < {$PG.CACHE_HITRATIO.MIN.WARN}`	Warning
PostgreSQL: Configuration has changed	`last(/PostgreSQL by Zabbix agent/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#1)<>last(/PostgreSQL by Zabbix agent/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#2) and length(last(/PostgreSQL by Zabbix agent/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]))>0`	Info
PostgreSQL: Total number of connections is too high	`min(/PostgreSQL by Zabbix agent/pgsql.connections.sum.total_pct,5m) > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	Average
PostgreSQL: Response too long	`min(/PostgreSQL by Zabbix agent/pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) > {$PG.PING_TIME.MAX.WARN}`	Average	Depends on: PostgreSQL: Service is down
PostgreSQL: Service is down	`last(/PostgreSQL by Zabbix agent/pgsql.ping["{$PG.HOST}","{$PG.PORT}"]) = 0`	High
PostgreSQL: Streaming lag with {#MASTER} is too high	`min(/PostgreSQL by Zabbix agent/pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) > {$PG.REPL_LAG.MAX.WARN}`	Average
PostgreSQL: Replication is down	`max(/PostgreSQL by Zabbix agent/pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m)=0`	Average
PostgreSQL: Service has been restarted	PostgreSQL uptime is less than 10 minutes.	`last(/PostgreSQL by Zabbix agent/pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]) < 10m`	Info
PostgreSQL: Version has changed	`last(/PostgreSQL by Zabbix agent/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#1)<>last(/PostgreSQL by Zabbix agent/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#2) and length(last(/PostgreSQL by Zabbix agent/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]))>0`	Info

LLD rule Database discovery

Name	Description	Type	Key and additional info
Database discovery	Zabbix agent	pgsql.discovery.db["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]

Item prototypes for Database discovery

Name	Description	Type	Key and additional info
DB {#DBNAME}: Database size	Database size	Zabbix agent	pgsql.db.size["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{#DBNAME}"]
DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	Dependent item	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blks_hit` Change per second
DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	Dependent item	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blks_read` Change per second
DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	Dependent item	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].conflicts` Change per second
DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	Dependent item	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].deadlocks` Change per second
DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	Dependent item	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].temp_bytes` Change per second
DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	Dependent item	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].temp_files` Change per second
DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	Dependent item	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_deleted` Change per second
DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	Dependent item	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_fetched` Change per second
DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	Dependent item	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_inserted` Change per second
DB {#DBNAME}: Tuples returned per second	Total number of rows updated by queries in this database	Dependent item	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_returned` Change per second
DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	Dependent item	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_updated` Change per second
DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	Dependent item	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].xact_commit` Change per second
DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	Dependent item	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].xact_rollback` Change per second
DB {#DBNAME}: Frozen XID before avtovacuum %	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	Dependent item	pgsql.frozenxid.prc_before_av["{#DBNAME}"] Preprocessing JSON Path: `$.prc_before_av`
DB {#DBNAME}: Frozen XID before stop %	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	Dependent item	pgsql.frozenxid.prc_before_stop["{#DBNAME}"] Preprocessing JSON Path: `$.prc_before_stop`
DB {#DBNAME}: Get frozen XID	Zabbix agent	pgsql.frozenxid["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
DB {#DBNAME}: Locks total	Total number of locks in the database	Dependent item	pgsql.locks.total["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].total`
DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	Dependent item	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_slow_count`
DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	Dependent item	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_time_max`
DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	Dependent item	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_time_sum`
DB {#DBNAME}: Queries slow query count	Slow query count	Dependent item	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_slow_count`
DB {#DBNAME}: Queries max query time	Max query time	Dependent item	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_time_max`
DB {#DBNAME}: Queries sum query time	Sum query time	Dependent item	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_time_sum`
DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	Dependent item	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_slow_count`
DB {#DBNAME}: Queries max transaction time	Max transaction query time	Dependent item	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_time_max`
DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	Dependent item	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_time_sum`
DB {#DBNAME}: Index scans per second	Number of index scans in the database	Dependent item	pgsql.scans.idx.rate["{#DBNAME}"] Preprocessing JSON Path: `$.idx` Change per second
DB {#DBNAME}: Sequential scans per second	Number of sequential scans in the database	Dependent item	pgsql.scans.seq.rate["{#DBNAME}"] Preprocessing JSON Path: `$.seq` Change per second
DB {#DBNAME}: Get scans	Number of scans done for table/index in the database	Zabbix agent	pgsql.scans["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]

Trigger prototypes for Database discovery

Name	Description	Expression	Severity
DB {#DBNAME}: Too many recovery conflicts	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them.https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`min(/PostgreSQL by Zabbix agent/pgsql.dbstat.conflicts.rate["{#DBNAME}"],5m) > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	Average
DB {#DBNAME}: Deadlock occurred	`min(/PostgreSQL by Zabbix agent/pgsql.dbstat.deadlocks.rate["{#DBNAME}"],5m) > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	High
DB {#DBNAME}: VACUUM FREEZE is required to prevent wraparound	Preventing Transaction ID Wraparound Failureshttps://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	`last(/PostgreSQL by Zabbix agent/pgsql.frozenxid.prc_before_stop["{#DBNAME}"])<{$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"}`	Average
DB {#DBNAME}: Number of locks is too high	`min(/PostgreSQL by Zabbix agent/pgsql.locks.total["{#DBNAME}"],5m)>{$PG.LOCKS.MAX.WARN:"{#DBNAME}"}`	Warning
DB {#DBNAME}: Too many slow queries	`min(/PostgreSQL by Zabbix agent/pgsql.queries.query.slow_count["{#DBNAME}"],5m)>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	Warning

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide feedback, discuss the template, or ask for help at ZABBIX forums

This template is for Zabbix version: 5.4

Also available for: 6.4 6.2 6.0 5.0

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql?at=release/5.4

PostgreSQL by user parameters

Overview

For Zabbix version: 5.4 and higher
Templates to monitor PostgreSQL by Zabbix. This template was tested on PostgreSQL versions 9.6, 10 and 11 on Linux and Windows.

Setup

Install Zabbix agent and create a read-only zbx_monitor user with proper access to your PostgreSQL server.

For PostgreSQL version 10 and above:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

For PostgreSQL version 9.6 and below:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>';
GRANT SELECT ON pg_stat_database TO zbx_monitor;

-- To collect WAL metrics, the user must have a `superuser` role.
ALTER USER zbx_monitor WITH SUPERUSER;

Copy postgresql/ to Zabbix agent home directory /var/lib/zabbix/. The postgresql/ directory contains the files needed to obtain metrics from PostgreSQL.
Copy template_db_postgresql.conf to Zabbix agent configuration directory /etc/zabbix/zabbix_agentd.d/ and restart Zabbix agent service.
Edit pg_hba.conf to allow connections from Zabbix agent https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Add rows (for example):
```
host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5
```
Import template file to Zabbix and link it to the target host
Set {$PG.HOST}, {$PG.PORT}, {$PG.USER}, {$PG.PASSWORD} and {$PG.DB} macros values.

Zabbix configuration

If PostgreSQL is installed from the PGDG repository, then add the path to pg_isready to the PATH environment variable for zabbix user.

Macros used

Name	Description	Default
{$PG.CACHE_HITRATIO.MIN.WARN}	-	`90`
{$PG.CHECKPOINTS_REQ.MAX.WARN}	-	`5`
{$PG.CONFLICTS.MAX.WARN}	-	`0`
{$PG.CONN_IDLE_IN_TRANS.MAX.WARN}	-	`5`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	-	`90`
{$PG.CONN_WAIT.MAX.WARN}	-	`0`
{$PG.DB}	-	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	-	`0`
{$PG.FROZENXID_PCT_STOP.MIN.HIGH}	-	`75`
{$PG.HOST}	-	`127.0.0.1`
{$PG.LLD.FILTER.DBNAME}	-	`(.*)`
{$PG.LOCKS.MAX.WARN}	-	`100`
{$PG.PASSWORD}	Please set user's password in this macro.	``
{$PG.PING_TIME.MAX.WARN}	-	`1s`
{$PG.PORT}	-	`5432`
{$PG.QUERY_ETIME.MAX.WARN}	-	`30`
{$PG.REPL_LAG.MAX.WARN}	-	`10m`
{$PG.SLOW_QUERIES.MAX.WARN}	-	`5`
{$PG.TRANS_ACTIVE.MAX.WARN}	-	`30s`
{$PG.TRANS_IDLE.MAX.WARN}	-	`30s`
{$PG.TRANS_WAIT.MAX.WARN}	-	`30s`
{$PG.USER}	-	`zbx_monitor`

Template links

There are no template links in this template.

Discovery rules

Name Description Type Key and additional info

Database discovery

Name	Description	Type	Key and additional info
Database discovery	-	ZABBIX_PASSIVE	pgsql.discovery.db["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Filter: - {#DBNAME} MATCHES_REGEX `{$PG.LLD.FILTER.DBNAME}`

ZABBIX_PASSIVE

pgsql.discovery.db["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]

Filter:

- {#DBNAME} MATCHES_REGEX {$PG.LLD.FILTER.DBNAME}

Items collected

Group	Name	Description	Type	Key and additional info
PostgreSQL	Bgwriter: Buffers allocated per second	Number of buffers allocated	DEPENDENT	pgsql.bgwriter.buffers_alloc.rate Preprocessing: - JSONPATH: `$.buffers_alloc` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written directly by a backend per second	Number of buffers written directly by a backend	DEPENDENT	pgsql.bgwriter.buffers_backend.rate Preprocessing: - JSONPATH: `$.buffers_backend` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers backend fsync per second	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	DEPENDENT	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing: - JSONPATH: `$.buffers_backend_fsync` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written during checkpoints per second	Number of buffers written during checkpoints	DEPENDENT	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing: - JSONPATH: `$.buffers_checkpoint` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written by the background writer per second	Number of buffers written by the background writer	DEPENDENT	pgsql.bgwriter.buffers_clean.rate Preprocessing: - JSONPATH: `$.buffers_clean` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Requested checkpoints per second	Number of requested checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_req.rate Preprocessing: - JSONPATH: `$.checkpoints_req` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Scheduled checkpoints per second	Number of scheduled checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_timed.rate Preprocessing: - JSONPATH: `$.checkpoints_timed` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	DEPENDENT	pgsql.bgwriter.checkpoint_sync_time Preprocessing: - JSONPATH: `$.checkpoint_sync_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	DEPENDENT	pgsql.bgwriter.checkpoint_write_time Preprocessing: - JSONPATH: `$.checkpoint_write_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Max written per second	Number of times the background writer stopped a cleaning scan because it had written too many buffers	DEPENDENT	pgsql.bgwriter.maxwritten_clean.rate Preprocessing: - JSONPATH: `$.maxwritten_clean` - CHANGE_PER_SECOND
PostgreSQL	Status: Cache hit ratio %	Cache hit ratio	ZABBIX_PASSIVE	pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Status: Config hash	PostgreSQL configuration hash	ZABBIX_PASSIVE	pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing: - DISCARD_UNCHANGED_HEARTBEAT: `1h`
PostgreSQL	Connections sum: Active	Total number of connections executing a query	DEPENDENT	pgsql.connections.sum.active Preprocessing: - JSONPATH: `$.active`
PostgreSQL	Connections sum: Idle	Total number of connections waiting for a new client command	DEPENDENT	pgsql.connections.sum.idle Preprocessing: - JSONPATH: `$.idle`
PostgreSQL	Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	DEPENDENT	pgsql.connections.sum.idle_in_transaction Preprocessing: - JSONPATH: `$.idle_in_transaction`
PostgreSQL	Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	DEPENDENT	pgsql.connections.sum.prepared Preprocessing: - JSONPATH: `$.prepared`
PostgreSQL	Connections sum: Total	Total number of connections	DEPENDENT	pgsql.connections.sum.total Preprocessing: - JSONPATH: `$.total`
PostgreSQL	Connections sum: Total %	Total number of connections in percentage	DEPENDENT	pgsql.connections.sum.total_pct Preprocessing: - JSONPATH: `$.total_pct`
PostgreSQL	Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	DEPENDENT	pgsql.connections.sum.waiting Preprocessing: - JSONPATH: `$.waiting`
PostgreSQL	Status: Ping time	-	ZABBIX_PASSIVE	pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing: - REGEX: `Time:\s+(\d+\.\d+)\s+ms \1` - MULTIPLIER: `0.001`
PostgreSQL	Status: Ping	-	ZABBIX_PASSIVE	pgsql.ping["{$PG.HOST}","{$PG.PORT}"] Preprocessing: - JAVASCRIPT: `return value.search(/accepting connections/)>0 ? 1 : 0` - DISCARD_UNCHANGED_HEARTBEAT: `1h`
PostgreSQL	Replication: standby count	Number of standby servers	ZABBIX_PASSIVE	pgsql.replication.count["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Replication: lag in seconds	Replication lag with Master in seconds	ZABBIX_PASSIVE	pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Replication: recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	ZABBIX_PASSIVE	pgsql.replication.recovery_role["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Replication: status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	ZABBIX_PASSIVE	pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Transactions: Max active transaction time	Current max active transaction time	DEPENDENT	pgsql.transactions.active Preprocessing: - JSONPATH: `$.active`
PostgreSQL	Transactions: Max idle transaction time	Current max idle transaction time	DEPENDENT	pgsql.transactions.idle Preprocessing: - JSONPATH: `$.idle`
PostgreSQL	Transactions: Max prepared transaction time	Current max prepared transaction time	DEPENDENT	pgsql.transactions.prepared Preprocessing: - JSONPATH: `$.prepared`
PostgreSQL	Transactions: Max waiting transaction time	Current max waiting transaction time	DEPENDENT	pgsql.transactions.waiting Preprocessing: - JSONPATH: `$.waiting`
PostgreSQL	Status: Uptime	-	ZABBIX_PASSIVE	pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Status: Version	PostgreSQL version	ZABBIX_PASSIVE	pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing: - DISCARD_UNCHANGED_HEARTBEAT: `1d`
PostgreSQL	WAL: Segments count	Number of WAL segments	DEPENDENT	pgsql.wal.count Preprocessing: - JSONPATH: `$.count`
PostgreSQL	WAL: Bytes written	WAL write in bytes	DEPENDENT	pgsql.wal.write Preprocessing: - JSONPATH: `$.write` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Database size	Database size	ZABBIX_PASSIVE	pgsql.db.size["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	DEPENDENT	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_hit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	DEPENDENT	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_read` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	DEPENDENT	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].conflicts` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	DEPENDENT	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].deadlocks` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	DEPENDENT	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_bytes` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	DEPENDENT	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_files` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	DEPENDENT	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_deleted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	DEPENDENT	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_fetched` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	DEPENDENT	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_inserted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples returned per second	Total number of rows updated by queries in this database	DEPENDENT	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_returned` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	DEPENDENT	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_updated` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	DEPENDENT	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_commit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	DEPENDENT	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_rollback` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Frozen XID before avtovacuum %	reventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	DEPENDENT	pgsql.frozenxid.prc_before_av["{#DBNAME}"] Preprocessing: - JSONPATH: `$.prc_before_av`
PostgreSQL	DB {#DBNAME}: Frozen XID before stop %	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	DEPENDENT	pgsql.frozenxid.prc_before_stop["{#DBNAME}"] Preprocessing: - JSONPATH: `$.prc_before_stop`
PostgreSQL	DB {#DBNAME}: Locks total	Total number of locks in the database	DEPENDENT	pgsql.locks.total["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].total`
PostgreSQL	DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	DEPENDENT	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_slow_count`
PostgreSQL	DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	DEPENDENT	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_max`
PostgreSQL	DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	DEPENDENT	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_sum`
PostgreSQL	DB {#DBNAME}: Queries slow query count	Slow query count	DEPENDENT	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_slow_count`
PostgreSQL	DB {#DBNAME}: Queries max query time	Max query time	DEPENDENT	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_max`
PostgreSQL	DB {#DBNAME}: Queries sum query time	Sum query time	DEPENDENT	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_sum`
PostgreSQL	DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	DEPENDENT	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_slow_count`
PostgreSQL	DB {#DBNAME}: Queries max transaction time	Max transaction query time	DEPENDENT	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_max`
PostgreSQL	DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	DEPENDENT	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_sum`
PostgreSQL	DB {#DBNAME}: Index scans per second	Number of index scans in the database	DEPENDENT	pgsql.scans.idx.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$.idx` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Sequential scans per second	Number of sequential scans in the database	DEPENDENT	pgsql.scans.seq.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$.seq` - CHANGE_PER_SECOND
Zabbix_raw_items	PostgreSQL: Get bgwriter	Statistics about the background writer process's activity	ZABBIX_PASSIVE	pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get connections sum	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	ZABBIX_PASSIVE	pgsql.connections.sum["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	ZABBIX_PASSIVE	pgsql.dbstat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	ZABBIX_PASSIVE	pgsql.locks["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get queries	Collect all metrics by query execution time	ZABBIX_PASSIVE	pgsql.queries["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{$PG.QUERY_ETIME.MAX.WARN}"]
Zabbix_raw_items	PostgreSQL: Get transactions	Collect metrics by transaction execution time	ZABBIX_PASSIVE	pgsql.transactions["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get WAL	Master item to collect WAL metrics	ZABBIX_PASSIVE	pgsql.wal.stat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	DB {#DBNAME}: Get frozen XID	-	ZABBIX_PASSIVE	pgsql.frozenxid["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
Zabbix_raw_items	DB {#DBNAME}: Get scans	Number of scans done for table/index in the database	ZABBIX_PASSIVE	pgsql.scans["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]

Triggers

Name	Description	Expression	Severity	Dependencies and additional info
PostgreSQL: Required checkpoints occurs too frequently (over {$PG.CHECKPOINTS_REQ.MAX.WARN})	Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint. At checkpoint time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log file. https://www.postgresql.org/docs/current/wal-configuration.html	`last(/PostgreSQL by user parameters/pgsql.bgwriter.checkpoints_req.rate) > {$PG.CHECKPOINTS_REQ.MAX.WARN}`	AVERAGE
PostgreSQL: Cache hit ratio too low (under {$PG.CACHE_HITRATIO.MIN.WARN} in 5m)	-	`max(/PostgreSQL by user parameters/pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) < {$PG.CACHE_HITRATIO.MIN.WARN}`	WARNING
PostgreSQL: Configuration has changed	-	`last(/PostgreSQL by user parameters/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#1)<>last(/PostgreSQL by user parameters/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#2) and length(last(/PostgreSQL by user parameters/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]))>0`	INFO
PostgreSQL: Total number of connections is too high (over {$PG.CONN_TOTAL_PCT.MAX.WARN} in 5m)	-	`min(/PostgreSQL by user parameters/pgsql.connections.sum.total_pct,5m) > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	AVERAGE
PostgreSQL: Response too long (over {$PG.PING_TIME.MAX.WARN})	-	`min(/PostgreSQL by user parameters/pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) > {$PG.PING_TIME.MAX.WARN}`	AVERAGE	Depends on: - PostgreSQL: Service is down
PostgreSQL: Service is down	-	`last(/PostgreSQL by user parameters/pgsql.ping["{$PG.HOST}","{$PG.PORT}"]) = 0`	HIGH
PostgreSQL: Streaming lag with {#MASTER} is too high (over {$PG.REPL_LAG.MAX.WARN} in 5m)	-	`min(/PostgreSQL by user parameters/pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m) > {$PG.REPL_LAG.MAX.WARN}`	AVERAGE
PostgreSQL: Replication is down	-	`max(/PostgreSQL by user parameters/pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],5m)=0`	AVERAGE
PostgreSQL: Service has been restarted (uptime < 10m)	PostgreSQL uptime is less than 10 minutes	`last(/PostgreSQL by user parameters/pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]) < 10m`	INFO
PostgreSQL: Version has changed (new version value received: {ITEM.VALUE})	-	`last(/PostgreSQL by user parameters/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#1)<>last(/PostgreSQL by user parameters/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],#2) and length(last(/PostgreSQL by user parameters/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]))>0`	INFO
DB {#DBNAME}: Too many recovery conflicts (over {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"} in 5m)	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them. https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`min(/PostgreSQL by user parameters/pgsql.dbstat.conflicts.rate["{#DBNAME}"],5m) > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	AVERAGE
DB {#DBNAME}: Deadlock occurred (over {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"} in 5m)	-	`min(/PostgreSQL by user parameters/pgsql.dbstat.deadlocks.rate["{#DBNAME}"],5m) > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	HIGH
DB {#DBNAME}: VACUUM FREEZE is required to prevent wraparound (frozen XID less than {$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"} %)	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	`last(/PostgreSQL by user parameters/pgsql.frozenxid.prc_before_stop["{#DBNAME}"])<{$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"}`	AVERAGE
DB {#DBNAME}: Number of locks is too high (over {$PG.LOCKS.MAX.WARN:"{#DBNAME}"} in 5m)	-	`min(/PostgreSQL by user parameters/pgsql.locks.total["{#DBNAME}"],5m)>{$PG.LOCKS.MAX.WARN:"{#DBNAME}"}`	WARNING
DB {#DBNAME}: Too many slow queries (over {$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"} in 5m)	-	`min(/PostgreSQL by user parameters/pgsql.queries.query.slow_count["{#DBNAME}"],5m)>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	WARNING
PostgreSQL: Failed to get items (no data for 30m)	Zabbix has not received data for items for the last 30 minutes	`nodata(/PostgreSQL by user parameters/pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"],30m) = 1`	WARNING	Depends on: - PostgreSQL: Service is down

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide a feedback, discuss the template or ask for help with it at ZABBIX forums.

This template is for Zabbix version: 5.0

Also available for: 6.4 6.2 6.0 5.4

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql?at=release/5.0

Template DB PostgreSQL by user parameters

Overview

For Zabbix version: 5.0 and higher
Templates to monitor PostgreSQL by Zabbix. This template was tested on PostgreSQL versions 9.6, 10 and 11 on Linux and Windows.

Setup

Install Zabbix agent and create a read-only zbx_monitor user with proper access to your PostgreSQL server.

For PostgreSQL version 10 and above:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

For PostgreSQL version 9.6 and below:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>';
GRANT SELECT ON pg_stat_database TO zbx_monitor;

-- To collect WAL metrics, the user must have a `superuser` role.
ALTER USER zbx_monitor WITH SUPERUSER;

Copy postgresql/ to Zabbix agent home directory /var/lib/zabbix/. The postgresql/ directory contains the files needed to obtain metrics from PostgreSQL.
Copy template_db_postgresql.conf to Zabbix agent configuration directory /etc/zabbix/zabbix_agentd.d/ and restart Zabbix agent service.
Edit pg_hba.conf to allow connections from Zabbix agent https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Add rows (for example):
```
host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5
```
Import template file to Zabbix and link it to the target host
Set {$PG.HOST}, {$PG.PORT}, {$PG.USER}, {$PG.PASSWORD} and {$PG.DB} macros values.

Zabbix configuration

If PostgreSQL is installed from the PGDG repository, then add the path to pg_isready to the PATH environment variable for zabbix user.

Macros used

Name	Description	Default
{$PG.CACHE_HITRATIO.MIN.WARN}	-	`90`
{$PG.CHECKPOINTS_REQ.MAX.WARN}	-	`5`
{$PG.CONFLICTS.MAX.WARN}	-	`0`
{$PG.CONN_IDLE_IN_TRANS.MAX.WARN}	-	`5`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	-	`90`
{$PG.CONN_WAIT.MAX.WARN}	-	`0`
{$PG.DB}	-	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	-	`0`
{$PG.FROZENXID_PCT_STOP.MIN.HIGH}	-	`75`
{$PG.HOST}	-	`127.0.0.1`
{$PG.LLD.FILTER.DBNAME}	-	`(.*)`
{$PG.LOCKS.MAX.WARN}	-	`100`
{$PG.PASSWORD}	Please set user's password in this macro.	``
{$PG.PING_TIME.MAX.WARN}	-	`1s`
{$PG.PORT}	-	`5432`
{$PG.QUERY_ETIME.MAX.WARN}	-	`30`
{$PG.REPL_LAG.MAX.WARN}	-	`10m`
{$PG.SLOW_QUERIES.MAX.WARN}	-	`5`
{$PG.TRANS_ACTIVE.MAX.WARN}	-	`30s`
{$PG.TRANS_IDLE.MAX.WARN}	-	`30s`
{$PG.TRANS_WAIT.MAX.WARN}	-	`30s`
{$PG.USER}	-	`zbx_monitor`

Template links

There are no template links in this template.

Discovery rules

Name Description Type Key and additional info

Database discovery

Name	Description	Type	Key and additional info
Database discovery	-	ZABBIX_PASSIVE	pgsql.discovery.db["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Filter: - A: {#DBNAME} MATCHES_REGEX `{$PG.LLD.FILTER.DBNAME}`

ZABBIX_PASSIVE

pgsql.discovery.db["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]

Filter:

- A: {#DBNAME} MATCHES_REGEX {$PG.LLD.FILTER.DBNAME}

Items collected

Group	Name	Description	Type	Key and additional info
PostgreSQL	Bgwriter: Buffers allocated per second	Number of buffers allocated	DEPENDENT	pgsql.bgwriter.buffers_alloc.rate Preprocessing: - JSONPATH: `$.buffers_alloc` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written directly by a backend per second	Number of buffers written directly by a backend	DEPENDENT	pgsql.bgwriter.buffers_backend.rate Preprocessing: - JSONPATH: `$.buffers_backend` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers backend fsync per second	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	DEPENDENT	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing: - JSONPATH: `$.buffers_backend_fsync` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written during checkpoints per second	Number of buffers written during checkpoints	DEPENDENT	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing: - JSONPATH: `$.buffers_checkpoint` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written by the background writer per second	Number of buffers written by the background writer	DEPENDENT	pgsql.bgwriter.buffers_clean.rate Preprocessing: - JSONPATH: `$.buffers_clean` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Requested checkpoints per second	Number of requested checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_req.rate Preprocessing: - JSONPATH: `$.checkpoints_req` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Scheduled checkpoints per second	Number of scheduled checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_timed.rate Preprocessing: - JSONPATH: `$.checkpoints_timed` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	DEPENDENT	pgsql.bgwriter.checkpoint_sync_time Preprocessing: - JSONPATH: `$.checkpoint_sync_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	DEPENDENT	pgsql.bgwriter.checkpoint_write_time Preprocessing: - JSONPATH: `$.checkpoint_write_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Max written per second	Number of times the background writer stopped a cleaning scan because it had written too many buffers	DEPENDENT	pgsql.bgwriter.maxwritten_clean.rate Preprocessing: - JSONPATH: `$.maxwritten_clean` - CHANGE_PER_SECOND
PostgreSQL	Status: Cache hit ratio %	Cache hit ratio	ZABBIX_PASSIVE	pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Status: Config hash	PostgreSQL configuration hash	ZABBIX_PASSIVE	pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing: - DISCARD_UNCHANGED_HEARTBEAT: `1h`
PostgreSQL	Connections sum: Active	Total number of connections executing a query	DEPENDENT	pgsql.connections.sum.active Preprocessing: - JSONPATH: `$.active`
PostgreSQL	Connections sum: Idle	Total number of connections waiting for a new client command	DEPENDENT	pgsql.connections.sum.idle Preprocessing: - JSONPATH: `$.idle`
PostgreSQL	Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	DEPENDENT	pgsql.connections.sum.idle_in_transaction Preprocessing: - JSONPATH: `$.idle_in_transaction`
PostgreSQL	Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	DEPENDENT	pgsql.connections.sum.prepared Preprocessing: - JSONPATH: `$.prepared`
PostgreSQL	Connections sum: Total	Total number of connections	DEPENDENT	pgsql.connections.sum.total Preprocessing: - JSONPATH: `$.total`
PostgreSQL	Connections sum: Total %	Total number of connections in percentage	DEPENDENT	pgsql.connections.sum.total_pct Preprocessing: - JSONPATH: `$.total_pct`
PostgreSQL	Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	DEPENDENT	pgsql.connections.sum.waiting Preprocessing: - JSONPATH: `$.waiting`
PostgreSQL	Status: Ping time	-	ZABBIX_PASSIVE	pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing: - REGEX: `Time:\s+(\d+\.\d+)\s+ms \1` - MULTIPLIER: `0.001`
PostgreSQL	Status: Ping	-	ZABBIX_PASSIVE	pgsql.ping["{$PG.HOST}","{$PG.PORT}"] Preprocessing: - JAVASCRIPT: `return value.search(/accepting connections/)>0 ? 1 : 0` - DISCARD_UNCHANGED_HEARTBEAT: `1h`
PostgreSQL	Replication: standby count	Number of standby servers	ZABBIX_PASSIVE	pgsql.replication.count["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Replication: lag in seconds	Replication lag with Master in seconds	ZABBIX_PASSIVE	pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Replication: recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	ZABBIX_PASSIVE	pgsql.replication.recovery_role["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Replication: status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	ZABBIX_PASSIVE	pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Transactions: Max active transaction time	Current max active transaction time	DEPENDENT	pgsql.transactions.active Preprocessing: - JSONPATH: `$.active`
PostgreSQL	Transactions: Max idle transaction time	Current max idle transaction time	DEPENDENT	pgsql.transactions.idle Preprocessing: - JSONPATH: `$.idle`
PostgreSQL	Transactions: Max prepared transaction time	Current max prepared transaction time	DEPENDENT	pgsql.transactions.prepared Preprocessing: - JSONPATH: `$.prepared`
PostgreSQL	Transactions: Max waiting transaction time	Current max waiting transaction time	DEPENDENT	pgsql.transactions.waiting Preprocessing: - JSONPATH: `$.waiting`
PostgreSQL	Status: Uptime	-	ZABBIX_PASSIVE	pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
PostgreSQL	Status: Version	PostgreSQL version	ZABBIX_PASSIVE	pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"] Preprocessing: - DISCARD_UNCHANGED_HEARTBEAT: `1d`
PostgreSQL	WAL: Segments count	Number of WAL segments	DEPENDENT	pgsql.wal.count Preprocessing: - JSONPATH: `$.count`
PostgreSQL	WAL: Bytes written	WAL write in bytes	DEPENDENT	pgsql.wal.write Preprocessing: - JSONPATH: `$.write` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Database size	Database size	ZABBIX_PASSIVE	pgsql.db.size["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	DEPENDENT	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_hit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	DEPENDENT	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_read` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	DEPENDENT	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].conflicts` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	DEPENDENT	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].deadlocks` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	DEPENDENT	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_bytes` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	DEPENDENT	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_files` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	DEPENDENT	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_deleted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	DEPENDENT	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_fetched` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	DEPENDENT	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_inserted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples returned per second	Total number of rows updated by queries in this database	DEPENDENT	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_returned` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	DEPENDENT	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_updated` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	DEPENDENT	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_commit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	DEPENDENT	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_rollback` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Frozen XID before avtovacuum %	reventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	DEPENDENT	pgsql.frozenxid.prc_before_av["{#DBNAME}"] Preprocessing: - JSONPATH: `$.prc_before_av`
PostgreSQL	DB {#DBNAME}: Frozen XID before stop %	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	DEPENDENT	pgsql.frozenxid.prc_before_stop["{#DBNAME}"] Preprocessing: - JSONPATH: `$.prc_before_stop`
PostgreSQL	DB {#DBNAME}: Locks total	Total number of locks in the database	DEPENDENT	pgsql.locks.total["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].total`
PostgreSQL	DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	DEPENDENT	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_slow_count`
PostgreSQL	DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	DEPENDENT	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_max`
PostgreSQL	DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	DEPENDENT	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_sum`
PostgreSQL	DB {#DBNAME}: Queries slow query count	Slow query count	DEPENDENT	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_slow_count`
PostgreSQL	DB {#DBNAME}: Queries max query time	Max query time	DEPENDENT	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_max`
PostgreSQL	DB {#DBNAME}: Queries sum query time	Sum query time	DEPENDENT	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_sum`
PostgreSQL	DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	DEPENDENT	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_slow_count`
PostgreSQL	DB {#DBNAME}: Queries max transaction time	Max transaction query time	DEPENDENT	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_max`
PostgreSQL	DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	DEPENDENT	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_sum`
PostgreSQL	DB {#DBNAME}: Index scans per second	Number of index scans in the database	DEPENDENT	pgsql.scans.idx.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$.idx` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Sequential scans per second	Number of sequential scans in the database	DEPENDENT	pgsql.scans.seq.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$.seq` - CHANGE_PER_SECOND
Zabbix_raw_items	PostgreSQL: Get bgwriter	Statistics about the background writer process's activity	ZABBIX_PASSIVE	pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get connections sum	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	ZABBIX_PASSIVE	pgsql.connections.sum["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	ZABBIX_PASSIVE	pgsql.dbstat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	ZABBIX_PASSIVE	pgsql.locks["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get queries	Collect all metrics by query execution time	ZABBIX_PASSIVE	pgsql.queries["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}","{$PG.QUERY_ETIME.MAX.WARN}"]
Zabbix_raw_items	PostgreSQL: Get transactions	Collect metrics by transaction execution time	ZABBIX_PASSIVE	pgsql.transactions["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	PostgreSQL: Get WAL	Master item to collect WAL metrics	ZABBIX_PASSIVE	pgsql.wal.stat["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"]
Zabbix_raw_items	DB {#DBNAME}: Get frozen XID	-	ZABBIX_PASSIVE	pgsql.frozenxid["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
Zabbix_raw_items	DB {#DBNAME}: Get scans	Number of scans done for table/index in the database	ZABBIX_PASSIVE	pgsql.scans["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]

Triggers

Name	Description	Expression	Severity	Dependencies and additional info
PostgreSQL: Required checkpoints occurs too frequently (over {$PG.CHECKPOINTS_REQ.MAX.WARN})	Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint. At checkpoint time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log file. https://www.postgresql.org/docs/current/wal-configuration.html	`{TEMPLATE_NAME:pgsql.bgwriter.checkpoints_req.rate.last()} > {$PG.CHECKPOINTS_REQ.MAX.WARN}`	AVERAGE
PostgreSQL: Cache hit ratio too low (under {$PG.CACHE_HITRATIO.MIN.WARN} in 5m)	-	`{TEMPLATE_NAME:pgsql.cache.hit["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].max(5m)} < {$PG.CACHE_HITRATIO.MIN.WARN}`	WARNING
PostgreSQL: Configuration has changed	-	`{TEMPLATE_NAME:pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].diff()}=1 and {TEMPLATE_NAME:pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].strlen()}>0`	INFO
PostgreSQL: Total number of connections is too high (over {$PG.CONN_TOTAL_PCT.MAX.WARN} in 5m)	-	`{TEMPLATE_NAME:pgsql.connections.sum.total_pct.min(5m)} > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	AVERAGE
PostgreSQL: Response too long (over {$PG.PING_TIME.MAX.WARN})	-	`{TEMPLATE_NAME:pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].min(5m)} > {$PG.PING_TIME.MAX.WARN}`	AVERAGE	Depends on: - PostgreSQL: Service is down
PostgreSQL: Service is down	-	`{TEMPLATE_NAME:pgsql.ping["{$PG.HOST}","{$PG.PORT}"].last()} = 0`	HIGH
PostgreSQL: Streaming lag with {#MASTER} is too high (over {$PG.REPL_LAG.MAX.WARN} in 5m)	-	`{TEMPLATE_NAME:pgsql.replication.lag.sec["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].min(5m)} > {$PG.REPL_LAG.MAX.WARN}`	AVERAGE
PostgreSQL: Replication is down	-	`{TEMPLATE_NAME:pgsql.replication.status["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].max(5m)}=0`	AVERAGE
PostgreSQL: Service has been restarted (uptime < 10m)	PostgreSQL uptime is less than 10 minutes	`{TEMPLATE_NAME:pgsql.uptime["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].last()} < 10m`	INFO
PostgreSQL: Version has changed (new version value received: {ITEM.VALUE})	-	`{TEMPLATE_NAME:pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].diff()}=1 and {TEMPLATE_NAME:pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].strlen()}>0`	INFO
DB {#DBNAME}: Too many recovery conflicts (over {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"} in 5m)	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them. https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`{TEMPLATE_NAME:pgsql.dbstat.conflicts.rate["{#DBNAME}"].min(5m)} > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	AVERAGE
DB {#DBNAME}: Deadlock occurred (over {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"} in 5m)	-	`{TEMPLATE_NAME:pgsql.dbstat.deadlocks.rate["{#DBNAME}"].min(5m)} > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	HIGH
DB {#DBNAME}: VACUUM FREEZE is required to prevent wraparound (frozen XID less than {$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"} %)	Preventing Transaction ID Wraparound Failures https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND	`{TEMPLATE_NAME:pgsql.frozenxid.prc_before_stop["{#DBNAME}"].last()}<{$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"}`	AVERAGE
DB {#DBNAME}: Number of locks is too high (over {$PG.LOCKS.MAX.WARN:"{#DBNAME}"} in 5m)	-	`{TEMPLATE_NAME:pgsql.locks.total["{#DBNAME}"].min(5m)}>{$PG.LOCKS.MAX.WARN:"{#DBNAME}"}`	WARNING
DB {#DBNAME}: Too many slow queries (over {$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"} in 5m)	-	`{TEMPLATE_NAME:pgsql.queries.query.slow_count["{#DBNAME}"].min(5m)}>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	WARNING
PostgreSQL: Failed to get items (no data for 30m)	Zabbix has not received data for items for the last 30 minutes	`{TEMPLATE_NAME:pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DB}"].nodata(30m)} = 1`	WARNING	Depends on: - PostgreSQL: Service is down

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide a feedback, discuss the template or ask for help with it at ZABBIX forums.

This template is for Zabbix version: 6.4

Also available for: 6.2 6.0 5.4 5.0

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql_agent2?at=release/6.4

PostgreSQL by Zabbix agent 2

Overview

This template is designed for the effortless deployment of PostgreSQL monitoring by Zabbix via Zabbix agent 2 and doesn't require any external scripts.

Requirements

Zabbix version: 6.4 and higher.

Tested versions

This template has been tested on:

PostgreSQL 10-15

Configuration

Setup

Deploy Zabbix agent2 with Postgres plugin. Starting with Zabbix versions 6.0.10 / 6.2.4 / 6.4 postgres metrics moved to a loadable plugin and requires separate package installation or compilation of a plugin from sources.
Create PostgreSQL user to monitor (<password> at your discretion) and inherit permissions from the default role pg_monitor:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

Edit pg_hba.conf to allow connections from Zabbix agent:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host       all        zbx_monitor     localhost               md5

For more information please read the PostgreSQL documentation https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Set in the {$PG.URI} macro the system data source name of the PostgreSQL instance such as <protocol(host:port)>.
Set the user name and password in host macros ({$PG.USER} and {$PG.PASSWORD}) if you want to override parameters from the Zabbix agent configuration file.

Macros used

Name	Description	Default
{$PG.PASSWORD}	`postgres`
{$PG.URI}	`tcp://localhost:5432`
{$PG.USER}	`postgres`
{$PG.LLD.FILTER.DBNAME}	`(.+)`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	`90`
{$PG.DATABASE}	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	`0`
{$PG.LLD.FILTER.APPLICATION}	`(.+)`
{$PG.CONFLICTS.MAX.WARN}	`0`
{$PG.QUERY_ETIME.MAX.WARN}	Execution time limit for count of slow queries.	`30`
{$PG.SLOW_QUERIES.MAX.WARN}	Slow queries count threshold for a trigger.	`5`

Items

Name	Description	Type	Key and additional info
PostgreSQL: Get bgwriter	https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-BGWRITER-VIEW	Zabbix agent	pgsql.bgwriter["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get archive	Collect archive status metrics	Zabbix agent	pgsql.archive["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	Zabbix agent	pgsql.dbstat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get dbstat sum	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	Zabbix agent	pgsql.dbstat.sum["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get connections	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	Zabbix agent	pgsql.connections["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get WAL	Collect WAL metrics	Zabbix agent	pgsql.wal.stat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	Zabbix agent	pgsql.locks["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Custom queries	Execute custom queries from file *.sql (check for option Plugins.Postgres.CustomQueriesPath at agent configuration)	Zabbix agent	pgsql.custom.query["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}",""]
PostgreSQL: Get replication	Collect metrics from the pg_stat_replication, which contains information about the WAL sender process, showing statistics about replication to that sender's connected standby server.	Zabbix agent	pgsql.replication.process["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get queries	Collect all metrics by query execution time	Zabbix agent	pgsql.queries["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}","{$PG.QUERY_ETIME.MAX.WARN}"]
WAL: Bytes written	WAL write in bytes	Dependent item	pgsql.wal.write Preprocessing JSON Path: `$.write` Change per second
WAL: Bytes received	WAL receive in bytes	Dependent item	pgsql.wal.receive Preprocessing JSON Path: `$.receive` Change per second
WAL: Segments count	Number of WAL segments	Dependent item	pgsql.wal.count Preprocessing JSON Path: `$.count`
Bgwriter: Buffers allocated	Number of buffers allocated	Dependent item	pgsql.bgwriter.buffers_alloc.rate Preprocessing JSON Path: `$.buffers_alloc` Change per second
Bgwriter: Buffers written directly by a backend	Number of buffers written directly by a backend	Dependent item	pgsql.bgwriter.buffers_backend.rate Preprocessing JSON Path: `$.buffers_backend` Change per second
Bgwriter: Number of bgwriter stopped	Number of times the background writer stopped a cleaning scan because it had written too many buffers	Dependent item	pgsql.bgwriter.maxwritten_clean.rate Preprocessing JSON Path: `$.maxwritten_clean` Change per second
Bgwriter: Times a backend execute its own fsync	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	Dependent item	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing JSON Path: `$.buffers_backend_fsync` Change per second
Checkpoint: Buffers background written	Number of buffers written by the background writer	Dependent item	pgsql.bgwriter.buffers_clean.rate Preprocessing JSON Path: `$.buffers_clean` Change per second
Checkpoint: Buffers checkpoints written	Number of buffers written during checkpoints	Dependent item	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing JSON Path: `$.buffers_checkpoint` Change per second
Checkpoint: By timeout	Number of scheduled checkpoints that have been performed	Dependent item	pgsql.bgwriter.checkpoints_timed.rate Preprocessing JSON Path: `$.checkpoints_timed` Change per second
Checkpoint: Requested	Number of requested checkpoints that have been performed	Dependent item	pgsql.bgwriter.checkpoints_req.rate Preprocessing JSON Path: `$.checkpoints_req` Change per second
Checkpoint: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	Dependent item	pgsql.bgwriter.checkpoint_write_time.rate Preprocessing JSON Path: `$.checkpoint_write_time` Custom multiplier: `0.001` Change per second
Checkpoint: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	Dependent item	pgsql.bgwriter.checkpoint_sync_time.rate Preprocessing JSON Path: `$.checkpoint_sync_time` Custom multiplier: `0.001` Change per second
Archive: Count of archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	Dependent item	pgsql.archive.count_archived_files Preprocessing JSON Path: `$.archived_count`
Archive: Count of attempts to archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	Dependent item	pgsql.archive.failed_trying_to_archive Preprocessing JSON Path: `$.failed_count`
Archive: Count of files in archive_status need to archive	Dependent item	pgsql.archive.count_files_to_archive Preprocessing JSON Path: `$.count_files`
Archive: Count of files need to archive	Size of files to archive	Dependent item	pgsql.archive.size_files_to_archive Preprocessing JSON Path: `$.size_files`
Dbstat: Blocks read time	Time spent reading data file blocks by backends, in milliseconds	Dependent item	pgsql.dbstat.sum.blk_read_time Preprocessing JSON Path: `$.blk_read_time` Custom multiplier: `0.001`
Dbstat: Blocks write time	Time spent writing data file blocks by backends, in milliseconds	Dependent item	pgsql.dbstat.sum.blk_write_time Preprocessing JSON Path: `$.blk_write_time` Custom multiplier: `0.001`
Dbstat: Checksum failures	Number of data page checksum failures detected (or on a shared object), or NULL if data checksums are not enabled. This metric included in PostgreSQL 12	Dependent item	pgsql.dbstat.sum.checksum_failures.rate Preprocessing JSON Path: `$.checksum_failures` Matches regular expression: `^\d*$` ⛔️Custom on fail: Set value to: `-2` Change per second ⛔️Custom on fail: Set value to: `-1`
Dbstat: Committed transactions	Number of transactions that have been committed	Dependent item	pgsql.dbstat.sum.xact_commit.rate Preprocessing JSON Path: `$.xact_commit` Change per second
Dbstat: Conflicts	Number of queries canceled due to conflicts with recovery. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.)	Dependent item	pgsql.dbstat.sum.conflicts.rate Preprocessing JSON Path: `$.conflicts` Change per second
Dbstat: Deadlocks	Number of deadlocks detected	Dependent item	pgsql.dbstat.sum.deadlocks.rate Preprocessing JSON Path: `$.deadlocks` Change per second
Dbstat: Disk blocks read	Number of disk blocks read	Dependent item	pgsql.dbstat.sum.blks_read.rate Preprocessing JSON Path: `$.blks_read` Change per second
Dbstat: Hit blocks read	Number of times disk blocks were found already in the buffer cache	Dependent item	pgsql.dbstat.sum.blks_hit.rate Preprocessing JSON Path: `$.blks_hit` Change per second
Dbstat: Number temp bytes	Total amount of data written to temporary files by queries	Dependent item	pgsql.dbstat.sum.temp_bytes.rate Preprocessing JSON Path: `$.temp_bytes` Change per second
Dbstat: Number temp bytes	Number of temporary files created by queries	Dependent item	pgsql.dbstat.sum.temp_files.rate Preprocessing JSON Path: `$.temp_files` Change per second
Dbstat: Roll backed transactions	Number of transactions that have been rolled back	Dependent item	pgsql.dbstat.sum.xact_rollback.rate Preprocessing JSON Path: `$.xact_rollback` Change per second
Dbstat: Rows deleted	Number of rows deleted by queries	Dependent item	pgsql.dbstat.sum.tup_deleted.rate Preprocessing JSON Path: `$.tup_deleted` Change per second
Dbstat: Rows fetched	Number of rows fetched by queries	Dependent item	pgsql.dbstat.sum.tup_fetched.rate Preprocessing JSON Path: `$.tup_fetched` Change per second
Dbstat: Rows inserted	Number of rows inserted by queries	Dependent item	pgsql.dbstat.sum.tup_inserted.rate Preprocessing JSON Path: `$.tup_inserted` Change per second
Dbstat: Rows returned	Number of rows returned by queries	Dependent item	pgsql.dbstat.sum.tup_returned.rate Preprocessing JSON Path: `$.tup_returned` Change per second
Dbstat: Rows updated	Number of rows updated by queries	Dependent item	pgsql.dbstat.sum.tup_updated.rate Preprocessing JSON Path: `$.tup_updated` Change per second
Dbstat: Backends connected	Number of connected backends	Dependent item	pgsql.dbstat.sum.numbackends Preprocessing JSON Path: `$.numbackends`
Connections sum: Active	Total number of connections executing a query	Dependent item	pgsql.connections.active Preprocessing JSON Path: `$.active`
Connections sum: Fastpath function call	Total number of connections executing a fast-path function	Dependent item	pgsql.connections.fastpath_function_call Preprocessing JSON Path: `$.idle_in_transaction`
Connections sum: Idle	Total number of connections waiting for a new client command	Dependent item	pgsql.connections.idle Preprocessing JSON Path: `$.idle`
Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	Dependent item	pgsql.connections.idle_in_transaction Preprocessing JSON Path: `$.idle_in_transaction`
Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	Dependent item	pgsql.connections.prepared Preprocessing JSON Path: `$.prepared`
Connections sum: Total	Total number of connections	Dependent item	pgsql.connections.total Preprocessing JSON Path: `$.total`
Connections sum: Total %	Total number of connections in percentage	Dependent item	pgsql.connections.total_pct Preprocessing JSON Path: `$.total_pct`
Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	Dependent item	pgsql.connections.waiting Preprocessing JSON Path: `$.waiting`
Connections sum: Idle in transaction (aborted)	Total number of connections in a transaction state, but not executing a query and one of the statements in the transaction caused an error.	Dependent item	pgsql.connections.idle_in_transaction_aborted Preprocessing JSON Path: `$.idle_in_transaction_aborted`
Connections sum: Disabled	Total number of disabled connections	Dependent item	pgsql.connections.disabled Preprocessing JSON Path: `$.disabled`
PostgreSQL: Age of oldest xid	Age of oldest xid.	Zabbix agent	pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Autovacuum: Count of autovacuum workers	Number of autovacuum workers.	Zabbix agent	pgsql.autovacuum.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Cache hit	Calculated	pgsql.cache.hit["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Uptime	Zabbix agent	pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Lag in bytes	Replication lag with Master in byte.	Zabbix agent	pgsql.replication.lag.b["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Lag in seconds	Replication lag with Master in seconds.	Zabbix agent	pgsql.replication.lag.sec["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	Zabbix agent	pgsql.replication.recovery_role["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Standby count	Number of standby servers	Zabbix agent	pgsql.replication.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	Zabbix agent	pgsql.replication.status["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Ping	Zabbix agent	pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Preprocessing Discard unchanged with heartbeat: `1h`

Triggers

Name	Description	Expression	Severity
Dbstat: Checksum failures detected	Data page checksum failures were detected on that DB instance.https://www.postgresql.org/docs/current/checksums.html	`last(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.sum.checksum_failures.rate)>0`	Average
Connections sum: Total number of connections is too high	`min(/PostgreSQL by Zabbix agent 2/pgsql.connections.total_pct,5m) > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	Average
PostgreSQL: Oldest xid is too big	`last(/PostgreSQL by Zabbix agent 2/pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]) > 18000000`	Average
PostgreSQL: Service has been restarted	`last(/PostgreSQL by Zabbix agent 2/pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]) < 600`	Average
PostgreSQL: Service is down	`last(/PostgreSQL by Zabbix agent 2/pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"])=0`	High

LLD rule Replication Discovery

Name	Description	Type	Key and additional info
Replication Discovery	Zabbix agent	pgsql.replication.process.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Item prototypes for Replication Discovery

Name Description Type Key and additional info

Application {#APPLICATION_NAME}: Replication flush lag

Dependent item

Name	Description	Type	Key and additional info
Application {#APPLICATION_NAME}: Replication flush lag	Dependent item	pgsql.replication.process.flush_lag["{#APPLICATION_NAME}"] Preprocessing JSON Path: `$['{#APPLICATION_NAME}'].flush_lag`
Application {#APPLICATION_NAME}: Replication replay lag	Dependent item	pgsql.replication.process.replay_lag["{#APPLICATION_NAME}"] Preprocessing JSON Path: `$['{#APPLICATION_NAME}'].replay_lag`
Application {#APPLICATION_NAME}: Replication write lag	Dependent item	pgsql.replication.process.write_lag["{#APPLICATION_NAME}"] Preprocessing JSON Path: `$['{#APPLICATION_NAME}'].write_lag`

pgsql.replication.process.flush_lag["{#APPLICATION_NAME}"]

Preprocessing

JSON Path: $['{#APPLICATION_NAME}'].flush_lag

Application {#APPLICATION_NAME}: Replication replay lag

Dependent item

pgsql.replication.process.replay_lag["{#APPLICATION_NAME}"]

Preprocessing

JSON Path: $['{#APPLICATION_NAME}'].replay_lag

Application {#APPLICATION_NAME}: Replication write lag

Dependent item

pgsql.replication.process.write_lag["{#APPLICATION_NAME}"]

Preprocessing

JSON Path: $['{#APPLICATION_NAME}'].write_lag

LLD rule Database discovery

Name	Description	Type	Key and additional info
Database discovery	Zabbix agent	pgsql.db.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Item prototypes for Database discovery

Name	Description	Type	Key and additional info
DB {#DBNAME}: Database age	Database age	Zabbix agent	pgsql.db.age["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
DB {#DBNAME}: Get bloating tables	Number of bloating tables	Zabbix agent	pgsql.db.bloating_tables["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
DB {#DBNAME}: Database size	Database size	Zabbix agent	pgsql.db.size["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	Dependent item	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blks_hit` Change per second
DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	Dependent item	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blks_read` Change per second
DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	Dependent item	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].conflicts` Change per second
DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	Dependent item	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].deadlocks` Change per second
DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	Dependent item	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].temp_bytes` Change per second
DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	Dependent item	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].temp_files` Change per second
DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	Dependent item	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_deleted` Change per second
DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	Dependent item	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_fetched` Change per second
DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	Dependent item	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_inserted` Change per second
DB {#DBNAME}: Tuples returned per second	Number of rows returned by queries in this database	Dependent item	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_returned` Change per second
DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	Dependent item	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_updated` Change per second
DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	Dependent item	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].xact_commit` Change per second
DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	Dependent item	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].xact_rollback` Change per second
DB {#DBNAME}: Backends connected	Number of backends currently connected to this database	Dependent item	pgsql.dbstat.numbackends["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].numbackends`
DB {#DBNAME}: Checksum failures	Number of data page checksum failures detected in this database	Dependent item	pgsql.dbstat.checksum_failures.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].checksum_failures` Matches regular expression: `^\d*$` ⛔️Custom on fail: Set value to: `-2` Change per second ⛔️Custom on fail: Set value to: `-1`
DB {#DBNAME}: Disk blocks read time	Time spent reading data file blocks by backends, in milliseconds	Dependent item	pgsql.dbstat.blk_read_time.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blk_read_time` Custom multiplier: `0.001` Change per second
DB {#DBNAME}: Disk blocks write time	Time spent writing data file blocks by backends, in milliseconds	Dependent item	pgsql.dbstat.blk_write_time.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blk_write_time` Custom multiplier: `0.001` Change per second
DB {#DBNAME}: Num of accessexclusive locks	Number of accessexclusive locks for each database	Dependent item	pgsql.locks.accessexclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].accessexclusive`
DB {#DBNAME}: Num of accessshare locks	Number of accessshare locks for each database	Dependent item	pgsql.locks.accessshare["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].accessshare`
DB {#DBNAME}: Num of exclusive locks	Number of exclusive locks for each database	Dependent item	pgsql.locks.exclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].exclusive`
DB {#DBNAME}: Num of rowexclusive locks	Number of rowexclusive locks for each database	Dependent item	pgsql.locks.rowexclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].rowexclusive`
DB {#DBNAME}: Num of rowshare locks	Number of rowshare locks for each database	Dependent item	pgsql.locks.rowshare["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].rowshare`
DB {#DBNAME}: Num of sharerowexclusive locks	Number of total sharerowexclusive for each database	Dependent item	pgsql.locks.sharerowexclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].sharerowexclusive`
DB {#DBNAME}: Num of shareupdateexclusive locks	Number of shareupdateexclusive locks for each database	Dependent item	pgsql.locks.shareupdateexclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].shareupdateexclusive`
DB {#DBNAME}: Num of share locks	Number of share locks for each database	Dependent item	pgsql.locks.share["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].share`
DB {#DBNAME}: Num of total locks	Number of total locks for each database	Dependent item	pgsql.locks.total["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].total`
DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	Dependent item	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_time_max`
DB {#DBNAME}: Queries max query time	Max query time	Dependent item	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_time_max`
DB {#DBNAME}: Queries max transaction time	Max transaction query time	Dependent item	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_time_max`
DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	Dependent item	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_slow_count`
DB {#DBNAME}: Queries slow query count	Slow query count	Dependent item	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_slow_count`
DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	Dependent item	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_slow_count`
DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	Dependent item	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_time_sum`
DB {#DBNAME}: Queries sum query time	Sum query time	Dependent item	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_time_sum`
DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	Dependent item	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_time_sum`

Trigger prototypes for Database discovery

Name	Description	Expression	Severity
DB {#DBNAME}: Too many recovery conflicts	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them.https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`min(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.conflicts.rate["{#DBNAME}"],5m) > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	Average
DB {#DBNAME}: Deadlock occurred	`min(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.deadlocks.rate["{#DBNAME}"],5m) > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	High
DB {#DBNAME}: Checksum failures detected	Data page checksum failures were detected on that database.https://www.postgresql.org/docs/current/checksums.html	`last(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.checksum_failures.rate["{#DBNAME}"])>0`	Average
DB {#DBNAME}: Too many slow queries	`min(/PostgreSQL by Zabbix agent 2/pgsql.queries.query.slow_count["{#DBNAME}"],5m)>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	Warning

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide feedback, discuss the template, or ask for help at ZABBIX forums

This template is for Zabbix version: 6.2

Also available for: 6.4 6.0 5.4 5.0

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql_agent2?at=release/6.2

PostgreSQL by Zabbix agent 2

Overview

For Zabbix version: 6.2 and higher. The template is developed to monitor DBMS PostgreSQL and its forks.

This template has been tested on:

PostgreSQL, version 10-15

Setup

Deploy Zabbix agent2 with Postgres plugin. Starting with Zabbix versions 6.0.10 / 6.2.4 / 6.4 postgres metrics moved to a loadable plugin and requires separate package installation or compilation of a plugin from sources.
Create PostgreSQL user to monitor (<password> at your discretion) and inherit permissions from the default role pg_monitor:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

Edit pg_hba.conf to allow connections from Zabbix agent:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host       all        zbx_monitor     localhost               md5

For more information please read the PostgreSQL documentation https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Set in the {$PG.URI} macro the system data source name of the PostgreSQL instance such as <protocol(host:port)>.
Set the user name and password in host macros ({$PG.USER} and {$PG.PASSWORD}) if you want to override parameters from the Zabbix agent configuration file.

Zabbix configuration

No specific Zabbix configuration is required.

Macros used

Name	Description	Default
{$PG.CONFLICTS.MAX.WARN}	-	`0`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	-	`90`
{$PG.DATABASE}	-	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	-	`0`
{$PG.LLD.FILTER.APPLICATION}	-	`(.+)`
{$PG.LLD.FILTER.DBNAME}	-	`(.+)`
{$PG.PASSWORD}	-	`postgres`
{$PG.QUERY_ETIME.MAX.WARN}	Execution time limit for count of slow queries.	`30`
{$PG.SLOW_QUERIES.MAX.WARN}	Slow queries count threshold for a trigger.	`5`
{$PG.URI}	-	`tcp://localhost:5432`
{$PG.USER}	-	`postgres`

Template links

There are no template links in this template.

Discovery rules

Name Description Type Key and additional info

Database discovery

Name	Description	Type	Key and additional info
Database discovery	-	ZABBIX_PASSIVE	pgsql.db.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Filter: AND - {#DBNAME} MATCHES_REGEX `{$PG.LLD.FILTER.DBNAME}`
Replication Discovery	-	ZABBIX_PASSIVE	pgsql.replication.process.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Filter: AND - {#APPLICATION_NAME} MATCHES_REGEX `{$PG.LLD.FILTER.APPLICATION}`

ZABBIX_PASSIVE

pgsql.db.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Filter:

AND

- {#DBNAME} MATCHES_REGEX {$PG.LLD.FILTER.DBNAME}

Replication Discovery

ZABBIX_PASSIVE

pgsql.replication.process.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Filter:

AND

- {#APPLICATION_NAME} MATCHES_REGEX {$PG.LLD.FILTER.APPLICATION}

Items collected

Group	Name	Description	Type	Key and additional info
PostgreSQL	PostgreSQL: Custom queries	Execute custom queries from file *.sql (check for option Plugins.Postgres.CustomQueriesPath at agent configuration)	ZABBIX_PASSIVE	pgsql.custom.query["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}",""]
PostgreSQL	WAL: Bytes written	WAL write in bytes	DEPENDENT	pgsql.wal.write Preprocessing: - JSONPATH: `$.write` - CHANGE_PER_SECOND
PostgreSQL	WAL: Bytes received	WAL receive in bytes	DEPENDENT	pgsql.wal.receive Preprocessing: - JSONPATH: `$.receive` - CHANGE_PER_SECOND
PostgreSQL	WAL: Segments count	Number of WAL segments	DEPENDENT	pgsql.wal.count Preprocessing: - JSONPATH: `$.count`
PostgreSQL	Bgwriter: Buffers allocated	Number of buffers allocated	DEPENDENT	pgsql.bgwriter.buffers_alloc.rate Preprocessing: - JSONPATH: `$.buffers_alloc` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written directly by a backend	Number of buffers written directly by a backend	DEPENDENT	pgsql.bgwriter.buffers_backend.rate Preprocessing: - JSONPATH: `$.buffers_backend` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Number of bgwriter stopped	Number of times the background writer stopped a cleaning scan because it had written too many buffers	DEPENDENT	pgsql.bgwriter.maxwritten_clean.rate Preprocessing: - JSONPATH: `$.maxwritten_clean` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Times a backend execute its own fsync	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	DEPENDENT	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing: - JSONPATH: `$.buffers_backend_fsync` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Buffers background written	Number of buffers written by the background writer	DEPENDENT	pgsql.bgwriter.buffers_clean.rate Preprocessing: - JSONPATH: `$.buffers_clean` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Buffers checkpoints written	Number of buffers written during checkpoints	DEPENDENT	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing: - JSONPATH: `$.buffers_checkpoint` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: By timeout	Number of scheduled checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_timed.rate Preprocessing: - JSONPATH: `$.checkpoints_timed` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Requested	Number of requested checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_req.rate Preprocessing: - JSONPATH: `$.checkpoints_req` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	DEPENDENT	pgsql.bgwriter.checkpoint_write_time.rate Preprocessing: - JSONPATH: `$.checkpoint_write_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in milliseconds	DEPENDENT	pgsql.bgwriter.checkpoint_sync_time.rate Preprocessing: - JSONPATH: `$.checkpoint_sync_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	DEPENDENT	pgsql.bgwriter.checkpoint_sync_time.rate Preprocessing: - JSONPATH: `$.checkpoint_sync_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Archive: Count of archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	DEPENDENT	pgsql.archive.count_archived_files Preprocessing: - JSONPATH: `$.archived_count`
PostgreSQL	Archive: Count of attempts to archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	DEPENDENT	pgsql.archive.failed_trying_to_archive Preprocessing: - JSONPATH: `$.failed_count`
PostgreSQL	Archive: Count of files in archive_status need to archive	-	DEPENDENT	pgsql.archive.count_files_to_archive Preprocessing: - JSONPATH: `$.count_files`
PostgreSQL	Archive: Count of files need to archive	Size of files to archive	DEPENDENT	pgsql.archive.size_files_to_archive Preprocessing: - JSONPATH: `$.size_files`
PostgreSQL	Dbstat: Blocks read time	Time spent reading data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.sum.blk_read_time Preprocessing: - JSONPATH: `$.blk_read_time` - MULTIPLIER: `0.001`
PostgreSQL	Dbstat: Blocks write time	Time spent writing data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.sum.blk_write_time Preprocessing: - JSONPATH: `$.blk_write_time` - MULTIPLIER: `0.001`
PostgreSQL	Dbstat: Checksum failures	Number of data page checksum failures detected (or on a shared object), or NULL if data checksums are not enabled. This metric included in PostgreSQL 12	DEPENDENT	pgsql.dbstat.sum.checksum_failures.rate Preprocessing: - JSONPATH: `$.checksum_failures` - MATCHES_REGEX: `^\d*$` - CHANGE_PER_SECOND ⛔️ON_FAIL: `CUSTOM_VALUE -> -1`
PostgreSQL	Dbstat: Committed transactions	Number of transactions that have been committed	DEPENDENT	pgsql.dbstat.sum.xact_commit.rate Preprocessing: - JSONPATH: `$.xact_commit` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Conflicts	Number of queries canceled due to conflicts with recovery. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.)	DEPENDENT	pgsql.dbstat.sum.conflicts.rate Preprocessing: - JSONPATH: `$.conflicts` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Deadlocks	Number of deadlocks detected	DEPENDENT	pgsql.dbstat.sum.deadlocks.rate Preprocessing: - JSONPATH: `$.deadlocks` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Disk blocks read	Number of disk blocks read	DEPENDENT	pgsql.dbstat.sum.blks_read.rate Preprocessing: - JSONPATH: `$.blks_read` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Hit blocks read	Number of times disk blocks were found already in the buffer cache	DEPENDENT	pgsql.dbstat.sum.blks_hit.rate Preprocessing: - JSONPATH: `$.blks_hit` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Number temp bytes	Total amount of data written to temporary files by queries	DEPENDENT	pgsql.dbstat.sum.temp_bytes.rate Preprocessing: - JSONPATH: `$.temp_bytes` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Number temp bytes	Number of temporary files created by queries	DEPENDENT	pgsql.dbstat.sum.temp_files.rate Preprocessing: - JSONPATH: `$.temp_files` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Roll backed transactions	Number of transactions that have been rolled back	DEPENDENT	pgsql.dbstat.sum.xact_rollback.rate Preprocessing: - JSONPATH: `$.xact_rollback` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows deleted	Number of rows deleted by queries	DEPENDENT	pgsql.dbstat.sum.tup_deleted.rate Preprocessing: - JSONPATH: `$.tup_deleted` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows fetched	Number of rows fetched by queries	DEPENDENT	pgsql.dbstat.sum.tup_fetched.rate Preprocessing: - JSONPATH: `$.tup_fetched` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows inserted	Number of rows inserted by queries	DEPENDENT	pgsql.dbstat.sum.tup_inserted.rate Preprocessing: - JSONPATH: `$.tup_inserted` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows returned	Number of rows returned by queries	DEPENDENT	pgsql.dbstat.sum.tup_returned.rate Preprocessing: - JSONPATH: `$.tup_returned` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows updated	Number of rows updated by queries	DEPENDENT	pgsql.dbstat.sum.tup_updated.rate Preprocessing: - JSONPATH: `$.tup_updated` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Backends connected	Number of connected backends	DEPENDENT	pgsql.dbstat.sum.numbackends Preprocessing: - JSONPATH: `$.numbackends`
PostgreSQL	Connections sum: Active	Total number of connections executing a query	DEPENDENT	pgsql.connections.active Preprocessing: - JSONPATH: `$.active`
PostgreSQL	Connections sum: Fastpath function call	Total number of connections executing a fast-path function	DEPENDENT	pgsql.connections.fastpath_function_call Preprocessing: - JSONPATH: `$.idle_in_transaction`
PostgreSQL	Connections sum: Idle	Total number of connections waiting for a new client command	DEPENDENT	pgsql.connections.idle Preprocessing: - JSONPATH: `$.idle`
PostgreSQL	Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	DEPENDENT	pgsql.connections.idle_in_transaction Preprocessing: - JSONPATH: `$.idle_in_transaction`
PostgreSQL	Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	DEPENDENT	pgsql.connections.prepared Preprocessing: - JSONPATH: `$.prepared`
PostgreSQL	Connections sum: Total	Total number of connections	DEPENDENT	pgsql.connections.total Preprocessing: - JSONPATH: `$.total`
PostgreSQL	Connections sum: Total %	Total number of connections in percentage	DEPENDENT	pgsql.connections.total_pct Preprocessing: - JSONPATH: `$.total_pct`
PostgreSQL	Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	DEPENDENT	pgsql.connections.waiting Preprocessing: - JSONPATH: `$.waiting`
PostgreSQL	Connections sum: Idle in transaction (aborted)	Total number of connections in a transaction state, but not executing a query and one of the statements in the transaction caused an error.	DEPENDENT	pgsql.connections.idle_in_transaction_aborted Preprocessing: - JSONPATH: `$.idle_in_transaction_aborted`
PostgreSQL	Connections sum: Disabled	Total number of disabled connections	DEPENDENT	pgsql.connections.disabled Preprocessing: - JSONPATH: `$.disabled`
PostgreSQL	PostgreSQL: Age of oldest xid	Age of oldest xid.	ZABBIX_PASSIVE	pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Autovacuum: Count of autovacuum workers	Number of autovacuum workers.	ZABBIX_PASSIVE	pgsql.autovacuum.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	PostgreSQL: Cache hit	-	CALCULATED	pgsql.cache.hit["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Expression: `last(//pgsql.dbstat.sum.blks_hit.rate) * 100 / (last(//pgsql.dbstat.sum.blks_hit.rate) + last(//pgsql.dbstat.sum.blks_read.rate))`
PostgreSQL	PostgreSQL: Uptime	-	ZABBIX_PASSIVE	pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Lag in bytes	Replication lag with Master in byte.	ZABBIX_PASSIVE	pgsql.replication.lag.b["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Lag in seconds	Replication lag with Master in seconds.	ZABBIX_PASSIVE	pgsql.replication.lag.sec["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	ZABBIX_PASSIVE	pgsql.replication.recovery_role["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Standby count	Number of standby servers	ZABBIX_PASSIVE	pgsql.replication.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	ZABBIX_PASSIVE	pgsql.replication.status["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	PostgreSQL: Ping	-	ZABBIX_PASSIVE	pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Preprocessing: - DISCARD_UNCHANGED_HEARTBEAT: `1h`
PostgreSQL	Application {#APPLICATION_NAME}: Replication flush lag		DEPENDENT	pgsql.replication.process.flush_lag["{#APPLICATION_NAME}"] Preprocessing: - JSONPATH: `$['{#APPLICATION_NAME}'].flush_lag`
PostgreSQL	Application {#APPLICATION_NAME}: Replication replay lag		DEPENDENT	pgsql.replication.process.replay_lag["{#APPLICATION_NAME}"] Preprocessing: - JSONPATH: `$['{#APPLICATION_NAME}'].replay_lag`
PostgreSQL	Application {#APPLICATION_NAME}: Replication write lag		DEPENDENT	pgsql.replication.process.write_lag["{#APPLICATION_NAME}"] Preprocessing: - JSONPATH: `$['{#APPLICATION_NAME}'].write_lag`
PostgreSQL	DB {#DBNAME}: Database age	Database age	ZABBIX_PASSIVE	pgsql.db.age["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Get bloating tables	Number of bloating tables	ZABBIX_PASSIVE	pgsql.db.bloating_tables["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Database size	Database size	ZABBIX_PASSIVE	pgsql.db.size["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	DEPENDENT	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_hit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	DEPENDENT	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_read` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	DEPENDENT	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].conflicts` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	DEPENDENT	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].deadlocks` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	DEPENDENT	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_bytes` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	DEPENDENT	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_files` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	DEPENDENT	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_deleted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	DEPENDENT	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_fetched` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	DEPENDENT	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_inserted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples returned per second	Number of rows returned by queries in this database	DEPENDENT	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_returned` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	DEPENDENT	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_updated` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	DEPENDENT	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_commit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	DEPENDENT	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_rollback` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Backends connected	Number of backends currently connected to this database	DEPENDENT	pgsql.dbstat.numbackends["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].numbackends`
PostgreSQL	DB {#DBNAME}: Checksum failures	Number of data page checksum failures detected in this database	DEPENDENT	pgsql.dbstat.checksum_failures.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].checksum_failures` - MATCHES_REGEX: `^\d*$` - CHANGE_PER_SECOND ⛔️ON_FAIL: `CUSTOM_VALUE -> -1`
PostgreSQL	DB {#DBNAME}: Disk blocks read time	Time spent reading data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.blk_read_time.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blk_read_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks write time	Time spent writing data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.blk_write_time.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blk_write_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Num of accessexclusive locks	Number of accessexclusive locks for each database	DEPENDENT	pgsql.locks.accessexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].accessexclusive`
PostgreSQL	DB {#DBNAME}: Num of accessshare locks	Number of accessshare locks for each database	DEPENDENT	pgsql.locks.accessshare["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].accessshare`
PostgreSQL	DB {#DBNAME}: Num of exclusive locks	Number of exclusive locks for each database	DEPENDENT	pgsql.locks.exclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].exclusive`
PostgreSQL	DB {#DBNAME}: Num of rowexclusive locks	Number of rowexclusive locks for each database	DEPENDENT	pgsql.locks.rowexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].rowexclusive`
PostgreSQL	DB {#DBNAME}: Num of rowshare locks	Number of rowshare locks for each database	DEPENDENT	pgsql.locks.rowshare["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].rowshare`
PostgreSQL	DB {#DBNAME}: Num of sharerowexclusive locks	Number of total sharerowexclusive for each database	DEPENDENT	pgsql.locks.sharerowexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].sharerowexclusive`
PostgreSQL	DB {#DBNAME}: Num of shareupdateexclusive locks	Number of shareupdateexclusive locks for each database	DEPENDENT	pgsql.locks.shareupdateexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].shareupdateexclusive`
PostgreSQL	DB {#DBNAME}: Num of share locks	Number of share locks for each database	DEPENDENT	pgsql.locks.share["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].share`
PostgreSQL	DB {#DBNAME}: Num of total locks	Number of total locks for each database	DEPENDENT	pgsql.locks.total["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].total`
PostgreSQL	DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	DEPENDENT	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_max`
PostgreSQL	DB {#DBNAME}: Queries max query time	Max query time	DEPENDENT	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_max`
PostgreSQL	DB {#DBNAME}: Queries max transaction time	Max transaction query time	DEPENDENT	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_max`
PostgreSQL	DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	DEPENDENT	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_slow_count`
PostgreSQL	DB {#DBNAME}: Queries slow query count	Slow query count	DEPENDENT	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_slow_count`
PostgreSQL	DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	DEPENDENT	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_slow_count`
PostgreSQL	DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	DEPENDENT	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_sum`
PostgreSQL	DB {#DBNAME}: Queries sum query time	Sum query time	DEPENDENT	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_sum`
PostgreSQL	DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	DEPENDENT	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_sum`
Zabbix raw items	PostgreSQL: Get bgwriter	https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-BGWRITER-VIEW	ZABBIX_PASSIVE	pgsql.bgwriter["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get archive	Collect archive status metrics	ZABBIX_PASSIVE	pgsql.archive["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	ZABBIX_PASSIVE	pgsql.dbstat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get dbstat sum	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	ZABBIX_PASSIVE	pgsql.dbstat.sum["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get connections	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	ZABBIX_PASSIVE	pgsql.connections["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get WAL	Collect WAL metrics	ZABBIX_PASSIVE	pgsql.wal.stat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	ZABBIX_PASSIVE	pgsql.locks["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get replication	Collect metrics from the pg_stat_replication, which contains information about the WAL sender process, showing statistics about replication to that sender's connected standby server.	ZABBIX_PASSIVE	pgsql.replication.process["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get queries	Collect all metrics by query execution time	ZABBIX_PASSIVE	pgsql.queries["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}","{$PG.QUERY_ETIME.MAX.WARN}"]

Triggers

Name	Description	Expression	Severity
Dbstat: Checksum failures detected	Data page checksum failures were detected on that DB instance. https://www.postgresql.org/docs/current/checksums.html	`last(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.sum.checksum_failures.rate)>0`	AVERAGE
Connections sum: Total number of connections is too high	-	`min(/PostgreSQL by Zabbix agent 2/pgsql.connections.total_pct,5m) > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	AVERAGE
PostgreSQL: Oldest xid is too big	-	`last(/PostgreSQL by Zabbix agent 2/pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]) > 18000000`	AVERAGE
PostgreSQL: Service has been restarted	-	`last(/PostgreSQL by Zabbix agent 2/pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]) < 600`	AVERAGE
PostgreSQL: Service is down	-	`last(/PostgreSQL by Zabbix agent 2/pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"])=0`	HIGH
DB {#DBNAME}: Too many recovery conflicts	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them. https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`min(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.conflicts.rate["{#DBNAME}"],5m) > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	AVERAGE
DB {#DBNAME}: Deadlock occurred	-	`min(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.deadlocks.rate["{#DBNAME}"],5m) > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	HIGH
DB {#DBNAME}: Checksum failures detected	Data page checksum failures were detected on that database. https://www.postgresql.org/docs/current/checksums.html	`last(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.checksum_failures.rate["{#DBNAME}"])>0`	AVERAGE
DB {#DBNAME}: Too many slow queries	-	`min(/PostgreSQL by Zabbix agent 2/pgsql.queries.query.slow_count["{#DBNAME}"],5m)>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	WARNING

Feedback

Please report any issues with the template at https://support.zabbix.com.

You can also provide feedback, discuss the template, or ask for help at ZABBIX forums.

This template is for Zabbix version: 6.0

Also available for: 6.4 6.2 5.4 5.0

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql_agent2?at=release/6.0

PostgreSQL by Zabbix agent 2

Overview

This template is designed for the effortless deployment of PostgreSQL monitoring by Zabbix via Zabbix agent 2 and doesn't require any external scripts.

Requirements

Zabbix version: 6.0 and higher.

Tested versions

This template has been tested on:

PostgreSQL 10-15

Configuration

Setup

Deploy Zabbix agent2 with Postgres plugin. Starting with Zabbix versions 6.0.10 / 6.2.4 / 6.4 postgres metrics moved to a loadable plugin and requires separate package installation or compilation of a plugin from sources.
Create PostgreSQL user to monitor (<password> at your discretion) and inherit permissions from the default role pg_monitor:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

Edit pg_hba.conf to allow connections from Zabbix agent:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host       all        zbx_monitor     localhost               md5

For more information please read the PostgreSQL documentation https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Set in the {$PG.URI} macro the system data source name of the PostgreSQL instance such as <protocol(host:port)>.
Set the user name and password in host macros ({$PG.USER} and {$PG.PASSWORD}) if you want to override parameters from the Zabbix agent configuration file.

Macros used

Name	Description	Default
{$PG.PASSWORD}	`postgres`
{$PG.URI}	`tcp://localhost:5432`
{$PG.USER}	`postgres`
{$PG.LLD.FILTER.DBNAME}	`(.+)`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	`90`
{$PG.DATABASE}	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	`0`
{$PG.LLD.FILTER.APPLICATION}	`(.+)`
{$PG.CONFLICTS.MAX.WARN}	`0`
{$PG.QUERY_ETIME.MAX.WARN}	Execution time limit for count of slow queries.	`30`
{$PG.SLOW_QUERIES.MAX.WARN}	Slow queries count threshold for a trigger.	`5`

Items

Name	Description	Type	Key and additional info
PostgreSQL: Get bgwriter	https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-BGWRITER-VIEW	Zabbix agent	pgsql.bgwriter["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get archive	Collect archive status metrics	Zabbix agent	pgsql.archive["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	Zabbix agent	pgsql.dbstat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get dbstat sum	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	Zabbix agent	pgsql.dbstat.sum["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get connections	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	Zabbix agent	pgsql.connections["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get WAL	Collect WAL metrics	Zabbix agent	pgsql.wal.stat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	Zabbix agent	pgsql.locks["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Custom queries	Execute custom queries from file *.sql (check for option Plugins.Postgres.CustomQueriesPath at agent configuration)	Zabbix agent	pgsql.custom.query["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}",""]
PostgreSQL: Get replication	Collect metrics from the pg_stat_replication, which contains information about the WAL sender process, showing statistics about replication to that sender's connected standby server.	Zabbix agent	pgsql.replication.process["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Get queries	Collect all metrics by query execution time	Zabbix agent	pgsql.queries["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}","{$PG.QUERY_ETIME.MAX.WARN}"]
WAL: Bytes written	WAL write in bytes	Dependent item	pgsql.wal.write Preprocessing JSON Path: `$.write` Change per second
WAL: Bytes received	WAL receive in bytes	Dependent item	pgsql.wal.receive Preprocessing JSON Path: `$.receive` Change per second
WAL: Segments count	Number of WAL segments	Dependent item	pgsql.wal.count Preprocessing JSON Path: `$.count`
Bgwriter: Buffers allocated	Number of buffers allocated	Dependent item	pgsql.bgwriter.buffers_alloc.rate Preprocessing JSON Path: `$.buffers_alloc` Change per second
Bgwriter: Buffers written directly by a backend	Number of buffers written directly by a backend	Dependent item	pgsql.bgwriter.buffers_backend.rate Preprocessing JSON Path: `$.buffers_backend` Change per second
Bgwriter: Number of bgwriter stopped	Number of times the background writer stopped a cleaning scan because it had written too many buffers	Dependent item	pgsql.bgwriter.maxwritten_clean.rate Preprocessing JSON Path: `$.maxwritten_clean` Change per second
Bgwriter: Times a backend execute its own fsync	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	Dependent item	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing JSON Path: `$.buffers_backend_fsync` Change per second
Checkpoint: Buffers background written	Number of buffers written by the background writer	Dependent item	pgsql.bgwriter.buffers_clean.rate Preprocessing JSON Path: `$.buffers_clean` Change per second
Checkpoint: Buffers checkpoints written	Number of buffers written during checkpoints	Dependent item	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing JSON Path: `$.buffers_checkpoint` Change per second
Checkpoint: By timeout	Number of scheduled checkpoints that have been performed	Dependent item	pgsql.bgwriter.checkpoints_timed.rate Preprocessing JSON Path: `$.checkpoints_timed` Change per second
Checkpoint: Requested	Number of requested checkpoints that have been performed	Dependent item	pgsql.bgwriter.checkpoints_req.rate Preprocessing JSON Path: `$.checkpoints_req` Change per second
Checkpoint: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	Dependent item	pgsql.bgwriter.checkpoint_write_time.rate Preprocessing JSON Path: `$.checkpoint_write_time` Custom multiplier: `0.001` Change per second
Checkpoint: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	Dependent item	pgsql.bgwriter.checkpoint_sync_time.rate Preprocessing JSON Path: `$.checkpoint_sync_time` Custom multiplier: `0.001` Change per second
Archive: Count of archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	Dependent item	pgsql.archive.count_archived_files Preprocessing JSON Path: `$.archived_count`
Archive: Count of attempts to archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	Dependent item	pgsql.archive.failed_trying_to_archive Preprocessing JSON Path: `$.failed_count`
Archive: Count of files in archive_status need to archive	Dependent item	pgsql.archive.count_files_to_archive Preprocessing JSON Path: `$.count_files`
Archive: Count of files need to archive	Size of files to archive	Dependent item	pgsql.archive.size_files_to_archive Preprocessing JSON Path: `$.size_files`
Dbstat: Blocks read time	Time spent reading data file blocks by backends, in milliseconds	Dependent item	pgsql.dbstat.sum.blk_read_time Preprocessing JSON Path: `$.blk_read_time` Custom multiplier: `0.001`
Dbstat: Blocks write time	Time spent writing data file blocks by backends, in milliseconds	Dependent item	pgsql.dbstat.sum.blk_write_time Preprocessing JSON Path: `$.blk_write_time` Custom multiplier: `0.001`
Dbstat: Checksum failures	Number of data page checksum failures detected (or on a shared object), or NULL if data checksums are not enabled. This metric included in PostgreSQL 12	Dependent item	pgsql.dbstat.sum.checksum_failures.rate Preprocessing JSON Path: `$.checksum_failures` Matches regular expression: `^\d*$` ⛔️Custom on fail: Set value to: `-2` Change per second ⛔️Custom on fail: Set value to: `-1`
Dbstat: Committed transactions	Number of transactions that have been committed	Dependent item	pgsql.dbstat.sum.xact_commit.rate Preprocessing JSON Path: `$.xact_commit` Change per second
Dbstat: Conflicts	Number of queries canceled due to conflicts with recovery. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.)	Dependent item	pgsql.dbstat.sum.conflicts.rate Preprocessing JSON Path: `$.conflicts` Change per second
Dbstat: Deadlocks	Number of deadlocks detected	Dependent item	pgsql.dbstat.sum.deadlocks.rate Preprocessing JSON Path: `$.deadlocks` Change per second
Dbstat: Disk blocks read	Number of disk blocks read	Dependent item	pgsql.dbstat.sum.blks_read.rate Preprocessing JSON Path: `$.blks_read` Change per second
Dbstat: Hit blocks read	Number of times disk blocks were found already in the buffer cache	Dependent item	pgsql.dbstat.sum.blks_hit.rate Preprocessing JSON Path: `$.blks_hit` Change per second
Dbstat: Number temp bytes	Total amount of data written to temporary files by queries	Dependent item	pgsql.dbstat.sum.temp_bytes.rate Preprocessing JSON Path: `$.temp_bytes` Change per second
Dbstat: Number temp bytes	Number of temporary files created by queries	Dependent item	pgsql.dbstat.sum.temp_files.rate Preprocessing JSON Path: `$.temp_files` Change per second
Dbstat: Roll backed transactions	Number of transactions that have been rolled back	Dependent item	pgsql.dbstat.sum.xact_rollback.rate Preprocessing JSON Path: `$.xact_rollback` Change per second
Dbstat: Rows deleted	Number of rows deleted by queries	Dependent item	pgsql.dbstat.sum.tup_deleted.rate Preprocessing JSON Path: `$.tup_deleted` Change per second
Dbstat: Rows fetched	Number of rows fetched by queries	Dependent item	pgsql.dbstat.sum.tup_fetched.rate Preprocessing JSON Path: `$.tup_fetched` Change per second
Dbstat: Rows inserted	Number of rows inserted by queries	Dependent item	pgsql.dbstat.sum.tup_inserted.rate Preprocessing JSON Path: `$.tup_inserted` Change per second
Dbstat: Rows returned	Number of rows returned by queries	Dependent item	pgsql.dbstat.sum.tup_returned.rate Preprocessing JSON Path: `$.tup_returned` Change per second
Dbstat: Rows updated	Number of rows updated by queries	Dependent item	pgsql.dbstat.sum.tup_updated.rate Preprocessing JSON Path: `$.tup_updated` Change per second
Dbstat: Backends connected	Number of connected backends	Dependent item	pgsql.dbstat.sum.numbackends Preprocessing JSON Path: `$.numbackends`
Connections sum: Active	Total number of connections executing a query	Dependent item	pgsql.connections.active Preprocessing JSON Path: `$.active`
Connections sum: Fastpath function call	Total number of connections executing a fast-path function	Dependent item	pgsql.connections.fastpath_function_call Preprocessing JSON Path: `$.idle_in_transaction`
Connections sum: Idle	Total number of connections waiting for a new client command	Dependent item	pgsql.connections.idle Preprocessing JSON Path: `$.idle`
Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	Dependent item	pgsql.connections.idle_in_transaction Preprocessing JSON Path: `$.idle_in_transaction`
Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	Dependent item	pgsql.connections.prepared Preprocessing JSON Path: `$.prepared`
Connections sum: Total	Total number of connections	Dependent item	pgsql.connections.total Preprocessing JSON Path: `$.total`
Connections sum: Total %	Total number of connections in percentage	Dependent item	pgsql.connections.total_pct Preprocessing JSON Path: `$.total_pct`
Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	Dependent item	pgsql.connections.waiting Preprocessing JSON Path: `$.waiting`
Connections sum: Idle in transaction (aborted)	Total number of connections in a transaction state, but not executing a query and one of the statements in the transaction caused an error.	Dependent item	pgsql.connections.idle_in_transaction_aborted Preprocessing JSON Path: `$.idle_in_transaction_aborted`
Connections sum: Disabled	Total number of disabled connections	Dependent item	pgsql.connections.disabled Preprocessing JSON Path: `$.disabled`
PostgreSQL: Age of oldest xid	Age of oldest xid.	Zabbix agent	pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Autovacuum: Count of autovacuum workers	Number of autovacuum workers.	Zabbix agent	pgsql.autovacuum.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Cache hit	Calculated	pgsql.cache.hit["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Uptime	Zabbix agent	pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Lag in bytes	Replication lag with Master in byte.	Zabbix agent	pgsql.replication.lag.b["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Lag in seconds	Replication lag with Master in seconds.	Zabbix agent	pgsql.replication.lag.sec["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	Zabbix agent	pgsql.replication.recovery_role["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Standby count	Number of standby servers	Zabbix agent	pgsql.replication.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Replication: Status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	Zabbix agent	pgsql.replication.status["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL: Ping	Zabbix agent	pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Preprocessing Discard unchanged with heartbeat: `1h`

Triggers

Name	Description	Expression	Severity
Dbstat: Checksum failures detected	Data page checksum failures were detected on that DB instance.https://www.postgresql.org/docs/current/checksums.html	`last(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.sum.checksum_failures.rate)>0`	Average
Connections sum: Total number of connections is too high	`min(/PostgreSQL by Zabbix agent 2/pgsql.connections.total_pct,5m) > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	Average
PostgreSQL: Oldest xid is too big	`last(/PostgreSQL by Zabbix agent 2/pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]) > 18000000`	Average
PostgreSQL: Service has been restarted	`last(/PostgreSQL by Zabbix agent 2/pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]) < 600`	Average
PostgreSQL: Service is down	`last(/PostgreSQL by Zabbix agent 2/pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"])=0`	High

LLD rule Replication Discovery

Name	Description	Type	Key and additional info
Replication Discovery	Zabbix agent	pgsql.replication.process.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Item prototypes for Replication Discovery

Name Description Type Key and additional info

Application {#APPLICATION_NAME}: Replication flush lag

Dependent item

Name	Description	Type	Key and additional info
Application {#APPLICATION_NAME}: Replication flush lag	Dependent item	pgsql.replication.process.flush_lag["{#APPLICATION_NAME}"] Preprocessing JSON Path: `$['{#APPLICATION_NAME}'].flush_lag`
Application {#APPLICATION_NAME}: Replication replay lag	Dependent item	pgsql.replication.process.replay_lag["{#APPLICATION_NAME}"] Preprocessing JSON Path: `$['{#APPLICATION_NAME}'].replay_lag`
Application {#APPLICATION_NAME}: Replication write lag	Dependent item	pgsql.replication.process.write_lag["{#APPLICATION_NAME}"] Preprocessing JSON Path: `$['{#APPLICATION_NAME}'].write_lag`

pgsql.replication.process.flush_lag["{#APPLICATION_NAME}"]

Preprocessing

JSON Path: $['{#APPLICATION_NAME}'].flush_lag

Application {#APPLICATION_NAME}: Replication replay lag

Dependent item

pgsql.replication.process.replay_lag["{#APPLICATION_NAME}"]

Preprocessing

JSON Path: $['{#APPLICATION_NAME}'].replay_lag

Application {#APPLICATION_NAME}: Replication write lag

Dependent item

pgsql.replication.process.write_lag["{#APPLICATION_NAME}"]

Preprocessing

JSON Path: $['{#APPLICATION_NAME}'].write_lag

LLD rule Database discovery

Name	Description	Type	Key and additional info
Database discovery	Zabbix agent	pgsql.db.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Item prototypes for Database discovery

Name	Description	Type	Key and additional info
DB {#DBNAME}: Database age	Database age	Zabbix agent	pgsql.db.age["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
DB {#DBNAME}: Get bloating tables	Number of bloating tables	Zabbix agent	pgsql.db.bloating_tables["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
DB {#DBNAME}: Database size	Database size	Zabbix agent	pgsql.db.size["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	Dependent item	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blks_hit` Change per second
DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	Dependent item	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blks_read` Change per second
DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	Dependent item	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].conflicts` Change per second
DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	Dependent item	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].deadlocks` Change per second
DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	Dependent item	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].temp_bytes` Change per second
DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	Dependent item	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].temp_files` Change per second
DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	Dependent item	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_deleted` Change per second
DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	Dependent item	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_fetched` Change per second
DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	Dependent item	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_inserted` Change per second
DB {#DBNAME}: Tuples returned per second	Number of rows returned by queries in this database	Dependent item	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_returned` Change per second
DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	Dependent item	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tup_updated` Change per second
DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	Dependent item	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].xact_commit` Change per second
DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	Dependent item	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].xact_rollback` Change per second
DB {#DBNAME}: Backends connected	Number of backends currently connected to this database	Dependent item	pgsql.dbstat.numbackends["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].numbackends`
DB {#DBNAME}: Checksum failures	Number of data page checksum failures detected in this database	Dependent item	pgsql.dbstat.checksum_failures.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].checksum_failures` Matches regular expression: `^\d*$` ⛔️Custom on fail: Set value to: `-2` Change per second ⛔️Custom on fail: Set value to: `-1`
DB {#DBNAME}: Disk blocks read time	Time spent reading data file blocks by backends, in milliseconds	Dependent item	pgsql.dbstat.blk_read_time.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blk_read_time` Custom multiplier: `0.001` Change per second
DB {#DBNAME}: Disk blocks write time	Time spent writing data file blocks by backends, in milliseconds	Dependent item	pgsql.dbstat.blk_write_time.rate["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].blk_write_time` Custom multiplier: `0.001` Change per second
DB {#DBNAME}: Num of accessexclusive locks	Number of accessexclusive locks for each database	Dependent item	pgsql.locks.accessexclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].accessexclusive`
DB {#DBNAME}: Num of accessshare locks	Number of accessshare locks for each database	Dependent item	pgsql.locks.accessshare["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].accessshare`
DB {#DBNAME}: Num of exclusive locks	Number of exclusive locks for each database	Dependent item	pgsql.locks.exclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].exclusive`
DB {#DBNAME}: Num of rowexclusive locks	Number of rowexclusive locks for each database	Dependent item	pgsql.locks.rowexclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].rowexclusive`
DB {#DBNAME}: Num of rowshare locks	Number of rowshare locks for each database	Dependent item	pgsql.locks.rowshare["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].rowshare`
DB {#DBNAME}: Num of sharerowexclusive locks	Number of total sharerowexclusive for each database	Dependent item	pgsql.locks.sharerowexclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].sharerowexclusive`
DB {#DBNAME}: Num of shareupdateexclusive locks	Number of shareupdateexclusive locks for each database	Dependent item	pgsql.locks.shareupdateexclusive["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].shareupdateexclusive`
DB {#DBNAME}: Num of share locks	Number of share locks for each database	Dependent item	pgsql.locks.share["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].share`
DB {#DBNAME}: Num of total locks	Number of total locks for each database	Dependent item	pgsql.locks.total["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].total`
DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	Dependent item	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_time_max`
DB {#DBNAME}: Queries max query time	Max query time	Dependent item	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_time_max`
DB {#DBNAME}: Queries max transaction time	Max transaction query time	Dependent item	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_time_max`
DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	Dependent item	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_slow_count`
DB {#DBNAME}: Queries slow query count	Slow query count	Dependent item	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_slow_count`
DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	Dependent item	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_slow_count`
DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	Dependent item	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].mro_time_sum`
DB {#DBNAME}: Queries sum query time	Sum query time	Dependent item	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].query_time_sum`
DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	Dependent item	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing JSON Path: `$['{#DBNAME}'].tx_time_sum`

Trigger prototypes for Database discovery

Name	Description	Expression	Severity
DB {#DBNAME}: Too many recovery conflicts	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them.https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`min(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.conflicts.rate["{#DBNAME}"],5m) > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	Average
DB {#DBNAME}: Deadlock occurred	`min(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.deadlocks.rate["{#DBNAME}"],5m) > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	High
DB {#DBNAME}: Checksum failures detected	Data page checksum failures were detected on that database.https://www.postgresql.org/docs/current/checksums.html	`last(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.checksum_failures.rate["{#DBNAME}"])>0`	Average
DB {#DBNAME}: Too many slow queries	`min(/PostgreSQL by Zabbix agent 2/pgsql.queries.query.slow_count["{#DBNAME}"],5m)>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	Warning

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide feedback, discuss the template, or ask for help at ZABBIX forums

This template is for Zabbix version: 5.4

Also available for: 6.4 6.2 6.0 5.0

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql_agent2?at=release/5.4

PostgreSQL by Zabbix agent 2

Overview

For Zabbix version: 5.4 and higher
The template is developed for monitoring DBMS PostgreSQL and its forks.

This template was tested on:

PostgreSQL, version 10, 11, 12

Setup

1. Create PostgreSQL user for monitoring (<password> at your discretion):

CREATE USER 'zbx_monitor' WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_dir(text) TO zbx_monitor;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stat_file(text) TO zbx_monitor;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_waldir() TO zbx_monitor;

2. Edit pg_hba.conf to allow connections from Zabbix agent:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host       all        zbx_monitor     localhost               md5

For more information please read the PostgreSQL documentation https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

3. Set in the {$PG.URI} macro the system data source name of the PostgreSQL instance such as <protocol(host:port)>.

4. Set the user name and password in host macros ({$PG.USER} and {$PG.PASSWORD}) if you want to override parameters from the Zabbix agent configuration file.

Zabbix configuration

No specific Zabbix configuration is required.

Macros used

Name	Description	Default
{$PG.CONFLICTS.MAX.WARN}	-	`0`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	-	`90`
{$PG.DATABASE}	-	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	-	`0`
{$PG.LLD.FILTER.APPLICATION}	-	`(.+)`
{$PG.LLD.FILTER.DBNAME}	-	`(.+)`
{$PG.PASSWORD}	-	`postgres`
{$PG.QUERY_ETIME.MAX.WARN}	Execution time limit for count of slow queries.	`30`
{$PG.SLOW_QUERIES.MAX.WARN}	Slow queries count threshold for a trigger.	`5`
{$PG.URI}	-	`tcp://localhost:5432`
{$PG.USER}	-	`postgres`

Template links

There are no template links in this template.

Discovery rules

Name Description Type Key and additional info

Database discovery

Name	Description	Type	Key and additional info
Database discovery	-	ZABBIX_PASSIVE	pgsql.db.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Filter: AND - {#DBNAME} MATCHES_REGEX `{$PG.LLD.FILTER.DBNAME}`
Replication Discovery	-	ZABBIX_PASSIVE	pgsql.replication.process.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Filter: AND - {#APPLICATION_NAME} MATCHES_REGEX `{$PG.LLD.FILTER.APPLICATION}`

ZABBIX_PASSIVE

pgsql.db.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Filter:

AND

- {#DBNAME} MATCHES_REGEX {$PG.LLD.FILTER.DBNAME}

Replication Discovery

ZABBIX_PASSIVE

pgsql.replication.process.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Filter:

AND

- {#APPLICATION_NAME} MATCHES_REGEX {$PG.LLD.FILTER.APPLICATION}

Items collected

Group	Name	Description	Type	Key and additional info
PostgreSQL	PostgreSQL: Custom queries	Execute custom queries from file *.sql (check for option Plugins.Postgres.CustomQueriesPath at agent configuration)	ZABBIX_PASSIVE	pgsql.custom.query["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}",""]
PostgreSQL	WAL: Bytes written	WAL write in bytes	DEPENDENT	pgsql.wal.write Preprocessing: - JSONPATH: `$.write` - CHANGE_PER_SECOND
PostgreSQL	WAL: Bytes received	WAL receive in bytes	DEPENDENT	pgsql.wal.receive Preprocessing: - JSONPATH: `$.receive` - CHANGE_PER_SECOND
PostgreSQL	WAL: Segments count	Number of WAL segments	DEPENDENT	pgsql.wal.count Preprocessing: - JSONPATH: `$.count`
PostgreSQL	Bgwriter: Buffers allocated	Number of buffers allocated	DEPENDENT	pgsql.bgwriter.buffers_alloc.rate Preprocessing: - JSONPATH: `$.buffers_alloc` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written directly by a backend	Number of buffers written directly by a backend	DEPENDENT	pgsql.bgwriter.buffers_backend.rate Preprocessing: - JSONPATH: `$.buffers_backend` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Number of bgwriter stopped	Number of times the background writer stopped a cleaning scan because it had written too many buffers	DEPENDENT	pgsql.bgwriter.maxwritten_clean.rate Preprocessing: - JSONPATH: `$.maxwritten_clean` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Times a backend execute its own fsync	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	DEPENDENT	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing: - JSONPATH: `$.buffers_backend_fsync` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Buffers background written	Number of buffers written by the background writer	DEPENDENT	pgsql.bgwriter.buffers_clean.rate Preprocessing: - JSONPATH: `$.buffers_clean` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Buffers checkpoints written	Number of buffers written during checkpoints	DEPENDENT	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing: - JSONPATH: `$.buffers_checkpoint` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: By timeout	Number of scheduled checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_timed.rate Preprocessing: - JSONPATH: `$.checkpoints_timed` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Requested	Number of requested checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_req.rate Preprocessing: - JSONPATH: `$.checkpoints_req` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	DEPENDENT	pgsql.bgwriter.checkpoint_write_time.rate Preprocessing: - JSONPATH: `$.checkpoint_write_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in milliseconds	DEPENDENT	pgsql.bgwriter.checkpoint_sync_time.rate Preprocessing: - JSONPATH: `$.checkpoint_sync_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	DEPENDENT	pgsql.bgwriter.checkpoint_sync_time.rate Preprocessing: - JSONPATH: `$.checkpoint_sync_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Archive: Count of archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	DEPENDENT	pgsql.archive.count_archived_files Preprocessing: - JSONPATH: `$.archived_count`
PostgreSQL	Archive: Count of attempts to archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	DEPENDENT	pgsql.archive.failed_trying_to_archive Preprocessing: - JSONPATH: `$.failed_count`
PostgreSQL	Archive: Count of files in archive_status need to archive	-	DEPENDENT	pgsql.archive.count_files_to_archive Preprocessing: - JSONPATH: `$.count_files`
PostgreSQL	Archive: Count of files need to archive	Size of files to archive	DEPENDENT	pgsql.archive.size_files_to_archive Preprocessing: - JSONPATH: `$.size_files`
PostgreSQL	Dbstat: Blocks read time	Time spent reading data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.sum.blk_read_time Preprocessing: - JSONPATH: `$.blk_read_time` - MULTIPLIER: `0.001`
PostgreSQL	Dbstat: Blocks write time	Time spent writing data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.sum.blk_write_time Preprocessing: - JSONPATH: `$.blk_read_time` - MULTIPLIER: `0.001`
PostgreSQL	Dbstat: Checksum failures	Number of data page checksum failures detected (or on a shared object), or NULL if data checksums are not enabled. This metric included in PostgreSQL 12	DEPENDENT	pgsql.dbstat.sum.checksum_failures.rate Preprocessing: - JSONPATH: `$.checksum_failures` - MATCHES_REGEX: `^\d*$` - CHANGE_PER_SECOND ⛔️ON_FAIL: `CUSTOM_VALUE -> -1`
PostgreSQL	Dbstat: Committed transactions	Number of transactions that have been committed	DEPENDENT	pgsql.dbstat.sum.xact_commit.rate Preprocessing: - JSONPATH: `$.xact_commit` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Conflicts	Number of queries canceled due to conflicts with recovery. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.)	DEPENDENT	pgsql.dbstat.sum.conflicts.rate Preprocessing: - JSONPATH: `$.conflicts` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Deadlocks	Number of deadlocks detected	DEPENDENT	pgsql.dbstat.sum.deadlocks.rate Preprocessing: - JSONPATH: `$.deadlocks` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Disk blocks read	Number of disk blocks read	DEPENDENT	pgsql.dbstat.sum.blks_read.rate Preprocessing: - JSONPATH: `$.blks_read` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Hit blocks read	Number of times disk blocks were found already in the buffer cache	DEPENDENT	pgsql.dbstat.sum.blks_hit.rate Preprocessing: - JSONPATH: `$.blks_hit` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Number temp bytes	Total amount of data written to temporary files by queries	DEPENDENT	pgsql.dbstat.sum.temp_bytes.rate Preprocessing: - JSONPATH: `$.temp_bytes` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Number temp bytes	Number of temporary files created by queries	DEPENDENT	pgsql.dbstat.sum.temp_files.rate Preprocessing: - JSONPATH: `$.temp_files` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Roll backed transactions	Number of transactions that have been rolled back	DEPENDENT	pgsql.dbstat.sum.xact_rollback.rate Preprocessing: - JSONPATH: `$.xact_rollback` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows deleted	Number of rows deleted by queries	DEPENDENT	pgsql.dbstat.sum.tup_deleted.rate Preprocessing: - JSONPATH: `$.tup_deleted` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows fetched	Number of rows fetched by queries	DEPENDENT	pgsql.dbstat.sum.tup_fetched.rate Preprocessing: - JSONPATH: `$.tup_fetched` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows inserted	Number of rows inserted by queries	DEPENDENT	pgsql.dbstat.sum.tup_inserted.rate Preprocessing: - JSONPATH: `$.tup_inserted` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows returned	Number of rows returned by queries	DEPENDENT	pgsql.dbstat.sum.tup_returned.rate Preprocessing: - JSONPATH: `$.tup_returned` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows updated	Number of rows updated by queries	DEPENDENT	pgsql.dbstat.sum.tup_updated.rate Preprocessing: - JSONPATH: `$.tup_updated` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Backends connected	Number of connected backends	DEPENDENT	pgsql.dbstat.sum.numbackends Preprocessing: - JSONPATH: `$.numbackends`
PostgreSQL	Connections sum: Active	Total number of connections executing a query	DEPENDENT	pgsql.connections.active Preprocessing: - JSONPATH: `$.active`
PostgreSQL	Connections sum: Fastpath function call	Total number of connections executing a fast-path function	DEPENDENT	pgsql.connections.fastpath_function_call Preprocessing: - JSONPATH: `$.idle_in_transaction`
PostgreSQL	Connections sum: Idle	Total number of connections waiting for a new client command	DEPENDENT	pgsql.connections.idle Preprocessing: - JSONPATH: `$.idle`
PostgreSQL	Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	DEPENDENT	pgsql.connections.idle_in_transaction Preprocessing: - JSONPATH: `$.idle_in_transaction`
PostgreSQL	Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	DEPENDENT	pgsql.connections.prepared Preprocessing: - JSONPATH: `$.prepared`
PostgreSQL	Connections sum: Total	Total number of connections	DEPENDENT	pgsql.connections.total Preprocessing: - JSONPATH: `$.total`
PostgreSQL	Connections sum: Total %	Total number of connections in percentage	DEPENDENT	pgsql.connections.total_pct Preprocessing: - JSONPATH: `$.total_pct`
PostgreSQL	Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	DEPENDENT	pgsql.connections.waiting Preprocessing: - JSONPATH: `$.waiting`
PostgreSQL	Connections sum: Idle in transaction (aborted)	Total number of connections in a transaction state, but not executing a query and one of the statements in the transaction caused an error.	DEPENDENT	pgsql.connections.idle_in_transaction_aborted Preprocessing: - JSONPATH: `$.idle_in_transaction_aborted`
PostgreSQL	Connections sum: Disabled	Total number of disabled connections	DEPENDENT	pgsql.connections.disabled Preprocessing: - JSONPATH: `$.disabled`
PostgreSQL	PostgreSQL: Age of oldest xid	Age of oldest xid.	ZABBIX_PASSIVE	pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Autovacuum: Count of autovacuum workers	Number of autovacuum workers.	ZABBIX_PASSIVE	pgsql.autovacuum.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	PostgreSQL: Cache hit	-	CALCULATED	pgsql.cache.hit["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Expression: `last(//pgsql.dbstat.sum.blks_hit.rate) * 100 / (last(//pgsql.dbstat.sum.blks_hit.rate) + last(//pgsql.dbstat.sum.blks_read.rate))`
PostgreSQL	PostgreSQL: Uptime	-	ZABBIX_PASSIVE	pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Lag in bytes	Replication lag with Master in byte.	ZABBIX_PASSIVE	pgsql.replication.lag.b["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Lag in seconds	Replication lag with Master in seconds.	ZABBIX_PASSIVE	pgsql.replication.lag.sec["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	ZABBIX_PASSIVE	pgsql.replication.recovery_role["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Standby count	Number of standby servers	ZABBIX_PASSIVE	pgsql.replication.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	ZABBIX_PASSIVE	pgsql.replication.status["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	PostgreSQL: Ping	-	ZABBIX_PASSIVE	pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Preprocessing: - DISCARD_UNCHANGED_HEARTBEAT: `1h`
PostgreSQL	Application {#APPLICATION_NAME}: Replication flush lag		DEPENDENT	pgsql.replication.process.flush_lag["{#APPLICATION_NAME}"] Preprocessing: - JSONPATH: `$['{#APPLICATION_NAME}'].flush_lag`
PostgreSQL	Application {#APPLICATION_NAME}: Replication replay lag		DEPENDENT	pgsql.replication.process.replay_lag["{#APPLICATION_NAME}"] Preprocessing: - JSONPATH: `$['{#APPLICATION_NAME}'].replay_lag`
PostgreSQL	Application {#APPLICATION_NAME}: Replication write lag		DEPENDENT	pgsql.replication.process.write_lag["{#APPLICATION_NAME}"] Preprocessing: - JSONPATH: `$['{#APPLICATION_NAME}'].write_lag`
PostgreSQL	DB {#DBNAME}: Database age	Database age	ZABBIX_PASSIVE	pgsql.db.age["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Get bloating tables	Number of bloating tables	ZABBIX_PASSIVE	pgsql.db.bloating_tables["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Database size	Database size	ZABBIX_PASSIVE	pgsql.db.size["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	DEPENDENT	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_hit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	DEPENDENT	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_read` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	DEPENDENT	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].conflicts` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	DEPENDENT	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].deadlocks` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	DEPENDENT	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_bytes` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	DEPENDENT	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_files` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	DEPENDENT	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_deleted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	DEPENDENT	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_fetched` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	DEPENDENT	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_inserted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples returned per second	Number of rows returned by queries in this database	DEPENDENT	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_returned` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	DEPENDENT	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_updated` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	DEPENDENT	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_commit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	DEPENDENT	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_rollback` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Backends connected	Number of backends currently connected to this database	DEPENDENT	pgsql.dbstat.numbackends["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].numbackends`
PostgreSQL	DB {#DBNAME}: Checksum failures	Number of data page checksum failures detected in this database	DEPENDENT	pgsql.dbstat.checksum_failures.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].checksum_failures` - MATCHES_REGEX: `^\d*$` - CHANGE_PER_SECOND ⛔️ON_FAIL: `CUSTOM_VALUE -> -1`
PostgreSQL	DB {#DBNAME}: Disk blocks read time	Time spent reading data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.blk_read_time.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blk_read_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks write time	Time spent writing data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.blk_write_time.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blk_write_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Num of accessexclusive locks	Number of accessexclusive locks for each database	DEPENDENT	pgsql.locks.accessexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].accessexclusive`
PostgreSQL	DB {#DBNAME}: Num of accessshare locks	Number of accessshare locks for each database	DEPENDENT	pgsql.locks.accessshare["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].accessshare`
PostgreSQL	DB {#DBNAME}: Num of exclusive locks	Number of exclusive locks for each database	DEPENDENT	pgsql.locks.exclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].exclusive`
PostgreSQL	DB {#DBNAME}: Num of rowexclusive locks	Number of rowexclusive locks for each database	DEPENDENT	pgsql.locks.rowexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].rowexclusive`
PostgreSQL	DB {#DBNAME}: Num of rowshare locks	Number of rowshare locks for each database	DEPENDENT	pgsql.locks.rowshare["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].rowshare`
PostgreSQL	DB {#DBNAME}: Num of sharerowexclusive locks	Number of total sharerowexclusive for each database	DEPENDENT	pgsql.locks.sharerowexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].sharerowexclusive`
PostgreSQL	DB {#DBNAME}: Num of shareupdateexclusive locks	Number of shareupdateexclusive locks for each database	DEPENDENT	pgsql.locks.shareupdateexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].shareupdateexclusive`
PostgreSQL	DB {#DBNAME}: Num of share locks	Number of share locks for each database	DEPENDENT	pgsql.locks.share["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].share`
PostgreSQL	DB {#DBNAME}: Num of total locks	Number of total locks for each database	DEPENDENT	pgsql.locks.total["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].total`
PostgreSQL	DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	DEPENDENT	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_max`
PostgreSQL	DB {#DBNAME}: Queries max query time	Max query time	DEPENDENT	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_max`
PostgreSQL	DB {#DBNAME}: Queries max transaction time	Max transaction query time	DEPENDENT	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_max`
PostgreSQL	DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	DEPENDENT	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_slow_count`
PostgreSQL	DB {#DBNAME}: Queries slow query count	Slow query count	DEPENDENT	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_slow_count`
PostgreSQL	DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	DEPENDENT	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_slow_count`
PostgreSQL	DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	DEPENDENT	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_sum`
PostgreSQL	DB {#DBNAME}: Queries sum query time	Sum query time	DEPENDENT	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_sum`
PostgreSQL	DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	DEPENDENT	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_sum`
Zabbix raw items	PostgreSQL: Get bgwriter	https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-BGWRITER-VIEW	ZABBIX_PASSIVE	pgsql.bgwriter["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get archive	Collect archive status metrics	ZABBIX_PASSIVE	pgsql.archive["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	ZABBIX_PASSIVE	pgsql.dbstat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get dbstat sum	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	ZABBIX_PASSIVE	pgsql.dbstat.sum["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get connections	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	ZABBIX_PASSIVE	pgsql.connections["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get WAL	Collect WAL metrics	ZABBIX_PASSIVE	pgsql.wal.stat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	ZABBIX_PASSIVE	pgsql.locks["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get replication	Collect metrics from the pg_stat_replication, which contains information about the WAL sender process, showing statistics about replication to that sender's connected standby server.	ZABBIX_PASSIVE	pgsql.replication.process["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix raw items	PostgreSQL: Get queries	Collect all metrics by query execution time	ZABBIX_PASSIVE	pgsql.queries["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}","{$PG.QUERY_ETIME.MAX.WARN}"]

Triggers

Name	Description	Expression	Severity
Connections sum: Total number of connections is too high (over {$PG.CONN_TOTAL_PCT.MAX.WARN} in 5m)	-	`min(/PostgreSQL by Zabbix agent 2/pgsql.connections.total_pct,5m) > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	AVERAGE
PostgreSQL: Oldest xid is too big	-	`last(/PostgreSQL by Zabbix agent 2/pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]) > 18000000`	AVERAGE
PostgreSQL: Service has been restarted (uptime={ITEM.LASTVALUE})	-	`last(/PostgreSQL by Zabbix agent 2/pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]) < 600`	AVERAGE
PostgreSQL: Service is down	-	`last(/PostgreSQL by Zabbix agent 2/pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"])=0`	HIGH
DB {#DBNAME}: Too many recovery conflicts (over {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"} in 5m)	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them. https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`min(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.conflicts.rate["{#DBNAME}"],5m) > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	AVERAGE
DB {#DBNAME}: Deadlock occurred (over {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"} in 5m)	-	`min(/PostgreSQL by Zabbix agent 2/pgsql.dbstat.deadlocks.rate["{#DBNAME}"],5m) > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	HIGH
DB {#DBNAME}: Too many slow queries (over {$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"} in 5m)	-	`min(/PostgreSQL by Zabbix agent 2/pgsql.queries.query.slow_count["{#DBNAME}"],5m)>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	WARNING

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide feedback, discuss the template or ask for help with it at ZABBIX forums.

This template is for Zabbix version: 5.0

Also available for: 6.4 6.2 6.0 5.4

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql_agent2?at=release/5.0

Template DB PostgreSQL by Zabbix agent 2

Overview

For Zabbix version: 5.0 and higher.
The template is developed to monitor DBMS PostgreSQL and its forks.

This template has been tested on:

PostgreSQL, version 10, 11, 12
Zabbix, version 5.0

Setup

Deploy Zabbix agent2 with Postgres plugin.
Create PostgreSQL user to monitor (<password> at your discretion) and inherit permissions from the default role pg_monitor:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

Edit pg_hba.conf to allow connections from Zabbix agent:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host       all        zbx_monitor     localhost               md5

For more information please read the PostgreSQL documentation https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Set in the {$PG.URI} macro the system data source name of the PostgreSQL instance such as <protocol(host:port)>.
Set the user name and password in host macros ({$PG.USER} and {$PG.PASSWORD}) if you want to override parameters from the Zabbix agent configuration file.

Zabbix configuration

No specific Zabbix configuration is required.

Macros used

Name	Description	Default
{$PG.CONFLICTS.MAX.WARN}	-	`0`
{$PG.CONN_TOTAL_PCT.MAX.WARN}	-	`90`
{$PG.DATABASE}	-	`postgres`
{$PG.DEADLOCKS.MAX.WARN}	-	`0`
{$PG.LLD.FILTER.APPLICATION}	-	`(.+)`
{$PG.LLD.FILTER.DBNAME}	-	`(.+)`
{$PG.PASSWORD}	-	`postgres`
{$PG.QUERY_ETIME.MAX.WARN}	Execution time limit for count of slow queries.	`30`
{$PG.SLOW_QUERIES.MAX.WARN}	Slow queries count threshold for a trigger.	`5`
{$PG.URI}	-	`tcp://localhost:5432`
{$PG.USER}	-	`postgres`

Template links

There are no template links in this template.

Discovery rules

Name Description Type Key and additional info

Replication Discovery

Name	Description	Type	Key and additional info
Replication Discovery	-	ZABBIX_PASSIVE	pgsql.replication.process.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Filter: AND - A: {#APPLICATION_NAME} MATCHES_REGEX `{$PG.LLD.FILTER.APPLICATION}`
Database discovery	-	ZABBIX_PASSIVE	pgsql.db.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Filter: AND - A: {#DBNAME} MATCHES_REGEX `{$PG.LLD.FILTER.DBNAME}`

ZABBIX_PASSIVE

pgsql.replication.process.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Filter:

AND

- A: {#APPLICATION_NAME} MATCHES_REGEX {$PG.LLD.FILTER.APPLICATION}

Database discovery

ZABBIX_PASSIVE

pgsql.db.discovery["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]

Filter:

AND

- A: {#DBNAME} MATCHES_REGEX {$PG.LLD.FILTER.DBNAME}

Items collected

Group	Name	Description	Type	Key and additional info
PostgreSQL	PostgreSQL: Custom queries	Execute custom queries from file *.sql (check for option Plugins.Postgres.CustomQueriesPath at agent configuration)	ZABBIX_PASSIVE	pgsql.custom.query["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}",""]
PostgreSQL	WAL: Bytes written	WAL write in bytes	DEPENDENT	pgsql.wal.write Preprocessing: - JSONPATH: `$.write` - CHANGE_PER_SECOND
PostgreSQL	WAL: Bytes received	WAL receive in bytes	DEPENDENT	pgsql.wal.receive Preprocessing: - JSONPATH: `$.receive` - CHANGE_PER_SECOND
PostgreSQL	WAL: Segments count	Number of WAL segments	DEPENDENT	pgsql.wal.count Preprocessing: - JSONPATH: `$.count`
PostgreSQL	Bgwriter: Buffers allocated	Number of buffers allocated	DEPENDENT	pgsql.bgwriter.buffers_alloc.rate Preprocessing: - JSONPATH: `$.buffers_alloc` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Buffers written directly by a backend	Number of buffers written directly by a backend	DEPENDENT	pgsql.bgwriter.buffers_backend.rate Preprocessing: - JSONPATH: `$.buffers_backend` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Number of bgwriter stopped	Number of times the background writer stopped a cleaning scan because it had written too many buffers	DEPENDENT	pgsql.bgwriter.maxwritten_clean.rate Preprocessing: - JSONPATH: `$.maxwritten_clean` - CHANGE_PER_SECOND
PostgreSQL	Bgwriter: Times a backend execute its own fsync	Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)	DEPENDENT	pgsql.bgwriter.buffers_backend_fsync.rate Preprocessing: - JSONPATH: `$.buffers_backend_fsync` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Buffers background written	Number of buffers written by the background writer	DEPENDENT	pgsql.bgwriter.buffers_clean.rate Preprocessing: - JSONPATH: `$.buffers_clean` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Buffers checkpoints written	Number of buffers written during checkpoints	DEPENDENT	pgsql.bgwriter.buffers_checkpoint.rate Preprocessing: - JSONPATH: `$.buffers_checkpoint` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: By timeout	Number of scheduled checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_timed.rate Preprocessing: - JSONPATH: `$.checkpoints_timed` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Requested	Number of requested checkpoints that have been performed	DEPENDENT	pgsql.bgwriter.checkpoints_req.rate Preprocessing: - JSONPATH: `$.checkpoints_req` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds	DEPENDENT	pgsql.bgwriter.checkpoint_write_time.rate Preprocessing: - JSONPATH: `$.checkpoint_write_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Checkpoint write time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in milliseconds	DEPENDENT	pgsql.bgwriter.checkpoint_sync_time.rate Preprocessing: - JSONPATH: `$.checkpoint_sync_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Checkpoint: Checkpoint sync time	Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk	DEPENDENT	pgsql.bgwriter.checkpoint_sync_time.rate Preprocessing: - JSONPATH: `$.checkpoint_sync_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	Archive: Count of archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	DEPENDENT	pgsql.archive.count_archived_files Preprocessing: - JSONPATH: `$.archived_count`
PostgreSQL	Archive: Count of attempts to archive files	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ARCHIVER-VIEW	DEPENDENT	pgsql.archive.failed_trying_to_archive Preprocessing: - JSONPATH: `$.failed_count`
PostgreSQL	Archive: Count of files in archive_status need to archive	-	DEPENDENT	pgsql.archive.count_files_to_archive Preprocessing: - JSONPATH: `$.count_files`
PostgreSQL	Archive: Count of files need to archive	Size of files to archive	DEPENDENT	pgsql.archive.size_files_to_archive Preprocessing: - JSONPATH: `$.size_files`
PostgreSQL	Dbstat: Blocks read time	Time spent reading data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.sum.blk_read_time Preprocessing: - JSONPATH: `$.blk_read_time` - MULTIPLIER: `0.001`
PostgreSQL	Dbstat: Blocks write time	Time spent writing data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.sum.blk_write_time Preprocessing: - JSONPATH: `$.blk_write_time` - MULTIPLIER: `0.001`
PostgreSQL	Dbstat: Checksum failures	Number of data page checksum failures detected (or on a shared object), or NULL if data checksums are not enabled. This metric included in PostgreSQL 12	DEPENDENT	pgsql.dbstat.sum.checksum_failures.rate Preprocessing: - JSONPATH: `$.checksum_failures` - MATCHES_REGEX: `^\d*$` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Committed transactions	Number of transactions that have been committed	DEPENDENT	pgsql.dbstat.sum.xact_commit.rate Preprocessing: - JSONPATH: `$.xact_commit` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Conflicts	Number of queries canceled due to conflicts with recovery. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.)	DEPENDENT	pgsql.dbstat.sum.conflicts.rate Preprocessing: - JSONPATH: `$.conflicts` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Deadlocks	Number of deadlocks detected	DEPENDENT	pgsql.dbstat.sum.deadlocks.rate Preprocessing: - JSONPATH: `$.deadlocks` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Disk blocks read	Number of disk blocks read	DEPENDENT	pgsql.dbstat.sum.blks_read.rate Preprocessing: - JSONPATH: `$.blks_read` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Hit blocks read	Number of times disk blocks were found already in the buffer cache	DEPENDENT	pgsql.dbstat.sum.blks_hit.rate Preprocessing: - JSONPATH: `$.blks_hit` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Number temp bytes	Total amount of data written to temporary files by queries	DEPENDENT	pgsql.dbstat.sum.temp_bytes.rate Preprocessing: - JSONPATH: `$.temp_bytes` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Number temp bytes	Number of temporary files created by queries	DEPENDENT	pgsql.dbstat.sum.temp_files.rate Preprocessing: - JSONPATH: `$.temp_files` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Roll backed transactions	Number of transactions that have been rolled back	DEPENDENT	pgsql.dbstat.sum.xact_rollback.rate Preprocessing: - JSONPATH: `$.xact_rollback` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows deleted	Number of rows deleted by queries	DEPENDENT	pgsql.dbstat.sum.tup_deleted.rate Preprocessing: - JSONPATH: `$.tup_deleted` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows fetched	Number of rows fetched by queries	DEPENDENT	pgsql.dbstat.sum.tup_fetched.rate Preprocessing: - JSONPATH: `$.tup_fetched` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows inserted	Number of rows inserted by queries	DEPENDENT	pgsql.dbstat.sum.tup_inserted.rate Preprocessing: - JSONPATH: `$.tup_inserted` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows returned	Number of rows returned by queries	DEPENDENT	pgsql.dbstat.sum.tup_returned.rate Preprocessing: - JSONPATH: `$.tup_returned` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Rows updated	Number of rows updated by queries	DEPENDENT	pgsql.dbstat.sum.tup_updated.rate Preprocessing: - JSONPATH: `$.tup_updated` - CHANGE_PER_SECOND
PostgreSQL	Dbstat: Backends connected	Number of connected backends	DEPENDENT	pgsql.dbstat.sum.numbackends Preprocessing: - JSONPATH: `$.numbackends`
PostgreSQL	Connections sum: Active	Total number of connections executing a query	DEPENDENT	pgsql.connections.active Preprocessing: - JSONPATH: `$.active`
PostgreSQL	Connections sum: Fastpath function call	Total number of connections executing a fast-path function	DEPENDENT	pgsql.connections.fastpath_function_call Preprocessing: - JSONPATH: `$.idle_in_transaction`
PostgreSQL	Connections sum: Idle	Total number of connections waiting for a new client command	DEPENDENT	pgsql.connections.idle Preprocessing: - JSONPATH: `$.idle`
PostgreSQL	Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	DEPENDENT	pgsql.connections.idle_in_transaction Preprocessing: - JSONPATH: `$.idle_in_transaction`
PostgreSQL	Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	DEPENDENT	pgsql.connections.prepared Preprocessing: - JSONPATH: `$.prepared`
PostgreSQL	Connections sum: Total	Total number of connections	DEPENDENT	pgsql.connections.total Preprocessing: - JSONPATH: `$.total`
PostgreSQL	Connections sum: Total %	Total number of connections in percentage	DEPENDENT	pgsql.connections.total_pct Preprocessing: - JSONPATH: `$.total_pct`
PostgreSQL	Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	DEPENDENT	pgsql.connections.waiting Preprocessing: - JSONPATH: `$.waiting`
PostgreSQL	Connections sum: Idle in transaction (aborted)	Total number of connections in a transaction state, but not executing a query and one of the statements in the transaction caused an error.	DEPENDENT	pgsql.connections.idle_in_transaction_aborted Preprocessing: - JSONPATH: `$.idle_in_transaction_aborted`
PostgreSQL	Connections sum: Disabled	Total number of disabled connections	DEPENDENT	pgsql.connections.disabled Preprocessing: - JSONPATH: `$.disabled`
PostgreSQL	PostgreSQL: Age of oldest xid	Age of oldest xid.	ZABBIX_PASSIVE	pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Autovacuum: Count of autovacuum workers	Number of autovacuum workers.	ZABBIX_PASSIVE	pgsql.autovacuum.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	PostgreSQL: Cache hit	-	CALCULATED	pgsql.cache.hit["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Expression: `last("pgsql.dbstat.sum.blks_hit.rate") * 100 / (last("pgsql.dbstat.sum.blks_hit.rate") + last("pgsql.dbstat.sum.blks_read.rate"))`
PostgreSQL	PostgreSQL: Uptime	-	ZABBIX_PASSIVE	pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Lag in bytes	Replication lag with Master in byte.	ZABBIX_PASSIVE	pgsql.replication.lag.b["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Lag in seconds	Replication lag with Master in seconds.	ZABBIX_PASSIVE	pgsql.replication.lag.sec["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	ZABBIX_PASSIVE	pgsql.replication.recovery_role["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Standby count	Number of standby servers	ZABBIX_PASSIVE	pgsql.replication.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	Replication: Status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	ZABBIX_PASSIVE	pgsql.replication.status["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
PostgreSQL	PostgreSQL: Ping	-	ZABBIX_PASSIVE	pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"] Preprocessing: - DISCARD_UNCHANGED_HEARTBEAT: `1h`
PostgreSQL	Application {#APPLICATION_NAME}: Replication flush lag		DEPENDENT	pgsql.replication.process.flush_lag["{#APPLICATION_NAME}"] Preprocessing: - JSONPATH: `$['{#APPLICATION_NAME}'].flush_lag`
PostgreSQL	Application {#APPLICATION_NAME}: Replication replay lag		DEPENDENT	pgsql.replication.process.replay_lag["{#APPLICATION_NAME}"] Preprocessing: - JSONPATH: `$['{#APPLICATION_NAME}'].replay_lag`
PostgreSQL	Application {#APPLICATION_NAME}: Replication write lag		DEPENDENT	pgsql.replication.process.write_lag["{#APPLICATION_NAME}"] Preprocessing: - JSONPATH: `$['{#APPLICATION_NAME}'].write_lag`
PostgreSQL	DB {#DBNAME}: Database age	Database age	ZABBIX_PASSIVE	pgsql.db.age["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Get bloating tables	Number of bloating tables	ZABBIX_PASSIVE	pgsql.db.bloating_tables["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Database size	Database size	ZABBIX_PASSIVE	pgsql.db.size["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{#DBNAME}"]
PostgreSQL	DB {#DBNAME}: Blocks hit per second	Total number of times disk blocks were found already in the buffer cache, so that a read was not necessary	DEPENDENT	pgsql.dbstat.blks_hit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_hit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	DEPENDENT	pgsql.dbstat.blks_read.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blks_read` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	DEPENDENT	pgsql.dbstat.conflicts.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].conflicts` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	DEPENDENT	pgsql.dbstat.deadlocks.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].deadlocks` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	DEPENDENT	pgsql.dbstat.temp_bytes.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_bytes` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	DEPENDENT	pgsql.dbstat.temp_files.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].temp_files` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	DEPENDENT	pgsql.dbstat.tup_deleted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_deleted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	DEPENDENT	pgsql.dbstat.tup_fetched.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_fetched` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	DEPENDENT	pgsql.dbstat.tup_inserted.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_inserted` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples returned per second	Number of rows returned by queries in this database	DEPENDENT	pgsql.dbstat.tup_returned.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_returned` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	DEPENDENT	pgsql.dbstat.tup_updated.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tup_updated` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	DEPENDENT	pgsql.dbstat.xact_commit.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_commit` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	DEPENDENT	pgsql.dbstat.xact_rollback.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].xact_rollback` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Backends connected	Number of backends currently connected to this database	DEPENDENT	pgsql.dbstat.numbackends["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].numbackends`
PostgreSQL	DB {#DBNAME}: Checksum failures	Number of data page checksum failures detected in this database	DEPENDENT	pgsql.dbstat.checksum_failures.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].checksum_failures` - MATCHES_REGEX: `^\d*$` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks read time	Time spent reading data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.blk_read_time.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blk_read_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Disk blocks write time	Time spent writing data file blocks by backends, in milliseconds	DEPENDENT	pgsql.dbstat.blk_write_time.rate["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].blk_write_time` - MULTIPLIER: `0.001` - CHANGE_PER_SECOND
PostgreSQL	DB {#DBNAME}: Num of accessexclusive locks	Number of accessexclusive locks for each database	DEPENDENT	pgsql.locks.accessexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].accessexclusive`
PostgreSQL	DB {#DBNAME}: Num of accessshare locks	Number of accessshare locks for each database	DEPENDENT	pgsql.locks.accessshare["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].accessshare`
PostgreSQL	DB {#DBNAME}: Num of exclusive locks	Number of exclusive locks for each database	DEPENDENT	pgsql.locks.exclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].exclusive`
PostgreSQL	DB {#DBNAME}: Num of rowexclusive locks	Number of rowexclusive locks for each database	DEPENDENT	pgsql.locks.rowexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].rowexclusive`
PostgreSQL	DB {#DBNAME}: Num of rowshare locks	Number of rowshare locks for each database	DEPENDENT	pgsql.locks.rowshare["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].rowshare`
PostgreSQL	DB {#DBNAME}: Num of sharerowexclusive locks	Number of total sharerowexclusive for each database	DEPENDENT	pgsql.locks.sharerowexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].sharerowexclusive`
PostgreSQL	DB {#DBNAME}: Num of shareupdateexclusive locks	Number of shareupdateexclusive locks for each database	DEPENDENT	pgsql.locks.shareupdateexclusive["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].shareupdateexclusive`
PostgreSQL	DB {#DBNAME}: Num of share locks	Number of share locks for each database	DEPENDENT	pgsql.locks.share["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].share`
PostgreSQL	DB {#DBNAME}: Num of total locks	Number of total locks for each database	DEPENDENT	pgsql.locks.total["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].total`
PostgreSQL	DB {#DBNAME}: Queries max maintenance time	Max maintenance query time	DEPENDENT	pgsql.queries.mro.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_max`
PostgreSQL	DB {#DBNAME}: Queries max query time	Max query time	DEPENDENT	pgsql.queries.query.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_max`
PostgreSQL	DB {#DBNAME}: Queries max transaction time	Max transaction query time	DEPENDENT	pgsql.queries.tx.time_max["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_max`
PostgreSQL	DB {#DBNAME}: Queries slow maintenance count	Slow maintenance query count	DEPENDENT	pgsql.queries.mro.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_slow_count`
PostgreSQL	DB {#DBNAME}: Queries slow query count	Slow query count	DEPENDENT	pgsql.queries.query.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_slow_count`
PostgreSQL	DB {#DBNAME}: Queries slow transaction count	Slow transaction query count	DEPENDENT	pgsql.queries.tx.slow_count["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_slow_count`
PostgreSQL	DB {#DBNAME}: Queries sum maintenance time	Sum maintenance query time	DEPENDENT	pgsql.queries.mro.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].mro_time_sum`
PostgreSQL	DB {#DBNAME}: Queries sum query time	Sum query time	DEPENDENT	pgsql.queries.query.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].query_time_sum`
PostgreSQL	DB {#DBNAME}: Queries sum transaction time	Sum transaction query time	DEPENDENT	pgsql.queries.tx.time_sum["{#DBNAME}"] Preprocessing: - JSONPATH: `$['{#DBNAME}'].tx_time_sum`
Zabbix_raw_items	PostgreSQL: Get bgwriter	https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-BGWRITER-VIEW	ZABBIX_PASSIVE	pgsql.bgwriter["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix_raw_items	PostgreSQL: Get archive	Collect archive status metrics	ZABBIX_PASSIVE	pgsql.archive["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix_raw_items	PostgreSQL: Get dbstat	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	ZABBIX_PASSIVE	pgsql.dbstat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix_raw_items	PostgreSQL: Get dbstat sum	Collect all metrics from pg_stat_database per database https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW	ZABBIX_PASSIVE	pgsql.dbstat.sum["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix_raw_items	PostgreSQL: Get connections	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	ZABBIX_PASSIVE	pgsql.connections["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix_raw_items	PostgreSQL: Get WAL	Collect WAL metrics	ZABBIX_PASSIVE	pgsql.wal.stat["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix_raw_items	PostgreSQL: Get locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	ZABBIX_PASSIVE	pgsql.locks["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix_raw_items	PostgreSQL: Get replication	Collect metrics from the pg_stat_replication, which contains information about the WAL sender process, showing statistics about replication to that sender's connected standby server.	ZABBIX_PASSIVE	pgsql.replication.process["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
Zabbix_raw_items	PostgreSQL: Get queries	Collect all metrics by query execution time	ZABBIX_PASSIVE	pgsql.queries["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","{$PG.DATABASE}","{$PG.QUERY_ETIME.MAX.WARN}"]

Triggers

Name	Description	Expression	Severity
Dbstat: Checksum failures detected	Data page checksum failures were detected on that DB instance. https://www.postgresql.org/docs/current/checksums.html	`{TEMPLATE_NAME:pgsql.dbstat.sum.checksum_failures.rate.last()}>0`	AVERAGE
Connections sum: Total number of connections is too high (over {$PG.CONN_TOTAL_PCT.MAX.WARN} in 5m)	-	`{TEMPLATE_NAME:pgsql.connections.total_pct.min(5m)} > {$PG.CONN_TOTAL_PCT.MAX.WARN}`	AVERAGE
PostgreSQL: Oldest xid is too big	-	`{TEMPLATE_NAME:pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"].last()} > 18000000`	AVERAGE
PostgreSQL: Service has been restarted (uptime={ITEM.LASTVALUE})	-	`{TEMPLATE_NAME:pgsql.uptime["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"].last()} < 600`	AVERAGE
PostgreSQL: Service is down	-	`{TEMPLATE_NAME:pgsql.ping["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"].last()}=0`	HIGH
DB {#DBNAME}: Too many recovery conflicts (over {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"} in 5m)	The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them. https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT	`{TEMPLATE_NAME:pgsql.dbstat.conflicts.rate["{#DBNAME}"].min(5m)} > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}`	AVERAGE
DB {#DBNAME}: Deadlock occurred (over {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"} in 5m)	-	`{TEMPLATE_NAME:pgsql.dbstat.deadlocks.rate["{#DBNAME}"].min(5m)} > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}`	HIGH
DB {#DBNAME}: Checksum failures detected	Data page checksum failures were detected on that database. https://www.postgresql.org/docs/current/checksums.html	`{TEMPLATE_NAME:pgsql.dbstat.checksum_failures.rate["{#DBNAME}"].last()}>0`	AVERAGE
DB {#DBNAME}: Too many slow queries (over {$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"} in 5m)	-	`{TEMPLATE_NAME:pgsql.queries.query.slow_count["{#DBNAME}"].min(5m)}>{$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}`	WARNING

Feedback

Please report any issues with the template at https://support.zabbix.com.

Link	Source	Compatibility	Type, Technology	Created Updated	Rating
PostgreSQL monitoring with Zabbix The Template DB PostgreSQL was formerly known as pgCayenne. It is a set of UserParameter for PostgreSQL monitoring, which consists of Zabbix agent configuration and XML Template for web monitoring. It is using built-in PostgreSQL system views and functions. The agent requires the standard psql client ... github.com/lesovsky/zabbix-extensions/tree/master/files/postgresql	GitHub	3.4	Template		Recommended
Postgres ODBC I have been looking for templates to use with the new ODBC database monitoring item type with out success.I have created a rudimentary one for postgres. To use it follow these instructions for setting up ODBC on the server side.https://www.zabbix.com/documentation/4.0/manual/config/items/itemtypes/odbc\_checksWe ... template_postgres_odbc_database_monitoring	GitHub Community Templates	5.0+
Zabbix plugin to monitor RDBMS ZabbixDBA is fast, flexible, and continuously developing plugin to monitor your RDBMS. ZabbixDBA uses threading of DBI connections which is good for monitoring of multiple database instances simultaneously. Just configure it, run daemon and it will do all the job github.com/netrusov/ZabbixDBA	GitHub 83		Module, Template Perl	2015-06-29 1 m	Popular
PostgreSQL partitioning Scripts to install, manage, and remove PostgreSQL partitioning for Zabbix DB github.com/robbrucks/zabbix-postgresql-auto-partitioning	GitHub 14		Shell	2017-05-15 2 m
Docker for postgreSQL Docker container for Zabbix DB on postrges, automatic partitioning. github.com/blackcobra1973/zabbix-db-postgresql	GitHub 5		docker Shell	2015-12-18 6 y
pgmon_2ndq is a set of postgresql monitoring functions implemented in the server. The functions are installed in their own database and connect back to all databases in the server to get database specific infoA cronjob gets the monitoring info from these functions in a single database call and saves it to a file github.com/postsql/pgmon_zabbix	GitHub 2		PLpgSQL	2011-12-30 5 y
Monitoring PostgreSQL in Zabbix Monitoring will be based on SQL queries to the statistics tables. github.com/CBEPX/PostgreSQL_Zabbix	GitHub 2		Template, Userparameter	2017-07-06 1 y
Backup script for Zabbix on postgreSQL github.com/YxRyaB/zabbix-postgres-backup	GitHub 1		Shell	2015-07-22 5 y

See all Zabbix community templates

Zabbix + Postgresql

Postgresql

Available solutions

PostgreSQL by Zabbix agent

Overview

Requirements

Tested versions

Configuration

Setup

Macros used

Items

Triggers

LLD rule Database discovery

Item prototypes for Database discovery

Trigger prototypes for Database discovery

Feedback

PostgreSQL by Zabbix agent

Overview

Setup

Zabbix configuration

Macros used

Template links

Discovery rules

Items collected

Triggers

Feedback

PostgreSQL by Zabbix agent

Overview

Requirements

Tested versions

Configuration

Setup

Macros used

Items

Triggers

LLD rule Database discovery

Item prototypes for Database discovery

Trigger prototypes for Database discovery

Feedback

PostgreSQL by user parameters

Overview

Setup

Zabbix configuration

Macros used

Template links

Discovery rules

Items collected

Triggers

Feedback

Template DB PostgreSQL by user parameters

Overview

Setup

Zabbix configuration

Macros used

Template links

Discovery rules

Items collected

Triggers

Feedback

PostgreSQL by Zabbix agent 2

Overview

Requirements

Tested versions

Configuration

Setup

Macros used

Items

Triggers

LLD rule Replication Discovery

Item prototypes for Replication Discovery

LLD rule Database discovery

Item prototypes for Database discovery

Trigger prototypes for Database discovery

Feedback

PostgreSQL by Zabbix agent 2

Overview

Setup

Zabbix configuration

Macros used

Template links