PostgreSQL 备份与 PITR 时间点恢复实战

PostgreSQL 备份只做 pg_dump 是远远不够的：真正救命的是 PITR（Point-In-Time Recovery，时间点恢复）——把数据库精确回滚到「误删那条 DELETE 执行前一秒」。本文用可直接复制的命令，讲清逻辑备份、物理备份、WAL 归档三层策略，并完整演示一次 PITR 恢复。

一、先分清三层备份，别混用

生产事故里最常见的一句话是「我们每天都有备份」，然后发现那个备份只能恢复到凌晨三点，中间 8 小时的数据全丢。原因就是把逻辑备份当成了灾备方案。PostgreSQL 的备份能力其实分三层，各有各的用途，缺一不可。

类型	工具	粒度	能否 PITR	典型场景
逻辑备份	pg_dump / pg_dumpall	单表 / 单库	❌ 不能	迁移、跨版本升级、抽单表
物理全量备份	pg_basebackup	整个实例	✅ 配合 WAL	灾备基线、搭建从库
WAL 归档	archive_command	事务级	✅ 核心依赖	把基线往前「续播」到任意时刻

一句话总结三者关系：物理全量备份是存档点，WAL 归档是录像带，PITR 就是把录像带从存档点播放到你指定的那一帧。逻辑备份则是另一条线，适合搬迁和取单表，不参与 PITR。

二、逻辑备份：pg_dump 的正确姿势

逻辑备份导出的是 SQL 或归档格式，跨版本、跨平台友好。关键是别再用 pg_dump db > db.sql 这种裸文本了——用 -Fc（custom 格式）才能并行恢复、按表挑选恢复。

# 单库备份为 custom 格式（自带压缩，可并行恢复）
pg_dump -h 127.0.0.1 -U postgres -d appdb -Fc -f /backup/appdb_$(date +%F).dump

# 只备份结构（迁移前对比 schema 很有用）
pg_dump -h 127.0.0.1 -U postgres -d appdb --schema-only -f /backup/appdb_schema.sql

# 全实例（含角色、权限、表空间定义）
pg_dumpall -h 127.0.0.1 -U postgres --globals-only -f /backup/globals.sql

# 恢复：4 个并行任务，只恢复 orders 表
pg_restore -h 127.0.0.1 -U postgres -d appdb -j 4 -t orders /backup/appdb_2026-08-21.dump

注意两个细节：pg_dumpall --globals-only 必须单独跑，否则恢复后会发现角色和权限全丢；-j 并行只在 -Fc / -Fd 格式下生效。另外大表 dump 会持有较久的快照，建议避开业务高峰，必要时先排查慢查询压力，方法可参考PostgreSQL 慢查询优化：执行计划解读与索引调优实战。

三、开启 WAL 归档：PITR 的地基

没有 WAL 归档，就没有 PITR。WAL（Write-Ahead Log，预写日志）记录了每一次数据变更，只要把它们连续保存下来，就能从任意全量备份点重放到任意时刻。配置在 postgresql.conf，改完需要重启（wal_level 和 archive_mode 都不是热加载参数）。

# postgresql.conf
wal_level = replica              # 至少 replica，logical 也可
archive_mode = on                # 开启归档
archive_command = 'test ! -f /pgarchive/%f && cp %p /pgarchive/%f'
archive_timeout = 300            # 5 分钟强制切换一次 WAL，限制丢失窗口
max_wal_size = 4GB
wal_compression = on             # 减少归档体积

3.1 archive_command 的三条铁律

• 幂等且不覆盖：test ! -f 前置判断，防止同名 WAL 被覆盖导致归档链断裂。
• 成功才返回 0：返回非 0 时 PostgreSQL 会保留该 WAL 并不断重试，磁盘可能被撑满，必须配合监控。
• 归档目录必须异地：放在同一块数据盘上，等于没备份。生产建议 NFS、对象存储或独立备份机。

验证归档是否真的在工作，不要靠猜，跑一次强制切换即可：

-- 手动切换 WAL，观察归档目录是否出现新文件
SELECT pg_switch_wal();

-- 查看归档统计：失败次数应恒为 0
SELECT archived_count, last_archived_wal, failed_count, last_failed_wal
FROM pg_stat_archiver;

failed_count 一旦持续增长，说明归档命令挂了（多半是目录权限或磁盘满）。这个指标应该直接接进告警，它比「备份任务成功」的日志可靠得多。

四、物理全量备份：pg_basebackup

pg_basebackup 直接复制数据目录，产出的是可以直接启动的实例副本，也是 PITR 的起点。它走的是复制协议，所以需要一个带 REPLICATION 权限的角色，并在 pg_hba.conf 放行。

-- 创建专用备份角色
CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'StrongPwd!2026';

# pg_hba.conf 放行复制连接
# TYPE  DATABASE        USER      ADDRESS         METHOD
host    replication     repluser  10.0.0.0/24     scram-sha-256

# 全量物理备份：tar + gzip，流式带上备份期间产生的 WAL
pg_basebackup -h 10.0.0.11 -U repluser -D /backup/base_$(date +%F) \
  -Ft -z -Xs -P -c fast

# 参数含义
# -Ft   tar 格式（-Fp 为原样目录，便于直接启动）
# -z    gzip 压缩
# -Xs   stream 方式同步抓取备份期间的 WAL，保证备份自洽
# -P    显示进度
# -c fast  立即触发 checkpoint，不等自然检查点（缩短备份等待）

PostgreSQL 13 起提供 pg_verifybackup，能基于 backup_manifest 校验备份完整性。备份完不校验，等于薛定谔的备份：

# 校验 -Fp 目录格式备份（tar 格式需先解包）
pg_verifybackup /backup/base_2026-08-21
# 输出 backup successfully verified 才算通过

五、实战 PITR：回滚到误删前一秒

场景假设：2026-08-21 03:12:30，有人在生产执行了一条没带 WHERE 的 DELETE FROM orders。我们的目标是把库恢复到 03:12:00。前提是：昨天的 pg_basebackup 存在，且从那时到现在的 WAL 归档连续未断。

5.1 恢复步骤

# 1) 停库，并把当前数据目录改名保留（千万别直接删，它可能还有用）
systemctl stop postgresql
mv /var/lib/pgsql/17/data /var/lib/pgsql/17/data.broken

# 2) 从全量备份还原基线
mkdir -p /var/lib/pgsql/17/data && chown postgres:postgres /var/lib/pgsql/17/data
tar -xzf /backup/base_2026-08-20/base.tar.gz -C /var/lib/pgsql/17/data
chmod 700 /var/lib/pgsql/17/data

# 3) 声明这是一次恢复（PG12 起用 signal 文件，recovery.conf 已废弃）
touch /var/lib/pgsql/17/data/recovery.signal
chown postgres:postgres /var/lib/pgsql/17/data/recovery.signal

然后在还原出来的 postgresql.conf 末尾追加恢复目标参数：

# 从归档目录取 WAL 重放
restore_command = 'cp /pgarchive/%f %p'
# 恢复到这一刻（务必带时区，否则按服务器时区解释）
recovery_target_time = '2026-08-21 03:12:00+08'
# 到点后自动结束恢复并可写
recovery_target_action = 'promote'
# 精确到目标点之前（exclusive），默认 on 表示包含目标事务
recovery_target_inclusive = off

# 4) 启动，跟踪日志观察重放进度
systemctl start postgresql
tail -f /var/lib/pgsql/17/data/log/postgresql-*.log
# 看到 recovery stopping before commit of transaction ... 与 database system is ready 即成功

5.2 四种恢复目标，按场景选

时间点不是唯一选择。如果知道确切的事务号或 LSN，定位会更精准，尤其在同一秒内有大量提交时：

• recovery_target_time：按时间，最常用，也最容易因时区写错而翻车。
• recovery_target_xid：按事务 ID，配合 pg_waldump 从归档里找到那条 DELETE 的 xid，精度最高。
• recovery_target_lsn：按日志序列号，适合已从监控中拿到 LSN 的场景。
• recovery_target_name：按命名还原点，事前用 SELECT pg_create_restore_point('before_release_v3'); 打标，发版前必做。

想确认那条误操作到底发生在哪个 LSN，可以直接翻 WAL：

# 解析归档中的 WAL，过滤 DELETE 相关记录
pg_waldump /pgarchive/00000001000000050000003C | grep -i delete | head -20

六、自动化与保留策略

备份必须自动化，否则一定会在某个周末被忘掉。最简单的方案是 cron，团队协作场景也可以挂到 CI 上定时跑，做法参考GitHub Actions 实战：从零搭建 CI/CD 流水线里的 schedule 触发器。

# /etc/cron.d/pg-backup
# 每天 02:00 全量物理备份
0 2 * * * postgres pg_basebackup -h 127.0.0.1 -U repluser -D /backup/base_$(date +\%F) -Ft -z -Xs -c fast >> /var/log/pgbackup.log 2>&1
# 每天 03:00 清理 7 天前的全量备份与已无用的归档
0 3 * * * postgres find /backup -maxdepth 1 -name 'base_*' -mtime +7 -exec rm -rf {} \;

归档清理不能用 find 粗暴删除——必须保证最老的那个全量备份所需的 WAL 还在。正确做法是用官方工具，以「最老备份的起始 WAL」为界：

# 删除该 WAL 之前的所有归档（该文件名可从最老备份的 backup_label 中读到）
pg_archivecleanup /pgarchive 00000001000000050000002A

如果不想自己拼这些脚本，生产环境更推荐 pgBackRest 或 Barman：它们内置增量备份、并行压缩、保留策略和校验，把上面这一整套封装成了几条命令。自建脚本适合理解原理和小规模场景，规模上去后维护成本会明显高于工具化方案。

七、五个高频踩坑

• 只有备份没有演练：从未恢复过的备份不算备份。建议每季度做一次全流程 PITR 演练，并记录 RTO（恢复耗时）。
• 时区写错：recovery_target_time 不带时区，服务器按本地时区解释，可能整体偏 8 小时，恢复出一个错误的时间点。
• 归档目录与数据同盘：磁盘或机器一挂，基线和录像带一起没了。必须异地。
• 忘了备份角色与全局对象：只 dump 了业务库，恢复后应用连不上，因为角色和权限没恢复。
• 恢复完成后忘记重开归档：PITR 起来的新实例需要重新配置 archive_command 并做一次新的全量备份，否则新时间线（timeline）没有备份保护。

最后一条尤其容易被忽略：PITR promote 之后会产生新的时间线（如 00000002），旧的归档对新时间线只覆盖到分叉点。恢复成功的第一件事，是立刻重跑一次 pg_basebackup。

八、小结

一套合格的 PostgreSQL 备份体系，是「每日 pg_basebackup 全量 + 持续 WAL 归档 + 定期 pg_verifybackup 校验 + 季度恢复演练」的组合，逻辑备份则作为迁移与单表抽取的补充手段。判断标准很简单：能不能在 30 分钟内把库恢复到任意指定的那一秒。如果答案是「不确定」，那就该马上安排一次演练。

延伸阅读：想了解 PostgreSQL 与 SQL Server 在备份机制上的取舍差异，可看PostgreSQL 与 SQL Server 对比；数据库性能层面的调优思路，则可参考MySQL 索引底层原理：B+树、覆盖索引与最左前缀中关于执行计划的分析方法。