Postgresql WAL日志解析挖掘（walminer 4.0）

1.walminer介绍

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

目前主要有如下功能：

• 从waL日志中解析出SQL，包括DML和少量DDL。
  解析出执行的SQL语句的工具，并能生成对应的undo SQL语句。与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。

• 数据页挽回。
  当数据库被执行了TRUNCATE等不被wal记录的数据清除操作，或者发生磁盘页损坏，可以使用此功能从wal日志中搜索数据，以期尽量挽回数据。

Walminer 3.0版本是以插件的模式进行使用。💻walminer 3.0安装使用看这里👀
Walminer 4.0版本摒弃插件模式改为bin模式，现已脱离对目标数据库的编译依赖和安装依赖，一个walminer工具可以解析PG10~PG15的WAL日志。

2.下载walminer

https://gitee.com/movead/XLogMiner/releases

3.安装walminer

## 解压缩
[root@pg soft]# su - postgres
[postgres@pg soft]$ tar -zxvf walminer_x86_64_v4.4.2.tar.gz

## 创建 walminer 运行目录
[postgres@pg soft]# mkdir -p /usr/local/walminer
[postgres@pg soft]# chown postgres:postgres walminer

## 设置环境变量
[postgres@pg ~]$ vi .bash_profile
export PATH=$PATH:/tmp/soft/walminer_x86_64_v4.4.2/bin/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/tmp/soft/walminer_x86_64_v4.4.2/lib/

[postgres@pg ~]$ source .bash_profile

## 测试安装情况（执行walminer help，可以正常打印 help 信息，则证明安装部署成功）
[postgres@pg ~]$ walminer help
walminer [command] [options]
COMMANDS
---------
#wal2sql
  options
    -D dic file for miner
    -a out detail info for catalog change
    -w wal file path to miner
    -t dest of miner result(1 stdout, 2 file, 3 db)(stdout default)
    -k boundary kind(1 all, 2 lsn, 3 time, 4 xid)(all default)
    -m miner mode(0 nomal miner, 1 accurate miner)(nomal default) if k=2
    -r the relname for single table miner 
    -s start location if k=2 or k=3, or xid if k = 4 
          if k=2 default the min lsn of input wals   
          if k=3 or k=4 you need input this
    -e end wal location if k=2 or k=3
          if k=2 default the max lsn of input wals   
          if k=3 you need input this
    -f file to store miner result if t = 2
    -d target database name if t=3(default postgres)
    -h target database host if t=3(default localhost)
    -p target database port if t=3(default 5432)
    -u target database user if t=3(default postgres)
    -W target user password if t=3

如果此步骤失败，出现以下错误：
walminer: /lib64/libc.so.6: version `GLIBC_2.25' not found (required by walminer)  
解决方法在后面，请下拉至 遇到的问题 进行查看。

4.WAL日志解析挖掘测试

4.1.普通误操作（DML）

## 创建测试数据
postgres=# create table t1(id int,name text);
CREATE TABLE
postgres=# insert into t2 values (1,'aaaa');
INSERT 0 1
postgres=# insert into t2 values (2,'bbbb');
INSERT 0 1
postgres=# insert into t2 values (3,'cccc');
INSERT 0 1
postgres=# select * from t2;
 id | name 
----+------
  1 | aaaa
  2 | bbbb
  3 | cccc
(3 rows)

postgres=# update t2 set name='bbbbbbbbbbbb' where id=3;
UPDATE 1
postgres=# delete from t2 where id=1;
DELETE 1
postgres=# select * from t2;
 id |     name     
----+--------------
  2 | bbbb
  3 | bbbbbbbbbbbb
(2 rows) 

## 生成数据字典
[postgres@pg ~]$ mkdir walminer
[postgres@pg ~]$ cd walminer/
[postgres@pg walminer]$ walminer builtdic -D ~/walminer/walminer.dic -f -h localhost -p 5432 -u postgres
#################################################
Walminer for PostgreSQL wal
Contact Author by mail 'lchch1990@sina.cn'
No License for walminer test, for get a license you can read:
https://gitee.com/movead/XLogMiner/wikis/walminer%20license
#################################################
DIC INFO#
sysid:7301135029075117750 dboid:5 timeline:1 dbversion:150004 walminer:4.4

## 执行解析
[postgres@pg walminer]$ walminer wal2sql -D ~/walminer/walminer.dic -w /pgsql15.4/pg_arch -t 3 -h localhost -u postgres -p 5432

postgres=# select * from walminer_contents;
-[ RECORD 16 ]------------------------------------------------------------------
sqlno      | 1
xid        | 784
topxid     | 0
sqlkind    | 
minerd     | t
timestamp  | 2023-11-23 16:04:25.292517+08
op_text    | DELETE FROM public.t2 WHERE id=1
undo_text  | INSERT INTO public.t2(id) VALUES(1)
complete   | t
relation   | t2
start_lsn  | 0/D03CE20
commit_lsn | 0/D03CE58
-[ RECORD 17 ]------------------------------------------------------------------
sqlno      | 1
xid        | 785
topxid     | 0
sqlkind    | UPDATE
minerd     | t
timestamp  | 2023-11-23 16:04:59.863816+08
op_text    | UPDATE public.t2 SET id=22 WHERE id=3
undo_text  | UPDATE public.t2 SET id=3 WHERE id=22
complete   | t
relation   | t2
start_lsn  | 0/D03CEB8
commit_lsn | 0/D03CF00

## 通过undo_text，对误操作进行恢复
postgres=# INSERT INTO public.t2(id) VALUES(1);
INSERT 0 1
postgres=# UPDATE public.t2 SET id=3 WHERE id=22;
UPDATE 1
postgres=# select * from t2;
 id 
----
  2
  1
  3
(3 rows)

4.2.drop/truncate操作（DDL）

# drop操作测试
## 创建测试表
postgres=# create table t4 (id int);
CREATE TABLE
postgres=# insert into t4 values(1111111);
INSERT 0 1
postgres=# select * from t4;
   id    
---------
 1111111
(1 row)

postgres=# select oid,relfilenode,relname from pg_class where relname ='t4';
  oid  | relfilenode | relname 
-------+-------------+---------
 16522 |       16522 | t4
(1 row)

postgres=# drop table t4 ;
DROP TABLE

## 创建替身表（表结构等字段需一致）
postgres=# create table t5 (id int);
CREATE TABLE
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/11035678
(1 row)

## 生成新的数据字典
[postgres@pg ~]$ walminer builtdic -D ~/walminer/walminer.dic -f -h localhost -p 5432 -u postgres
#################################################
Walminer for PostgreSQL wal
Contact Author by mail 'lchch1990@sina.cn'
No License for walminer test, for get a license you can read:
https://gitee.com/movead/XLogMiner/wikis/walminer%20license
#################################################
DIC INFO#
sysid:7301135029075117750 dboid:5 timeline:1 dbversion:150004 walminer:4.4

## 执行替身命令
[postgres@pg ~]$ walminer avatardic -D ~/walminer/walminer.dic  -r 't5' -n 16522
#################################################
Walminer for PostgreSQL wal
Contact Author by mail 'lchch1990@sina.cn'
No License for walminer test, for get a license you can read:
https://gitee.com/movead/XLogMiner/wikis/walminer%20license
#################################################
Avatar rel t5 to relfilenode 16522

## 执行解析
[postgres@pg ~]$ walminer wal2sql -D ~/walminer/walminer.dic -w /pgsql15.4/pg_arch -t 3 -h localhost -u postgres -p 5432

postgres=# select * from walminer_contents ;
-[ RECORD 8 ]-----------------------------------------------------------------
sqlno      | 1
xid        | 868
topxid     | 0
sqlkind    | INSERT
minerd     | t
timestamp  | 2023-11-23 17:08:54.77116+08
op_text    | INSERT INTO public.t5(id) VALUES(1*****1)
undo_text  | DELETE FROM public.t5 WHERE id=1*****1
complete   | t
relation   | t5
start_lsn  | 0/110323D8
commit_lsn | 0/11032418


# truncate操作测试
## 创建测试表
postgres=# create table t4 (id int);
CREATE TABLE
postgres=# insert into t4 values (11111111);
INSERT 0 1
postgres=# select * from t4;
    id    
----------
 11111111
(1 row)
postgres=# truncate table t4;
TRUNCATE TABLE
postgres=# select * from t4;
 id 
----
(0 rows)

## 查看t4表信息，并创建替身表
postgres=# select oid,relfilenode from pg_class where relname='t4';
  oid  | relfilenode 
-------+-------------
 16561 |       16567
(1 row)
postgres=# create table t5 (id int);
CREATE TABLE

## 生成新的数据字典
[postgres@pg ~]$ walminer builtdic -D ~/walminer/walminer.dic -f -h localhost -p 5432 -u postgres
#################################################
Walminer for PostgreSQL wal
Contact Author by mail 'lchch1990@sina.cn'
No License for walminer test, for get a license you can read:
https://gitee.com/movead/XLogMiner/wikis/walminer%20license
#################################################
DIC INFO#
sysid:7301135029075117750 dboid:5 timeline:1 dbversion:150004 walminer:4.4

## 执行替身命令
[postgres@pg ~]$ walminer avatardic -D ~/walminer/walminer.dic  -r 't5' -n 16561（此为oid）
#################################################
Walminer for PostgreSQL wal
Contact Author by mail 'lchch1990@sina.cn'
No License for walminer test, for get a license you can read:
https://gitee.com/movead/XLogMiner/wikis/walminer%20license
#################################################
Avatar rel t5 to relfilenode 16561

## 执行解析
[postgres@pg ~]$ walminer wal2sql -D ~/walminer/walminer.dic -w /pgsql15.4/pg_arch -t 3 -h localhost -u postgres -p 5432

postgres=# select * from walminer_contents ;
-[ RECORD 1 ]------------------------------------------
sqlno      | 1
xid        | 922
topxid     | 0
sqlkind    | INSERT
minerd     | t
timestamp  | 2023-11-24 10:27:39.794824+08
op_text    | INSERT INTO public.t5(id) VALUES(11111111)
undo_text  | DELETE FROM public.t5 WHERE id=11111111
complete   | t
relation   | t5
start_lsn  | 0/170389F0
commit_lsn | 0/17038A30

## 通过op_text，进行恢复
postgres=# INSERT INTO public.t4(id) VALUES(11111111);
INSERT 0 1
postgres=# select * from t4;
    id    
----------
 11111111
(1 row)

5.参数说明

## walminer builtdic -D ~/walminer/walminer.dic -f -h localhost -p 5432 -u postgres
-d：目标数据库名（默认postgres）
-h：目标数据库地址（默认localhost）。如果-h指定的不是localhost，那么需要为-u指定的用户配置流复制权限。
-p：目标数据库端口（默认5432）
-u：目标数据库的用户名（默认postgres）
-W：目标数据库用户的密码
-D：数据字典生成文件路径
-f：指定-f，代表重写存在的walminer.dic文件

## walminer avatardic -D ~/walminer/walminer.dic  -r 't5' -n 16561
-r：替身的表名
-n：数据字典中被替身的relfilenode或oid

## walminer wal2sql -D ~/walminer/walminer.dic -w /pgsql15.4/pg_arch -t 3 -h localhost -u postgres -p 5432
-w：需要解析的wal所在目录
-t：解析结果的输出位置（1：标准输出，默认。2：输出到文件。3：输出到数据库。）
-k：解范围类型（1：解析所有，默认。2：指定lsn范围。3：指定时间范围。4：指定事务id。）
-m：解析模式（0：普通模式，对-s指定的范围进行解析。1：精确模式，k大于1时保证-s指定范围内数据的完全解析。）
-r：单表解析的表名
-s：当k=2时为开始lsn；当k=3时为开始时间；当k=4时为xid
-e：当k=2时为结束lsn；当k=3时为结束时间
-f：当t=2时指定解析结果的输出文件
-d：当t=3时指定目标数据库的数据库名（默认postgres）
-h：当t=3时指定目标数据库的地址（默认localhost）
-p：当t=3时指定目标数据库的端口（默认5432）
-u：当t=3时指定目标数据库的连接用户名（默认postgres）
-W：当t=3时指定目标数据库的连接用户密码

6.遇到的问题

升级glibc存在系统崩溃的风险，务必在测试环境进行严格测试，确保没有问题后在操作生产环境。

# 错误现象及原因
[postgres@pg ~]$ walminer help
walminer: /lib64/libc.so.6: version `GLIBC_2.25' not found (required by walminer)

是因为当前的glibc版本不符合要求，查看当前的glibc版本。

[root@pg ~]# strings /lib64/libc.so.6 | grep -E "^GLIBC" | sort -V -r | uniq
GLIBC_PRIVATE
GLIBC_2.17  ---目前glibc最高版本
GLIBC_2.16
GLIBC_2.15
GLIBC_2.14
GLIBC_2.13
GLIBC_2.12
GLIBC_2.11
GLIBC_2.10
GLIBC_2.9
GLIBC_2.8
GLIBC_2.7
GLIBC_2.6
GLIBC_2.5
GLIBC_2.4
GLIBC_2.3.4
GLIBC_2.3.3
GLIBC_2.3.2
GLIBC_2.3
GLIBC_2.2.6
GLIBC_2.2.5

# 解决方法
对glibc进行版本升级，以解决此问题。
升级glibc之前，需要对依赖的make、gcc也进行同步升级。

## 升级 make
如果当前版本为3.x，则需要升级，若版本为4.x，则跳过升级步骤。
[root@pg ~]# make -v
GNU Make 3.82

下载make安装包：
https://mirrors.aliyun.com/gnu/make/make-4.3.tar.gz

解压缩并创建构建目录：
[root@pg upgrade]# tar -zxvf make-4.3.tar.gz
[root@pg upgrade]# cd make-4.3/
[root@pg make-4.3]# mkdir build
[root@pg make-4.3]# cd build

进行预编译检查：
[root@pg build]# ../configure --prefix=/usr
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
..........
config.status: creating build.cfg
config.status: creating lib/Makefile
config.status: creating po/Makefile.in
config.status: creating doc/Makefile
config.status: creating tests/config-flags.pm
config.status: creating src/config.h
config.status: executing depfiles commands
config.status: executing po-directories commands
config.status: creating po/POTFILES
config.status: creating po/Makefile

进行编译安装：
[root@pg build]# make && make install
[root@pg build]# make -v
GNU Make 4.3

## 升级 gcc
如果当前版本为4.x，则需要升级，若版本为9.x，则跳过升级步骤。
[root@pg ~]# gcc -v
gcc version 4.8.5 20150623 (Red Hat 4.8.5-36) (GCC)

下载gcc安装包：
https://mirrors.aliyun.com/gnu/gcc/gcc-9.3.0/gcc-9.3.0.tar.gz

下载gcc依赖包：
https://gcc.gnu.org/pub/gcc/infrastructure/gmp-6.1.0.tar.bz2
https://gcc.gnu.org/pub/gcc/infrastructure/mpfr-3.1.4.tar.bz2
https://gcc.gnu.org/pub/gcc/infrastructure/mpc-1.0.3.tar.gz
https://gcc.gnu.org/pub/gcc/infrastructure/isl-0.18.tar.bz2

解压缩依赖包：
[root@pg upgrade]# tar -zvxf mpc-1.0.3.tar.gz
[root@pg upgrade]# tar jxvf gmp-6.1.0.tar.bz2
[root@pg upgrade]# tar jxvf mpfr-3.1.4.tar.bz2
[root@pg upgrade]# tar jxvf isl-0.18.tar.bz2

安装依赖包：
各依赖包之间也存在先后安装顺序（gmp-mpfr-mpc-isl）
--gmp
[root@pg upgrade]# cd gmp-6.1.0/
[root@pg gmp-6.1.0]# ./configure --prefix=/usr
checking build system type... cabylake-pc-linux-gnu
checking host system type... cabylake-pc-linux-gnu
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
...............
config.status: executing libtool commands
configure: summary of build options:

  Version:           GNU MP 6.1.0
  Host type:         cabylake-pc-linux-gnu
  ABI:               64
  Install prefix:    /usr/local
  Compiler:          gcc -std=gnu99
  Static libraries:  yes
  Shared libraries:  yes
[root@pg gmp-6.1.0]# make && make install

--mpfr
[root@pg upgrade]# cd mpfr-3.1.4/
[root@pg mpfr-3.1.4]# ./configure --prefix=/usr
[root@pg mpfr-3.1.4]# make && make install

--mpc
[root@pg upgrade]# cd mpc-1.0.3/
[root@pg mpc-1.0.3]# ./configure --prefix=/usr
[root@pg mpc-1.0.3]# make && make install

--isl
[root@pg upgrade]# cd isl-0.18/
[root@pg isl-0.18]# ./configure --prefix=/usr
[root@pg isl-0.18]# make && make install

创建gcc构建目录，进行预编译检查：
[root@pg gcc-9.3.0]# mkdir build
[root@pg gcc-9.3.0]# cd build/
[root@pg build]# ../configure --enable-checking=release --enable-language=c,c++ --disable-multilib --prefix=/usr

进行编译安装：
[root@pg build]# make && make install #该步骤执行时间较长（3小时左右），可以采用下面的方式提高编译速度。

或者执行
make -j4 #-j 代表编译时的任务数，为CPU核数，这样构建速度会快一些。
make install

[root@pg ~]# gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-pc-linux-gnu/9.3.0/lto-wrapper
Target: x86_64-pc-linux-gnu
Configured with: ../configure --enable-checking=release --enable-language=c,c++ --disable-multilib --prefix=/usr
Thread model: posix
gcc version 9.3.0 (GCC)

### gcc编译过程中如果遇到错误找不到 libisl.so.15 文件，可以设置环境变量后，再次尝试编译gcc
[root@pg ~]# vi .bash_profile
export LD_LIBRARY_PATH=/usr/lib:$LD_LIBRARY_PATH
[root@pg ~]# source .bash_profile

## 升级 glibc
下载glibc安装包：
https://mirrors.aliyun.com/gnu/glibc/glibc-2.31.tar.gz

解压缩文件：
[root@pg upgrade]# tar -zxvf glibc-2.31.tar.gz

查看安装glibc所需依赖包：
[root@pg upgrade]# cd glibc-2.31/
[root@pg glibc-2.31]# cat INSTALL | grep -E "newer|later" | grep "*"
   * GNU 'make' 4.0 or newer
   * GCC 6.2 or newer
   * GNU 'binutils' 2.25 or later
   * GNU 'texinfo' 4.7 or later
   * GNU 'bison' 2.7 or later
   * GNU 'sed' 3.02 or newer
   * Python 3.4 or later
   * GDB 7.8 or later with support for Python 2.7/3.4 or later
   * GNU 'gettext' 0.10.36 or later
注：确保上述依赖包的版本满足当前需求，对不满足的进行版本升级，如不升级，后续预编译检查会存在错误。

### 例如 python 版本不满足glibc编译升级要求，进行 python 版本升级
当前python版本：
[root@pg ~]# python -V
Python 2.7.5

下载python安装包：
https://www.python.org/ftp/python/3.8.10/Python-3.8.10.tgz

进行编译升级：
[root@pg upgrade]# tar -zxvf Python-3.8.10.tgz
[root@pg upgrade]# cd Python-3.8.10/
[root@pg Python-3.8.10]# ./configure --prefix=/usr
[root@pg Python-3.8.10]# make && make install
[root@pg bin]# ln -s /usr/local/bin/python3.8 /usr/bin/python
[root@pg bin]# python -V
Python 3.8.10

创建glibc构建目录，进行预编译检查：
[root@pg glibc-2.31]# mkdir build
[root@pg glibc-2.31]# cd build/
[root@pg build]# ../configure --prefix=/usr --disable-profile --enable-add-ons --with-headers=/usr/include --with-binutils=/usr/bin --disable-sanity-checks --disable-werror

编译安装之前，备份当前lib目录：
cp -r /usr/lib64 /usr/lib64.back

进行glibc编译安装：
[root@pg build]# make && make install

安装完成后，可以忽略出现的以下错误：
You should restart this script from your build directory after you've
fixed all problems!
Btw. the script doesn't work if you're installing GNU libc not as your
primary library!
make[1]: *** [Makefile:120: install] Error 1
make[1]: Leaving directory '/tmp/upgrade/glibc-2.31'
make: *** [Makefile:12: install] Error 2

查看glibc升级后的版本：
[root@pg ~]# ldd --version
ldd (GNU libc) 2.31

[root@pg ~]# strings /lib64/libc.so.6 | grep -E "^GLIBC" | sort -V -r | uniq
GLIBC_PRIVATE
GLIBC_2.30
GLIBC_2.29
GLIBC_2.28
GLIBC_2.27
GLIBC_2.26
GLIBC_2.25
GLIBC_2.24
GLIBC_2.23
GLIBC_2.22
GLIBC_2.18
GLIBC_2.17
GLIBC_2.16
GLIBC_2.15
GLIBC_2.14
GLIBC_2.13
GLIBC_2.12
GLIBC_2.11
GLIBC_2.10
GLIBC_2.9
GLIBC_2.8
GLIBC_2.7
GLIBC_2.6
GLIBC_2.5
GLIBC_2.4
GLIBC_2.3.4
GLIBC_2.3.3
GLIBC_2.3.2
GLIBC_2.3
GLIBC_2.2.6
GLIBC_2.2.5

### glibc升级后，避免打开新的终端会话出现以下警告，需要执行以下命令
--警告信息如下
Connecting to 192.168.88.6:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

Last login: Thu Nov 23 14:33:43 2023 from 192.168.88.1
-bash: warning: setlocale: LC_TIME: cannot change locale (en_US.UTF-8)

--执行命令
[root@pg build]# make localedata/install-locales