src/go/plugin/go.d/modules/postgres/metadata.yaml
plugin_name: go.d.plugin
modules:
- meta:
id: collector-go.d.plugin-postgres
plugin_name: go.d.plugin
module_name: postgres
monitored_instance:
name: PostgreSQL
link: https://www.postgresql.org/
categories:
- data-collection.database-servers
icon_filename: postgres.svg
related_resources:
integrations:
list:
- plugin_name: apps.plugin
module_name: apps
- plugin_name: cgroups.plugin
module_name: cgroups
alternative_monitored_instances: []
info_provided_to_referring_integrations:
description: ""
keywords:
- db
- database
- postgres
- postgresql
- sql
most_popular: true
overview:
multi_instance: true
data_collection:
metrics_description: |
This collector monitors the activity and performance of Postgres servers, collects replication statistics, metrics for each database, table and index, and more.
method_description: |
It establishes a connection to the Postgres instance via a TCP or UNIX socket.
To collect metrics for database tables and indexes, it establishes an additional connection for each discovered database.
default_behavior:
auto_detection:
description: |
By default, it detects instances running on localhost by trying to connect as root and netdata using known PostgreSQL TCP and UNIX sockets:
- 127.0.0.1:5432
- /var/run/postgresql/
limits:
description: |
Table and index metrics are not collected for databases with more than 50 tables or 250 indexes.
These limits can be changed in the configuration file.
performance_impact:
description: ""
additional_permissions:
description: ""
supported_platforms:
include: []
exclude: []
setup:
prerequisites:
list:
- title: Create netdata user
description: |
Create a user with granted `pg_monitor`
or `pg_read_all_stat` [built-in role](https://www.postgresql.org/docs/current/predefined-roles.html).
To create the `netdata` user with these permissions, execute the following in the psql session, as a user with CREATEROLE privileges:
```postgresql
CREATE USER netdata;
GRANT pg_monitor TO netdata;
```
After creating the new user, restart the Netdata agent with `sudo systemctl restart netdata`, or
the [appropriate method](/docs/netdata-agent/start-stop-restart.md) for your
system.
configuration:
file:
name: go.d/postgres.conf
options:
description: |
The following options can be defined globally: update_every, autodetection_retry.
folding:
title: Config options
enabled: true
list:
- name: update_every
description: Data collection frequency.
default_value: 5
required: false
- name: autodetection_retry
description: Recheck interval in seconds. Zero means no recheck will be scheduled.
default_value: 0
required: false
- name: dsn
description: Postgres server DSN (Data Source Name). See [DSN syntax](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING).
default_value: postgres://postgres:postgres@127.0.0.1:5432/postgres
required: true
- name: timeout
description: Query timeout in seconds.
default_value: 2
required: false
- name: collect_databases_matching
description: Databases selector. Determines which database metrics will be collected. Syntax is [simple patterns](https://github.com/netdata/netdata/tree/master/src/go/plugin/go.d/pkg/matcher#simple-patterns-matcher).
default_value: ""
required: false
- name: max_db_tables
description: Maximum number of tables in the database. Table metrics will not be collected for databases that have more tables than max_db_tables. 0 means no limit.
default_value: 50
required: false
- name: max_db_indexes
description: Maximum number of indexes in the database. Index metrics will not be collected for databases that have more indexes than max_db_indexes. 0 means no limit.
default_value: 250
required: false
examples:
folding:
title: Config
enabled: true
list:
- name: TCP socket
description: An example configuration.
folding:
enabled: false
config: |
jobs:
- name: local
dsn: 'postgresql://netdata@127.0.0.1:5432/postgres'
- name: Unix socket
description: An example configuration.
config: |
jobs:
- name: local
dsn: 'host=/var/run/postgresql dbname=postgres user=netdata'
- name: Multi-instance
description: |
> **Note**: When you define multiple jobs, their names must be unique.
Local and remote instances.
config: |
jobs:
- name: local
dsn: 'postgresql://netdata@127.0.0.1:5432/postgres'
- name: remote
dsn: 'postgresql://netdata@203.0.113.0:5432/postgres'
troubleshooting:
problems:
list: []
alerts:
- name: postgres_total_connection_utilization
metric: postgres.connections_utilization
info: average total connection utilization over the last minute
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_acquired_locks_utilization
metric: postgres.locks_utilization
info: average acquired locks utilization over the last minute
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_txid_exhaustion_perc
metric: postgres.txid_exhaustion_perc
info: percent towards TXID wraparound
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_db_cache_io_ratio
metric: postgres.db_cache_io_ratio
info: average cache hit ratio in db ${label:database} over the last minute
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_db_transactions_rollback_ratio
metric: postgres.db_cache_io_ratio
info: average aborted transactions percentage in db ${label:database} over the last five minutes
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_db_deadlocks_rate
metric: postgres.db_deadlocks_rate
info: number of deadlocks detected in db ${label:database} in the last minute
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_table_cache_io_ratio
metric: postgres.table_cache_io_ratio
info: average cache hit ratio in db ${label:database} table ${label:table} over the last minute
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_table_index_cache_io_ratio
metric: postgres.table_index_cache_io_ratio
info: average index cache hit ratio in db ${label:database} table ${label:table} over the last minute
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_table_toast_cache_io_ratio
metric: postgres.table_toast_cache_io_ratio
info: average TOAST hit ratio in db ${label:database} table ${label:table} over the last minute
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_table_toast_index_cache_io_ratio
metric: postgres.table_toast_index_cache_io_ratio
info: average index TOAST hit ratio in db ${label:database} table ${label:table} over the last minute
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_table_bloat_size_perc
metric: postgres.table_bloat_size_perc
info: bloat size percentage in db ${label:database} table ${label:table}
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_table_last_autovacuum_time
metric: postgres.table_autovacuum_since_time
info: time elapsed since db ${label:database} table ${label:table} was vacuumed by the autovacuum daemon
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_table_last_autoanalyze_time
metric: postgres.table_autoanalyze_since_time
info: time elapsed since db ${label:database} table ${label:table} was analyzed by the autovacuum daemon
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
- name: postgres_index_bloat_size_perc
metric: postgres.index_bloat_size_perc
info: bloat size percentage in db ${label:database} table ${label:table} index ${label:index}
link: https://github.com/netdata/netdata/blob/master/src/health/health.d/postgres.conf
metrics:
folding:
title: Metrics
enabled: false
description: ""
availability: []
scopes:
- name: global
description: These metrics refer to the entire monitored application.
labels: []
metrics:
- name: postgres.connections_utilization
description: Connections utilization
unit: percentage
chart_type: line
dimensions:
- name: used
- name: postgres.connections_usage
description: Connections usage
unit: connections
chart_type: stacked
dimensions:
- name: available
- name: used
- name: postgres.connections_state_count
description: Connections in each state
unit: connections
chart_type: stacked
dimensions:
- name: active
- name: idle
- name: idle_in_transaction
- name: idle_in_transaction_aborted
- name: disabled
- name: postgres.transactions_duration
description: Observed transactions time
unit: transactions/s
chart_type: stacked
dimensions:
- name: a dimension per bucket
- name: postgres.queries_duration
description: Observed active queries time
unit: queries/s
chart_type: stacked
dimensions:
- name: a dimension per bucket
- name: postgres.locks_utilization
description: Acquired locks utilization
unit: percentage
chart_type: line
dimensions:
- name: used
- name: postgres.checkpoints_rate
description: Checkpoints
unit: checkpoints/s
chart_type: stacked
dimensions:
- name: scheduled
- name: requested
- name: postgres.checkpoints_time
description: Checkpoint time
unit: milliseconds
chart_type: stacked
dimensions:
- name: write
- name: sync
- name: postgres.bgwriter_halts_rate
description: Background writer scan halts
unit: events/s
chart_type: line
dimensions:
- name: maxwritten
- name: postgres.buffers_io_rate
description: Buffers written rate
unit: B/s
chart_type: area
dimensions:
- name: checkpoint
- name: backend
- name: bgwriter
- name: postgres.buffers_backend_fsync_rate
description: Backend fsync calls
unit: calls/s
chart_type: line
dimensions:
- name: fsync
- name: postgres.buffers_allocated_rate
description: Buffers allocated
unit: B/s
chart_type: line
dimensions:
- name: allocated
- name: postgres.wal_io_rate
description: Write-Ahead Log writes
unit: B/s
chart_type: line
dimensions:
- name: write
- name: postgres.wal_files_count
description: Write-Ahead Log files
unit: files
chart_type: stacked
dimensions:
- name: written
- name: recycled
- name: postgres.wal_archiving_files_count
description: Write-Ahead Log archived files
unit: files/s
chart_type: stacked
dimensions:
- name: ready
- name: done
- name: postgres.autovacuum_workers_count
description: Autovacuum workers
unit: workers
chart_type: line
dimensions:
- name: analyze
- name: vacuum_analyze
- name: vacuum
- name: vacuum_freeze
- name: brin_summarize
- name: postgres.txid_exhaustion_towards_autovacuum_perc
description: Percent towards emergency autovacuum
unit: percentage
chart_type: line
dimensions:
- name: emergency_autovacuum
- name: postgres.txid_exhaustion_perc
description: Percent towards transaction ID wraparound
unit: percentage
chart_type: line
dimensions:
- name: txid_exhaustion
- name: postgres.txid_exhaustion_oldest_txid_num
description: Oldest transaction XID
chart_type: line
unit: xid
dimensions:
- name: xid
- name: postgres.catalog_relations_count
description: Relation count
unit: relations
chart_type: stacked
dimensions:
- name: ordinary_table
- name: index
- name: sequence
- name: toast_table
- name: view
- name: materialized_view
- name: composite_type
- name: foreign_table
- name: partitioned_table
- name: partitioned_index
- name: postgres.catalog_relations_size
description: Relation size
unit: B
chart_type: stacked
dimensions:
- name: ordinary_table
- name: index
- name: sequence
- name: toast_table
- name: view
- name: materialized_view
- name: composite_type
- name: foreign_table
- name: partitioned_table
- name: partitioned_index
- name: postgres.uptime
description: Uptime
unit: seconds
chart_type: line
dimensions:
- name: uptime
- name: postgres.databases_count
description: Number of databases
unit: databases
chart_type: line
dimensions:
- name: databases
- name: repl application
description: These metrics refer to the replication application.
labels:
- name: application
description: application name
metrics:
- name: postgres.replication_app_wal_lag_size
description: Standby application WAL lag size
unit: B
chart_type: line
dimensions:
- name: sent_lag
- name: write_lag
- name: flush_lag
- name: replay_lag
- name: postgres.replication_app_wal_lag_time
description: Standby application WAL lag time
unit: seconds
chart_type: line
dimensions:
- name: write_lag
- name: flush_lag
- name: replay_lag
- name: repl slot
description: These metrics refer to the replication slot.
labels:
- name: slot
description: replication slot name
metrics:
- name: postgres.replication_slot_files_count
description: Replication slot files
unit: files
chart_type: line
dimensions:
- name: wal_keep
- name: pg_replslot_files
- name: database
description: These metrics refer to the database.
labels:
- name: database
description: database name
metrics:
- name: postgres.db_transactions_ratio
description: Database transactions ratio
unit: percentage
chart_type: line
dimensions:
- name: committed
- name: rollback
- name: postgres.db_transactions_rate
description: Database transactions
unit: transactions/s
chart_type: line
dimensions:
- name: committed
- name: rollback
- name: postgres.db_connections_utilization
description: Database connections utilization
unit: percentage
chart_type: line
dimensions:
- name: used
- name: postgres.db_connections_count
description: Database connections
unit: connections
chart_type: line
dimensions:
- name: connections
- name: postgres.db_cache_io_ratio
description: Database buffer cache miss ratio
unit: percentage
chart_type: line
dimensions:
- name: miss
- name: postgres.db_io_rate
description: Database reads
unit: B/s
chart_type: line
dimensions:
- name: memory
- name: disk
- name: postgres.db_ops_fetched_rows_ratio
description: Database rows fetched ratio
unit: percentage
chart_type: line
dimensions:
- name: fetched
- name: postgres.db_ops_read_rows_rate
description: Database rows read
unit: rows/s
chart_type: line
dimensions:
- name: returned
- name: fetched
- name: postgres.db_ops_write_rows_rate
description: Database rows written
unit: rows/s
chart_type: line
dimensions:
- name: inserted
- name: deleted
- name: updated
- name: postgres.db_conflicts_rate
description: Database canceled queries
unit: queries/s
chart_type: line
dimensions:
- name: conflicts
- name: postgres.db_conflicts_reason_rate
description: Database canceled queries by reason
unit: queries/s
chart_type: line
dimensions:
- name: tablespace
- name: lock
- name: snapshot
- name: bufferpin
- name: deadlock
- name: postgres.db_deadlocks_rate
description: Database deadlocks
unit: deadlocks/s
chart_type: line
dimensions:
- name: deadlocks
- name: postgres.db_locks_held_count
description: Database locks held
unit: locks
chart_type: stacked
dimensions:
- name: access_share
- name: row_share
- name: row_exclusive
- name: share_update
- name: share
- name: share_row_exclusive
- name: exclusive
- name: access_exclusive
- name: postgres.db_locks_awaited_count
description: Database locks awaited
unit: locks
chart_type: stacked
dimensions:
- name: access_share
- name: row_share
- name: row_exclusive
- name: share_update
- name: share
- name: share_row_exclusive
- name: exclusive
- name: access_exclusive
- name: postgres.db_temp_files_created_rate
description: Database created temporary files
unit: files/s
chart_type: line
dimensions:
- name: created
- name: postgres.db_temp_files_io_rate
description: Database temporary files data written to disk
unit: B/s
chart_type: line
dimensions:
- name: written
- name: postgres.db_size
description: Database size
unit: B
chart_type: line
dimensions:
- name: size
- name: table
description: These metrics refer to the database table.
labels:
- name: database
description: database name
- name: schema
description: schema name
- name: table
description: table name
- name: parent_table
description: parent table name
metrics:
- name: postgres.table_rows_dead_ratio
description: Table dead rows
unit: percentage
chart_type: line
dimensions:
- name: dead
- name: postgres.table_rows_count
description: Table total rows
unit: rows
chart_type: line
dimensions:
- name: live
- name: dead
- name: postgres.table_ops_rows_rate
description: Table throughput
unit: rows/s
chart_type: line
dimensions:
- name: inserted
- name: deleted
- name: updated
- name: postgres.table_ops_rows_hot_ratio
description: Table HOT updates ratio
unit: percentage
chart_type: line
dimensions:
- name: hot
- name: postgres.table_ops_rows_hot_rate
description: Table HOT updates
unit: rows/s
chart_type: line
dimensions:
- name: hot
- name: postgres.table_cache_io_ratio
description: Table I/O cache miss ratio
unit: percentage
chart_type: line
dimensions:
- name: miss
- name: postgres.table_io_rate
description: Table I/O
unit: B/s
chart_type: line
dimensions:
- name: memory
- name: disk
- name: postgres.table_index_cache_io_ratio
description: Table index I/O cache miss ratio
unit: percentage
chart_type: line
dimensions:
- name: miss
- name: postgres.table_index_io_rate
description: Table index I/O
unit: B/s
chart_type: line
dimensions:
- name: memory
- name: disk
- name: postgres.table_toast_cache_io_ratio
description: Table TOAST I/O cache miss ratio
unit: percentage
chart_type: line
dimensions:
- name: miss
- name: postgres.table_toast_io_rate
description: Table TOAST I/O
unit: B/s
chart_type: line
dimensions:
- name: memory
- name: disk
- name: postgres.table_toast_index_cache_io_ratio
description: Table TOAST index I/O cache miss ratio
unit: percentage
chart_type: line
dimensions:
- name: miss
- name: postgres.table_toast_index_io_rate
description: Table TOAST index I/O
unit: B/s
chart_type: line
dimensions:
- name: memory
- name: disk
- name: postgres.table_scans_rate
description: Table scans
unit: scans/s
chart_type: line
dimensions:
- name: index
- name: sequential
- name: postgres.table_scans_rows_rate
description: Table live rows fetched by scans
unit: rows/s
chart_type: line
dimensions:
- name: index
- name: sequential
- name: postgres.table_autovacuum_since_time
description: Table time since last auto VACUUM
unit: seconds
chart_type: line
dimensions:
- name: time
- name: postgres.table_vacuum_since_time
description: Table time since last manual VACUUM
unit: seconds
chart_type: line
dimensions:
- name: time
- name: postgres.table_autoanalyze_since_time
description: Table time since last auto ANALYZE
unit: seconds
chart_type: line
dimensions:
- name: time
- name: postgres.table_analyze_since_time
description: Table time since last manual ANALYZE
unit: seconds
chart_type: line
dimensions:
- name: time
- name: postgres.table_null_columns
description: Table null columns
unit: columns
chart_type: line
dimensions:
- name: "null"
- name: postgres.table_size
description: Table total size
unit: B
chart_type: line
dimensions:
- name: size
- name: postgres.table_bloat_size_perc
description: Table bloat size percentage
unit: percentage
chart_type: line
dimensions:
- name: bloat
- name: postgres.table_bloat_size
description: Table bloat size
unit: B
chart_type: line
dimensions:
- name: bloat
- name: index
description: These metrics refer to the table index.
labels:
- name: database
description: database name
- name: schema
description: schema name
- name: table
description: table name
- name: parent_table
description: parent table name
- name: index
description: index name
metrics:
- name: postgres.index_size
description: Index size
unit: B
chart_type: line
dimensions:
- name: size
- name: postgres.index_bloat_size_perc
description: Index bloat size percentage
unit: percentage
chart_type: line
dimensions:
- name: bloat
- name: postgres.index_bloat_size
description: Index bloat size
unit: B
chart_type: line
dimensions:
- name: bloat
- name: postgres.index_usage_status
description: Index usage status
unit: status
chart_type: line
dimensions:
- name: used
- name: unused