KingbaseES恢复被删除数据
生产环境操作请先备份整个data目录或cp 当前数据目录/home/kingbase/pg_data到新的data目录，然后在备份的data目录进行恢复被删除数据操作。
通过新备份的路径恢复数据之后，确认数据完整性(确认业务数据完整性)。再把数据从备份环境导出重新导入到生产环境。
使用到的系统工具：
sys_resetwal
sys_waldump
-
准备测试环境：
使用initdb初始化数据库
[kingbase@postgres ~]$ initdb -EUTF8 -Usystem -mpg -D /home/kingbase/pg_data
The files belonging to this database system will be owned by user "kingbase".
This user must also own the server process.The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".The comparision of strings is case-sensitive.
Data page checksums are disabled.creating directory /home/kingbase/pg_data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
Begin setup encrypt device
initializing the encrypt device ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
create security database ... ok
load security database ... ok
syncing data to disk ... okinitdb: warning: enabling "trust" authentication for local connections
You can change this by editing sys_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.Success. You can now start the database server using:
sys_ctl -D /home/kingbase/pg_data -l logfile start--启动刚initdb初始化的数据库
[kingbase@postgres ~]$ sys_ctl -D /home/kingbase/pg_data/ start
waiting for server to start....2022-12-16 10:26:49.044 CST [24811] LOG: sepapower extension initialized
2022-12-16 10:26:49.049 CST [24811] LOG: starting KingbaseES V008R006C006B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-12-16 10:26:49.049 CST [24811] LOG: listening on IPv4 address "0.0.0.0", port 54321
2022-12-16 10:26:49.049 CST [24811] LOG: listening on IPv6 address "::", port 54321
2022-12-16 10:26:49.053 CST [24811] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"
2022-12-16 10:26:49.095 CST [24811] LOG: redirecting log output to logging collector process
2022-12-16 10:26:49.095 CST [24811] HINT: Future log output will appear in directory "sys_log".
done
server started[kingbase@postgres ~]$ ksql -Usystem -dtest
ksql (V8.0)
Type "help" for help.test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+---------+-------+-------------------
security | system | UTF8 | C | C |
template0 | system | UTF8 | C | C | =c/system +
| | | | | system=CTc/system
template1 | system | UTF8 | C | C | =c/system +
| | | | | system=CTc/system
test | system | UTF8 | C | C |
(4 rows) -
创建测试表并插入测试数据
--创建表
CREATE TABLE TB (
"MENU_ID" numeric(8,0) NOT NULL,
"SYSTEM_CODE" character varying(15) NULL,
"ORDER_NO" character varying(5) NULL,
"TITLE" character varying(50) NULL,
"OBJECT_CODE" character varying(12) NULL,
"ICO" character varying(36) NULL,
"ICO_LAYOUT" character varying(10) NULL,
"MENU_ID_PARENT" numeric(8,0) NOT NULL DEFAULT 0
);
--插入数据
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(1320,'SYS','4D1','药品黑白名单管理','PASS-W01',NULL,NULL,1319),
(1321,'SYS','4D2','药品限制使用设置','PASS-W03',NULL,NULL,1319),
(293,'SYS','3843','医嘱排斥项设置','ORDER-W05',NULL,NULL,260),
(294,'SYS','385','化验医嘱',NULL,NULL,NULL,515),
(295,'SYS','3851','化验容器设置','ASSAY-W01',NULL,NULL,294),
(296,'SYS','3852','化验标本类型设置','ASSAY-W02',NULL,NULL,294),
(297,'SYS','3853','化验报告取单时间设置','ASSAY-W03',NULL,NULL,294),
(300,'SYS','10','功能相关',NULL,NULL,NULL,299),
(301,'SYS','101','系统对象设置','SYS-W01',NULL,NULL,300),
(302,'SYS','102','系统菜单设置','SYS-W02',NULL,NULL,300);
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(303,'SYS','103','系统平台功能设置','SYS-W03',NULL,NULL,300),
(1172,'SYS','150','系统报表设置','RPT-W11',NULL,NULL,1171),
(1173,'SYS','151','病人出院列表','RPT-W01',NULL,NULL,1171),
(1174,'SYS','152','费用统计','RPT-W01',NULL,NULL,1171),
(1175,'SYS','153','医院业务统计报表','RPT-W01',NULL,NULL,1171),
(663,'SYS','3B6','体温单格式配置','ENR-W14',NULL,NULL,373),
(1176,'SYS','154','医院业务统计报表(病区)','RPT-W01',NULL,NULL,1171),
(665,'SYS','316','病案诊断类型设置','DIAG-W04',NULL,NULL,613),
(666,'SYS','132','报表格式打印机设置','SYS1-W04',NULL,NULL,405),
(669,'KTCIS_IDW','57','住院病历质量检查评分','EMR-W21',NULL,NULL,239);
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(673,'SYS','4S','包药机相关设置',NULL,NULL,NULL,516),
(674,'SYS','4S1','包药机设置','MED-W05',NULL,NULL,673),
(373,'SYS','3B','电子护理记录基础设置',NULL,NULL,NULL,517),
(675,'SYS','4M','摆药包药相关设置',NULL,NULL,NULL,516),
(676,'SYS','4M0','药品包药字典设置','PACK_MED-W01',NULL,NULL,675),
(304,'SYS','104','系统参数设置','SYS-W04',NULL,NULL,300),
(305,'SYS','105','个人参数设置','SYS-W05',NULL,NULL,300),
(306,'SYS','106','机器参数设置','SYS-W06',NULL,NULL,300),
(307,'SYS','107','系统机器设置','SYS-W07',NULL,NULL,300),
(308,'SYS','108','通知发送','SYS-W08',NULL,NULL,300);
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(309,'SYS','109','系统文件发布','SYS-W09',NULL,NULL,300),
(310,'SYS','10A','系统文件查询','SYS-W10',NULL,NULL,300),
(311,'SYS','10B','带功能的窗体测试','SYS-W99',NULL,NULL,300),
(312,'SYS','11','行政组织',NULL,NULL,NULL,299),
(325,'SYS','12','医院基础设置',NULL,NULL,NULL,299),
(326,'SYS','311','数据字典设置','PUB2-W01',NULL,NULL,613),
(327,'SYS','121','行政区域设置','PUB2-W02',NULL,NULL,325),
(329,'SYS','1251','自定义字段设置','PUB2-W05',NULL,NULL,614),
(330,'SYS','124','医疗工作点设置','PUB2-W06',NULL,NULL,325),
(331,'SYS','1252','系统条件参数设置','PUB2-W07',NULL,NULL,614);
-
删除数据后，未执行checkpoint，vacuum操作，进行数据恢复
3.1 确认当前事务日志及事务(LSN)号
select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
--查询insert后，delete前的当前事务日志及事务(LSN)号
test=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+------------------------------------
0/17AF9D0 | 000000010000000000000001 | (000000010000000000000001,8059344)
(1 row)
3.2 进行删除数据操作，不手动执行chekpoint命令
test=# select count(*) from tb;
count
-------
40
(1 row)
test=# delete from tb where "MENU_ID"=300;
DELETE 1
test=#
test=# select count(*) from tb;
count
-------
39
(1 row)
--查询进行delete删除操作后的当前事务日志及事务(LSN)号
test=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+------------------------------------
0/17B07B8 | 000000010000000000000001 | (000000010000000000000001,8062904)
(1 row)
3.3 根据查询到的事务号以及日志文件，查找WAL日志确定恢复数据的范围
lsn数据类型可以被用来存储 LSN（日志序列号）数据，LSN 是一个指向WAL中的位置的指针。
这个类型是XLogRecPtr的一种表达并且是数据库内部系统类型。
在数据库内部，一个 LSN 是一个 64 位整数，表示在预写式日志流中的一个字节位置。
它由两个长度为8位的十六进制数组成，中间用斜线分隔，如6/7311F80。
lsn类型支持标准的比较操作符 = 和 >
两个 LSN 可以做相减操作， 结果将是分隔两个预写式日志位置的字节数
--通过wal日志事物号找到delete的大概位置，这里位置是 552
[kingbase@postgres sys_wal]$ pwd
/home/kingbase/pg_data/sys_wal
[kingbase@postgres sys_wal]$ ls -l
total 16384
-rw------- 1 kingbase kingbase 16777216 Dec 16 11:47 000000010000000000000001
drwx------ 2 kingbase kingbase 6 Dec 16 10:26 archive_status
[kingbase@postgres sys_wal]$ sys_waldump 000000010000000000000001 -s 0/17AF9D0
rmgr: Heap len (rec/tot): 59/ 3227, tx: 552, lsn: 0/017AF9D0, prev 0/017AF9A0, desc: DELETE off 8 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16384 blk 0 FPW
rmgr: Transaction len (rec/tot): 34/ 34, tx: 552, lsn: 0/017B0688, prev 0/017AF9D0, desc: COMMIT 2022-12-16 11:46:15.040166 CST
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 0/017B06B0, prev 0/017B0688, desc: RUNNING_XACTS nextXid 553 latestCompletedXid 24839816 oldestRunningXid 553
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 0/017B06E0, prev 0/017B06B0, desc: RUNNING_XACTS nextXid 553 latestCompletedXid 24839904 oldestRunningXid 553
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/017B0710, prev 0/017B06E0, desc: CHECKPOINT_ONLINE redo 0/17B06E0; tli 1; prev tli 1; fpw true; xid 0:553; oid 24576; multi 1; offset 0; oldest xid 519 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 553; online
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 0/017B0788, prev 0/017B0710, desc: RUNNING_XACTS nextXid 553 latestCompletedXid 24839952 oldestRunningXid 553
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/017B07B8, prev 0/017B0788, desc: CHECKPOINT_SHUTDOWN redo 0/17B07B8; tli 1; prev tli 1; fpw true; xid 0:553; oid 16388; multi 1; offset 0; oldest xid 519 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown
sys_waldump: fatal: error in WAL record at 0/17B07B8: invalid record length at 0/17B0830: wanted 24, got 0
--如果删除数据量大，涉及多个wal日志，也可以指定事务日志起始wal文件名，结束wal日志文件名称
--示例 sys_waldump 000000010000000000000001 000000010000000000000009 -s 0/17AF9D0
--通过以下命令进行在wal日志精确定位事务号
[kingbase@postgres sys_wal]$ sys_waldump 000000010000000000000001 | grep DELETE
sys_waldump: fatal: error in WAL record at 0/17B07B8: invalid record length at 0/17B0830: wanted 24, got 0
rmgr: Heap len (rec/tot): 59/ 3227, tx: 552, lsn: 0/017AF9D0, prev 0/017AF9A0, desc: DELETE off 8 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16384 blk 0 FPW
[kingbase@postgres sys_wal]$
3.4 进行删除数据恢复操作
--停止数据库
[kingbase@postgres ~]$ sys_ctl -D /home/kingbase/pg_data/ stop
waiting for server to shut down.... done
server stopped
--备份当前数据目录data到新的路径或者新的目录
[kingbase@postgres ~]$ cp -r pg_data/ pg_data_bak
[kingbase@postgres ~]$ ll
total 808
drwxrwxr-x 3 kingbase kingbase 16 Nov 10 01:12 V8R6C6B21
-rwxr-xr-x 1 kingbase kingbase 3276 Nov 10 00:31 license.dat
-rw-rw-r-- 1 kingbase kingbase 252 Dec 6 16:48 my_test.c
-rw-rw-r-- 1 kingbase kingbase 10403 Dec 13 18:21 nohup.out
drwx------ 22 kingbase kingbase 4096 Dec 16 10:26 ora_data
drwx------ 23 kingbase kingbase 4096 Dec 16 11:47 pg_data
drwx------ 23 kingbase kingbase 4096 Dec 16 12:01 pg_data_bak
--使用备份的data目录，通过指定事务号进行删除数据的恢复。
--使用sys_resetwal工具指定事务号552进行删除数据的恢复
[kingbase@postgres ~]$ sys_resetwal -x 552 /home/kingbase/pg_data_bak/
Write-ahead log reset
--启动通过cp备份的数据库查询数据是否被恢复
[kingbase@postgres ~]$ sys_ctl -D /home/kingbase/pg_data_bak/ start
waiting for server to start....2022-12-16 12:06:48.993 CST [30197] LOG: sepapower extension initialized
2022-12-16 12:06:48.997 CST [30197] LOG: starting KingbaseES V008R006C006B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-12-16 12:06:48.997 CST [30197] LOG: listening on IPv4 address "0.0.0.0", port 54321
2022-12-16 12:06:48.997 CST [30197] LOG: listening on IPv6 address "::", port 54321
2022-12-16 12:06:49.001 CST [30197] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"
2022-12-16 12:06:49.082 CST [30197] LOG: redirecting log output to logging collector process
2022-12-16 12:06:49.082 CST [30197] HINT: Future log output will appear in directory "sys_log".
done
server started
[kingbase@postgres ~]$ ksql -Usystem -dtest
ksql (V8.0)
Type "help" for help.
test=# select count(*) from tb;
count
-------
40
(1 row)
test=# select * from tb where "MENU_ID"=300;
MENU_ID | SYSTEM_CODE | ORDER_NO | TITLE | OBJECT_CODE | ICO | ICO_LAYOUT | MENU_ID_PARENT
---------+-------------+----------+--------------+-------------+-----+------------+----------------
300 | SYS | 10 | 功能相关 | | | | 299
(1 row)
经确认进行删除表数据操作后未手动执行checkpoint命令，通过sys_resetwal工具可以恢复被删除的数据。
- 删除数据后执行checkpoint，不执行vacuum，恢复被删除的数据
--启动pg_data数据库
[kingbase@postgres ~]$ sys_ctl -D /home/kingbase/pg_data/ start
waiting for server to start....2022-12-16 12:13:51.096 CST [30580] LOG: sepapower extension initialized
2022-12-16 12:13:51.101 CST [30580] LOG: starting KingbaseES V008R006C006B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-12-16 12:13:51.101 CST [30580] LOG: listening on IPv4 address "0.0.0.0", port 54321
2022-12-16 12:13:51.101 CST [30580] LOG: listening on IPv6 address "::", port 54321
2022-12-16 12:13:51.105 CST [30580] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"
2022-12-16 12:13:51.142 CST [30580] LOG: redirecting log output to logging collector process
2022-12-16 12:13:51.142 CST [30580] HINT: Future log output will appear in directory "sys_log".
done
server started
--登录数据库删除TB表
[kingbase@postgres ~]$ ksql -Usystem -dtest
ksql (V8.0)
Type "help" for help.
test=# drop table tb;
DROP TABLE
--创建表
CREATE TABLE TB (
"MENU_ID" numeric(8,0) NOT NULL,
"SYSTEM_CODE" character varying(15) NULL,
"ORDER_NO" character varying(5) NULL,
"TITLE" character varying(50) NULL,
"OBJECT_CODE" character varying(12) NULL,
"ICO" character varying(36) NULL,
"ICO_LAYOUT" character varying(10) NULL,
"MENU_ID_PARENT" numeric(8,0) NOT NULL DEFAULT 0
);
--插入数据
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(1320,'SYS','4D1','药品黑白名单管理','PASS-W01',NULL,NULL,1319),
(1321,'SYS','4D2','药品限制使用设置','PASS-W03',NULL,NULL,1319),
(293,'SYS','3843','医嘱排斥项设置','ORDER-W05',NULL,NULL,260),
(294,'SYS','385','化验医嘱',NULL,NULL,NULL,515),
(295,'SYS','3851','化验容器设置','ASSAY-W01',NULL,NULL,294),
(296,'SYS','3852','化验标本类型设置','ASSAY-W02',NULL,NULL,294),
(297,'SYS','3853','化验报告取单时间设置','ASSAY-W03',NULL,NULL,294),
(300,'SYS','10','功能相关',NULL,NULL,NULL,299),
(301,'SYS','101','系统对象设置','SYS-W01',NULL,NULL,300),
(302,'SYS','102','系统菜单设置','SYS-W02',NULL,NULL,300);
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(303,'SYS','103','系统平台功能设置','SYS-W03',NULL,NULL,300),
(1172,'SYS','150','系统报表设置','RPT-W11',NULL,NULL,1171),
(1173,'SYS','151','病人出院列表','RPT-W01',NULL,NULL,1171),
(1174,'SYS','152','费用统计','RPT-W01',NULL,NULL,1171),
(1175,'SYS','153','医院业务统计报表','RPT-W01',NULL,NULL,1171),
(663,'SYS','3B6','体温单格式配置','ENR-W14',NULL,NULL,373),
(1176,'SYS','154','医院业务统计报表(病区)','RPT-W01',NULL,NULL,1171),
(665,'SYS','316','病案诊断类型设置','DIAG-W04',NULL,NULL,613),
(666,'SYS','132','报表格式打印机设置','SYS1-W04',NULL,NULL,405),
(669,'KTCIS_IDW','57','住院病历质量检查评分','EMR-W21',NULL,NULL,239);
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(673,'SYS','4S','包药机相关设置',NULL,NULL,NULL,516),
(674,'SYS','4S1','包药机设置','MED-W05',NULL,NULL,673),
(373,'SYS','3B','电子护理记录基础设置',NULL,NULL,NULL,517),
(675,'SYS','4M','摆药包药相关设置',NULL,NULL,NULL,516),
(676,'SYS','4M0','药品包药字典设置','PACK_MED-W01',NULL,NULL,675),
(304,'SYS','104','系统参数设置','SYS-W04',NULL,NULL,300),
(305,'SYS','105','个人参数设置','SYS-W05',NULL,NULL,300),
(306,'SYS','106','机器参数设置','SYS-W06',NULL,NULL,300),
(307,'SYS','107','系统机器设置','SYS-W07',NULL,NULL,300),
(308,'SYS','108','通知发送','SYS-W08',NULL,NULL,300);
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(309,'SYS','109','系统文件发布','SYS-W09',NULL,NULL,300),
(310,'SYS','10A','系统文件查询','SYS-W10',NULL,NULL,300),
(311,'SYS','10B','带功能的窗体测试','SYS-W99',NULL,NULL,300),
(312,'SYS','11','行政组织',NULL,NULL,NULL,299),
(325,'SYS','12','医院基础设置',NULL,NULL,NULL,299),
(326,'SYS','311','数据字典设置','PUB2-W01',NULL,NULL,613),
(327,'SYS','121','行政区域设置','PUB2-W02',NULL,NULL,325),
(329,'SYS','1251','自定义字段设置','PUB2-W05',NULL,NULL,614),
(330,'SYS','124','医疗工作点设置','PUB2-W06',NULL,NULL,325),
(331,'SYS','1252','系统条件参数设置','PUB2-W07',NULL,NULL,614);
4.1 确认当前事务日志及事务(LSN)号
select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
--查询insert后，delete前的当前事务日志及事务(LSN)号
test=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+------------------------------------
0/17CD750 | 000000010000000000000001 | (000000010000000000000001,8181584)
(1 row)
4.2 进行删除数据操作，手动执行chekpoint命令
test=# delete from tb ;
DELETE 40
test=# select count(*) from tb;
count
-------
0
(1 row)
test=# checkpoint ;
CHECKPOINT
--查询删除数据并且手动执行checkpoint命令后，当前事务日志及事务(LSN)号
test=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+------------------------------------
0/17CED90 | 000000010000000000000001 | (000000010000000000000001,8187280)
(1 row)
4.3 根据查询到的事务号以及日志文件，查找WAL日志确定恢复数据的范围
--通过wal日志事物号找到delete的大概位置，这里位置是 559
# sys_waldump 000000010000000000000001 -s 0/17CD750
[kingbase@postgres sys_wal]$ sys_waldump 000000010000000000000001 -s 0/17CD750
rmgr: Heap len (rec/tot): 59/ 3227, tx: 559, lsn: 0/017CD750, prev 0/017CD720, desc: DELETE off 1 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE408, prev 0/017CD750, desc: DELETE off 2 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE440, prev 0/017CE408, desc: DELETE off 3 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE478, prev 0/017CE440, desc: DELETE off 4 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE4B0, prev 0/017CE478, desc: DELETE off 5 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE4E8, prev 0/017CE4B0, desc: DELETE off 6 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE520, prev 0/017CE4E8, desc: DELETE off 7 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE558, prev 0/017CE520, desc: DELETE off 8 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE590, prev 0/017CE558, desc: DELETE off 9 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE5C8, prev 0/017CE590, desc: DELETE off 10 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE600, prev 0/017CE5C8, desc: DELETE off 11 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE638, prev 0/017CE600, desc: DELETE off 12 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE670, prev 0/017CE638, desc: DELETE off 13 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE6A8, prev 0/017CE670, desc: DELETE off 14 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE6E0, prev 0/017CE6A8, desc: DELETE off 15 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE718, prev 0/017CE6E0, desc: DELETE off 16 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE750, prev 0/017CE718, desc: DELETE off 17 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE788, prev 0/017CE750, desc: DELETE off 18 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE7C0, prev 0/017CE788, desc: DELETE off 19 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE7F8, prev 0/017CE7C0, desc: DELETE off 20 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE830, prev 0/017CE7F8, desc: DELETE off 21 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE868, prev 0/017CE830, desc: DELETE off 22 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE8A0, prev 0/017CE868, desc: DELETE off 23 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE8D8, prev 0/017CE8A0, desc: DELETE off 24 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE910, prev 0/017CE8D8, desc: DELETE off 25 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE948, prev 0/017CE910, desc: DELETE off 26 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE980, prev 0/017CE948, desc: DELETE off 27 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE9B8, prev 0/017CE980, desc: DELETE off 28 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE9F0, prev 0/017CE9B8, desc: DELETE off 29 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEA28, prev 0/017CE9F0, desc: DELETE off 30 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEA60, prev 0/017CEA28, desc: DELETE off 31 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEA98, prev 0/017CEA60, desc: DELETE off 32 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEAD0, prev 0/017CEA98, desc: DELETE off 33 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEB08, prev 0/017CEAD0, desc: DELETE off 34 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEB40, prev 0/017CEB08, desc: DELETE off 35 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEB78, prev 0/017CEB40, desc: DELETE off 36 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEBB0, prev 0/017CEB78, desc: DELETE off 37 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEBE8, prev 0/017CEBB0, desc: DELETE off 38 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEC20, prev 0/017CEBE8, desc: DELETE off 39 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEC58, prev 0/017CEC20, desc: DELETE off 40 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 559, lsn: 0/017CEC90, prev 0/017CEC58, desc: COMMIT 2022-12-16 12:21:54.321583 CST
rmgr: Standby len (rec/tot): 46/ 46, tx: 0, lsn: 0/017CECB8, prev 0/017CEC90, desc: RUNNING_XACTS nextXid 560 latestCompletedXid 24964240 oldestRunningXid 559
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 0/017CECE8, prev 0/017CECB8, desc: RUNNING_XACTS nextXid 560 latestCompletedXid 24964328 oldestRunningXid 560
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/017CED18, prev 0/017CECE8, desc: CHECKPOINT_ONLINE redo 0/17CECE8; tli 1; prev tli 1; fpw true; xid 0:560; oid 24580; multi 1; offset 0; oldest xid 519 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 560; online
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 0/017CED90, prev 0/017CED18, desc: RUNNING_XACTS nextXid 560 latestCompletedXid 24964376 oldestRunningXid 560
rmgr: Heap len (rec/tot): 53/ 1081, tx: 0, lsn: 0/017CEDC0, prev 0/017CED90, desc: INPLACE off 3, blkref #0: rel 1663/13857/1259 blk 0 FPW
rmgr: Standby len (rec/tot): 90/ 90, tx: 0, lsn: 0/017CF200, prev 0/017CEDC0, desc: INVALIDATIONS ; inval msgs: catcache 62 catcache 61 relcache 16388
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 0/017CF260, prev 0/017CF200, desc: RUNNING_XACTS nextXid 560 latestCompletedXid 24965632 oldestRunningXid 560
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/017CF290, prev 0/017CF260, desc: CHECKPOINT_SHUTDOWN redo 0/17CF290; tli 1; prev tli 1; fpw true; xid 0:560; oid 16392; multi 1; offset 0; oldest xid 519 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown
sys_waldump: fatal: error in WAL record at 0/17CF290: invalid record length at 0/17CF308: wanted 24, got 0
--如果删除数据量大，涉及多个wal日志，也可以指定事务日志起始wal文件名，结束wal日志文件名称
--示例 sys_waldump 000000010000000000000001 000000010000000000000009 -s 0/17AF9D0
--通过以下命令进行在wal日志精确定位事务号
[kingbase@postgres sys_wal]$ sys_waldump 000000010000000000000001 | grep DELETE
rmgr: Heap len (rec/tot): 59/ 3227, tx: 559, lsn: 0/017CD750, prev 0/017CD720, desc: DELETE off 1 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE408, prev 0/017CD750, desc: DELETE off 2 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE440, prev 0/017CE408, desc: DELETE off 3 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE478, prev 0/017CE440, desc: DELETE off 4 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE4B0, prev 0/017CE478, desc: DELETE off 5 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE4E8, prev 0/017CE4B0, desc: DELETE off 6 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE520, prev 0/017CE4E8, desc: DELETE off 7 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE558, prev 0/017CE520, desc: DELETE off 8 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE590, prev 0/017CE558, desc: DELETE off 9 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE5C8, prev 0/017CE590, desc: DELETE off 10 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE600, prev 0/017CE5C8, desc: DELETE off 11 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE638, prev 0/017CE600, desc: DELETE off 12 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE670, prev 0/017CE638, desc: DELETE off 13 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE6A8, prev 0/017CE670, desc: DELETE off 14 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE6E0, prev 0/017CE6A8, desc: DELETE off 15 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE718, prev 0/017CE6E0, desc: DELETE off 16 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE750, prev 0/017CE718, desc: DELETE off 17 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE788, prev 0/017CE750, desc: DELETE off 18 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE7C0, prev 0/017CE788, desc: DELETE off 19 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE7F8, prev 0/017CE7C0, desc: DELETE off 20 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE830, prev 0/017CE7F8, desc: DELETE off 21 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE868, prev 0/017CE830, desc: DELETE off 22 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE8A0, prev 0/017CE868, desc: DELETE off 23 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE8D8, prev 0/017CE8A0, desc: DELETE off 24 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE910, prev 0/017CE8D8, desc: DELETE off 25 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE948, prev 0/017CE910, desc: DELETE off 26 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE980, prev 0/017CE948, desc: DELETE off 27 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE9B8, prev 0/017CE980, desc: DELETE off 28 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CE9F0, prev 0/017CE9B8, desc: DELETE off 29 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEA28, prev 0/017CE9F0, desc: DELETE off 30 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEA60, prev 0/017CEA28, desc: DELETE off 31 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEA98, prev 0/017CEA60, desc: DELETE off 32 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEAD0, prev 0/017CEA98, desc: DELETE off 33 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEB08, prev 0/017CEAD0, desc: DELETE off 34 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEB40, prev 0/017CEB08, desc: DELETE off 35 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEB78, prev 0/017CEB40, desc: DELETE off 36 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEBB0, prev 0/017CEB78, desc: DELETE off 37 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEBE8, prev 0/017CEBB0, desc: DELETE off 38 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEC20, prev 0/017CEBE8, desc: DELETE off 39 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 559, lsn: 0/017CEC58, prev 0/017CEC20, desc: DELETE off 40 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
4.4 进行恢复删除数据操作
--停止数据库
[kingbase@postgres ~]$ sys_ctl -D /home/kingbase/pg_data/ stop
waiting for server to shut down.... done
server stopped
[kingbase@postgres ~]$ ls -l
total 804
drwxrwxr-x 3 kingbase kingbase 16 Nov 10 01:12 V8R6C6B21
-rwxr-xr-x 1 kingbase kingbase 3276 Nov 10 00:31 license.dat
-rw-rw-r-- 1 kingbase kingbase 252 Dec 6 16:48 my_test.c
-rw-rw-r-- 1 kingbase kingbase 10403 Dec 13 18:21 nohup.out
drwx------ 22 kingbase kingbase 4096 Dec 16 10:26 ora_data
drwx------ 23 kingbase kingbase 4096 Dec 16 12:23 pg_data
--备份当前data数据目录到新目录
[kingbase@postgres ~]$ cp -r pg_data/ pg_data_bak
[kingbase@postgres ~]$ ls -l
total 808
drwxrwxr-x 3 kingbase kingbase 16 Nov 10 01:12 V8R6C6B21
-rwxr-xr-x 1 kingbase kingbase 3276 Nov 10 00:31 license.dat
-rw-rw-r-- 1 kingbase kingbase 252 Dec 6 16:48 my_test.c
-rw-rw-r-- 1 kingbase kingbase 10403 Dec 13 18:21 nohup.out
drwx------ 22 kingbase kingbase 4096 Dec 16 10:26 ora_data
drwx------ 23 kingbase kingbase 4096 Dec 16 12:23 pg_data
drwx------ 23 kingbase kingbase 4096 Dec 16 12:32 pg_data_bak
--使用备份的data目录，通过指定事务号进行删除数据的恢复。
--使用sys_resetwal工具指定事务号552进行删除数据的恢复
[kingbase@postgres ~]$ sys_resetwal -x 559 /home/kingbase/pg_data_bak/
Write-ahead log reset
--启动通过cp备份的数据库查询数据是否被恢复
[kingbase@postgres ~]$ sys_ctl -D /home/kingbase/pg_data_bak/ start
waiting for server to start....2022-12-16 12:34:36.822 CST [31721] LOG: sepapower extension initialized
2022-12-16 12:34:36.827 CST [31721] LOG: starting KingbaseES V008R006C006B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-12-16 12:34:36.828 CST [31721] LOG: listening on IPv4 address "0.0.0.0", port 54321
2022-12-16 12:34:36.828 CST [31721] LOG: listening on IPv6 address "::", port 54321
2022-12-16 12:34:36.831 CST [31721] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"
2022-12-16 12:34:36.874 CST [31721] LOG: redirecting log output to logging collector process
2022-12-16 12:34:36.874 CST [31721] HINT: Future log output will appear in directory "sys_log".
done
server started
[kingbase@postgres ~]$ ksql -Usystem -dtest
ksql (V8.0)
Type "help" for help.
test=# select count(*) from tb;
count
-------
40
(1 row)
经确认进行删除表数据操作后并且手动执行checkpoint命令后，通过sys_resetwal工具也是可以恢复被删除的数据。
- 恢复被删除的数据并且执行了Vacuum操作,数据是否可以被恢复
--启动pg_data数据库
[kingbase@postgres ~]$ sys_ctl -D /home/kingbase/pg_data/ start
waiting for server to start....2022-12-16 12:13:51.096 CST [30580] LOG: sepapower extension initialized
2022-12-16 12:13:51.101 CST [30580] LOG: starting KingbaseES V008R006C006B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-12-16 12:13:51.101 CST [30580] LOG: listening on IPv4 address "0.0.0.0", port 54321
2022-12-16 12:13:51.101 CST [30580] LOG: listening on IPv6 address "::", port 54321
2022-12-16 12:13:51.105 CST [30580] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"
2022-12-16 12:13:51.142 CST [30580] LOG: redirecting log output to logging collector process
2022-12-16 12:13:51.142 CST [30580] HINT: Future log output will appear in directory "sys_log".
done
server started
--登录数据库删除TB表
[kingbase@postgres ~]$ ksql -Usystem -dtest
ksql (V8.0)
Type "help" for help.
test=# drop table tb;
DROP TABLE
--创建表
CREATE TABLE TB (
"MENU_ID" numeric(8,0) NOT NULL,
"SYSTEM_CODE" character varying(15) NULL,
"ORDER_NO" character varying(5) NULL,
"TITLE" character varying(50) NULL,
"OBJECT_CODE" character varying(12) NULL,
"ICO" character varying(36) NULL,
"ICO_LAYOUT" character varying(10) NULL,
"MENU_ID_PARENT" numeric(8,0) NOT NULL DEFAULT 0
);
--插入数据
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(1320,'SYS','4D1','药品黑白名单管理','PASS-W01',NULL,NULL,1319),
(1321,'SYS','4D2','药品限制使用设置','PASS-W03',NULL,NULL,1319),
(293,'SYS','3843','医嘱排斥项设置','ORDER-W05',NULL,NULL,260),
(294,'SYS','385','化验医嘱',NULL,NULL,NULL,515),
(295,'SYS','3851','化验容器设置','ASSAY-W01',NULL,NULL,294),
(296,'SYS','3852','化验标本类型设置','ASSAY-W02',NULL,NULL,294),
(297,'SYS','3853','化验报告取单时间设置','ASSAY-W03',NULL,NULL,294),
(300,'SYS','10','功能相关',NULL,NULL,NULL,299),
(301,'SYS','101','系统对象设置','SYS-W01',NULL,NULL,300),
(302,'SYS','102','系统菜单设置','SYS-W02',NULL,NULL,300);
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(303,'SYS','103','系统平台功能设置','SYS-W03',NULL,NULL,300),
(1172,'SYS','150','系统报表设置','RPT-W11',NULL,NULL,1171),
(1173,'SYS','151','病人出院列表','RPT-W01',NULL,NULL,1171),
(1174,'SYS','152','费用统计','RPT-W01',NULL,NULL,1171),
(1175,'SYS','153','医院业务统计报表','RPT-W01',NULL,NULL,1171),
(663,'SYS','3B6','体温单格式配置','ENR-W14',NULL,NULL,373),
(1176,'SYS','154','医院业务统计报表(病区)','RPT-W01',NULL,NULL,1171),
(665,'SYS','316','病案诊断类型设置','DIAG-W04',NULL,NULL,613),
(666,'SYS','132','报表格式打印机设置','SYS1-W04',NULL,NULL,405),
(669,'KTCIS_IDW','57','住院病历质量检查评分','EMR-W21',NULL,NULL,239);
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(673,'SYS','4S','包药机相关设置',NULL,NULL,NULL,516),
(674,'SYS','4S1','包药机设置','MED-W05',NULL,NULL,673),
(373,'SYS','3B','电子护理记录基础设置',NULL,NULL,NULL,517),
(675,'SYS','4M','摆药包药相关设置',NULL,NULL,NULL,516),
(676,'SYS','4M0','药品包药字典设置','PACK_MED-W01',NULL,NULL,675),
(304,'SYS','104','系统参数设置','SYS-W04',NULL,NULL,300),
(305,'SYS','105','个人参数设置','SYS-W05',NULL,NULL,300),
(306,'SYS','106','机器参数设置','SYS-W06',NULL,NULL,300),
(307,'SYS','107','系统机器设置','SYS-W07',NULL,NULL,300),
(308,'SYS','108','通知发送','SYS-W08',NULL,NULL,300);
INSERT INTO TB ("MENU_ID","SYSTEM_CODE","ORDER_NO","TITLE","OBJECT_CODE","ICO","ICO_LAYOUT","MENU_ID_PARENT") VALUES
(309,'SYS','109','系统文件发布','SYS-W09',NULL,NULL,300),
(310,'SYS','10A','系统文件查询','SYS-W10',NULL,NULL,300),
(311,'SYS','10B','带功能的窗体测试','SYS-W99',NULL,NULL,300),
(312,'SYS','11','行政组织',NULL,NULL,NULL,299),
(325,'SYS','12','医院基础设置',NULL,NULL,NULL,299),
(326,'SYS','311','数据字典设置','PUB2-W01',NULL,NULL,613),
(327,'SYS','121','行政区域设置','PUB2-W02',NULL,NULL,325),
(329,'SYS','1251','自定义字段设置','PUB2-W05',NULL,NULL,614),
(330,'SYS','124','医疗工作点设置','PUB2-W06',NULL,NULL,325),
(331,'SYS','1252','系统条件参数设置','PUB2-W07',NULL,NULL,614);
5.1 确认insert后当前事务日志及wal事务号
select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
--查询insert后，delete前的当前事务日志及事务(LSN)号
test=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+------------------------------------
0/17D4E28 | 000000010000000000000001 | (000000010000000000000001,8212008)
(1 row)
test=# select count(*) from tb;
count
-------
40
(1 row)
5.2 进行删除数据操作并且手动执行vacuum
test=# delete from tb;
DELETE 40
test=# vacuum tb;
VACUUM
test=# select count(*) from tb;
count
-------
0
(1 row)
--查询删除数据并且手动执行checkpoint命令后，当前事务日志及事务(LSN)号
test=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+------------------------------------
0/17D9728 | 000000010000000000000001 | (000000010000000000000001,8230696)
(1 row)
5.3 根据查询到的事务号以及日志文件，查找WAL日志确定恢复数据的范围
--通过wal日志事物号找到delete的大概位置，这里位置是 564
[kingbase@postgres sys_wal]$ pwd
/home/kingbase/pg_data/sys_wal
[kingbase@postgres sys_wal]$ ls -l
total 16384
-rw------- 1 kingbase kingbase 16777216 Dec 16 14:10 000000010000000000000001
drwx------ 2 kingbase kingbase 6 Dec 16 10:26 archive_status
--如果删除数据量大，涉及多个wal日志，也可以指定事务日志起始wal文件名，结束wal日志文件名称
--示例 sys_waldump 000000010000000000000001 000000010000000000000009 -s 0/17AF9D0
[kingbase@postgres sys_wal]$ sys_waldump 000000010000000000000001 -s 0/17D4E28
rmgr: Heap len (rec/tot): 59/ 6339, tx: 564, lsn: 0/017D4E28, prev 0/017D4DF8, desc: DELETE off 41 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6708, prev 0/017D4E28, desc: DELETE off 42 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6740, prev 0/017D6708, desc: DELETE off 43 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6778, prev 0/017D6740, desc: DELETE off 44 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D67B0, prev 0/017D6778, desc: DELETE off 45 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D67E8, prev 0/017D67B0, desc: DELETE off 46 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6820, prev 0/017D67E8, desc: DELETE off 47 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6858, prev 0/017D6820, desc: DELETE off 48 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6890, prev 0/017D6858, desc: DELETE off 49 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D68C8, prev 0/017D6890, desc: DELETE off 50 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6900, prev 0/017D68C8, desc: DELETE off 51 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6938, prev 0/017D6900, desc: DELETE off 52 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6970, prev 0/017D6938, desc: DELETE off 53 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D69A8, prev 0/017D6970, desc: DELETE off 54 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D69E0, prev 0/017D69A8, desc: DELETE off 55 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6A18, prev 0/017D69E0, desc: DELETE off 56 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6A50, prev 0/017D6A18, desc: DELETE off 57 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6A88, prev 0/017D6A50, desc: DELETE off 58 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6AC0, prev 0/017D6A88, desc: DELETE off 59 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6AF8, prev 0/017D6AC0, desc: DELETE off 60 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6B30, prev 0/017D6AF8, desc: DELETE off 61 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6B68, prev 0/017D6B30, desc: DELETE off 62 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6BA0, prev 0/017D6B68, desc: DELETE off 63 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6BD8, prev 0/017D6BA0, desc: DELETE off 64 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6C10, prev 0/017D6BD8, desc: DELETE off 65 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6C48, prev 0/017D6C10, desc: DELETE off 66 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6C80, prev 0/017D6C48, desc: DELETE off 67 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6CB8, prev 0/017D6C80, desc: DELETE off 68 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6CF0, prev 0/017D6CB8, desc: DELETE off 69 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6D28, prev 0/017D6CF0, desc: DELETE off 70 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6D60, prev 0/017D6D28, desc: DELETE off 71 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6D98, prev 0/017D6D60, desc: DELETE off 72 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6DD0, prev 0/017D6D98, desc: DELETE off 73 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6E08, prev 0/017D6DD0, desc: DELETE off 74 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6E40, prev 0/017D6E08, desc: DELETE off 75 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6E78, prev 0/017D6E40, desc: DELETE off 76 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6EB0, prev 0/017D6E78, desc: DELETE off 77 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6EE8, prev 0/017D6EB0, desc: DELETE off 78 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6F20, prev 0/017D6EE8, desc: DELETE off 79 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6F58, prev 0/017D6F20, desc: DELETE off 80 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 564, lsn: 0/017D6F90, prev 0/017D6F58, desc: COMMIT 2022-12-16 14:10:35.799685 CST
rmgr: Standby len (rec/tot): 46/ 46, tx: 0, lsn: 0/017D6FB8, prev 0/017D6F90, desc: RUNNING_XACTS nextXid 565 latestCompletedXid 24997776 oldestRunningXid 564
rmgr: Heap2 len (rec/tot): 214/ 214, tx: 0, lsn: 0/017D6FE8, prev 0/017D6FB8, desc: CLEAN remxid 564, blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap2 len (rec/tot): 214/ 214, tx: 0, lsn: 0/017D70C0, prev 0/017D6FE8, desc: CLEAN remxid 564, blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap2 len (rec/tot): 64/ 8256, tx: 0, lsn: 0/017D7198, prev 0/017D70C0, desc: VISIBLE cutoff xid 0 flags 0x03, blkref #0: rel 1663/13857/16388 fork vm blk 0 FPW, blkref #1: rel 1663/13857/16388 blk 0
rmgr: Standby len (rec/tot): 42/ 42, tx: 565, lsn: 0/017D91F0, prev 0/017D7198, desc: LOCK xid 565 db 13857 rel 16388
rmgr: Storage len (rec/tot): 46/ 46, tx: 565, lsn: 0/017D9220, prev 0/017D91F0, desc: TRUNCATE base/13857/16388 to 0 blocks flags 7
rmgr: Heap len (rec/tot): 53/ 1081, tx: 565, lsn: 0/017D9250, prev 0/017D9220, desc: INPLACE off 3, blkref #0: rel 1663/13857/1259 blk 0 FPW
rmgr: Transaction len (rec/tot): 98/ 98, tx: 565, lsn: 0/017D9690, prev 0/017D9250, desc: COMMIT 2022-12-16 14:10:46.815167 CST; inval msgs: catcache 62 catcache 61 relcache 16388
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 0/017D96F8, prev 0/017D9690, desc: RUNNING_XACTS nextXid 566 latestCompletedXid 25007760 oldestRunningXid 566
sys_waldump: fatal: error in WAL record at 0/17D96F8: invalid record length at 0/17D9728: wanted 24, got 0
--通过以下命令进行在wal日志精确定位事务号
rmgr: Heap len (rec/tot): 59/ 6339, tx: 564, lsn: 0/017D4E28, prev 0/017D4DF8, desc: DELETE off 41 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6708, prev 0/017D4E28, desc: DELETE off 42 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6740, prev 0/017D6708, desc: DELETE off 43 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6778, prev 0/017D6740, desc: DELETE off 44 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D67B0, prev 0/017D6778, desc: DELETE off 45 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D67E8, prev 0/017D67B0, desc: DELETE off 46 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6820, prev 0/017D67E8, desc: DELETE off 47 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6858, prev 0/017D6820, desc: DELETE off 48 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6890, prev 0/017D6858, desc: DELETE off 49 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D68C8, prev 0/017D6890, desc: DELETE off 50 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6900, prev 0/017D68C8, desc: DELETE off 51 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6938, prev 0/017D6900, desc: DELETE off 52 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6970, prev 0/017D6938, desc: DELETE off 53 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D69A8, prev 0/017D6970, desc: DELETE off 54 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D69E0, prev 0/017D69A8, desc: DELETE off 55 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6A18, prev 0/017D69E0, desc: DELETE off 56 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6A50, prev 0/017D6A18, desc: DELETE off 57 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6A88, prev 0/017D6A50, desc: DELETE off 58 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6AC0, prev 0/017D6A88, desc: DELETE off 59 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6AF8, prev 0/017D6AC0, desc: DELETE off 60 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6B30, prev 0/017D6AF8, desc: DELETE off 61 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6B68, prev 0/017D6B30, desc: DELETE off 62 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6BA0, prev 0/017D6B68, desc: DELETE off 63 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6BD8, prev 0/017D6BA0, desc: DELETE off 64 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6C10, prev 0/017D6BD8, desc: DELETE off 65 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6C48, prev 0/017D6C10, desc: DELETE off 66 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6C80, prev 0/017D6C48, desc: DELETE off 67 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6CB8, prev 0/017D6C80, desc: DELETE off 68 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6CF0, prev 0/017D6CB8, desc: DELETE off 69 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6D28, prev 0/017D6CF0, desc: DELETE off 70 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6D60, prev 0/017D6D28, desc: DELETE off 71 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6D98, prev 0/017D6D60, desc: DELETE off 72 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6DD0, prev 0/017D6D98, desc: DELETE off 73 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6E08, prev 0/017D6DD0, desc: DELETE off 74 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6E40, prev 0/017D6E08, desc: DELETE off 75 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6E78, prev 0/017D6E40, desc: DELETE off 76 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6EB0, prev 0/017D6E78, desc: DELETE off 77 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6EE8, prev 0/017D6EB0, desc: DELETE off 78 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6F20, prev 0/017D6EE8, desc: DELETE off 79 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6F58, prev 0/017D6F20, desc: DELETE off 80 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
5.4 进行恢复删除数据操作
--停止数据库
[kingbase@postgres ~]$ sys_ctl -D pg_data/ stop
waiting for server to shut down.... done
server stopped
--使用cp对当前data目录进行备份
[kingbase@postgres ~]$ cp -r pg_data/ pg_data_bak
[kingbase@postgres ~]$ ls -l
total 808
drwxrwxr-x 3 kingbase kingbase 16 Nov 10 01:12 V8R6C6B21
-rwxr-xr-x 1 kingbase kingbase 3276 Nov 10 00:31 license.dat
-rw-rw-r-- 1 kingbase kingbase 252 Dec 6 16:48 my_test.c
-rw-rw-r-- 1 kingbase kingbase 10403 Dec 13 18:21 nohup.out
drwx------ 22 kingbase kingbase 4096 Dec 16 10:26 ora_data
drwx------ 23 kingbase kingbase 4096 Dec 16 14:17 pg_data
drwx------ 23 kingbase kingbase 4096 Dec 16 14:17 pg_data_bak
--使用备份的data目录，通过指定事务号进行删除数据的恢复。
--使用sys_resetwal工具指定事务号552进行删除数据的恢复
[kingbase@postgres ~]$ sys_resetwal -x 564 /home/kingbase/pg_data_bak/
Write-ahead log reset
--启动通过cp备份的数据库查询数据是否被恢复
[kingbase@postgres ~]$ sys_ctl -D /home/kingbase/pg_data_bak/ start
waiting for server to start....2022-12-16 14:20:32.872 CST [5064] LOG: sepapower extension initialized
2022-12-16 14:20:32.875 CST [5064] LOG: starting KingbaseES V008R006C006B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-12-16 14:20:32.876 CST [5064] LOG: listening on IPv4 address "0.0.0.0", port 54321
2022-12-16 14:20:32.876 CST [5064] LOG: listening on IPv6 address "::", port 54321
2022-12-16 14:20:32.879 CST [5064] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"
2022-12-16 14:20:32.915 CST [5064] LOG: redirecting log output to logging collector process
2022-12-16 14:20:32.915 CST [5064] HINT: Future log output will appear in directory "sys_log".
done
server started
[kingbase@postgres ~]$ ksql -Usystem -dtest
ksql (V8.0)
Type "help" for help.
test=# select count(*) from tb;
count
-------
0
(1 row)
经确认，如果数据被删除并且手动执行了vacuum或者系统自动触发了vacuum操作，被删除的数据是无法被恢复。
--在实际的场景中需要留意在被删除数据事务号后wal日志是否有rmgr: Heap2或者CLEAN remxid 564(xid)记录.
--资源rmgr Heap2的记录与vacuum有关，这里是测试表TB的 vacuum操作
test=# select pg_relation_filepath('tb');
pg_relation_filepath
----------------------
base/13857/16388
(1 row)
test=# select pg_relation_filenode('tb');
pg_relation_filenode
----------------------
16388
(1 row)
rmgr: Heap len (rec/tot): 54/ 54, tx: 564, lsn: 0/017D6F58, prev 0/017D6F20, desc: DELETE off 80 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/13857/16388 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 564, lsn: 0/017D6F90, prev 0/017D6F58, desc: COMMIT 2022-12-16 14:10:35.799685 CST
rmgr: Standby len (rec/tot): 46/ 46, tx: 0, lsn: 0/017D6FB8, prev 0/017D6F90, desc: RUNNING_XACTS nextXid 565 latestCompletedXid 24997776 oldestRunningXid 564
rmgr: Heap2 len (rec/tot): 214/ 214, tx: 0, lsn: 0/017D6FE8, prev 0/017D6FB8, desc: CLEAN remxid 564, blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap2 len (rec/tot): 214/ 214, tx: 0, lsn: 0/017D70C0, prev 0/017D6FE8, desc: CLEAN remxid 564, blkref #0: rel 1663/13857/16388 blk 0
rmgr: Heap2 len (rec/tot): 64/ 8256, tx: 0, lsn: 0/017D7198, prev 0/017D70C0, desc: VISIBLE cutoff xid 0 flags 0x03, blkref #0: rel 1663/13857/16388 fork vm blk 0 FPW, blkref #1: rel 1663/13857/16388 blk 0
rmgr: Standby len (rec/tot): 42/ 42, tx: 565, lsn: 0/017D91F0, prev 0/017D7198, desc: LOCK xid 565 db 13857 rel 16388
rmgr: Storage len (rec/tot): 46/ 46, tx: 565, lsn: 0/017D9220, prev 0/017D91F0, desc: TRUNCATE base/13857/16388 to 0 blocks flags 7
- 通过SYS_RESETWAL工具恢复被删除数据的测试总结：
6.1. 数据被删除后，未执行checkpoint，vacuum操作，被删除数据可以通过sys_resetwal工具恢复。(前提是WAL日志文件存在且文件未损坏)。
6.2. 数据被删除后，执行checkpoint，未进行vacuum操作，被删除数据可以通过sys_resetwal工具恢复。(前提是WAL日志文件存在且文件未损坏)。
6.3. 数据被删除后，不管是手动执行还是系统自动执行vacuum操作，被删除的数据都无法通过sys_resetwal工具恢复。
注意：在进行了sys_resetwal操作后，设置的LSN 号后的数据都取消了，相当于回滚到指定的事务LSN 号的位置。（在生产进行此操作必须先备份data目录，然后在备份的data目录进行操作，一定要谨慎谨慎再谨慎）
SYS_RESETWAL操作会把DATA目录在线WAL 日志进行清理，原有的日志都会被清除
恢复之前的wal日志
[kingbase@postgres sys_wal]$ ll total 98304 -rw------- 1 kingbase kingbase 16777216 Nov 11 12:15 000000010000000000000007 -rw------- 1 kingbase kingbase 16777216 Nov 11 12:11 000000010000000000000008 -rw------- 1 kingbase kingbase 16777216 Nov 11 12:11 000000010000000000000009 -rw------- 1 kingbase kingbase 16777216 Nov 11 12:11 00000001000000000000000A -rw------- 1 kingbase kingbase 16777216 Nov 11 12:11 00000001000000000000000B -rw------- 1 kingbase kingbase 16777216 Nov 11 12:11 00000001000000000000000C drwx------ 2 kingbase kingbase 6 Nov 11 12:08 archive_status恢复之后的日志：
[kingbase@postgres sys_wal]$ ll total 16384 -rw------- 1 kingbase kingbase 16777216 Nov 11 13:17 00000001000000000000000D drwx------ 2 kingbase kingbase 6 Nov 11 12:08 archive_status [kingbase@postgres sys_wal]$WAL日志切换
自之前的WAL日志文件切换以后如果没有新的WAL日志生成，就不会创建新的 WAL 日志文件,也就是说手动切换wal日志，需要有新数据生成才会发生切换，如果没有新数据生成，手动执行sys_switch_wal也无法进行wal日志切换。 有新的数据产生，执行sys_switch_wal就可以切换wal日志： SELECT sys_walfile_name(sys_switch_wal()), now(), sys_walfile_name(sys_switch_wal());
KINGBASE研究院
