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

Typical Use Case

PostgreSQL ops: connect, create tables and indexes, manage users and privileges, backup/restore with pg_dump, locate slow queries and lock waits, and troubleshoot connection limits and table bloat.

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

Parameter matrix

Parameter	Effect	Example
`-U`	Username to log in as	`psql -U postgres`
`-d`	Database to connect to	`psql -U app -d mydb`
`-h`	Host to connect to	`psql -h 127.0.0.1 -U app`
`-c`	Run a single SQL statement and exit	`psql -U postgres -c 'SELECT version();'`
`-f`	Execute a SQL file	`psql -U postgres -d mydb -f schema.sql`
`--clean`	Drop objects before restoring over them	`pg_restore --clean -U postgres -d mydb dump.dump`
`EXPLAIN ANALYZE`	Actually execute the query and print both plan and timings	`EXPLAIN ANALYZE SELECT * FROM t WHERE id=1;`
`pg_stat_activity`	View showing active connections and the locks they hold	`SELECT * FROM pg_stat_activity WHERE state<>$$idle$$;`
`VACUUM`	Reclaim dead tuples and refresh planner statistics	`VACUUM ANALYZE mytable;`
`pg_stat_statements`	Extension that aggregates SQL timing and call counts	`SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC;`

Common pitfalls

SymptomConnecting fails with remaining connection slots are reserved for non-replication superuser connections.

Causemax_connections is exhausted, so ordinary clients are refused.

FixPut a pooler such as pgbouncer in front, raise max_connections, and clear idle sessions with pg_terminate_backend.

SymptomA query is slow and EXPLAIN shows a Seq Scan.

CauseA missing index, or stale statistics that mislead the planner.

FixIndex the filter columns, run ANALYZE to refresh statistics, and stop applying functions or implicit casts to indexed columns.

SymptomThe table is far larger than the data it holds (bloat).

CauseHeavy UPDATE and DELETE activity left dead tuples that were never reclaimed.

FixRun VACUUM regularly (autovacuum is on by default), and on hot-updated tables use VACUUM FULL or pg_repack to rebuild.

SymptomRestoring a pg_dump fails with permission or already-exists errors.

CauseThe backup and the target disagree on owner or schema, or the same data was imported twice.

FixRestore with --clean, confirm the target is empty or overwritable, and pass -O to ignore ownership differences.

SymptomA long transaction holds a lock and everything else queues behind it.

CauseAn uncommitted transaction or an explicit LOCK is sitting on the resource.

FixInspect pg_stat_activity and pg_locks to find the blocker, then terminate long idle-in-transaction sessions.

SymptomCapitalisation or reserved words lead to Identifier too long or a syntax error.

CauseUnquoted names are folded to lower case, or the name exceeds the 63-byte limit.

FixStandardise on short lower-case names with underscores under 63 bytes, and only double-quote when you truly need mixed case, consistently.

Troubleshooting

1Inspect active connections and blockers
SELECT pid, state, query FROM pg_stat_activity WHERE state <> $$idle$$;
Finds long transactions and blocking sessions; finish them with pg_terminate_backend(pid) when needed.
2Find the slowest SQL
SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
Requires CREATE EXTENSION pg_stat_statements first, then optimise the winner with EXPLAIN ANALYZE.
3Review table bloat
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
A high dead-tuple count means it is time to schedule a VACUUM.
4Restore a single database from backup
pg_restore --clean -U postgres -d mydb dump.dump
--clean drops before it creates, so check the state of the target database before you run it.

Command Examples

Find the blocker: transactions and locks first, then decide who to kill

-- 1) Sessions sitting on an open transaction (the usual source of blocking)
SELECT pid, state, query FROM pg_stat_activity WHERE state = 'idle in transaction';

-- 2) The full lock-wait picture: who is waiting, and on whom
SELECT pid, mode, granted, query FROM pg_locks l JOIN pg_stat_activity a USING(pid) WHERE NOT l.granted;

-- 3) Try the polite cancel first, keeping the session connected
SELECT pg_cancel_backend(12345);

-- 4) Drop the connection only after confirming the session is useless
SELECT pg_terminate_backend(12345);

Order matters: pg_cancel_backend sends a cancel signal that aborts the running statement while leaving the session usable, whereas pg_terminate_backend drops the connection outright and throws away everything uncommitted in that transaction. An idle in transaction session holds its locks indefinitely, so read the first query before picking a victim.

Output

 pg_terminate_backend
---------------------
 t
(1 row)

Backup and parallel restore: custom format plus multiple workers

# Logical dump in the compressed custom format (-Fc), which allows parallel restore
pg_dump -U postgres -Fc db_name > db.dump

# Replay with 4 workers in parallel to cut wall time on a large database
pg_restore -U postgres -d db_name -j 4 db.dump

# Role definitions live in their own dump: restore roles before objects when migrating

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

Only the -Fc custom format supports pg_restore -j; plain SQL text can only be replayed single-threaded, which is an order of magnitude slower on a large database. Restore roles before objects, otherwise the run fails halfway with permission errors about missing owners. Confirm the target database is empty, or pass --clean, because existing objects of the same name abort the restore.

Locating slow queries and bloat: plans, buffer hits, scan counts, size

-- 1) Plan only, nothing is executed
EXPLAIN SELECT * FROM t WHERE col = 1;

-- 2) Really run it and report actual timings (this does execute DML!)
EXPLAIN ANALYZE SELECT * FROM t WHERE col = 1;

-- 3) Add buffer hits to tell CPU work apart from IO work
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM t WHERE col = 1;

-- 4) Tables that keep getting full scans: index these first
SELECT * FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_scan DESC;

-- 5) Tables with the most dead tuples: these need a VACUUM
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- 6) How much disk the current database occupies
SELECT pg_size_pretty(pg_database_size(current_database()));

EXPLAIN ANALYZE is not read-only: it really executes the statement, so on an UPDATE, DELETE or INSERT it modifies data on the spot, and a forgotten WHERE clause rewrites the whole table. seq_scan is a cumulative counter that also includes normal startup and maintenance scans, so a missing index only shows up as a combination of a large table with a high share of sequential scans. A high n_dead_tup means UPDATEs and DELETEs left dead tuples behind and the disk space is never handed back to the operating system automatically.

Create a role, grant access, then reload changed parameters

-- A role that exists purely to run the application
CREATE ROLE app_user WITH LOGIN PASSWORD 'replace-with-a-secret';

-- Grant connection rights and nothing else yet
GRANT CONNECT ON DATABASE db TO app_user;

-- Persist the setting, then hot-reload it without dropping connections
ALTER SYSTEM SET shared_buffers = 4GB;
SELECT pg_reload_conf();

ALTER SYSTEM writes postgresql.auto.conf and nothing takes effect immediately: settings like shared_buffers keep their old value after pg_reload_conf() until a restart, and the server log notes which pending settings need one. The t return value only means the configuration file was read and the reload signal was sent, not that the change is live.

Output

 pg_reload_conf
---------------
 t
(1 row)

Common Pitfalls

Grants like GRANT ALL ON SCHEMA public or GRANT ALL ON DATABASE hand the whole database to the application account: once the application is compromised through SQL injection, an executable upload directory or a poisoned dependency, the attacker gets read-write plus object creation on everything, not just the application tables. Grant application roles per table and revoke the privileges public holds by default.
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret' stores the password in clear text inside a script, and those scripts tend to end up in version control, CI logs and backup files, so every rotation means digging through history. Set the password interactively with \password or inject it from a secrets manager instead of writing it to a file.
EXPLAIN ANALYZE genuinely executes the statement. On an UPDATE, DELETE or INSERT it writes data immediately, and a missing WHERE clause rewrites the entire table. Use plain EXPLAIN to inspect the plan, or wrap the statement in BEGIN ... ROLLBACK.
VACUUM FULL takes an ACCESS EXCLUSIVE lock and rewrites the whole table, blocking every read and write on it, which on a large table is equivalent to downtime; pg_terminate_backend on a session that is mid-write discards the data that transaction already wrote. Reclaim space with pg_repack, or do it in a maintenance window.
A failed CREATE INDEX CONCURRENTLY leaves an INVALID index behind: it still consumes disk and slows writes, but the planner will not use it, so queries keep doing sequential scans and it is easy to conclude the index "was built, it just is not chosen". Also, CONCURRENTLY cannot run inside a transaction block.

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.