PostgreSQL 常用的 100 条命令

PostgreSQL 常用的 100 条命令

前言

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

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

一、实例与基础信息

01 查看 PostgreSQL 版本

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

SELECT version();

也可以只查看版本号：

SHOW server_version;

02 查看服务器版本号

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

SELECT current_setting('server_version');

03 查看当前数据库

SELECT current_database();

04 查看当前用户

SELECT current_user;

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

SELECT
	current_user,
	session_user;

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

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

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

SELECT
	inet_server_addr(),
	inet_server_port();

06 查看客户端地址和端口

SELECT
	inet_client_addr(),
	inet_client_port();

07 查看数据库启动时间

SELECT pg_postmaster_start_time();

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

SELECT
	now() - pg_postmaster_start_time() AS uptime;

08 查看当前时间和时区

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

09 查看数据目录

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 查看当前活动会话

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

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;

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 查看空闲事务

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

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;

32 取消正在执行的 SQL

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

SELECT pg_cancel_backend(12345);

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 查看完整阻塞关系

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

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;

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 不会真正执行 SQL。

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

47 查看实际执行计划

它会实际执行 SQL，并显示：实际耗时、实际返回行数、执行循环次数、Shared Buffer 命中、磁盘读取、临时文件读写。

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

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

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 格式执行计划

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

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

50 安装 pg_stat_statements

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

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

shared_preload_libraries = 'pg_stat_statements'

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

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

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

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;

57 重置 pg_stat_statements

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

SELECT pg_stat_statements_reset();

58 查看数据库缓存命中率

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

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;

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 查看表总大小

总大小包括：表数据、索引、TOAST 数据、TOAST 索引。

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

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 查看未使用索引

不能因为 idx_scan = 0 就直接删除索引，需要同时确认：统计信息是否刚重置、实例是否刚重启、是否为唯一约束索引、是否用于低频但关键的月末或年末任务、是否被外键关联查询使用、是否作为备用执行计划存在。

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;

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 在线创建索引

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

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

71 在线重建索引

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

REINDEX INDEX CONCURRENTLY public.idx_table_name_col;

72 查看表的行数估算

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

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

精确统计需要执行，超大表慎用：

SELECT count(*) FROM public.table_name;

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 查看表膨胀相关指标

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

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;

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 清理可回收的死亡元组，使空间可以被后续数据复用，通常不会把表文件空间归还给操作系统。

VACUUM public.table_name;

77 执行 Vacuum Analyze

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

VACUUM (ANALYZE) public.table_name;

等价写法：

VACUUM ANALYZE public.table_name;

78 显示 Vacuum 详细输出

VACUUM (VERBOSE, ANALYZE) public.table_name;

79 执行 Vacuum Full

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

VACUUM FULL public.table_name;

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

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

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;

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 查看事务年龄和冻结风险

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

库级事务年龄：

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;

八、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 统计信息

常见字段：wal_records、wal_fpi、wal_bytes、wal_buffers_full、wal_write、wal_sync

SELECT * FROM pg_stat_wal;

91 查看归档状态

重点关注：archived_count、failed_count、last_archived_wal、last_archived_time、last_failed_wal、last_failed_time。归档持续失败可能导致 pg_wal 目录不断增长。

SELECT * FROM pg_stat_archiver;

92 手工切换 WAL

适用场景：测试归档链路、触发当前 WAL 文件归档、备份流程、恢复验证。不应在高频循环中随意执行。

SELECT pg_switch_wal();

九、流复制与复制槽

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

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

SELECT pg_is_in_recovery();

94 查看主库复制状态

主库通过 pg_stat_replication 查看 WAL Sender；备库通过 pg_stat_wal_receiver 查看 WAL Receiver。

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;

95 计算各备库复制延迟

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

SELECT
	application_name,
	client_addr,
	state,
	sync_state,
	pg_size_pretty(
		pg_wal_lsn_diff(