KingbaseES V8R3 备份恢复系列之 -- sys_rman备份过程分析

news/2024/7/7 19:48:24

​

案例说明：
本案例通过对KingbaseES sys_rman物理备份过程的详细描述，有助于在执行sys_rman过程中发生故障的分析。适用版本： KingbaseES V8R3

一、sys_rman执行过程简介

1. 调用select sys_start_backup()开始备份，sys_start_backup()进行创建基础备份的准备工作，包括：

1)判断WAL归档是否已开启
如果没有开启,备份依然会进行，但在备份结束后会显示提醒信息：
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup.

2)强制进入全页写模式
判断当前配置是否为全页写模式，如果当前full_page_writes设置为off，则强制更改为on

TEST=# show full_page_writes ;
 full_page_writes
------------------
 on
(1 row)

3)创建一个检查点

4)查看当前对应的wal(xlog)日志文件及lsn

select lsn::text as lsn,sys_catalog.sys_xlogfile_name(lsn)::text as wal_segment_name
     from sys_catalog.sys_start_backup('sys_rman backup start at ' ||current_timestamp,false,false) as lsn;

2、执行系统层面的文件拷贝3、调用SELECT * FROM sys_stop_backup()结束备份
1)执行wal(xlog)日志文件归档
2)归档完成后，备份结束

二、sys_rman执行备份分析

Tips：
可以通过以下两个参数，查看sys_rman执行的详细过程

1、查看sys_rman全备过程

执行备份初始化：

[kingbase@node101 bin]$ ./sys_rman -U SYSTEM -W 123456 -d TEST 
-B /data/kingbase/bk/v8r3 -D /home/kingbase/cluster/HAR3/db/data  init

执行备份：

[kingbase@node101 bin]$ ./sys_rman -U SYSTEM -W 123456 -d TEST -B /data/kingbase/bk/v8r3 -b full -D /home/kingbase/cluster/HAR3/db/data -v --progress backup

# 获取当前数据库配置参数
LOG: (query) SELECT current_setting($1)
LOG:    (param:0) = block_size
LOG: (query) SELECT current_setting($1)
LOG:    (param:0) = wal_block_size
LOG: (query) SELECT sys_is_in_recovery()

# 备份开始调用sys_backup_start()
LOG: ========================================
LOG: backup start
LOG: ----------------------------------------
# configuration
BACKUP_MODE=FULL
LOG: ----------------------------------------
LOG: backup destination is initialized
LOG: database backup start
LOG: (query) SELECT proname FROM sys_proc WHERE proname='pg_ptrack_clear'
LOG: (query) SELECT sys_start_backup($1, $2)
LOG:    (param:0) = 2023-03-29 13:48:16 with sys_rman
LOG:    (param:1) = true

# 获取当前wal日志文件及lsn
LOG: wait_archive_lsn() wait for lsn:3959423016 /data/kingbase/bk/v8r3/wal/0000000500000000000000EB

# 创建相关目录并开始copy文件
LOG: make directory "base"
LOG: make directory "base/1"
.......
LOG: make directory "sys_twophase"
LOG: make directory "sys_xlog"

WARNING: Start thread num:2917
LOG: (1/2917) base/14946/2830_vm
LOG: copied 0
Progress 0/2887LOG: (2/2917) base/14943/3348
LOG: copied 0
Progress 1/2887LOG: (3/2917) base/14943/3348_vm
LOG: copied 0
Progress 2/2887LOG: (4/2917) base/16392/14808
LOG: copied 0
Progress 3/2887LOG: (5/2917) base/14946/2830
LOG: copied 0
Progress 4/2887LOG: (6/2917) base/14946/2613_vm
LOG: copied 0
.......
LOG: (2888/2917) global/sys_internal.init
LOG: (2889/2917) kingbase.auto.conf
......
LOG: (2907/2917) sys_multixact/offsets/0000
# 备份结束
LOG: backup RS9PGG is valid

2、查看sys_rman增量备份过程

1、执行事务操作(产生增量数据)

PROD=# select count(*) from t1;
  COUNT
----------
 10010000
(1 row)

PROD=# create table t2 as select * from t1;
SELECT 10010000
PROD=# drop table t1;
NOTICE:  view V_T1 depends on table T1
DROP TABLE

2、查看增量备份过程

如下图所示：最近全量备份及结束对应的LSN：

每次块的变化都包含了LSN的修改，那么也即是说，我们可以通过第一次备份开始时的全局LSN，以及当前需要备份的数据的page LSN来判断此页是否发生过修改。

[kingbase@node101 bin]$ ./sys_rman -U SYSTEM -W 123456 -d TEST -B /data/kingbase/bk/v8r3 -b page -D /home/kingbase/cluster/HAR3/db/data -v --progress backup

# 获取当前数据库配置参数
LOG: (query) SELECT current_setting($1)
LOG:    (param:0) = block_size
LOG: (query) SELECT current_setting($1)
LOG:    (param:0) = wal_block_size
LOG: (query) SELECT sys_is_in_recovery()

# 备份开始调用sys_backup_start()
LOG: ========================================
LOG: backup start
LOG: ----------------------------------------
# configuration
BACKUP_MODE=PAGE
LOG: ----------------------------------------
LOG: backup destination is initialized
LOG: database backup start
LOG: (query) SELECT proname FROM sys_proc WHERE proname='pg_ptrack_clear'
LOG: (query) SELECT sys_start_backup($1, $2)
LOG:    (param:0) = 2023-03-29 14:07:05 with sys_rman
LOG:    (param:1) = true

# 获取当前wal日志文件及lsn
LOG: wait_archive_lsn() wait for lsn:4731347992 /data/kingbase/bk/v8r3/wal/000000050000000100000019
LOG: backup only the page that there was of the update from LSN(0/EC000028)
LOG: (query) SET client_min_messages = warning;
LOG: (query) SELECT * FROM sys_switch_xlog()
LOG: wait_for_archive(): tli=5 lsn=1/1A0D34D0
LOG: wait_for_archive() wait for /home/kingbase/cluster/HAR3/db/data/sys_xlog/archive_status/00000005000000010000001A.ready
LOG: (query) SELECT txid_current();
LOG: get_xid():2232
LOG: wait_for_archive() .ready deleted in 1 try
LOG: extractPageMap

# 通过读取wal日志获取变化的数据块，执行增量备份
LOG: current_tli:5
LOG: prev_backup->start_lsn: 0/EC000028
LOG: current.start_lsn: 1/1A02A418
LOG: opening WAL segment "/data/kingbase/bk/v8r3/wal/0000000500000000000000EC"
LOG: opening WAL segment "/data/kingbase/bk/v8r3/wal/0000000500000000000000ED"
LOG: opening WAL segment "/data/kingbase/bk/v8r3/wal/0000000500000000000000EE"
LOG: opening WAL segment "/data/kingbase/bk/v8r3/wal/0000000500000000000000EF"
.......
LOG: opening WAL segment "/data/kingbase/bk/v8r3/wal/000000050000000100000018"
LOG: opening WAL segment "/data/kingbase/bk/v8r3/wal/000000050000000100000019"
LOG: opening WAL segment "/data/kingbase/bk/v8r3/wal/00000005000000010000001A"
LOG: make directory "base"
.......

# 创建相关目录并开始copy文件(只拷贝有变化块的数据文件)
LOG: make directory "sys_xlog"
WARNING: Start thread num:2916
LOG: (1/2916) base/14946/2830_vm
LOG: skip
LOG: (2/2916) base/14943/3348
LOG: skip
.....
LOG: (2615/2916) base/16392/2703
LOG: copied 8196
Progress 4/2886LOG: (2616/2916) base/16392/2696
LOG: copied 8196
Progress 5/2886LOG:
LOG: (2681/2916) base/16392/2662
LOG: copied 8196
LOG: (2686/2916) base/16392/3455
LOG: copied 8196
LOG: (2681/2916) base/16392/2662
LOG: copied 8196
LOG: (2686/2916) base/16392/3455
LOG: copied 8196
.......
LOG: (2916/2916) base/16392/41366
LOG: copied 443477364
Progress 20/2886
LOG: (query) SET client_min_messages = warning;

# 调用sys_stop_backup()结束备份
LOG: (query) SELECT * FROM sys_stop_backup()

# 执行wal日志归档
LOG: wait_for_archive(): tli=5 lsn=1/1DDE0AA0
LOG: wait_for_archive() wait for /home/kingbase/cluster/HAR3/db/data/sys_xlog/archive_status/00000005000000010000001D.ready
LOG: (query) SELECT txid_current();
LOG: get_xid():2233
LOG: wait_for_archive() .ready deleted in 0 try

# 备份结束
LOG: database backup completed(Backup: 443704210)
LOG: ========================================
LOG: all backup completed(read: 443704210 write: 443704210)
LOG: ========================================
INFO: validate: RS9QBT backup and archive log files by CRC
LOG: database files...
LOG: (2/2916) backup_label
LOG: (2427/2916) base/16392/1247
......
LOG: (2796/2916) base/16392/41366
LOG: (2885/2916) global/sys_control
LOG: (2892/2916) sys_clog/0000
LOG: (2900/2916) sys_logical/replorigin_checkpoint
LOG: backup RS9QBT is valid

3、查询增量备份copy的数据文件

1)如下图所示，被拷贝数据文件

2)查询数据文件对应的object

PROD=# select relname,relfilenode from sys_class where sys_relation_filepath(oid) like 'base/16392/41366' ;
 RELNAME | RELFILENODE
---------+-------------
 T2      |       41366
(1 row)

---如上所示，数据文件对应的t2表，是上次全备后新增加的object。

4、查看增量备份访问的wal日志信息

-rw------- 1 kingbase kingbase 16M Mar 29 13:48 0000000500000000000000EC
-rw------- 1 kingbase kingbase 16M Mar 29 14:06 0000000500000000000000ED
.......
-rw------- 1 kingbase kingbase 16M Mar 29 14:06 000000050000000100000017
-rw------- 1 kingbase kingbase 16M Mar 29 14:07 000000050000000100000018
-rw------- 1 kingbase kingbase 16M Mar 29 14:07 000000050000000100000019
-rw------- 1 kingbase kingbase 16M Mar 29 14:07 00000005000000010000001A

---以上wal日志，是在增量备份过程中读取的日志文件,从起始lsn所对应的wal日志开始。

三、查看和清理冗余备份

1、查看备份信息

[kingbase@node101 bin]$ ./sys_rman -U SYSTEM -W 123456 -d TEST  -B /data/kingbase/bk/v8r3 show
==========================================================================================================
ID       Recovery time        Mode          Current/Parent TLI  Time            Data  start_lsn  stop_lsn Status
==========================================================================================================
RS9PB9   2023-03-29 13:45:13  FULL           5 / 4              4s           525MB  0/EA000028  0/EA000130  OK
RS9KOV   2023-03-29 12:05:22  FULL           5 / 4              3s           525MB  0/E8000028  0/E80000F8  OK
RS9KI2   2023-03-29 12:01:19  FULL           5 / 4              5s           525MB  0/E6000028  0/E6000130  OK

2、清理冗余备份

[kingbase@node101 bin]$ ./sys_rman -U SYSTEM -W 123456 -d TEST  -B /data/kingbase/bk/v8r3 delete RS9PB9
INFO: delete: RS9PB9 2023-03-29 13:45:13
[kingbase@node101 bin]$ ./sys_rman -U SYSTEM -W 123456 -d TEST  -B /data/kingbase/bk/v8r3 delete RS9KOV
INFO: delete: RS9KOV 2023-03-29 12:05:22
[kingbase@node101 bin]$ ./sys_rman -U SYSTEM -W 123456 -d TEST  -B /data/kingbase/bk/v8r3 delete RS9KI2
INFO: delete: RS9KI2 2023-03-29 12:01:19

[kingbase@node101 bin]$ ./sys_rman -U SYSTEM -W 123456 -d TEST  -B /data/kingbase/bk/v8r3 show
==========================================================================================================
ID       Recovery time        Mode          Current/Parent TLI  Time            Data  start_lsn  stop_lsn Status
==========================================================================================================

四、备份故障案例

如下所示：备份故障信息，是因为在第一次执行sys_rman备份，未做init的备份目录的初始化导致。

五、总结
通过对执行sys_rman物理备份的过程分析，可以看出，对于增量备份是通过分析wal日志获取到从上次全备以来发生变化的块；对于全备后，事务量比较大的数据库环境，执行增量备份可能效率会比较慢，需要读取大量的wal日志，来获取变化的块，然后拷贝对应的数据文件。


http://lihuaxi.xjx100.cn/news/1151736.html

相关文章

SOFA Weekly｜SOFA 大事记、Layotto 社区会议回顾与预告、社区本周贡献

SOFA Weekly｜SOFA 大事记、Layotto 社区会议回顾与预告、社区本周贡献

SOFA WEEKLY | 每周精选 筛选每周精华问答，同步开源进展 欢迎留言互动～ SOFAStack（Scalable Open Financial Architecture Stack）是蚂蚁集团自主研发的金融级云原生架构，包含了构建金融级云原生架构所需的各个组件&am…
阅读更多...
java selenium关闭https 证书认证

java selenium关闭https 证书认证

直接上代码： DesiredCapabilities capabilities DesiredCapabilities.chrome(); ChromeOptions options new ChromeOptions(); options.addArguments("test-type"); options.addArguments("start-maximized"); options.addArguments("u…
阅读更多...
ARM处理器概论与组织

ARM处理器概论与组织

目录 1.ARM产品系列 2.体系结构 3.ARM指令集 定义： ARM的指令集： 4.编译原理 5.ARM存储模型 6.ARM的8种工作方式 ARM（Advanced RISC Machines）有三种含义 一个公司的名称、一类处理器的通称、一种技术，我们在这…
阅读更多...
nginx 502性能调优---已解决

nginx 502性能调优---已解决

上半部分是调优，下半部分是解决502的问题 php调优： memory_limit：根据适当情况上调，我这里是提升一倍（脚本内存限制） max_children：适当调整（进程数） request_terminate…
阅读更多...
有名的国外CRM系统有哪些？如何选择？

有名的国外CRM系统有哪些？如何选择？

CRM系统可以帮助企业获得更多地潜在客户，优化销售流程，提高工作效率，增强客户服务水平，是如今企业不可或缺的工具。那么，国外CRM的知名厂商有哪些？下面小编来为大家进行推荐。 Zoho CRM： Zoho…
阅读更多...
Rust每日一练(Leetday0005) 罗马数字、公共前缀、三数之和

Rust每日一练(Leetday0005) 罗马数字、公共前缀、三数之和

目录 13. 罗马数字转整数 Roman to Integer 🌟 14. 最长公共前缀 Longest Common Prefix 🌟 15. 三数之和 3Sum 🌟🌟 🌟 每日一练刷题专栏 🌟 Rust每日一练 专栏 Golang每日一练 专栏 Python每日…
阅读更多...
手动输入域账号自动禁用windows域用户的脚本

手动输入域账号自动禁用windows域用户的脚本

禁用windows域用户的脚本，需求： 1、显示该域用户的创建时间，最后登录时间和存放的OU， 2、将该账号禁用； 3、禁用后重新返回交互界面等待输入新的域账号。 Powershell脚本内如下： # 无限循环等待用户的输入…
阅读更多...
李彦宏一句“车水马龙”，中国AI的一幕天地宽广

李彦宏一句“车水马龙”，中国AI的一幕天地宽广

“什么叫生成式？比如说，给我画一幅车水马龙的图片。这种东西，过去人们不觉得是人工智能应该做的事，现在可以做了。”5月18日，百度CEO李彦宏在天津举办的第七届世界智能大会上演讲时，说了这么一句话。 不了解…
阅读更多...
最新文章

Copyright @ 2022~2023