PITR简介

1.什么是PITR ？

PITR: 全称是Point-In-Time-Recover （时间点恢复），是PG从8.0版本开始引入的一个特性，该特性可以使用基础备份和连续归档日志将数据库集群恢复到任意时间点。

2.PITR的工作流程
1）PostgreSQL从backup_label文件中读取CHECKPOINT LOCATION的值，即REDO点。

2）PostgreSQL从recovery.conf(版本11或更早)或PostgreSQL .conf(版本12或更高)中读取restore_command和recovery_target_time参数值。

3） PostgreSQL从REDO点开始重放WAL数据，通过参数resotere_command，从归档日志中读取WAL数据，将归档日志从归档区复制到临时区。(临时区域中复制的日志文件使用后会被删除。)

注：如果没有配置recovery_target_time，PG将重放日志直到归档结束。

4）恢复过程结束后，生成时间线历史文件，如00000002.History，该文件被创建在pg_xlog子目录(在版本10或更高版本，pg_wal子目录)，如果启用了归档日志特性，归档目录下也会创建相同的命名文件。

3.PITR与正常的数据库恢复有何不同？

正常恢复模式是从base目录下的pg_wal目录中获取wal文件，而PITR模式是从archive_command中设置的归档目录中获取。

正常恢复模式从pg_control文件获取检查点位置，而PITR模式从backup_label文件中获取检查点位置。

4.PITR操作步骤
1）配置归档参数，vi postgresql.conf。

listen_addresses = '*' 
archive_mode = on  
archive_command = 'cp %p /home/postgres/archive/%f'

重启数据库生效。

2）进行基础备份。

[postgres@90220 pg12]$ pg_basebackup -Pv -Ft -Xf -D basebackup
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000060 on timeline 1
40974/40974 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

3）创造测试数据。

[postgres@90220 pg12]$ psql
psql (12.0)
Type "help" for help.

postgres=# create table tb1 (c1 int,c2 char);
CREATE TABLE
postgres=# insert into tb1 values(2,'c');
INSERT 0 1
postgres=# select now(); --恢复到这个时间点time1
              now              
-------------------------------
 2019-10-25 17:05:35.023024+08
(1 row)

postgres=# drop table tb1 ;
DROP TABLE

4）关闭数据库。

[postgres@90220 pg12]$ pg_ctl stop
waiting for server to shut down....2019-10-25 17:06:51.193 CST [34745] LOG:  received fast shutdown request
2019-10-25 17:06:51.194 CST [34745] LOG:  aborting any active transactions
2019-10-25 17:06:51.195 CST [34745] LOG:  background worker "logical replication launcher" (PID 34753) exited with exit code 1
2019-10-25 17:06:51.196 CST [34747] LOG:  shutting down
2019-10-25 17:06:51.398 CST [34745] LOG:  database system is shut down
 done
server stopped

5）替换data。

[postgres@90220 pg12]$ cd data/
[postgres@90220 data]$ rm -rf * 
[postgres@90220 data]$ cp ../basebackup/base.tar .
[postgres@90220 data]$ tar xf base.tar 
[postgres@90220 data]$ ls
backup_label  global        pg_hba.conf    pg_multixact  pg_serial     pg_stat_tmp  pg_twophase  pg_xact               tablespace_map
base          pg_commit_ts  pg_ident.conf  pg_notify     pg_snapshots  pg_subtrans  PG_VERSION   postgresql.auto.conf
base.tar      pg_dynshmem   pg_logical     pg_replslot   pg_stat       pg_tblspc    pg_wal       postgresql.conf

6）在data目录下创建标志文件。

[postgres@90220 data]$ touch recovery.signal

7）修改postgresql.conf配置文件。

restore_command = 'cp /home/postgres/archive/%f %p'
recovery_target_time = '2019-10-25 17:05:35.023024+08'
recovery_target_action = 'promote'
注：recovery_targer_action控制恢复到指定目标时数据库的动作，默认是pause，表示将恢复暂停，只允许读操作，需要手动执行pg_wal_replay_resume() 恢复，设置为promote后，恢复过程完成，服务器接受连接 

8）启动数据库，完成恢复。

[postgres@90220 data]$ pg_ctl start
waiting for server to start....2019-10-25 17:17:00.376 CST [34988] LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2019-10-25 17:17:00.378 CST [34988] LOG:  listening on IPv6 address "::1", port 5432
2019-10-25 17:17:00.378 CST [34988] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-10-25 17:17:00.380 CST [34988] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-10-25 17:17:00.392 CST [34989] LOG:  database system was interrupted; last known up at 2019-10-25 16:59:34 CST
cp: 无法获取"/home/postgres/archive/00000002.history" 的文件状态(stat): 没有那个文件或目录
2019-10-25 17:17:00.403 CST [34989] LOG:  starting point-in-time recovery to 2019-10-25 17:05:35.023024+08
2019-10-25 17:17:00.418 CST [34989] LOG:  restored log file "000000010000000000000002" from archive
2019-10-25 17:17:01.036 CST [34989] LOG:  redo starts at 0/2000060
2019-10-25 17:17:01.037 CST [34989] LOG:  consistent recovery state reached at 0/2000138
2019-10-25 17:17:01.037 CST [34988] LOG:  database system is ready to accept read only connections
 done
server started
[postgres@90220 data]$ 2019-10-25 17:17:01.049 CST [34989] LOG:  restored log file "000000010000000000000003" from archive
2019-10-25 17:17:01.512 CST [34989] LOG:  recovery stopping before commit of transaction 488, time 2019-10-25 17:05:45.546195+08
2019-10-25 17:17:01.512 CST [34989] LOG:  redo done at 0/3013528
2019-10-25 17:17:01.512 CST [34989] LOG:  last completed transaction was at log time 2019-10-25 17:05:33.544826+08
cp: 无法获取"/home/postgres/archive/00000002.history" 的文件状态(stat): 没有那个文件或目录
2019-10-25 17:17:01.522 CST [34989] LOG:  selected new timeline ID: 2
2019-10-25 17:17:01.944 CST [34989] LOG:  archive recovery complete
cp: 无法获取"/home/postgres/archive/00000001.history" 的文件状态(stat): 没有那个文件或目录
2019-10-25 17:17:01.972 CST [34988] LOG:  database system is ready to accept connections

9）验证表已恢复且可写

[postgres@90220 data]$ psql
psql (12.0)
Type "help" for help.

postgres=# select * from tb1 ;
 c1 | c2 
----+----
  2 | c
(1 row)

postgres=# insert into tb1 values (3,'c');
INSERT 0 1

相关参考链接：
https://blog.csdn.net/danns888/article/details/120059776
https://blog.csdn.net/ziptop/article/details/102746152