walminer
简介
walminer 可以解析 wal 日志，尽可能多的获取有用信息,进一步生成undo语句，实现恢复删除或丢失的数据。 功能如下：
从wal日志中提取DML和少部分DDL，并生成对应的回滚语句；
磁盘部分损坏或执行了TRUNCATE等清除操作时，可以使用walminer尝试从wal中提取数据，尽最大可能找回数据。不保证一定能恢复成功。
前提要求
表结构需要尽可能完整，例如字段名称，字段类型等
wal日志文件越多越好
注意事项
不建议在生产环境直接执行本节的所有操作。
建议将当前所有文件复制到一个独立的机器进行恢复操作，避免对当前环境造成更大的数据损坏。
在将结果恢复到生产环境时，建议先备份生产环境当前的所有数据。
在一次解析开始时会首先创建或truncate walminer_contents表，当得到想要的数据时建议先导出备份它们再执行第二次解析。
安装/启用
udb-tx-v22.4.22 及以后的版本,已启用 walminer, 直接使用即可。
udb-tx-v22.4.22 及以前的版本,按以下步骤安装启用。
安装
联系技术支持获取并安装。
启用
walminer.so 放到安装目录的lib/ 下
walminer.control和walminer–3.0.sql 放到安装目录的share/extension/ 下
chown -R udb.udb 安装目录/lib/walminer.so
chown -R udb.udb 安装目录/share/extension/walminer.*
验证安装
[udb@0a2593d70d5b udb]$ ud_sql
ud_sql (22.4)
Type "help" for help.
unvdb=# create extension walminer;
-- 创建扩展插件
CREATE EXTENSION
unvdb=#
使用说明
基础操作
create extension walminer; -- 创建扩展
select walminer_wal_add('/data/udb/ud_wal'); --添加要解析的wal日志目录或单个文件名
select walminer_wal_remove('/opt/test/wal'); --移除wal日志目录或单个文件名
select walminer_wal_list(); --列出wal文件
解析操作
#普通解析
select walminer_all(); --解析已添加的全部wal日志
select walminer_by_time(starttime, endtime); --在add的wal日志中查找对应时间范围的wal记录
select walminer_by_lsn(startlsn, endlsn); --在add的wal日志中查找对应lsn范围的wal记录
select walminer_by_xid(xid); --在add的wal日志中查找对应xid的wal记录
#精确解析
select walminer_by_time(starttime, endtime,'true'); --精确查找对应时间范围的wal记录
select walminer_by_lsn(startlsn, endlsn,'true'); --精确查找对应lsn范围的wal记录
select walminer_by_xid(xid,'true'); --精确查找对应xid的wal记录
#普通解析会严格匹配给定的范围，但可能没找到之前的checkpoint点，所以会出现记录不全的情况；
#精确解析会自动查找给定范围之前的checkpoint点，更完美的解析出结果。当普通解析结果不全时可以尝试精确解析。
#当本机没有数据结构时，可以加载其它机器生成的的数据结构
select walminer_build_dictionary('/tmp/store_dictionary'); --根据当前数据库结构生成数据字典
select walminer_load_dictionary('/tmp/store_dictionary'); --加载数据字典
#reloid 参数可以查找指定表，要用表的oid
select walminer_by_time(starttime, endtime,'false',reloid); --非精确模式查找对应时间范围的reloid表oid的wal记录
select walminer_by_lsn(startlsn, endlsn,'true',reloid); --在add的wal日志中查找对应lsn范围的wal记录
select walminer_by_xid(xid,'true',reloid);--在add的wal日志中查找对应xid的wal记录
#当表结构不完整时创建临时表并使用临时表映射解析。
create table tmp_avatar(i int); --创建临时表
select walminer_table_avatar(tmp_avatar, missed_relfilenode); --映射到临时表。此时的解析结果将按临时表格式输出。
select wal2sql(); --执行解析，wal文件越多，需要的时间也越多。
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
)
select walminer_stop(); --结束分析操作，并释放内存。
实践演示
delete 操作恢复
模拟删除操作
waltest=# select * from tb1;
id | name
----+------
1 | a
2 | b
(2 rows)
waltest=# delete from tb1;
DELETE 2
waltest=# select * from tb1;
id | name
----+------
(0 rows)
配置walminer
waltest=# create extension walminer;--创建扩展
CREATE EXTENSION
waltest=# select walminer_wal_add('/data/udb/ud_wal');--添加wal目录
walminer_wal_add
---------------------
24 file add success
(1 row)
waltest=# select walminer_wal_list();--列出wal文件
walminer_wal_list
---------------------------------------------
(/data/udb/ud_wal/00000001000000090000001C)
(/data/udb/ud_wal/00000001000000090000001D)
(/data/udb/ud_wal/00000001000000090000001E)
(/data/udb/ud_wal/00000001000000090000001F)
(/data/udb/ud_wal/000000010000000900000020)
(/data/udb/ud_wal/000000010000000900000021)
(/data/udb/ud_wal/000000010000000900000022)
(/data/udb/ud_wal/000000010000000900000023)
(/data/udb/ud_wal/000000010000000900000024)
(/data/udb/ud_wal/000000010000000900000025)
(/data/udb/ud_wal/000000010000000900000026)
(/data/udb/ud_wal/000000010000000900000027)
(/data/udb/ud_wal/000000010000000900000028)
(/data/udb/ud_wal/000000010000000900000029)
(/data/udb/ud_wal/00000001000000090000002A)
(/data/udb/ud_wal/00000001000000090000002B)
(/data/udb/ud_wal/00000001000000090000002C)
(/data/udb/ud_wal/00000001000000090000002D)
(/data/udb/ud_wal/00000001000000090000002E)
(/data/udb/ud_wal/00000001000000090000002F)
(/data/udb/ud_wal/000000010000000900000030)
(/data/udb/ud_wal/000000010000000900000031)
(/data/udb/ud_wal/000000010000000900000032)
(/data/udb/ud_wal/000000010000000900000033)
(24 rows)
waltest=# select walminer_all(); --提取wal日志
NOTICE: Switch wal to 00000001000000090000001C on time 2023-03-27 13:58:30.446893+08
NOTICE: Switch wal to 00000001000000090000001D on time 2023-03-27 13:58:30.447915+08
NOTICE: Switch wal to 00000001000000090000001E on time 2023-03-27 13:58:30.44841+08
walminer_all
---------------------
ud_minerwal success
(1 row)
waltest=# select * from walminer_contents; --查看提取结果
sqlno | xid | topxid | sqlkind | minerd | timestamp | op_text | undo_text | complete | schema | relat
ion | start_lsn | commit_lsn
-------+------+--------+---------+--------+-------------------------------+-----------------------------------+-------------------------------------------------+----------+--------+------
----+------------+------------
1 | 1528 | 0 | 3 | t | 2023-03-27 13:56:45.925757+08 | DELETE FROM public.tb1 WHERE id=1 | INSERT INTO public.tb1(id ,name) VALUES(1 ,'a') | t | public | tb1
| 9/1C0058F8 | 9/1C005A10
2 | 1528 | 0 | 3 | t | 2023-03-27 13:56:45.925757+08 | DELETE FROM public.tb1 WHERE id=2 | INSERT INTO public.tb1(id ,name) VALUES(2 ,'b') | t | public | tb1
| 9/1C0059A0 | 9/1C005A10
(2 rows)
--执行undo_text修复数据
waltest=# INSERT INTO public.tb1(id ,name) VALUES(1 ,'a');
INSERT 0 1
waltest=# INSERT INTO public.tb1(id ,name) VALUES(2 ,'b');
INSERT 0 1
waltest=# select * from tb1;--验证结果
id | name
----+------
1 | a
2 | b
(2 rows)
select walminer_stop();--结束分析并释放内存。
DDL操作恢复
注意：ddl删除的列，只能恢复列信息，不能恢复列数据。
模拟删除列操作
unvdb=# \c waltest;
You are now connected to database "waltest" as user "unvdb".
waltest=# select * from tb1;
id | name | age
----+------+-----
1 | a | 1
2 | b | 2
(2 rows)
waltest=# ALTER TABLE "public"."tb1" DROP COLUMN "age";
ALTER TABLE
waltest=# select * from tb1;
id | name
----+------
1 | a
2 | b
(2 rows)
恢复配置
waltest=# CREATE EXTENSION walminer;
CREATE EXTENSION
waltest=# select walminer_wal_add('/data/udb/ud_wal');
walminer_wal_add
---------------------
11 file add success
(1 row)
waltest=# select walminer_wal_list();
walminer_wal_list
---------------------------------------------
(/data/udb/ud_wal/000000010000000900000029)
(/data/udb/ud_wal/00000001000000090000002A)
(/data/udb/ud_wal/00000001000000090000002B)
(/data/udb/ud_wal/00000001000000090000002C)
(/data/udb/ud_wal/00000001000000090000002D)
(/data/udb/ud_wal/00000001000000090000002E)
(/data/udb/ud_wal/00000001000000090000002F)
(/data/udb/ud_wal/000000010000000900000030)
(/data/udb/ud_wal/000000010000000900000031)
(/data/udb/ud_wal/000000010000000900000032)
(/data/udb/ud_wal/000000010000000900000033)
(11 rows)
waltest=# select wal2sql_with_ddl(); --开启ddl解析
wal2sql_with_ddl
------------------
t
(1 row)
waltest=# select walminer_by_time('2023-03-27 15:00', '2023-03-27 15:15','true'); --必须使用精确解析
NOTICE: Switch wal to 000000010000000900000029 on time 2023-03-27 15:12:59.812091+08
NOTICE: Con not find relfilenode 65692 in dictionary, ignored related records
NOTICE: Con not find relfilenode 65693 in dictionary, ignored related records
walminer_by_time
---------------------
ud_minerwal success
(1 row)
waltest=# select * from walminer_contents;--检查解析结果
sqlno | xid | topxid | sqlkind | minerd | timestamp | op_text | undo_text | complete | schema |
relation | start_lsn | commit_lsn
-------+------+--------+---------+--------+-------------------------------+-------------------------------------------------+-----------------------------------+----------+------------+--
------------+------------+------------
1 | 1578 | 0 | 4 | t | 2023-03-27 15:01:03.11249+08 | TRUNCATE TABLE public.tb1 | | t | pg_catalog | p
g_class | 9/291A7EB8 | 9/291AA7E0
1 | 1583 | 0 | 1 | t | 2023-03-27 15:09:24.706606+08 | INSERT INTO public.tb1(id ,name) VALUES(1 ,'a') | DELETE FROM public.tb1 WHERE id=1 | t | public | t
b1 | 9/291DA7F8 | 9/291DA910
1 | 1584 | 0 | 1 | t | 2023-03-27 15:09:27.201411+08 | INSERT INTO public.tb1(id ,name) VALUES(2 ,'b') | DELETE FROM public.tb1 WHERE id=2 | t | public | t
b1 | 9/291DA948 | 9/291DA9F8
1 | 1585 | 0 | 2 | t | 2023-03-27 15:09:31.79967+08 | UPDATE public.tb1 SET WHERE id=2 | UPDATE public.tb1 SET WHERE id=2 | t | public | t
b1 | 9/291DA9F8 | 9/291DAA78
1 | 1586 | 0 | 4 | t | 2023-03-27 15:10:15.078618+08 | ALTER TABLE public.tb1 DROP COLUMN age | | t | pg_catalog | p
g_attribute | 9/291DCB98 | 9/291E0BF0
(5 rows)
修复数据
op_text列显示执行过 ALTER TABLE public.tb1 DROP COLUMN age，使用alter add 把它添加进来即可，但此列的数据无法恢复。