KingbaseES通过sys_waldump解析wal日志
前言
oracle中的redo日志我们无法直接读取，然而对于KingbaseES数据库，我们可以利用sys_waldump工具解析wal日志，查看wal日志记录的信息。
我们可以利用 sys_waldump --rmgr=list看一下有哪些管理类型：
[
](javascript:void(0)😉
[kingbase@localhost data]$ sys_waldump --rmgr=list
XLOG
Transaction
Storage
CLOG
Database
Tablespace
TablespaceEnc
TabcolumnEnc
MultiXact
RelMap
Standby
Heap2
Heap
Btree
Hash
Gin
Gist
Sequence
SPGist
BRIN
CommitTs
ReplicationOrigin
Generic
LogicalMessage
[](javascript:void(0)😉
实验
我们进行一些简单的ddl，dml操作，并同时查看数据库的当前的wal日志中记录位置。
[](javascript:void(0)😉
test=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
4/7321F5C8
(1 row)
test=# create database testq;
CREATE DATABASE
test=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
4/7321FCA8
(1 row)
test=# create table ddd (id int);
CREATE TABLE
test=#
test=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
4/73229928
(1 row)
test=# insert into ddd values (1);
INSERT 0 1
test=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
4/732299F0
(1 row)
test=# insert into ddd values (2);
INSERT 0 1
test=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
4/73229A88
(1 row)
test=# insert into ddd values (3);
INSERT 0 1
test=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
4/73229B20
(1 row)
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
--------------------+--------------------------+-----------------------------------
4/73229B20 | 000000010000000400000073 | (000000010000000400000073,2267936)
(1 row)
[](javascript:void(0)😉
然后通过sys_waldump查询wal日志中的对应记录信息。进入sys_wal目录 #表示注释，不是实际wal日志中记录的内容。
[](javascript:void(0)😉
sys_waldump 000000010000000400000073 -s 4/7321F5C8 rmgr: Heap len (rec/tot): 56/ 706, tx: 115749, lsn: 4/7321F5C8, prev 4/7321F598, desc: INSERT off 18 flags 0x00, blkref #0: rel 1664/0/1262 blk 0 FPW
rmgr: Btree len (rec/tot): 55/ 298, tx: 115749, lsn: 4/7321F890, prev 4/7321F5C8, desc: INSERT_LEAF off 12, blkref #0: rel 1664/0/146276 blk 1 FPW
rmgr: Btree len (rec/tot): 55/ 239, tx: 115749, lsn: 4/7321F9C0, prev 4/7321F890, desc: INSERT_LEAF off 12, blkref #0: rel 1664/0/146277 blk 1 FPW
rmgr: Standby len (rec/tot): 46/ 46, tx: 0, lsn: 4/7321FAB0, prev 4/7321F9C0, desc: RUNNING_XACTS nextXid 115750 latestCompletedXid 1931606464 oldestRunningXid 115749
rmgr: Standby len (rec/tot): 46/ 46, tx: 0, lsn: 4/7321FAE0, prev 4/7321FAB0, desc: RUNNING_XACTS nextXid 115750 latestCompletedXid 1931606752 oldestRunningXid 115749
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 4/7321FB10, prev 4/7321FAE0, desc: CHECKPOINT_ONLINE redo 4/7321FAE0; tli 1; prev tli 1; fpw true; xid 0:115750; oid 211935; multi 1; offset 0; oldest xid 867 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 115749; online
#CHECKPOINT_ONLINE redo是日志中记录的checkpoint位置有关信息，这个很重要，日志中会频繁出现，create database 前后分别记录了检查点信息。记住这是数据库ddl操作。（曾经遇到过create database等待检查点操作，而检查点操作正在缓慢的进行，由于copy导入几十GB数据量）
rmgr: Database len (rec/tot): 42/ 42, tx: 115749, lsn: 4/7321FB88, prev 4/7321FB10, desc: CREATE copy dir 1663/16051 to 1663/203764
rmgr: Standby len (rec/tot): 46/ 46, tx: 0, lsn: 4/7321FBB8, prev 4/7321FB88, desc: RUNNING_XACTS nextXid 115750 latestCompletedXid 1931606968 oldestRunningXid 115749
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 4/7321FBE8, prev 4/7321FBB8, desc: CHECKPOINT_ONLINE redo 4/7321FBB8; tli 1; prev tli 1; fpw true; xid 0:115750; oid 211935; multi 1; offset 0; oldest xid 867 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 115749; online
rmgr: Transaction len (rec/tot): 66/ 66, tx: 115749, lsn: 4/7321FC60, prev 4/7321FBE8, desc: COMMIT 2022-08-10 14:39:15.217942 CST; inval msgs: catcache 24; sync
#创建数据库直接从template拷贝文件到新建的数据库目录，创建完数据库记录检查点信息，然后commitrmgr: XLOG len (rec/tot): 51/ 698, tx: 0, lsn: 4/7321FCA8, prev 4/7321FC60, desc: FPI_FOR_HINT , blkref #0: rel 1664/0/1262 blk 0 FPW
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 4/7321FF68, prev 4/7321FCA8, desc: RUNNING_XACTS nextXid 115750 latestCompletedXid 1931607136 oldestRunningXid 115750
rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 4/7321FF98, prev 4/7321FF68, desc: CREATE base/16385/203765#在存储中创建ddd表的物理数据文件对象
rmgr: Heap len (rec/tot): 56/ 1626, tx: 115750, lsn: 4/7321FFC8, prev 4/7321FF98, desc: INSERT off 62 flags 0x00, blkref #0: rel 1663/16385/1247 blk 10 FPW
rmgr: Btree len (rec/tot): 55/ 1373, tx: 115750, lsn: 4/73220640, prev 4/7321FFC8, desc: INSERT_LEAF off 115, blkref #0: rel 1663/16385/2703 blk 4 FPW
rmgr: Btree len (rec/tot): 55/ 2866, tx: 115750, lsn: 4/73220BA0, prev 4/73220640, desc: INSERT_LEAF off 99, blkref #0: rel 1663/16385/2704 blk 5 FPW
rmgr: Heap len (rec/tot): 56/ 1938, tx: 115750, lsn: 4/732216D8, prev 4/73220BA0, desc: INSERT off 108 flags 0x00, blkref #0: rel 1663/16385/2608 blk 94 FPW
rmgr: Btree len (rec/tot): 55/ 2710, tx: 115750, lsn: 4/73221E70, prev 4/732216D8, desc: INSERT_LEAF off 247, blkref #0: rel 1663/16385/2673 blk 48 FPW
rmgr: Btree len (rec/tot): 55/ 2582, tx: 115750, lsn: 4/73222920, prev 4/73221E70, desc: INSERT_LEAF off 249, blkref #0: rel 1663/16385/2674 blk 69 FPW
rmgr: Heap len (rec/tot): 207/ 207, tx: 115750, lsn: 4/73223338, prev 4/73222920, desc: INSERT off 63 flags 0x00, blkref #0: rel 1663/16385/1247 blk 10
rmgr: Btree len (rec/tot): 64/ 64, tx: 115750, lsn: 4/73223408, prev 4/73223338, desc: INSERT_LEAF off 115, blkref #0: rel 1663/16385/2703 blk 4
rmgr: Btree len (rec/tot): 55/ 2870, tx: 115750, lsn: 4/73223448, prev 4/73223408, desc: INSERT_LEAF off 47, blkref #0: rel 1663/16385/2704 blk 1 FPW
rmgr: Heap len (rec/tot): 80/ 80, tx: 115750, lsn: 4/73223F80, prev 4/73223448, desc: INSERT off 109 flags 0x00, blkref #0: rel 1663/16385/2608 blk 94
rmgr: Btree len (rec/tot): 72/ 72, tx: 115750, lsn: 4/73223FD0, prev 4/73223F80, desc: INSERT_LEAF off 247, blkref #0: rel 1663/16385/2673 blk 48
rmgr: Btree len (rec/tot): 55/ 1752, tx: 115750, lsn: 4/73224030, prev 4/73223FD0, desc: INSERT_LEAF off 168, blkref #0: rel 1663/16385/2674 blk 40 FPW
rmgr: Heap len (rec/tot): 56/ 1500, tx: 115750, lsn: 4/73224708, prev 4/73224030, desc: INSERT off 54 flags 0x00, blkref #0: rel 1663/16385/1259 blk 0 FPW
rmgr: Btree len (rec/tot): 55/ 1058, tx: 115750, lsn: 4/73224CE8, prev 4/73224708, desc: INSERT_LEAF off 87, blkref #0: rel 1663/16385/2662 blk 5 FPW
rmgr: Btree len (rec/tot): 55/ 3506, tx: 115750, lsn: 4/73225110, prev 4/73224CE8, desc: INSERT_LEAF off 79, blkref #0: rel 1663/16385/2663 blk 1 FPW
rmgr: Btree len (rec/tot): 55/ 2599, tx: 115750, lsn: 4/73225EC8, prev 4/73225110, desc: INSERT_LEAF off 243, blkref #0: rel 1663/16385/3455 blk 4 FPW
rmgr: Heap len (rec/tot): 56/ 709, tx: 115750, lsn: 4/73226908, prev 4/73225EC8, desc: INSERT off 17 flags 0x00, blkref #0: rel 1663/16385/1249 blk 119 FPW
rmgr: Btree len (rec/tot): 55/ 3182, tx: 115750, lsn: 4/73226BD0, prev 4/73226908, desc: INSERT_LEAF off 254, blkref #0: rel 1663/16385/2658 blk 28 FPW
rmgr: Btree len (rec/tot): 55/ 3700, tx: 115750, lsn: 4/73227840, prev 4/73226BD0, desc: INSERT_LEAF off 330, blkref #0: rel 1663/16385/2659 blk 19 FPW
rmgr: Heap len (rec/tot): 175/ 175, tx: 115750, lsn: 4/732286D0, prev 4/73227840, desc: INSERT off 50 flags 0x00, blkref #0: rel 1663/16385/1249 blk 119
rmgr: Btree len (rec/tot): 72/ 72, tx: 115750, lsn: 4/73228780, prev 4/732286D0, desc: INSERT_LEAF off 254, blkref #0: rel 1663/16385/2658 blk 28
rmgr: Btree len (rec/tot): 64/ 64, tx: 115750, lsn: 4/732287C8, prev 4/73228780, desc: INSERT_LEAF off 330, blkref #0: rel 1663/16385/2659 blk 19
rmgr: Heap len (rec/tot): 175/ 175, tx: 115750, lsn: 4/73228808, prev 4/732287C8, desc: INSERT off 51 flags 0x00, blkref #0: rel 1663/16385/1249 blk 119
rmgr: Btree len (rec/tot): 72/ 72, tx: 115750, lsn: 4/732288B8, prev 4/73228808, desc: INSERT_LEAF off 256, blkref #0: rel 1663/16385/2658 blk 28
rmgr: Btree len (rec/tot): 64/ 64, tx: 115750, lsn: 4/73228900, prev 4/732288B8, desc: INSERT_LEAF off 330, blkref #0: rel 1663/16385/2659 blk 19
rmgr: Heap len (rec/tot): 175/ 175, tx: 115750, lsn: 4/73228940, prev 4/73228900, desc: INSERT off 52 flags 0x00, blkref #0: rel 1663/16385/1249 blk 119
rmgr: Btree len (rec/tot): 72/ 72, tx: 115750, lsn: 4/732289F0, prev 4/73228940, desc: INSERT_LEAF off 254, blkref #0: rel 1663/16385/2658 blk 28
rmgr: Btree len (rec/tot): 64/ 64, tx: 115750, lsn: 4/73228A38, prev 4/732289F0, desc: INSERT_LEAF off 330, blkref #0: rel 1663/16385/2659 blk 19
rmgr: Heap len (rec/tot): 175/ 175, tx: 115750, lsn: 4/73228A78, prev 4/73228A38, desc: INSERT off 53 flags 0x00, blkref #0: rel 1663/16385/1249 blk 119
rmgr: Btree len (rec/tot): 72/ 72, tx: 115750, lsn: 4/73228B28, prev 4/73228A78, desc: INSERT_LEAF off 257, blkref #0: rel 1663/16385/2658 blk 28
rmgr: Btree len (rec/tot): 64/ 64, tx: 115750, lsn: 4/73228B70, prev 4/73228B28, desc: INSERT_LEAF off 330, blkref #0: rel 1663/16385/2659 blk 19
rmgr: Heap len (rec/tot): 175/ 175, tx: 115750, lsn: 4/73228BB0, prev 4/73228B70, desc: INSERT off 54 flags 0x00, blkref #0: rel 1663/16385/1249 blk 119
rmgr: Btree len (rec/tot): 72/ 72, tx: 115750, lsn: 4/73228C60, prev 4/73228BB0, desc: INSERT_LEAF off 254, blkref #0: rel 1663/16385/2658 blk 28
rmgr: Btree len (rec/tot): 64/ 64, tx: 115750, lsn: 4/73228CA8, prev 4/73228C60, desc: INSERT_LEAF off 330, blkref #0: rel 1663/16385/2659 blk 19
rmgr: Heap len (rec/tot): 175/ 175, tx: 115750, lsn: 4/73228CE8, prev 4/73228CA8, desc: INSERT off 55 flags 0x00, blkref #0: rel 1663/16385/1249 blk 119
rmgr: Btree len (rec/tot): 72/ 72, tx: 115750, lsn: 4/73228D98, prev 4/73228CE8, desc: INSERT_LEAF off 258, blkref #0: rel 1663/16385/2658 blk 28
rmgr: Btree len (rec/tot): 64/ 64, tx: 115750, lsn: 4/73228DE0, prev 4/73228D98, desc: INSERT_LEAF off 330, blkref #0: rel 1663/16385/2659 blk 19
rmgr: Heap len (rec/tot): 80/ 80, tx: 115750, lsn: 4/73228E20, prev 4/73228DE0, desc: INSERT off 110 flags 0x00, blkref #0: rel 1663/16385/2608 blk 94
rmgr: Btree len (rec/tot): 55/ 1409, tx: 115750, lsn: 4/73228E70, prev 4/73228E20, desc: INSERT_LEAF off 124, blkref #0: rel 1663/16385/2673 blk 55 FPW
rmgr: Btree len (rec/tot): 55/ 697, tx: 115750, lsn: 4/732293F8, prev 4/73228E70, desc: INSERT_LEAF off 57, blkref #0: rel 1663/16385/2674 blk 44 FPW#以上是忘系统表以及系统索引插入信息，包括pg_attribute，pg_type，pg_depend_depender_index，pg_attribute_relid_attnam_index等
rmgr: Standby len (rec/tot): 42/ 42, tx: 115750, lsn: 4/732296B8, prev 4/732293F8, desc: LOCK xid 115750 db 16385 rel 203765
rmgr: Heap len (rec/tot): 113/ 113, tx: 115750, lsn: 4/732296E8, prev 4/732296B8, desc: HOT_UPDATE off 17 xmax 115750 flags 0x20 ; new off 56 xmax 0, blkref #0: rel 1663/16385/1249 blk 119
rmgr: Transaction len (rec/tot): 453/ 453, tx: 115750, lsn: 4/73229760, prev 4/732296E8, desc: COMMIT 2022-08-10 14:39:37.074690 CST; inval msgs: catcache 7 catcache 6 catcache 101 catcache 100 catcache 101 catcache 100 catcache 61 catcache 60 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 relcache 203765 snapshot 2608 relcache 203765
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 4/73229928, prev 4/73229760, desc: RUNNING_XACTS nextXid 115751 latestCompletedXid 1931646816 oldestRunningXid 115751
rmgr: Heap len (rec/tot): 59/ 59, tx: 115751, lsn: 4/73229958, prev 4/73229928, desc: INSERT+INIT off 1 flags 0x00, blkref #0: rel 1663/16385/203765 blk 0
rmgr: Transaction len (rec/tot): 38/ 38, tx: 115751, lsn: 4/73229998, prev 4/73229958, desc: COMMIT 2022-08-10 14:40:04.027177 CST
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 4/732299C0, prev 4/73229998, desc: RUNNING_XACTS nextXid 115752 latestCompletedXid 1931647384 oldestRunningXid 115752
rmgr: Heap len (rec/tot): 59/ 59, tx: 115752, lsn: 4/732299F0, prev 4/732299C0, desc: INSERT off 2 flags 0x00, blkref #0: rel 1663/16385/203765 blk 0
rmgr: Transaction len (rec/tot): 38/ 38, tx: 115752, lsn: 4/73229A30, prev 4/732299F0, desc: COMMIT 2022-08-10 14:40:43.151810 CST
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 4/73229A58, prev 4/73229A30, desc: RUNNING_XACTS nextXid 115753 latestCompletedXid 1931647536 oldestRunningXid 115753
rmgr: Heap len (rec/tot): 59/ 59, tx: 115753, lsn: 4/73229A88, prev 4/73229A58, desc: INSERT off 3 flags 0x00, blkref #0: rel 1663/16385/203765 blk 0
rmgr: Transaction len (rec/tot): 38/ 38, tx: 115753, lsn: 4/73229AC8, prev 4/73229A88, desc: COMMIT 2022-08-10 14:41:05.048269 CST#这里完成了表中三条insert记录 对应3个heap。并且commmit。同时备库完成同样操作。
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 4/73229AF0, prev 4/73229AC8, desc: RUNNING_XACTS nextXid 115754 latestCompletedXid 1931647688 oldestRunningXid 115754
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 4/73229B20, prev 4/73229AF0, desc: RUNNING_XACTS nextXid 115754 latestCompletedXid 1931647776 oldestRunningXid 115754
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 4/73229B50, prev 4/73229B20, desc: CHECKPOINT_ONLINE redo 4/73229B20; tli 1; prev tli 1; fpw true; xid 0:115754; oid 211935; multi 1; offset 0; oldest xid 867 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 115754; online#所有事务完成后再次记录checkpint信息。
rmgr: Standby len (rec/tot): 42/ 42, tx: 0, lsn: 4/73229BC8, prev 4/73229B50, desc: RUNNING_XACTS nextXid 115754 latestCompletedXid 1931647824 oldestRunningXid 115754
sys_waldump: fatal: error in WAL record at 4/73229BC8: invalid record length at 4/73229BF8: wanted 24, got 0
[](javascript:void(0)😉
在查看wal日志内容时，commit时间至关重要。这对于不完全恢复时间点的选择是重要的参考依据。
除此我们还可以打印出两个日志之间的信息：
sys_waldump 000000010000000400000072 000000010000000400000073 |more
还可以查看特定日志中transaction信息：
sys_waldump -b -f -r transaction 000000010000000400000073
也可以通过xid查看日志中信息：
sys_waldump 000000010000000400000073 -x '115718'
使用-z参数进行每种类型的分类统计。不过可能不常用。
sys_waldump 000000010000000400000073 -z
-f跟踪接下后要发生的日志信息，并且只查看database类有关记录：
sys_waldump -b -f -r database 000000010000000400000073
KINGBASE研究院
