合 PG日志挖掘工具之WalMiner用于误操作恢复

发布日期 2021年9月27日 · 已更新 2023年3月17日

0 1195 4

👉 本文共约8681个字，系统预计阅读时间或需33分钟。

WalMiner简介
walminer3.0更新内容
walminer4.0介绍
PG版本支持
编译安装
使用方法-SQL解析
场景一：从WAL日志产生的数据库中直接执行解析
1. 创建walminer的extension
2. 添加要解析的wal日志文件
3. Remove wal日志文件
4. List wal日志文件
5. 执行解析
6. 解析结果查看
7. 结束walminer操作
场景二：从非WAL产生的数据库中执行WAL日志解析
于生产数据库
1.创建walminer的extension
2.生成数据字典
于测试数据库
1. 创建5walminer的extension
2. load数据字典
3. add wal日志文件
4. remove wal日志文件
5. list wal日志文件
6. 执行解析
7. 解析结果查看
8.结束walminer操作,该函数作用为释放内存，结束日志分析，该函数没有参数。
场景三：自apply解析（开发中的功能,慎用）
此功能可以处理主备切换延迟数据
自apply解析功能说明
场景四：DDL解析
使用限制
使用方法-数据页挽回(坏块修复)
1. 环境搭建
2. 执行数据挽回
使用限制
老版本示例
WalMiner 4.3版本使用
安装
使用示例
builtdic功能
功能综述
help信息
使用演示
showdic功能
功能综述
help信息
使用演示
avatardic功能
功能综述
help信息
使用演示
wal2sql功能
功能综述
help信息
使用演示
附录
版本更新
fosync功能
功能综述
功能原理
help信息
版本更新
DDL解析模块
功能综述
已经实现的DDL命令
适用范围
dic同步模块
模块综述
数据字典同步
版本限制
总结
参考

PG中通过pg_waldump来分析pg_wal日志：https://www.dbaup.com/pgzhongtongguopg_waldumplaifenxipg_walrizhi.html

WalMiner简介

WalMiner是从PostgreSQL的WAL(write ahead logs)日志中解析出执行的SQL语句的工具，并能生成对应的undo SQL语句。与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。

WalMiner是从PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持。目前主要有如下功能：

从waL日志中解析出SQL，包括DML和少量DDL
解析出执行的SQL语句的工具，并能生成对应的undo SQL语句。与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。
数据页挽回
当数据库被执行了TRUNCATE等不被wal记录的数据清除操作，或者发生磁盘页损坏，可以使用此功能从wal日志中搜索数据，以期尽量挽回数据。

XlogMiner Enhancements Released and Renamed to WalMiner：https://www.postgresql.org/about/news/xlogminer-enhancements-released-and-renamed-to-walminer-1919/

注意：walminer是Highgo开源的一款软件。HGDB所有版本均支持walminer。

walminer3.0更新内容

代码结构变化
增加数据页挽回功能(坏块修复)
增加对开发库PG14的支持

walminer4.0介绍

walminer是PostgreSQL的wal日志解析工具，4.0版本摒弃插件模式改为bin模式，现已脱离对目标数据库的编译依赖和安装依赖，一个walminer工具可以解析PG10~PG15的WAL日志。现已实现的功能为wal2sql、fosync、pgto。

1.1 walminer功能介绍

wal2sql为walminer的基本功能，可以将wal日志解析为SQL语句，以及其undo语句,可解析部分DDL语句。
fosync为在wal2sql的基础上实现的，PG故障转移延迟数据同步功能。
pgto为PG的CDC工具,库外解析;一键部署;低wal级别。

1.2 walminer导航

walminer4.0的功能使用方法介绍地址
walminer4.0功能演示视频地址
walminer4.0软件下载地址
开发wiki地址

PG版本支持

walminer3.0支持PostgreSQL 10及其以上版本。（此版本放弃对9.x的支持）

编译安装

编译一：PG源码编译
如果你从编译pg数据库开始：

将walminer目录放置到编译通过的PG工程的"../contrib/"目录下
进入walminer目录
执行命令
make && make install
1
make && make install

编译二：依据PG安装编译
如果你使用yum或者pg安装包已经编译安装了pg

配置pg的bin路径至环境变量
export PGHOME=/pg13/pg13 export PATH=$PGHOME/bin:$PATH:.
1
2
export PGHOME=/pg13/pg13
export PATH=$PGHOME/bin:$PATH:.
进入walminer代码路径
unzip XLogMiner-master.zip cd ./XLogMiner-master/walminer
1
2
unzip XLogMiner-master.zip
cd ./XLogMiner-master/walminer

执行编译安装，注意修改MAJORVERSION的值，MAJORVERSION支持‘10’,‘11’,‘12’,‘13’

USE_PGXS=1 MAJORVERSION=13 make
USE_PGXS=1 MAJORVERSION=13 make install

1 2	USE_PGXS=1 MAJORVERSION=13 make USE_PGXS=1 MAJORVERSION=13 make install

过程：

[pg13@lhrpg walminer]$ USE_PGXS=1 MAJORVERSION=13 make
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o walminer.o walminer.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o wm_utils.o wm_utils.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o datadictionary.o datadictionary.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o fetchcatalogtable.o fetchcatalogtable.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o wallist.o wallist.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o walreader.o walreader.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o walminer_decode.o walminer_decode.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o imagemanage.o imagemanage.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o wal2sql.o wal2sql.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o walminer_contents.o walminer_contents.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o walminer_thread.o walminer_thread.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o wal2sql_spi.o wal2sql_spi.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o wal2sql_ddl.o wal2sql_ddl.c
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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal  -D_GNU_SOURCE   -c -o pagecollect.o pagecollect.c
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 -shared -o walminer.so walminer.o wm_utils.o datadictionary.o fetchcatalogtable.o wallist.o walreader.o walminer_decode.o imagemanage.o wal2sql.o walminer_contents.o walminer_thread.o wal2sql_spi.o wal2sql_ddl.o pagecollect.o -L/pg13/pg13/lib    -Wl,--as-needed -Wl,-rpath,'/pg13/pg13/lib',--enable-new-dtags  
[pg13@lhrpg walminer]$ USE_PGXS=1 MAJORVERSION=13 make install
/usr/bin/mkdir -p '/pg13/pg13/lib/postgresql'
/usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension'
/usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension'
/usr/bin/install -c -m 755  walminer.so '/pg13/pg13/lib/postgresql/walminer.so'
/usr/bin/install -c -m 644 .//walminer.control '/pg13/pg13/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//walminer--3.0.sql  '/pg13/pg13/share/postgresql/extension/'

[pg13@lhrpg walminer]$ USE_PGXS=1 MAJORVERSION=13 make

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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal -D_GNU_SOURCE -c -o walminer.o walminer.c

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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal -D_GNU_SOURCE -c -o wallist.o wallist.c

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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal -D_GNU_SOURCE -c -o wal2sql.o wal2sql.c

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 -shared -o walminer.so walminer.o wm_utils.o datadictionary.o fetchcatalogtable.o wallist.o walreader.o walminer_decode.o imagemanage.o wal2sql.o walminer_contents.o walminer_thread.o wal2sql_spi.o wal2sql_ddl.o pagecollect.o -L/pg13/pg13/lib -Wl,--as-needed -Wl,-rpath,'/pg13/pg13/lib',--enable-new-dtags

[pg13@lhrpg walminer]$ USE_PGXS=1 MAJORVERSION=13 make install

/usr/bin/mkdir -p '/pg13/pg13/lib/postgresql'

/usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension'

/usr/bin/install -c -m 755 walminer.so '/pg13/pg13/lib/postgresql/walminer.so'

/usr/bin/install -c -m 644 .//walminer.control '/pg13/pg13/share/postgresql/extension/'

/usr/bin/install -c -m 644 .//walminer--3.0.sql '/pg13/pg13/share/postgresql/extension/'

使用方法-SQL解析

场景一：从WAL日志产生的数据库中直接执行解析

1. 创建walminer的extension

create extension walminer;

1	create extension walminer;

2. 添加要解析的wal日志文件

-- 添加wal文件：
select walminer_wal_add('/pg13/pgdata/pg_wal');
-- 注：参数可以为目录或者文件

-- 添加wal文件：

select walminer_wal_add('/pg13/pgdata/pg_wal');

-- 注：参数可以为目录或者文件

3. Remove wal日志文件

-- 移除wal文件：
select walminer_wal_remove('/opt/test/wal');
-- 注：参数可以为目录或者文件

-- 移除wal文件：

select walminer_wal_remove('/opt/test/wal');

-- 注：参数可以为目录或者文件

4. List wal日志文件

-- 列出wal文件：
select walminer_wal_list();

1 2	-- 列出wal文件： select walminer_wal_list();

5. 执行解析

5.1 普通解析

-- 解析add的全部wal日志
select walminer_all();
-- 或 
select wal2sql();

-- 在add的wal日志中查找对应时间范围的wal记录
-- 可以参照walminer_time.sql回归测试中的使用用例
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);

-- 解析add的全部wal日志

select walminer_all();

-- 或

select wal2sql();

-- 在add的wal日志中查找对应时间范围的wal记录

-- 可以参照walminer_time.sql回归测试中的使用用例

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);

5.2 精确解析

-- 在add的wal日志中查找对应时间范围的wal记录
select walminer_by_time(starttime, endtime,'true'); 
或 
select wal2sql(starttime, endtime,'true');
-- 在add的wal日志中查找对应lsn范围的wal记录
select walminer_by_lsn(startlsn, endlsn,'true'); 
或 
select wal2sql(startlsn, endlsn,'true');
-- 在add的wal日志中查找对应xid的wal记录
select walminer_by_xid(xid,'true'); 
或 
select wal2sql(xid,'true');

-- 在add的wal日志中查找对应时间范围的wal记录

select walminer_by_time(starttime, endtime,'true');

或

select wal2sql(starttime, endtime,'true');

-- 在add的wal日志中查找对应lsn范围的wal记录

select walminer_by_lsn(startlsn, endlsn,'true');

或

select wal2sql(startlsn, endlsn,'true');

-- 在add的wal日志中查找对应xid的wal记录

select walminer_by_xid(xid,'true');

或

select wal2sql(xid,'true');

walminer的构建基础是，checkpoint之后对每一个page的更改会产生全页写(FPW),因此一个checkpoint之后的所有wal日志可以完美解析。注意checkpoint是指checkpoint开始的点，而不是checkpoint的wal记录的点，参照说明

普通解析会直接解析给定范围内的wal日志，因为可能没有找到之前的checkpoint点，所以会出现有些记录解析不全导致出现空的解析结果。

精确解析是指walminer程序会界定需要解析的wal范围，并在给定的wal范围之前探索一个checkpoint开始点c1，从c1点开始记录FPI，然后就可以完美解析指定的wal范围。如果在给定的wal段内没有找到c1点，那么此次解析会报错停止。

5.3 单表解析

-- 在add的wal日志中查找对应时间范围的wal记录
select walminer_by_time(starttime, endtime,'false',reloid); 
或
select wal2sql(starttime, endtime,'true',reloid);

-- 在add的wal日志中查找对应lsn范围的wal记录
select walminer_by_lsn(startlsn, endlsn,'true',reloid); 
或 
select wal2sql(startlsn, endlsn,'false',reloid);

-- 在add的wal日志中查找对应xid的wal记录
select walminer_by_xid(xid,'true',reloid);
或 
select wal2sql(xid,'true',reloid);

-- 在add的wal日志中查找对应时间范围的wal记录

select walminer_by_time(starttime, endtime,'false',reloid);

或

select wal2sql(starttime, endtime,'true',reloid);

-- 在add的wal日志中查找对应lsn范围的wal记录

select walminer_by_lsn(startlsn, endlsn,'true',reloid);

或

select wal2sql(startlsn, endlsn,'false',reloid);

-- 在add的wal日志中查找对应xid的wal记录

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

或

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

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

5.4 快捷解析

场景1中的加载数据字典和加载wal日志步骤可以省略，默认直接加载当前数据字典和当前wal路径下的所有wal文件。这个解析模式只在学习本工具时使用，在生产数据库中，可能会因为wal段切换而导致解析失败。

5.5 替身解析

如果一个表被drop或者被truncate等操作，导致新产生的数据字典不包含旧的数据库中所包含的relfilenode，那么使用新的数据字典无法解析出旧的wal日志中包含的的某些内容。在知晓旧表的表结构的前提下，可以使用替身解析模式。替身模式目前只适用于[场景一]。

-- 假设表t1被执行了vacuum full，执行vacuum full前的relfilenode为16384
-- 新建表t1的替身表
create table t1_avatar(i int);
-- 执行替身映射
select walminer_table_avatar(avatar_table_name, missed_relfilenode);
-- 执行解析
select wal2sql();
-- 查看解析结果时，会发现，对t1表的数据都以t1_avatar表的形式展现在输出结果中

-- 假设表t1被执行了vacuum full，执行vacuum full前的relfilenode为16384

-- 新建表t1的替身表

create table t1_avatar(i int);

-- 执行替身映射

select walminer_table_avatar(avatar_table_name, missed_relfilenode);

-- 执行解析

select wal2sql();

-- 查看解析结果时，会发现，对t1表的数据都以t1_avatar表的形式展现在输出结果中

6. 解析结果查看

select * from walminer_contents;

1	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
)

-- 表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

)

:warning: 注意：walminer_contents是walminer自动生成的unlogged表(之前是临时表，由于临时表在清理上有问题，引起工具使用不便，所以改为unlogged表)，在一次解析开始会首先创建或truncate walminer_contents表。

7. 结束walminer操作

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

select walminer_stop();

1	select walminer_stop();

场景二：从非WAL产生的数据库中执行WAL日志解析

:warning: 要求执行解析的PostgreSQL数据库和被解析的为同一版本

于生产数据库

1.创建walminer的extension

create extension walminer;

1	create extension walminer;

2.生成数据字典

select walminer_build_dictionary('/opt/proc/store_dictionary');
-- 注：参数可以为目录或者文件

1 2	select walminer_build_dictionary('/opt/proc/store_dictionary'); -- 注：参数可以为目录或者文件

于测试数据库

1. 创建5walminer的extension

create extension walminer;

1	create extension walminer;

2. load数据字典

select walminer_load_dictionary('/opt/test/store_dictionary');
-- 注：参数可以为目录或者文件

1 2	select walminer_load_dictionary('/opt/test/store_dictionary'); -- 注：参数可以为目录或者文件

3. add wal日志文件

-- 增加wal文件：
select walminer_wal_add('/opt/test/wal');
-- 注：参数可以为目录或者文件

-- 增加wal文件：

select walminer_wal_add('/opt/test/wal');

-- 注：参数可以为目录或者文件

4. remove wal日志文件

-- 移除wal文件：
select walminer_wal_remove('/opt/test/wal');
-- 注：参数可以为目录或者文件

-- 移除wal文件：

select walminer_wal_remove('/opt/test/wal');

-- 注：参数可以为目录或者文件

5. list wal日志文件

-- 列出wal文件：
select walminer_wal_list();
-- 注：参数可以为目录或者文件

-- 列出wal文件：

select walminer_wal_list();

-- 注：参数可以为目录或者文件

6. 执行解析

同上

7. 解析结果查看

select * from walminer_contents;

1	select * from walminer_contents;

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

select walminer_stop();

1	select walminer_stop();

场景三：自apply解析（开发中的功能,慎用）

场景一和场景二中的解析结果是放到结果表中的，场景三可以将解析结果直接apply到解析数据库中。命令执行的流程与场景一和场景二相同。

-- 参数意义参考walminer_by_lsn()接口
select walminer_apply(startlsn, endlsn,'true', reloid);

1 2	-- 参数意义参考walminer_by_lsn()接口 select walminer_apply(startlsn, endlsn,'true', reloid);

此功能可以处理主备切换延迟数据

当主库A发生故障，从库B切换为主库之后。

B库将A库未通过流复制apply的wal日志拷贝到B库可以获取的路径（这一步目前需要DBA自行处理，尚未纳入本功能）
在B库加载wal日志，执行walminer_apply()解析，其中：
startlsn选取未能apply到B库的lsn的开始值
endlsn参数写NULL
'true'这里最好填写‘true’，就不要写‘false’了
reloid是可选参数
walminer_apply()完成后,可以看到延迟的数据已经写到B库了

自apply解析功能说明

目前处于coding中，后续会添加严格的txid限制，避免错误修改数据，现在是尝鲜测试版
对于有冲突的项目，会把冲突sql存放到$PGDATA/pg_walminer/wm_analyselog/apply_failure文件中，供DBA自行判断处理
保持事务性，同一个事务中的一条SQLapply失败后，整个事务都会apply失败
看大家需求，后续可能考虑增加远程apply功能

场景四：DDL解析

系统表变化解析

目前walminer支持解析系统表的变化。也就是说如果在PG执行了DDL语句，walminer可以分析出DDL语句引起的系统表的变化。

-- 在执行解析之前，先执行如下语句，即可开启系统表解析功能
select wal2sql_with_catalog();

1 2	-- 在执行解析之前，先执行如下语句，即可开启系统表解析功能 select wal2sql_with_catalog();

DDL解析

-- 在执行解析之前，先执行如下语句，即可开启DDL解析功能
select wal2sql_with_ddl();

1 2	-- 在执行解析之前，先执行如下语句，即可开启DDL解析功能 select wal2sql_with_ddl();

:warning:系统表变化解析和DDL解析不共存，总是接受最新确定的状态。

:warning:walminer对DML数据的解析是要求没有系统表变化的，因此存在DDL变化时，可能导致DML解析不出来的情况。

使用限制

本版本解析DML语句。DDL语句解析功能正在不断开发。
只能解析与数据字典时间线一致的wal文件
当前walminer无法处理数据字典不一致问题，walminer始终以给定的数据字典为准，
对于无法处理的relfilenode，那么会丢弃这一条wal记录(会有一个notice在解析结果中没有体现)
complete属性只有在wallevel大于minimal时有效
xid解析模式不支持子事务
同时只能有一个walminer解析进程，否则会出现解析混乱

使用方法-数据页挽回(坏块修复)

1. 环境搭建

创建extension，创建数据地点，加载wal日志的方法与[SQL解析]中描述的方法一致。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(4)

标签： PG PostgreSQL 误操作恢复日志挖掘 WalMiner

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 PG日志挖掘工具之WalMiner用于误操作恢复

WalMiner简介

walminer3.0更新内容

walminer4.0介绍

PG版本支持

编译安装

使用方法-SQL解析

场景一：从WAL日志产生的数据库中直接执行解析

1. 创建walminer的extension

2. 添加要解析的wal日志文件

3. Remove wal日志文件

4. List wal日志文件

5. 执行解析

6. 解析结果查看

7. 结束walminer操作

场景二：从非WAL产生的数据库中执行WAL日志解析

于生产数据库

1.创建walminer的extension

2.生成数据字典

于测试数据库

1. 创建5walminer的extension

2. load数据字典

3. add wal日志文件

4. remove wal日志文件

5. list wal日志文件

6. 执行解析

7. 解析结果查看

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

场景三：自apply解析（开发中的功能,慎用）

此功能可以处理主备切换延迟数据

自apply解析功能说明

场景四：DDL解析

使用限制

使用方法-数据页挽回(坏块修复)

1. 环境搭建

相关文章

您可能还喜欢...

【PG】小麦苗PGCM第7期证书邮寄

PG或GP删除数据库报错dropdb: database removal failed: ERROR: database "db1" is being accessed by other users DETAIL: There are 3 other sessions using the database.

PG不能启动，报错PANIC replication checkpoint has wrong magic 1714437491 instead of 307747550

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复