PostgreSQL中WAL日志解析工具——WalMiner
?WalMiner是從PostgreSQL的WAL(write ahead logs)日志中解析出執行的SQL語句的工具，并能生成對應的undo SQL語句。與傳統的logical decode插件相比，walminer不要求logical日志級別且解析方式較為靈活。
WalMiner背景
WAL日志
在PostgreSQL中，WAL日志記錄了數據庫重要數據文件的所有變化，你對數據的操作都會被記錄到其中，WAL日志是保證數據完整性的一種標準方法，WAL的中心概念是數據文件（存儲著表和索引）的修改必須在這些動作被日志記錄之后才被寫入，即在描述這些改變的日志記錄被刷到持久存儲以后。
由于WAL日志是二進制格式的文件，如果遇到wal日志增長的情況，并且我們甚至還不知道是什么樣的數據變更引起的大量的WAL日志增長，我們就會想辦法知道WAL日志到底記錄了什么信息，如何讀取PostgreSQL的WAL日志內容？但是目前并沒有一款完善的wal日志解析工具，即使wal日志在那里我們也沒有簡便的方法來讀出wal的內容（pg_waldump是wal的解析工具，但是它只會告訴你向哪個表發生了insert，但是沒有insert的具體數據）。
so，WalMiner 工具就此問世…
什么是WalMiner
Walminer是從PostgreSQL的WAL(write ahead logs)日志的解析工具，它可以從wal日志中解析出用戶執行的DML語句，以及用戶執行DDL語句對系統表產生的DML語句。它的前身是xlogminer，但是xlogminer有較大的限制，比如wal日志級別需要是logical、需要將表改為FULL模式。Walminer則不需要這些限制，它可以解析普通的archive或replica級別以上的wal日志。
WalMiner 工具下載地址為：http://www.sunline.cc/download/329654
WalMiner 工具代碼開源地址為：https://gitee.com/movead/XLogMiner
WalMiner功能
WalMiner是從PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，從而提供PG的數據恢復支持。目前主要有如下功能：
-
從waL日志中解析出SQL，包括DML和少量DDL
解析出執行的SQL語句的工具，并能生成對應的undo SQL語句。與傳統的logical decode插件相比，walminer不要求logical日志級別且解析方式較為靈活。
-
數據頁挽回
當數據庫被執行了TRUNCATE等不被wal記錄的數據清除操作，或者發生磁盤頁損壞，可以使用此功能從wal日志中搜索數據，以期盡量挽回數據。
WalMiner解析原理
INSERT語句解析原理
從wal日志的一條insert類型的record中可以獲取到relfilenode，結合數據字典就可以得到這個insert目標表的“表名”、“字段類型”、“字段名”。insert的實際數據在record中以“變更數據的方式”或者“FPW”的形式存在。獲取這些數據后，結合表的字段類型，就可以拼接出這條insert語句。
DELETE語句解析原理
delete語句的解析中，獲取目標表的表結構的過程是跟insert解析是一致的，但是在delete語句產生的record中一般是不存在delete的具體數據的（FPW除外），那如何才能獲取這些數據呢？
在一個檢查點后，第一次修改page時會進行PFW，因此雖然這個record中不存在我們想要的數據，但是在這個record之前的某個record中一定有這個page的FPW。因此我們可以在讀到一個FPW后記錄下這個FPW的數據，以供以后的解析使用。注意：每次提供的wal日志可能是有限的，因此每次解析很可能會存在一些無法找到其FPW的record，因此會有無法解析出的行。
獲取到了這行delete的具體數據，那么就可以拼接這條delete語句了。
UPDATE語句解析原理
Update語句的解析原理就是一個delete一個insert。將舊的數據行delete，insert新的數據行，這里就不再贅述了。
WalMiner使用
Walminer可以在生產庫（待解析wal日志的生成庫）執行，也可以將wal日志和數據字典放到任意一個跟生產庫配置相同的測試庫解析。Walminer的具體使用情況在開源代碼的readme中有詳細的介紹，這里只介紹一下在測試庫使用的情況。
編譯安裝
PG安裝編譯 ——使用yum或者pg安裝包安裝pg
-
配置pg的bin路徑至環境變量
export PGHOME=/opt/pgsql13.2 export PATH=$PGHOME/bin:$PATH -
進入walminer代碼路徑
[postgres@lyp ~]$ ll XLogMiner-master.zip -rw-r--r--. 1 ~ postgres 247496 Jan 8 11:29 XLogMiner-master.zip [postgres@lyp ~]$ unzip XLogMiner-master.zip Archive: XLogMiner-master.zip f8e322361555cbe8f790c9dbdb448e9453f85950 creating: XLogMiner-master/ extracting: XLogMiner-master/.gitignore ...................... .........省略......... ...................... inflating: XLogMiner-master/walminer/wm_utils.c inflating: XLogMiner-master/walminer/wm_utils.h [postgres@lyp contrib]$ cd XLogMiner-master/ [postgres@lyp XLogMiner-master]$ ll total 24 -rw-rw-r--. 1 postgres postgres 1071 Dec 29 15:45 LICENSE -rw-rw-r--. 1 postgres postgres 0 Dec 29 15:45 README.EN.MD -rw-rw-r--. 1 postgres postgres 12639 Dec 29 15:45 README.md drwxrwxr-x. 5 postgres postgres 4096 Dec 29 15:45 walminer [postgres@lyp XLogMiner-master]$ cd walminer [postgres@lyp walminer]$ -
執行編譯安裝
USE_PGXS=1 MAJORVERSION=12 make #MAJORVERSION支持‘10’,‘11’,‘12’,‘13’ [postgres@lyp walminer]$ USE_PGXS=1 MAJORVERSION=13 make install gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -DPG_VERSION_13 -I. -I./ -I/opt/pgsql13.2/include/server -I/opt/pgsql13.2/include/internal -D_GNU_SOURCE -c -o walminer.o walminer.c ...................... .........省略......... ...................... /usr/bin/install -c -m 644 .//walminer.control '/opt/pgsql13.2/share/extension/' /usr/bin/install -c -m 644 .//walminer--3.0.sql '/opt/pgsql13.2/share/extension/' [postgres@lyp walminer]$注：如make遇報錯：make: *** No rule to make target `install’. Stop.
可以安裝包：
yum -y install zlib zlib-devel openssl openssl-devel pcre pcre-devel yum -y install gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-develfreetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-develglib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devele2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel opensslopenssl-devel openldap openldap-devel nss_ldap openldap-clientsopenldap-servers后退出用戶從新進入，再次make
使用方法-SQL解析
從WAL日志產生的數據庫中直接執行解析
1. 創建walminer的extension
create extension walminer;
[postgres@lyp walminer]$ psql
psql (13.2)
Type "help" for help.
postgres=# create extension walminer;
CREATE EXTENSION
postgres=#
2. 添加要解析的wal日志文件
-- 添加wal文件：
select walminer_wal_add('/pgsql/data/pg_wal');
-- 注：參數可以為目錄或者文件
postgres=# select walminer_wal_add('/pgsql/data/pg_wal');
walminer_wal_add
---------------------
58 file add success
(1 row)
postgres=#
3. Remove wal日志文件
-- 移除wal文件：select walminer_wal_remove('/opt/test/wal');-- 注：參數可以為目錄或者文件
postgres=# select walminer_wal_remove ('/pgsql/data/pg_wal/0000000100000001000000AD'); walminer_wal_remove ----------------------- 1 file remove success(1 row)postgres=#
4. List wal日志文件
-- 列出wal文件：select walminer_wal_list();
postgres=# select walminer_wal_list(); walminer_wal_list ----------------------------------------------- (/pgsql/data/pg_wal/000000010000000100000088) (/pgsql/data/pg_wal/000000010000000100000089) (/pgsql/data/pg_wal/00000001000000010000008A)...............................省略............................... (/pgsql/data/pg_wal/0000000100000001000000BF) (/pgsql/data/pg_wal/0000000100000001000000C0) (/pgsql/data/pg_wal/0000000100000001000000C1)(57 rows)postgres=#
5. 執行解析
--解析add的全部wal日志select walminer_all();或 select wal2sql();--在add的wal日志中查找對應時間范圍的wal記錄--可以參照walminer_time.sql回歸測試中的使用用例--時間解析模式的解析結果可能比預期的解析結果要多,詳情參照[walminer_decode.c]代碼中的注釋select walminer_by_time(starttime, endtime);或 select wal2sql(starttime, endtime);--在add的wal日志中查找對應lsn范圍的wal記錄--可以參照walminer_lsn.sql回歸測試中的使用用例select walminer_by_lsn(startlsn, endlsn);或 select wal2sql(startlsn, endlsn);--在add的wal日志中查找對應xid的wal記錄--可以參照walminer_xid.sql回歸測試中的使用用例--前一個walminer版本對xid的支持是范圍解析，但是xid的提交是不連續的--會導致各種問題，所以這個版本只支持單xid解析select walminer_by_xid(xid);或 select wal2sql(xid);
postgres=# select walminer_all();NOTICE: Switch wal to 000000010000000100000088 on time 2022-01-08 12:44:13.887693+08 walminer_all --------------------- pg_minerwal success(1 row)postgres=#
6. 解析結果查看
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)
postgres=# select * from walminer_contents;-[ RECORD 1 ]----------------------------------------------sqlno | 1xid | 542topxid | 0sqlkind | 1minerd | ttimestamp | 2022-01-04 22:31:12.579464+08op_text | INSERT INTO public.test_decoding(id) VALUES(1)undo_text | DELETE FROM public.test_decoding WHERE id=1complete | tschema | publicrelation | test_decodingstart_lsn | 1/88085F48commit_lsn | 1/88085FB8postgres=#
注意：walminer_contents是walminer自動生成的unlogged表(之前是臨時表，由于臨時表在清理上有問題，引起工具使用不便，所以改為unlogged表)，在一次解析開始會首先創建或truncate walminer_contents表。
7. 結束walminer操作
該函數作用為釋放內存，結束日志分析，該函數沒有參數。
select walminer_stop();
從非WAL產生的數據庫中執行WAL日志解析
要求執行解析的PostgreSQL數據庫和被解析的為同一版本
于生產數據庫
1.創建walminer的extension
create extension walminer;
2.生成數據字典
select walminer_build_dictionary('/opt/proc/store_dictionary');-- 注：參數可以為目錄或者文件
于測試數據庫
1. 創建5walminer的extension
create extension walminer;
2. load數據字典
select walminer_load_dictionary('/opt/test/store_dictionary');-- 注：參數可以為目錄或者文件
3. add wal日志文件
-- 增加wal文件：select walminer_wal_add('/opt/test/wal');-- 注：參數可以為目錄或者文件
4. remove wal日志文件
-- 移除wal文件：select walminer_wal_remove('/opt/test/wal');-- 注：參數可以為目錄或者文件
5. list wal日志文件
-- 列出wal文件：select walminer_wal_list();-- 注：參數可以為目錄或者文件
6. 執行解析
同上
7. 解析結果查看
select * from walminer_contents;
8.結束walminer操作,該函數作用為釋放內存，結束日志分析，該函數沒有參數。
select walminer_stop();
注意：walminer_contents是walminer自動生成的unlogged表(之前是臨時表，由于臨時表在清理上有問題，引起工具使用不便，所以改為unlogged表)，在一次解析開始會首先創建或truncate walminer_contents表。
使用限制
-
本版本解析DML語句。
-
只能解析與數據字典時間線一致的wal文件
-
當前walminer無法處理數據字典不一致問題，walminer始終以給定的數據字典為準，對于無法處理的relfilenode，那么會丟棄這一條wal記錄(會有一個notice在解析結果中沒有體現)
-
complete屬性只有在wallevel大于minimal時有效
-
xid解析模式不支持子事務
-
同時只能有一個walminer解析進程，否則會出現解析混亂
使用方法-數據頁挽回(壞塊修復)
1. 環境搭建
創建extension，創建數據地點，加載wal日志的方法與[SQL解析]中描述的方法一致。
2. 執行數據挽回
select page_collect(relfilenode, reloid, pages)
relfilenode：需要解析的wal日志中的relfilenode
reloid：解析庫中存在的表的OID，此命令將會將從wal中找到的page覆蓋到reloid制定的表中
pages：是字符串類型，制定想要挽回的目標page。格式為’0,1,2,7’或者’all’。
具體使用方法可以從pc_base.sql測試用例文件中獲取。
此功能持續開發中，后續會添加基于基礎備份的數據頁挽回
使用限制
1.將部分page恢復到其他表后，查詢時可能會出現報錯的情況。這是因為恢復后的page可能依賴其他page數據，而其依賴的page沒有恢復到這個表中。
2.執行此命令后請立即備份，因為此命令對數據的操作不會記錄在wal中。
END
文章參考：
http://www.postgres.cn/news/viewone/1/417
https://gitee.com/movead/XLogMiner#%E4%BD%BF%E7%94%A8%E9%99%90%E5%88%B6-1
?
