To query these metrics, you must be logged into PostgreSQL as a user that has read access to pg_stat_database
- Check version of PostgreSQL:
SELECT version(); - Show location of postgresql.conf file (requires superuser privileges):
SHOW config_file; - Show location of data directory (requires superuser privileges):
SHOW data_directory; - Show location of log directory (may be a path relative to data directory, requires superuser privileges):
SHOW log_directory; - List all databases:
\l - List all tables + table size in bytes in the current database:
\dt+ - List all roles and privileges in the current database:
\du - Check if this server is a standby (in recovery mode):
SELECT pg_is_in_recovery(); - View connection info:
\conninfo - Show all available psql commands:
\?
- Replication delay in bytes (version < 10.x):
SELECT abs(pg_xlog_location_diff(pg_last_xlog_receive_location(), pg_last_xlog_replay_location())) AS replication_delay_bytes; - Replication delay in bytes (version > 10.x):
SELECT abs(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS replication_delay_bytes; - Replication delay in seconds (version < 10.x):
SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE GREATEST (0, EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())) END; - Replication delay in seconds (version > 10.x):
SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE GREATEST (0, EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())) END; - Number of checkpoints requested & scheduled:
SELECT checkpoints_req, checkpoints_timed FROM pg_stat_bgwriter;
- Number of index scans (per index and per table):
SELECT indexrelname, relname, idx_scan FROM pg_stat_user_indexes; - Number of sequential scans (per table):
SELECT relname, seq_scan FROM pg_stat_user_tables; - Rows fetched by queries (per database):
SELECT datname, tup_fetched FROM pg_stat_database; - Rows returned by queries (per database):
SELECT datname, tup_returned FROM pg_stat_database; - Bytes written temporarily to disk to execute queries (per database) (version > 9.2):
SELECT datname, temp_bytes FROM pg_stat_database;
- Rows inserted, updated, deleted by queries (per database):
SELECT datname, tup_inserted, tup_updated, tup_deleted FROM pg_stat_database - Rows inserted, updated, deleted by queries (per table):
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables; - Heap-only tuple (HOT) updates (per table):
SELECT relname, n_tup_hot_upd FROM pg_stat_user_tables; - Total commits and rollbacks across all databases:
SELECT SUM(xact_commit) AS total_commits, SUM(xact_rollback)AS total_rollbacks FROM pg_stat_database;
- Locks (by table and lock mode):
SELECT mode, pg_class.relname, count(*) FROM pg_locks JOIN pg_class ON (pg_locks.relation = pg_class.oid) WHERE pg_locks.mode IS NOT NULL AND pg_class.relname NOT LIKE 'pg_%%' GROUP BY pg_class.relname, mode; - Deadlocks (per database) (version > 9.2):
SELECT datname, deadlocks FROM pg_stat_database; - Dead rows (per table):
SELECT relname, n_dead_tup FROM pg_stat_user_tables;
- Number of active connections:
SELECT COUNT(*) FROM pg_stat_activity WHERE state='active'; - Percentage of max connections in use:
SELECT (SELECT SUM(numbackends) FROM pg_stat_database) / (SELECT setting::float FROM pg_settings WHERE name = 'max_connections');
- Disk space used in bytes, excluding indexes (per table):
SELECT relname AS "table_name", pg_size_pretty(pg_table_size(C.oid)) AS "table_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r') ORDER BY pg_table_size(C.oid) DESC; - Number of index scans (per index and per table):
SELECT indexrelname, relname, idx_scan FROM pg_stat_user_indexes;