1、前言
对于从事DBA相关的朋友而言，最大的悲哀莫过于没有好好的做好数据备份，最后导致数据丢失，正所谓，出事了，被迫或有意提桶跑路，甚至有如股市里的“关灯吃面”，自己慢慢承受带来的恶果。
这里就大致介绍一下自己国人开发的walminer，用于PG数据库中的历史数据恢复。本篇先暂时介绍完全开源的3.0版。以后有机会再介绍4.0收费版。它们各有特色。
什么是WalMiner
WalMiner是从PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持。目前主要有如下功能：
从waL日志中解析出SQL，包括DML和少量DDL
解析出执行的SQL语句的工具，并能生成对应的undo SQL语句。与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。
数据页挽回
当数据库被执行了TRUNCATE等不被wal记录的数据清除操作，或者发生磁盘页损坏，可以使用此功能从wal日志中搜索数据，以期尽量挽回数据。
这里借一张简图(简图来自参考文章里的原图，一并致谢)，可以概括它的基本工作原理：
2、编译安装
基本上有两种方法，一般使用第2种方法比较常见。
取代码：它现在换名字了，叫XLogMiner，由国内相关人员开发并发布。源码：https://gitee.com/movead/XLogMiner
walminer3.0支持PostgreSQL 10,11,12,13,14。（此版本放弃对9.x的支持）
代码位置：(https://gitee.com/movead/XLogMiner/releases)可以找到source code下载https://gitee.com/movead/XLogMiner/repository/archive/walminer_3.0_stable.zip
或者clone下来，取master branch，当然这个时候你看到的可能是2.0版本，不影响。
编译一：PG源码编译
如果你从编译pg数据库开始
将walminer目录放置到编译通过的PG工程的"../contrib/"目录下
进入walminer目录
执行命令
make && make install复制
编译二：依据PG安装编译
如果你使用yum或者pg安装包安装了pg
配置pg的bin路径至环境变量
export PATH=/usr/pgsql-14/bin:$PATH复制进入walminer代码路径
执行编译安装
USE_PGXS=1 MAJORVERSION=14 make
#MAJORVERSION支持‘10’,‘11’,‘12’,‘13’,'14'
USE_PGXS=1 MAJORVERSION=14 make install复制
注：原文档中，可能上边的变量：MAJORVERSION只有10到13，没关系，我们也可以把它设为14，甚至15，跟实际的PG版本相匹配就行。
3、简单使用实例
先记启用插件，要让walminer能用，还得启用归档日志，防止wal log被覆盖重用。因为它的基础就是建立在wal log还在的基础上进行逆向分析。
如，有一张表，我插入了几条数据，中间误删除了一条记录：id=2。现在想把它恢复回去。
mydb=# create extension walminer;
CREATE EXTENSION
mydb=#
mydb=# create table t(id int primary key, col2 varchar(32));
CREATE TABLE
mydb=# insert into t values(1, 'wang');
INSERT 0 1
mydb=# insert into t values(2, 'wang2');
INSERT 0 1
mydb=# insert into t values(3, 'wang3');
INSERT 0 1
mydb=# insert into t values(4, 't4'), (5, 't5');
INSERT 0 2
mydb=# delete from t where id = 2;
DELETE 1
mydb=# checkpoint;
CHECKPOINT复制
我们这里也不想通过pg_waldump之类的，挖出xid相关信息进行PITR恢复了，那个不是挺费时间的吗。看看walminer怎么做。
在上边建完插件之后，添加wal log 目录或列表，然后就可以查询了。如：
mydb=# select walminer_wal_list();
walminer_wal_list
----------------------------------------------------------
(/var/lib/pgsql/14/data/pg_wal/000000010000000000000001)
(/var/lib/pgsql/14/data/pg_wal/000000010000000000000002)
(/var/lib/pgsql/14/data/pg_wal/000000010000000000000003)
(/var/lib/pgsql/14/data/pg_wal/000000010000000000000004)
(4 rows)
mydb=# select wal2sql();
NOTICE: Switch wal to 000000010000000000000001 on time 2023-03-28 05:10:47.622928+08
NOTICE: Switch wal to 000000010000000000000002 on time 2023-03-28 05:10:47.790919+08
NOTICE: Switch wal to 000000010000000000000003 on time 2023-03-28 05:10:48.052498+08
NOTICE: Switch wal to 000000010000000000000004 on time 2023-03-28 05:10:48.391953+08
wal2sql
---------------------
pg_minerwal success
(1 row)
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+----------
public | t | table | postgres
public | walminer_contents | table | postgres
(2 rows)
mydb=# select * from walminer_contents;
sqlno | xid | topxid | sqlkind | minerd | timestamp | op_text |
undo_text | complete | schema | relation | start_lsn | commit_lsn
-------+-----+--------+---------+--------+-------------------------------+---------------------------------------------------+----------
-----------------------------------------+----------+--------+----------+-----------+------------
1 | 789 | 0 | 1 | t | 2023-03-28 05:01:48.924149+08 | INSERT INTO public.t(id ,col2) VALUES(1 ,'wang') | DELETE FR
OM public.t WHERE id=1 | t | public | t | 0/42D4EF8 | 0/42D5000
1 | 790 | 0 | 1 | t | 2023-03-28 05:01:53.97429+08 | INSERT INTO public.t(id ,col2) VALUES(2 ,'wang2') | DELETE FR
OM public.t WHERE id=2 | t | public | t | 0/42D5000 | 0/42D50B0
1 | 791 | 0 | 1 | t | 2023-03-28 05:02:01.33367+08 | INSERT INTO public.t(id ,col2) VALUES(3 ,'wang3') | DELETE FR
OM public.t WHERE id=3 | t | public | t | 0/42D50E8 | 0/42D5198
1 | 792 | 0 | 1 | t | 2023-03-28 05:02:18.997766+08 | INSERT INTO public.t(id ,col2) VALUES(4 ,'t4') | DELETE FR
OM public.t WHERE id=4 | t | public | t | 0/42D5198 | 0/42D52C0
2 | 792 | 0 | 1 | t | 2023-03-28 05:02:18.997766+08 | INSERT INTO public.t(id ,col2) VALUES(5 ,'t5') | DELETE FR
OM public.t WHERE id=5 | t | public | t | 0/42D5218 | 0/42D52C0
1 | 793 | 0 | 3 | t | 2023-03-28 05:02:29.346528+08 | DELETE FROM public.t WHERE id=2 | INSERT IN
TO public.t(id ,col2) VALUES(2 ,'wang2') | t | public | t | 0/42D52F8 | 0/42D5358
(6 rows)复制
最后我们从表walminer_contents里可以清楚看到id=2时的undo SQL。将其执行一下，就能得到恢复。
当然这上边是把所有的wal log全部解析。你也可以只针对某张表进行过滤解析。先得到表的oid，然后再过滤。
-- 可以捋一下，walminer_contents是一个unlogged表，便于清除，性能也不错。
mydb=# \d+ walminer_contents
Unlogged table "public.walminer_contents"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
sqlno | integer | | | | plain | | |
xid | bigint | | | | plain | | |
topxid | bigint | | | | plain | | |
sqlkind | integer | | | | plain | | |
minerd | boolean | | | | plain | | |
timestamp | timestamp with time zone | | | | plain | | |
op_text | text | | | | extended | | |
undo_text | text | | | | extended | | |
complete | boolean | | | | plain | | |
schema | text | | | | extended | | |
relation | text | | | | extended | | |
start_lsn | pg_lsn | | | | plain | | |
commit_lsn | pg_lsn | | | | plain | | |
Access method: heap
-- 得以目标表的oid: 24605
mydb=# select oid, pg_relation_filepath(oid) from pg_class where relname='t';
oid | pg_relation_filepath
-------+----------------------
24605 | base/16384/24605
(1 row)复制
再重新生成content:
mydb=# select wal2sql('2023-03-27 00:00:00', '2023-03-28 08:08:08', true, 24605);
NOTICE: Add wal from current pg_wal directory, do not suggest use this way in produce
NOTICE: Switch wal to 000000010000000000000004 on time 2023-03-28 05:20:52.612745+08
wal2sql
---------------------
pg_minerwal success
(1 row)
mydb=# select sqlno, xid, op_text, undo_text, timestamp, commit_lsn from walminer_contents;
sqlno | xid | op_text | undo_text | timesta
mp | commit_lsn
-------+-----+---------------------------------------------------+---------------------------------------------------+------------------
-------------+------------
1 | 789 | INSERT INTO public.t(id ,col2) VALUES(1 ,'wang') | DELETE FROM public.t WHERE id=1 | 2023-03-28 05:01:
48.924149+08 | 0/42D5000
1 | 790 | INSERT INTO public.t(id ,col2) VALUES(2 ,'wang2') | DELETE FROM public.t WHERE id=2 | 2023-03-28 05:01:
53.97429+08 | 0/42D50B0
1 | 791 | INSERT INTO public.t(id ,col2) VALUES(3 ,'wang3') | DELETE FROM public.t WHERE id=3 | 2023-03-28 05:02:
01.33367+08 | 0/42D5198
1 | 792 | INSERT INTO public.t(id ,col2) VALUES(4 ,'t4') | DELETE FROM public.t WHERE id=4 | 2023-03-28 05:02:
18.997766+08 | 0/42D52C0
2 | 792 | INSERT INTO public.t(id ,col2) VALUES(5 ,'t5') | DELETE FROM public.t WHERE id=5 | 2023-03-28 05:02:
18.997766+08 | 0/42D52C0
1 | 793 | DELETE FROM public.t WHERE id=2 | INSERT INTO public.t(id ,col2) VALUES(2 ,'wang2') | 2023-03-28 05:02:
29.346528+08 | 0/42D5358
(6 rows)复制
这样把范围一下子缩小了很多， 同样能找出结果。上边的例子主要是用于单表解析，用的是oid。不是filenodeid。
每次wal2sql的时候，系统先是直接truncate那张content表。所以不用担心记录会重复叠加。找到对应的undo_text (SQL)，执行一下，就可以立即恢复回去。
-- 简单恢复一下数据mydb=# INSERT INTO public.t(id ,col2) VALUES(2 ,'wang2');
INSERT 0 1
mydb=# select * from t;
id | col2
----+-------
1 | wang
3 | wang3
4 | t4
5 | t5
2 | wang2
(5 rows)复制
当然，walminer还有很多其它骚操作。你可以直接上它的源码网站上仔细跟读并实验验证。
4、提一提pg_resetwal
我们知道PostgreSQL的多版本原理是旧数据并不删除：
1. 对于删除数据的操作，只是把行上的xmax改成当前的事务id
2. 对于更新操作，只是把原先行上xmax改成当前的事务id，并插入一个新行，而新行上的xmin置为当前的事务id
所以，假如数据还没有被vacuum进程清理掉的话，那么就可以找回来。
说到这里，不得不提一下pg_resetwal，在目标表没有vacuum（如果vacuum的话，数据可能被清理）的情况下，它也可以通过重置系统的xid值，恢复到那个删除操作之前的状态。但是这要的代价还是比较大的。
网上有不少相关文章介绍这种暴力骚操作。不赘述。
参考：
https://gitee.com/movead/XLogMiner
如何抢救被删的数据 (https://mp.weixin.qq.com/s/lWgZskzX_75-K81MFZh2LQ)
---END---
复制
