新突破，令人惊艳的Walminer4.0

xiongcc2024年05月31日约 12 分钟

前言

在上一篇文章中，笔者介绍了各种闪回的实现方式，其中walminer应该算是最为便捷，高效的实现方式了，其他的实现方式，或多或少存在各种各种问题：

基于触发器，比如时态表，性能会有影响，另外所有表都去创建触发器也不现实
基于审计日志，全量记录也会产生性能影响，以及历史数据如何清理也是问题
基于逻辑解码，得全局打开日志级别，无法针对特定表设置，对于OLD KEYS，还需要身份标识，如果有TOAST (十分常见) 会更棘手一点
保留死元组，会导致表膨胀，影响查询效率

所以，walminer直接解析WAL的形式，并且4.0也脱离了插件的形式，无疑是最为优雅的解决方案。

功能一览

4.0在3.0的基础上做了大量增强，并且极大简化了使用步骤，https://gitee.com/movead/XLogMiner

之前的walminer版本需要在数据库安装walminer插件生成数据字典后才能完成解析，这种插件的方式可能在部署使用上有些不便，为了安装、部署、使用的方便，walminer4.0改为bin工具，无需在目标数据库安装任何插件。另外walminer工具的编译安装不再依赖任何数据库版本，因此一份walminer工具可以支持多个PG版本的解析

walminer支持多种功能，最为核心的功能是wal2sql，即将WAL解析为具体的SQL；也支持一键式CDC搭建，还支持fosync，即Failover sync(我猜的)，这个功能值得说道说道，PG主备在异步流复制的情况下，备库LSN可能比主库LSN小很多，如果主库还没有来得及发送给备库，当备库提升为主库，这部分数据就丢了，与pg_rewind实现还略有不同

pg_rewind的原理就是从history文件中找到分叉点(绿色的部分)，抹除红色部分的INSERT2。所以pg_rewind的目的是为了让旧主可以顺利降级成为备节点，而无需重建；而fosync的功能则是为了将这部分丢失的INSERT2，也就是还未重放的数据(现在的新主)，解析出来。具体的演示过程可以参照使用walminer同步故障转移后的延迟数据。

wal2sql

walminer最核心的功能当然是解析为具体的SQL了，pg_tm_aux + decoder_raw应该也能做到类似的事情，找到具体位点，创建指定位点LSN，解析为具体SQL，自己再反向生成UNDO SQL，不过要略微繁琐，其次还要考虑时间线对于pg_tm_aux的影响。

使用方式很简单

#wal2sql
  options
    -D dic file for miner
    -a out detail info for catalog change
    -C enable DDL miner
    -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 
    -b target database name which contain rel pointed by -r
    -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

举个栗子，以LSN为例，总共插入10条数据，更新4条，删除2条

postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 2/7F343080
(1 row)postgres=# create table t1(id int,info text);
CREATE TABLE
postgres=# insert into t1 select n,md5(random()::text) from generate_series(1,10) as n;
INSERT 0 10
postgres=# update t1 set info = 'xiongcc' where id < 5;
UPDATE 4
postgres=# delete from t1 where id > 8;
DELETE 2
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 2/7F359CE8
(1 row)

[postgres@sdw20 ~]$ walminer wal2sql -w /home/pgdata/pg_wal/ -D ./walminer.dic -k 2 -s 2/7F343080 -e 2/7F359CE8
#################################################
Walminer for PostgreSQL wal
Contact Author by mail '[email protected]'
Vip License for xiongcc
#################################################
Switch wal to /home/pgdata/pg_wal//00000001000000020000007F on time 2024-05-31 11:38:57.042163+08
[WARNING][filter_in_decode]Can not find relfilenode 16421 in dic
[XID]=16964, [TOPXID]=0
[SQLNO]=1
[SQL]=INSERT INTO public.t1(id ,info) VALUES(1 ,'98cff85633a3ebee6f41e836d3cb1db9')
[UNDO]=DELETE FROM public.t1 WHERE id=1 AND info='98cff85633a3ebee6f41e836d3cb1db9'
[database]=postgres
[COMPLETE]=true
[LSN]=2/7f359690
[COMMITLSN]=2/7f359a50
[COMMITTIME]=2024-05-31 11:37:25.748464+08
...
...

默认是输出至标准输出，也可以选择输出至表中

postgres=# select sqlkind,count(*) from walminer_contents group by 1;
 sqlkind | count 
---------+-------
         |     2
 INSERT  |    10
 UPDATE  |     4
(3 rows)postgres=# select * from walminer_contents limit 1;
-[ RECORD 1 ]-----------------------------------------------------------------------------
sqlno      | 1
xid        | 16964
topxid     | 0
database   | postgres
sqlkind    | INSERT
minerd     | t
timestamp  | 2024-05-31 11:37:25.748464+08
op_text    | INSERT INTO public.t1(id ,info) VALUES(1 ,'98cff85633a3ebee6f41e836d3cb1db9')
undo_text  | DELETE FROM public.t1 WHERE id=1 AND info='98cff85633a3ebee6f41e836d3cb1db9'
complete   | t
relation   | t1
start_lsn  | 2/7F359690
commit_lsn | 2/7F359A50

解析正确，不过这里不清楚为啥sqlkind没有显示DELETE。

基于此，我们就可以做一些其他功能了，比如日常运维过程中，我们经常会遇到WAL突然暴涨的CASE，那么如何知晓是什么表导致的？哪些表是热表，又是哪些行为产生了大量的WAL？这些都可以基于walminer来实现。

[postgres@sdw20 ~]$ walminer wal2sql -w /home/pgdata/pg_wal/ -D ./walminer.dic -k 3 -s '2024-05-31 12:10:08' -e '2024-05-31 12:10:43' -t 3 -p 5435 
#################################################
Walminer for PostgreSQL wal
Contact Author by mail '[email protected]'
Vip License for xiongcc
#################################################
Switch wal to /home/pgdata/pg_wal//000000010000000200000080 on time 2024-05-31 12:13:35.057624+08
Get start lsn 2/80000060 for time range
Switch wal to /home/pgdata/pg_wal//000000010000000200000080 on time 2024-05-31 12:13:35.0945+08postgres=# select relation,sqlkind,count(*) from walminer_contents group by 1,2;
 relation | sqlkind | count  
----------+---------+--------
 t2       | INSERT  | 164553
(1 row)

解析指定时间段的WAL，搭配如下SQL，即可分析是哪个表产生了大量的日志 (以及PostgreSQL原生的pg_stat_wal视图)。

with tmp_file as (
  select t1.file,
      t1.file_ls,
      (pg_stat_file(t1.file)).size as size,
      (pg_stat_file(t1.file)).access as access,
      (pg_stat_file(t1.file)).modification as last_update_time,
      (pg_stat_file(t1.file)).change as change,
      (pg_stat_file(t1.file)).creation as creation,
      (pg_stat_file(t1.file)).isdir as isdir
   from (select dir||'/'||pg_ls_dir(t0.dir) as file,
          pg_ls_dir(t0.dir) as file_ls
       from ( select '/home/pgdata/pg_wal'::text as dir
           --需要修改这个物理路径
           --select '/mnt/nas_dbbackup/archivelog'::text as dir
           --select setting as dir from pg_settings where name='log_directory'
          ) t0
      ) t1 
   where 1=1
   order by (pg_stat_file(file)).modification desc
) 
select to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') as day_id,
    sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <24 then 1 else 0 end) as wal_num_all,
    sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <1 then 1 else 0 end) as wal_num_00_01,
    sum(case when date_part('hour',tf0.last_update_time) >=1 and date_part('hour',tf0.last_update_time) <2 then 1 else 0 end) as wal_num_01_02,
    sum(case when date_part('hour',tf0.last_update_time) >=2 and date_part('hour',tf0.last_update_time) <3 then 1 else 0 end) as wal_num_02_03,
    sum(case when date_part('hour',tf0.last_update_time) >=3 and date_part('hour',tf0.last_update_time) <4 then 1 else 0 end) as wal_num_03_04,
    sum(case when date_part('hour',tf0.last_update_time) >=4 and date_part('hour',tf0.last_update_time) <5 then 1 else 0 end) as wal_num_04_05,
    sum(case when date_part('hour',tf0.last_update_time) >=5 and date_part('hour',tf0.last_update_time) <6 then 1 else 0 end) as wal_num_05_06,
    sum(case when date_part('hour',tf0.last_update_time) >=6 and date_part('hour',tf0.last_update_time) <7 then 1 else 0 end) as wal_num_06_07,
    sum(case when date_part('hour',tf0.last_update_time) >=7 and date_part('hour',tf0.last_update_time) <8 then 1 else 0 end) as wal_num_07_08,
    sum(case when date_part('hour',tf0.last_update_time) >=8 and date_part('hour',tf0.last_update_time) <9 then 1 else 0 end) as wal_num_08_09,
    sum(case when date_part('hour',tf0.last_update_time) >=9 and date_part('hour',tf0.last_update_time) <10 then 1 else 0 end) as wal_num_09_10,
    sum(case when date_part('hour',tf0.last_update_time) >=10 and date_part('hour',tf0.last_update_time) <11 then 1 else 0 end) as wal_num_10_11,
    sum(case when date_part('hour',tf0.last_update_time) >=11 and date_part('hour',tf0.last_update_time) <12 then 1 else 0 end) as wal_num_11_12,
    sum(case when date_part('hour',tf0.last_update_time) >=12 and date_part('hour',tf0.last_update_time) <13 then 1 else 0 end) as wal_num_12_13,
    sum(case when date_part('hour',tf0.last_update_time) >=13 and date_part('hour',tf0.last_update_time) <14 then 1 else 0 end) as wal_num_13_14,
    sum(case when date_part('hour',tf0.last_update_time) >=14 and date_part('hour',tf0.last_update_time) <15 then 1 else 0 end) as wal_num_14_15,
    sum(case when date_part('hour',tf0.last_update_time) >=15 and date_part('hour',tf0.last_update_time) <16 then 1 else 0 end) as wal_num_15_16,
    sum(case when date_part('hour',tf0.last_update_time) >=16 and date_part('hour',tf0.last_update_time) <17 then 1 else 0 end) as wal_num_16_17,
    sum(case when date_part('hour',tf0.last_update_time) >=17 and date_part('hour',tf0.last_update_time) <18 then 1 else 0 end) as wal_num_17_18,
    sum(case when date_part('hour',tf0.last_update_time) >=18 and date_part('hour',tf0.last_update_time) <19 then 1 else 0 end) as wal_num_18_19,
    sum(case when date_part('hour',tf0.last_update_time) >=19 and date_part('hour',tf0.last_update_time) <20 then 1 else 0 end) as wal_num_19_20,
    sum(case when date_part('hour',tf0.last_update_time) >=20 and date_part('hour',tf0.last_update_time) <21 then 1 else 0 end) as wal_num_20_21,
    sum(case when date_part('hour',tf0.last_update_time) >=21 and date_part('hour',tf0.last_update_time) <22 then 1 else 0 end) as wal_num_21_22,
    sum(case when date_part('hour',tf0.last_update_time) >=22 and date_part('hour',tf0.last_update_time) <23 then 1 else 0 end) as wal_num_22_23, 
    sum(case when date_part('hour',tf0.last_update_time) >=23 and date_part('hour',tf0.last_update_time) <24 then 1 else 0 end) as wal_num_23_24
from tmp_file tf0
where 1=1
 and tf0.file_ls not in ('archive_status')
group by to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd')
order by to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') desc
;

小试牛刀

让我们多造点测试数据，我们需要提前将保留的WAL设大一点，防止被回收，不然可能会提示

WALMINER_ERROR:Input walfiles can not cover startlsn xxx

postgres=# select pg_switch_wal();    
 pg_switch_wal 
---------------
 7/96A360B0
(1 row)postgres=# create table tbig(id int);
CREATE TABLE
postgres=# select 'tbig'::regclass::oid;
  oid  
-------
 16501
(1 row)
[postgres@sdw20 pg_wal]$ pg_waldump 000000010000000A00000020 | more
rmgr: Storage     len (rec/tot):     42/    42, tx:          0, lsn: A/20000028, prev A/1F2FA6B8, desc: CREATE base/13593/1
6501

起始LSN是A/20000028

postgres=# insert into tbig select n from generate_series(1,10000000) as n;
INSERT 0 10000000
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 A/4643ED80
(1 row)

结束LSN是A/4643ED80，解析一下

[postgres@sdw20 ~]$ walminer wal2sql -w /home/pgdata/pg_wal/ -D ./walminer.dic -k 2 -s A/20000028 -e A/4643ED80 -t 3 -p 5435
#################################################
Walminer for PostgreSQL wal
Contact Author by mail '[email protected]'
Vip License for xiongcc
#################################################
Switch wal to /home/pgdata/pg_wal//000000010000000A00000020 on time 2024-05-31 12:39:22.225508+08
...Switch wal to /home/pgdata/pg_wal//000000010000000A00000045 on time 2024-05-31 12:41:35.13447+08
Switch wal to /home/pgdata/pg_wal//000000010000000A00000046 on time 2024-05-31 12:41:38.692849+08
...

解析成功，表中也是1KW数据。

postgres=# select count(*) from walminer_contents ;
  count   
----------
 10000000
(1 row)postgres=# select * from walminer_contents order by sqlno limit 2;
-[ RECORD 1 ]-------------------------------------
sqlno      | 1
xid        | 202704
topxid     | 0
database   | postgres
sqlkind    | INSERT
minerd     | t
timestamp  | 2024-05-31 12:37:46.522569+08
op_text    | INSERT INTO public.tbig(id) VALUES(1)
undo_text  | DELETE FROM public.tbig WHERE id=1
complete   | t
relation   | tbig
start_lsn  | A/20018F20
commit_lsn | A/4643ED20
-[ RECORD 2 ]-------------------------------------
sqlno      | 2
xid        | 202704
topxid     | 0
database   | postgres
sqlkind    | INSERT
minerd     | t
timestamp  | 2024-05-31 12:37:46.522569+08
op_text    | INSERT INTO public.tbig(id) VALUES(2)
undo_text  | DELETE FROM public.tbig WHERE id=2
complete   | t
relation   | tbig
start_lsn  | A/20018F60
commit_lsn | A/4643ED20

DDL解析

DDL的解析据作者介绍，目前已经支持如下功能：

CREATE TABLE
DROP TABLE
TRUNCATE TABLE
RENAME TABLE
ALTER TABLE...ADD COLUMN
ALTER TABLE...DROP COLUMN
ALTER TABLE...ALTER COLUMN TYPE
ALTER TABLE...RENAME COLUMN

大多数表级DDL均已支持。另外，当前版本还仅支持8KB的WAL BLOCK SIZE，作者在后续版本会进行完善。

小结

感谢作者提供这么好用的工具，弥补了这块生态的空白。作者已持续几年维护walminer之前的版本，开源创作不易，也希望各位读者能够多多支持作者，👉🏻 https://gitee.com/movead/XLogMiner/wikis/walminer%20license

前言

功能一览

wal2sql

小试牛刀

DDL解析

小结

来自同一个声音

PGSimCity - PostgreSQL 工作原理可视化解析

从 Lakebase 到 LTAP，是旧饭新炒，还是新的技术范式？

AI 时代首选数据库：PostgreSQL 入门到进阶实战

pg-slide-harvester：自动收集 PG 大会演讲资料