WalMiner

什么是WalMiner

WalMiner是从PostgreSQL的WAL(write ahead logs)日志中解析出执行的SQL语句的工具，并能生成出对应的undo SQL语句。

WalMiner可用于以下使用场景

HA故障切换后，从旧主解析出丢失的数据供业务修单
误删或误改数据后快速找回
其他需要从WAL中解析出SQL的场景

本分支基于WalMiner，并做了如下改进和变更

使用单个wal2sql()函数执行WAL解析任务
支持指定起始和结束LSN位置过滤事务
支持从WAL记录的old tuple或old key tuple中解析old元组构造where条件
增加lsn和commit_end_lsn结果输出字段
添加FPI(FULL PAGE IMAGE)解析开关，默认关闭image解析
优化WAL解析速度，大约提升10倍
给定LSN起始位置后，支持根据WAL文件名筛选，避免大量冗余的文件读取。
修复多个解析BUG
增加回归测试集

同时增加以下限制

不支持从外部导入字典
WAL日志级别必须配置为logical
PG11/PG12中WAL文件大小必须16MB

配置要求

需要将数据库日志级别配置为logical

wal_level=logical

PG版本支持

PostgreSQL 10,11,12

编译安装

cd walminer
make && make install
psql -c 'create extension walminer'

回归测试

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

使用限制

本版本只解析DML语句，不处理DDL语句
执行了删除表、truncate表、更改表的表空间、更改表字段的类型、vacuum full，这样的DDL语句后，发生DDL语句之前的此表相关的DML语句不会再被解析。
解析结果依赖于数据字典。（举例：创建表t1,所有者为user1，但是中间将所有者改为user2。那解析结果中，所有t1相关操作所有者都将标示为user2）
wal日志如果发生缺失，在缺失的wal日志中发生提交的数据，都不会在解析结果中出现
解析结果中undo字段的ctid属性是发生变更“当时”的值，如果因为vacuum等操作导致ctid发生变更，这个值将不准确。对于有可能存在重复行的数据，我们需要通过这个值确定undo对应的tuple条数，不代表可以直接执行该undo语句。
执行了表字段drop的DDL语句后,发生DDL语句之前的这个字段相关的值都会被解析为encode（'AD976BC56F'，hex）的形式，另外自定义类型也会解析为这种形式
只能解析与数据字典时间线一致的wal文件
不建议使用walminer解析大宗copy语句（在同一个事务中插入大量数据行）产生的wal日志,这会导致解析过程中的效率低下和内存占用过高
为完整解析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也存在以下限制

必须开启full page write。这是默认值，问题不大。
解析目的WAL到前一次checkpoint之间的WAL必须保留并被解析

联系我们

发现bug或者有好的建议可以提issue。

小桥河西 / WalMiner

WalMiner

什么是WalMiner

配置要求

PG版本支持

编译安装

使用方法

函数定义

wal2sql

功能

输入参数

输出

wal2sql_bytime

功能

输入参数

输出

wal2sql_byxid

功能

输入参数

输出

使用示例

使用限制

附录：DML解析能力

联系我们

简介

发行版 (2)

贡献者

近期动态

小桥河西 / WalMiner .gitee-modal { width: 500px !important; }

WalMiner

什么是WalMiner

配置要求

PG版本支持

编译安装

使用方法

函数定义

wal2sql

功能

输入参数

输出

wal2sql_bytime

功能

输入参数

输出

wal2sql_byxid

功能

输入参数

输出

使用示例

使用限制

附录：DML解析能力

联系我们

简介

发行版 (2)

贡献者

近期动态

搜索帮助

小桥河西 / WalMiner