PG在线WAL日志解析成回滚SQL工具-WalMiner
什么是WalMiner
WalMiner是从PostgreSQL的WAL(write ahead logs)日志中解析出执行的SQL语句的工具，并能生成出对应的undo SQL语句。
配置要求
需要将数据库日志级别配置需要大于minimal
PG版本支持
PG9.5.0之前的版本用XLogMiner
PG9.5.0~PG10.X版本使用WalMiner_10_X
PG11之后的版本使用WalMiner_11_X
下面的实验，
在PG11.7+WalMiner_11_X的环境下进行
操作系统：
[postgres@node100 ~]$ cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core
编译安装
编译一：PG源码编译 如果你从编译pg数据库开始
将walminer目录放置到编译通过的PG工程的"../contrib/"目录下
[root@node100 walminer]# pwd
/usr/local/src/postgresql-11.17/contrib
进入walminer目录
[root@node100 contrib]# cd walminer/
[root@node100 walminer]# pwd
/usr/local/src/postgresql-11.17/contrib/walminer
执行命令
[root@node100 walminer]# make && make install
如下：
unzip walminer-WalMiner_11_0_1.zip
cp -r walminer /usr/local/postgresql-11.7/contrib
cd /usr/local/postgresql-11.7/contrib/walminer
make && make install
编译二：依据PG安装编译 如果你使用yum或者pg安装包安装了pg
配置pg的bin路径至环境变量
export PATH=/h2/pg_install/bin:$PATH
进入walminer代码路径
执行编译安装
USE_PGXS=1 MAJORVERSION=12 make
#MAJORVERSION支持‘10’,‘11’,‘12’,‘13’
USE_PGXS=1 MAJORVERSION=12
make install
使用方法
创建walminer的extension
postgres=# create extension walminer;
CREATE EXTENSION
查看当前wal文件名
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
0000000700000000000000F1
制造场景
postgres=# create table tmp_test(a int,info text);
CREATE TABLE
postgres=# insert into tmp_test values(1,'yejf');
INSERT 0 1
postgres=# insert into tmp_test values(2,'yejf');
INSERT 0 1
postgres=# delete from tmp_test where a=1;
DELETE 1
Add wal日志文件
postgres=# select walminer_wal_add('/data/pg11data/pg_wal/0000000700000000000000F1');
NOTICE: Get data dictionary from current database.
walminer_wal_add
--------------------
1 file add success
(1 row)
#参数可以为目录或者文件
List wal日志文件
postgres=# select walminer_wal_list();
walminer_wal_list
--------------------------------------------------
(/data/pg11data/pg_wal/0000000700000000000000F1)
(1 row)
执行解析
使用语法
select walminer_start('START_TIMSTAMP','STOP_TIMESTAMP','START_XID','STOP_XID');
---如果分析全部日志：
select walminer_start('null','null',0,0);
postgres=# select walminer_start('null','null',0,0);
NOTICE: Change Wal Segment To:/usr/local/pg1117/data/pg_wal/000000010000000000000001
NOTICE: wal record after time 2022-11-30 17:14:54+08 or 0/164d628 will be analyse completely
walminer_start
---------------------
walminer sucessful!
(1 row)
---将系统表修改结果输出到$PGDATA/walminer/temp下：
select walminer_start('null','null',0,0,true);
START_TIMESTAMP：指定输出结果中最早的记录条目，即从该时间开始输出分析数据；若该参数值为空，则以分析日志列表中最早数据开始输出；若该参数值指定时间没有包含在所分析xlog列表中，即通过分析发现全部早于该参数指定时间，则返回空值。
STOP_TIMESTAMP：指定数据结果中最晚的记录条目，即输出结果如果大于该时间，则停止分析，不需要继续输出；如果该参数值为空，则从START_TIMESTAMP开始的所有日志都进行分析和输出。
START_XID：作用与START_TIMESTAMP相同，指定开始的XID值；
STOP_XID：作用与STOP_TIMESTAMP相同，指定结束的XID值
现解析全部日志
postgres=# select walminer_start('null','null',0,0);
NOTICE: Change Wal Segment To:/data/pg11data/pg_wal/0000000700000000000000F1
walminer_start
---------------------
walminer sucessful!
(1 row)
查看解析的结果
postgres=# select * from walminer_contents ;
xid | virtualxid | timestamptz | record_database | record_user | record_tablespace | record_
schema | op_type | op_text |
op_undo
-----+------------+-------------------------------+-----------------+-------------+-------------------+--------
-------+---------+-----------------------------------------------------------------+---------------------------
--------------------------------------------------------
571 | 1 | 2023-10-24 10:48:09.987605+08 | postgres | postgres | pg_default | public
| INSERT | INSERT INTO "public"."tmp_test"("a", "info") VALUES(1, 'yejf'); | DELETE FROM "public"."tmp_
test" WHERE "a"=1 AND "info"='yejf' AND ctid = '(0,1)';
572 | 1 | 2023-10-24 10:48:12.801305+08 | postgres | postgres | pg_default | public
| INSERT | INSERT INTO "public"."tmp_test"("a", "info") VALUES(2, 'yejf'); | DELETE FROM "public"."tmp_
test" WHERE "a"=2 AND "info"='yejf' AND ctid = '(0,2)';
573 | 1 | 2023-10-24 10:48:21.654495+08 | postgres | postgres | pg_default | public
| DELETE | DELETE FROM "public"."tmp_test" WHERE "a"=1 AND "info"='yejf'; | INSERT INTO "public"."tmp_
test"("a", "info") VALUES(1, 'yejf');
(3 rows)
可以看到刚才的操作记录都解析出来了，而且生成对应的undo语句。walminer_contents是walminer自动生成的临时表，因此当session断开再重新进入或其他session中解析数据不可见。这么做主要是基于安全考虑。如果希望保留解析结果，可利用create xxx as select * from walminer_contents;写入普通表中。
数据恢复
可以将op_undo执行，用于还原数据。
postgres=# \pset footer
Default footer is off.
postgres=# \pset t
Tuples only is on.
postgres=# \o undo.sql
postgres=# select op_undo||';' from walminer_contents;
postgres=# \q
[postgres@lyp ~]$ more undo.sql
DELETE FROM public.test WHERE id=201 AND name='test201';
DELETE FROM public.test WHERE id=202 AND name='test202';
DELETE FROM public.test WHERE id=205 AND name='test205';
DELETE FROM public.test WHERE id=206 AND name='test206';
DELETE FROM public.test WHERE id=207 AND name='test207';
DELETE FROM public.test WHERE id=208 AND name='test208';
DELETE FROM public.test WHERE id=209 AND name='test209';
DELETE FROM public.test WHERE id=210 AND name='test210';
INSERT INTO public.test(id ,name) VALUES(100 ,'test100');
INSERT INTO public.test(id ,name) VALUES(201 ,'test201');
UPDATE public.test SET id=202 WHERE id=102 AND name='test202';
UPDATE public.test SET id=66 WHERE id=88 AND name='test66';
UPDATE public.test SET id=99, name='test99' WHERE id=50 AND name='test50';
结束walminer操作
select walminer_stop();
回滚语句插入到原表中：
[postgres@node100 ~]$ psql
psql (11.17)
Type "help" for help.
postgres=# \i undo.sql
DELETE 0
DELETE 0
DELETE 1
DELETE 1
DELETE 1
DELETE 1
DELETE 1
DELETE 1
INSERT 0 1
INSERT 0 1
UPDATE 1
UPDATE 1
UPDATE 2
postgres=#
使用限制
本版本只解析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日志,这会导致解析过程中的效率低下和内存占用过高
小结
DDL语句可以从系统表修改结果文件中查看判断，查并没有具体时间，可根据wal文件日志的时间来估计
解析其它PG数据库的wal日志时，需要从对应的PG数据库创建数据字典，并在本地导入，才可解析对应的wal日志
select walminer_build_dictionary('/data/pg11data/walminer'); --创建数据字典
select walminer_load_dictionary('/data/pg11data/walminer'); --导入数据字典
