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.
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 5432psql -U postgres -d db_name -c "\dt"CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';GRANT CONNECT ON DATABASE db TO app_user;GRANT ALL ON SCHEMA public TO app_user;ALTER SYSTEM SET shared_buffers = 4GB;SELECT pg_reload_conf();Database & Table 8
\l\dt\d table_name\d+ table_nameCREATE INDEX CONCURRENTLY idx_name ON t(col);VACUUM ANALYZE t;VACUUM FULL t;REINDEX INDEX CONCURRENTLY idx_name;Query Analysis 6
EXPLAIN SELECT * FROM t WHERE col = 1;EXPLAIN ANALYZE SELECT * FROM t WHERE col = 1;EXPLAIN (ANALYZE, BUFFERS) SELECT ...SELECT * FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_scan DESC;SELECT pg_size_pretty(pg_database_size(current_database()));SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;Lock & Transaction 6
SELECT pid, state, query FROM pg_stat_activity WHERE state != 'idle';SELECT pg_cancel_backend(<pid>);SELECT pg_terminate_backend(<pid>);SELECT * FROM pg_locks WHERE NOT granted;SELECT pid, mode, granted, query FROM pg_locks l JOIN pg_stat_activity a USING(pid) WHERE NOT l.granted;SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';Backup & Recovery 6
pg_dump -U postgres db_name > backup.sqlpg_dump -U postgres -Fc db_name > db.dumppg_restore -U postgres -d db_name -j 4 db.dumppg_dumpall -U postgres --roles-only > roles.sqlSELECT pg_start_backup("label");SELECT pg_walfile_name(pg_current_wal_lsn());Streaming Replication 4
SELECT application_name, state, sync_state, sent_lsn, write_lsn FROM pg_stat_replication;SELECT status, receive_lsn, replay_lsn FROM pg_stat_wal_receiver;SELECT NOW() - pg_last_xact_replay_timestamp() AS replication_lag;SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication;Parameter matrix
| 参数 | Effect | Example |
|---|---|---|
-U | 指定登录用户 | psql -U postgres |
-d | 指定数据库 | psql -U app -d mydb |
-h | 指定主机 | psql -h 127.0.0.1 -U app |
-c | 直接执行一条 SQL | psql -U postgres -c 'SELECT version();' |
-f | 执行 SQL 文件 | psql -U postgres -d mydb -f schema.sql |
--clean | 恢复前先 DROP 对象 | pg_restore --clean -U postgres -d mydb dump.dump |
EXPLAIN ANALYZE | 真实执行并输出计划与耗时 | EXPLAIN ANALYZE SELECT * FROM t WHERE id=1; |
pg_stat_activity | 视图：查看活动连接与锁 | SELECT * FROM pg_stat_activity WHERE state<>$$idle$$; |
VACUUM | 回收 dead tuples、更新统计 | VACUUM ANALYZE mytable; |
pg_stat_statements | 扩展：聚合 SQL 耗时与调用次数 | SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC; |
Common pitfalls
Symptom连接报 remaining connection slots are reserved for non-replication superuser connections。
Causemax_connections 用尽，普通连接被拒。
Fix用连接池（pgbouncer）；调大 max_connections；结束空闲连接（pg_terminate_backend）。
Symptom查询很慢，EXPLAIN 显示 Seq Scan。
Cause缺索引或统计信息过期导致优化器误判。
Fix对过滤列建索引；跑 ANALYZE 更新统计；避免对索引列做函数/隐式类型转换。
Symptom表体积远大于实际数据（膨胀 bloat）。
Cause大量 UPDATE/DELETE 产生 dead tuples 未回收。
Fix定期 VACUUM（autovacuum 已默认开）；对高频更新表手动 VACUUM FULL 或 pg_repack 重建。
Symptompg_dump 恢复时报权限或对象已存在错误。
Cause备份与恢复库模式不一致，或重复导入。
Fix恢复前用 --clean 先清理；确认目标库为空或同名对象可覆盖；用 -O 忽略属主差异。
Symptom事务长时间持有锁，其它操作排队。
Cause未提交的长事务或显式 LOCK 占住资源。
Fix查 pg_stat_activity 与 pg_locks 定位阻塞源；kill 长时间 idle-in-transaction 连接。
Symptom大小写/保留字导致 Identifier too long 或语法错。
Cause未加引号的对象名被折叠为小写，或名称超 63 字节限制。
Fix统一规范命名（<63 字节、小写、下划线）；确需保留大小写时用双引号并保持一致。
Troubleshooting
1查看活动连接与阻塞
SELECT pid, state, query FROM pg_stat_activity WHERE state <> $$idle$$;定位长事务与阻塞源，必要时用 pg_terminate_backend(pid) 结束。
2定位最慢的 SQL
SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;需先 CREATE EXTENSION pg_stat_statements；结合 EXPLAIN ANALYZE 优化。
3查看表膨胀情况
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;dead tuples 多时安排 VACUUM。
4从备份恢复单库
pg_restore --clean -U postgres -d mydb dump.dump--clean 会先 DROP 再创建，恢复前确认目标状态。
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.
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.
Contact Us