Postgres cheatsheet

psql — connect and meta-commands

Command	Description
`psql -h localhost -U myuser -d mydb`	Connect to database
`psql -U postgres`	Connect as superuser (local)
`\l`	List databases
`\du`	List roles
`\dt`	List tables (current schema)
`\d+ table_name`	Describe table with size
`\conninfo`	Show connection details
`\q`	Quit

Service and health

Command	Description
`systemctl status postgresql`	Service status (Debian/Ubuntu)
`pg_isready -h localhost -p 5432`	Check if server accepts connections
`pg_ctlcluster 16 main status`	Cluster status (Debian)
`ss -tlnp \| grep 5432`	Confirm listener
`tail -f /var/log/postgresql/postgresql-16-main.log`	Follow log (Debian path)

Monitoring queries

-- Active sessions
SELECT pid, usename, datname, state, wait_event_type, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start;

-- Blocking locks
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       blocked.query AS blocked_query, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks blk ON blk.locktype = bl.locktype AND blk.database IS NOT DISTINCT FROM bl.database
  AND blk.relation IS NOT DISTINCT FROM bl.relation AND blk.page IS NOT DISTINCT FROM bl.page
  AND blk.tuple IS NOT DISTINCT FROM bl.tuple AND blk.virtualxid IS NOT DISTINCT FROM bl.virtualxid
  AND blk.transactionid IS NOT DISTINCT FROM bl.transactionid AND blk.classid IS NOT DISTINCT FROM bl.classid
  AND blk.objid IS NOT DISTINCT FROM bl.objid AND blk.objsubid IS NOT DISTINCT FROM bl.objsubid
  AND blk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = blk.pid AND blk.granted;

-- Database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY 2 DESC;

Session management

SQL	Description
`SELECT pg_cancel_backend(pid);`	Cancel query (keep connection)
`SELECT pg_terminate_backend(pid);`	Terminate connection
`SHOW max_connections;`	Connection limit
`SELECT count(*) FROM pg_stat_activity;`	Current connection count

Streaming replication

Command / SQL	Description
`SELECT pg_is_in_recovery();`	`true` on a standby
`SELECT * FROM pg_stat_replication;`	WAL senders on primary (lag, state)
`SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();`	Standby receive/replay position
`pg_basebackup -h primary -U replicator -D /path -P -R`	Clone primary to new standby
`SELECT pg_promote();`	Promote standby to primary (PG 12+)

Logical replication

-- On publisher
CREATE PUBLICATION my_pub FOR TABLE orders, customers;

-- On subscriber
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=primary dbname=mydb user=replicator password=...'
  PUBLICATION my_pub;

-- Monitor
SELECT * FROM pg_stat_subscription;
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel;

Replication lag check

-- On primary: bytes behind primary
SELECT application_name, client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag_bytes,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Backup and restore

Command	Description
`pg_dump -Fc mydb > mydb.dump`	Custom-format logical backup
`pg_dumpall > all.sql`	All databases + globals
`pg_restore -d mydb mydb.dump`	Restore from custom format
`pg_basebackup -h primary -D /backup/data -U replicator -Fp -Xs -P`	Physical base backup
`psql -c "SELECT pg_switch_wal();"`	Force WAL segment switch (with archiving)

Useful SQL

SQL	Description
`EXPLAIN ANALYZE SELECT ...`	Query plan with runtime
`VACUUM ANALYZE table_name;`	Reclaim space and update stats
`SELECT version();`	Server version
`SELECT current_setting('data_directory');`	Data directory path

Pro tips

Connection refused? Check pg_isready and listen_addresses in postgresql.conf
Auth failures almost always trace to pg_hba.conf — match order matters, first rule wins
Use pg_stat_activity before restarting — see what's running
After config changes: systemctl reload postgresql (many params) or restart (some need full restart)
Replication ≠ backup — schedule pg_dump or pgBackRest; test restores

Practice scenarios

Hands-on Postgres scenarios on live Linux VMs: postgres