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

MogDB 学习笔记之 -- PITR恢复

时间：2024-02-19 19:46:47浏览次数：15  
标签：10 14 -- PITR MogDB 29 2022 db1 dbmt

概念描述

背景信息

当数据库崩溃或希望回退到数据库之前的某一状态时，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 192.168.3.24 -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/xinxin1222/p/18021804

相关文章

  • password_encryption_type 和 pg_hba.conf 不匹配导致用户连不上
    问题概述xxx客户新上一套opengauss数据库，在测试中用户输入正确的密码，提示用户密码错误，导致用户被锁问题原因password_encryption_type和pg_hba.conf不匹配导致用户连不上模拟问题因没有opengauss的环境，测试环境选择Mogdb1、准备测试环境，修改password_encryption_type。......
  • java的getResource方法 总结一下 在Java中获取资源的时候，经常用到getResource和getRes
    java的getResource方法总结一下在Java中获取资源的时候，经常用到getResource和getResourceAsStream，本文总结一下这两种获取资源文件的路径差异1.前言在Java中获取资源的时候，经常用到getResource和getResourceAsStream，本文总结一下这两种获取资源文件的路径差异。2.Class.get......
  • 系统科学方法概论第三章读后感
    信息方法是一种研究方法，它运用信息的概念，将系统的运动过程看作是信息的传递和转换过程。这种方法完全撇开系统的具体运动形式，而是关注系统如何通过获取、传递、运用和存储信息来实现其目的。信息方法的特点是以信息为基础，从信息的整个流程进行综合考察，以获得对系统的整体性认识。......
  • oceanbase 社区版安装
    一、环境准备节点类别主机名IPOBSERVERobserver1192.168.3.41OBSERVERobserver2192.168.3.42OBSERVERobserver3192.168.3.43ocpocpserver192.168.3.47中控机obcontrol192.168.3.48二、名词解析中控机：存储Oceanbase数据库安装包和集群配置信......
  • mogdb的一主两备
    一、环境准备节点类别主机名IP主节点mogdb1192.168.3.68备节点1mogdb2192.168.3.69备节点2mogdb3192.168.3.70二、关闭防火墙和透明大页1、修改操作系统配置1)关闭SELINUX修改/etc/selinux/config文件中的“SELINUX”值为“disabled”2)关闭......
  • Codeforces Round 927 (Div. 3)
    CodeforcesRound927(Div.3)A-ThornsandCoins解题思路:出现连续两个障碍之前，所有金币都能拿到。代码:#include<bits/stdc++.h>usingnamespacestd;usingll=longlong;usingpii=pair<ll,ll>;#definefifirst#definesesecondusingi128=__int128_t;......
  • Oracle 低版本客户端连接19C报错ORA-28040
    适用范围12.2+问题概述客户使用Oracle11.2客户端连接Oracle19c的时候，报错:ORA-28040:NomatchingauthenticationprotocolORA-28040:没有匹配的验证协议问题原因原因客户端与服务器的没有匹配的认证协议解决方案1、在数据库服务器上的$ORACLE_HOME/network/admin/sql......
  • 系统科学方法概论第四章读后感
    在我接触并深入研究计算机控制方法后，我对其在现代社会中的重要性有了更深的认识。当我阅读关于计算机控制方法的书籍时，我被其精妙而复杂的设计所吸引，这不仅是一门科学，更是一门艺术。对于我来说，计算机控制方法不仅是工程领域的专业知识，更是一种解决现实问题的有效工具。通过学习和......
  • dismount ASM磁盘组，影响未使用的其它ASM磁盘组
    问题概述登录数据库,查看活动会话，发现大量librarycachelock,logfileswitch(archivingneeded),归档失败，redolog无法重用。问题原因备份一体机每次备份后会DISMOUNTBAKDG，这个动作为导致数据库实例将"卸载"未使用的ASM磁盘组,ARCHDG在这种机制下被DISMOUNT,当数据库需......
  • rlwrap解决opengauss,pg,oracle上下左右及回退乱码
    安装下rlwrap,最新版本是0.43下载地址https://fossies.org/linux/privat/rlwrap-0.43.tar.gz/安装rlwraptar-zxvfrlwrap-0.43.tar.gz[root@openguesssoft]#lltotal298864-rw-------1ommdbgrp1234Apr810:30cluster_config_opendb.xmldrwx------.15rootroot......

赞助商

阅读排行

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

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

Copyright © 2020-2023 IPS99 版权所有 IPS99