搭建 PostgreSQL 时间点恢复（PITR）

适用场景

需要把 PostgreSQL 恢复到误删数据、错误更新或故障发生前的某个时间点。PITR 依赖基础备份和连续 WAL 归档，适合对 RPO 要求较高的生产库。

前置条件

• PostgreSQL 已开启 WAL 归档。
• 对象存储或独立备份服务器，用于存放基础备份和 WAL。
• pgBackRest、WAL-G 或 Barman 等备份工具。
• 明确的恢复目标：时间点、事务 ID 或命名还原点。

步骤

1. 配置 WAL 归档：

psql -c "ALTER SYSTEM SET wal_level = replica;"
psql -c "ALTER SYSTEM SET archive_mode = on;"
psql -c "ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=main archive-push %p';"
psql -c "SELECT pg_reload_conf();"

2. 初始化 pgBackRest 并做基础备份：

pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main check
pgbackrest --stanza=main backup --type=full
pgbackrest info

3. 确认 WAL 持续归档：

psql -c "SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;"
pgbackrest --stanza=main check

4. 模拟误操作前创建还原点：

psql -c "SELECT pg_create_restore_point('before_batch_update');"

5. 恢复到目标时间点。先停止应用和数据库：

pg_ctl -D /var/lib/postgresql/data stop -m fast
pgbackrest --stanza=main --delta restore

6. 配置恢复目标：

cat > /var/lib/postgresql/data/postgresql.auto.conf <<'EOF'
restore_command = 'pgbackrest --stanza=main archive-get %f "%p"'
recovery_target_time = '2026-10-18 02:30:00+08'
recovery_target_action = 'promote'
EOF
touch /var/lib/postgresql/data/recovery.signal

7. 启动并验证：

pg_ctl -D /var/lib/postgresql/data start
psql -c "SELECT pg_is_in_recovery();"
psql -c "SELECT * FROM critical_table LIMIT 10;"

验证

• pgbackrest info 显示基础备份和 WAL 归档完整。
• pg_stat_archiver 的 failed_count 为 0。
• 恢复后数据停在目标时间点，pg_is_in_recovery() 返回 f。
• 应用连接、序列、索引和权限正常。
• 恢复后的数据库能做一次新的基础备份。

回滚

• 恢复前保留原数据目录或做一次完整备份。
• 如果恢复目标时间点选错，重新从基础备份恢复，不要在原目录上反复启动。
• 恢复后先只读验证，确认数据正确再开放写入。
• 保留 WAL 归档，直到确认不再需要更早的时间点。

常见坑

• 只做了基础备份，没有开启 WAL 归档，无法 PITR。
• archive_command 失败但没人监控，WAL 堆积或丢失。
• 恢复目标时间落在基础备份之前，恢复直接失败。
• recovery.signal 或 restore_command 配错，数据库反复重启。
• 恢复后没有重置归档和备份计划，新的 WAL 没有继续归档。
• 时间点使用数据库时区以外的时区，恢复结果偏差。
• 恢复环境磁盘空间不足，WAL 回放中断。

相关命令

• pgbackrest — 基础备份、WAL 归档和恢复
• pg_ctl — 启停数据库
• psql — 创建还原点和验证数据
• pg_stat_archiver — 归档状态
• wal-g — 另一种 WAL 备份工具