Postgresql

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.

Available solutions




This template is for Zabbix version: 5.4
Also available for: 5.0

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

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

See Zabbix template operation for basic instructions.

  1. 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;
  2. Copy postgresql/ to Zabbix agent home directory /var/lib/zabbix/. The postgresql/ directory contains the files needed to obtain metrics from PostgreSQL.

  3. Copy template_db_postgresql.conf to Zabbix agent configuration directory /etc/zabbix/zabbix_agentd.d/ and restart Zabbix agent service.

  4. 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
  5. 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>
  6. Import template_db_postgresql.yaml 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

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

Template links

There are no template links in this template.

Discovery rules

Name Description Type Key and additional info
Database discovery

-

ZABBIX_PASSIVE pgsql.discovery.db["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$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.DB}"]
PostgreSQL Status: Config hash

PostgreSQL configuration hash

ZABBIX_PASSIVE pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$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.DB}"]

Preprocessing:

- REGEX: Time:\s+(\d+\.\d+)\s+ms \1

- MULTIPLIER: 0.001

PostgreSQL Status: Ping

-

ZABBIX_PASSIVE pgsql.ping["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.DB}"]

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.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.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.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.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.DB}"]
PostgreSQL Status: Version

PostgreSQL version

ZABBIX_PASSIVE pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$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.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.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.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.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.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.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.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.DB}"]
Zabbix_raw_items DB {#DBNAME}: Get frozen XID

-

ZABBIX_PASSIVE pgsql.frozenxid["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{#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}","{#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.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.DB}"],#1)<>last(/PostgreSQL by user parameters/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.DB}"],#2) and length(last(/PostgreSQL by user parameters/pgsql.config.hash["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$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.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}","{$PG.USER}","{$PG.DB}"]) = 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.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.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.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.DB}"],#1)<>last(/PostgreSQL by user parameters/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.DB}"],#2) and length(last(/PostgreSQL by user parameters/pgsql.version["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$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 then {$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.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.

Add your solution