WalMiner
什么是WalMiner
- WalMiner是从PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持，需要将数据库日志级别配置需要大于minimal
主要功能
-
从waL日志中解析出SQL，包括DML和少量DDL
解析出执行的SQL语句的工具，并能生成对应的undo SQL语句。与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。
-
数据页挽回
当数据库被执行了TRUNCATE等不被wal记录的数据清除操作，或者发生磁盘页损坏，可以使用此功能从wal日志中搜索数据，以期尽量挽回数据。
-
支持delete，drop table，truncate，vacuum full等操作后数据恢复
版本支持
- walminer3.0支持PostgreSQL 10及其以上版本。（此版本放弃对9.x的支持）
安装
-
配置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
案例1（还原delete的数据）
1、创建walminer的extension
postgres=# create extension walminer;
CREATE EXTENSION
2、添加解析的wal日志
--创建表
postgres=# create table test(id int,primary key(id));
CREATE TABLE
--插入数据
postgres=# insert into test values(1);
INSERT 0 1
--删除数据
postgres=# delete from test;
DELETE 1
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000700000045
(1 row)
-- 注：参数可以为目录或者文件
postgres=# select walminer_wal_add('/home/pg12/pg_wal');
walminer_wal_add
---------------------
24 file add success
(1 row)
3、移除wal文件
-- 移除wal文件：
select walminer_wal_remove('/home/pg12/pg_wal');
--官方文档说是可以移除目录或者文件，但是测试只能移除文件，不能是目录
postgres=#select walminer_wal_remove('/home/pg12/pg_wal');
错误: Argument can be file only, an not be a directory
4、列出wal日志文件
--查看那些在分析的列表里
postgres=# select walminer_wal_list();
walminer_wal_list
---------------------------------------------------------------------
(/home/pg12/pg_wal/000000010000000700000043)
(/home/pg12/pg_wal/000000010000000700000044)
(/home/pg12/pg_wal/000000010000000700000045)
、、、、
--如果没有添加，这一步会报错
postgres=# select walminer_wal_list();
错误: wal list has not been loaded or has been removed.
5、解析wal日志，还原删除的数据
--解析add的全部wal日志
select walminer_all();
或 select wal2sql();
postgres=# select walminer_all();
注意: Switch wal to 000000010000000700000043 on time 2021-10-09 14:01:12.821196+08
注意: Switch wal to 000000010000000700000044 on time 2021-10-09 14:01:12.887438+08
注意: Switch wal to 000000010000000700000045 on time 2021-10-09 14:01:13.025056+08
walminer_all
---------------------
pg_minerwal success
(1 row)
--解析完成后会把所有的结果记录到walminer_contents里面，可以使用walminer_by_time获取指定时间段
postgres=# select walminer_by_time('2021-10-09 12:00:00', '2021-10-09 15:00:00');
注意: Add wal from current pg_wal directory, do not suggest use this way in produce
注意: Switch wal to 000000010000000700000045 on time 2021-10-09 14:02:07.654465+08
walminer_by_time
---------------------
pg_minerwal success
(1 row)
--op_text记录了操作语句，undo_text是还原语句
postgres=# select * from walminer_contents;
sqlno | xid | topxid | sqlkind | minerd | timestamp | op_text | undo_text | complete | schema | relation
| start_lsn | commit_lsn
-------+-----------+--------+---------+--------+-------------------------------+---------------------------------------+---------------------------------------+----------+--------+---------
-+------------+------------
1 | 140477556 | 0 | 1 | t | 2021-10-09 13:57:56.836588+08 | INSERT INTO public.test(id) VALUES(1) | DELETE FROM public.test WHERE id=1 | t | public | test
| 7/453F22D0 | 7/453F2418
1 | 140477557 | 0 | 3 | t | 2021-10-09 13:58:21.183022+08 | DELETE FROM public.test WHERE id=1 | INSERT INTO public.test(id) VALUES(1) | t | public | test
| 7/453F2500 | 7/453F25B8
(2 rows)
可以将undo_text执行，用于还原数据，由于是根据wal日志还原，所以vacuum 操作不影响，也可以直接还原
该中方法相比于直接修改xid的又是在于在发生vacuum之后或者发生误操作后又向表中插入了新的有效数据后，修改xid就不太好使
案例2（还原vacuum full的数据）
1、vacumm full
--在test表中插入三条数据
postgres=# insert into test values(4),(5),(6);
INSERT 0 3
postgres=# select *from test;
id
----
4
5
6
(3 rows)
postgres=# select pg_relation_filepath('test');
pg_relation_filepath
----------------------
base/13593/31828459
(1 row)
postgres=# select current_timestamp;
current_timestamp
-------------------------------
2021-10-09 15:04:50.848161+08
(1 row)
postgres=#
postgres=# delete from test;
DELETE 3
--vacuum full
postgres=# vacuum full test;
VACUUM
postgres=# select current_timestamp;
current_timestamp
-------------------------------
2021-10-09 15:05:19.661514+08
(1 row)
2、替身解析
- 如果一个表被drop或者被truncate，以及vacuum full等操作，导致新产生的数据字典不包含旧的数据库中所包含的relfilenode，那么使用新的数据字典无法解析出旧的wal日志中包含的某些内容。在知道旧表的表结构的前提下，可以使用替身解析模式。替身解析只能适用于从WAL日志产生的数据库中直接执行解析，不适用从非wal产生的数据库中执行wal日志解析
--test表已经被删除了，需要找到删除前test表的relfilenode
--新建test的替身表
postgres=# create table test_a(id int,primary key(id));
CREATE TABLE
--在执行解析之前，先执行如下语句，即可开启系统表解析功能
select wal2sql_with_catalog();
select wal2sql();
--在walminer_contents里找到修改的记录找到对应的
select * from walminer_contents;
--获取到relfilenode:31828459
postgres=# select walminer_table_avatar('test_a',31828459);
walminer_table_avatar
--------------------------------
MAP[test_a:31828432]->31828459
(1 row)
postgres=# select wal2sql();
注意: Add wal from current pg_wal directory, do not suggest use this way in produce
注意: Switch wal to 000000010000000700000043 on time 2021-10-09 15:06:04.286037+08
注意: Switch wal to 000000010000000700000044 on time 2021-10-09 15:06:04.351453+08
注意: Switch wal to 000000010000000700000045 on time 2021-10-09 15:06:04.475823+08
wal2sql
---------------------
pg_minerwal success
(1 row)
postgres=# select * from walminer_contents;
sqlno | xid | topxid | sqlkind | minerd | timestamp | op_text | undo_text | c
omplete | schema | relation | start_lsn | commit_lsn
-------+-----------+--------+---------+--------+-------------------------------+-----------------------------------------------------+----------------------------------------------------+--
--------+--------+----------+------------+------------
1 | 140311525 | 0 | 1 | t | 2021-10-08 15:33:28.6956+08 | INSERT INTO public.test1(id ,info) VALUES(1 ,'234') | DELETE FROM public.test1 WHERE id=1 AND info='234' | t
| public | test1 | 7/43CCC9E8 | 7/43CCF430
1 | 140477839 | 0 | 1 | t | 2021-10-09 15:04:44.485147+08 | INSERT INTO public.test_a(id) VALUES(4) | DELETE FROM public.test_a WHERE id=4 | t
| public | test_a | 7/455C7CE0 | 7/455C7F28
2 | 140477839 | 0 | 1 | t | 2021-10-09 15:04:44.485147+08 | INSERT INTO public.test_a(id) VALUES(5) | DELETE FROM public.test_a WHERE id=5 | t
| public | test_a | 7/455C7DF8 | 7/455C7F28
3 | 140477839 | 0 | 1 | t | 2021-10-09 15:04:44.485147+08 | INSERT INTO public.test_a(id) VALUES(6) | DELETE FROM public.test_a WHERE id=6 | t
| public | test_a | 7/455C7E78 | 7/455C7F28
1 | 140477844 | 0 | 3 | t | 2021-10-09 15:05:12.29125+08 | DELETE FROM public.test_a WHERE id=4 | INSERT INTO public.test_a(id) VALUES(4) | t
| public | test_a | 7/455D0978 | 7/455D0A68
2 | 140477844 | 0 | 3 | t | 2021-10-09 15:05:12.29125+08 | DELETE FROM public.test_a WHERE id=5 | INSERT INTO public.test_a(id) VALUES(5) | t
| public | test_a | 7/455D09B8 | 7/455D0A68
3 | 140477844 | 0 | 3 | t | 2021-10-09 15:05:12.29125+08 | DELETE FROM public.test_a WHERE id=6 | INSERT INTO public.test_a(id) VALUES(6) | t
| public | test_a | 7/455D09F8 | 7/455D0A68
(7 rows)
--停止walminer
select walminer_stop();
能获取到delete 并且执行过vacuum full的数据
前面都是在源库执行的，也可以将wal日志和数据字典放到任意一个跟生产库配置相同的测试库解析
从非wal产生的数据库中执行wal日志解析
这种适用于不动生产环境，但是将wal日志在测试环境还原
生产库生成数据字典
--1、创建walminer扩展
create extension walminer;
--2、生成数据字典
select walminer_build_dictionary('/opt/proc/store_dictionary');
-- 注：参数可以为目录或者文件
会生成一个：dictionary.d的文件
测试数据库中加载数据字典
--1、创建walminer扩展
create extension walminer;
--2、load数据字典
select walminer_load_dictionary('/opt/test/store_dictionary');
--3、add wal日志文件
select walminer_wal_add('/opt/test/wal');
--4、移除wal文件：
select walminer_wal_remove('/opt/test/wal');
--5、list wal日志文件
-- 列出wal文件：注：参数可以为目录或者文件
select walminer_wal_list();
--6、 执行解析
select wal2sql();
--7、解析结果查看
select * from walminer_contents;
--8、结束walminer操作,该函数作用为释放内存，结束日志分析，该函数没有参数。
select walminer_stop();
注意：walminer_contents是walminer自动生成的unlogged表(之前是临时表，由于临时表在清理上有问题，引起工具使用不便，所以改为unlogged表)，在一次解析开始会首先创建或truncate walminer_contents表。
注意事项
-
本版本解析DML语句。DDL语句解析功能正在不断开发。
-
只能解析与数据字典时间线一致的wal文件
-
当前walminer无法处理数据字典不一致问题，walminer始终以给定的数据字典为准，
对于无法处理的relfilenode，那么会丢弃这一条wal记录(会有一个notice在解析结果中没有体现)
-
complete属性只有在wallevel大于minimal时有效
-
xid解析模式不支持子事务
-
同时只能有一个walminer解析进程，否则会出现解析混乱