Postgresql WAL日志解析挖掘（walminer 3.0）
1.walminer介绍
WalMiner是PostgreSQL的WAL(write ahead logs)日志解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持。
目前主要有如下功能：
-
从waL日志中解析出SQL，包括DML和少量DDL。
解析出执行的SQL语句的工具，并能生成对应的undo SQL语句。与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。 -
数据页挽回。
当数据库被执行了TRUNCATE等不被wal记录的数据清除操作，或者发生磁盘页损坏，可以使用此功能从wal日志中搜索数据，以期尽量挽回数据。
Walminer 3.0版本是以插件的模式进行使用。
walminer 4.0版本摒弃插件模式改为bin模式，现已脱离对目标数据库的编译依赖和安装依赖，一个walminer工具可以解析PG10~PG15的WAL日志。💻walminer 4.0安装使用看这里👀
2.下载walminer
https://gitee.com/movead/XLogMiner
3.安装walminer
## 解压walminer_3.0
[postgres@pg tmp]$ unzip XLogMiner-walminer_3.0_stable.zip
## 将解压后的 walminer 目录放置到编译通过的PG工程的"../contrib/"目录下
[postgres@pg tmp]$ cd XLogMiner-walminer_3.0_stable/
[postgres@pg XLogMiner-walminer_3.0_stable]$ cp -r walminer/ /tmp/soft/postgresql-15.4/contrib/
## 编译安装walminer_3.0
[postgres@pg XLogMiner-walminer_3.0_stable]$ cd /tmp/soft/postgresql-15.4/contrib/walminer/
[postgres@pg walminer]$ make && make install
## 数据库中创建 walminer 插件
postgres=# create extension walminer;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
walminer | 3.0 | public | analyse wal to SQL
(2 rows)
4.WAL日志解析挖掘测试
## 创建测试表
postgres=# create table t1(id int,note text);
CREATE TABLE
postgres=# insert into t1 values (1,'aaa');
INSERT 0 1
postgres=# insert into t1 values (2,'bbb');
INSERT 0 1
postgres=# insert into t1 values (3,'ccc');
INSERT 0 1
postgres=# select * from t1;
id | note
----+------
1 | aaa
2 | bbb
3 | ccc
(3 rows)
## 模拟误操作
postgres=# update t1 set note='bbbbbbbbbbb' where id=2;
UPDATE 1
postgres=# delete from t1 where id=1;
DELETE 1
postgres=# select * from t1;
id | note
----+-------------
3 | ccc
2 | bbbbbbbbbbb
(2 rows)
## 切换wal日志
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/70580F0
(1 row)
## 根据误操作时间选择合适的wal日志
[postgres@pg ~]$ cd /pgsql15.4/data/pg_wal
[postgres@pg pg_wal]$ ls -lrt
total 32768
-rw-------. 1 postgres postgres 16777216 Nov 21 16:31 000000010000000000000008
drwx------. 2 postgres postgres 43 Nov 21 16:31 archive_status
-rw-------. 1 postgres postgres 16777216 Nov 21 16:31 000000010000000000000009
注：如果pg_wal下的wal日志已经发生归档，或者重用，就需要找到误操作时间范围的归档日志文件。
## 添加解析的wal日志
postgres=# select walminer_wal_add('/pgsql15.4/data/pg_wal');
walminer_wal_add
--------------------
2 file add success
(1 row)
postgres=# select walminer_wal_add('/pgsql15.4/pg_arch/000000010000000000000008');
walminer_wal_add
--------------------
1 file add success
(1 row)
注：参数可以为一个目录或文件。
## 列出等待解析的wal日志
postgres=# select walminer_wal_list();
walminer_wal_list
---------------------------------------------------
(/pgsql15.4/pg_arch/000000010000000000000008)
(/pgsql15.4/data/pg_wal/000000010000000000000009)
(/pgsql15.4/data/pg_wal/00000001000000000000000A)
(3 rows)
## 移除不需要解析的wal日志
postgres=# select walminer_wal_remove('/pgsql15.4/pg_arch/000000010000000000000008');
## 开始解析
postgres=# select walminer_all();
NOTICE: Switch wal to 000000010000000000000008 on time 2023-11-21 16:53:54.534608+08
NOTICE: Switch wal to 000000010000000000000009 on time 2023-11-21 16:53:54.535068+08
walminer_all
---------------------
pg_minerwal success
(1 row)
--指定时间范围解析
select walminer_by_time('2023-11-21 16:20:00','2023-11-21 16:31:00');
'2023-11-21 16:20:00'：开始时间
'2023-11-21 16:31:00'：结束时间
## 查看解析结果
postgres=# \x
Expanded display is on.
postgres=# select * from walminer_contents;
-[ RECORD 1 ]-----------------------------------------------------------------
sqlno | 1
xid | 740
topxid | 0
sqlkind | 2
minerd | t
timestamp | 2023-11-21 16:29:47.905102+08
op_text | UPDATE public.t1 SET note='bbbbbbbbbbb' WHERE id=2 AND note='bbb'
undo_text | UPDATE public.t1 SET note='bbb' WHERE id=2 AND note='bbbbbbbbbbb'
complete | t
schema | public
relation | t1
start_lsn | 0/8000060
commit_lsn | 0/8000180
-[ RECORD 2 ]-----------------------------------------------------------------
sqlno | 1
xid | 741
topxid | 0
sqlkind | 3
minerd | t
timestamp | 2023-11-21 16:30:52.479069+08
op_text | DELETE FROM public.t1 WHERE id=1 AND note='aaa'
undo_text | INSERT INTO public.t1(id ,note) VALUES(1 ,'aaa')
complete | t
schema | public
relation | t1
start_lsn | 0/80001B8
commit_lsn | 0/8000218
## 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
)
## 结束wal日志分析
postgres=# select walminer_stop();
walminer_stop
------------------
walminer stoped!
(1 row)
## 通过undo_text，对误操作进行恢复
postgres=# UPDATE public.t1 SET note='bbb' WHERE id=2 AND note='bbbbbbbbbbb';
UPDATE 1
postgres=# INSERT INTO public.t1(id ,note) VALUES(1 ,'aaa');
INSERT 0 1
postgres=# select * from t1;
id | note
----+------
3 | ccc
2 | bbb
1 | aaa
(3 rows)