KingbaseES实现在线增量备份与任意时间点恢复

时间：2023-05-02 12:37:00 fz系列无源交流电流隔离变送器

功能背景

修改或删除人为操作错误的重要数据，需要KingbaseES 支持数据恢复到误操作前的那一刻

解决方案

提供增量备份能力，支持任何时间点恢复

在任何时间点实现增量备份和恢复的想法是

记录数据库系统的操作记录（WAL归档）
在某个时刻进行完整的数据库备份
当需要恢复时，恢复上次完整的数据库备份，并根据操作记录将数据库恢复到指定时间（即可实现PITR 恢复时间点)

配置示例

操作
1.配置数据库，vim kingbase.conf

wal_level = replica archive_mode = on archive_command = 'cp %p /home/cli/archive/%f && echo %f >> /home/cli/archive/archive.list' wal_keep_segments=1024

二、重启数据库生效
sys_ctl -D /home/cli/kingbase/db/Server/bin/data/ restart

3.之后将记录数据库的操作。wal日志文件到/home/cli/archive目录下
登录数据库插入数据
create table test(a int primary key);
insert into test values (generate_series(1,50000));
[cli@iZbp1fpui5cmgd2buwhk5fZ bin]$ ls ~/archive/
000000010000000000000001 archive.list

4.可用于创建基本备份sys_basebackup或者sys_rman，这里我们用sys_basebackup
sys_basebackup -D /home/cli/kingbase/db/Server/bin/bak -Fp -v -P -Usystem -R
在这里插入图片描述
5.准备测试数据

[cli@iZbp1fpui5cmgd2buwhk5fZ bin]$ ./ksql  -Usystem -dtest -p 54321 ksql (V8.0) Type "help" for help. test=# create table t2(a int); CREATE TABLE test=# insert into t2 values (generate_series(1,1000000)); INSERT 0 1000000 test=# select now();               now -------------------------------  2022-04-23 15:06:03.613826 08 (1 row)  test=# test=# create table t3(a int); CREATE TABLE test=# insert into t3 values (generate_series(1,1000000)); INSERT 0 1000000

6.假设数据库故障，停止数据库，备份故障data并将基础备份的bak替换为新data

sys_ctl -D /home/cli/kingbase/db/Server/bin/data/ stop
mv data data_back
mv bak data

7、修改data目录下kingbase.auto.conf
restore_command = 'cp /home/cli/archive/%f %p > /home/cli/archive/recovery.log 2>&1 ’
recovery_target_time = ‘2022-04-23 15:06:03’

recovery_target_time表示要恢复的时间点

8.启动数据库
./sys_ctl -D /home/cli/kingbase/db/Server/bin/data/ start

查看数据，可知t3未被恢复

日志信息：
2022-04-23 15:17:15.821 CST [2367] LOG: database system was interrupted; last known up at 2022-04-23 14:56:23 CST
2022-04-23 15:17:15.956 CST [2367] LOG: entering standby mode
2022-04-23 15:17:15.975 CST [2367] LOG: restored log file “000000010000000000000003” from archive
2022-04-23 15:17:16.059 CST [2367] LOG: redo starts at 0/3000028
2022-04-23 15:17:16.059 CST [2367] LOG: redo wal segment count 1
2022-04-23 15:17:16.063 CST [2367] LOG: consistent recovery state reached at 0/30000F8
2022-04-23 15:17:16.064 CST [2365] LOG: database system is ready to accept read only connections
2022-04-23 15:17:16.083 CST [2367] LOG: restored log file “000000010000000000000004” from archive
2022-04-23 15:17:16.642 CST [2367] LOG: restored log file “000000010000000000000005” from archive
2022-04-23 15:17:17.239 CST [2367] LOG: restored log file “000000010000000000000006” from archive
2022-04-23 15:17:17.825 CST [2367] LOG: restored log file “000000010000000000000007” from archive
2022-04-23 15:17:18.330 CST [2367] LOG: recovery stopping before commit of transaction 909, time 2022-04-23 15:06:15.005918 08
2022-04-23 15:17:18.330 CST [2367] LOG: recovery has paused
2022-04-23 15:17:18.330 CST [2367] HINT: Execute pg_wal_replay_resume() to continue.

更多人大金仓数据库信息，详见金仓文件管理系统】

锐单商城拥有海量元器件数据手册、IC替代型号，打造电子元器件IC百科大全！

KingbaseES实现在线增量备份与任意时间点恢复

相关文章