随笔- 320 文章- 0 评论- 5 阅读- 34799

KingbaseES V8R3备份恢复案例之---PITR恢复

案例说明：
数据库表出现“catalog is missing 2 attribute(s) for relid 16389”，基于KingbaseES V8R3的PITR恢复。

适用版本：
KingbaseES V8R3

一、创建测试用例
1、创建测试表

prod=# create table public.test1(a int,b int,c int,d int);
CREATE TABLE
prod=# insert into test1 values (10,10,10),(20,20,20),(30,30,30);
INSERT 0 3

prod=# select * from test1;
 a  | b  | c  | d
----+----+----+---
 10 | 10 | 10 |
 20 | 20 | 20 |
 30 | 30 | 30 |
(3 rows)

二、执行数据库全备

# 初始化备份
[kingbase@node201 bin]$ ./sys_rman init  -U system -p 54325 -d test -B /home/kingbase/bk/r3  -D /home/kingbase/db/r3_370/data

# 执行物理全备
[kingbase@node201 bin]$ ./sys_rman backup -b full -U system -d test -p 54325 -B /home/kingbase/bk/r3  -D /home/kingbase/db/r3_370/data
INFO: validate: SAQC8U backup and archive log files by CRC

# 校验备份
[kingbase@node201 bin]$ ./sys_rman validate  -B /home/kingbase/bk/r3
INFO: validate: SAQC8U backup and archive log files by CRC
INFO: backup validation completed successfully

# 查看备份信息
[kingbase@node201 bin]$ ./sys_rman  -U system -d test -p 54325 -B /home/kingbase/bk/r3  -D /home/kingbase/db/r3_370/data  show
==========================================================================================================
ID       Recovery time        Mode          Current/Parent TLI  Time            Data  start_lsn  stop_lsn Status
==========================================================================================================
SAQC8U   2024-03-22 11:26:56  FULL           1 / 0              2s           124MB  0/2F000028  0/2F000130  OK

三、模拟数据表故障

# 模拟业务访问
prod=# insert into test1 values (40,40,40),(50,50,50);
INSERT 0 2

prod=# select * from test1;
 a  | b  | c  | d
----+----+----+---
 10 | 10 | 10 |
 20 | 20 | 20 |
 30 | 30 | 30 |
 40 | 40 | 40 |
 50 | 50 | 50 |
(5 rows)

# 模拟恢复时间点
prod=# select now();
              now
-------------------------------
 2024-03-22 11:31:08.615781+08
(1 row)

# 开启更新系统表权限
prod=# show allow_system_table_mods ;
 allow_system_table_mods
-------------------------
 on
(1 row)

# 模拟系统表被人为破坏
prod=# delete from sys_attribute where attrelid =
prod-#  (select oid from sys_class where relname = 'test1' and
prod(#  relnamespace = (select oid from sys_namespace where nspname = 'public'))
prod-#  and attname = 'a';
DELETE 1

prod=#  delete from sys_attribute where attrelid = (select oid from sys_class where relname = 'test1'
prod(#   and relnamespace = (select oid from sys_namespace where nspname = 'public'))
prod-#  and attname = 'c';
DELETE 1

# 表故障信息
prod=# select * from test1;
ERROR:  catalog is missing 2 attribute(s) for relid 16389
LINE 1: select * from test1;
                      ^

四、执行物理备份恢复

1、关闭数据库服务（生产环境可以在新的实例下恢复）

[kingbase@node201 bin]$ ./sys_ctl stop -D /home/kingbase/db/r3_370/data/                                  waiting for server to shut down.... done
server stopped

2、执行sys_rman restore备份（PITR)

[kingbase@node201 bin]$ cd /home/kingbase/db/r3_370/
[kingbase@node201 r3_370]$ ls
arch  bk  data  tps
[kingbase@node201 r3_370]$ mv data data.bk

[kingbase@node201 bin]$ ./sys_rman  -U system -d test --progress --time="2024-03-22 11:31:08" -B /home/kingbase/bk/r3  -D /home/kingbase/db/r3_370/data  restore
INFO: validate: SAQC8U backup and archive log files by SIZE
INFO: restore complete. Recovery starts automatically when the Kingbase server is started.

3、启动数据库执行recovery（PITR)

# 查看数据恢复前recovery.conf配置
[kingbase@node201 bin]$ cat /home/kingbase/db/r3_370/data/recovery.conf
restore_command = 'cp /home/kingbase/bk/r3/wal/%f %p'
recovery_target_time = '2024-03-22 11:31:08'
recovery_target_timeline = '1'

# 启动数据库服务
[kingbase@node201 bin]$ ./sys_ctl start -D /home/kingbase/db/r3_370/data/
server starting
[kingbase@node201 bin]$ 2024-03-22 11:48:37 CST LOG:  redirecting log output to logging collector process
2024-03-22 11:48:37 CST HINT:  Future log output will appear in directory "sys_log".

sys_log日志：

[kingbase@node201 sys_log]$ tail -1000 kingbase-2024-03-22_114837.log
2024-03-22 11:48:37 CST LOG:  database system was interrupted; last known up at 2024-03-22 11:26:54 CST
2024-03-22 11:48:37 CST LOG:  creating missing WAL directory "sys_xlog/archive_status"
2024-03-22 11:48:38 CST LOG:  starting point-in-time recovery to 2024-03-22 11:31:08+08
2024-03-22 11:48:38 CST LOG:  restored log file "00000001000000000000002F" from archive
2024-03-22 11:48:38 CST LOG:  redo starts at 0/2F000028
2024-03-22 11:48:38 CST LOG:  redo wal segment count 1
2024-03-22 11:48:38 CST LOG:  consistent recovery state reached at 0/2F000130
2024-03-22 11:48:38 CST LOG:  restored log file "000000010000000000000030" from archive
2024-03-22 11:48:38 CST LOG:  restored log file "000000010000000000000031" from archive
2024-03-22 11:48:38 CST LOG:  recovery stopping before commit of transaction 2090, time 2024-03-22 11:35:08.562374+08
2024-03-22 11:48:38 CST LOG:  complete: 1/1
2024-03-22 11:48:38 CST LOG:  redo done at 0/31001490
2024-03-22 11:48:38 CST LOG:  last completed transaction was at log time 2024-03-22 11:29:59.689584+08
cp: cannot stat ‘/home/kingbase/bk/r3/wal/00000002.history’: No such file or directory
2024-03-22 11:48:38 CST LOG:  selected new timeline ID: 2
2024-03-22 11:48:38 CST LOG:  archive recovery complete
cp: cannot stat ‘/home/kingbase/bk/r3/wal/00000001.history’: No such file or directory
2024-03-22 11:48:38 CST LOG:  checkpoint starting: end-of-recovery immediate wait
2024-03-22 11:48:38 CST LOG:  checkpoint complete: wrote 2 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.009 s; sync files=2, longest=0.000 s, average=0.000 s; distance=32773 kB, estimate=32773 kB
2024-03-22 11:48:38 CST LOG:  MultiXact member wraparound protections are now enabled
2024-03-22 11:48:38 CST LOG:  database system is ready to accept connections
2024-03-22 11:48:38 CST LOG:  starting syslogical supervisor
2024-03-22 11:48:38 CST LOG:  autovacuum launcher started
2024-03-22 11:48:38 CST LOG:  starting syslogical database manager for database TEST
2024-03-22 11:48:38 CST LOG:  manager worker [13017] at slot 0 generation 1 detaching cleanly
2024-03-22 11:48:38 CST LOG:  starting syslogical database manager for database TEMPLATE1
2024-03-22 11:48:38 CST LOG:  manager worker [13019] at slot 0 generation 2 detaching cleanly
2024-03-22 11:48:38 CST LOG:  starting syslogical database manager for database TEMPLATE2
2024-03-22 11:48:38 CST LOG:  manager worker [13021] at slot 0 generation 3 detaching cleanly
2024-03-22 11:48:38 CST LOG:  starting syslogical database manager for database SAMPLES
2024-03-22 11:48:38 CST LOG:  manager worker [13022] at slot 0 generation 4 detaching cleanly
2024-03-22 11:48:38 CST LOG:  starting syslogical database manager for database SECURITY
2024-03-22 11:48:38 CST LOG:  manager worker [13023] at slot 0 generation 5 detaching cleanly
2024-03-22 11:48:38 CST LOG:  starting syslogical database manager for database prod
2024-03-22 11:48:38 CST LOG:  manager worker [13024] at slot 0 generation 6 detaching cleanly
2024-03-22 11:53:38 CST LOG:  checkpoint starting: time
2024-03-22 11:53:38 CST LOG:  checkpoint complete: wrote 2 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 2 recycled; write=0.202 s, sync=0.001 s, total=0.210 s; sync files=2, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=29495 kB
2024-03-22 11:54:05 CST FATAL:  terminating connection due to client idle timeout
.........
2024-03-22 15:15:42 CST LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.001 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=1905 kB

获取归档日志恢复：

备份归档日志：

五、查看数据库恢复状态

如下所示，数据恢复成功....

[kingbase@node201 bin]$ ./ksql -U system -W 123456 test -p 54325
ksql (V008R003C002B0370)
Type "help" for help.


prod=# select * from test1;
 a  | b  | c  | d
----+----+----+---
 10 | 10 | 10 |
 20 | 20 | 20 |
 30 | 30 | 30 |
 40 | 40 | 40 |
 50 | 50 | 50 |
(5 rows)