PostgreSQL DBA cheat sheet: psql, sessions, locks, vacuum, replication and backups

#psql essentials

#Server and databases

SELECT version();
SELECT pg_postmaster_start_time(), now() - pg_postmaster_start_time() AS uptime;
SHOW config_file; SHOW data_directory;
SELECT name, setting, unit, source FROM pg_settings WHERE name IN
  ('shared_buffers','work_mem','maintenance_work_mem','max_connections','effective_cache_size','max_wal_size');
SELECT pg_reload_conf();                                -- after editing postgresql.conf (non-restart settings)
SELECT name, pending_restart FROM pg_settings WHERE pending_restart;

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;

#Sessions

SELECT pid, usename, datname, client_addr, application_name, state,
       now() - xact_start AS xact_age, now() - query_start AS query_age, wait_event_type, wait_event,
       left(query, 100) AS query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_age DESC NULLS LAST;

-- idle in transaction (holds locks and blocks vacuum)
SELECT pid, usename, now() - state_change AS idle_for, left(query, 80)
FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY idle_for DESC;

-- connections per state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

SELECT pg_cancel_backend(12345);      -- cancel the query
SELECT pg_terminate_backend(12345);   -- kill the session

#Locks and blocking

SELECT blocked.pid AS blocked_pid, blocked.usename, left(blocked.query, 60) AS blocked_query,
       blocking.pid AS blocking_pid, left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY (pg_blocking_pids(blocked.pid));

Set guards: SET lock_timeout = '5s'; SET statement_timeout = '5min'; for maintenance sessions, and idle_in_transaction_session_timeout server-wide.

#Slow queries (pg_stat_statements)

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;      -- needs shared_preload_libraries
SELECT round(total_exec_time) AS total_ms, calls, round(mean_exec_time, 1) AS mean_ms,
       rows, left(query, 100) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 15;
SELECT pg_stat_statements_reset();

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

#Sizes

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS heap,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

#Vacuum, dead tuples and wraparound

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

-- transaction ID age (alert above ~1 billion; hard limit ~2.1 billion)
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;
SELECT relname, age(relfrozenxid) AS xid_age FROM pg_class WHERE relkind = 'r' ORDER BY xid_age DESC LIMIT 10;

-- running vacuums
SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total FROM pg_stat_progress_vacuum;

VACUUM (VERBOSE, ANALYZE) sales.orders;
VACUUM (FREEZE, VERBOSE) sales.orders;            -- for wraparound pressure
ALTER TABLE sales.orders SET (autovacuum_vacuum_scale_factor = 0.02);

Things that stop vacuum cleaning: long-running transactions, idle-in-transaction sessions, old replication slots, and prepared transactions (SELECT * FROM pg_prepared_xacts).

#Indexes

-- unused indexes
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

CREATE INDEX CONCURRENTLY idx_orders_customer ON sales.orders (customer_id);
REINDEX INDEX CONCURRENTLY idx_orders_customer;       -- PG 12+
DROP INDEX CONCURRENTLY idx_old;

#Replication

-- on the primary
SELECT client_addr, application_name, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes, replay_lag
FROM pg_stat_replication;

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;                          -- inactive slots fill the disk with WAL
SELECT pg_drop_replication_slot('old_slot');

-- on the standby
SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() AS replay_delay;
SELECT pg_promote();                                -- promote a standby (PG 12+)

#Backup and restore

pg_dump -h db01 -U postgres -Fc -d sales -f sales.dump            # one database, custom format
pg_dump -h db01 -U postgres -Fd -j 8 -d sales -f sales_dir         # parallel directory format
pg_dumpall -h db01 -U postgres --globals-only > globals.sql         # roles and tablespaces
pg_restore -h db02 -U postgres -d sales -j 8 --no-owner sales.dump
pg_restore -l sales.dump | head                                    # list contents

pg_basebackup -h db01 -U replicator -D /var/lib/pgsql/17/data -X stream -P -R   # new standby

pgbackrest --stanza=sales info
pgbackrest --stanza=sales --type=diff backup
pgbackrest --stanza=sales --type=time --target="2026-10-23 09:15:00+05:30" --target-action=promote restore

#Roles and privileges

CREATE ROLE app_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE sales TO app_ro;
GRANT USAGE ON SCHEMA sales TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO app_ro;
GRANT pg_read_all_stats TO monitoring;                   -- monitoring without superuser
\du+                                                      -- in psql: roles and memberships
SELECT rolname, rolsuper, rolcanlogin, rolvaliduntil FROM pg_roles;

#Logs

SELECT pg_current_logfile();

Useful settings: log_min_duration_statement = '500ms', log_lock_waits = on, log_autovacuum_min_duration = '1s', log_checkpoints = on.

Tip

The three queries that solve most PostgreSQL incidents: pg_stat_activity (what's running and waiting), pg_blocking_pids() (who blocks whom) and pg_replication_slots (why the disk is filling with WAL).