PostgreSQL 命令速查表 - PostgreSQL 数据库常用命令大全

面向要连接管理、做查询分析、处理锁问题的 PostgreSQL 使用者。PostgreSQL 的特色在于 MVCC 与丰富的类型/索引，但也带来锁等待、VACUUM、表膨胀这些独有的运维课题。读完能用 psql 完成角色与库管理，用 EXPLAIN ANALYZE 判断查询代价集中在顺序扫描还是索引，定位 pg_locks 中的锁等待与死锁，并用 pg_dump/pg_restore 做跨实例迁移与恢复。

数据库·共 37 条命令·最后更新 2026-07-21

典型使用场景

PostgreSQL 运维：连接、建表与索引、用户权限、备份恢复（pg_dump/restore）、定位慢查询与锁等待，以及排查连接数上限与膨胀（bloat）。

连接与角色 Connection & Role 7

psql -U postgres -h 127.0.0.1 -p 5432

连接指定用户和主机，-W 强制密码提示

psql -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;

授予 schema 权限

ALTER SYSTEM SET shared_buffers = 4GB;

修改参数需 reload，部分需 restart

SELECT pg_reload_conf();

重载配置文件，不中断连接

库表操作 Database & Table 8

\l

列出所有数据库

\dt

列出当前库的所有表

\d table_name

查看表结构、索引和约束

\d+ table_name

查看表结构详情含描述和存储信息

CREATE INDEX CONCURRENTLY idx_name ON t(col);

并发建索引不锁表，但耗时更长且不能在事务中用

VACUUM ANALYZE t;

回收死元组并更新统计信息，不锁表

VACUUM FULL t;

全量回收磁盘空间，会锁表且重写整张表

REINDEX INDEX CONCURRENTLY idx_name;

并发重建索引不锁表（PG 12+）

查询分析 Query Analysis 6

EXPLAIN SELECT * FROM t WHERE col = 1;

查看执行计划，不执行查询

EXPLAIN ANALYZE SELECT * FROM t WHERE col = 1;

执行并显示实际耗时，注意会修改数据（DML）

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

含缓冲区命中信息，判断 IO 瓶颈

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;

查看死元组最多的表，需要 VACUUM

锁与事务 Lock & Transaction 6

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

查看活跃查询，找长查询和卡住的连接

SELECT pg_cancel_backend(<pid>);

取消查询（发 SIGINT），不断开连接

SELECT pg_terminate_backend(<pid>);

终止连接（发 SIGTERM），强制断开

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.sql

逻辑备份单个库

pg_dump -U postgres -Fc db_name > db.dump

自定义压缩格式，支持并行恢复

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

并行恢复（4 个 worker），加速大库恢复

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

只备份角色定义，迁移时先恢复

SELECT pg_start_backup("label");

开始物理备份（需 archive_mode 开启）

SELECT pg_walfile_name(pg_current_wal_lsn());

查看当前 WAL 文件名

流复制 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 再创建，恢复前确认目标状态。

命令示例

定位锁阻塞源头：先看事务与锁，再决定终止谁

-- 1) 谁开着事务不提交（最常见的阻塞源）
SELECT pid, state, query FROM pg_stat_activity WHERE state = 'idle in transaction';

-- 2) 锁等待的完整现场：谁在等、等谁
SELECT pid, mode, granted, query FROM pg_locks l JOIN pg_stat_activity a USING(pid) WHERE NOT l.granted;

-- 3) 先尝试温和取消，不断开连接
SELECT pg_cancel_backend(12345);

-- 4) 确认无用后再强制断开
SELECT pg_terminate_backend(12345);

顺序不能反：pg_cancel_backend 发取消信号让当前查询中止，连接还在、可以继续用；pg_terminate_backend 直接断开会话，事务里未提交的数据全部丢失。空转的 idle in transaction 连接会一直占着锁不放，先看第 1 条查询的结果再决定动谁。

输出

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

备份与并行恢复：自定义格式 + 多 worker 回放

# 逻辑备份为自定义压缩格式（-Fc），支持并行恢复
pg_dump -U postgres -Fc db_name > db.dump

# 用 4 个 worker 并行恢复，加速大库回放
pg_restore -U postgres -d db_name -j 4 db.dump

# 角色定义单独导出，迁移时先恢复角色再建对象
pg_dumpall -U postgres --roles-only > roles.sql

只有 -Fc 自定义格式才支持 pg_restore 的 -j 并行；纯 SQL 文本格式只能单线程顺序回放，大库恢复时间差一个数量级。角色先于对象恢复，否则恢复到一半会因缺少属主/角色而报权限错误。恢复前确认目标库为空或配合 --clean 先清理，否则同名对象已存在直接报错。

慢查询与膨胀定位：计划、缓冲区命中、扫描次数、空间

-- 1) 只看计划不执行
EXPLAIN SELECT * FROM t WHERE col = 1;

-- 2) 真跑一次并给出实际耗时（会真实执行 DML！）
EXPLAIN ANALYZE SELECT * FROM t WHERE col = 1;

-- 3) 带缓冲区命中，判断瓶颈是 CPU 还是 IO
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM t WHERE col = 1;

-- 4) 哪些表被反复全表扫描，优先给它们加索引
SELECT * FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_scan DESC;

-- 5) 死元组最多的表，需要 VACUUM
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- 6) 当前库占了多少磁盘
SELECT pg_size_pretty(pg_database_size(current_database()));

EXPLAIN ANALYZE 不是只读操作，它会真正执行语句：写在 UPDATE / DELETE / INSERT 上就是当场改数据，忘了加 WHERE 就会改全表。seq_scan 计数是累积值且包含正常的启动与维护扫描，判断是否缺索引要看「表很大 + seq_scan 占比高」的组合。n_dead_tup 高说明 UPDATE/DELETE 留下了死元组，磁盘不会自动还给操作系统。

建账号收权限 + 改参数后热加载

-- 建一个只用来跑程序的登录角色
CREATE ROLE app_user WITH LOGIN PASSWORD 'replace-with-a-secret';

-- 只给连接权限
GRANT CONNECT ON DATABASE db TO app_user;

-- 持久化参数，然后热加载（不中断连接）
ALTER SYSTEM SET shared_buffers = 4GB;
SELECT pg_reload_conf();

ALTER SYSTEM 改的是 postgresql.auto.conf，参数本身不会立刻生效：shared_buffers 这类需要重启的配置 pg_reload_conf() 之后仍是旧值（可在日志里看到「参数变更需重启」的提示）。返回 t 表示配置文件读到了、已排队的信号发成功，不等于配置已应用。

输出

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

常见坑与注意事项

GRANT ALL ON SCHEMA public、GRANT ALL ON DATABASE 这类授权会把整个库交给应用账号：应用一旦被拿下（SQL 注入、上传目录可执行、依赖投毒），攻击者拿到的不只是自己的表，而是全库读写与建对象的能力。应用账号应逐表授权，并回收 public 上默认存在的权限。
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret' 把口令明文写进脚本；这类脚本通常同时进了版本库、CI 日志与备份文件，密码轮换时还要翻历史记录逐个改。口令用 \password 交互输入或从密钥管理系统注入，别落到文件里。
EXPLAIN ANALYZE 会真实执行语句。对 UPDATE / DELETE / INSERT 使用它就是当场写数据，漏写 WHERE 时直接改全表；只想看计划请用不带 ANALYZE 的 EXPLAIN，或把语句包在 BEGIN ... ROLLBACK 里。
VACUUM FULL 会拿 ACCESS EXCLUSIVE 锁并重写整张表，运行期间该表的读写全部阻塞，大表上等同于一次停机；pg_terminate_backend 强杀正在写事务的会话，则把该事务已写入的数据一起丢掉。要回收空间用 pg_repack，或在维护窗口内做。
CREATE INDEX CONCURRENTLY 中途失败会留下 INVALID 索引：它照样占磁盘、拖慢写入，但优化器不会使用它，于是查询仍然全表扫描，很容易被误认为「索引已经建好了只是没走」。另外 CONCURRENTLY 不能在事务块里执行。

提示

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日，内容持续校对官方文档。

联系我们

命令或描述有误？提交反馈、商务合作或产品建议都可发送邮件给我们。

联系我们

典型使用场景

连接与角色 Connection & Role 7

库表操作 Database & Table 8

查询分析 Query Analysis 6

锁与事务 Lock & Transaction 6

备份恢复 Backup & Recovery 6

流复制 Streaming Replication 4

参数矩阵

易错点与避坑指南

排障路径

命令示例

定位锁阻塞源头：先看事务与锁，再决定终止谁

备份与并行恢复：自定义格式 + 多 worker 回放

慢查询与膨胀定位：计划、缓冲区命中、扫描次数、空间

建账号收权限 + 改参数后热加载

常见坑与注意事项

提示

常见问题

相关速查表

分享

官方参考来源

由 巧匠 维护

联系我们

由巧匠维护