PostgreSQL Cheatsheet - PostgreSQL Command Reference

Essential PostgreSQL commands for daily database administration and development. Organized by scenario from table creation to query optimization. Copy and use directly during troubleshooting.

Databases·37 commands·Last updated 2026-07-21

Back to Databases

Connection & Role 7

psql -U postgres -h 127.0.0.1 -p 5432⧉

Connect with user and host, -W forces password prompt

psql -U postgres -d db_name -c "\dt"⧉

Execute command and exit, good for scripts

CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';⧉

Create a login role

GRANT CONNECT ON DATABASE db TO app_user;⧉

Grant database connect privilege

GRANT ALL ON SCHEMA public TO app_user;⧉

Grant schema privileges

ALTER SYSTEM SET shared_buffers = 4GB;⧉

Change parameter, needs reload, some need restart

SELECT pg_reload_conf();⧉

Reload config without disconnecting

Database & Table 8

\l⧉

List all databases

\dt⧉

List all tables in current database

\d table_name⧉

View table structure, indexes, and constraints

\d+ table_name⧉

View detailed table info with description and storage

CREATE INDEX CONCURRENTLY idx_name ON t(col);⧉

Create index without locking table, but takes longer and cannot be used in transactions

VACUUM ANALYZE t;⧉

Reclaim dead tuples and update statistics, no lock

VACUUM FULL t;⧉

Full disk space reclamation, locks table and rewrites entire table

REINDEX INDEX CONCURRENTLY idx_name;⧉

Rebuild index without locking (PG 12+)

Query Analysis 6

EXPLAIN SELECT * FROM t WHERE col = 1;⧉

View execution plan, does not execute query

EXPLAIN ANALYZE SELECT * FROM t WHERE col = 1;⧉

Execute and show actual timing, beware of DML modifications

EXPLAIN (ANALYZE, BUFFERS) SELECT ...⧉

Include buffer hit info, identify I/O bottlenecks

SELECT * FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_scan DESC;⧉

Find tables with frequent full scans, consider adding indexes

SELECT pg_size_pretty(pg_database_size(current_database()));⧉

View current database size

SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;⧉

Find tables with most dead tuples, needs VACUUM

Lock & Transaction 6

SELECT pid, state, query FROM pg_stat_activity WHERE state != 'idle';⧉

View active queries, find long-running and stuck connections

SELECT pg_cancel_backend(<pid>);⧉

Cancel query (SIGINT), keeps connection open

SELECT pg_terminate_backend(<pid>);⧉

Terminate connection (SIGTERM), force disconnect

SELECT * FROM pg_locks WHERE NOT granted;⧉

View waiting locks

SELECT pid, mode, granted, query FROM pg_locks l JOIN pg_stat_activity a USING(pid) WHERE NOT l.granted;⧉

Lock wait query with blocker identification

SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';⧉

Find idle but uncommitted transactions that hold locks

Backup & Recovery 6

pg_dump -U postgres db_name > backup.sql⧉

Logical backup of a single database

pg_dump -U postgres -Fc db_name > db.dump⧉

Custom compressed format, supports parallel restore

pg_restore -U postgres -d db_name -j 4 db.dump⧉

Parallel restore (4 workers), speed up large restores

pg_dumpall -U postgres --roles-only > roles.sql⧉

Backup role definitions only, restore first during migration

SELECT pg_start_backup("label");⧉

Start physical backup (requires archive_mode enabled)

SELECT pg_walfile_name(pg_current_wal_lsn());⧉

View current WAL file name

Streaming Replication 4

SELECT application_name, state, sync_state, sent_lsn, write_lsn FROM pg_stat_replication;⧉

Primary side: view replica sync status

SELECT status, receive_lsn, replay_lsn FROM pg_stat_wal_receiver;⧉

Replica side: view receive and replay progress

SELECT NOW() - pg_last_xact_replay_timestamp() AS replication_lag;⧉

View replication lag duration

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication;⧉

View replication lag in bytes

💡 Tips

CREATE INDEX CONCURRENTLY cannot be used in a transaction block. If it fails mid-way, it leaves an INVALID index that must be DROPped and recreated.
idle in transaction connections are the most common source of lock blocking. Set idle_in_transaction_session_timeout to auto-clean them.
pg_dump is logical backup; for large data volumes, use pg_basebackup for physical backup + WAL archiving for PITR.