首页 > 其他分享 >openGauss/MogDB 学习笔记之 -- PITR恢复

openGauss/MogDB 学习笔记之 -- PITR恢复

时间：2024-03-25 10:56:41浏览次数：19  
标签：10 14 -- PITR MogDB 29 2022 db1 dbmt

openGauss/MogDB 学习笔记之 -- PITR 恢复
概念描述
背景信息
当数据库崩溃或希望回退到数据库之前的某一状态时，MogDB 的即时恢复功能（Point-In-Time Recovery，简称 PITR）可以支持恢复到备份归档数据之后的任意时间点。

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

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

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

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

测试验证
1、环境准备(gs_baasebackup 备份)
[omm@db1 db1]$ gs_basebackup -U em_ljc -W -h ... -p 26000 -D /home/omm/gs_pitr
Password:
INFO: The starting position of the xlog copy of the full build is: 4/EA000028. The slot minimum LSN is: 0/0.
[2022-10-29 14:18:24]:begin build tablespace list
[2022-10-29 14:18:24]:finish build tablespace list
[2022-10-29 14:18:24]:begin get xlog by xlogstream
[2022-10-29 14:18:24]: check identify system success
[2022-10-29 14:18:24]: send START_REPLICATION 4/EA000000 success
[2022-10-29 14:18:24]: keepalive message is received
[2022-10-29 14:18:24]: keepalive message is received
[2022-10-29 14:18:27]: keepalive message is received
[2022-10-29 14:18:30]: keepalive message is received
[2022-10-29 14:18:30]: keepalive message is received
[2022-10-29 14:18:33]: keepalive message is received
[2022-10-29 14:18:36]: keepalive message is received
[2022-10-29 14:18:36]: keepalive message is received
[2022-10-29 14:18:39]: keepalive message is received
[2022-10-29 14:18:42]: keepalive message is received
[2022-10-29 14:18:42]: keepalive message is received
[2022-10-29 14:18:45]: keepalive message is received
[2022-10-29 14:18:47]: keepalive message is received
[2022-10-29 14:18:50]: keepalive message is received
[2022-10-29 14:18:53]: keepalive message is received
[2022-10-29 14:18:56]: keepalive message is received
[2022-10-29 14:18:59]: keepalive message is received
[2022-10-29 14:18:59]: keepalive message is received
[2022-10-29 14:19:02]: keepalive message is received
[2022-10-29 14:19:05]: keepalive message is received
[2022-10-29 14:19:05]: keepalive message is received
[2022-10-29 14:19:08]: keepalive message is received
[2022-10-29 14:19:11]: keepalive message is received
[2022-10-29 14:19:11]: keepalive message is received
[2022-10-29 14:19:14]: keepalive message is received
[2022-10-29 14:19:17]: keepalive message is received
[2022-10-29 14:19:17]: keepalive message is received
[2022-10-29 14:19:20]: keepalive message is received
[2022-10-29 14:19:23]: keepalive message is received
[2022-10-29 14:19:23]: keepalive message is received
[2022-10-29 14:19:29]:gs_basebackup: base backup successfully
[omm@db1 db1]$
2、创建测试数据（还原点 recovery_target_name ）
miao=> create table t1 (id int,tm timestamp,LSN varchar(20));
insert into t1 values(1,now(),'Started');
select * from t1;
select * from pg_switch_xlog();
CREATE TABLE
miao=> insert into t1 values(1,now(),'Started');
INSERT 0 1
miao=> select * from t1;
id | tm | lsn
----+----------------------------+---------
1 | 2022-10-29 14:22:52.273009 | Started
(1 row)

miao=> select * from pg_switch_xlog();
pg_switch_xlog

4/EC0029F8
(1 row)

miao=>
-- 创建一个还原点 restore_point_1

miao=> select pg_create_restore_point('restore_point_1');
pg_create_restore_point

4/ED00EF40
(1 row)
3、第 2 次插入数据（时间 recovery_target_time ）
miao=> insert into t1 values(2,now(),'First Insert');
INSERT 0 1
miao=> select * from t1;
id | tm | lsn
----+----------------------------+--------------
1 | 2022-10-29 14:22:52.273009 | Started
2 | 2022-10-29 14:26:10.55628 | First Insert
(2 rows)

miao=> select pg_switch_xlog();
select now();
pg_switch_xlog

4/ED017848
(1 row)

miao=> select now();
now

2022-10-29 14:26:10.754567+08
(1 row)

4、第 3 次插入数据（LSN recovery_target_lsn ）
miao=> insert into t1 values(3,now(),'Second Insert');
INSERT 0 1
miao=> select * from t1;
id | tm | lsn
----+----------------------------+---------------
1 | 2022-10-29 14:22:52.273009 | Started
2 | 2022-10-29 14:26:10.55628 | First Insert
3 | 2022-10-29 14:31:13.220708 | Second Insert
(3 rows)

miao=> select pg_switch_xlog();

select * from pg_current_xlog_location();
pg_switch_xlog

4/EE0230C0
(1 row)

miao=>
miao=> select * from pg_current_xlog_location();
pg_current_xlog_location

4/EF000148
(1 row)
5、模拟故障删除目录
[omm@db1 gs_pitr]$ cp /archivelog/* /home/omm/gs_pitr/archivelog/

[omm@db1 gs_pitr]$ gs_om -t stop
Stopping cluster.

Successfully stopped cluster.

End stop cluster.
[omm@db1 gs_pitr]$ rm -fr /mogdb/data/db1/
6、第一阶段恢复（按照还原点进行恢复）
[omm@db1 data]$ mkdir /mogdb/data/db1/
[omm@db1 db1]$ cp -fr /home/omm/gs_pitr/* /mogdb/data/db1/

[omm@db1 db1]$ vi recovery.conf

restore_command = 'cp /mogdb/data/db1/archivelog/%f %p'
recovery_target_name = 'restore_point_1' ## 恢复到指定的还原点restore_point_1,此时后面2条数据
recovery_target_inclusive = true
[omm@db1 db1]$ gs_om -t start
Starting cluster.

[SUCCESS] db1
2022-10-29 15:22:47.061 635cd4c6.1 [unknown] 140295007823424 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2022-10-29 15:22:47.064 635cd4c6.1 [unknown] 140295007823424 [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 (1967 Mbytes) is larger.

Successfully started.

[omm@db1 db1]$ gsql -d miao -p 26000 -U dbmt -r
Password for user dbmt:
gsql ((MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

miao=> \d
List of relations
Schema | Name | Type | Owner | Storage
--------+--------------------------------+-------+-------+------------------------------------------------------
dbmt | b1921101_bak | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | cc_chx | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | cd_material | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | customer_me_degree_wbinfo | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | customer_me_degree_wbinfo_test | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | erp_i_contract_class | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | scm_warning_reauidt_detail | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | sm_filemanagerconfig | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | t_o | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | t_o_t | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | t_p_t | table | dbmt | {orientation=row,compression=no,storage_type=USTORE}
dbmt | temp1 | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | tiji_update | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | tmp_mis_code_zq | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | tmp_mul_stock_detail_20200614 | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | tmp_output_bill_20190821 | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | tmp_project_party | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | tmp_unit_vol | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | tmp_user_rep | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | ur_s_role | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | w_work_cooperate_gjback0721 | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
dbmt | yang_tempate3 | table | dbmt | {orientation=row,compression=no,storage_type=ustore}
public | t1 | table | omm | {orientation=row,compression=no,storage_type=USTORE}
(105 rows)

miao=> select * from t1;
id | tm | lsn
----+----------------------------+---------
1 | 2022-10-29 14:22:52.273009 | Started
(1 row)

miao=>
7、 第二阶段恢复(按照时间进行还原)
[omm@db1 db1]$ vi recovery.conf
restore_command = 'cp /mogdb/data/db1/archivelog/%f %p'
recovery_target_time = '2022-10-29 14:26:44'
recovery_target_inclusive = true
~

"recovery.conf" [New] 3L, 137C written
[omm@db1 db1]$ gs_om -t start
Starting cluster.

[SUCCESS] db1
2022-10-29 15:22:47.061 635cd4c6.1 [unknown] 140295007823424 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2022-10-29 15:22:47.064 635cd4c6.1 [unknown] 140295007823424 [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 (1967 Mbytes) is larger.

Successfully started.
[omm@db1 db1]$ gsql -d miao -U dbmt -p 26999 -r
failed to connect /opt/mogdb/tools/omm_mppdb:26999.
[omm@db1 db1]$ gsql -d miao -U dbmt -p 26000 -r
Password for user dbmt:
gsql ((MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

miao=> select * from t1;
id | tm | lsn
----+----------------------------+--------------
1 | 2022-10-29 14:22:52.273009 | Started
2 | 2022-10-29 14:26:10.55628 | First Insert
(2 rows)
8、 第三阶段恢复(按照 lsn 进行恢复)
restore_command = 'cp /mogdb/data/db1/archivelog/%f %p'
recovery_target_lsn = '4/EE0230C0'
recovery_target_inclusive = true

[omm@db1 archivelog]$ gs_om -t stop
Stopping cluster.

[omm@db1 db1]$ gs_om -t start
Starting cluster.

[SUCCESS] db1
2022-10-29 16:08:54.842 635cdf96.1 [unknown] 140627704972864 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2022-10-29 16:08:54.887 635cdf96.1 [unknown] 140627704972864 [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 (1967 Mbytes) is larger.

Successfully started.
[omm@db1 db1]$ gsql -d miao -U dbmt -p 26000
Password for user dbmt:
gsql ((MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

miao=> select 8 from t1;
?column?

    8
    8
    8

(3 rows)

miao=> select * from t1;
id | tm | lsn
----+----------------------------+---------------
1 | 2022-10-29 14:22:52.273009 | Started
2 | 2022-10-29 14:26:10.55628 | First Insert
3 | 2022-10-29 14:31:13.220708 | Second Insert
(3 rows)

miao=>
9、 手动结束 PITR 状态
miao=> select pg_is_in_recovery();
pg_is_in_recovery

t
(1 row)

miao=> select pg_xlog_replay_resume();
pg_xlog_replay_resume

(1 row)
[omm@db1 db1]$ gsql -d miao -U dbmt -p 26000 -r
Password for user dbmt:
gsql ((MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

miao=> select pg_is_in_recovery();
pg_is_in_recovery

f
(1 row)

miao=>

标签：10,14,--,PITR,MogDB,29,2022,db1,dbmt
From： https://www.cnblogs.com/helloopenGauss/p/18093921

相关文章

  • 代码随想录算法训练营第五十五天 | 583. 两个字符串的删除操作， 72. 编辑距离
    72.编辑距离 已解答中等 相关标签相关企业 给你两个单词 word1 和 word2， 请返回将 word1 转换成 word2 所使用的最少操作数 。你可以对一个单词进行如下三种操作：插入一个字符删除一个字符替换一个字符 示例1：输入：word1="horse"......
  • Linux脚本首行中的#!是什么作用？为文件指定解释器
    起因今天和小伙伴日常在群内吹水的时候，有群友问了这样一个问题：脚本文件第一行加个#!/usr/bin/bash有什么用呢？其实，除了这种之外，还有很多其他写法，比如：#!/bin/bash#!/usr/bin/envbash#!/bin/awk这些写法大同小异，但很多同学像我一样，在之前编写shell脚本的时候顺手就抄过......
  • 闲话
    dz前天语录（虽迟但到）中午吃饭dz坐郭航语旁边dz：中午我不回宿舍了郭：知道了，你跟我说干嘛dz：你不是班长吗我就跟你说一下郭：那我不同意dz：不同意就不同意呗，反正我跟你说了放张好图不败的英雄-唐俊迪/张志林为了心中的梦浴血奋战像一阵狂风黑夜会释放光明不败的英雄......
  • 简单mips题目尝试
    0x01前言mips是另一种不同的架构何指令集，推荐使用ghidra和ida插件进行反汇编，其中的知识我就不多赘述，因为我也一知半解Orz0x02简单的ctf题目尝试[UTCTF2020]babymips首先利用ghidra反汇编一下程序看看，按g可以跳转main函数看看 发现具体逻辑将一段东西赋值给austak_68，然......
  • vivado设置为中文
    方法步骤第一步1.当我们进入到VisualStudioCode，中之后，你可以看到该页面是英文来显示的，那么我们可以点击左侧的箭头所指的图标。第二步2.接着进入到页面中，在搜索框中输入【简体中文】，接着按下键盘上的回车键进行搜索，搜索出来的简体中文，点击【install】按钮进行安装。......
  • openGauss Cluster Manager RTO Test
    一、环境介绍软件环境类别 版本 下载链接 备注OS openEuler20.03(LTS) https://repo.openeuler.org/openEuler-20.03-LTS/ISO/aarch64/openEuler-20.03-LTS-aarch64-dvd.iso 操作系统BenchmarkSQL 5.0 https://sourceforge.net/projects/benchmarksql/files/latest/downloa......
  • ElementUI的Table控件，合并列
    x//计算需要合并的列for(letk=0;k<that.tableData.length;k++){//判断当前类型是否与下一个类型一致，一致则两个单元格合并。if((k+1)<that.tableData.length&&......
  • # 基于BEV的自动驾驶会颠覆现有的自动驾驶架构吗
    基于BEV的自动驾驶会颠覆现有的自动驾驶架构吗引言很多人都有这样的疑问--基于BEV(BirdsEyeView)的自动驾驶方案是什么？这个问题，目前学术界还没有统一的定义，但从我的开发经验上，尝试做一个解释：以鸟瞰视角为基础形成的端到端的自动驾驶算法和系统。感知模块是最为重要的自动驾......
  • 平芯微PW1605，24V,30V,48V可调过压保护芯片，1A-5A可调限流芯片
    一般描述PW1605 是一款电流限制开关，具有可编程输入过压保护和输出电压箝位功能。集成保护N沟道FET具有极低的RDS（ON）功能，PW1605有助于降低正常工作期间的功率损耗。可编程软启动时间控制启动期间输出电压的压摆率。独立的使能控制允许复杂的系统排序控制。该器件还集成了......
  • Drop 、Delete、Truncate的区别是什么
    Drop、Delete、Truncate的区别是什么？DROP删除表结构和数据，truncate和delete只删除数据truncate操作，表和索引所占用的空间会恢复到初始大小；delete操作不会减小表或索引所占用的空间；drop操作释放表或索引的占用空间。truncate和drop是DDL，操作立即生效，delete是DML，......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99