PostgreSQL DBA 应该掌握的 100 条命令

前言

PostgreSQL 的运维逻辑与 MySQL、Oracle 有明显差异。很多刚接触 PostgreSQL 的 DBA，看到数据库连接数升高、表空间增长、SQL 卡顿或者主从延迟时，仍然沿用其他数据库的处理习惯：先看 CPU、再看磁盘、最后考虑重启。

但 PostgreSQL 的很多生产问题，实际上都与长事务、MVCC 垃圾版本、锁等待、统计信息失真、WAL 堆积、复制槽未消费以及 Autovacuum 工作不充分有关。

下面整理 100 条 PostgreSQL DBA 日常使用频率较高的命令，覆盖实例信息、连接会话、事务锁、SQL 性能、表与索引、Vacuum、WAL、主从复制、备份恢复和权限管理等核心场景。

本文主要面向 PostgreSQL 14—18。不同版本的统计视图字段可能存在差异，执行前应先确认数据库版本。当前 PostgreSQL 官方文档的 Current 版本为 PostgreSQL 18。

一、实例与基础信息

1. 查看 PostgreSQL 版本

SELECT version();

返回 PostgreSQL 版本、编译器、操作系统架构等信息。

也可以只查看版本号：

SHOW server_version;

2. 查看服务器版本号

SELECT current_setting('server_version');

在脚本中使用current_setting()，通常比解析version()的返回文本更方便。

3. 查看当前数据库

SELECT current_database();

4. 查看当前用户

SELECT current_user;

同时查看当前用户和会话用户：

SELECT current_user, session_user;

session_user表示最初建立连接的用户，current_user可能因SET ROLE等操作发生变化。

5. 查看数据库服务器地址和端口

SELECT inet_server_addr(), inet_server_port();

在 VIP、负载均衡、读写分离和多实例环境中，可以用它确认当前连接到了哪台数据库。

6. 查看客户端地址和端口

SELECT inet_client_addr(), inet_client_port();

7. 查看数据库启动时间

SELECT pg_postmaster_start_time();

计算实例已经运行了多长时间：

SELECT now() - pg_postmaster_start_time() AS uptime;

8. 查看当前时间和时区

SELECT now(), current_timestamp, current_setting('TimeZone');

9. 查看数据目录

SHOW data_directory;

也可以查询：

SELECT current_setting('data_directory');

10. 查看配置文件路径

SHOW config_file;

同时查看主要配置文件：

SELECT current_setting('config_file') AS config_file, current_setting('hba_file') AS hba_file, current_setting('ident_file') AS ident_file;

分别对应：

• postgresql.conf
• pg_hba.conf
• pg_ident.conf

二、数据库、模式与对象

11. 查看所有数据库

在psql中执行：

\l

SQL 方式：

SELECT datname, datdba::regrole AS owner, encoding, datcollate, datctype, datallowconn FROM pg_database ORDER BY datname;

12. 查看当前数据库大小

SELECT pg_size_pretty(pg_database_size(current_database()));

13. 查看所有数据库大小

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS database_size FROM pg_database WHERE datallowconn ORDER BY pg_database_size(datname) DESC;

14. 查看当前数据库中的模式

在psql中：

\dn

SQL 方式：

SELECT schema_name, schema_owner FROM information_schema.schemata ORDER BY schema_name;

15. 查看当前搜索路径

SHOW search_path;

search_path会影响未指定 Schema 的对象解析顺序。

16. 查看指定模式中的表

在psql中：

\dt public.*

SQL 方式：

SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;

17. 查看表结构

在psql中：

\d public.table_name

查看更完整的信息：

\d+ public.table_name

\d+可以显示字段、索引、约束、访问方法、表大小和存储参数等信息。

18. 查看视图

\dv

查看物化视图：

\dm

SQL 方式：

SELECT schemaname, viewname, viewowner FROM pg_views ORDER BY schemaname, viewname;

19. 查看函数和存储过程

\df

查看更详细的信息：

\df+

SQL 查询：

SELECT n.nspname AS schema_name, p.proname AS routine_name, pg_get_function_identity_arguments(p.oid) AS arguments, p.prokind FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY n.nspname, p.proname;

20. 查看扩展

\dx

SQL 方式：

SELECT extname, extversion, extnamespace::regnamespace AS schema_name FROM pg_extension ORDER BY extname;

三、连接与会话管理

21. 查看当前活动会话

SELECT pid, usename, datname, client_addr, application_name, state, backend_start, query_start, wait_event_type, wait_event, query FROM pg_stat_activity ORDER BY query_start NULLS LAST;

pg_stat_activity是 PostgreSQL 会话排查的核心视图。

22. 查看当前连接数

SELECT count(*) AS current_connections FROM pg_stat_activity;

23. 按数据库统计连接数

SELECT datname, count(*) AS connection_count FROM pg_stat_activity GROUP BY datname ORDER BY connection_count DESC;

24. 按用户统计连接数

SELECT usename, count(*) AS connection_count FROM pg_stat_activity GROUP BY usename ORDER BY connection_count DESC;

25. 按客户端地址统计连接数

SELECT client_addr, count(*) AS connection_count FROM pg_stat_activity GROUP BY client_addr ORDER BY connection_count DESC;

26. 查看最大连接数

SHOW max_connections;

查看为超级用户预留的连接数：

SHOW superuser_reserved_connections;

27. 查看连接使用率

SELECT count(*) AS current_connections, current_setting('max_connections')::int AS max_connections, round( count(*) * 100.0 / current_setting('max_connections')::int, 2 ) AS usage_percent FROM pg_stat_activity;

28. 查看正在执行的 SQL

SELECT pid, usename, datname, client_addr, now() - query_start AS running_time, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() ORDER BY query_start;

29. 查看执行超过 60 秒的 SQL

SELECT pid, usename, datname, client_addr, now() - query_start AS running_time, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '60 seconds' ORDER BY query_start;

30. 查看空闲连接

SELECT pid, usename, datname, client_addr, state, now() - state_change AS idle_time, query FROM pg_stat_activity WHERE state = 'idle' ORDER BY state_change;

31. 查看空闲事务

SELECT pid, usename, datname, client_addr, xact_start, now() - xact_start AS transaction_time, state, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start;

idle in transaction是 PostgreSQL 运维中必须重点关注的状态。会话虽然没有执行 SQL，但事务仍未结束，可能持有锁、阻止 Vacuum 清理垃圾版本，并导致表膨胀。

32. 取消正在执行的 SQL

SELECT pg_cancel_backend(12345);

pg_cancel_backend()只取消当前 SQL，一般不会断开数据库连接。

33. 终止数据库会话

SELECT pg_terminate_backend(12345);

终止连接后，该会话中的未提交事务会被回滚。

34. 批量取消长时间 SQL

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '30 minutes' AND pid <> pg_backend_pid();

生产环境不要直接执行。建议先将查询结果中的会话逐个确认，再决定是否取消。

35. 查看自己的后台进程 PID

SELECT pg_backend_pid();

四、事务、锁等待与阻塞

36. 查看长事务

SELECT pid, usename, datname, client_addr, xact_start, now() - xact_start AS transaction_age, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

37. 查看超过 10 分钟的事务

SELECT pid, usename, datname, client_addr, now() - xact_start AS transaction_age, state, query FROM pg_stat_activity WHERE xact_start < now() - interval '10 minutes' ORDER BY xact_start;

38. 查看当前锁

SELECT pid, locktype, relation::regclass AS relation, mode, granted, waitstart FROM pg_locks ORDER BY granted, pid;

39. 查看正在等待的锁

SELECT pid, locktype, relation::regclass AS relation, page, tuple, transactionid, mode, waitstart FROM pg_locks WHERE NOT granted ORDER BY waitstart;

40. 查看被谁阻塞

SELECT pid, pg_blocking_pids(pid) AS blocking_pids, wait_event_type, wait_event, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

41. 查看完整阻塞关系

SELECT blocked.pid AS blocked_pid, blocked.usename AS blocked_user, now() - blocked.query_start AS blocked_duration, blocked.query AS blocked_query, blocker.pid AS blocker_pid, blocker.usename AS blocker_user, now() - blocker.query_start AS blocker_duration, blocker.state AS blocker_state, blocker.query AS blocker_query FROM pg_stat_activity blocked CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid JOIN pg_stat_activity blocker ON blocker.pid = bpid ORDER BY blocked.query_start;

这条 SQL 可以直接建立等待会话与阻塞会话之间的关系。

42. 查看阻塞其他会话的进程

SELECT DISTINCT blocker.pid, blocker.usename, blocker.datname, blocker.client_addr, blocker.state, blocker.xact_start, blocker.query_start, blocker.query FROM pg_stat_activity blocked CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid JOIN pg_stat_activity blocker ON blocker.pid = bpid;

43. 终止阻塞源会话

SELECT pg_terminate_backend(12345);

终止前必须确认：

• 是否存在未提交事务；
• 回滚需要多长时间；
• 是否为关键业务连接；
• 是否会触发应用重试风暴；
• 是否还有更上游的阻塞源。

44. 查看预备事务

SELECT * FROM pg_prepared_xacts;

两阶段提交环境中，长期未完成的预备事务可能持续持有锁。

45. 查看数据库死锁数量

SELECT datname, deadlocks FROM pg_stat_database ORDER BY deadlocks DESC;

这里记录的是数据库启动以来或统计信息重置以来累计检测到的死锁数量。

五、SQL 性能与执行计划

46. 查看估算执行计划

EXPLAIN SELECT * FROM public.table_name WHERE id = 100;

EXPLAIN不会真正执行 SQL。

47. 查看实际执行计划

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM public.table_name WHERE id = 100;

它会实际执行 SQL，并显示：

• 实际耗时；
• 实际返回行数；
• 执行循环次数；
• Shared Buffer 命中；
• 磁盘读取；
• 临时文件读写。

对于UPDATE、DELETE和INSERT，执行EXPLAIN ANALYZE会真正修改数据。生产环境中应放在事务中验证，并在确认后回滚。

BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE public.table_name SET status = 1 WHERE id = 100; ROLLBACK;

48. 查看更完整的执行计划

EXPLAIN ( ANALYZE, BUFFERS, WAL, VERBOSE, SETTINGS, SUMMARY ) SELECT * FROM public.table_name WHERE id = 100;

49. 查看 JSON 格式执行计划

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM public.table_name WHERE id = 100;

JSON 格式更适合执行计划平台、自动化分析工具和程序解析。

50. 安装 pg_stat_statements

首先需要在配置文件中加入：

shared_preload_libraries = 'pg_stat_statements'

重启数据库后，在目标数据库创建扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

pg_stat_statements用于聚合记录 SQL 的执行次数、总耗时、平均耗时、返回行数和 I/O 等统计数据。该扩展需要在具体数据库中创建。

51. 查看总耗时最高的 SQL

SELECT queryid, calls, round(total_exec_time::numeric, 2) AS total_exec_ms, round(mean_exec_time::numeric, 2) AS avg_exec_ms, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

52. 查看平均耗时最高的 SQL

SELECT queryid, calls, round(mean_exec_time::numeric, 2) AS avg_exec_ms, round(max_exec_time::numeric, 2) AS max_exec_ms, rows, query FROM pg_stat_statements WHERE calls >= 10 ORDER BY mean_exec_time DESC LIMIT 20;

53. 查看执行次数最多的 SQL

SELECT queryid, calls, round(total_exec_time::numeric, 2) AS total_exec_ms, round(mean_exec_time::numeric, 2) AS avg_exec_ms, query FROM pg_stat_statements ORDER BY calls DESC LIMIT 20;

54. 查看读取数据块最多的 SQL

SELECT queryid, calls, shared_blks_read, shared_blks_hit, temp_blks_read, temp_blks_written, query FROM pg_stat_statements ORDER BY shared_blks_read DESC LIMIT 20;

55. 查看临时文件消耗最高的 SQL

SELECT queryid, calls, temp_blks_read, temp_blks_written, round(total_exec_time::numeric, 2) AS total_exec_ms, query FROM pg_stat_statements WHERE temp_blks_written > 0 ORDER BY temp_blks_written DESC LIMIT 20;

56. 查看 WAL 生成量最高的 SQL

SELECT queryid, calls, wal_records, wal_fpi, pg_size_pretty(wal_bytes::bigint) AS wal_size, query FROM pg_stat_statements ORDER BY wal_bytes DESC LIMIT 20;

适合分析批量更新、大事务以及 WAL 异常增长问题。

57. 重置 pg_stat_statements

SELECT pg_stat_statements_reset();

重置前应确认是否还需要保留原有 SQL 性能基线。

58. 查看数据库缓存命中率

SELECT datname, blks_read, blks_hit, round( blks_hit * 100.0 / NULLIF(blks_hit + blks_read, 0), 2 ) AS cache_hit_percent FROM pg_stat_database WHERE datname IS NOT NULL ORDER BY cache_hit_percent;

缓存命中率高并不代表 SQL 一定正常，还要结合执行计划、物理 I/O 延迟、工作集大小和访问模式判断。

59. 查看表扫描情况

SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch FROM pg_stat_user_tables ORDER BY seq_tup_read DESC LIMIT 20;

60. 查看统计信息最近更新时间

SELECT schemaname, relname, last_analyze, last_autoanalyze, analyze_count, autoanalyze_count FROM pg_stat_user_tables ORDER BY greatest(last_analyze, last_autoanalyze) NULLS FIRST;

六、表、索引与空间分析

61. 查看表总大小

SELECT pg_size_pretty( pg_total_relation_size('public.table_name') ) AS total_size;

总大小包括：

• 表数据；
• 索引；
• TOAST 数据；
• TOAST 索引。

62. 分别查看表和索引大小

SELECT pg_size_pretty( pg_relation_size('public.table_name') ) AS table_size, pg_size_pretty( pg_indexes_size('public.table_name') ) AS index_size, pg_size_pretty( pg_total_relation_size('public.table_name') ) AS total_size;

63. 查看最大的表

SELECT schemaname, relname, pg_size_pretty( pg_total_relation_size(relid) ) AS total_size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

64. 查看索引

在psql中：

\di public.*

SQL 方式：

SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename, indexname;

65. 查看表的所有索引定义

SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'table_name';

66. 查看索引使用情况

SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty( pg_relation_size(indexrelid) ) AS index_size FROM pg_stat_user_indexes ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

67. 查看未使用索引

SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan, pg_size_pretty( pg_relation_size(indexrelid) ) AS index_size FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

不能因为idx_scan = 0就直接删除索引，需要同时确认：

• 统计信息是否刚重置；
• 实例是否刚重启；
• 是否为唯一约束索引；
• 是否用于低频但关键的月末或年末任务；
• 是否被外键关联查询使用；
• 是否作为备用执行计划存在。

68. 查看重复索引定义

SELECT indrelid::regclass AS table_name, array_agg(indexrelid::regclass) AS indexes, pg_get_indexdef(indexrelid) AS index_definition FROM pg_index GROUP BY indrelid, indkey, indclass, indcollation, indexprs, indpred, pg_get_indexdef(indexrelid) HAVING count(*) > 1;

实际判断重复索引时，应重点比较索引列、顺序、排序方式、表达式和过滤条件，不能只比较索引名称。

69. 查看无效索引

SELECT n.nspname AS schema_name, t.relname AS table_name, i.relname AS index_name FROM pg_index x JOIN pg_class i ON i.oid = x.indexrelid JOIN pg_class t ON t.oid = x.indrelid JOIN pg_namespace n ON n.oid = t.relnamespace WHERE NOT x.indisvalid ORDER BY n.nspname, t.relname;

并发创建或重建索引失败后，可能留下无效索引。

70. 在线创建索引

CREATE INDEX CONCURRENTLY idx_table_name_col ON public.table_name(col_name);

CONCURRENTLY可以降低创建索引期间对业务 DML 的阻塞，但执行时间通常更长，资源消耗也可能更高，而且不能在显式事务块中执行。

71. 在线重建索引

REINDEX INDEX CONCURRENTLY public.idx_table_name_col;

普通REINDEX默认需要较强的表锁。在支持的版本中，生产环境通常优先评估REINDEX CONCURRENTLY。

72. 查看表的行数估算

SELECT relname, reltuples::bigint AS estimated_rows FROM pg_class WHERE oid = 'public.table_name'::regclass;

这是统计信息中的估算值，并不是精确行数。

精确统计需要执行：

SELECT count(*) FROM public.table_name;

对于超大表，count(*)可能执行很久并产生大量 I/O。

73. 查看表的活跃与死亡元组

SELECT schemaname, relname, n_live_tup, n_dead_tup, round( n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2 ) AS dead_tuple_percent FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

74. 查看表膨胀相关指标

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count, pg_size_pretty( pg_total_relation_size(relid) ) AS total_size FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

n_dead_tup只是估算值，不能单独作为表膨胀比例。准确判断还需结合pgstattuple、表文件大小、历史数据量和业务更新模型。

75. 查看 TOAST 表大小

SELECT c.oid::regclass AS table_name, c.reltoastrelid::regclass AS toast_table, pg_size_pretty( pg_total_relation_size(c.reltoastrelid) ) AS toast_size FROM pg_class c WHERE c.oid = 'public.table_name'::regclass;

七、Vacuum、Autovacuum 与统计信息

PostgreSQL 采用 MVCC。更新和删除通常不会立即覆盖原有行版本，而是产生可清理的死亡元组。因此，Vacuum 不是可有可无的“优化动作”，而是 PostgreSQL 日常运行机制的重要组成部分。官方文档也明确指出，PostgreSQL 数据库需要定期执行 Vacuum，大部分环境由 Autovacuum 自动完成。

76. 手工执行 Vacuum

VACUUM public.table_name;

普通VACUUM清理可回收的死亡元组，使空间可以被后续数据复用，通常不会把表文件空间归还给操作系统。

77. 执行 Vacuum Analyze

VACUUM (ANALYZE) public.table_name;

也可以写成：

VACUUM ANALYZE public.table_name;

它会先执行 Vacuum，再收集优化器统计信息。

78. 显示 Vacuum 详细输出

VACUUM (VERBOSE, ANALYZE) public.table_name;

79. 执行 Vacuum Full

VACUUM FULL public.table_name;

VACUUM FULL会重写整张表，将可释放空间归还给操作系统，但需要强锁，并可能产生较高的 I/O 和额外临时空间需求。生产大表上不能把它当作常规维护命令。

80. 单独收集统计信息

ANALYZE public.table_name;

指定字段：

ANALYZE public.table_name(col1, col2);

81. 提高字段统计信息目标值

ALTER TABLE public.table_name ALTER COLUMN col_name SET STATISTICS 1000;

然后重新收集：

ANALYZE public.table_name;

适用于数据分布倾斜、默认统计信息粒度不足，导致优化器行数估算明显失真的字段。

82. 查看 Autovacuum 配置

SELECT name, setting, unit, source FROM pg_settings WHERE name LIKE 'autovacuum%' ORDER BY name;

83. 查看正在执行的 Vacuum

SELECT pid, datname, relid::regclass AS table_name, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, num_dead_item_ids FROM pg_stat_progress_vacuum;

PostgreSQL 能够为VACUUM、ANALYZE、CREATE INDEX、CLUSTER、COPY和基础备份等操作提供进度视图。

84. 查看 Autovacuum Worker

SELECT pid, datname, usename, backend_type, query_start, wait_event_type, wait_event, query FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';

85. 查看事务年龄和冻结风险

SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY age(datfrozenxid) DESC;

查看表级冻结年龄：

SELECT n.nspname AS schema_name, c.relname AS table_name, age(c.relfrozenxid) AS xid_age FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'm') ORDER BY age(c.relfrozenxid) DESC LIMIT 20;

事务 ID 年龄过高可能导致数据库进入防止事务 ID 回卷的保护状态，是 PostgreSQL DBA 必须监控的指标。

八、WAL、检查点与归档

86. 查看当前 WAL 位置

SELECT pg_current_wal_lsn();

87. 查看 WAL 文件名

SELECT pg_walfile_name(pg_current_wal_lsn());

88. 计算两个 WAL 位置的差值

SELECT pg_size_pretty( pg_wal_lsn_diff( '0/5000000'::pg_lsn, '0/4000000'::pg_lsn ) );

89. 查看 WAL 配置

SELECT name, setting, unit, source FROM pg_settings WHERE name IN ( 'wal_level', 'max_wal_size', 'min_wal_size', 'wal_buffers', 'wal_compression', 'checkpoint_timeout', 'checkpoint_completion_target', 'archive_mode', 'archive_command' ) ORDER BY name;

90. 查看 WAL 统计信息

SELECT * FROM pg_stat_wal;

常见字段包括：

• wal_records
• wal_fpi
• wal_bytes
• wal_buffers_full
• wal_write
• wal_sync

91. 查看归档状态

SELECT * FROM pg_stat_archiver;

重点关注：

• archived_count
• failed_count
• last_archived_wal
• last_archived_time
• last_failed_wal
• last_failed_time

归档持续失败可能导致pg_wal目录不断增长。

92. 手工切换 WAL

SELECT pg_switch_wal();

通常用于：

• 测试归档链路；
• 触发当前 WAL 文件归档；
• 备份流程；
• 恢复验证。

不应在高频循环中随意执行。

九、流复制与复制槽

93. 查看当前节点是否处于恢复状态

SELECT pg_is_in_recovery();

返回：

• false：通常为主库；
• true：通常为物理备库。

94. 查看主库复制状态

SELECT pid, usename, application_name, client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;

官方文档说明，主库可以通过pg_stat_replication查看 WAL Sender；备库可以通过pg_stat_wal_receiver查看 WAL Receiver。

95. 计算各备库复制延迟

SELECT application_name, client_addr, state, sync_state, pg_size_pretty( pg_wal_lsn_diff( pg_current_wal_lsn(), replay_lsn ) ) AS replay_lag_bytes, replay_lag FROM pg_stat_replication ORDER BY pg_wal_lsn_diff( pg_current_wal_lsn(), replay_lsn ) DESC;

replay_lag是时间维度，LSN 差值是 WAL 字节维度，两者应该结合分析。

96. 查看备库 WAL 接收状态

SELECT * FROM pg_stat_wal_receiver;

97. 查看备库回放位置和延迟时间

SELECT pg_last_wal_receive_lsn() AS receive_lsn, pg_last_wal_replay_lsn() AS replay_lsn, pg_size_pretty( pg_wal_lsn_diff( pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn() ) ) AS receive_replay_gap, now() - pg_last_xact_replay_timestamp() AS replay_delay;

需要注意：当主库长时间没有事务提交时，时间差值可能持续增大，并不一定代表复制正在延迟。

98. 查看复制槽

SELECT slot_name, slot_type, database, active, active_pid, restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size FROM pg_replication_slots;

复制槽能够防止主库过早删除消费者尚未使用的 WAL。但如果复制槽长期不消费，主库可能持续保留 WAL，最终导致磁盘空间耗尽。

查看复制槽保留的 WAL 大小：

SELECT slot_name, slot_type, active, pg_size_pretty( pg_wal_lsn_diff( pg_current_wal_lsn(), restart_lsn ) ) AS retained_wal FROM pg_replication_slots WHERE restart_lsn IS NOT NULL ORDER BY pg_wal_lsn_diff( pg_current_wal_lsn(), restart_lsn ) DESC;

十、备份、恢复与权限管理

99. 使用 pg_dump 进行逻辑备份

备份单个数据库：

pg_dump \ -h 127.0.0.1 \ -p 5432 \ -U backup_user \ -F c \ -f appdb_$(date +%F).dump \ appdb

其中：

• -F c：使用 Custom 格式；
• -f：指定输出文件；
• Custom 格式支持通过pg_restore选择对象并行恢复。

并行备份需要使用 Directory 格式：

pg_dump \ -h 127.0.0.1 \ -p 5432 \ -U backup_user \ -F d \ -j 8 \ -f appdb_dir \ appdb

恢复 Custom 格式备份：

createdb \ -h 127.0.0.1 \ -p 5432 \ -U postgres \ appdb_restore

pg_restore \ -h 127.0.0.1 \ -p 5432 \ -U postgres \ -d appdb_restore \ -j 8 \ appdb_2026-07-17.dump

备份全局对象：

pg_dumpall \ -h 127.0.0.1 \ -p 5432 \ -U postgres \ --globals-only \ > globals_$(date +%F).sql

PostgreSQL 官方将备份方式概括为 SQL Dump、文件系统级备份和连续归档三类。

物理基础备份：

pg_basebackup \ -h 10.0.0.10 \ -p 5432 \ -U repl_user \ -D /backup/base_$(date +%F) \ -Fp \ -Xs \ -P \ -R

pg_basebackup可以对运行中的 PostgreSQL 集群创建基础备份，可用于时间点恢复，也可作为流复制备库的初始数据。

验证基础备份：

pg_verifybackup /backup/base_2026-07-17

pg_verifybackup会根据pg_basebackup生成的备份清单验证基础备份完整性。

100. 用户、角色与权限管理

查看所有角色：

\du

SQL 方式：

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolconnlimit FROM pg_roles ORDER BY rolname;

创建登录用户：

CREATE ROLE app_user LOGIN PASSWORD 'StrongPassword';

创建只读角色：

CREATE ROLE app_readonly NOLOGIN;

允许连接数据库：

GRANT CONNECT ON DATABASE appdb TO app_readonly;

授权使用 Schema：

GRANT USAGE ON SCHEMA public TO app_readonly;

授权读取现有表：

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

授权读取现有序列：

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;

配置以后新建表的默认权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

将只读角色授予具体用户：

GRANT app_readonly TO app_user;

查看表权限：

\dp public.table_name

查看用户成员关系：

SELECT member.rolname AS member_name, role.rolname AS granted_role FROM pg_auth_members m JOIN pg_roles role ON role.oid = m.roleid JOIN pg_roles member ON member.oid = m.member ORDER BY member.rolname, role.rolname;

修改密码：

ALTER ROLE app_user PASSWORD 'NewStrongPassword';

禁止登录：

ALTER ROLE app_user NOLOGIN;

限制连接数量：

ALTER ROLE app_user CONNECTION LIMIT 20;

删除用户：

DROP ROLE app_user;

删除前需要确认该用户是否拥有对象或仍被授予权限。

补充：PostgreSQL DBA 常用的 psql 命令

除了 SQL，DBA 还需要熟悉psql自带的反斜杠命令。

\l

查看数据库。

\c appdb

切换数据库。

\dn

查看 Schema。

\dt

查看表。

\d+ public.table_name

查看表详细结构。

\di

查看索引。

\dv

查看视图。

\dm

查看物化视图。

\df

查看函数。

\du

查看角色。

\dx

查看扩展。

\x

切换扩展显示模式，查看宽表结果时非常实用。

\timing on

显示 SQL 执行时间。

\watch 2

每两秒重复执行上一条 SQL，适合实时观察连接数、复制延迟、Vacuum 进度等指标。

\o output.txt

将查询结果输出到文件。

\copy public.table_name TO '/tmp/table.csv' CSV HEADER

通过客户端导出 CSV。

\q

退出psql。

PostgreSQL 故障排查的正确顺序

真正有价值的不是把这 100 条命令全部背下来，而是知道什么时候使用哪一类命令。

当 PostgreSQL 业务出现卡顿时，可以按照下面的顺序排查。

第一步：检查连接和正在执行的 SQL

重点查看：

SELECT * FROM pg_stat_activity;

确认是否存在：

• 连接数暴增；
• 长时间运行 SQL；
• 大量空闲连接；
• idle in transaction；
• 相同 SQL 集中并发执行；
• 明显异常的等待事件。

第二步：检查长事务和锁等待

重点查看：

SELECT * FROM pg_locks WHERE NOT granted;

以及：

SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

很多 PostgreSQL 卡顿问题并不是 SQL 本身执行慢，而是 SQL 在等待另外一个长事务释放锁。

第三步：检查 SQL 执行计划和历史负载

当前 SQL 使用：

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

历史 SQL 使用：

SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC;

既要看单次执行很慢的 SQL，也要看单次不慢但执行次数极高的 SQL。

第四步：检查死亡元组和 Autovacuum

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

如果存在大量死亡元组，还要继续判断：

• 是否有长事务阻止清理；
• Autovacuum 是否被关闭；
• Autovacuum 参数是否过于保守；
• 表级 Autovacuum 参数是否合理；
• 是否存在持续高频更新；
• 是否出现事务 ID 冻结风险。

第五步：检查 WAL 和复制

主库检查：

SELECT * FROM pg_stat_replication;

备库检查：

SELECT * FROM pg_stat_wal_receiver;

复制槽检查：

SELECT * FROM pg_replication_slots;

如果pg_wal目录持续增长，除了检查归档失败，还必须检查失效或长期不消费的复制槽。

第六步：再考虑参数和系统资源

只有确认连接、SQL、锁、事务、Vacuum、WAL 和复制状态后，才应该进一步检查：

• shared_buffers
• work_mem
• maintenance_work_mem
• effective_cache_size
• max_connections
• checkpoint_timeout
• max_wal_size
• autovacuum_max_workers
• autovacuum_vacuum_scale_factor

参数调整不能替代 SQL 优化，也不能解决长事务、锁等待和应用连接管理问题。

总结

PostgreSQL DBA 与其他数据库 DBA 最大的区别之一，是必须真正理解 MVCC、Vacuum、WAL 和事务可见性机制。

看到表空间增长，不能立即执行VACUUM FULL；看到查询慢，不能只想着加索引；看到备库延迟，也不能只盯着时间字段。很多现象背后，可能是一个长期未提交事务、一条数据分布估算错误的 SQL、一个停止消费的复制槽，或者一次没有及时完成的 Autovacuum。

这 100 条命令覆盖了 PostgreSQL 日常运维的大部分基础入口，但命令只是工具。一个成熟 DBA 的核心能力，仍然是根据会话、锁、事务、执行计划、统计信息和 WAL 之间的关系，建立完整的故障因果链。