PostgreSQL 常用命令速查表
把 PostgreSQL 日常最常敲的命令按连接、库表、查询分析、锁排查、备份恢复和复制分组整理，排障现场直接复制改参数即可。
返回 数据库连接与角色 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;查看复制延迟字节数
💡 提示
- CREATE INDEX CONCURRENTLY 不能在事务块中使用，如果中间失败会留下 INVALID 索引，需 DROP 后重建。
- idle in transaction 连接是最常见的锁阻塞源头，设置 idle_in_transaction_session_timeout 自动清理。
- pg_dump 是逻辑备份，大数据量用 pg_basebackup 做物理备份 + WAL 归档实现 PITR。