# WalMiner **Repository Path**: skykiker/XLogMiner ## Basic Information - **Project Name**: WalMiner - **Description**: walminer是PostgreSQL WAL的解析工具，可将WAL解析成原始SQL和undo SQL生成。这个分支对原始walminer进行了一些增强 - **Primary Language**: C - **License**: MIT - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 8 - **Forks**: 59 - **Created**: 2020-03-25 - **Last Updated**: 2024-07-24 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README WalMiner ===== # 什么是WalMiner WalMiner是从PostgreSQL的WAL(write ahead logs)日志中解析出执行的SQL语句的工具，并能生成出对应的undo SQL语句。 WalMiner可用于以下使用场景 1. HA故障切换后，从旧主解析出丢失的数据供业务修单 2. 误删或误改数据后快速找回 3. 其他需要从WAL中解析出SQL的场景 本分支基于WalMiner，并做了如下改进和变更 1. 使用单个wal2sql()函数执行WAL解析任务 2. 支持指定起始和结束LSN位置过滤事务 3. 支持从WAL记录的old tuple或old key tuple中解析old元组构造where条件 4. 增加lsn和commit_end_lsn结果输出字段 5. 添加FPI(FULL PAGE IMAGE)解析开关，默认关闭image解析 6. 优化WAL解析速度，大约提升10倍 7. 给定LSN起始位置后，支持根据WAL文件名筛选，避免大量冗余的文件读取。 8. 修复多个解析BUG 9. 增加回归测试集 同时增加以下限制 1. 不支持从外部导入字典 2. WAL日志级别必须配置为logical 3. PG11/PG12中WAL文件大小必须16MB # 配置要求 需要将数据库日志级别配置为logical ``` wal_level=logical ``` ## PG版本支持 - PostgreSQL 10,11,12 # 编译安装 编译安装 ```shell cd walminer make && make install psql -c 'create extension walminer' ``` 回归测试 ```shell make installcheck ``` # 使用方法 ## 函数定义 ### wal2sql #### 功能 解析WAL。支持按LSN位置过滤。 #### 输入参数 - wal_dir text WAL目录路径,NULL代表当前库的pg_wal目录 - startlsn pg_lsn 事务的起始LSN位置，commit的结束位置大于该位点的事务匹配过滤条件。NULL代表不过滤 - endlsn pg_lsn = NULL 事务的结束LSN位置，commit的结束位置小于等于该位点的事务匹配过滤条件。NULL代表不过滤，默认为NULL。 - startlsn_scan_seg_advance int = 2 当startlsn不为空时，提前完全解析多少个WAL文件，默认为2。设置-1可以禁用该功能，禁用后可能会遇到解析遗漏或解析失败的BUG，而且有大量WAL时解析速度会更慢。 - use_history_image bool = false 是否解析并保存WAL中的历史FPI,保存历史FPI可以更好的解析出SQL，但是FPI依赖被解析事务位置到前一个checkpoint之间的WAL都被保留。默认为false。 - tempresult bool = false 是否输出临时结果。这个开关用来debug，正常用不到。默认为false。 #### 输出 redo 和undo SQL等 ### wal2sql_bytime #### 功能 解析WAL。支持按时间过滤。 #### 输入参数 - wal_dir text WAL目录路径,NULL代表当前库的pg_wal目录 - starttime timestampTz 事务范围的起始时间，commit的时间大于等于该位点的事务匹配过滤条件。NULL代表不过滤 - endtime timestampTz = NULL 事务范围的结束时间，commit的时间小于等于该位点的事务匹配过滤条件。NULL代表不过滤，默认为NULL。 - use_history_image bool = false 是否解析并保存WAL中的历史FPI,保存历史FPI可以更好的解析出SQL，但是FPI依赖被解析事务位置到前一个checkpoint之间的WAL都被保留。默认为false。 - tempresult bool = false 是否输出临时结果。这个开关用来debug，正常用不到。默认为false。 #### 输出 redo 和undo SQL等 ### wal2sql_byxid #### 功能 解析WAL。支持按时间过滤。 #### 输入参数 - wal_dir text WAL目录路径,NULL代表当前库的pg_wal目录 - startxid int 事务范围的起始事务ID，事务号大于等于该xid的事务匹配过滤条件。NULL代表不过滤 - endxid int = 0 事务范围的结束事务ID，事务号小于等于该xid的事务匹配过滤条件。NULL代表不过滤，默认为NULL。 - use_history_image bool = false 是否解析并保存WAL中的历史FPI,保存历史FPI可以更好的解析出SQL，但是FPI依赖被解析事务位置到前一个checkpoint之间的WAL都被保留。默认为false。 - tempresult bool = false 是否输出临时结果。这个开关用来debug，正常用不到。默认为false。 #### 输出 redo 和undo SQL等 ## 使用示例 指定起始LSN位置，解析WAL。 建表 ``` postgres=# create table tb1(id int primary key, c1 text); CREATE TABLE ``` 记录WAL位置 ``` postgres=# select pg_current_wal_lsn(); pg_current_wal_lsn -------------------- D/E7418F8 (1 row) ``` 对于真实故障切换场景，可以从新主的时间线历史中找到新主和旧主发生分叉的WAL位置，然后以该位置作为起始LSN到旧主上解析WAL可以找到故障切换后新主丢失的数据。 ``` [root@sndsdevdb18 walminer]# tail -1 /pgsql/data/pg_wal/00000003.history 2 1/31000098 no recovery target specified ``` 更新数据 ``` postgres=# insert into tb1 values(1,'xxx'); INSERT 0 1 postgres=# update tb1 set c1='yyy' where id=1; UPDATE 1 postgres=# delete from tb1 where id=1; DELETE 1 ``` 解析WAL ``` postgres=# \x Expanded display is on. postgres=# select * from wal2sql(NULL,'D/E7418F8'); NOTICE: Get data dictionary from current database. NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000C NOTICE: wal record after time 2020-03-22 00:19:14+08 or d/c894f60 will be analyse completely NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000D NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000E -[ RECORD 1 ]-----+--------------------------------------------------------------------------- xid | 1124529 virtualxid | 1 timestamptz | 2020-03-30 17:48:59.343712+08 record_database | postgres record_user | postgres record_tablespace | pg_default record_schema | public op_type | INSERT op_text | INSERT INTO "public"."tb1"("id", "c1") VALUES(1, 'xxx'); op_undo | DELETE FROM "public"."tb1" WHERE "id"=1 AND "c1"='xxx' AND ctid = '(0,1)'; lsn | D/E7418F8 commit_end_lsn | D/E7419F8 -[ RECORD 2 ]-----+--------------------------------------------------------------------------- xid | 1124530 virtualxid | 1 timestamptz | 2020-03-30 17:49:30.965638+08 record_database | postgres record_user | postgres record_tablespace | pg_default record_schema | public op_type | UPDATE op_text | UPDATE "public"."tb1" SET "c1" = 'yyy' WHERE "id"=1; op_undo | NULL lsn | D/E741A30 commit_end_lsn | D/E741AB0 -[ RECORD 3 ]-----+--------------------------------------------------------------------------- xid | 1124531 virtualxid | 1 timestamptz | 2020-03-30 17:49:49.371617+08 record_database | postgres record_user | postgres record_tablespace | pg_default record_schema | public op_type | DELETE op_text | DELETE FROM "public"."tb1" WHERE "id"=1; op_undo | NULL lsn | D/E741AE8 commit_end_lsn | D/E741B58 ``` 以上输出对于解析原始redo SQL已经足够，如果要解析完整的undo SQL，可以打开解析历史FPI的开关进行解析，如下。 ``` postgres=# select * from wal2sql(NULL,'D/E7418F8',NULL,2,true); NOTICE: Get data dictionary from current database. NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000C NOTICE: wal record after time 2020-03-22 00:19:14+08 or d/c894f60 will be analyse completely NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000D NOTICE: Change Wal Segment To:/pgsql/data/pg_wal/000000030000000D0000000E -[ RECORD 1 ]-----+--------------------------------------------------------------------------------------- xid | 1124529 virtualxid | 1 timestamptz | 2020-03-30 17:48:59.343712+08 record_database | postgres record_user | postgres record_tablespace | pg_default record_schema | public op_type | INSERT op_text | INSERT INTO "public"."tb1"("id", "c1") VALUES(1, 'xxx'); op_undo | DELETE FROM "public"."tb1" WHERE "id"=1 AND "c1"='xxx' AND ctid = '(0,1)'; lsn | D/E7418F8 commit_end_lsn | D/E7419F8 -[ RECORD 2 ]-----+--------------------------------------------------------------------------------------- xid | 1124530 virtualxid | 1 timestamptz | 2020-03-30 17:49:30.965638+08 record_database | postgres record_user | postgres record_tablespace | pg_default record_schema | public op_type | UPDATE op_text | UPDATE "public"."tb1" SET "c1" = 'yyy' WHERE "id"=1 AND "c1"='xxx'; op_undo | UPDATE "public"."tb1" SET "c1" = 'xxx' WHERE "id"=1 AND "c1"='yyy' AND ctid = '(0,2)'; lsn | D/E741A30 commit_end_lsn | D/E741AB0 -[ RECORD 3 ]-----+--------------------------------------------------------------------------------------- xid | 1124531 virtualxid | 1 timestamptz | 2020-03-30 17:49:49.371617+08 record_database | postgres record_user | postgres record_tablespace | pg_default record_schema | public op_type | DELETE op_text | DELETE FROM "public"."tb1" WHERE "id"=1 AND "c1"='yyy'; op_undo | INSERT INTO "public"."tb1"("id", "c1") VALUES(1, 'yyy'); lsn | D/E741AE8 commit_end_lsn | D/E741B58 ``` # 使用限制 1. 本版本只解析DML语句，不处理DDL语句 2. 执行了删除表、truncate表、更改表的表空间、更改表字段的类型、vacuum full，这样的DDL语句后，发生DDL语句之前的此表相关的DML语句不会再被解析。 3. 解析结果依赖于数据字典。（举例：创建表t1,所有者为user1，但是中间将所有者改为user2。那解析结果中，所有t1相关操作所有者都将标示为user2） 4. wal日志如果发生缺失，在缺失的wal日志中发生提交的数据，都不会在解析结果中出现 5. 解析结果中undo字段的ctid属性是发生变更“当时”的值，如果因为vacuum等操作导致ctid发生变更，这个值将不准确。对于有可能存在重复行的数据，我们需要通过这个值确定undo对应的tuple条数，不代表可以直接执行该undo语句。 6. 执行了表字段drop的DDL语句后,发生DDL语句之前的这个字段相关的值都会被解析为encode（'AD976BC56F'，hex）的形式，另外自定义类型也会解析为这种形式 7. 只能解析与数据字典时间线一致的wal文件 8. 不建议使用walminer解析大宗copy语句（在同一个事务中插入大量数据行）产生的wal日志,这会导致解析过程中的效率低下和内存占用过高 9. 为完整解析undo SQL或对于无主键的表解析redo SQL，需要设置表的replica identity FULL属性或打开历史image开关解析 # 附录：DML解析能力 不使用历史image(FPI),仅基于WAL记录本身记录的信息，从WAL解析成SQL的能力如下 | wal_level | replica identity FULL | 有主键或identity key | 主键有变更 | SQL类型 | 更新后tuple | 更新前tuple | redo解析 | undo解析 | | ------ | ------ | ------ | ------ | ------ | ------ | ------ | ------ | ------ | | logical | - | - | - | insert | FULL | - | YES | YES | | logical | YES | - | - | delete | - | FULL | YES | YES | | logical | YES | - | - | update | FULL | FULL | YES | YES | | logical | NO | 有主键 | - | delete | - | 仅KEY值 | YES(*1) | NO | | logical | NO | 有主键 | YES | update | FULL | 仅KEY值 | YES(*1) | NO | | logical | NO | 有主键 | NO | update | FULL | 无 | YES(*1) | NO | | logical | NO | 无主键 | - | delete | - | 无 | NO | NO | | logical | NO | 无主键 | - | update | FULL | 无 | NO(*2) | NO | | other | - | - | - | update | 仅被更新字段 | 无 | NO | NO | - *1)不能提供全量字段值相等的where条件限定，where条件中仅包含KEY值 - *2)只能以VALUES形式输出变更后tuple 基于上表，单纯从WAL记录解析SQL必须依赖logical，对于无主键的表还依赖于replica identity FULL。 通过解析历史image(FPI)，可以去掉了对logical和replica identity FULL的依赖。但解析历史image也存在以下限制 1. 必须开启full page write。这是默认值，问题不大。 2. 解析目的WAL到前一次checkpoint之间的WAL必须保留并被解析 # 联系我们 发现bug或者有好的建议可以提issue。