Available solutions

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

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql

Template DB PostgreSQL

Overview

Templates to monitor PostgreSQL by Zabbix.\ This template was tested on Zabbix 4.2.1 and PostgreSQL vesions 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
```
If you need to monitor the remote server then create .pgpass file in Zabbix agent home directory /var/lib/zabbix/ and add the connection details with the instance, port, database, user and password information in the below format https://www.postgresql.org/docs/current/libpq-pgpass.html.

Example 1:
```
<REMOTE_HOST1>:5432:postgres:zbx_monitor:<PASSWORD>

<REMOTE_HOST2>:5432:postgres:zbx_monitor:<PASSWORD>
...
<REMOTE_HOSTN>:5432:postgres:zbx_monitor:<PASSWORD>
```
Example 2:
```
*:5432:postgres:zbx_monitor:<PASSWORD>
```
Import template_db_postgresql.xml to Zabbix and link it to the target host

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

Macro	Description	Default
{$PG.HOST}	Database server host or socket directory	127.0.0.1
{$PG.PORT}	Database server port	5432
{$PG.USER}	Database user name	zbx_monitor
{$PG.DB}	Database name to connect to the server	postgres
{$PG.LLD.FILTER.DBNAME}	Regular expression for filtering names of discovered databases	(.*)
{$PG.CHECKPOINTS_REQ.MAX.WARN}	Requested checkpoints threshold for trigger expression	5
{$PG.PING_TIME.MAX.WARN}	Maximum ping time for trigger expression	1s
{$PG.CACHE_HITRATIO.MIN.WARN}	Minimum cache hit ratio for trigger expression	90
{$PG.CONN_TOTAL_PCT.MAX.WARN}	Maximum number of open connections for trigger expression	90
{$PG.CONN_WAIT.MAX.WARN}	Maximum number of waiting connections for trigger expression	0
{$PG.CONN_IDLE_IN_TRANS.MAX.WARN}	Maximum number of 'idle in transaction' connections for trigger expression	5
{$PG.DEADLOCKS.MAX.WARN}	Maximum number of deadlocks for trigger expression	0
{$PG.CONFLICTS.MAX.WARN}	Maximum number of recovery conflicts for trigger expression	0
{$PG.REPL_LAG.MAX.WARN}	Maximum replication lag for trigger expression	10m
{$PG.TRANS_ACTIVE.MAX.WARN}	Maximum active transaction time for trigger expression	30s
{$PG.TRANS_IDLE.MAX.WARN}	Maximum 'idle in transaction' connection time for trigger expression	30s
{$PG.TRANS_WAIT.MAX.WARN}	Maximum waiting transaction time for trigger expression	30s
{$PG.LOCKS.MAX.WARN}	Maximum number of locks for trigger expression	100
{$PG.QUERY_ETIME.MAX.WARN}	Maximum query execution time in seconds	30
{$PG.SLOW_QUERIES.MAX.WARN}	Maximum number of slow queries for trigger expression	5
{$PG.FROZENXID_PCT_STOP.MIN.HIGH}	Minimum percentage of frozen XID	75

Template links

There are no template links in this template.

Discovery rules

Name	Description	Type
Databases discovery	Use the macro {$PG.LLD.FILTER.DBNAME} to filter the discovered databases	Zabbix agent

Items collected

Name	Description	Type
Bgwriter	Statistics about the background writer process's activity	Zabbix agent
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
Bgwriter: Buffers backend 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
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
Bgwriter: Requested checkpoints	Number of requested checkpoints that have been performed	Dependent item
Bgwriter: Max written	Number of times the background writer stopped a cleaning scan because it had written too many buffers	Dependent item
Bgwriter: Scheduled checkpoints	Number of scheduled checkpoints that have been performed	Dependent item
Bgwriter: Buffers written during checkpoints	Number of buffers written during checkpoints	Dependent item
Bgwriter: Buffers written directly by a backend	Number of buffers written directly by a backend	Dependent item
Bgwriter: Buffers written by the background writer	Number of buffers written by the background writer	Dependent item
Bgwriter: Buffers allocated	Number of buffers allocated	Dependent item
Status: Version		Zabbix agent
Status: Ping		Zabbix agent
Status: Ping time		Zabbix agent
Status: Uptime		Zabbix agent
Status: Cache hit ratio %	Cache hit ratio	Zabbix agent
Status: DB {#DBNAME}: Size	Database size	Zabbix agent
Connections sum	Collect all metrics from pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW	Zabbix agent
Connections sum: Total %	Total number of connections in percentage	Dependent item
Connections sum: Total	Total number of connections	Dependent item
Connections sum: Waiting	Total number of waiting connections https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE	Dependent item
Connections sum: Idle in transaction	Total number of connections in a transaction state, but not executing a query	Dependent item
Connections sum: Idle	Total number of connections waiting for a new client command	Dependent item
Connections sum: Active	Total number of connections executing a query	Dependent item
Connections sum: Prepared	Total number of prepared transactions https://www.postgresql.org/docs/current/sql-prepare-transaction.html	Dependent item
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
DB {#DBNAME}: Commits per second	Number of transactions in this database that have been committed	Dependent item
DB {#DBNAME}: Tuples updated per second	Total number of rows updated by queries in this database	Dependent item
DB {#DBNAME}: Tuples returned per second	Total number of rows updated by queries in this database	Dependent item
DB {#DBNAME}: Tuples inserted per second	Total number of rows inserted by queries in this database	Dependent item
DB {#DBNAME}: Tuples fetched per second	Total number of rows fetched by queries in this database	Dependent item
DB {#DBNAME}: Tuples deleted per second	Total number of rows deleted by queries in this database	Dependent item
DB {#DBNAME}: Temp_files created per second	Total number of temporary files created by queries in this database	Dependent item
DB {#DBNAME}: Temp_bytes written per second	Total amount of data written to temporary files by queries in this database	Dependent item
DB {#DBNAME}: Rollbacks per second	Total number of transactions in this database that have been rolled back	Dependent item
DB {#DBNAME}: Detected deadlocks per second	Total number of detected deadlocks in this database	Dependent item
DB {#DBNAME}: Detected conflicts per second	Total number of queries canceled due to conflicts with recovery in this database	Dependent item
DB {#DBNAME}: Disk blocks read per second	Total number of disk blocks read in this database	Dependent item
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
Replication: standby count	Number of standby servers	Zabbix agent
Replication: recovery role	Replication role: 1 — recovery is still in progress (standby mode), 0 — master mode.	Zabbix agent
Replication: status	Replication status: 0 — streaming is down, 1 — streaming is up, 2 — master mode	Zabbix agent
Replication: lag in seconds	Replication lag with Master in seconds	Zabbix agent
Transactions	Collect metrics by transaction execution time	Zabbix agent
Transactions: Max active transaction time	Current max active transaction time	Dependent item
Transactions: Max idle transaction time	Current max idle transaction time	Dependent item
Transactions: Max prepared transaction time	Current max prepared transaction time	Dependent item
Transactions: Max waiting transaction time	Current max waiting transaction time	Dependent item
Status: Config hash	PostgreSQL configuration hash	Zabbix agent
WAL	Master item to collect WAL metrics	Zabbix agent
WAL: Bytes written per second	WAL write in bytes	Dependent item
WAL: Segments count	Number of WAL segments	Dependent item
Locks	Collect all metrics from pg_locks per database https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES	Zabbix agent
DB {#DBNAME} locks: Total	Total number of locks in the database	Dependent item
DB {#DBNAME} scans	Number of scans done for table/index in the database	Zabbix agent
DB {#DBNAME} scans: Index	Number of index scans in the database	Dependent item
DB {#DBNAME} scans: Sequential	Number of sequential scans in the database	Dependent item
Queries	Collect all metrics by query execution time	Zabbix agent
DB {#DBNAME} queries: Max maintenance time	Max maintenance query time	Dependent item
DB {#DBNAME} queries: Max query time	Max query time	Dependent item
DB {#DBNAME} queries: Max transaction time	Max transaction query time	Dependent item
DB {#DBNAME} queries: Sum maintenance time	Sum maintenance query time	Dependent item
DB {#DBNAME} queries: Sum query time	Sum query time	Dependent item
DB {#DBNAME} queries: Sum transaction time	Sum transaction query time	Dependent item
DB {#DBNAME} queries: Slow maintenance count	Slow maintenance query count	Dependent item
DB {#DBNAME} queries: Slow query count	Slow query query count	Dependent item
DB {#DBNAME} queries: Slow transaction count	Slow transaction query count	Dependent item
DB {#DBNAME} frozen XID		Zabbix agent
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
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

Triggers

Name	Severity	Expression	Description	Dependencies
PostgreSQL: Required checkpoints occurs too frequently (over {$PG.CHECKPOINTS_REQ.MAX.WARN})	Average	pgsql.bgwriter.checkpoints_req.last() > {$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
PostgreSQL: Response too long (over {$PG.PING_TIME.MAX.WARN})	Average	pgsql.ping.time.min(5m) > {$PG.PING_TIME.MAX.WARN}		PostgreSQL: Service is down
PostgreSQL: Service is down	High	pgsql.ping.last() = 0
PostgreSQL: Failed to get items (no data for 30m)	Warning	pgsql.bgwriter.nodata(30m) = 1	Zabbix has not received data for items for the last 30 minutes	PostgreSQL: Service is down
PostgreSQL: Service has been restarted (uptime < 10m)	Info	pgsql.uptime.last() < 10m	PostgreSQL uptime is less than 10 minutes
PostgreSQL: Cache hit ratio too low (under {$PG.CACHE_HITRATIO.MIN.WARN} in 5m)	Warning	pgsql.cache.hit.max(5m) < {$PG.CACHE_HITRATIO.MIN.WARN}
PostgreSQL: Total number of connections is too high (over {$PG.CONN_TOTAL_PCT.MAX.WARN} in 5m)	Average	pgsql.connections.sum.total_pct.min(5m) > {$PG.CONN_TOTAL_PCT.MAX.WARN}
Database {#DBNAME}: Deadlock occurred (over {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"} in 5m)	High	pgsql.dbstat.deadlocks["{#DBNAME}"].min(5m) > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}
PostgreSQL: Too many recovery conflicts (over {$PG.CONFLICTS.MAX.WARN} in 5m)	Average	pgsql.dbstat.sum.conflicts.min(5m) > {$PG.CONFLICTS.MAX.WARN}	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
PostgreSQL: Streaming lag is too high (over {$PG.REPL_LAG.MAX.WARN} in 5m)	Average	pgsql.streaming.lag.sec.min(5m) > {$PG.REPL_LAG.MAX.WARN}
PostgreSQL: Configuration has changed	Info	pgsql.config.diff() = 1 and pgsql.config.strlen() > 0
PostgreSQL: Version has changed (new version value received: {ITEM.VALUE})	Info	pgsql.version.diff() = 1 and pgsql.version.strlen() > 0
Database {#DBNAME}: Number of locks is too high (over {$PG.LOCKS.MAX.WARN:"{#DBNAME}"} in 5m)	Warning	pgsql.locks.total["{#DBNAME}"].min(5m) > {$PG.LOCKS.MAX.WARN:"{#DBNAME}"}
Database {#DBNAME}: Too many slow queries (over {$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"} in 5m)	Warning	pgsql.queries.query.slow_count["{#DBNAME}"].min(5m) > {$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"}
Database {#DBNAME}: VACUUM FREEZE is required to prevent wraparound (frozen XID less then {$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"} %)	Average	pgsql.db.frozenxid_prc.before_stop["{#DBNAME}"].last() < {$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"}
PostgreSQL: Replication is down	Average	pgsql.replication.status.max(5m) = 0

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.

References

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql_agent2

Template DB PostgreSQL Agent 2

Overview

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

This template was tested on:

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

Setup

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

CREATE USER 'zbx_monitor' IDENTIFIED BY '<password>';
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;

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD
    host            all              zbx_monitor            127.0.0.1                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.DEADLOCKS.MAX.WARN}	-	`0`
{$PG.LLD.FILTER.DBNAME}	-	`(.+)`
{$PG.PASSWORD}	-	`postgres`
{$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 - A: {#DBNAME} MATCHES_REGEX `{$PG.LLD.FILTER.DBNAME}`

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	WAL: Bytes written	WAL write in bytes	DEPENDENT	pgsql.wal.write Preprocessing: - JSONPATH: `$.write` - CHANGE_PER_SECOND
PostgreSQL	WAL: Segments count	Number of WAL segments	DEPENDENT	pgsql.wal.count Preprocessing: - JSONPATH: `$.write`
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 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.sync_time.rate Preprocessing: - JSONPATH: `$.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
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	-	ZABBIX_PASSIVE	pgsql.cache.hit["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]
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	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 оf 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 per second	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 read per second	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`
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}"]

Triggers

Name	Description	Expression	Severity
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

Feedback

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

Link	Source	Compatibility	Type, Technology	Created Updated	Rating
PgBouncer monitorong template Description Check PgBouncer status and LLD pools. Check pools metrics. Dependencies psql, bash, zabbix-agent. ✔ Low Level Discovery (LLD) ✔ Zabbix Agent github.com/Lelik13a/Zabbix-PgBouncer share.zabbix.com/pgbouncer-monitorong-template	share.zabbix.com		Template	2020-05-14 9 m
Zabbix Agent - PostgreSQL ✔ Zabbix Agent github.com/sergiotocalini/zapgix share.zabbix.com/zapgix	share.zabbix.com GitHub 20		Template, Userparameter Shell	2018-02-28 2021 y	Popular
Module for monitoring PostgreSQL ibzbxpgsql (Lib-Zabbix-PostgreSQL) provides detailed and granular monitoring of PostgreSQL servers using a native Zabbix agent module ✔ Database Monitor cavaliercoder.com/libzbxpgsql/ share.zabbix.com/postgresql-monitoring-for-zabbix	share.zabbix.com 1		c	2015-08-23 2 y	Recommended
PostgreSQL monitoring template for Zabbix pg_monz enables various types of monitoring of PostgreSQL such as alive, resource, performance, etc. It supports some constitution patterns which includes single PostgreSQL pattern, HA pattern with Streaming Replication and load balancing pattern with pgpool-II ✔ LLD ✔ Zabbix Agent pg-monz.github.io/pg_monz/index-en.html share.zabbix.com/postgresql	share.zabbix.com 1665	1.8	Template, Userparameter	2014-09-26 6 y
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
PosgreSQL partitioning Scripts for partitioning postgresql ✔ DB Patch github.com/cavaliercoder/zabbix-pgsql-partitioning share.zabbix.com/postgresql-partitioning-scripts	share.zabbix.com GitHub 26		PLpgSQL	2017-06-17 2 y
PostreSQL monitoring Supports PostgreSQL 9.6+ ✔ Low Level Discovery (LLD) ✔ Zabbix Agent Active www.linuxelite.com.br/monitoramento/monitorando-postgresql-9-6-no-zabbix/ share.zabbix.com/postgresql-9-9	share.zabbix.com 4230	2.4	Template, Userparameter	2017-09-01 3 y	Popular
Monitoring agent for PostgreSQL Tool for monitoring PostgreSQL with wide range of features ✔ Zabbix Agent Active github.com/postgrespro/mamonsu share.zabbix.com/mamonsu-monitoring-agent-for-postgresql	share.zabbix.com GitHub 151		Template, Module python Python	2017-07-30 2021 y
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 68		Module, Template Perl	2015-06-29 25 d	Popular
Zabbix PostgreSQL scripts Scripts for partitioning the Zabbix database on PostgreSQL. github.com/cavaliercoder/zabbix-pgsql-partitioning	GitHub 26		PLpgSQL	2016-09-29 5 m	Popular
PostgreSQL partitioning Scripts to install, manage, and remove PostgreSQL partitioning for Zabbix DB github.com/robbrucks/zabbix-postgresql-auto-partitioning	GitHub 10		Shell	2017-05-15 1 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 4 y
Zabbix PostgreSQL template Agenless remote postgresql monitoring using zabbix github.com/datorama/zabbix_postgresql_template	GitHub 4		Template Perl	2015-01-29 1 m
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 3 y
Backup script for Zabbix on postgreSQL github.com/YxRyaB/zabbix-postgres-backup	GitHub 1		Shell	2015-07-22 2 y
Monitoring PostgreSQL in Zabbix Monitoring will be based on SQL queries to the statistics tables. github.com/CBEPX/PostgreSQL_Zabbix	GitHub 1		Template, Userparameter	2017-07-06 2 y

+ Propose new solution

Zabbix + Postgresql

Postgresql

Available solutions

Template DB PostgreSQL

Overview

Setup

Zabbix configuration

Macros used

Template links

Discovery rules

Items collected

Triggers

Feedback

References

Template DB PostgreSQL Agent 2

Overview

Setup

Zabbix configuration

Macros used

Template links

Discovery rules

Items collected

Triggers

Feedback

Articles and documentation