【openGauss训练营学习心得】OpenGauss PITR恢复

背景信息

当数据库崩溃或希望回退到数据库之前的某一状态时， openGauss的即时恢复功能（ Point-In-Time Recovery，简称PITR）可以支持恢复到备份归档数据之后的任意时间点。

说明：

● PITR仅支持恢复到物理备份数据之后的某一时间点。
● 仅主节点可以进行PITR恢复，备机需要进行全量build达成与主机数据同步。

前提条件

● 基于经过物理备份的全量数据文件。
● 基于已归档的WAL日志文件。

恢复流程

步骤1 将物理备份的文件替换目标数据库目录。
步骤2 删除数据库目录下pg_xlog/中的所有文件。
步骤3 将归档的WAL日志文件复制到pg_xlog文件中（此步骤可以省略，通过配置recovery.conf恢复命令文件中的restore_command项替代）。
步骤4 在数据库目录下创建恢复命令文件recovery.conf，指定数据库恢复的程度。
步骤5 启动数据库。
步骤6 连接数据库，查看是否恢复到希望预期的状态。
步骤7 若已经恢复到预期状态，通过pg_xlog_replay_resume()指令使主节点对外提供服务。

recovery.conf文件配置

####  归档恢复配置  ####
restore_command = 'cp /opt/huawei/install/data/archivelog/%f %p'    ## 该SHELL命令获取已归档的WAL文件。
archive_cleanup_command = 'pg_archivecleanup /opt/huawei/install/data/archivelog %r'  ## 清理备库WAL归档日志的shell命令，每次重启时会执行
recovery_end_command = string                                        ## (可选) 在恢复完成时执行的SHELL命令,为以后的复制或恢复提供一个清理机制
## 说明：
##  %f即归档检索中的文件名，%p即复制目的地的路径名，%r最新可用重启点的文件名
##  如果多个备机从相同的归档路径恢复时，需要确保该路径存在所有备机恢复所需要的WAL文件。

#### 恢复目标设置(四选一) ####
recovery_target_name = 'point_a'      ## 还原到一个使用pg_create_restore_point()创建的还原点
recovery_target_time = '2021-09-08 10:59:42'   ## 还原到一个指定时间戳
recovery_target_xid = '3000'                  ## 还原到一个事务ID
recovery_target_lsn = '0/0FFFFFF'             ## 还原到日志的指定LSN点
recovery_target_inclusive = true              ## 声明是否在指定恢复目标之后停止(true) 或 之前停止(false),不支持recovery_target_name 配置
## 注意：
##   > 如果不配置任何恢复目标 或 配置目标不存在，则默认恢复到最新的WAL日志点。
##   > recovery_target_name， recovery_target_time， recovery_target_xid，recovery_target_lsn,这四个配置项仅同时支持一项。

备份恢复测试

备份

[omm@OpenGauss backup]$ gs_basebackup -D /home/omm/backup -h 127.0.0.1 -p 15400
INFO:  The starting position of the xlog copy of the full build is: 0/18000028. The slot minimum LSN is: 0/0.
[2021-09-08 10:57:51]:begin build tablespace list
[2021-09-08 10:57:51]:finish build tablespace list
[2021-09-08 10:57:51]:begin get xlog by xlogstream
[2021-09-08 10:57:51]: check identify system success                                                
[2021-09-08 10:57:51]: send START_REPLICATION 0/18000000 success                                    
[2021-09-08 10:57:51]: keepalive message is received                                                
[2021-09-08 10:57:51]: keepalive message is received                                                
[2021-09-08 10:57:56]:gs_basebackup: base backup successfully

创建测试数据

devin=> select * from test1;
 id 
----
  1
  2
  3
(3 rows)

devin=> select current_time;
       timetz       
--------------------
 10:59:42.122792+08
(1 row)
--此时表中三条数据

devin=> insert into test1 values (4);
INSERT 0 1
devin=> select current_time;
      timetz       
-------------------
 11:00:21.01102+08
(1 row)
--此时表中四条数据

devin=> insert into test1 values(5);
INSERT 0 1
devin=> select current_time;
       timetz       
--------------------
 11:00:53.604855+08
(1 row)
--此时表中五条数据

恢复测试

由于是在本机测试，直接用备份文件覆盖原dn目录文件（建议先将原dn目录文件备份一下）

恢复第一阶段

## copy备份文件到dn目录
[omm@OpenGauss backup]$ cp -r * /opt/huawei/install/data/dn/

## 配置recovery.conf
[omm@OpenGauss dn]$ vi recovery.conf
restore_command = 'cp /opt/huawei/install/data/archivelog/%f %p'
archive_cleanup_command = 'pg_archivecleanup /opt/huawei/install/data/archivelog  %r'
recovery_target_time = '2021-09-08 10:59:42'      ## 指定恢复时间点，此时表中只有三条数据
recovery_target_inclusive = true

## 启动数据库
[omm@OpenGauss dn]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] OpenGauss
2021-09-08 11:05:44.538 61382888.1 [unknown] 140062797747968 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2021-09-08 11:05:44.550 61382888.1 [unknown] 140062797747968 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4250 Mbytes) is larger.
=========================================
Successfully started.

##### 数据验证
devin=> select * from test1;
 id 
----
  1
  2
  3
(3 rows)

恢复第二阶段

[omm@OpenGauss dn]$ vi recovery.conf 
restore_command = 'cp /opt/huawei/install/data/archivelog/%f %p'
archive_cleanup_command = 'pg_archivecleanup /opt/huawei/install/data/archivelog  %r'
recovery_target_time = '2021-09-08 11:00:21'      ## 指定恢复时间点，此时表中有四条数据
recovery_target_inclusive = true

重启数据库
[omm@OpenGauss script]$ gs_om -t stop  
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
[omm@OpenGauss dn]$ gs_om -t start  
Starting cluster.
=========================================
[SUCCESS] OpenGauss
2021-09-08 11:07:02.160 613828d6.1 [unknown] 140188722190080 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2021-09-08 11:07:02.170 613828d6.1 [unknown] 140188722190080 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4250 Mbytes) is larger.
=========================================
Successfully started.

##### 数据验证
[omm@OpenGauss script]$ gsql -d devin -U devin -p 15400 
Password for user devin: 
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:03 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

devin=> select * from test1;
 id 
----
  1
  2
  3
  4
(4 rows)

恢复第三阶段

[omm@OpenGauss dn]$ vi recovery.conf 
restore_command = 'cp /opt/huawei/install/data/archivelog/%f %p'
archive_cleanup_command = 'pg_archivecleanup /opt/huawei/install/data/archivelog  %r'
recovery_target_time = '2021-09-08 11:00:53'      ## 指定恢复时间点，此时表中有五条数据
recovery_target_inclusive = true

重启数据库
[omm@OpenGauss script]$ gs_om -t stop  
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
[omm@OpenGauss dn]$ gs_om -t start  
Starting cluster.
=========================================
[SUCCESS] OpenGauss
2021-09-08 11:07:37.921 613828f9.1 [unknown] 139719194806016 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2021-09-08 11:07:37.930 613828f9.1 [unknown] 139719194806016 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4250 Mbytes) is larger.
=========================================
Successfully started.

##### 数据验证
[omm@OpenGauss script]$ gsql -d devin -U devin -p 15400 
Password for user devin: 
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:03 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

devin=> select * from test1;
 id 
----
  1
  2
  3
  4
  5
(5 rows)