PostgreSQL 备份与恢复深度实战：逻辑备份、物理备份、WAL 归档与 PITR

引言

「数据库没备份 = 数据随时可能归零」。PostgreSQL 提供两类互补的备份手段：逻辑备份（pg_dump 导出数据与 schema，跨版本/跨平台灵活）与物理备份（pg_basebackup + WAL 归档，支持精确到秒的时间点恢复 PITR）。真正的生产级方案是两者结合：定期逻辑备份兜底 + 连续 WAL 归档保证 RPO 逼近零。

本文系统覆盖备份原理、工具用法、WAL 归档与恢复流程、备份策略矩阵（RPO/RTO 权衡）、恢复演练清单，以及误删/误改/坏盘三大场景的实战恢复步骤。

前置：/postgres-high-availability/（流复制与故障转移，与备份互为补充）、/postgres-monitoring-diagnostics/（归档与备份监控）。

目录

• 1. 备份的本质：RPO 与 RTO
• 2. 逻辑备份：pg_dump 与 pg_restore
• 3. 逻辑备份的增量与选择性策略
• 4. 物理备份：pg_basebackup
• 5. WAL 归档：连续备份的关键
• 6. PITR 时间点恢复
• 7. 备份策略矩阵与自动调度
• 8. 恢复演练清单
• 9. 三大故障场景实战恢复
• 10. 速查表与一句话记忆
• 延伸阅读

1. 备份的本质：RPO 与 RTO

任何备份方案都用两个指标衡量：

三种备份手段的定位：

记忆：RPO 由「备份频率」决定，RTO 由「恢复熟练度」决定——两者都要靠演练来验证，而不是文档上的数字。

2. 逻辑备份：pg_dump 与 pg_restore

pg_dump 导出数据库的逻辑结构（schema + 数据）：

# 单库逻辑备份（自定义格式，推荐——支持选择性恢复与压缩）
pg_dump -h dbhost -U user -Fc -f /backup/db_20260928.dump mydb

# 纯 SQL 格式（可读，但恢复不灵活）
pg_dump -h dbhost -U user -f /backup/db_20260928.sql mydb

# 只导 schema / 只导数据
pg_dump --schema-only mydb
pg_dump --data-only mydb

pg_restore 恢复（自定义格式专用）：

# 恢复到目标库
createdb -h dbtarget mydb_new
pg_restore -h dbtarget -d mydb_new /backup/db_20260928.dump

# 只恢复某个表（选择性恢复）
pg_restore -h dbtarget -d mydb_new -t orders /backup/db_20260928.dump

# 单事务恢复（要么全成要么全败）
pg_restore --single-transaction -d mydb_new /backup/db_20260928.dump

关键认知：

提示：逻辑备份是「数据」备份，不备份索引/统计信息等物理状态——恢复到新库后要重建统计（ANALYZE）。

3. 逻辑备份的增量与选择性策略

pg_dump 不支持原生增量，但可用「分区/分表 + 时间窗口」实现类增量：

# 场景：日志/事件表按天分区，只备份最近变化的分区
pg_dump -Fc -t events_20260928 mydb -f /backup/events_20260928.dump
pg_dump -Fc -t events_20260929 mydb -f /backup/events_20260929.dump

选择性备份的常见场景：

□ 大而低频的表：每周全量，不参与每日增量
□ 配置/字典表：变化极小，低频备份
□ 敏感表：单独加密后存储
□ 归档分区：进入只读后一次备份，之后只备份索引

逻辑备份 vs 物理备份选型：

实战建议：大库主用物理备份 + WAL，逻辑备份作为「跨版本/选择性」的补充，两者并存。

4. 物理备份：pg_basebackup

pg_basebackup 生成数据库目录的一致快照，可与 WAL 归档结合：

# 基础用法：带 WAL 的完整备份
pg_basebackup -h primary -U backup_user -D /backup/base_20260928 \
  -X stream -P -c fast

# 指定格式（pg_basebackup 要求数据目录为空或不存在）
pg_basebackup -h primary -U backup_user -D /backup/base_20260928 \
  -X stream -Ft -z        # tar 格式 + gzip 压缩

关键参数：

物理备份的用途：PITR 基础镜像、搭建从库、快速恢复整库。它是目录级一致快照，恢复后必须结合 WAL 重放到目标时间点。

5. WAL 归档：连续备份的关键

WAL（Write-Ahead Log）记录了每次变更。开启连续归档后，把 WAL 文件持续搬运到归档目录，就能从「基础备份 + 全部 WAL」重放到任意时刻。

开启归档（postgresql.conf）：

wal_level = replica            # 或 logical（逻辑复制需要）
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 60           # 空闲也强制归档（保证 WAL 连续性）

归档目录结构：

/archive/
  ├── 000000010000000000000001   # 每个 WAL 段（16MB）
  ├── 000000010000000000000002
  └── ...

验证归档是否在跑：

-- 查询当前 WAL 与归档状态
SELECT pg_current_wal_lsn(), pg_current_wal_insert_lsn();
SELECT * FROM pg_stat_archiver;   -- archived_count 增长 = 正常

记忆：WAL 归档 = 把「从备份时刻之后的所有变更」保存下来——没有归档，物理备份只能恢复到备份瞬间，无法时间点恢复。

6. PITR 时间点恢复

核心流程：基础备份 → 恢复到备份时刻 → 重放 WAL → 停在目标时间点。

# 1. 恢复基础备份
tar -xzf /backup/base_20260928.tar.gz -C /data
chown -R postgres:postgres /data

# 2. 在数据目录创建恢复配置（现代 PostgreSQL 用 restore_command）
#    postgresql.conf 中（或通过 recovery.signal）：
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-28 14:30:00+08'   # 精确时间点
# recovery_target_timeline = 'latest'

# 3. 放置信号文件并启动，进入恢复，到达目标自动停止
touch /data/recovery.signal
pg_ctl start -D /data
# 恢复完成后 recovery.signal 被移除，进入正常主库模式

recovery_target 三种形式：

recovery_target_time = '...'   按时间点
recovery_target_lsn = '0/...'   按 LSN（更精确）
recovery_target_xid = '...'     按事务 ID

PITR 两大注意：

□ 基础备份必须与归档 WAL 版本匹配（同 major version）
□ 恢复期间数据库只读；到达目标后需把 timeline 标记为最新才能对外提供

7. 备份策略矩阵与自动调度

生产推荐分层策略：

自动调度（crontab 示例）：

# 每天 02:00 物理备份，每周日 03:00 逻辑备份
0 2 * * *  /opt/scripts/backup_base.sh >> /var/log/backup.log 2>&1
0 3 * * 0  /opt/scripts/backup_dump.sh >> /var/log/backup.log 2>&1

备份脚本要点：

□ 备份目录按日期分目录，保留策略自动清理过期
□ 备份完成后校验文件完整（pg_restore -l / pg_basebackup 退出码）
□ 把备份与数据库放不同磁盘/机房
□ 记录备份日志 + 告警（备份失败要立刻发现）

8. 恢复演练清单

「备份存在 ≠ 能恢复」——定期演练是唯一验证手段。

演练项目：

□ 完整恢复：从基础备份 + 归档 WAL 恢复到一台空机器
□ 时间点恢复：恢复到指定时间（验证 target 参数生效）
□ 单表恢复：pg_restore -t 选择性恢复
□ 建从库：从最新备份搭建流复制从库
□ 计时：记录每个场景的 RTO，检验 SLA

演练模板记录：

场景：误删 orders 表
步骤：pg_restore -t orders 上次逻辑备份 → 缺失数据再 PITR 到删除前
RTO：实测 X 分钟
结果：通过 / 发现不足（记录改进）

心法：每季度一次完整演练，演练中发现的问题就是生产事故的预演。

9. 三大故障场景实战恢复

场景一：误删某表

# 若最近逻辑备份里有 → 单表恢复最快
pg_restore -h db -d mydb -t orders /backup/weekly.dump

# 若无 → PITR 到删除前时刻，单独导出该表

场景二：误 UPDATE/DELETE 大片数据

方案：PITR 到事故前 → 导出受影响数据 → 导回主库
（避免全库回滚，只取需要的部分）

场景三：数据目录损坏/主机故障

# 新机器重建
tar -xzf 最新物理备份 -C /data
touch /data/recovery.signal
# restore_command 指向归档 → 重放到故障前
pg_ctl start
# 验证数据 → 移除 recovery.signal → 对外服务

通用恢复心法：

1. 先备份现状（别再破坏现场）
2. 确定 RPO 目标：丢多少可接受
3. 选恢复路径：逻辑备份够则用逻辑，不够则 PITR
4. 恢复到独立实例验证，再切流量
5. 全程记录，复盘改进

10. 速查表与一句话记忆

一句话记忆：逻辑备份保灵活、物理备份保速度、WAL 归档保「任意时刻」——三层互补才叫完整备份；而只有经过演练的恢复流程，才配叫生产级数据安全。

延伸阅读

• /postgres-high-availability/ — 流复制与故障转移（备份的架构兄弟）
• /postgres-monitoring-diagnostics/ — 归档与备份监控告警
• /postgres-logical-replication/ — 逻辑复制与 CDC 的另一种数据流动
• /postgres-security-production/ — 备份数据的加密与权限
• [[postgresql]] — PostgreSQL 数据库专题