List of Information and Metrics Collected

The information and metrics collected (and reported) by pgmetrics are listed below. Some values are only available in certain PostgreSQL versions and above. A notation like “v9.6+” indicates that the value is available only in PostgreSQL version 9.6 and above. Note that versions 9.2 and below are not supported by pgmetrics.

The collected information falls into three categories:

  • cluster-level
  • database-level
  • system-level

Cluster-level Information

This section lists the cluster-level information, which is always collected irrespective of the number of databases specified on the command line.

Note: Technically, a single running PostgreSQL server manages a “cluster” of databases. Not to be confused with the a set of co-operating servers.

Overall

  • system identifier - v9.6+
  • last commit - the last committed transaction ID and timestamp, if the “track_commit_timestamp” option is set to on. v9.5+
  • checkpoint/prior/redo locations - v9.6+
  • time of last checkpoint - v9.6+
  • timeline ID - v9.6+
  • oldest/next/oldest-active transaction IDs - v9.6+
  • settings - entries from pg_settings
Backends
  • pg_stat_activity - the entries from pg_stat_activity. Detailed waiting-for-lock information is available only in v9.6+. PostgreSQL-internal backend information present in v10+ is currently not collected.
  • pg_stat_archiver - the entries from pg_stat_archiver. v9.4+
  • pg_stat_wal - the entries from pg_stat_wal. v14+
BG Writer and Checkpointer
  • pg_stat_bgwriter - the entries from pg_stat_bgwriter.
  • pg_stat_checkpointer - the entries from pg_stat_checkpointer. v17+
Replication
  • pg_stat_replication - the entries from pg_stat_replication. This gives details of outgoing replication.
  • pg_stat_wal_receiver - the entries from pg_stat_wal_receiver. This gives details of incoming replication. v9.6+
  • pg_replication_slots - the entries from pg_replication_slots. This gives details of physical and logical replication slots. v9.4+
  • last received/replayed location - in recovery mode
  • last replayed timestamp - in recovery mode
  • is in recovery mode?
  • is WAL replay paused?
  • current wal/wal insert/wal flush locations - v9.6+
  • publications, subscriptions - v10+ (added in pgmetrics v1.4)
Job Progress
  • pg_stat_progress_analyze - the entries from pg_stat_progress_analyze. v13+
  • pg_stat_progress_basebackup - the entries from pg_stat_progress_basebackup. v13+
  • pg_stat_progress_cluster - the entries from pg_stat_progress_cluster. v12+
  • pg_stat_progress_copy - the entries from pg_stat_progress_copy. v14+
  • pg_stat_progress_create_index - the entries from pg_stat_progress_create_index. v12+
  • pg_stat_progress_vacuum - the entries from pg_stat_progress_vacuum. v9.6+
Database Information

The information about all databases, except template databases, are fetched from pg_stat_database. For each database, the size as reported by the pg_database_size() function is also collected.

Note that it is possible to skip the collection of the size using a command-line parameter.

Tablespace Information

The OID, name and location of each tablespace is collected from pg_tablespace. For the default tablespaces pg_global and pg_default, the value of the PostgreSQL database directory ($PGDATA) is used.

The size, as reported by pg_tablespace_size() is also collected. It is possible to skip this.

Roles

The list of roles and associated attributes are collected from pg_auth_members and pg_roles.

Locks and Blocked Queries

The list of granted and waiting locks from pg_locks. This is cross-referenced with the list of blocked backends and their queries to generate the list of SQL queries alongwith the query they are waiting for and the name of the table/index it is waiting on.

Other
  • number of WAL files in the pg_xlog (pg_wal in v10+) directory
  • number of .ready files in the pg_xlog/archive_status (pg_wal/archive_status in v10+) directory
  • notification queue usage - v9.6+

Database-level Information

This section lists the database-level information, which is collected for each database that is specified on the command line.

  • pg_stat_user_tables - entries from pg_stat_user_tables for all user tables in the database
  • pg_statio_user_tables - entries from pg_statio_user_tables for all user tables in the database
  • pg_table_size - size information for each table as reported by the pg_table_size() function, can be skipped
  • pg_stat_user_indexes - entries from pg_stat_user_indexes for all user indexes in the database
  • pg_total_relation_size - size information for each index as reported by the pg_total_relation_size() function, can be skipped
  • pg_statio_user_sequences - entries from pg_statio_user_sequences for all sequences in the database
  • pg_stat_user_functions - entries from pg_stat_user_functions for all tracked user functions. This will have information only if the “track_functions” option is enabled in PostgreSQL configuration.
  • pg_available_extensions - the name, installed/default versions and comment for each extension in the database, read from pg_available_extensions
  • disabled triggers - the trigger and procedure name for each disabled trigger in the database, read from pg_trigger and pg_proc
  • pg_stat_statements - the slowest 100 queries (this number can be set as a command-line parameter) as reported by pg_stat_statments
  • publications, subscriptions - the logical replication publications and subscriptions in this database, PostgreSQL v10+ (added in pgmetrics v1.4)
  • bloat - the estimated bloat for each table and index, using the same query from Greg Sabino Mullane’s check_postgres script

System-level Information

When pgmetrics is run directly on the system where the PostgreSQL server is running, it can collect additional system-level information from the OS. Currently, this is done only for Linux systems.

  • hostname - the hostname of the system
  • disk/inodes used/total - for each tablespace location, including the disk containing $PGDATA
  • cpu - the count and model of the CPU cores
  • load average - the 1-minute load average
  • memory free/buffers/cached/used - the RAM usage
  • swap used/free - the swap usage

AWS RDS & CloudWatch Information

See the AWS RDS page for more information.

Deadlocks, Query Plans and Autovacuum Runs

See the Log Files page for more information.

Citus, Azure Hyperscale

See the Citus page for more information.

PgBouncer

See the PgBouncer page for more information.

Pgpool

See the Pgpool page for more information.