PostgreSQL 物理备份与时间恢复（PITR）：一次 basebackup 到任意时间点的完整链路

有回隔壁组一个开发手滑，在正式库上跑了句 UPDATE orders SET status='x'（忘了 WHERE），三百万行的订单状态全没了。他第一反应是 pg_dump 最近有备份吗？有，但那是昨晚的——中间一整天的交易全没了。这种时候能救命的只有 PITR：把库恢复到「他敲下回车前一秒」。

逻辑备份（pg_dump / pg_dumpall）导出来的是 SQL 或可见数据，恢复只能整库或整表回退到备份那一刻，中间发生的变更一律拿不回来。物理备份不一样：它直接拷数据文件，再配合 WAL（预写日志）连续归档，就能把库「重放」到任意时间点。库过百 G 之后，物理备份的速度和可靠性也远好于逻辑导出。

下面这套我在好几台 PG 12/15/16 上跑过，路径以 RHEL 系（含 Alibaba Cloud Linux）的 /var/lib/pgsql/16/data 为例，Debian/Ubuntu 把 /var/lib/postgresql/16/main 对应换一下即可。

原理先说清，不然命令记不住

PG 每改一行，不是直接写数据文件，而是先写进 WAL。数据文件是异步刷盘的。这意味着：只要从某个一致性的「基础备份」开始，把之后产生的所有 WAL 按序重放，就能精确还原到任意时刻的状态。基础备份用 pg_basebackup 拿，WAL 用归档命令持续收。

三个角色：

base backup：某一刻数据文件的一致性快照。
WAL archive：基础备份之后所有的 WAL 段，连续不断。
恢复目标：recovery_target_time 或 recovery_target_lsn，告诉 PG 重放到哪停。

少一样都做不了 PITR：有基础备份没 WAL，只能恢复到备份时刻；有 WAL 没基础备份，重放无从谈起。

第一步：建一个专用备份账号

别用 superuser 跑备份。建个只有复制和登录权限的角色：

CREATE ROLE backup WITH LOGIN REPLICATION PASSWORD '换成强密码';

REPLICATION 权限是 pg_basebackup 和流复制必须的，但它不等于 superuser，权限面小很多。然后在 pg_hba.conf 放行这个角色连复制：

# 放允许的来源段，本地就写 127.0.0.1/32
host  replication  backup  127.0.0.1/32  scram-sha-256
host  replication  backup  10.0.0.0/24   scram-sha-256

改完 SELECT pg_reload_conf(); 或者 systemctl reload postgresql 让配置生效。密码别明文写在脚本里，用 ~/.pgpass 存，权限设 600：

127.0.0.1:5432:replication:backup:换成强密码

第二步：打开 WAL 归档

基础备份之前，归档必须已经在跑——不然基础备份开始之后到归档就绪之间的 WAL 就断了，PITR 链不完整。改 postgresql.conf：

wal_level = replica            # 低于 replica 不记足够信息，PG10+ 默认就是 replica
archive_mode = on              # on / always / off，备库想也归档用 always
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'   # 关键行
archive_timeout = 300          # 5分钟强制切一段，避免低峰期 WAL 不动导致恢复点滞后

archive_command 里 %p 是 WAL 段在 PG 里的路径，%f 是文件名。test ! -f 那句是为了幂等：同一个段如果已经归档过就跳过，避免重复拷贝盖掉。cp 成功返回 0，PG 才认为这段归档好了；返回非 0 它会一直重试，直到成功，所以命令写错（比如目录不存在）会让 WAL 在 pg_wal/ 里越堆越多，最后把数据盘写爆——归档目录一定要提前建好并确认权限归 postgres：

mkdir -p /archive
chown postgres:postgres /archive
chmod 700 /archive

改完配置 systemctl restart postgresql（wal_level 和 archive_mode 都是需要重启的参数）。验证归档真的在动：

SELECT * FROM pg_stat_archiver;

看 archived_count 在涨、last_archived_wal 有值，就说明链路通了。线上我更推荐把归档推到异地或对象存储，本地 /archive 只是演示。用 rsync 推远程的写法：

archive_command = 'rsync -a %p backup@10.0.0.9:/archive/%f && test ! -f /archive/%f && cp %p /archive/%f'

注意异地网络抖动可能让 rsync 返回非 0，PG 会重试，这没问题；但别让命令「假成功」，确保真正落盘才返回 0。

第三步：跑 pg_basebackup

归档确认在跑之后，再拿基础备份：

pg_basebackup \
  -h 127.0.0.1 -p 5432 -U backup \
  -D /backup/pgbase_$(date +%F) \
  -Ft -z -P -Xs -R

flag 逐个说：

-Ft：打成 tar 包，比plain格式好搬运；多表空间会生成多个 tar。
-z：tar 包同时压缩，省空间。
-P：显示进度。
-Xs：流复制方式把备份期间的 WAL 一起传过来（standalone 备份也靠它，不依赖归档命令）。
-R：自动在恢复时写入 primary_conninfo 等连接信息——做备库时有用，纯 PITR 恢复其实不需要它，但加上无害。

跑完 /backup/pgbase_2026-09-09/ 下会有 base.tar.gz（主表空间）和可能的 pg_wal.tar.gz（用了 -Xs 时）。把这个目录整体拷贝到异地或备份服务器，保留策略我一般留最近两份基础备份 + 它们所需的全部 WAL。

一个常见的坑：pg_basebackup 跑的时候数据库在写，-Xs 已经保证了备份一致性，但如果你没用 -Xs 也没开归档，基础备份就废了。二者至少占一个。

第四步：真的恢复——把它拉回出事前一秒

假设今天 14:32 那句误 UPDATE 发生了，我们要恢复到 14:31:50。

先停库、保现场：

systemctl stop postgresql
# 旧数据目录别直接删，改名留着，恢复失败还能回退
mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data.failed
mkdir -p /var/lib/pgsql/16/data
chown postgres:postgres /var/lib/pgsql/16/data
chmod 700 /var/lib/pgsql/16/data

解压基础备份回去：

# 用备份当天的那份
tar -xf /backup/pgbase_2026-09-09/base.tar.gz -C /var/lib/pgsql/16/data
# 如果有表空间 tar，按 tablespace_map 指示解到对应位置

写恢复配置。PG 12 之后不再用 recovery.conf，而是把参数写进 postgresql.auto.conf 并在数据目录放一个 recovery.signal 文件：

cat >> /var/lib/pgsql/16/data/postgresql.auto.conf <<'EOF'
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-09 14:31:50+08'
recovery_target_inclusive = true
recovery_target_timeline = 'latest'
EOF
touch /var/lib/pgsql/16/data/recovery.signal

参数含义：

restore_command：从归档取 WAL 的命令，和基础备份时的 archive_command 方向相反。
recovery_target_time：重放到这个时间点停。想精确到事务可以改用 recovery_target_lsn（从 pg_waldump 里找）。
recovery_target_inclusive = true：包含这一时刻的事务；如果误删就发生在这一刻，设 false 更稳，停在它之前。
recovery_target_timeline = 'latest'：恢复到最新时间线，多轮 PITR 时避免卡在旧时间线。

启动，PG 会进入恢复模式，把基础备份之后、目标时间之前的 WAL 全重放完，然后自动把 recovery.signal 改名为 recovery.done，并以读写模式打开：

systemctl start postgresql
tail -f /var/lib/pgsql/16/data/log/*.log
# 看到 "database system is ready to accept connections" 且没再提 recovery，就成了

进去确认确实退出了恢复态：

SELECT pg_is_in_recovery();   -- 应该返回 f

这时查 orders 表，那三百万行应该还是出事前的状态。验证无误后，再把 data.failed 这个旧目录择机删掉（别急着删，留几天兜底）。

找不到准确时间？用 pg_waldump 翻 WAL

如果你不确定该恢复到哪一秒，或者想精确到某条事务，pg_waldump 能把 WAL 翻译成可读记录：

# 看某段 WAL 里发生了什么
pg_waldump /archive/000000010000000000000003 | less
# 按时间窗过滤
pg_waldump -s "2026-09-09 14:30:00" -e "2026-09-09 14:33:00" /archive/000000010000000000000003

输出里能看到事务 ID、操作类型、表 OID。配合 recovery_target_xid 恢复到「某个事务之前」，比靠时间更准。误 UPDATE 那种事故，我通常先 pg_waldump 圈出那条 UPDATE 的 xid，然后 recovery_target_xid 设成它减一，干净利落。

几个会让你恢复的夜晚很难熬的坑

归档没开就跑基础备份：基础备份本身能拿，但缺了它之后的 WAL，恢复只能到备份时刻。基础备份前一定先验 pg_stat_archiver。
归档目录权限不对：postgres 写不进 /archive，archive_command 返回非 0，WAL 在 pg_wal/ 堆积，磁盘悄悄涨满。监控 pg_stat_archiver.last_failed_wal 不为空就要报警。
恢复完忘了验证：库起得来不代表数据对。恢复后先跑关键表的行数、校验和、最近更新时间，跟业务确认，再放流量。
数据目录权限：解压回去后必须 chown postgres 且 chmod 700，权限不对 PG 直接拒绝启动，报错还挺隐晦。
多轮恢复的时间线：每次 PITR 会生成新时间线（00000002.history 之类）。再恢复时要 recovery_target_timeline='latest'，否则默认停在最初那条时间线，找不到后续 WAL。
表空间：有自定义表空间时，基础备份会有额外的 tar，恢复要按 tablespace_map 解到对的挂载点，漏了会报文件不存在。

没演练过的备份等于没备份

这是最实在的一条。我见过太多团队备份脚本跑得欢，真出事恢复时发现基础备份损坏、或者 WAL 少了一段，那时候再哭来不及。建议至少每季度做一次恢复演练：在隔离机器上按上面流程恢复最近一份基础备份到某时间点，核对行数和关键业务表。演练脚本可以固定下来， CI 之外单独排期跑。

另外，WAL 归档的保留期要覆盖你的恢复窗口。比如基础备份每周日做一次，那归档至少保留到「下一份基础备份可用」之后，否则中间某天的 PITR 会缺 WAL。用 pg_archivecleanup 配合基础备份时间清理过期归档：

# 删掉早于某个时间线文件的归档段
pg_archivecleanup /archive 00000001000000000000000A

最后提一句：PITR 解决的是「恢复到过去某点」，不是「实时高可用」。它和流复制、Patroni 这类高可用方案是互补的——高可用管故障切换，PITR 管人为误操作和逻辑损坏。两个都得有，别指望一个顶俩。