PostgreSQL DBA 应该掌握的 100 条命令

原创三笠丶 2026-07-21

513

前言

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 之间的关系，建立完整的故障因果链。

postgresql 墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者