PostgreSQL中WAL日志解析工具——WalMiner

原创李先生 2022-01-08

8203

WalMiner是从PostgreSQL的WAL(write ahead logs)日志中解析出执行的SQL语句的工具，并能生成对应的undo SQL语句。与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。

WalMiner背景

WAL日志

在PostgreSQL中，WAL日志记录了数据库重要数据文件的所有变化，你对数据的操作都会被记录到其中，WAL日志是保证数据完整性的一种标准方法，WAL的中心概念是数据文件（存储着表和索引）的修改必须在这些动作被日志记录之后才被写入，即在描述这些改变的日志记录被刷到持久存储以后。

由于WAL日志是二进制格式的文件，如果遇到wal日志增长的情况，并且我们甚至还不知道是什么样的数据变更引起的大量的WAL日志增长，我们就会想办法知道WAL日志到底记录了什么信息，如何读取PostgreSQL的WAL日志内容？但是目前并没有一款完善的wal日志解析工具，即使wal日志在那里我们也没有简便的方法来读出wal的内容（pg_waldump是wal的解析工具，但是它只会告诉你向哪个表发生了insert，但是没有insert的具体数据）。

so，WalMiner 工具就此问世…

什么是WalMiner

Walminer是从PostgreSQL的WAL(write ahead logs)日志的解析工具，它可以从wal日志中解析出用户执行的DML语句，以及用户执行DDL语句对系统表产生的DML语句。它的前身是xlogminer，但是xlogminer有较大的限制，比如wal日志级别需要是logical、需要将表改为FULL模式。Walminer则不需要这些限制，它可以解析普通的archive或replica级别以上的wal日志。

WalMiner 工具下载地址为：https://www.modb.pro/download/329654

WalMiner 工具代码开源地址为：https://gitee.com/movead/XLogMiner

WalMiner功能

WalMiner是从PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持。目前主要有如下功能：

从waL日志中解析出SQL，包括DML和少量DDL

解析出执行的SQL语句的工具，并能生成对应的undo SQL语句。与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。
数据页挽回

当数据库被执行了TRUNCATE等不被wal记录的数据清除操作，或者发生磁盘页损坏，可以使用此功能从wal日志中搜索数据，以期尽量挽回数据。

WalMiner解析原理

INSERT语句解析原理

从wal日志的一条insert类型的record中可以获取到relfilenode，结合数据字典就可以得到这个insert目标表的“表名”、“字段类型”、“字段名”。insert的实际数据在record中以“变更数据的方式”或者“FPW”的形式存在。获取这些数据后，结合表的字段类型，就可以拼接出这条insert语句。

DELETE语句解析原理

delete语句的解析中，获取目标表的表结构的过程是跟insert解析是一致的，但是在delete语句产生的record中一般是不存在delete的具体数据的（FPW除外），那如何才能获取这些数据呢？

在一个检查点后，第一次修改page时会进行PFW，因此虽然这个record中不存在我们想要的数据，但是在这个record之前的某个record中一定有这个page的FPW。因此我们可以在读到一个FPW后记录下这个FPW的数据，以供以后的解析使用。注意：每次提供的wal日志可能是有限的，因此每次解析很可能会存在一些无法找到其FPW的record，因此会有无法解析出的行。

获取到了这行delete的具体数据，那么就可以拼接这条delete语句了。

UPDATE语句解析原理

Update语句的解析原理就是一个delete一个insert。将旧的数据行delete，insert新的数据行，这里就不再赘述了。

WalMiner使用

Walminer可以在生产库（待解析wal日志的生成库）执行，也可以将wal日志和数据字典放到任意一个跟生产库配置相同的测试库解析。Walminer的具体使用情况在开源代码的readme中有详细的介绍，这里只介绍一下在测试库使用的情况。

编译安装

PG安装编译 ——使用yum或者pg安装包安装pg

配置pg的bin路径至环境变量

export PGHOME=/opt/pgsql13.2
export PATH=$PGHOME/bin:$PATH

进入walminer代码路径

[postgres@lyp ~]$ ll XLogMiner-master.zip 
-rw-r--r--. 1 ~ postgres 247496 Jan  8 11:29 XLogMiner-master.zip
[postgres@lyp ~]$ unzip XLogMiner-master.zip 
Archive:  XLogMiner-master.zip
f8e322361555cbe8f790c9dbdb448e9453f85950
   creating: XLogMiner-master/
 extracting: XLogMiner-master/.gitignore  
......................
.........省略.........
......................
  inflating: XLogMiner-master/walminer/wm_utils.c  
  inflating: XLogMiner-master/walminer/wm_utils.h  
[postgres@lyp contrib]$ cd XLogMiner-master/
[postgres@lyp XLogMiner-master]$ ll
total 24
-rw-rw-r--. 1 postgres postgres  1071 Dec 29 15:45 LICENSE
-rw-rw-r--. 1 postgres postgres     0 Dec 29 15:45 README.EN.MD
-rw-rw-r--. 1 postgres postgres 12639 Dec 29 15:45 README.md
drwxrwxr-x. 5 postgres postgres  4096 Dec 29 15:45 walminer
[postgres@lyp XLogMiner-master]$ cd walminer
[postgres@lyp walminer]$

执行编译安装

USE_PGXS=1 MAJORVERSION=12 make
#MAJORVERSION支持‘10’,‘11’,‘12’,‘13’

[postgres@lyp walminer]$ USE_PGXS=1 MAJORVERSION=13 make install
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -DPG_VERSION_13 -I. -I./ -I/opt/pgsql13.2/include/server -I/opt/pgsql13.2/include/internal  -D_GNU_SOURCE   -c -o walminer.o walminer.c
......................
.........省略.........
......................
/usr/bin/install -c -m 644 .//walminer.control '/opt/pgsql13.2/share/extension/'
/usr/bin/install -c -m 644 .//walminer--3.0.sql  '/opt/pgsql13.2/share/extension/'
[postgres@lyp walminer]$

注：如make遇报错：make: *** No rule to make target `install’. Stop.

可以安装包：

yum -y install zlib zlib-devel openssl openssl-devel pcre pcre-devel
yum -y install gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-develfreetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-develglib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devele2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel opensslopenssl-devel openldap openldap-devel nss_ldap openldap-clientsopenldap-servers

后退出用户从新进入，再次make

使用方法-SQL解析

从WAL日志产生的数据库中直接执行解析

1. 创建walminer的extension

create extension walminer;

[postgres@lyp walminer]$ psql
psql (13.2)
Type "help" for help.
postgres=# create extension walminer;
CREATE EXTENSION
postgres=#

2. 添加要解析的wal日志文件

-- 添加wal文件：
select walminer_wal_add('/pgsql/data/pg_wal');
-- 注：参数可以为目录或者文件

postgres=# select walminer_wal_add('/pgsql/data/pg_wal');
  walminer_wal_add   
---------------------
 58 file add success
(1 row)

postgres=#

3. Remove wal日志文件

-- 移除wal文件：select walminer_wal_remove('/opt/test/wal');-- 注：参数可以为目录或者文件

postgres=# select walminer_wal_remove ('/pgsql/data/pg_wal/0000000100000001000000AD');  walminer_wal_remove  ----------------------- 1 file remove success(1 row)postgres=#

4. List wal日志文件

-- 列出wal文件：select walminer_wal_list();

postgres=# select walminer_wal_list();               walminer_wal_list               ----------------------------------------------- (/pgsql/data/pg_wal/000000010000000100000088) (/pgsql/data/pg_wal/000000010000000100000089) (/pgsql/data/pg_wal/00000001000000010000008A)...............................省略............................... (/pgsql/data/pg_wal/0000000100000001000000BF) (/pgsql/data/pg_wal/0000000100000001000000C0) (/pgsql/data/pg_wal/0000000100000001000000C1)(57 rows)postgres=#

5. 执行解析

--解析add的全部wal日志select walminer_all();或 select wal2sql();--在add的wal日志中查找对应时间范围的wal记录--可以参照walminer_time.sql回归测试中的使用用例--时间解析模式的解析结果可能比预期的解析结果要多,详情参照[walminer_decode.c]代码中的注释select walminer_by_time(starttime, endtime);或 select wal2sql(starttime, endtime);--在add的wal日志中查找对应lsn范围的wal记录--可以参照walminer_lsn.sql回归测试中的使用用例select walminer_by_lsn(startlsn, endlsn);或 select wal2sql(startlsn, endlsn);--在add的wal日志中查找对应xid的wal记录--可以参照walminer_xid.sql回归测试中的使用用例--前一个walminer版本对xid的支持是范围解析，但是xid的提交是不连续的--会导致各种问题，所以这个版本只支持单xid解析select walminer_by_xid(xid);或 select wal2sql(xid);

postgres=# select walminer_all();NOTICE:  Switch wal to 000000010000000100000088 on time 2022-01-08 12:44:13.887693+08    walminer_all     --------------------- pg_minerwal success(1 row)postgres=#

6. 解析结果查看

select * from walminer_contents;-- 表walminer_contents ( sqlno int, 		--本条sql在其事务内的序号 xid bigint,		--事务ID topxid bigint,		--如果为子事务，这是是其父事务；否则为0 sqlkind int,		--sql类型1->insert;2->update;3->delete(待优化项目) minerd bool,		--解析结果是否完整(缺失checkpoint情况下可能无法解析出正确结果) timestamp timestampTz, --这个SQL所在事务提交的时间 op_text text,		--sql undo_text text,	--undo sql complete bool,		--如果为false，说明有可能这个sql所在的事务是不完整解析的 schema text,		--目标表所在的模式 relation text,		--目标表表名 start_lsn pg_lsn,	--这个记录的开始LSN commit_lsn pg_lsn	--这个事务的提交LSN)

postgres=# select * from walminer_contents;-[ RECORD 1 ]----------------------------------------------sqlno      | 1xid        | 542topxid     | 0sqlkind    | 1minerd     | ttimestamp  | 2022-01-04 22:31:12.579464+08op_text    | INSERT INTO public.test_decoding(id) VALUES(1)undo_text  | DELETE FROM public.test_decoding WHERE id=1complete   | tschema     | publicrelation   | test_decodingstart_lsn  | 1/88085F48commit_lsn | 1/88085FB8postgres=#

注意：walminer_contents是walminer自动生成的unlogged表(之前是临时表，由于临时表在清理上有问题，引起工具使用不便，所以改为unlogged表)，在一次解析开始会首先创建或truncate walminer_contents表。

7. 结束walminer操作

该函数作用为释放内存，结束日志分析，该函数没有参数。

select walminer_stop();

从非WAL产生的数据库中执行WAL日志解析

要求执行解析的PostgreSQL数据库和被解析的为同一版本

于生产数据库

1.创建walminer的extension

create extension walminer;

2.生成数据字典

select walminer_build_dictionary('/opt/proc/store_dictionary');-- 注：参数可以为目录或者文件

于测试数据库

1. 创建5walminer的extension

create extension walminer;

2. load数据字典

select walminer_load_dictionary('/opt/test/store_dictionary');-- 注：参数可以为目录或者文件

3. add wal日志文件

-- 增加wal文件：select walminer_wal_add('/opt/test/wal');-- 注：参数可以为目录或者文件

4. remove wal日志文件

-- 移除wal文件：select walminer_wal_remove('/opt/test/wal');-- 注：参数可以为目录或者文件

5. list wal日志文件

-- 列出wal文件：select walminer_wal_list();-- 注：参数可以为目录或者文件

6. 执行解析

同上

7. 解析结果查看

select * from walminer_contents;

8.结束walminer操作,该函数作用为释放内存，结束日志分析，该函数没有参数。

select walminer_stop();

注意：walminer_contents是walminer自动生成的unlogged表(之前是临时表，由于临时表在清理上有问题，引起工具使用不便，所以改为unlogged表)，在一次解析开始会首先创建或truncate walminer_contents表。

使用限制

本版本解析DML语句。
只能解析与数据字典时间线一致的wal文件
当前walminer无法处理数据字典不一致问题，walminer始终以给定的数据字典为准，对于无法处理的relfilenode，那么会丢弃这一条wal记录(会有一个notice在解析结果中没有体现)
complete属性只有在wallevel大于minimal时有效
xid解析模式不支持子事务
同时只能有一个walminer解析进程，否则会出现解析混乱

使用方法-数据页挽回(坏块修复)

1. 环境搭建

创建extension，创建数据地点，加载wal日志的方法与[SQL解析]中描述的方法一致。

2. 执行数据挽回

select page_collect(relfilenode, reloid, pages)

relfilenode：需要解析的wal日志中的relfilenode

reloid：解析库中存在的表的OID，此命令将会将从wal中找到的page覆盖到reloid制定的表中

pages：是字符串类型，制定想要挽回的目标page。格式为’0,1,2,7’或者’all’。

具体使用方法可以从pc_base.sql测试用例文件中获取。

此功能持续开发中，后续会添加基于基础备份的数据页挽回

使用限制

1.将部分page恢复到其他表后，查询时可能会出现报错的情况。这是因为恢复后的page可能依赖其他page数据，而其依赖的page没有恢复到这个表中。

2.执行此命令后请立即备份，因为此命令对数据的操作不会记录在wal中。

END

文章参考：
http://www.postgres.cn/news/viewone/1/417
https://gitee.com/movead/XLogMiner#%E4%BD%BF%E7%94%A8%E9%99%90%E5%88%B6-1

最后修改时间：2022-01-09 18:41:15

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

文章被以下合辑收录

PostgreSQL分享—李先生（共50篇）

日常的PostgreSQL分享，包含运维、升级、安装部署、疑难问题等

PostgreSQL中WAL日志解析工具——WalMiner

WalMiner背景

WAL日志

什么是WalMiner

WalMiner功能

WalMiner解析原理

INSERT语句解析原理

DELETE语句解析原理

UPDATE语句解析原理

WalMiner使用

编译安装

使用方法-SQL解析

从WAL日志产生的数据库中直接执行解析

1. 创建walminer的extension

2. 添加要解析的wal日志文件

3. Remove wal日志文件

4. List wal日志文件

5. 执行解析

6. 解析结果查看

7. 结束walminer操作

从非WAL产生的数据库中执行WAL日志解析

于生产数据库

1.创建walminer的extension

2.生成数据字典

于测试数据库

1. 创建5walminer的extension

2. load数据字典

3. add wal日志文件

4. remove wal日志文件

5. list wal日志文件

6. 执行解析

7. 解析结果查看

8.结束walminer操作,该函数作用为释放内存，结束日志分析，该函数没有参数。

使用限制

使用方法-数据页挽回(坏块修复)

1. 环境搭建

2. 执行数据挽回

使用限制

文章被以下合辑收录

评论