PostgreSQL12通过WalMiner恢复数据

1、WalMiner简介

官方文档：https://gitee.com/movead/XLogMiner

WalMiner是从PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持。目前主要有如下功能：
• 从waL日志中解析出SQL，包括DML和少量DDL
解析出执行的SQL语句的工具，并能生成对应的undo SQL语句。与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。
• 数据页挽回
当数据库被执行了TRUNCATE等不被wal记录的数据清除操作，或者发生磁盘页损坏，可以使用此功能从wal日志中搜索数据，以期尽量挽回数据

walminer3.0更新内容
• 代码结构变化
• 增加数据页挽回功能(坏块修复)
• 增加对开发库PG14的支持

---

2、安装WalMiner

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

export PATH=/app/telepg/pg12.5/bin:$PATH
export PGHOME=/app/telepg/pg12.5

2. 进入walminer代码路径

unzip XLogMiner-master.zip
cd ./XLogMiner-master/walminer

3. 执行编译安装

USE_PGXS=1 MAJORVERSION=12 make
#MAJORVERSION支持‘10’,‘11’,‘12’,‘13’
USE_PGXS=1 MAJORVERSION=12 make install

---

3、WalMiner通过WAL日志进行SQL解析

3.1、创建walminer的extension

postgres=# select * from pg_available_extensions where name='walminer';
   name   | default_version | installed_version |      comment       
----------+-----------------+-------------------+--------------------
 walminer | 3.0             |                   | analyse wal to SQL
(1 row)
postgres=# create extension walminer;
CREATE EXTENSION
postgres=# 
postgres=# select * from pg_extension;
  oid  | extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+----------+----------+--------------+----------------+------------+-----------+--------------
 13579 | plpgsql  |       10 |           11 | f              | 1.0        |           | 
 24673 | pg_trgm  |       10 |         2200 | t              | 1.4        |           | 
 24896 | walminer |       10 |         2200 | t              | 3.0        |           | 
(3 rows)

3.2、在源库进行误操作

postgres=# select * from test1;
 id | name 
----+------
  1 | asd
  2 | qwe
  3 | zxc
(3 rows)

postgres=# update test1 set name='1111' where id=1;   ---update数据并切换归档
UPDATE 1
postgres=# 
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 20/570003D8
(1 row)

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 20/58000078
(1 row)

postgres=# delete from test1 where id>2; ---delete数据并切换归档
DELETE 1
postgres=# 
postgres=# select pg_switch_wal();      
 pg_switch_wal 
---------------
 20/590000E0
(1 row)

3.3、把wal日志加到walminer

把在误操作时间范围内的wal日志都添加

postgres=# select walminer_wal_add('/app/telepg/pg_data/pg_wal');

postgres=# select walminer_wal_list();
                   walminer_wal_list                   
-------------------------------------------------------
 (/app/telepg/pg_data/pg_wal/000000020000002000000056)
 (/app/telepg/pg_data/pg_wal/000000020000002000000057)
 (/app/telepg/pg_data/pg_wal/000000020000002000000058)
 (/app/telepg/pg_data/pg_wal/000000020000002000000059)
(4 rows)

3.4、解析sql

1、先确认误删表的oid

postgres=# select oid,relname from pg_class where relname='test1';
  oid  | relname 
-------+---------
 24761 | test1
(1 row)

2、解析
• 填入误操作的大致开始时间和结束时间
• 每运行一次以下语句，walminer_contents表就会重新插入一次
• wal日志也需要重新配置一次

--'true'和'false'代表是否为精确解析模式，reloid为目标表的oid(注意不是relfilenode)
--以下俩种显示的结果一致
select walminer_by_time('2022-02-10 15:40:21', '2022-02-10 15:55:21','true',24761); 
或 
select wal2sql('2022-02-10 15:40:21', '2022-02-10 15:55:21','true',24761);

3.5、查看解析结果

• 精确模式的结果
会把误操作的sql和回滚sql都展示出来，会显示具体到值
通过得到的回滚sql就可以进行恢复数据

postgres=# select * from walminer_contents;                                          
-[ RECORD 1 ]-------------------------------------------------------------
sqlno      | 1
xid        | 870
topxid     | 0
sqlkind    | 2
minerd     | t
timestamp  | 2022-02-10 15:40:54.966033+08
op_text    | UPDATE public.test1 SET name='1111' WHERE id=1 AND name='asd'  ---误操作语句
undo_text  | UPDATE public.test1 SET name='asd' WHERE id=1 AND name='1111'  ---回滚语句
complete   | t
schema     | public
relation   | test1
start_lsn  | 20/57000308
commit_lsn | 20/57000388
-[ RECORD 2 ]-------------------------------------------------------------
sqlno      | 1
xid        | 871
topxid     | 0
sqlkind    | 3
minerd     | t
timestamp  | 2022-02-10 15:41:40.97732+08
op_text    | DELETE FROM public.test1 WHERE id=3 AND name='zxc'    ---误操作语句
undo_text  | INSERT INTO public.test1(id ,name) VALUES(3 ,'zxc')   ---回滚语句
complete   | t
schema     | public
relation   | test1
start_lsn  | 20/59000060
commit_lsn | 20/590000C8

• 非精确模式的结果
不会显示具体到值

postgres=# select * from walminer_contents;
-[ RECORD 1 ]-----------------------------------------------
sqlno      | 1
xid        | 870
topxid     | 0
sqlkind    | 2
minerd     | f
timestamp  | 2022-02-10 15:40:54.966033+08
op_text    | UPDATE public.test1 (WALMINER_DATA_MISSED)
undo_text  | UPDATE public.test1 (WALMINER_DATA_MISSED)
complete   | t
schema     | public
relation   | test1
start_lsn  | 20/57000308
commit_lsn | 20/57000388
-[ RECORD 2 ]-----------------------------------------------
sqlno      | 1
xid        | 871
topxid     | 0
sqlkind    | 3
minerd     | f
timestamp  | 2022-02-10 15:41:40.97732+08
op_text    | DELETE FROM public.test1 (WALMINER_DATA_MISSED)
undo_text  | INSERT INTO public.test1) VALUES)
complete   | t
schema     | public
relation   | test1
start_lsn  | 20/59000060
commit_lsn | 20/590000C8

3.6、结束walminer操作

该函数作用为释放内存，结束日志分析，该函数没有参数。

postgres=# select walminer_stop();
-[ RECORD 1 ]-+-----------------
walminer_stop | walminer stoped!

运行了之后，walminer_contents表还是会有记录