PostgreSQL DBA Checklist

psql, sessions, locks, size, vacuum, backups and incident evidence. Last updated August 31, 2026.

PostgreSQL troubleshooting should start with identity and read-only evidence: which server, which database, which user, which sessions, which locks and which storage pressure. The PostgreSQL documentation describes psql as the interactive terminal for PostgreSQL. This page turns that into a practical DBA checklist for developers and administrators.

Connection identity

Save this block before changing config or tuning SQL. It prevents the classic mistake of debugging the wrong database.

psql -c "select version();"
psql -c "select current_database(), current_user, inet_server_addr(), inet_server_port();"
psql -c "show data_directory;"
psql -c "show config_file;"
psql -c "show hba_file;"

Database size and table signals

psql -c "select pg_size_pretty(pg_database_size(current_database())) as database_size;"
psql -c "select schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_user_tables order by n_dead_tup desc limit 20;"
psql -c "select relname, pg_size_pretty(pg_total_relation_size(relid)) as total_size from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc limit 20;"

Sessions and slow activity

psql -c "select pid, usename, application_name, client_addr, state, wait_event_type, wait_event, now() - query_start as age, left(query, 120) as query from pg_stat_activity where state <> 'idle' order by query_start;"
psql -c "select state, count(*) from pg_stat_activity group by state order by count(*) desc;"
psql -c "select application_name, client_addr, count(*) from pg_stat_activity group by application_name, client_addr order by count(*) desc limit 20;"

Locks and blocking

Use lock evidence carefully. Killing sessions can cause application errors, so collect the blocking chain first.

psql -c "select blocked.pid as blocked_pid, blocked.query as blocked_query, blocking.pid as blocking_pid, blocking.query as blocking_query from pg_stat_activity blocked join pg_locks blocked_locks on blocked_locks.pid = blocked.pid join pg_locks blocking_locks on blocking_locks.locktype = blocked_locks.locktype and blocking_locks.database is not distinct from blocked_locks.database and blocking_locks.relation is not distinct from blocked_locks.relation and blocking_locks.page is not distinct from blocked_locks.page and blocking_locks.tuple is not distinct from blocked_locks.tuple and blocking_locks.virtualxid is not distinct from blocked_locks.virtualxid and blocking_locks.transactionid is not distinct from blocked_locks.transactionid and blocking_locks.classid is not distinct from blocked_locks.classid and blocking_locks.objid is not distinct from blocked_locks.objid and blocking_locks.objsubid is not distinct from blocked_locks.objsubid and blocking_locks.pid <> blocked_locks.pid join pg_stat_activity blocking on blocking.pid = blocking_locks.pid where not blocked_locks.granted and blocking_locks.granted;"

Backup and restore readiness

Evidence	Command	Why it matters
Logical backup exists	`pg_dump --format=custom`	Confirms you can export a restoreable database artifact.
Restore test works	`pg_restore --list backup.dump`	Confirms the artifact can be inspected before restore.
Disk has room	`df -h`	Backups and indexes fail when storage is tight.
WAL pressure is known	`select * from pg_stat_archiver;`	Archive or replication problems can fill disks.
Ownership is known	`\du`	Restore and migration failures often come from role mismatch.

Safe backup commands

pg_dump --format=custom --file=backup.dump my_database
pg_restore --list backup.dump
createdb my_database_restore_test
pg_restore --dbname=my_database_restore_test --clean --if-exists backup.dump

Incident evidence bundle

date -Is
hostname
psql -c "select version();"
psql -c "select current_database(), current_user, inet_server_addr(), inet_server_port();"
psql -c "select state, count(*) from pg_stat_activity group by state order by count(*) desc;"
psql -c "select pg_size_pretty(pg_database_size(current_database())) as database_size;"

The best PostgreSQL DBA habit is boring and powerful: prove connection identity, collect read-only evidence, then decide whether the issue is SQL, locks, storage, config or application traffic.