PostgreSQL 运维命令 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 版本

SQL

1SELECT version();

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

也可以只查看版本号：

SQL

1SHOW server_version;

2. 查看服务器版本号

SQL

1SELECT current_setting('server_version');

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

3. 查看当前数据库

SQL

1SELECT current_database();

4. 查看当前用户

SQL

1SELECT current_user;

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

SQL

1SELECT
2    current_user,
3    session_user;

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

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

SQL

1SELECT
2    inet_server_addr(),
3    inet_server_port();

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

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

SQL

1SELECT
2    inet_client_addr(),
3    inet_client_port();

7. 查看数据库启动时间

SQL

1SELECT pg_postmaster_start_time();

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

SQL

1SELECT
2    now() - pg_postmaster_start_time() AS uptime;

8. 查看当前时间和时区

SQL

1SELECT
2    now(),
3    current_timestamp,
4    current_setting('TimeZone');

9. 查看数据目录

SQL

1SHOW data_directory;

也可以查询：

SQL

1SELECT current_setting('data_directory');

10. 查看配置文件路径

SQL

1SHOW config_file;

同时查看主要配置文件：

SQL

1SELECT
2    current_setting('config_file') AS config_file,
3    current_setting('hba_file') AS hba_file,
4    current_setting('ident_file') AS ident_file;

分别对应：

postgresql.conf
pg_hba.conf
pg_ident.conf

二、数据库、模式与对象

11. 查看所有数据库

在 psql 中执行：

1\l

SQL 方式：

SQL

1SELECT
2    datname,
3    datdba::regrole AS owner,
4    encoding,
5    datcollate,
6    datctype,
7    datallowconn
8FROM pg_database
9ORDER BY datname;

12. 查看当前数据库大小

SQL

1SELECT pg_size_pretty(pg_database_size(current_database()));

13. 查看所有数据库大小

SQL

1SELECT
2    datname,
3    pg_size_pretty(pg_database_size(datname)) AS database_size
4FROM pg_database
5WHERE datallowconn
6ORDER BY pg_database_size(datname) DESC;

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

在 psql 中：

1\dn

SQL 方式：

SQL

1SELECT
2    schema_name,
3    schema_owner
4FROM information_schema.schemata
5ORDER BY schema_name;

15. 查看当前搜索路径

SQL

1SHOW search_path;

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

16. 查看指定模式中的表

在 psql 中：

1\dt public.*

SQL 方式：

SQL

1SELECT
2    schemaname,
3    tablename,
4    tableowner
5FROM pg_tables
6WHERE schemaname = 'public'
7ORDER BY tablename;

17. 查看表结构

在 psql 中：

1\d public.table_name

查看更完整的信息：

1\d+ public.table_name

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

18. 查看视图

1\dv

查看物化视图：

1\dm

SQL 方式：

SQL

1SELECT
2    schemaname,
3    viewname,
4    viewowner
5FROM pg_views
6ORDER BY schemaname, viewname;

19. 查看函数和存储过程

1\df

查看更详细的信息：

1\df+

SQL 查询：

SQL

1SELECT
2    n.nspname AS schema_name,
3    p.proname AS routine_name,
4    pg_get_function_identity_arguments(p.oid) AS arguments,
5    p.prokind
6FROM pg_proc p
7JOIN pg_namespace n
8  ON n.oid = p.pronamespace
9WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
10ORDER BY n.nspname, p.proname;

20. 查看扩展

1\dx

SQL 方式：

SQL

1SELECT
2    extname,
3    extversion,
4    extnamespace::regnamespace AS schema_name
5FROM pg_extension
6ORDER BY extname;

三、连接与会话管理

21. 查看当前活动会话

SQL

1SELECT
2    pid,
3    usename,
4    datname,
5    client_addr,
6    application_name,
7    state,
8    backend_start,
9    query_start,
10    wait_event_type,
11    wait_event,
12    query
13FROM pg_stat_activity
14ORDER BY query_start NULLS LAST;

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

22. 查看当前连接数

SQL

1SELECT count(*) AS current_connections
2FROM pg_stat_activity;

23. 按数据库统计连接数

SQL

1SELECT
2    datname,
3    count(*) AS connection_count
4FROM pg_stat_activity
5GROUP BY datname
6ORDER BY connection_count DESC;

24. 按用户统计连接数

SQL

1SELECT
2    usename,
3    count(*) AS connection_count
4FROM pg_stat_activity
5GROUP BY usename
6ORDER BY connection_count DESC;

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

SQL

1SELECT
2    client_addr,
3    count(*) AS connection_count
4FROM pg_stat_activity
5GROUP BY client_addr
6ORDER BY connection_count DESC;

26. 查看最大连接数

SQL

1SHOW max_connections;

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

SQL

1SHOW superuser_reserved_connections;

27. 查看连接使用率

SQL

1SELECT
2    count(*) AS current_connections,
3    current_setting('max_connections')::int AS max_connections,
4    round(
5        count(*) * 100.0 /
6        current_setting('max_connections')::int,
7        2
8    ) AS usage_percent
9FROM pg_stat_activity;

28. 查看正在执行的 SQL

SQL

1SELECT
2    pid,
3    usename,
4    datname,
5    client_addr,
6    now() - query_start AS running_time,
7    wait_event_type,
8    wait_event,
9    query
10FROM pg_stat_activity
11WHERE state = 'active'
12  AND pid <> pg_backend_pid()
13ORDER BY query_start;

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

SQL

1SELECT
2    pid,
3    usename,
4    datname,
5    client_addr,
6    now() - query_start AS running_time,
7    wait_event_type,
8    wait_event,
9    query
10FROM pg_stat_activity
11WHERE state = 'active'
12  AND query_start < now() - interval '60 seconds'
13ORDER BY query_start;

30. 查看空闲连接

SQL

1SELECT
2    pid,
3    usename,
4    datname,
5    client_addr,
6    state,
7    now() - state_change AS idle_time,
8    query
9FROM pg_stat_activity
10WHERE state = 'idle'
11ORDER BY state_change;

31. 查看空闲事务

SQL

1SELECT
2    pid,
3    usename,
4    datname,
5    client_addr,
6    xact_start,
7    now() - xact_start AS transaction_time,
8    state,
9    query
10FROM pg_stat_activity
11WHERE state = 'idle in transaction'
12ORDER BY xact_start;

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

32. 取消正在执行的 SQL

SQL

1SELECT pg_cancel_backend(12345);

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

33. 终止数据库会话

SQL

1SELECT pg_terminate_backend(12345);

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

34. 批量取消长时间 SQL

SQL

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

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

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

SQL

1SELECT pg_backend_pid();

四、事务、锁等待与阻塞

36. 查看长事务

SQL

1SELECT
2    pid,
3    usename,
4    datname,
5    client_addr,
6    xact_start,
7    now() - xact_start AS transaction_age,
8    state,
9    wait_event_type,
10    wait_event,
11    query
12FROM pg_stat_activity
13WHERE xact_start IS NOT NULL
14ORDER BY xact_start;

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

SQL

1SELECT
2    pid,
3    usename,
4    datname,
5    client_addr,
6    now() - xact_start AS transaction_age,
7    state,
8    query
9FROM pg_stat_activity
10WHERE xact_start < now() - interval '10 minutes'
11ORDER BY xact_start;

38. 查看当前锁

SQL

1SELECT
2    pid,
3    locktype,
4    relation::regclass AS relation,
5    mode,
6    granted,
7    waitstart
8FROM pg_locks
9ORDER BY granted, pid;

39. 查看正在等待的锁

SQL

1SELECT
2    pid,
3    locktype,
4    relation::regclass AS relation,
5    page,
6    tuple,
7    transactionid,
8    mode,
9    waitstart
10FROM pg_locks
11WHERE NOT granted
12ORDER BY waitstart;

40. 查看被谁阻塞

SQL

1SELECT
2    pid,
3    pg_blocking_pids(pid) AS blocking_pids,
4    wait_event_type,
5    wait_event,
6    query
7FROM pg_stat_activity
8WHERE cardinality(pg_blocking_pids(pid)) > 0;

41. 查看完整阻塞关系

SQL

1SELECT
2    blocked.pid AS blocked_pid,
3    blocked.usename AS blocked_user,
4    now() - blocked.query_start AS blocked_duration,
5    blocked.query AS blocked_query,
6    blocker.pid AS blocker_pid,
7    blocker.usename AS blocker_user,
8    now() - blocker.query_start AS blocker_duration,
9    blocker.state AS blocker_state,
10    blocker.query AS blocker_query
11FROM pg_stat_activity blocked
12CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid
13JOIN pg_stat_activity blocker
14  ON blocker.pid = bpid
15ORDER BY blocked.query_start;

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

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

SQL

1SELECT DISTINCT
2    blocker.pid,
3    blocker.usename,
4    blocker.datname,
5    blocker.client_addr,
6    blocker.state,
7    blocker.xact_start,
8    blocker.query_start,
9    blocker.query
10FROM pg_stat_activity blocked
11CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid
12JOIN pg_stat_activity blocker
13  ON blocker.pid = bpid;

43. 终止阻塞源会话

SQL

1SELECT pg_terminate_backend(12345);

终止前必须确认：

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

44. 查看预备事务

SQL

1SELECT *
2FROM pg_prepared_xacts;

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

45. 查看数据库死锁数量

SQL

1SELECT
2    datname,
3    deadlocks
4FROM pg_stat_database
5ORDER BY deadlocks DESC;

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

五、SQL 性能与执行计划

46. 查看估算执行计划

SQL

1EXPLAIN
2SELECT *
3FROM public.table_name
4WHERE id = 100;

EXPLAIN 不会真正执行 SQL。

47. 查看实际执行计划

SQL

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

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

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

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

SQL

1BEGIN;
2
3EXPLAIN (ANALYZE, BUFFERS)
4UPDATE public.table_name
5SET status = 1
6WHERE id = 100;
7
8ROLLBACK;

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

SQL

1EXPLAIN (
2    ANALYZE,
3    BUFFERS,
4    WAL,
5    VERBOSE,
6    SETTINGS,
7    SUMMARY
8)
9SELECT *
10FROM public.table_name
11WHERE id = 100;

49. 查看 JSON 格式执行计划

SQL

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

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

50. 安装 pg_stat_statements

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

CONF

1shared_preload_libraries = 'pg_stat_statements'

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

SQL

1CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

51. 查看总耗时最高的 SQL

SQL

1SELECT
2    queryid,
3    calls,
4    round(total_exec_time::numeric, 2) AS total_exec_ms,
5    round(mean_exec_time::numeric, 2) AS avg_exec_ms,
6    rows,
7    query
8FROM pg_stat_statements
9ORDER BY total_exec_time DESC
10LIMIT 20;

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

SQL

1SELECT
2    queryid,
3    calls,
4    round(mean_exec_time::numeric, 2) AS avg_exec_ms,
5    round(max_exec_time::numeric, 2) AS max_exec_ms,
6    rows,
7    query
8FROM pg_stat_statements
9WHERE calls >= 10
10ORDER BY mean_exec_time DESC
11LIMIT 20;

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

SQL

1SELECT
2    queryid,
3    calls,
4    round(total_exec_time::numeric, 2) AS total_exec_ms,
5    round(mean_exec_time::numeric, 2) AS avg_exec_ms,
6    query
7FROM pg_stat_statements
8ORDER BY calls DESC
9LIMIT 20;

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

SQL

1SELECT
2    queryid,
3    calls,
4    shared_blks_read,
5    shared_blks_hit,
6    temp_blks_read,
7    temp_blks_written,
8    query
9FROM pg_stat_statements
10ORDER BY shared_blks_read DESC
11LIMIT 20;

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

SQL

1SELECT
2    queryid,
3    calls,
4    temp_blks_read,
5    temp_blks_written,
6    round(total_exec_time::numeric, 2) AS total_exec_ms,
7    query
8FROM pg_stat_statements
9WHERE temp_blks_written > 0
10ORDER BY temp_blks_written DESC
11LIMIT 20;

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

SQL

1SELECT
2    queryid,
3    calls,
4    wal_records,
5    wal_fpi,
6    pg_size_pretty(wal_bytes::bigint) AS wal_size,
7    query
8FROM pg_stat_statements
9ORDER BY wal_bytes DESC
10LIMIT 20;

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

57. 重置 pg_stat_statements

SQL

1SELECT pg_stat_statements_reset();

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

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

SQL

1SELECT
2    datname,
3    blks_read,
4    blks_hit,
5    round(
6        blks_hit * 100.0 /
7        NULLIF(blks_hit + blks_read, 0),
8        2
9    ) AS cache_hit_percent
10FROM pg_stat_database
11WHERE datname IS NOT NULL
12ORDER BY cache_hit_percent;

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

59. 查看表扫描情况

SQL

1SELECT
2    schemaname,
3    relname,
4    seq_scan,
5    seq_tup_read,
6    idx_scan,
7    idx_tup_fetch
8FROM pg_stat_user_tables
9ORDER BY seq_tup_read DESC
10LIMIT 20;

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

SQL

1SELECT
2    schemaname,
3    relname,
4    last_analyze,
5    last_autoanalyze,
6    analyze_count,
7    autoanalyze_count
8FROM pg_stat_user_tables
9ORDER BY greatest(last_analyze, last_autoanalyze) NULLS FIRST;

六、表、索引与空间分析

61. 查看表总大小

SQL

1SELECT
2    pg_size_pretty(
3        pg_total_relation_size('public.table_name')
4    ) AS total_size;

总大小包括：

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

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

SQL

1SELECT
2    pg_size_pretty(
3        pg_relation_size('public.table_name')
4    ) AS table_size,
5    pg_size_pretty(
6        pg_indexes_size('public.table_name')
7    ) AS index_size,
8    pg_size_pretty(
9        pg_total_relation_size('public.table_name')
10    ) AS total_size;

63. 查看最大的表

SQL

1SELECT
2    schemaname,
3    relname,
4    pg_size_pretty(
5        pg_total_relation_size(relid)
6    ) AS total_size
7FROM pg_stat_user_tables
8ORDER BY pg_total_relation_size(relid) DESC
9LIMIT 20;

64. 查看索引

在 psql 中：

1\di public.*

SQL 方式：

SQL

1SELECT
2    schemaname,
3    tablename,
4    indexname,
5    indexdef
6FROM pg_indexes
7WHERE schemaname = 'public'
8ORDER BY tablename, indexname;

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

SQL

1SELECT
2    indexname,
3    indexdef
4FROM pg_indexes
5WHERE schemaname = 'public'
6  AND tablename = 'table_name';

66. 查看索引使用情况

SQL

1SELECT
2    schemaname,
3    relname AS table_name,
4    indexrelname AS index_name,
5    idx_scan,
6    idx_tup_read,
7    idx_tup_fetch,
8    pg_size_pretty(
9        pg_relation_size(indexrelid)
10    ) AS index_size
11FROM pg_stat_user_indexes
12ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

67. 查看未使用索引

SQL

1SELECT
2    schemaname,
3    relname AS table_name,
4    indexrelname AS index_name,
5    idx_scan,
6    pg_size_pretty(
7        pg_relation_size(indexrelid)
8    ) AS index_size
9FROM pg_stat_user_indexes
10WHERE idx_scan = 0
11ORDER BY pg_relation_size(indexrelid) DESC;

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

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

68. 查看重复索引定义

SQL

1SELECT
2    indrelid::regclass AS table_name,
3    array_agg(indexrelid::regclass) AS indexes,
4    pg_get_indexdef(indexrelid) AS index_definition
5FROM pg_index
6GROUP BY
7    indrelid,
8    indkey,
9    indclass,
10    indcollation,
11    indexprs,
12    indpred,
13    pg_get_indexdef(indexrelid)
14HAVING count(*) > 1;

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

69. 查看无效索引

SQL

1SELECT
2    n.nspname AS schema_name,
3    t.relname AS table_name,
4    i.relname AS index_name
5FROM pg_index x
6JOIN pg_class i
7  ON i.oid = x.indexrelid
8JOIN pg_class t
9  ON t.oid = x.indrelid
10JOIN pg_namespace n
11  ON n.oid = t.relnamespace
12WHERE NOT x.indisvalid
13ORDER BY n.nspname, t.relname;

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

70. 在线创建索引

SQL

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

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

71. 在线重建索引

SQL

1REINDEX INDEX CONCURRENTLY public.idx_table_name_col;

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

72. 查看表的行数估算

SQL

1SELECT
2    relname,
3    reltuples::bigint AS estimated_rows
4FROM pg_class
5WHERE oid = 'public.table_name'::regclass;

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

精确统计需要执行：

SQL

1SELECT count(*)
2FROM public.table_name;

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

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

SQL

1SELECT
2    schemaname,
3    relname,
4    n_live_tup,
5    n_dead_tup,
6    round(
7        n_dead_tup * 100.0 /
8        NULLIF(n_live_tup + n_dead_tup, 0),
9        2
10    ) AS dead_tuple_percent
11FROM pg_stat_user_tables
12ORDER BY n_dead_tup DESC;

74. 查看表膨胀相关指标

SQL

1SELECT
2    schemaname,
3    relname,
4    n_live_tup,
5    n_dead_tup,
6    last_vacuum,
7    last_autovacuum,
8    vacuum_count,
9    autovacuum_count,
10    pg_size_pretty(
11        pg_total_relation_size(relid)
12    ) AS total_size
13FROM pg_stat_user_tables
14ORDER BY n_dead_tup DESC;

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

75. 查看 TOAST 表大小

SQL

1SELECT
2    c.oid::regclass AS table_name,
3    c.reltoastrelid::regclass AS toast_table,
4    pg_size_pretty(
5        pg_total_relation_size(c.reltoastrelid)
6    ) AS toast_size
7FROM pg_class c
8WHERE c.oid = 'public.table_name'::regclass;

七、Vacuum、Autovacuum 与统计信息

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

76. 手工执行 Vacuum

SQL

1VACUUM public.table_name;

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

77. 执行 Vacuum Analyze

SQL

1VACUUM (ANALYZE) public.table_name;

也可以写成：

SQL

1VACUUM ANALYZE public.table_name;

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

78. 显示 Vacuum 详细输出

SQL

1VACUUM (VERBOSE, ANALYZE) public.table_name;

79. 执行 Vacuum Full

SQL

1VACUUM FULL public.table_name;

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

80. 单独收集统计信息

SQL

1ANALYZE public.table_name;

指定字段：

SQL

1ANALYZE public.table_name(col1, col2);

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

SQL

1ALTER TABLE public.table_name
2ALTER COLUMN col_name
3SET STATISTICS 1000;

然后重新收集：

SQL

1ANALYZE public.table_name;

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

82. 查看 Autovacuum 配置

SQL

1SELECT
2    name,
3    setting,
4    unit,
5    source
6FROM pg_settings
7WHERE name LIKE 'autovacuum%'
8ORDER BY name;

83. 查看正在执行的 Vacuum

SQL

1SELECT
2    pid,
3    datname,
4    relid::regclass AS table_name,
5    phase,
6    heap_blks_total,
7    heap_blks_scanned,
8    heap_blks_vacuumed,
9    index_vacuum_count,
10    num_dead_item_ids
11FROM pg_stat_progress_vacuum;

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

84. 查看 Autovacuum Worker

SQL

1SELECT
2    pid,
3    datname,
4    usename,
5    backend_type,
6    query_start,
7    wait_event_type,
8    wait_event,
9    query
10FROM pg_stat_activity
11WHERE backend_type = 'autovacuum worker';

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

SQL

1SELECT
2    datname,
3    age(datfrozenxid) AS xid_age
4FROM pg_database
5ORDER BY age(datfrozenxid) DESC;

查看表级冻结年龄：

SQL

1SELECT
2    n.nspname AS schema_name,
3    c.relname AS table_name,
4    age(c.relfrozenxid) AS xid_age
5FROM pg_class c
6JOIN pg_namespace n
7  ON n.oid = c.relnamespace
8WHERE c.relkind IN ('r', 'm')
9ORDER BY age(c.relfrozenxid) DESC
10LIMIT 20;

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

八、WAL、检查点与归档

86. 查看当前 WAL 位置

SQL

1SELECT pg_current_wal_lsn();

87. 查看 WAL 文件名

SQL

1SELECT pg_walfile_name(pg_current_wal_lsn());

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

SQL

1SELECT pg_size_pretty(
2    pg_wal_lsn_diff(
3        '0/5000000'::pg_lsn,
4        '0/4000000'::pg_lsn
5    )
6);

89. 查看 WAL 配置

SQL

1SELECT
2    name,
3    setting,
4    unit,
5    source
6FROM pg_settings
7WHERE name IN (
8    'wal_level',
9    'max_wal_size',
10    'min_wal_size',
11    'wal_buffers',
12    'wal_compression',
13    'checkpoint_timeout',
14    'checkpoint_completion_target',
15    'archive_mode',
16    'archive_command'
17)
18ORDER BY name;

90. 查看 WAL 统计信息

SQL

1SELECT *
2FROM pg_stat_wal;

常见字段包括：

wal_records
wal_fpi
wal_bytes
wal_buffers_full
wal_write
wal_sync

91. 查看归档状态

SQL

1SELECT *
2FROM pg_stat_archiver;

重点关注：

archived_count
failed_count
last_archived_wal
last_archived_time
last_failed_wal
last_failed_time

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

92. 手工切换 WAL

SQL

1SELECT pg_switch_wal();

通常用于：

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

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

九、流复制与复制槽

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

SQL

1SELECT pg_is_in_recovery();

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

94. 查看主库复制状态

SQL

1SELECT
2    pid,
3    usename,
4    application_name,
5    client_addr,
6    state,
7    sync_state,
8    sent_lsn,
9    write_lsn,
10    flush_lsn,
11    replay_lsn,
12    write_lag,
13    flush_lag,
14    replay_lag
15FROM pg_stat_replication;

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

95. 计算各备库复制延迟

SQL

1SELECT
2    application_name,
3    client_addr,
4    state,
5    sync_state,
6    pg_size_pretty(
7        pg_wal_lsn_diff(
8            pg_current_wal_lsn(),
9            replay_lsn
10        )
11    ) AS replay_lag_bytes,
12    replay_lag
13FROM pg_stat_replication
14ORDER BY
15    pg_wal_lsn_diff(
16        pg_current_wal_lsn(),
17        replay_lsn
18    ) DESC;

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

96. 查看备库 WAL 接收状态

SQL

1SELECT *
2FROM pg_stat_wal_receiver;

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

SQL

1SELECT
2    pg_last_wal_receive_lsn() AS receive_lsn,
3    pg_last_wal_replay_lsn() AS replay_lsn,
4    pg_size_pretty(
5        pg_wal_lsn_diff(
6            pg_last_wal_receive_lsn(),
7            pg_last_wal_replay_lsn()
8        )
9    ) AS receive_replay_gap,
10    now() - pg_last_xact_replay_timestamp() AS replay_delay;

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

98. 查看复制槽

SQL

1SELECT
2    slot_name,
3    slot_type,
4    database,
5    active,
6    active_pid,
7    restart_lsn,
8    confirmed_flush_lsn,
9    wal_status,
10    safe_wal_size
11FROM pg_replication_slots;

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

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

SQL

1SELECT
2    slot_name,
3    slot_type,
4    active,
5    pg_size_pretty(
6        pg_wal_lsn_diff(
7            pg_current_wal_lsn(),
8            restart_lsn
9        )
10    ) AS retained_wal
11FROM pg_replication_slots
12WHERE restart_lsn IS NOT NULL
13ORDER BY
14    pg_wal_lsn_diff(
15        pg_current_wal_lsn(),
16        restart_lsn
17    ) DESC;

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

99. 使用 pg_dump 进行逻辑备份

备份单个数据库：

Bash

1pg_dump \
2  -h 127.0.0.1 \
3  -p 5432 \
4  -U backup_user \
5  -F c \
6  -f appdb_$(date +%F).dump \
7  appdb

其中：

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

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

Bash

1pg_dump \
2  -h 127.0.0.1 \
3  -p 5432 \
4  -U backup_user \
5  -F d \
6  -j 8 \
7  -f appdb_dir \
8  appdb

恢复 Custom 格式备份：

Bash

1createdb \
2  -h 127.0.0.1 \
3  -p 5432 \
4  -U postgres \
5  appdb_restore

Bash

1pg_restore \
2  -h 127.0.0.1 \
3  -p 5432 \
4  -U postgres \
5  -d appdb_restore \
6  -j 8 \
7  appdb_2026-07-17.dump

备份全局对象：

Bash

1pg_dumpall \
2  -h 127.0.0.1 \
3  -p 5432 \
4  -U postgres \
5  --globals-only \
6  > globals_$(date +%F).sql

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

物理基础备份：

Bash

1pg_basebackup \
2  -h 10.0.0.10 \
3  -p 5432 \
4  -U repl_user \
5  -D /backup/base_$(date +%F) \
6  -Fp \
7  -Xs \
8  -P \
9  -R

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

验证基础备份：

Bash

1pg_verifybackup /backup/base_2026-07-17

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

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

查看所有角色：

1\du

SQL 方式：

SQL

1SELECT
2    rolname,
3    rolsuper,
4    rolcreaterole,
5    rolcreatedb,
6    rolcanlogin,
7    rolreplication,
8    rolconnlimit
9FROM pg_roles
10ORDER BY rolname;

创建登录用户：

SQL

1CREATE ROLE app_user
2LOGIN
3PASSWORD 'StrongPassword';

创建只读角色：

SQL

1CREATE ROLE app_readonly NOLOGIN;

允许连接数据库：

SQL

1GRANT CONNECT
2ON DATABASE appdb
3TO app_readonly;

授权使用 Schema：

SQL

1GRANT USAGE
2ON SCHEMA public
3TO app_readonly;

授权读取现有表：

SQL

1GRANT SELECT
2ON ALL TABLES IN SCHEMA public
3TO app_readonly;

授权读取现有序列：

SQL

1GRANT SELECT
2ON ALL SEQUENCES IN SCHEMA public
3TO app_readonly;

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

SQL

1ALTER DEFAULT PRIVILEGES
2IN SCHEMA public
3GRANT SELECT ON TABLES
4TO app_readonly;

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

SQL

1GRANT app_readonly TO app_user;

查看表权限：

1\dp public.table_name

查看用户成员关系：

SQL

1SELECT
2    member.rolname AS member_name,
3    role.rolname AS granted_role
4FROM pg_auth_members m
5JOIN pg_roles role
6  ON role.oid = m.roleid
7JOIN pg_roles member
8  ON member.oid = m.member
9ORDER BY member.rolname, role.rolname;

修改密码：

SQL

1ALTER ROLE app_user
2PASSWORD 'NewStrongPassword';

禁止登录：

SQL

1ALTER ROLE app_user NOLOGIN;

限制连接数量：

SQL

1ALTER ROLE app_user CONNECTION LIMIT 20;

删除用户：

SQL

1DROP ROLE app_user;

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

补充：PostgreSQL DBA 常用的 psql 命令

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

1\l

查看数据库。

1\c appdb

切换数据库。

1\dn

查看 Schema。

1\dt

查看表。

1\d+ public.table_name

查看表详细结构。

1\di

查看索引。

1\dv

查看视图。

1\dm

查看物化视图。

1\df

查看函数。

1\du

查看角色。

1\dx

查看扩展。

1\x

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

1\timing on

显示 SQL 执行时间。

1\watch 2

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

1\o output.txt

将查询结果输出到文件。

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

通过客户端导出 CSV。

1\q

退出 psql。

PostgreSQL 故障排查的正确顺序

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

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

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

重点查看：

SQL

1SELECT *
2FROM pg_stat_activity;

确认是否存在：

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

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

重点查看：

SQL

1SELECT *
2FROM pg_locks
3WHERE NOT granted;

以及：

SQL

1SELECT
2    pid,
3    pg_blocking_pids(pid),
4    query
5FROM pg_stat_activity
6WHERE cardinality(pg_blocking_pids(pid)) > 0;

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

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

当前 SQL 使用：

SQL

1EXPLAIN (ANALYZE, BUFFERS)
2SELECT ...;

历史 SQL 使用：

SQL

1SELECT *
2FROM pg_stat_statements
3ORDER BY total_exec_time DESC;

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

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

SQL

1SELECT
2    relname,
3    n_live_tup,
4    n_dead_tup,
5    last_autovacuum
6FROM pg_stat_user_tables
7ORDER BY n_dead_tup DESC;

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

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

第五步：检查 WAL 和复制

主库检查：

SQL

1SELECT *
2FROM pg_stat_replication;

备库检查：

SQL

1SELECT *
2FROM pg_stat_wal_receiver;

复制槽检查：

SQL

1SELECT *
2FROM 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 运维命令 100 条 - DBA 学习之路