利用WalMiner解析PostgreSQL的WAL日志

IT那活儿 2022-12-15

1336

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

前奏

WalMiner是从PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持。如果出现业务误操作DML的情况，可以及时挽回数据。

1.1 安装WalMiner

上来就踩坑，编译报错：

make: opt/rh/llvm-toolset-7/root/bin/clang: Command not found
make: *** [pg_logminer.bc] Error 127

处理方式，注释插件控制bc：

cd app/pg/pg_156_5432/postgresql/lib/postgresql/pgxs/src/makefiles/
vi pgxs.mk

#ifeq ($(with_llvm), yes)
#all: $(addsuffix .bc, $(MODULES)) $(patsubst %.o,%.bc, $(OBJS))
#endif

#ifeq ($(with_llvm), yes)
#       $(foreach mod, $(MODULES), $(call install_llvm_module,$(mod),$(mod).bc))
#endif # with_llvm

#ifeq ($(with_llvm), yes)
#       $(call install_llvm_module,$(MODULE_big),$(OBJS))
#endif # with_llvm

#ifeq ($(with_llvm), yes)
#       $(foreach mod, $(MODULES), $(call uninstall_llvm_module,$(mod)))
#endif # with_llvm

#ifeq ($(with_llvm), yes)
#       $(call uninstall_llvm_module,$(MODULE_big))
#endif # with_llvm

1.2 重新编译，记得刷新postgresql环境变量

/usr/bin/mkdir -p '/app/pg/pg_156_5432/postgresql/lib/postgresql'
/usr/bin/mkdir -p '/app/pg/pg_156_5432/postgresql/share/postgresql/extension'
/usr/bin/mkdir -p '/app/pg/pg_156_5432/postgresql/share/postgresql/extension'
/usr/bin/install -c -m 755  walminer.so '/app/pg/pg_156_5432/postgresql/lib/postgresql/walminer.so'
/usr/bin/install -c -m 644 .//walminer.control '/app/pg/pg_156_5432/postgresql/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//walminer--1.0.sql '/app/pg/pg_156_5432/postgresql/share/postgresql/extension/'

1.3 进库创建

postgres=# create extension walminer;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version |   Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.7     | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
telepg_monitor | 2.7.6   | public | Expansion monitoring module
walminer | 1.0 | public | inspect the contents of database pages at a low level

二

解析步骤

2.1 添加要解析的wal日志文件

postgres=# select walminer_wal_add('/app/pg/data_156_5432/data/pg_wal');
NOTICE: Get data dictionary from current database.
walminer_wal_add
---------------------
13 file add success
(1 row)

2.2 查看可以解析的文件

postgres=# select walminer_wal_list();
walminer_wal_list
---------------------------------------------------------------
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000001)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000002)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000003)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000004)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000005)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000006)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000007)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000008)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000009)
(/app/pg/data_156_5432/data/pg_wal/00000001000000000000000A)
(/app/pg/data_156_5432/data/pg_wal/00000001000000000000000B)
(/app/pg/data_156_5432/data/pg_wal/00000001000000000000000C)
(/app/pg/data_156_5432/data/pg_wal/00000001000000000000000D)
(13 rows)

2.3 解析wal

postgres=# select walminer_start('null','null',0,0);
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000001
NOTICE: wal record after time 2022-02-24 18:17:44+08 or 0/166aff0 will be analyse completely
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000002
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000003
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000004
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000005
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000006
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000007
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000008
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000009
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/00000001000000000000000A
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/00000001000000000000000B
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/00000001000000000000000C
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/00000001000000000000000D
walminer_start
---------------------
walminer sucessful!
(1 row)

2.4 查看解析结果

postgres=# select * from walminer_contents;
-[ RECORD 1 ]-----+-------------------------------------------------------------
xid | 609
virtualxid | 1
timestamptz | 2022-06-13 16:25:42.259986+08
record_database | postgres
record_user | root
record_tablespace | pg_default
record_schema | public
op_type | INSERT
op_text | INSERT INTO "public"."test"("id") VALUES(1);
op_undo | DELETE FROM "public"."test" WHERE "id"=1 AND ctid = '(0,1)';
lsn | 0/DAA6138
commit_end_lsn | 0/DAA61A8

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。

2.5 结束walminer操作

select walminer_stop();

三

常规操作

3.1 根据时间解析

select walminer_by_time(starttime, endtime);

3.2 精确到表的解析

select walminer_by_xid(xid,'true',reloid);

注：'true'和‘false’代表是否为精确解析模式，reloid为目标表的oid(注意不是relfilenode)

四

适用性

4.1 优点

walminer可执行的骚操作：walminer是提供解析功能，可以挪用别的数据库的wal日志到有插件的数据上执行（数据库版本一致，要依靠数据字典），并查看。避免没有插件，再安装插件耽误时间。

4.2 使用注意事项

1）walminer对DDL支持力度不大，很多无法解析，不建议作为解析DDL依靠。
2）walminer版本有三个，建议用2.0，telepg12.4不支持3.0。
3）PG11/PG12中WAL文件大小必须16MB。

本文作者：饶茂林(上海新炬中北团队）

本文来源：“IT那活儿”公众号

软件 postgresql

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。