PostgreSQL 命令速查表 - PostgreSQL 数据库常用命令大全
面向要连接管理、做查询分析、处理锁问题的 PostgreSQL 使用者。PostgreSQL 的特色在于 MVCC 与丰富的类型/索引，但也带来锁等待、VACUUM、表膨胀这些独有的运维课题。读完能用 psql 完成角色与库管理，用 EXPLAIN ANALYZE 判断查询代价集中在顺序扫描还是索引，定位 pg_locks 中的锁等待与死锁，并用 pg_dump/pg_restore 做跨实例迁移与恢复。
典型使用场景
PostgreSQL 运维：连接、建表与索引、用户权限、备份恢复（pg_dump/restore）、定位慢查询与锁等待，以及排查连接数上限与膨胀（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;参数矩阵
| 参数 | 作用 | 示例 |
|---|---|---|
-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; |
易错点与避坑指南
现象连接报 remaining connection slots are reserved for non-replication superuser connections。
原因max_connections 用尽，普通连接被拒。
处置用连接池（pgbouncer）；调大 max_connections；结束空闲连接（pg_terminate_backend）。
现象查询很慢，EXPLAIN 显示 Seq Scan。
原因缺索引或统计信息过期导致优化器误判。
处置对过滤列建索引；跑 ANALYZE 更新统计；避免对索引列做函数/隐式类型转换。
现象表体积远大于实际数据（膨胀 bloat）。
原因大量 UPDATE/DELETE 产生 dead tuples 未回收。
处置定期 VACUUM（autovacuum 已默认开）；对高频更新表手动 VACUUM FULL 或 pg_repack 重建。
现象pg_dump 恢复时报权限或对象已存在错误。
原因备份与恢复库模式不一致，或重复导入。
处置恢复前用 --clean 先清理；确认目标库为空或同名对象可覆盖；用 -O 忽略属主差异。
现象事务长时间持有锁，其它操作排队。
原因未提交的长事务或显式 LOCK 占住资源。
处置查 pg_stat_activity 与 pg_locks 定位阻塞源；kill 长时间 idle-in-transaction 连接。
现象大小写/保留字导致 Identifier too long 或语法错。
原因未加引号的对象名被折叠为小写，或名称超 63 字节限制。
处置统一规范命名（<63 字节、小写、下划线）；确需保留大小写时用双引号并保持一致。
排障路径
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 再创建，恢复前确认目标状态。
提示
- CREATE INDEX CONCURRENTLY 不能在事务块中使用，如果中间失败会留下 INVALID 索引，需 DROP 后重建。
- idle in transaction 连接是最常见的锁阻塞源头，设置 idle_in_transaction_session_timeout 自动清理。
- pg_dump 是逻辑备份，大数据量用 pg_basebackup 做物理备份 + WAL 归档实现 PITR。
常见问题
PostgreSQL 主键用自增还是 UUID？serial 和 identity 有何区别？
单库并发不高时用自增主键写入快、占用小；需要跨库合并或分布式唯一时用 UUID。自增优先用 identity 列（GENERATED ... AS IDENTITY）而非旧的 serial，前者更规范，序列与表绑定关系也更清晰。
VACUUM 是干什么的，为什么我的表会膨胀？
PostgreSQL 的 MVCC 让每次更新和删除都留下死元组，VACUUM 负责回收这些死行空间并供后续插入复用；内建 autovacuum 默认会自动跑，但更新频繁且没及时触发时表就会膨胀。表异常大、性能下滑时可手动 VACUUM FULL（注意它要锁表），并关注 autovacuum_vacuum_scale_factor 等配置。
psql 连接报 role does not exist 或 password authentication failed 是什么原因？
role does not exist 表示登录用户名不是库里的角色，常见是误用了系统用户名，应换成已存在的角色或先 CREATE ROLE；password authentication failed 表示该角色启用了密码但口令不对，或 pg_hba.conf 的认证方式（md5/scram-sha-256）与现状不匹配，需要修正口令或用匹配的认证协议重连。
查询被锁住或一直等待是怎么回事，想排查怎么办？
被锁通常是有别的事务持有锁，最常见的是开着事务一直不提交或长事务未结束。用 pg_stat_activity 查看 state 为 idle in transaction 的会话，结合 pg_locks 的 wait_event 字段判断等待类型，杀掉阻塞会话并让长事务尽快提交或回滚即可恢复。
想迁移或备份整个库，pg_dump 和 pg_dumpall 怎么选？
迁移单个库用 pg_dump 导出、pg_restore 恢复，能指定格式并做选择性或增量恢复；要连角色、表空间、权限等全局对象一起备份则用 pg_dumpall 或额外转储全局数据。跨版本迁移时先恢复 schema 再同步数据仍是稳妥套路。
由 巧匠 维护
公开更新于 2026年7月21日，内容持续校对官方文档。