postgresql中wal日志解析工具——walminer -m6米乐安卓版下载

postgresql中wal日志解析工具——walminer

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

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日志中搜索数据，以期尽量挽回数据。

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

编译安装

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

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

   export pghome=/opt/pgsql13.2
   export path=$pghome/bin:$path
   

2. 进入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]$ 
   

3. 执行编译安装

   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表。

使用限制

1. 本版本解析dml语句。

2. 只能解析与数据字典时间线一致的wal文件

3. 当前walminer无法处理数据字典不一致问题，walminer始终以给定的数据字典为准，对于无法处理的relfilenode，那么会丢弃这一条wal记录(会有一个notice在解析结果中没有体现)

4. complete属性只有在wallevel大于minimal时有效

5. xid解析模式不支持子事务

6. 同时只能有一个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#使用限制-1

​