環境

系統平臺： Linux x86-64 Red Hat Enterprise Linux 7

版本： 4.3.4.7

文件用途

本文主要用於介紹如何通過walminer實現對資料庫wal日誌的挖掘，內容包含wal的安裝及使用。

詳細資訊

一、walminer介紹

1、簡單介紹

walminer是Highgo開源的一款軟體，WalMiner是從PostgreSQL的WAL(write ahead logs)日誌中解析出執行的SQL語句的工具，並能生成出對應的undo SQL語句。

2、版本控制

支援範圍：支援所有版本HGDB

PG9.5.0之前的版本沒有做過測試

PG9.5.0~PG10.X版本使用WalMiner_10_X

PG11之後的版本使用WalMiner_11_X

10.0.1版本

1.WalMiner支援解析minimal級別以上的任何wal日誌級別。

2.無需將表設定為IDENTITY FULL模式。

3.增加對系統表修改的wal記錄的解析。

4.他庫解析時relfilenode不匹配的bug修改

11.0.1版本

1.WalMiner支援解析minimal級別以上的任何wal日誌級別。

2.無需將表設定為IDENTITY FULL模式。

3.增加對系統表修改的wal記錄的解析。

4.他庫解析時relfilenode不匹配的bug修改

5.pg11支援性修改

3、下載地址：
[WalMiner]( )

4、使用前提

資料據庫日誌級別配置需要大於minimal ，若想做最完整的日誌挖掘，建議設定為logical。

二、walminer的安裝

1、確定資料庫版本

highgo=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (NeoKylin 4.8.5-16), 64-bit
(1 row)
pg10.5,使用walminer_10_x版本

2、資料庫執行引數設定

①建立歸檔目錄
[root@mode2 pg_wal]# mkdir /hgdbbak/archive/ -p
②修改資料庫引數

必須設定如下三個引數，據庫日誌級別配置需要大於minimal

wal_level分為 minimal, replica, or logical，若想做最完整的日誌挖掘，建議設定為logical。

highgo=# alter system set wal_level = 'replica';
highgo=# alter system set archive_mode = on;
highgo=# alter system set archive_directory = '/hgdbbak/archive/';

③重啟資料庫生效

[root@mode2 pg_wal]# pg_ctl restart

3、安裝walminer

①將下載的walniner資料夾上傳至資料庫程式碼的contrib目錄下，執行make;make install
[root@mode2 walminer]# pwd
/opt/HighGo4.3.4.7-see/share/postgresql/contrib/walminer
[root@mode2 walminer]# ls
datadictionary.c logminer.c Makefile pg_logminer.c walminer--1.0.sql xlogminer_contents.c xlogreader_logmine
datadictionary.h logminer.h organizsql.c pg_logminer.h walminer.control xlogminer_contents.h
[root@mode2 walminer]# make
Makefile:16: ../../src/Makefile.global: No such file or directory
Makefile:17: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target `/contrib/contrib-global.mk'. Stop.

②根據編譯時的報錯資訊修改檔案資訊
[root@mode2 walminer]# vi Makefile

top_builddir = /opt/HighGo4.3.4.7-see/lib/postgresql/pgxs ----修改此目錄
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk

[root@mode2 walminer]# find / -name Makefile.global
/opt/HighGo4.3.4.7-see/lib/postgresql/pgxs/src/Makefile.global

[root@mode2 walminer]# make
Makefile:17: /opt/HighGo4.3.4.7-see/lib/postgresql/pgxs//contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target `/opt/HighGo4.3.4.7-see/lib/postgresql/pgxs//contrib/contrib-global.mk'. Stop.

③配置編譯引數
[root@mode2 walminer]# make USE_PGXS=1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attributeity -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/HighGo4.3.4.7-see/include/postg/opt/HighGo4.3.4.7-see/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_logminer.o pg_logm
pg_logminer.c: In function ‘logminer_elog’:
pg_logminer.c:151:2: warning: function might be possible candidate for ‘gnu_printf’ format attribute [-Wsuggest-attrib
vfprintf(tempFileOpen, _(fmt), args);
^
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attributeity -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/HighGo4.3.4.7-see/include/postg/opt/HighGo4.3.4.7-see/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o logminer.o logminer.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attributeity -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/HighGo4.3.4.7-see/include/postg/opt/HighGo4.3.4.7-see/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o organizsql.o organizs
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attributeity -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/HighGo4.3.4.7-see/include/postg/opt/HighGo4.3.4.7-see/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o xlogreader_logminer.ominer.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attributeity -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/HighGo4.3.4.7-see/include/postg/opt/HighGo4.3.4.7-see/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o datadictionary.o data
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attributeity -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/HighGo4.3.4.7-see/include/postg/opt/HighGo4.3.4.7-see/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o xlogminer_contents.o nts.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attributeity -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o walminer.so pg_logminer.o logminer.ologreader_logminer.o datadictionary.o xlogminer_contents.o -L/opt/HighGo4.3.4.7-see/lib    -Wl,--as-needed -Wl,-rpath,.4.7-see/lib',--enable-new-dtags
④安裝walminer

[root@mode2 walminer]# make USE_PGXS=1 install
/usr/bin/mkdir -p '/opt/HighGo4.3.4.7-see/lib/postgresql'
/usr/bin/mkdir -p '/opt/HighGo4.3.4.7-see/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/HighGo4.3.4.7-see/share/postgresql/extension'
/usr/bin/install -c -m 755 walminer.so '/opt/HighGo4.3.4.7-see/lib/postgresql/walminer.so'
/usr/bin/install -c -m 644 .//walminer.control '/opt/HighGo4.3.4.7-see/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//walminer--1.0.sql '/opt/HighGo4.3.4.7-see/share/postgresql/extension/'

⑤資料庫內建立walminer擴充套件

[root@mode2 walminer]# psql -U sysdba -d highgo
NOTICE:
-------------------------------------------
Login User: sysdba
Login time: 2020-05-18 10:28:15.806217+08
Login Address: [local]
Last Login Status: SUCCESS
Login Failures: 0
Valied Until: infinity
-------------------------------------------

psql (4.3.4.7)
Type "help" for help.

highgo=# create extension walminer;
CREATE EXTENSION

三、使用限制

1.只能解析DML語句，不處理DDL語句。

未來改動：DDL語句的解析已放入todolist，可能會逐步支援各種DDL語句。

2.執行了刪除表、truncate表、更改表的表空間、更改表欄位的型別、vacuum full，這樣的DDL語句後，發生DDL語句之前的此表相關的DML語句不會再被解析。
應對措施：建議在執行表結構變更之前，先儲存一份資料字典，用來保證可以解析歷史wal日誌。
未來改動：現在已經考慮在walminer內增加儲存資料字典的功能。

3.解析結果依賴於資料字典。（舉例：建立表t1,所有者為user1，但是中間將所有者改為user2。那解析結果中，所有t1相關操作所有者都將標示為user2）。
應對措施：建議在執行表結構變更之前，先儲存一份資料字典，用來保證可以解析歷史wal日誌。
未來改動：現在已經考慮在walminer內增加儲存資料字典的功能。

4.解析結果中undo欄位的ctid屬性是發生變更“當時”的值，如果因為vacuum等操作導致ctid發生變更，這個值將不準確。對於有可能存在重複行的資料，我們需要通過這個值確定undo對應的tuple條數，不代表可以直接執行該undo語句。

5.執行了表欄位drop的DDL語句後,發生DDL語句之前的這個欄位相關的值都會被解析為encode（'AD976BC56F'，hex）的形式，另外自定義型別也會解析為這種形式。

6.只能解析與資料字典時間線一致的wal檔案。

7.WalMiner是個人出品，暫時未進行全面測試。

8.不建議使用walminer解析大宗copy語句（在同一個事務中插入大量資料行）產生的wal日誌，這會導致解析過程中的效率低下和記憶體佔用過高。

9、引數解釋

--解析語法：

select walminer_start(’START_TIMSTAMP’,’STOP_TIMESTAMP’,’START_XID’,’STOP_XID’)

--如果分析全部日誌：

select walminer_start('null','null',0,0);

--將系統表修改結果輸出到$PGDATA/walminer/temp下：

select walminer_start('null','null',0,0,true);

START_TIMESTAMP：指定輸出結果中最早的記錄條目，即從該時間開始輸出分析資料；若該引數值為空，則以分析日誌列表中最早資料開始輸出；若該引數值指定時間沒有包含在所分析xlog列表中，即通過分析發現全部早於該引數指定時間，則返回空值。

STOP_TIMESTAMP：指定資料結果中最晚的記錄條目，即輸出結果如果大於該時間，則停止分析，不需要繼續輸出；如果該引數值為空，則從START_TIMESTAMP開始的所有日誌都進行分析和輸出。

START_XID：作用與START_TIMESTAMP相同，指定開始的XID值；

STOP_XID：作用與STOP_TIMESTAMP相同，指定結束的XID值

注意：兩組引數只能有一組為有效輸入，否則報錯。

四、場景一 WAL日誌生成庫直接解析

1、新建表及測試資料

highgo=# create table t2(i int,j int, k varchar);

CREATE TABLE
highgo=#
highgo=# insert into t2 values(1,1,'qqqqqq');
INSERT 0 1
highgo=# insert into t2 values(2,2,'wwwwww');
INSERT 0 1
highgo=# insert into t2 values(3,3,'eeeee');
INSERT 0 1
highgo=# update t2 set k = '1111qqqqq' where i = 1;
UPDATE 1
highgo=# delete from t2 where j = 2;
DELETE 1
highgo=# insert into t2 values(4,4,'44444rrrrrr');

INSERT 0 1

2、檢視當前正在使用的wal日誌，切換wal日誌

highgo=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000002
(1 row)

highgo=# select pg_switch_wal();

pg_switch_wal

---------------

0/2034158
(1 row)

3、載入wal日誌到解析器，本次新增pg_wal目錄內的單個wal日誌，注意是相對路徑。可新增單個wal日誌，也可以載入pg_wal目錄或wal日誌歸檔目錄內的所有日誌

highgo=# select walminer_wal_add('pg_wal/000000010000000000000002');
NOTICE: Get data dictionary from current database.
walminer_wal_add
--------------------
1 file add success
(1 row)

舉例1：載入wal歸檔目錄內的單個wal日誌，使用絕對路徑

highgo=# select walminer_wal_add('/hgdbbak/archive/000000010000000000000003');
NOTICE: Get data dictionary from current database.
walminer_wal_add
--------------------
1 file add success
(1 row)

舉例2：新增整個wal歸檔目錄內的日誌，使用絕對路徑

highgo=# select walminer_wal_add('/hgdbbak/archive');

walminer_wal_add
--------------------
2 file add success
(1 row)

4、檢視解析器內載入的日誌

highgo=# select walminer_wal_list();
walminer_wal_list
---------------------------------------------
(/opt/HigoGO4.3.4.7-see/data/pg_wal/000000010000000000000003)
(1 row)

5、執行解析

highgo=# select walminer_start('NULL','NULL',0,0);
NOTICE: Change Wal Segment To:pg_wal/000000010000000000000002
NOTICE: Change Wal Segment To:(null)
walminer_start
---------------------
walminer sucessful!
(1 row)

6、解析結果檢視

highgo=# \x
Expanded display is on.
highgo=# select record_database,record_user,op_text,op_undo from walminer_contents;
-[ RECORD 1 ]---+-----------------------------------------------------------------------------------------------------
record_database | highgo
record_user     | sysdba
op_text         | INSERT INTO "public"."t2"("i", "j", "k") VALUES(1, 1, 'qqqqqq');
op_undo         | DELETE FROM "public"."t2" WHERE "i"=1 AND "j"=1 AND "k"='qqqqqq' AND ctid = '(0,1)';
-[ RECORD 2 ]---+-----------------------------------------------------------------------------------------------------
record_database | highgo
record_user     | sysdba
op_text         | INSERT INTO "public"."t2"("i", "j", "k") VALUES(2, 2, 'wwwwww');
op_undo         | DELETE FROM "public"."t2" WHERE "i"=2 AND "j"=2 AND "k"='wwwwww' AND ctid = '(0,2)';
-[ RECORD 3 ]---+-----------------------------------------------------------------------------------------------------
record_database | highgo
record_user     | sysdba
op_text         | INSERT INTO "public"."t2"("i", "j", "k") VALUES(3, 3, 'eeeee');
op_undo         | DELETE FROM "public"."t2" WHERE "i"=3 AND "j"=3 AND "k"='eeeee' AND ctid = '(0,3)';
-[ RECORD 4 ]---+-----------------------------------------------------------------------------------------------------
record_database | highgo
record_user     | sysdba
op_text         | UPDATE "public"."t2" SET "k" = '1111qqqqq' WHERE "i"=1 AND "j"=1 AND "k"='qqqqqq';
op_undo         | UPDATE "public"."t2" SET "k" = 'qqqqqq' WHERE "i"=1 AND "j"=1 AND "k"='1111qqqqq' AND ctid = '(0,4)'
-[ RECORD 5 ]---+-----------------------------------------------------------------------------------------------------
record_database | highgo
record_user     | sysdba
op_text         | DELETE FROM "public"."t2" WHERE "i"=2 AND "j"=2 AND "k"='wwwwww';
op_undo         | INSERT INTO "public"."t2"("i", "j", "k") VALUES(2, 2, 'wwwwww');
-[ RECORD 6 ]---+-----------------------------------------------------------------------------------------------------
record_database | highgo
record_user     | sysdba
op_text         | INSERT INTO "public"."t2"("i", "j", "k") VALUES(4, 4, '44444rrrrrr');
op_undo         | DELETE FROM "public"."t2" WHERE "i"=4 AND "j"=4 AND "k"='44444rrrrrr' AND ctid = '(0,5)';

highgo=# \q

注：通過解析內容可看到剛才執行的插入語句及對應的undo語句

7、停止解析，釋放記憶體

test=# select walminer_stop();
walminer_stop
-------------------
walminer cleaned!
(1 row)

五、從非wal產生的資料庫中執行wal日誌解析

1、在生產庫中建立擴充套件

test=# create extension xlogminer;

2、建立資料字典

test=# select walminer_build_dictionary('/hgdbbak/test');
walminer_build_dictionary
---------------------------
Dictionary build success!
(1 row)

3、在測試庫中建立擴充套件

highgo=# create extension walminer ;
CREATE EXTENSION

4、載入字典庫，將在生產庫建立的字典移動到/opt下，載入此字典庫

highgo=# select walminer_load_dictionary('/opt/test')
highgo-# ;
walminer_load_dictionary
--------------------------
Dictionary load success!
(1 row)

5、載入wal日誌

highgo=# select walminer_wal_add('/hgdbbak/archive');
walminer_wal_add
--------------------
3 file add success
(1 row)

highgo=# select walminer_wal_list();
walminer_wal_list
---------------------------------------------
(/hgdbbak/archive/000000010000000000000003)
(/hgdbbak/archive/000000010000000000000004)
(/hgdbbak/archive/000000010000000000000005)
(3 rows)

6、開始解析wal日誌

highgo=# select walminer_start('null','null',900,1000);
NOTICE: Change Wal Segment To:/hgdbbak/archive/000000010000000000000003
NOTICE: wal record after time 2020-05-18 11:02:36+08 or 0/300d2e8 will be analyse completely
NOTICE: Change Wal Segment To:/hgdbbak/archive/000000010000000000000004
NOTICE: Change Wal Segment To:/hgdbbak/archive/000000010000000000000005
NOTICE: Change Wal Segment To:(null)
walminer_start
---------------------
walminer sucessful!
(1 row)

7、檢視解析內容

highgo=# \x
Expanded display is on.
highgo=# select * from walminer_contents ;
-[ RECORD 1 ]-----+-------------------------------------------------------------------------------------------------
----------------------------------------------
xid | 983
virtualxid | 1
timestamptz | 2020-05-18 13:42:56.797565+08
record_database | test
record_user | sysdba
record_tablespace | pg_default
record_schema | public
op_type | INSERT
op_text | INSERT INTO "public"."gt_test1"("id", "passwd", "num") VALUES(2, 'be236f80d7aa78bf06ca288edbdc03
3e', 3001);
op_undo | DELETE FROM "public"."gt_test1" WHERE "id"=2 AND "passwd"='be236f80d7aa78bf06ca288edbdc033e' AND
"num"=3001 AND ctid = '(0,1)';
-[ RECORD 2 ]-----+-------------------------------

瀚高資料庫日誌挖掘方法

相關文章