Skip to main content

PostgreSQL monitoring with Netdata

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.

This module monitors one or more Postgres servers, depending on your configuration.

To find out more about these metrics and why they are important to monitor, read our blog post on PostgreSQL monitoring with Netdata

Requirements

  • PostgreSQL v9.4+
  • User with granted pg_monitor or pg_read_all_stat built-in role.

Without additional configuration Netdata will attempt to use the default postgres user - but a separate netdata user can be created for this purpose. If you have PostgreSQL 10+ and want to use Netdata to monitor statistics normally reserved for superusers grant the netdata user pg_monitor permissions. Some of the advanced metrics also require additional permissions as mentioned in metrics.

To create the netdata user with these permissions, execute the following in the psql session, as a user with CREATEROLE priviliges:

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 for your system

Metrics

  • all metrics have "postgres." prefix.
  • db_size need CONNECT privilege to the database.
  • table* and index* metrics need additional configuration.
  • table_bloat and index_bloat metrics need read (SELECT) permission to the table.
  • wal_files_count, wal_archiving_files_count and replication_slot_files_count need superuser status.

Labels per scope:

  • global: no labels.
  • repl application: application.
  • repl slot: slot.
  • database: database.
  • table: database, schema, table.
  • index: database, schema, table, index.
MetricScopeDimensionsUnits
connections_utilizationglobalusedpercentage
connections_usageglobalavailable, usedconnections
connections_state_countglobalactive, idle, idle_in_transaction, idle_in_transaction_aborted, disabledconnections
transactions_durationglobala dimension per buckettransactions/s
queries_durationglobala dimension per bucketqueries/s
locks_utilizationglobalusedpercentage
checkpoints_rateglobalscheduled, requestedcheckpoints/s
checkpoints_timeglobalwrite, syncmilliseconds
bgwriter_halts_rateglobalmaxwrittenevents/s
buffers_io_rateglobalcheckpoint, backend, bgwriterB/s
buffers_backend_fsync_rateglobalfsynccalls/s
buffers_allocated_rateglobalallocatedB/s
wal_io_rateglobalwriteB/s
wal_files_countglobalwritten, recycledfiles
wal_archiving_files_countglobalready, donefiles/s
autovacuum_workers_countglobalanalyze, vacuum_analyze, vacuum, vacuum_freeze, brin_summarizeworkers
txid_exhaustion_towards_autovacuum_percglobalemergency_autovacuumpercentage
txid_exhaustion_percglobaltxid_exhaustionpercentage
txid_exhaustion_oldest_txid_numglobalxidxid
catalog_relations_countglobalordinary_table, index, sequence, toast_table, view, materialized_view, composite_type, foreign_table, partitioned_table, partitioned_indexrelations
catalog_relations_sizeglobalordinary_table, index, sequence, toast_table, view, materialized_view, composite_type, foreign_table, partitioned_table, partitioned_indexB
uptimeglobaluptimeseconds
databases_countglobaldatabasesdatabases
replication_app_wal_lag_sizerepl applicationsent_lag, write_lag, flush_lag, replay_lagB
replication_app_wal_lag_timerepl applicationwrite_lag, flush_lag, replay_lagseconds
replication_slot_files_countrepl slotwal_keep, pg_replslot_filesfiles
db_transactions_ratiodatabasecommitted, rollbackpercentage
db_transactions_ratedatabasecommitted, rollbacktransactions/s
db_connections_utilizationdatabaseusedpercentage
db_connections_countdatabaseconnectionsconnections
db_cache_io_ratiodatabasemisspercentage
db_io_ratedatabasememory, diskB/s
db_ops_fetched_rows_ratiodatabasefetchedpercentage
db_ops_read_rows_ratedatabasereturned, fetchedrows/s
db_ops_write_rows_ratedatabaseinserted, deleted, updatedrows/s
db_conflicts_ratedatabaseconflictsqueries/s
db_conflicts_reason_ratedatabasetablespace, lock, snapshot, bufferpin, deadlockqueries/s
db_deadlocks_ratedatabasedeadlocksdeadlocks/s
db_locks_held_countdatabaseaccess_share, row_share, row_exclusive, share_update, share, share_row_exclusive, exclusive, access_exclusivelocks
db_locks_awaited_countdatabaseaccess_share, row_share, row_exclusive, share_update, share, share_row_exclusive, exclusive, access_exclusivelocks
db_temp_files_created_ratedatabasecreatedfiles/s
db_temp_files_io_ratedatabasewrittenB/s
db_sizedatabasesizeB
table_rows_dead_ratiotabledeadpercentage
table_rows_counttablelive, deadrows
table_ops_rows_ratetableinserted, deleted, updatedrows/s
table_ops_rows_hot_ratiotablehotpercentage
table_ops_rows_hot_ratetablehotrows/s
table_cache_io_ratiotablemisspercentage
table_io_ratetablememory, diskB/s
table_index_cache_io_ratiotablemisspercentage
table_index_io_ratetablememory, diskB/s
table_toast_cache_io_ratiotablemisspercentage
table_toast_io_ratetablememory, diskB/s
table_toast_index_cache_io_ratiotablemisspercentage
table_toast_index_io_ratetablememory, diskB/s
table_scans_ratetableindex, sequentialscans/s
table_scans_rows_ratetableindex, sequentialrows/s
table_autovacuum_since_timetabletimeseconds
table_vacuum_since_timetabletimeseconds
table_autoanalyze_since_timetabletimeseconds
table_analyze_since_timetabletimeseconds
table_null_columnstablenullcolumns
table_sizetablesizeB
table_bloat_size_perctablebloatpercentage
table_bloat_sizetablebloatB
index_sizeindexsizeB
index_bloat_size_percindexbloatpercentage
index_bloat_sizeindexbloatB
index_usage_statusindexused, unusedstatus

Configuration

Edit the go.d/postgres.conf configuration file using edit-config from the Netdata config directory, which is typically at /etc/netdata.

cd /etc/netdata # Replace this path with your Netdata config directory
sudo ./edit-config go.d/postgres.conf

DSN (Data Source Name) may either be in URL format or key=word format. See Connection Strings for details.

jobs:
- name: local
dsn: 'postgres://postgres:[email protected]:5432/postgres'

- name: local
dsn: 'host=/var/run/postgresql dbname=postgres user=postgres'

- name: remote
dsn: 'postgres://postgres:[email protected]:5432/postgres'

Database detailed metrics

Detailed metrics include table* and index*.

By default, this module only collects detailed metrics for the database it is connected to. Collection from all databases on a database server is disabled because each database requires an additional connection.

Use the collect_databases_matching configuration option to select the databases from which you want to collect detailed metrics. The value supports Netdata simple patterns.

jobs:
- name: local
dsn: 'postgres://postgres:[email protected]:5432/postgres'
collect_databases_matching: 'mydb1 mydb2 !mydb3 mydb4'

For all available options see module configuration file.

Troubleshooting

To troubleshoot issues with the postgres collector, run the go.d.plugin with the debug option enabled. The output should give you clues as to why the collector isn't working.

  • Navigate to the plugins.d directory, usually at /usr/libexec/netdata/plugins.d/. If that's not the case on your system, open netdata.conf and look for the plugins setting under [directories].

    cd /usr/libexec/netdata/plugins.d/
  • Switch to the netdata user.

    sudo -u netdata -s
  • Run the go.d.plugin to debug the collector:

    ./go.d.plugin -d -m postgres

Was this page helpful?

Contribute