备份、PITR 与 pg_stat 运维视图 - 面试题

#

★★★

1. PITR 的实现，PG 12+ 通过 recovery.signal/standby.signal 进入恢复模式，restore_command 配置在 postgresql.conf？

问题

请说明 PostgreSQL 12+ 时间点恢复（PITR）的实现，包括 recovery.signal/standby.signal 进入恢复模式以及 restore_command 的配置？

考察点

recovery.signal 与 standby.signal 的区别
restore_command 的作用
PITR 恢复流程

答案

PostgreSQL 12+ 中，创建 recovery.signal 文件会进入恢复模式（recovery mode），创建 standby.signal 文件则进入 standby 模式（持续恢复）。基础备份恢复进行 PITR 时，在数据目录放置 recovery.signal，设置为 recovery_target_time 等目标，并在 postgresql.conf 中配置 restore_command（如 'cp /archive/%f %p'）从归档目录拉取 WAL，恢复进程按归档 WAL 重放直到目标时间点，然后创建 recovery.done 结束恢复。standby.signal 用于备库持续恢复（流复制），不达到目标即停止。

解析

recovery.signal 与 standby.signal 是 PG12+ 的恢复入口标记者，替代了旧版的 recovery.conf。restore_command 负责从归档获取 WAL，是 PITR 能否恢复的关键。理解两种 signal 的区别对搭建恢复与备库至关重要。

示例代码

# postgresql.conf
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-06-01 12:00:00'

# 数据目录创建恢复标记
touch recovery.signal

#

★★★

2. PostgreSQL 物理备份，pg_basebackup 的应用？

问题

请说明 PostgreSQL 物理备份工具 pg_basebackup 的应用？

考察点

pg_basebackup 的功能
备份选项
与 PITR 配合

答案

pg_basebackup 是 PostgreSQL 官方物理备份工具，从主库在线创建一致性基础备份（拷贝整个数据目录），不会阻塞正常读写。常用选项：-Fp 输出为普通目录格式（或 -Ft 输出为 tar 包）、-Xs 流式收集 WAL、-R 自动生成备库配置（standby.signal + primary_conninfo）、-c fast 强制 checkpoint。pg_basebackup 产生的备份可作为备库基础，也可配合 WAL 归档实现 PITR。它是搭建流复制和 PITR 备份的基础。

解析

pg_basebackup 通过在备份开始时记录起点 LSN，保证备份与后续 WAL 的衔接一致性。它是最常用的物理备份方式，理解其选项才能正确搭建备库与恢复体系。

示例代码

pg_basebackup -h primary -U repl -D /backup/base -Fp -Xs -R
pg_basebackup -h primary -U repl -Ft -z -D /backup/base.tar.gz

#

★★★

3. PostgreSQL 逻辑备份，pg_dump 的格式（plain、custom、directory、tar）？

问题

请说明 pg_dump 的逻辑备份格式，包括 plain、custom、directory、tar 的区别？

考察点

pg_dump 的格式
各格式特点
恢复方式

答案

pg_dump 是逻辑备份工具，支持多种格式：plain（纯 SQL 文本，psql 直接恢复）、custom（自定义压缩格式，pg_restore 选择恢复、可并行）、directory（目录格式，每个表一个文件，支持并行恢复、压缩）、tar（tar 包格式，不压缩、用 pg_restore 恢复）。逻辑备份按对象导出 SQL/数据，可跨版本、跨平台，但耗时较长；custom 和 directory 格式支持 pg_restore 的灵活选择（指定表、并行）、支持压缩，是常用格式。

解析

格式选择影响备份的灵活性与恢复方式。plain 适合小库/迁移脚本，custom/directory 适合大库与需要选择性恢复的场景。逻辑备份与物理备份互补。

示例代码

pg_dump -Fc -f app.dump appdb          # custom
pg_dump -Fd -f dumpdir appdb           # directory
pg_restore -d appdb -j 4 app.dump      # 并行恢复

#

★★★

4. WAL 归档与 PITR 的协同？

问题

请说明 WAL 归档与 PITR（时间点恢复）如何协同工作？

考察点

WAL 归档的作用
PITR 恢复流程
基础备份与归档的关系

答案

PITR 需要"基础备份 + 归档 WAL"两部分。基础备份（如 pg_basebackup）提供恢复到某个时间点的数据库快照，归档 WAL 则记录基础备份之后的所有变更。恢复时，先用基础备份还原数据目录，再通过 restore_command 重放归档 WAL 到目标时间点。WAL 归档（archive_mode/archive_command）持续把完成的 WAL 段复制到归档存储，保证任意时间点都能恢复。二者协同实现 RPO 接近于零的灾难恢复。

解析

基础备份决定"最老可恢复点"，归档 WAL 决定"最新可恢复点"。WAL 归档越及时、越完整，PITR 覆盖范围越广。这是数据库灾备的核心。

#

★★★

5. PostgreSQL 备份的实现？

问题

请说明 PostgreSQL 备份的整体实现方案，包括物理备份、逻辑备份与工具链？

考察点

物理备份方案
逻辑备份方案
备份策略与工具

答案

PostgreSQL 备份分物理与逻辑两类。物理备份：pg_basebackup 做基础备份，配 WAL 归档（pg_receivewal 或 archive_command）实现 PITR；也可用 pgBackRest、pg_rman 等工具链管理。逻辑备份：pg_dump/pg_dumpall 导出对象与数据，适合迁移、小库、选择性恢复。生产环境通常采用"定期的物理基础备份 + 持续 WAL 归档"实现完善的 PITR，辅以逻辑备份做迁移与容灾。备份需定期验证可恢复性（恢复演练）。

解析

完善备份 = 物理基础备份 + WAL 归档 + 定期验证。工具链（pgBackRest）提供加密、压缩、增量、并行等增强。备份策略要兼顾 RPO/RTO 与成本。

#

★★★

6. pg_stat 系统视图族，pg_stat_activity、pg_stat_user_tables、pg_stat_user_indexes？

问题

请说明 pg_stat 系统视图族，包括 pg_stat_activity、pg_stat_user_tables、pg_stat_user_indexes 的用途？

考察点

pg_stat_activity 的用途
pg_stat_user_tables 的指标
pg_stat_user_indexes 的指标

答案

pg_stat_activity 显示当前会话/连接活动状态，包括 pid、state、query、xact_start、backend_xmin、wait_event 等，用于排查慢查询、长事务、锁等待、连接数。pg_stat_user_tables 统计用户表的累积活动，包括 seq_scan、idx_scan、n_tup_ins/upd/del、n_dead_tup、last_vacuum 等，用于分析表访问模式、死元组与膨胀。pg_stat_user_indexes 统计索引的 idx_scan、idx_tup_read、idx_tup_fetch 等，用于识别未使用索引（idx_scan 小）与索引有效性。

解析

pg_stat 视图族是 PostgreSQL 运维与调优的核心数据来源。三者分别覆盖"会话/查询"、"表级活动"、"索引级活动"，结合可定位性能瓶颈（慢查询、膨胀、无用索引）。

示例代码

SELECT pid, state, query, wait_event_type FROM pg_stat_activity;
SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables;
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;

#

★★★

7. pg_stat_replication 的复制状态监控？

问题

请说明如何使用 pg_stat_replication 监控复制状态？

考察点

pg_stat_replication 的字段
复制延迟评估
上下游状态

答案

pg_stat_replication 在主库上显示每个 walsender（备库/订阅端）的复制状态，关键字段：application_name、state（streaming/startup/backup）、client_addr、sync_state（sync/async/potential）、sent_lsn、write_lsn、flush_lsn、replay_lsn（备库应用位置）、write/flush/replay_lag（延迟）。通过比较 sent_lsn 与 replay_lsn 可评估备库落后量；sync_state 显示是否为同步复制。监控复制延迟与状态是保证高可用可靠性的关键。

解析

pg_stat_replication 是主库侧查看下游消费进度的视图。replay_lag 反映备库实际应用延迟，是判断"是否跟得上"的核心指标。结合备库侧 pg_stat_wal_receiver 可完整掌握复制链路。

示例代码

SELECT application_name, state, sync_state, replay_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag
FROM pg_stat_replication;

#

★★★

8. pg_stat_statements 扩展的安装与查询，SQL 指纹、统计信息？

问题

请说明 pg_stat_statements 扩展的安装与查询，包括 SQL 指纹和统计信息的使用？

考察点

pg_stat_statements 的安装
SQL 指纹
统计信息查询

答案

pg_stat_statements 是性能统计扩展，需把 pg_stat_statements 加入 shared_preload_libraries 并重启，再 CREATE EXTENSION pg_stat_statements。它按 SQL 指纹（SQL fingerprint，即去掉具体参数值的规范化文本）聚合统计：query、calls（执行次数）、total_exec_time、mean_exec_time、min/max_exec_time、rows、shared_blks_hit/read、sort_count 等。通过查询 pg_stat_statements 按 mean_exec_time 或 total_exec_time 排序，可快速定位最耗时的 SQL 和热点查询，是性能调优的核心工具。

解析

SQL 指纹让相同模板不同参数的语句聚合统计，便于分析热点。pg_stat_statements 是 DBA 性能分析的第一工具，配合 pg_stat_activity 定位具体执行。

示例代码

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

#

★★

9. 分区裁剪（Partition Pruning）的优化？

问题

请说明 PostgreSQL 分区裁剪（Partition Pruning）的优化机制？

考察点

分区裁剪的概念
裁剪的触发条件
优化效果

答案

分区裁剪（Partition Pruning）是查询在分区表上定位到符合条件的分区、跳过无关分区扫描的优化机制。当查询的 WHERE 条件能约束分区键（如 created_at = '2026-01-01' 或范围），优化器只扫描相关分区，避免全部分区扫描，显著减少 IO 与查询时间。裁剪可用静态参数（常量）在计划阶段裁剪，也可用绑定参数（PREPARE/参数化）在部分场景执行阶段裁剪。对时间序列分区表，按时间范围查询可精准裁剪到少数分区。

解析

分区裁剪是声明式分区的核心性能优势。裁剪生效的前提是 WHERE 条件基于分区键且形式可推断。启用 constraint_exclusion（旧版）与合理分区键设计能最大化裁剪效果。

示例代码

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
-- 仅扫描 2026-01 分区

#

★★

10. 分区的索引策略，全局索引 vs 局部索引？

问题

请说明 PostgreSQL 分区的索引策略，包括全局索引与局部索引？

考察点

局部索引机制
分区索引的自动创建
全局索引的差异

答案

PostgreSQL 声明式分区默认是"局部索引"——即每个分区各自有独立索引，父表上建索引会自动在子分区上创建对应索引（CREATE INDEX ON parent 会递归到各分区）。查询时通过分区裁剪定位到分区后使用该分区的局部索引。PostgreSQL 11+ 支持在被分区表上创建索引，并自动传播到分区；但 PG 不支持像其他数据库那样的"全局索引"（跨分区的单一索引），唯一约束需要包含分区键。因此设计时需考虑分区键与索引的唯一性约束匹配。

解析

局部索引是 PG 分区表的默认且推荐方式，配合分区裁剪性能良好。全局索引缺失意味着跨分区唯一性受限于分区键，需在分区键设计时考虑。

示例代码

CREATE INDEX idx_orders_created ON orders (created_at);  -- 自动在每个分区创建
CREATE UNIQUE INDEX uq_orders_id ON orders (id, created_at);  -- 唯一约束需含分区键

#

★★

11. pg_stat_database 的库级指标？

问题

请说明 pg_stat_database 提供的库级指标？

考察点

pg_stat_database 的字段
库级活动统计
监控用途

答案

pg_stat_database 按数据库提供统计指标，包括：numbackends（当前连接数）、xact_commit/xact_rollback（事务提交/回滚数）、blks_read/blks_hit（磁盘读/缓存命中，blks_hit 可用于计算缓存命中率）、tup_returned/tup_fetched/tup_inserted/tup_updated/tup_deleted（行操作）、deadlocks（死锁数）、conflicts（备库冲突数）、temp_files/temp_bytes（临时文件使用）、stats_reset（统计重置时间）。用于评估库级负载、缓存命中率、死锁与临时文件问题。

解析

pg_stat_database 是库级监控入口。缓存命中率（blks_hit/(blks_hit+blks_read)）与死锁、回滚率是评估库健康度的常用指标。

示例代码

SELECT datname, numbackends, xact_commit, xact_rollback,
       blks_hit, blks_read, temp_bytes, deadlocks
FROM pg_stat_database;

#

★★

12. 分区的 ATTACH/DETACH 操作？

问题

请说明 PostgreSQL 分区的 ATTACH/DETACH 操作？

考察点

ATTACH 分区
DETACH 分区
运维场景

答案

ATTACH 是把已有表挂接为分区（ALTER TABLE ... ATTACH PARTITION），需满足分区边界约束；DETACH 是把分区从分区表中摘除（ALTER TABLE ... DETACH PARTITION）。PG 12+ 支持 DETACH PARTITION CONCURRENTLY（在线、不阻塞写入，需两阶段）和 ATTACH PARTITION（PG 14+ 支持 CONCURRENTLY 异步）。这些操作常用于时间分区表的滚动维护：把旧分区 DETACH 转冷存储/归档，把新分区 ATTACH 上线。DETACH 后分区表成为独立表，可单独处理。

解析

ATTACH/DETACH 是分区表生命周期维护的核心。CONCURRENTLY 版本避免阻塞业务，适合在线维护。理解其约束与并发特性对构建分区归档方案重要。

示例代码

ALTER TABLE orders ATTACH PARTITION orders_2026
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
ALTER TABLE orders DETACH PARTITION orders_2020;
ALTER TABLE orders DETACH PARTITION orders_2020 CONCURRENTLY;

#

★★

13. 如何用 pg_stat_archiver 监控 WAL 归档进度与失败（archived_count、failed_count、last_archived_time）？

问题

请说明如何用 pg_stat_archiver 监控 WAL 归档进度与失败？

考察点

pg_stat_archiver 的字段
归档失败监控
归档进度评估

答案

pg_stat_archiver 提供归档统计：archived_count（已成功归档的 WAL 段数）、failed_count（失败次数）、last_archived_wal/last_archived_time（最近成功归档的文件与时间）、last_failed_wal/last_failed_time（最近失败的文件与时间）、stats_reset。通过监控 failed_count 增长与 last_failed_time 是否持续更新可判断归档是否失败；若 last_archived_time 长时间不更新而 failed_count 增长，说明归档卡住，需处理归档命令或存储问题，否则将阻塞主库 WAL 进度。

解析

归档失败是生产隐患，会阻塞主库。pg_stat_archiver 是判断归档健康度的直接视图，配合 WAL 产生速率监控可评估归档是否跟上。

示例代码

SELECT archived_count, failed_count, last_archived_time, last_failed_time
FROM pg_stat_archiver;

#

★★

14. pgBackRest 工具链的应用？

问题

请说明 pgBackRest 工具链的应用？

考察点

pgBackRest 的功能
备份与恢复
高级特性

答案

pgBackRest 是专业的 PostgreSQL 备份工具，提供完整备份、差异备份、增量备份，支持压缩、加密、并行、校验和、归档管理（异步 WAL 归档）、PITR、按需恢复恢复到指定点。它通过 stanza 管理备份，支持本地/远程存储（如 S3、NFS），有保留策略（自动清理与保留对齐）。相比手工脚本，pgBackRest 提供更可靠、可自动化、可监控的备份恢复体系，适合生产环境。

解析

pgBackRest 是 PostgreSQL 备份的现代化标准方案。其增量+归档+保留策略极大降低存储成本并保证 PITR 能力，是"生产级备份"的推荐工具。

示例代码

pgbackrest --stanza=app --type=full backup
pgbackrest --stanza=app --type=incr backup
pgbackrest --stanza=app restore --target-time '2026-06-01 12:00:00'

#

★

15. PostgreSQL 分区表在时间序列数据归档、历史分区 DETACH 转冷存储与保留策略中的应用？

问题

请说明 PostgreSQL 分区表在时间序列数据归档、历史分区转冷存储与保留策略中的应用？

考察点

时间分区表设计
分区 DETACH 转冷存储
保留策略

答案

时间序列数据（日志、监控、订单）常用 RANGE 分区按时间建分区表。数据按时间落入对应分区，查询按时间范围可分区裁剪。归档与保留策略：定期把旧分区 ALTER TABLE ... DETACH PARTITION 转成独立表，迁移到冷存储（如对象存储、低速盘）或归档库；达到保留期后 DROP 旧分区释放空间。配合 pg_cron 或 pg_partman 等自动化调度，实现新分区自动创建、旧分区自动归档/删除的滚动维护。

解析

时间分区表让大数据量的保留策略可操作化：分区作为"删除/归档"的原子单位，DETACH 后可离线归档，避免大表 DELETE 的昂贵开销。理解保留策略设计可显著降低运维成本。

示例代码

ALTER TABLE logs DETACH PARTITION logs_2024;
-- 归档日志_2024 后：
DROP TABLE logs_2024;

#

★

16. pg_verifybackup 与 pg_checksums 如何验证物理备份与数据文件的完整性？

问题

请说明 pg_verifybackup 与 pg_checksums 如何验证物理备份与数据文件的完整性？

考察点

pg_verifybackup 的用途
pg_checksums 的用途
完整性验证机制

答案

pg_verifybackup 用于验证 pg_basebackup 生成的备份，检查备份文件是否存在、大小与校验和是否匹配、备份清单（backup_manifest）的一致性，可提前发现备份损坏。pg_checksums 用于启用/校验数据文件的校验和（checksums），需在初始化时（initdb -k）或关闭时通过 pg_checksums --enable 启用，之后数据库每页写入时计算校验和，启动/读页时校验，可发现数据页被静默损坏。二者结合：pg_verifybackup 验证备份完整性，pg_checksums 验证线上数据文件完整性。

解析

数据完整性是可靠性的基础。pg_verifybackup 在恢复前验证备份，pg_checksums 在运行中检测静默损坏。两者是"备份可用、数据未坏"的保障。

示例代码

pg_verifybackup /backup/base
pg_checksums --enable -D /var/lib/pgsql/data
pg_checksums --check -D /var/lib/pgsql/data

#

★

17. pg_stat_bgwriter 与 pg_stat_checkpointer 如何反映 checkpoint 频率与缓冲写回活动？

问题

请说明 pg_stat_bgwriter 与 pg_stat_checkpointer 如何反映 checkpoint 频率与缓冲写回活动？

考察点

pg_stat_bgwriter 的指标
pg_stat_checkpointer 的指标
checkpoint 频率评估

答案

pg_stat_bgwriter（PG 旧版）统计后台写进程：buffers_clean、maxwritten_clean、buffers_backend、buffers_alloc 等，反映缓冲区的写回活动。pg_stat_checkpointer（PG 17+ 拆分）统计 checkpoint 相关：num_timed（定时 checkpoint 次数）、num_requested（请求 checkpoint 次数）、buffers_written（checkpoint 写回缓冲数）、checkpoint_write_time、checkpoint_sync_time。通过观察 checkpoint 次数与时间可评估 checkpoint 频率：num_timed 增长过快说明 max_wal_size 偏小或 checkpoint 频繁；checkpoint_sync_time 过高说明 fsync 开销大。

解析

checkpoint 频率与写回活动直接影响写入性能与恢复时间。pg_stat_checkpointer 的 num_timed/num_requested 占比与时间可指导 max_wal_size 调优。

示例代码

SELECT * FROM pg_stat_checkpointer;
SELECT * FROM pg_stat_bgwriter;

#

★

18. pg_stat_wal 的 wal_records/wal_bytes 如何用于评估 WAL 写入量与全页写比例？

问题

请说明 pg_stat_wal 的 wal_records/wal_bytes 如何用于评估 WAL 写入量与全页写比例？

考察点

pg_stat_wal 的字段
WAL 写入量评估
全页写比例

答案

pg_stat_wal 提供 WAL 统计：wal_records（WAL 记录数）、wal_fpi（full page images，全页写次数）、wal_bytes（WAL 写入字节数）、wal_buffers_full、wal_write 等。通过 wal_records 与 wal_bytes 可评估 WAL 写入总量与速率；wal_fpi/wal_records 或 wal_fpi 字节占比可评估全页写比例——全页写（checkpoint 后首个被修改页的完整页写入）在每次 checkpoint 后初次修改页时发生，占比较多会增加 WAL 量。监控全页写比例可判断 checkpoint 频率与 WAL 膨胀。

解析

pg_stat_wal 是 WAL 层面的监控视图。全页写比例高说明 checkpoint 频繁或写放大，需评估 max_wal_size 与 checkpoint 调优。理解 wal_fpi 可定位 WAL 异常增长。

示例代码

SELECT wal_records, wal_fpi, wal_bytes,
       pg_size_pretty(wal_bytes) AS wal_size
FROM pg_stat_wal;