首页 > 其他分享 >KingbaseES V8R3 备份恢复案例 -- sys_rman物理备份异机恢复

KingbaseES V8R3 备份恢复案例 -- sys_rman物理备份异机恢复

时间：2023-09-19 18:59:25浏览次数：28  
标签：V8R3 15 04 -- 18 备份 kingbase 2023 CST

案例说明：
在生产环境通过sys_rman执行了物理备份后，需要在异机构建测试环境，本案例描述了通过物理备份异机恢复的详细过程及操作。

适用版本：
KingbaseES V8R3

节点信息：

[kingbase@node102 bin]$ cat /etc/hosts
......
192.168.1.101   node101    # 生产节点
192.168.1.102   node102    # 测试节点

一、生产库执行sys_rman物理备份

1、生产环境相关配置参数

# 开启归档
test=# show archive_mode ;
 archive_mode
--------------
 on
(1 row)

# 归档文件存储路径
test=# show archive_dest ;
       archive_dest
--------------------------
 /data/kingbase/arch/c290
(1 row)

# 归档配置
test=# show archive_command ;
                              archive_command
----------------------------------------------------------------------------
 test ! -f /data/kingbase/arch/c290/%f && cp %p /data/kingbase/arch/c290/%f
(1 row)

# wal日志配置
test=# show wal_level ;
 wal_level
-----------
 replica
(1 row)

2、执行sys_rman物理备份

1）备份初始化

[kingbase@node101 ~]$ mkdir -p /data/kingbase/bk/c290
[kingbase@node101 bin]$ ./sys_rman -U system -W 123456 -d test -D /opt/Kingbase/ES/C290/data -B /data/kingbase/bk/c290/ init

2）执行数据库全备

[kingbase@node101 bin]$ ./sys_rman -U system -W 123456 -d test -D /opt/Kingbase/ES/C290/data -B /data/kingbase/bk/c290/ -b full backup
INFO: validate: RTATKP backup and archive log files by CRC

[kingbase@node101 bin]$ ./sys_rman -U system -W 123456 -d test -D /opt/Kingbase/ES/C290/data -B /data/kingbase/bk/c290/ validate
INFO: validate: RTATKP backup and archive log files by CRC
INFO: backup validation completed successfully

3）执行增量备份

# 事务操作
prod=# create table t2 as select * from t1;
SELECT 10000

prod=# select count(*) from t2;
 count
-------
 10000
(1 row)

# 生成检查点（在恢复时，缩短recovery时间）。
prod=# select sys_switch_xlog();
 sys_switch_xlog
-----------------
 0/70000A0
(1 row)

prod=# checkpoint;
CHECKPOINT

# 执行正增量备份
[kingbase@node101 bin]$ ./sys_rman -U system -W 123456 -d test -D /opt/Kingbase/ES/C290/data -B /data/kingbase/bk/c290/ -b page backup
INFO: validate: RTATU1 backup and archive log files by CRC

4） 查看备份信息

[kingbase@node101 bin]$ ./sys_rman -U system -W 123456 -d test -D /opt/Kingbase/ES/C290/data -B /data/kingbase/bk/c290/ show
==========================================================================================================
ID       Recovery time        Mode          Current/Parent TLI  Time            Data  start_lsn  stop_lsn Status
==========================================================================================================
RTATU1   2023-04-18 14:54:03  PAGE           1 / 0              2s           628kB  0/9000028  0/A000078  OK
RTATKP   2023-04-18 14:48:27  FULL           1 / 0              2s            80MB  0/3000028  0/3000130  OK

5）查看备份文件信息

[kingbase@node101 c290]$ ls -lh
total 4.0K
drwx------ 4 kingbase kingbase 32 Apr 18 14:54 backups
-rw-r--r-- 1 kingbase kingbase 41 Apr 18 14:47 sys_rman.conf
lrwxrwxrwx 1 kingbase kingbase 25 Apr 18 15:40 wal -> /data/kingbase/arch/c290/

二、sys_rman执行异机恢复

Tips:
物理备份的恢复一般分为两个步骤

restore： 还原备份数据文件到data目录下
reocovery： 启动实例后从最近的检查点开始应用xlog日志到一致性状态后，开启数据库。

1、准备数据库环境

  1）在测试主机安装和生产主机相同的数据库版本
  2）创建相同的备份存储路径和xlog日志归档路径
  3）归档及wal日志配置和生产库相同

2、复制生产库备份到测试主机
[kingbase@node101 c290]$ scp -r * node102:/data/kingbase/bk/c290/

3、执行sys_rman恢复

1）restore备份到data目录下

# 备份测试库data目录
[kingbase@node102 c290]$ cd /opt/Kingbase/ES/C290/
[kingbase@node102 C290]$ mv data data.bk

# 创建data目录并授权
[kingbase@node102 bin]$ mkdir -p /opt/Kingbase/ES/C290/data
[kingbase@node102 bin]$ chmod 700 /opt/Kingbase/ES/C290/data

# 在测试库上查看备份信息
[kingbase@node102 bin]$ ./sys_rman -U system -W 123456 -d test -D /opt/Kingbase/ES/C290/data -B /data/kingbase/bk/c290/ show
==========================================================================================================
ID       Recovery time        Mode          Current/Parent TLI  Time            Data  start_lsn  stop_lsn Status
==========================================================================================================
RTATU1   2023-04-18 14:54:03  PAGE           1 / 0              2s           628kB  0/9000028  0/A000078  OK
RTATKP   2023-04-18 14:48:27  FULL           1 / 0              2s            80MB  0/3000028  0/3000130  OK

# 执行sys_rman restore
[kingbase@node102 bin]$ ./sys_rman -U system -W 123456 -d test -D /opt/Kingbase/ES/C290/data -B /data/kingbase/bk/c290/ restore
INFO: validate: RTATKP backup and archive log files by SIZE
INFO: validate: RTATU1 backup and archive log files by SIZE
INFO: restore complete. Recovery starts automatically when the Kingbase server is started.

如下图所示，执行restore：

2）启动测试库实例执行recovery

# 启动数据库实例
[kingbase@node102 bin]$ ./sys_ctl start -D ../../data
server starting
.......

# 查看sys_log日志
[kingbase@node102 sys_log]$ tail -1000 kingbase-2023-04-18_154713.log
2023-04-18 15:47:13 CST LOG:  database system was interrupted; last known up at 2023-04-18 14:54:01 CST
2023-04-18 15:47:13 CST LOG:  creating missing WAL directory "sys_xlog/archive_status"
2023-04-18 15:47:13 CST LOG:  starting archive recovery
2023-04-18 15:47:13 CST LOG:  restored log file "000000010000000000000009" from archive
2023-04-18 15:47:13 CST LOG:  redo starts at 0/9000028
2023-04-18 15:47:13 CST LOG:  redo wal segment count 1
2023-04-18 15:47:13 CST LOG:  restored log file "00000001000000000000000A" from archive
2023-04-18 15:47:13 CST LOG:  consistent recovery state reached at 0/A000078
2023-04-18 15:47:13 CST LOG:  restored log file "00000001000000000000000B" from archive
2023-04-18 15:47:13 CST LOG:  restored log file "00000001000000000000000C" from archive
cp: cannot stat ‘/data/kingbase/bk/c290//wal/00000001000000000000000D’: No such file or directory
2023-04-18 15:47:13 CST LOG:  complete: 1/1
2023-04-18 15:47:13 CST LOG:  redo done at 0/C0000D0
2023-04-18 15:47:13 CST LOG:  last completed transaction was at log time 2023-04-18 14:54:03.704661+08
2023-04-18 15:47:13 CST LOG:  restored log file "00000001000000000000000C" from archive
cp: cannot stat ‘/data/kingbase/bk/c290//wal/00000002.history’: No such file or directory
2023-04-18 15:47:13 CST LOG:  selected new timeline ID: 2
2023-04-18 15:47:13 CST LOG:  archive recovery complete
cp: cannot stat ‘/data/kingbase/bk/c290//wal/00000001.history’: No such file or directory
2023-04-18 15:47:13 CST LOG:  MultiXact member wraparound protections are now enabled
2023-04-18 15:47:13 CST LOG:  autovacuum launcher started
2023-04-18 15:47:13 CST LOG:  database system is ready to accept connections
2023-04-18 15:47:13 CST LOG:  starting syslogical supervisor
2023-04-18 15:47:13 CST LOG:  starting syslogical database manager for database TEST
2023-04-18 15:47:13 CST LOG:  manager worker [11755] at slot 0 generation 1 detaching cleanly
2023-04-18 15:47:13 CST LOG:  starting syslogical database manager for database TEMPLATE1
2023-04-18 15:47:13 CST LOG:  manager worker [11757] at slot 0 generation 2 detaching cleanly
2023-04-18 15:47:13 CST LOG:  starting syslogical database manager for database TEMPLATE2
2023-04-18 15:47:13 CST LOG:  manager worker [11758] at slot 0 generation 3 detaching cleanly
2023-04-18 15:47:13 CST LOG:  starting syslogical database manager for database SAMPLES
2023-04-18 15:47:13 CST LOG:  manager worker [11759] at slot 0 generation 4 detaching cleanly
2023-04-18 15:47:13 CST LOG:  starting syslogical database manager for database SECURITY
2023-04-18 15:47:13 CST LOG:  manager worker [11760] at slot 0 generation 5 detaching cleanly
2023-04-18 15:47:13 CST LOG:  starting syslogical database manager for database prod
2023-04-18 15:47:13 CST LOG:  manager worker [11761] at slot 0 generation 6 detaching cleanly

如下图所示，数据库执行reocvery操作：

三、测试库连接访问

[kingbase@node102 bin]$ ./ksql -U system -W 123456 test
ksql (V008R003C002B0290)
Type "help" for help.

test=# \l
                               List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-----------+--------+----------+-------------+-------------+--------------------
 prod      | SYSTEM | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 SAMPLES   | SYSTEM | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 SECURITY  | SYSTEM | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 TEMPLATE0 | SYSTEM | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/SYSTEM         +
           |        |          |             |             | SYSTEM=CTcb/SYSTEM
 TEMPLATE1 | SYSTEM | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/SYSTEM         +
           |        |          |             |             | SYSTEM=CTcb/SYSTEM
 TEMPLATE2 | SYSTEM | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/SYSTEM        +
           |        |          |             |             | SYSTEM=CTcb/SYSTEM
 TEST      | SYSTEM | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
(7 rows)

test=# \c prod
You are now connected to database "prod" as user "system".
prod=# \d
                    List of relations
 Schema |             Name              | Type  | Owner
--------+-------------------------------+-------+--------
 PUBLIC | pathman_cache_stats           | view  | SYSTEM
 PUBLIC | pathman_concurrent_part_tasks | view  | SYSTEM
 PUBLIC | pathman_config                | table | SYSTEM
 PUBLIC | pathman_config_params         | table | SYSTEM
 PUBLIC | pathman_partition_list        | view  | SYSTEM
 PUBLIC | t1                            | table | SYSTEM
 PUBLIC | t2                            | table | SYSTEM
(7 rows)

prod=# select count(*) from t1;
 count
-------
 10000
(1 row)

prod=# select count(*) from t2;
 count
-------
 10000
(1 row)

---如上所示，测试库数据恢复到了最近的备份点。

三、总结
sys_rman物理备份支持异机恢复，操作过程相对比较简单；可以将生产库的备份目录建立nfs共享，然后在测试环境mount共享文件系统，不用再从生产主机将备份拷贝到测试主机。

标签：V8R3,15,04,--,18,备份,kingbase,2023,CST
From： https://www.cnblogs.com/kingbase/p/17460968.html

相关文章

  • KingbaseES数据库改写SQL Server数据库CROSS APPLY和OUTER APPLY
    一、功能介绍：CROSSAPPLY和OUTERAPPLY是SQLServer中的一种连接操作，类似于JOIN语句可以将一张表与一个表函数或一个子查询进行关联。表函数是一种返回一个表类型的数据的函数，子查询是一个嵌套在外部查询中的查询。它们可以与表值函数或子查询配合使用，返回左表和右表的匹配结果。......
  • itsdangerous：Python的签名和序列化库
    安装你可以从PyPI上直接安装这个库:pipinstallitsdangerous适用案例在取消订阅某个通讯时，你可以在URL里序列化并且签名一个用户的ID。这种情况下你不需要生成一个一次性的token并把它们存到数据库中。在任何的激活账户的链接或类似的情形下，同样适用。被签名的对象可以被存......
  • 自用小技巧
    实时查看日志tail-fcdx-api-1.0.log随机查询三条数据ORDERBYrand()LIMIT3查询创建字段时间大于30分钟的数据TIMESTAMPDIFF(MINUTE,create_time,NOW())>30nginx配置ssl证书http://t.csdn.cn/AOGDs解压tar包tar-zxfkeepalived-2.0.20.tar.gz永久关闭防火墙syste......
  • 化腐朽为神奇的QueryMapping
    化腐朽为神奇的QueryMapping老车除了报废没别的方法？应用系统就像老车，经过十几二十年的使用，积累了大量里程数据，但是英雄迟暮，反应迟钝，时不时还要病休。但就这样报废，推到重来，如果没有充足的预算，实在是下不了这个决心，不知道该怎么办，让这辆老车焕发第二春。人们习惯，难以改变缓慢的......
  • KingbaseES数据库安装PostGIS扩展GEOSUnaryunionPrec错误
    一、问题现象：KingbaseESV008R006C007B0012数据库集群安装PostGIS扩展插件报错。createextensionpostgis;ERROR:couldnotloadiibrary"/opt/kingbase/cluster/kingbase/lib/postgis-3.so”:/opt/kingbase/cluster/kingbase/lib/postgis-3.so:undefinedsymbo1:GEOSUnar......
  • DevOps&Apipost
    DevOps旨在通过自动化流程和改善协作，实现软件开发、测试和交付的一体化，从而提高软件交付的质量和速度。为了提高工作效率，加快软件的交付流程，越来越多企业的选择DevOps工作流程。其中API管理的地位非常重要。随着API数量的大幅增长，也带来了新的API管理需求。如何在DevOps......
  • KingbaseES数据库分区表添加主键与索引的建议
    一、初始化测试环境#数据库版本信息KingbaseESV008R006C007B0012onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.1.220080704(RedHat4.1.2-46),64-bit1.创建分区表：createtabletb(idbigint,statdate,nobigint,pdatedate,infovarchar2(50))partitionbyra......
  • 基本环境安装 jdk，mq，redis，nginx
    JDK：解压安装包，命令为tar-zxvfjdk-8u381-linux-x64.tar.gz配置环境变量，使用vim命令（需要安装vim，安装命令为：yuminstallvim）修改/etc/profile文件，在文件末尾加入如下配置 JAVA_HOME=/usr/local/jdk8/jdk1.8.0_381 PATH=$JAVA_HOME/bin:$PATH重新加载profile文件，使更改的配置......
  • Go每日一库之15：gojsonq
    简介在日常工作中，每一名开发者，不管是前端还是后端，都经常使用JSON。JSON是一个很简单的数据交换格式。相比于XML，它灵活、轻巧、使用方便。JSON也是RESTfulAPI推荐的格式。有时，我们只想读取JSON中的某一些字段。如果自己手动解析、一层一层读取，这就变得异常繁琐了。特别是在......
  • 跳转到应用市场下载
    ps：自用的，在此简单记录 //判断应用市场judeBrand(){constuserAgent=navigator.userAgent.toLowerCase()constisIphone=userAgent.match(/(iphone|ipad|ipod)/i);constisHuawei=userAgent.match(/huawei/i);constisHonor=userAgent.match(/honor/i......

赞助商

阅读排行

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

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

Copyright © 2020-2023 IPS99 版权所有 IPS99