PostgreSQL 时间点恢复(PITR) 基于 WAL 与基础备份实战

启用 WAL 归档（postgresql.conf）：wal_level = replica

archive_mode = on

archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

执行基础备份：pg_basebackup -h 127.0.0.1 -p 5432 -U replicator -D /backups/base_2025_11_26 -X fetch -P

准备恢复目录与参数：echo 'restore_command = "cp /var/lib/postgresql/wal_archive/%f %p"' >> /backups/base_2025_11_26/postgresql.auto.conf

touch /backups/base_2025_11_26/recovery.signal

echo "recovery_target_time = '2025-11-26 10:00:00+00'" >> /backups/base_2025_11_26/postgresql.auto.conf

启动使用备份的数据目录：pg_ctl -D /backups/base_2025_11_26 start