PostgreSQL12中实现增量备份与任意时间点恢复

在postgres中,能够使用简单的逻辑备份工具pg_dump做某个数据库的完整备份

问题

如果数据库在运行过程中发生故障, 使用逻辑备份只能将数据库还原到备份时刻, 无法恢复到故障发生前的那个时刻；

由于误操作修改或删除了重要数据, 需要还原到误操作前的那个时刻

解决方案

增量备份和任意时间点恢复

实现增量备份的思路是
1. 记录数据库系统的操作记录（WAL归档）
2. 在某一时刻进行一次完整的数据库备份
3. 需要恢复时,还原上一次完整的数据库备份,根据操作记录恢复数据库至指定的某个时刻(即可以实现PITR 时间点恢复)

操作

1、配置数据库、vim postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /home/test/archive/%f && echo %f >> /home/test/archive/archive.list' 
wal_keep_segments=1024

2、重启数据库

pg_ctl -D /home/test/data/ -l /home/test/log restart

3、此后数据库的操作都会记录wal日志文件到/home/test/archive目录下

[test@localhost bin]$ ls /home/test/archive/
000000010000000000000001  000000010000000000000002  000000010000000000000003  000000010000000000000004  archive.list

4、创建基础备份，可以使用pg_basebackup

pg_basebackup -D /home/test/back -Fp -v -P -Upostgres -R

5、测试数据准备

postgres=# create table t2(a int);
CREATE TABLE
postgres=# insert into t2 values (generate_series(1,1000000));
INSERT 0 1000000
记录时间：2020年 05月 09日 星期六 21:46:57 CST

postgres=# create table t3(a int);
CREATE TABLE
postgres=# insert into t3 values (generate_series(1,1000000));
INSERT 0 1000000

6、假定数据库故障，停止数据库，备份故障data并将基础备份的back替换为新data

mv data data_back
mv back data

7、修改data目录下postgresql.auto.conf

restore_command = 'cp /home/test/archive/%f %p > /home/test/archive/recovery.log 2>&1  '
recovery_target_time = '2020-05-09 21:46:57'

recovery_target_time表示要恢复到的时间点

8、启动数据库

 /usr/pgsql-12/bin/./pg_ctl -D data/ start

9、查看数据，可知t3未被恢复

[test@localhost ~]$ psql -Upostgres
psql (12.2)
输入 "help" 来获取帮助信息.

postgres=# \dt
              关联列表
 架构模式 | 名称 |  类型  |  拥有者
----------+------+--------+----------
 public   | t1   | 数据表 | postgres
 public   | t2   | 数据表 | postgres
(2 行记录)

日志信息：

2020-05-09 21:40:15.638 CST [13422] 日志:  数据库上次关闭时间为 2020-05-08 23:24:35 CST
2020-05-09 21:40:15.686 CST [13420] 日志:  数据库系统准备接受连接
2020-05-09 22:05:18.986 CST [23055] 日志:  数据库系统中断；上一次的启动时间是在2020-05-09 21:53:25 CST
2020-05-09 22:05:19.003 CST [23055] 日志:  正在进入备用模式
2020-05-09 22:05:19.019 CST [23055] 日志:  从归档中恢复日志文件 "000000010000000000000009"
2020-05-09 22:05:19.040 CST [23055] 日志:  redo 在 0/9000060 开始
2020-05-09 22:05:19.041 CST [23055] 日志:  在0/9000138上已到达一致性恢复状态
2020-05-09 22:05:19.042 CST [23053] 日志:  数据库系统准备接受只读请求的连接
2020-05-09 22:05:19.055 CST [23055] 日志:  从归档中恢复日志文件 "00000001000000000000000A"
2020-05-09 22:05:19.384 CST [23055] 日志:  从归档中恢复日志文件 "00000001000000000000000B"
2020-05-09 22:05:19.837 CST [23055] 日志:  从归档中恢复日志文件 "00000001000000000000000C"
2020-05-09 22:05:20.343 CST [23055] 日志:  从归档中恢复日志文件 "00000001000000000000000D"
2020-05-09 22:05:20.986 CST [23055] 日志:  恢复停止在事物 494 提交之前, 时间 2020-05-09 21:47:16.174754+08
2020-05-09 22:05:20.986 CST [23055] 日志:  恢复操作已暂停
2020-05-09 22:05:20.986 CST [23055] 提示:  执行 pg_wal_replay_resume() 以继续.

 the end;