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
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+
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.
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+
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
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
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.