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

本是古典何须时尚 2023-10-16 06:19 29阅读 0赞

#### 模拟场景 ####

> 场景：每天凌晨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);

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

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

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

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

4、恢复数据（确认有归档日志）

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

![image.png][]

然后将全备的内容中的base.tar扔data目录下，归档日志也扔到/archive位置。

5、查看归档日志，找到指定的事务id

查看归档日志，需要基于postgresql提供的一个命令

# 如果命令未找到，说明两种情况，要么没有这个可执行文件，要么是文件在，没设置环境变量
    # 咱们这是后者
    pg_waldump
    # 也可以采用全路径的方式
    /usr/pgsql-12/bin/pg_waldump

![image.png][image.png 1]

![image.png][image.png 2]

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

修改`postgresql.auto.conf`文件

将之前的最大恢复，更换为指定的事务id恢复

基于提供的配置例子，如何指定事务id

![image.png][image.png 3]

修改postgresql.auto.conf文件指定好事务ID

![image.png][image.png 4]

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

![image.png][image.png 5]

8、记得执行会后的函数，避免无法执行写操作

select pg_wal_replay_resume();

[image.png]: https://img-blog.csdnimg.cn/img_convert/363ab1225bfb7bd54d6fafe38af69a31.png
[image.png 1]: https://img-blog.csdnimg.cn/img_convert/744758033d09606a038417bd0aca0b31.png
[image.png 2]: https://img-blog.csdnimg.cn/img_convert/3434f3771d1d7378620e2f0516df1bf1.png
[image.png 3]: https://img-blog.csdnimg.cn/img_convert/e46bcbdec29c639cf6e5b70b60a47d48.png
[image.png 4]: https://img-blog.csdnimg.cn/img_convert/ab532e05906825648445e84c7a4e955f.png
[image.png 5]: https://img-blog.csdnimg.cn/img_convert/71e886c0f09433e1c5bb4db291274fb7.png