postgreSQL基于时间点的恢复（PITR）

postgreSQL基于时间点的回复（PITR）

postgreSQL基于时间点的回复（PITR）

PostgreSQL教程：物理备份与恢复（PITR-Point in time Recovery 基于归档日志将数据恢复到指定的事务id或者是指定时间点）

模拟场景

场景：每天凌晨02:00，开始做全备（PBK），到了第二天，如果有人14:00分将数据做了误删，希望将数据恢复到14:00分误删之前的状态？

1、恢复全备数据，使用PBK的全备数据恢复到凌晨02:00的数据。（数据会丢失很多）
2、归档恢复：备份中的归档，有02:00~14:00之间的数据信息，可以基于归档日志将数据恢复到指定的事务id或者是指定时间点，从而实现数据的完整恢复。

准备场景和具体操作

1、构建一张t3表查询一些数据

在需要备份的库中创建标记表，并检查点和归档指令

-- 构建一张表
create table t3 (id int);
insert into t3 values (1);
insert into t3 values (11);

#刷新内存脏页到磁盘
checkpoint;

#手动日志归档
select pg_switch_wal();

2、模拟凌晨2点开始做全备操作

pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.11.32 -p 5432 -R

  pg_basebackup -D /data/postgresql/backup/pg_basebackup_$(date +%Y%m%d) \
              -Fp -Xs -P -v \
              -U postgres \
              -h localhost \
              -p 5432
			  -X stream -v -P \
			  -R


-Fp：原始格式（非tar格式）
-Xs：流式传输WAL文件（可选）
-P：显示进度
 -R /--write-recovery-conf        # 16 以前叫 --write-conf

3、再次做一些写操作，然后误删数据

-- 凌晨2点已经全备完毕
-- 模拟第二天操作
insert into t3 values (111);
insert into t3 values (1111);
-- 误删操作  2023年3月20日20:13:26
delete from t3;

4、拷贝备份文件到目标机器（确认有归档日志）

将当前服务的数据全部干掉，按照之前的全备恢复的套路先走着

rm -rf /data/postgresql/pgdata/*

然后将全备的内容中的所有备份文件扔pgdata目录下，归档日志也扔到/archive位置。

scp -r /data/postgresql/backup/pg_basebackup_20250905/* postgres@192.168.201.65:/data/postgresql/pgdata/
scp -r /data/postgresql/archive/* postgres@192.168.xxx.xx:/data/postgresql/archive/

5、恢复数据

在 PostgreSQL 12 及以后版本，“恢复到最新时间 / 指定时间 / 指定事务” 的目标参数都写在 postgresql.auto.conf（或 postgresql.conf）中，并配合一个空文件 recovery.signal（PG12+）即可触发 PITR。
下面给出 3 种场景的最小可用模板，直接复制到 $PGDATA/postgresql.auto.conf，重启实例即可生效。

pg12以前
在PG12以前需要修改 recovery.conf文件配置还原参数

cp   $PGHOME/share/recovery.conf.sample  $PGDATA/recovery.conf 

cat  >  $PGDATA/recovery.conf << EOF
restore_command = 'cp /data/pg_arch/20180118/%f %p'
recovery_target_timeline = 'latest'
EOF

备注1：配置recovery_target_timeline参数, 便于判断是否已经到达还原点. (可选, 仅做PITR时需要.一般都是恢复到最新)
备注2：对路径 /data/pg_arch/20180118/的解释
备注3：备份完成后recovery.conf文件名会自动修改为 recovery.done

pg12以后
从v12开始，针对此问题进行了改进，把recovery.conf中的参数合到了postgresql.conf配置文件中，但在非恢复模式这些参数将被忽略。

从PG12开始，recovery.conf文件不存在，由下面两个新文件进行替换：
recovery.signal：告诉PostgreSQL进入正常的归档恢复
standby.signal：告诉PostgreSQL进入standby模式
如果两个文件都存在，则standby.signal优先。

1.直接全量恢复，直接恢复到数据崩溃或者误删时的最新状态、

恢复到最新状态（latest）

restore_command = 'cp /data/postgresql/archive/%f %p'
recovery_target = 'immediate'
recovery_target_timeline = 'latest'

recovery_target = 'immediate' 最大程度的恢复
说明

不需要指定具体时间或事务号，重放所有可用 WAL 后自动结束恢复。
恢复完成后默认进入只读模式，如需可写，执行 SELECT pg_wal_replay_resume(); 并重启或删除 recovery.signal 。

2.基于时间点恢复，恢复到指定时间点

恢复到指定时间点

restore_command = 'cp /data/postgresql/archive/%f %p'
recovery_target_time = '2025-09-05 14:30:00'   -- 任意合法时间戳
recovery_target_timeline = 'latest'
recovery_target_action = 'pause'   -- 可选：pause / promote / shutdown

说明

时间格式使用 ISO 8601：'YYYY-MM-DD HH:MI:SS'（可带时区）。
若该时间点恰好处在事务中间，PostgreSQL 会恢复到包含该时间的最近一致事务提交点。
r- ecovery_target_action=pause 让实例在目标点保持只读，方便验证；确认无误后执行 pg_wal_replay_resume() 提升为可写。

3.基于事务号恢复，恢复到指定事务执行之前

恢复到指定事务 ID（XID）

restore_command = 'cp /data/postgresql/archive/%f %p'
recovery_target_xid = '757'      -- 用 pg_waldump 或审计日志查到的 XID  eg:pg_waldump xxxxxxxxxxxxxxx01
recovery_target_timeline = 'latest'
recovery_target_action = 'pause'

说明

恢复到该 XID 提交完成后的那一刻；若 XID 不存在则继续重放到 WAL 末尾。比如删除语句的XID是758，那我们应该恢复到对应的上一个事务id的位置eg:757
启动日志会提示：recovery stopping after commit of transaction 123456 。
同样需手动 pg_wal_replay_resume() 结束只读状态。

4.基于设置的还原点恢复

如果有还原点，也可以恢复到指定的还原点：
创建还原点：
SELECT pg_create_restore_point('restore_point1');

恢复到还原点：
recovery_target_name ='restore_point1'

通用步骤（PG12+）

1.停库：systemctl stop postgresql
2.清空数据目录并拷入基础备份。
3.在 $PGDATA 创建空文件：touch recovery.signal
4.按上述场景编辑 postgresql.auto.conf。
5.启动集群：systemctl start postgresql
6.若配置了 recovery_target_action=pause，验证数据后执行
SELECT pg_wal_replay_resume(); 即可变为可读写 。

可选补充参数

- recovery_target_name = 'before_upgrade'   -- 恢复到预先命名的还原点
- recovery_target_lsn = '0/16B6F98'         -- 恢复到指定 LSN
- recovery_target_timeline = '2'             -- 强制进入某时间线分支 
按需要只保留一个 recovery_target_* 参数即可；多个同时存在时，PostgreSQL 按优先级选取。

- recovery_target = 'immediate' 指定恢复应该在达到一个一致状态后尽快结束，即尽早结束。 在从一个在线备份中恢复时，这意味着备份结束的那个点。
- recovery_target_name (string)指定恢复将继续进行的已命名的恢复点 （pg_create_restore_point()创建）。
- recovery_target_time (timestamp)这个参数指定恢复将继续执行的时间戳。精确的停止点也受到recovery_target_inclusive的影响。
- recovery_target_xid (string)指定恢复将继续执行的事务ID。
- recovery_target_inclusive (boolean)指定我们是否在指定的恢复目标之后停止（true）， 或者在恢复目标之前停止（false）。
- recovery_target_timeline (string)指定恢复到一个特定的时间线中。默认值是沿着基础备份建立时的当前时间线恢复。
将这个参数设置为latest会恢复到该归档中能找到的最新的时间线， 这在一个后备服务器中有用。
- recovery_target_action (enum) (boolean)指定当到达恢复目标时服务器应该采取什么动作。默认值是pause， 这意味着将暂停恢复。
- promote意味着将结束恢复进程并且服务器开始接受连接。 shutdown将在到达恢复目标后停止服务器。

6、修改pgdata目录下的恢复数据的方式

vim postgresql.auto.conf

# recovery.conf内容示例
restore_command = 'cp /data/postgresql/archive/%f %p'
recovery_target_time = '2025-09-05 13:57:00 CST'
# 可选参数：
# recovery_target_xid = '123456'
# recovery_target_name = 'restore_point_1'

7、验证数据

启动postgreSQL服务，查看是否恢复到指定事务ID

8.结束只读状态

select pg_wal_replay_resume();

pg_basebackup参数说明

下面给出 pg_basebackup 常用参数的速查说明（基于 PostgreSQL 15/16 官方手册及近期云厂商文档整理，可直接用于生产脚本）。
若需完整列表，可在服务器执行 pg_basebackup --help 或参考在线手册。

一、备份目的地与格式

-D, --pgdata=DIR          备份根目录（必选）。目录必须为空或不存在。
-F, --format=p|t          输出格式：plain（默认，直接复制文件）或 tar（打包）。
-z, --gzip                仅对 tar 格式生效，启用 gzip 压缩。
-Z, --compress=0-9        指定压缩级别，0=不压缩，9=最大压缩（tar 格式）。
--waldir=WALDIR           单独指定 WAL 目录存放位置（plain 格式时有用）。

二、WAL（事务日志）控制

-X, --wal-method=none|fetch|stream
none   – 不包含 WAL，不能做 PITR。
fetch  – 备份结束后一次性拉取所需 WAL，可能增加主库压力。
stream – 备份期间并行流式接收 WAL，推荐方式，可做到“零丢失”。
-C, --create-slot         备份前自动创建复制槽（需与 -X stream 合用）。
-S, --slot=SLOTNAME       使用已存在的复制槽进行流式 WAL 接收。
--no-slot                 禁止创建临时复制槽（若主库版本≥10，默认会建临时槽）。

三、连接参数

-h, --host=HOST           主库 IP 或套接字目录。
-p, --port=PORT           端口，默认 5432。
-U, --username=NAME       连接用户，须具备 REPLICATION 或 SUPERUSER 权限。
-w, --no-password         不提示密码（依赖 .pgpass 或环境变量）。
-W, --password            强制交互输入密码。
-d, --dbname=CONNSTR      直接给连接串，如 "host=ip port=5432 user=repl dbname=postgres"

四、备份过程调优

-c, --checkpoint=fast|spread
fast    – 立即做检查点，备份开始快，但可能造成 I/O 尖峰。
spread  – 默认，平滑分散检查点写盘。
-r, --max-rate=RATE       限速，单位 kB/s，可加 M 后缀，如 100M。
-P, --progress            实时打印进度百分比。
-v, --verbose             输出更详细日志，排错必备。
-N, --no-sync             写完文件后不强制刷盘，备份快但掉电可能损坏。
-n, --no-clean            备份失败时不删除已拷贝文件，便于调试。

五、表空间/路径映射（plain 格式）

-T, --tablespace-mapping=OLDDIR=NEWDIR
把原实例里 OLDDIR 表空间目录重定向到 NEWDIR。
可多次指定，用于异机恢复时路径不一致场景。

六、常用组合示例

本地 tar+压缩+流式 WAL，带进度条
pg_basebackup -D /backup/full_$(date +%F) -Ft -z -Z5 -X stream -P -v -U repl -W
远程异地备库搭建（自动生成 standby 信号）
pg_basebackup -D /data/pgdata -Fp -X stream -P -R -S standby_slot -h 主库IP -p 5432 -U repl -W
限速 50 MB/s、平滑检查点，用于生产高峰时段
pg_basebackup -D /backup/full -Ft -X stream -c spread -r 50M -P -v

eg:
pg_basebackup -D /data/postgresql/backup/pg_basebackup_$(date +%Y%m%d) -Fp -Xs -P -v -U postgres -h 192.168.201.51 -p 5432

tar+压缩+流式 WAL，带进度条
pg_basebackup -D /data/postgresql/backup/full_$(date +%F) -Ft -z -Z5 -X stream -P -v -U postgres -h 192.168.201.51 -p 5432 -W

或者文件加上时分秒：date +%Y%m%d%H%M%S


-Ft tar格式打包
-z, --gzip                仅对 tar 格式生效，启用 gzip 压缩。
-Z5, --compress=0-9        指定压缩级别，0=不压缩，9=最大压缩（tar 格式）。
-X, --wal-method=none|fetch|stream
   none   – 不包含 WAL，不能做 PITR。
   fetch  – 备份结束后一次性拉取所需 WAL，可能增加主库压力。
   stream – 备份期间并行流式接收 WAL，推荐方式，可做到“零丢失”。
-P, --progress            实时打印进度百分比。
-v, --verbose             输出更详细日志，排错必备。

**备份输出文件说明： **
backup_manifest 是 pg_basebackup 在备份结束后自动生成的一个 JSON 格式“备份清单”，PostgreSQL 13 及以上版本都有，PG17 又为其增加了增量备份功能。文件里记录了“这份备份里到底有什么、每个文件是否完好、以及这份备份在 WAL 时间轴上的起止位置”，供 pg_verifybackup 做完整性校验，也供后续增量备份（-i）做基准。核心信息分四大段：

七、权限与网络前提

• 主库 pg_hba.conf 需开放 replication 连接：
host replication repl_user 客户端IP/32 md5
• 主库 postgresql.conf 需设置：
wal_level = replica        # 或更高
max_wal_senders = ≥2       # 建议 10
hot_standby = on           # 若后续要挂备库

八、输出解读

备份结束若看到 pg_basebackup: base backup completed 且 exit=0 即成功。
目录下若使用 -R 会自动生成 standby.signal 并将主库连接信息写入 postgresql.auto.conf，备库直接启动即可。

posted @ 2026-05-18 15:24 数据库小白（专注）阅读(7) 评论(0) 收藏举报

刷新页面返回顶部

数据库小白（专注）