PostgreSQL Cheatsheet - PostgreSQL Command Reference

For anyone connecting, analyzing queries, or resolving lock issues on PostgreSQL. Its strength is MVCC and a rich type/index ecosystem, but that also brings lock waits, VACUUM, and table bloat unique to it. By the end you can manage roles and databases with psql, use EXPLAIN ANALYZE to see whether cost comes from a sequential scan or an index, locate lock waits and deadlocks in pg_locks, and migrate or restore across instances with pg_dump/pg_restore.

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

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.

FAQ

Should the PostgreSQL primary key be auto-increment or UUID, and what is the difference between serial and identity?

For a single database with modest concurrency, an auto-increment primary key writes fast and stays compact; use UUID when objects must be unique across databases or in a distributed setting. For auto-increment prefer IDENTITY columns defined with GENERATED ... AS IDENTITY over the older serial, because identity is cleaner and its sequence ownership is clearer.

What does VACUUM do, and why does my table grow (bloat) in PostgreSQL?

MVCC leaves a dead tuple behind on every update and delete; VACUUM reclaims that space so later inserts can reuse it. The built-in autovacuum normally runs on its own, but tables bloat when updates are heavy and a vacuum is not triggered in time. For an abnormally large, slow table you can run VACUUM FULL manually (it locks the table) and tune settings such as autovacuum_vacuum_scale_factor.

What do the psql errors role does not exist and password authentication failed mean in PostgreSQL?

role does not exist means the login name is not a role in the cluster, often because you used a system username instead — switch to an existing role or create one with CREATE ROLE. password authentication failed means the role has a password but the supplied password is wrong, or the auth method in pg_hba.conf (md5 vs scram-sha-256) does not match, so correct the password or reconnect with a matching auth protocol.

Why do my queries get stuck waiting, and how do I debug locks in PostgreSQL?

A query waits because another transaction holds the lock, most often a session left idle in transaction or a long transaction that never finishes. Check pg_stat_activity for sessions with state idle in transaction, use pg_locks and the wait_event column to identify the wait type, then terminate the blocking session and get the long transaction to commit or roll back.

Should I use pg_dump or pg_dumpall for migration or backup in PostgreSQL?

For a single database use pg_dump to export and pg_restore to restore, which supports chosen formats and selective or incremental restores. To include global objects such as roles, tablespaces, and privileges, use pg_dumpall or additionally dump the globals. For cross-version migration, restoring the schema first and then the data remains a safe approach.

Official References

Each command links to its official documentation below, so you can verify the latest usage and read deeper.

Official Docs

Maintained by LaoHand

Publicly updated on Jul 21, 2026, continuously proofread against official docs.

Contact Us

Wrong command or description? Send us corrections, business inquiries or product feedback by email.