xlog、wal 分析工具 xlogminer、walminer

os: ubuntu 16.04
db: postgresql 10.6

该 blog 是 http://postgres.cn/v2/news/viewone/1/417 的实践记录.读者可以看 https://gitee.com/movead/XLogMiner 的说明.翰高公司在postgresql上还有有相当实力的.

XlogMiner 是从PostgreSQL的WAL(write ahead logs)日志中解析出执行的SQL语句的工具，并能生成出对应的undo SQL语句。
其开源项目地址为：https://github.com/HighgoSoftware/XLogMiner。 此版本使用限制较大，需要将 wal 级别设置为 logical，而且需要将表设置为 IDENTITY FULL 模式。
目前XlogMiner改名为WalMiner。

新的开源地址为：https://gitee.com/movead/XLogMiner

同时,WalMiner做了功能增强
1.WalMiner支持解析minimal级别以上的任何wal日志级别。

wal_level = logical                     # minimal, replica, or logical

2.无需将表设置为 IDENTITY FULL 模式。

3.增加对系统表修改的 wal 记录的解析。

版本

# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 16.04.5 LTS
Release:	16.04
Codename:	xenial
#
# su - postgres
$ psql -c "select version();"
                                                     version                                                     
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

下载安装

# su - postgres
$ git clone https://gitee.com/movead/XLogMiner.git
$ cd XLogMiner/
$ git branch -a
$ git checkout WalMiner_10_0_1

仔细阅读 ./XLogMiner/README.md,内附详细说明

此时需要将walminer目录放置到编译通过的PG工程的"…/contrib/"目录下

$ cp -R ~/XLogMiner/walminer ~/postgresql-10.6/contrib/
$ cd ~/postgresql-10.6/contrib/walminer
$ make
$ make install
/bin/mkdir -p '/usr/pgsql-10/lib'
/bin/mkdir -p '/usr/pgsql-10/share/extension'
/bin/mkdir -p '/usr/pgsql-10/share/extension'
/usr/bin/install -c -m 755  walminer.so '/usr/pgsql-10/lib/walminer.so'
/usr/bin/install -c -m 644 ./walminer.control '/usr/pgsql-10/share/extension/'
/usr/bin/install -c -m 644 ./walminer--1.0.sql  '/usr/pgsql-10/share/extension/'

如果是通过 apt,yum方式安装的话,需要在另外的机器上用源码变异生成 .so .control .sql 文件,然后拷贝
参考<<ubuntu 16.04 + apt/make + postgresql 11>> https://blog.csdn.net/ctypyb2002/article/details/88393046

使用

walminer 是作为 extension 存在的,所以需要事先 create extension

$ ls -l $PGDATA/pg_wal
total 49156
-rw------- 1 postgres postgres 16777216 Mar 15 11:01 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Apr  2 12:35 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Mar 15 11:07 000000010000000000000003
drwx------ 2 postgres postgres     4096 Mar 12 16:55 archive_status

postgres=# show wal_level;
 wal_level 
-----------
 logical
(1 row)

postgres=# create table tmp_t0(c0 varchar(100),c1 varchar(100),c2 varchar(100));
CREATE TABLE
postgres=# 
postgres=# insert into tmp_t0(c0,c1,c2) select id::text,md5(id::text),md5(md5(id::text)) from generate_series(1,20) as id;
INSERT 0 20
postgres=#
postgres=# delete from tmp_t0 where c0::int4 <=10;
DELETE 10
postgres=# selecg pg_switch_wal();
postgres=#
postgres=# checkpoint;
CHECKPOINT

$ cp $PGDATA/pg_wal/000000010000000000000003 /tmp

将需要被分析的wal日志拷贝到 /tmp 目录.

create extension walminer

postgres=# create extension walminer;
CREATE EXTENSION
postgres=# 
postgres=# \dx
                                List of installed extensions
     Name     | Version |   Schema   |                      Description                      
--------------+---------+------------+-------------------------------------------------------
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 walminer     | 1.0     | public     | inspect the contents of database pages at a low level
(2 rows)

添加 wal 文件或者目录

postgres=# select walminer_wal_add('/tmp/000000010000000000000003');

或者移除

postgres=# select walminer_wal_remove('/tmp/000000010000000000000003');

列出 wal 文件

postgres=# select walminer_wal_list();
        walminer_wal_list        
---------------------------------
 (/tmp/000000010000000000000003)
(1 row)

解析

postgres=# select walminer_start('NULL','NULL',0,0);
NOTICE:  Change Wal Segment To:/tmp/000000010000000000000003
NOTICE:  wal record after time 2019-04-02 15:27:57+08 or 0/30001e8 will be analyse completely
NOTICE:  Change Wal Segment To:(null)
   walminer_start    
---------------------
 walminer sucessful!
(1 row)

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值

查看解析结果

postgres=# select * from walminer_contents;

结束 walminer 操作

postgres=# select walminer_stop();

参考:
http://postgres.cn/v2/news/viewone/1/417
https://github.com/HighgoSoftware/XLogMiner
https://gitee.com/movead/XLogMiner