postgres实现任意时间点恢复（使用pg_basebackup）

配置postgresql.conf文件

#日志级别
wal_level = replica
#归档开关
archive_mode = on
#归档命令
archive_command = 'cp %p /usr/local/postgres/archive/%f' 

第一次执行这个配置，需要重启数据库；

查看归档目录

此后数据库的操作都会记录wal日志文件到/usr/local/postgres/archive目录下

如果归档目录中没有内容，需要执行强制日志归档命令

select pg_switch_wal();

使用pg_basebackup进行基础备份

[bxy@localhost postgres]$ ./bin/pg_basebackup -h 127.0.0.1 -Ft -Pv -Xf -D backup/

备份完成后，对应备份目录会出现基础备份 是一个压缩包

准备试验数据

在2023-02-01 16:40:00 创建表dog 并随意插入几条数据
等待适当时间，在16:43:00创建表cat 并随意插入几条数据
恢复时间点 2023-02-01 16:42:00 （创建两张表之间的时间点就行）
如果恢复后数据库中存在dog表，不存在cat表 则试验成功。

模拟数据库故障

模拟数据库故障，停掉数据库

使用备份替换data目录

修改postgresql.conf

restore_command = 'cp /usr/local/postgres/archive/%f %p'
recovery_target_time = '2023-02-02 06:55:00'

添加空文件 提醒postgresql要做recovery

touch data/recovery.signal

重启数据库

[bxy@localhost postgres]$ ./bin/pg_ctl -D /usr/local/postgres/data/ start
waiting for server to start....2023-02-02 07:09:59.237 CST [2897] LOG:  starting PostgreSQL 13.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
2023-02-02 07:09:59.238 CST [2897] LOG:  listening on IPv6 address "::1", port 5432
2023-02-02 07:09:59.238 CST [2897] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-02-02 07:09:59.241 CST [2897] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-02-02 07:09:59.250 CST [2898] LOG:  database system was interrupted; last known up at 2023-02-02 06:50:57 CST
cp: 无法获取"/usr/local/postgres/archive/00000003.history" 的文件状态(stat): 没有那个文件或目录
2023-02-02 07:09:59.277 CST [2898] LOG:  starting point-in-time recovery to 2023-02-02 06:55:00+08
2023-02-02 07:09:59.282 CST [2898] LOG:  restored log file "00000002.history" from archive
2023-02-02 07:09:59.311 CST [2898] LOG:  restored log file "000000020000000000000006" from archive
2023-02-02 07:09:59.349 CST [2898] LOG:  redo starts at 0/6000028
2023-02-02 07:09:59.350 CST [2898] LOG:  consistent recovery state reached at 0/6000100
2023-02-02 07:09:59.350 CST [2897] LOG:  database system is ready to accept read only connections
2023-02-02 07:09:59.379 CST [2898] LOG:  restored log file "000000020000000000000007" from archive
 done
server started
[bxy@localhost postgres]$ 2023-02-02 07:09:59.413 CST [2898] LOG:  recovery stopping before commit of transaction 491, time 2023-02-02 07:00:38.056702+08
2023-02-02 07:09:59.413 CST [2898] LOG:  pausing at the end of recovery
2023-02-02 07:09:59.413 CST [2898] HINT:  Execute pg_wal_replay_resume() to promote.

[bxy@localhost postgres]$ 
[bxy@localhost postgres]$ ./bin/psql -d postgres
psql (13.8)
Type "help" for help.

postgres=# 
postgres=# \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | dog  | table | bxy
(1 row)

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

postgres=# 2023-02-02 07:12:05.587 CST [2898] LOG:  redo done at 0/700F3B0
cp: 无法获取"/usr/local/postgres/archive/00000003.history" 的文件状态(stat): 没有那个文件或目录
2023-02-02 07:12:05.594 CST [2898] LOG:  selected new timeline ID: 3
2023-02-02 07:12:05.652 CST [2898] LOG:  archive recovery complete
2023-02-02 07:12:05.660 CST [2898] LOG:  restored log file "00000002.history" from archive
2023-02-02 07:12:05.678 CST [2897] LOG:  database system is ready to accept connections

postgres=# 

恢复后的数据库为只读模式 需要执行

select pg_wal_replay_resume()

查看当前时间线的命令

[bxy@localhost postgres]$ ./bin/pg_controldata /usr/local/postgres/data/ |grep TimeLineID
Latest checkpoint's TimeLineID:       3
Latest checkpoint's PrevTimeLineID:   2