PostgreSQL 扩展之 WalMiner

1月18日，北京，聊聊2025如何加入技术开发？

WalMiner是从PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持，3.0支持PostgreSQL10及其以上版本（pg14.2版本暂不支持，已与作者沟通）。

WalMiner可以从waL日志中解析出SQL，包括DML和少量DDL，与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。

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

PostgreSQL版本：13.6
WalMiner 版本：3.0

安装部署

获取软件

  
  
  
   
   
   --下载(由于3.0暂时没有release，直接下载master包即可)
   
   
   wget https://gitee.com/movead/XLogMiner/repository/archive/master.zip
   
   
   
   
   
   --将WalMiner放到数据库源码的contrib目录下,并给777权限
   
   
   # pwd
   
   
   /opt/software/postgresql-13.6/contrib
   
   
   
   
   
   # ll |grep -i WalMiner
   
   
   drwxrwxrwx 8  777 root 4096 Mar 15 14:31 WalMiner

编译安装

  
  
  
   
   
   --编译
   
   
   # make && make install
   
   
   
   
   
   --安装
   
   
   postgres=# \dx
   
   
                                               List of installed extensions
   
   
           Name        | Version |   Schema   |                              Description
   
   
   --------------------+---------+------------+------------------------------------------------------------------------
   
   
    pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
   
   
    plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
   
   
   (2 rows)
   
   
   
   
   
   postgres=# select * from pg_available_extensions() where name='walminer';
   
   
      name   | default_version |      comment
   
   
   ----------+-----------------+--------------------
   
   
    walminer | 3.0             | analyse wal to SQL
   
   
   (1 row)
   
   
   
   
   
   postgres=# create extension walminer;
   
   
   CREATE EXTENSION
   
   
   postgres=# \dx
   
   
                                               List of installed extensions
   
   
           Name        | Version |   Schema   |                              Description
   
   
   --------------------+---------+------------+------------------------------------------------------------------------
   
   
    pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
   
   
    plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
   
   
    walminer           | 3.0     | public     | analyse wal to SQL
   
   
   (3 rows)
   
   
   
   
   
   postgres=# \df walminer*
   
   
                                                                                      List of functions
   
   
    Schema |            Name            | Result data type |                                                  Argument data types                                                  | Type
   
   
   --------+----------------------------+------------------+-----------------------------------------------------------------------------------------------------------------------+------
   
   
    public | walminer_all               | text             | reloid oid DEFAULT 0, tempresult boolean DEFAULT false                                                                | func
   
   
    public | walminer_apply             | text             | startlsn pg_lsn, endlsn pg_lsn, fsearch boolean DEFAULT true, reloid oid DEFAULT 0, tempresult boolean DEFAULT false  | func
   
   
    public | walminer_build_dictionary  | cstring          | path cstring                                                                                                          | func
   
   
    public | walminer_by_lsn            | text             | startlsn pg_lsn, endlsn pg_lsn, fsearch boolean DEFAULT false, reloid oid DEFAULT 0, tempresult boolean DEFAULT false | func
   
   
    public | walminer_by_time           | text             | starttime text, endtime text, fsearch boolean DEFAULT false, reloid oid DEFAULT 0, tempresult boolean DEFAULT false   | func
   
   
    public | walminer_by_xid            | text             | xid integer, fsearch boolean DEFAULT false, reloid oid DEFAULT 0, tempresult boolean DEFAULT false                    | func
   
   
    public | walminer_contents_check    | void             |                                                                                                                       | func
   
   
    public | walminer_debug_mode        | boolean          |                                                                                                                       | func
   
   
    public | walminer_help              | cstring          |                                                                                                                       | func
   
   
    public | walminer_load_dictionary   | cstring          | path cstring                                                                                                          | func
   
   
    public | walminer_mrecords_inmemory | integer          | records integer                                                                                                       | func
   
   
    public | walminer_regression_mode   | boolean          |                                                                                                                       | func
   
   
    public | walminer_stop              | cstring          |                                                                                                                       | func
   
   
    public | walminer_table_avatar      | cstring          | tablename cstring, relfilenode oid                                                                                    | func
   
   
    public | walminer_version           | cstring          |                                                                                                                       | func
   
   
    public | walminer_wal_add           | cstring          | path cstring                                                                                                          | func
   
   
    public | walminer_wal_list          | SETOF record     |                                                                                                                       | func
   
   
    public | walminer_wal_remove        | cstring          | path cstring                                                                                                          | func
   
   
   (18 rows)
   
   
   
   
   
   postgres=# \df *wal2*
   
   
                                                                                     List of functions
   
   
    Schema |         Name         | Result data type |                                                    Argument data types                                                     | Type
   
   
   --------+----------------------+------------------+----------------------------------------------------------------------------------------------------------------------------+------
   
   
    public | wal2sql              | text             | reloid oid DEFAULT 0, tempresult boolean DEFAULT false                                                                     | func
   
   
    public | wal2sql              | text             | startlsn pg_lsn, endlsn pg_lsn, fsearch boolean DEFAULT false, reloid oid DEFAULT 0, tempresult boolean DEFAULT false      | func
   
   
    public | wal2sql              | text             | starttime text, endtime text, fsearch boolean DEFAULT false, reloid oid DEFAULT 0, tempresult boolean DEFAULT false        | func
   
   
    public | wal2sql              | text             | xid integer, fsearch boolean DEFAULT false, reloid oid DEFAULT 0, tempresult boolean DEFAULT false                         | func
   
   
    public | wal2sql_internal     | text             | starttime text, endtime text, startlsn pg_lsn, endlsn pg_lsn, xid integer, fsearch boolean, reloid oid, tempresult boolean | func
   
   
    public | wal2sql_self_apply   | integer          |                                                                                                                            | func
   
   
    public | wal2sql_with_catalog | boolean          |                                                                                                                            | func
   
   
    public | wal2sql_with_ddl     | boolean          |                                                                                                                            | func
   
   
   (8 rows)

测试场景

ps: 作者的码云中各测试场景都有非常详细的示例，大赞

  
  
  
   
   
   --测试表准备
   
   
   
   
   
   postgres=# show full_page_writes ;
   
   
    full_page_writes
   
   
   ------------------
   
   
    on
   
   
   (1 row)
   
   
   
   
   
   postgres=# create table walminer_t(id int,col1 varchar(8));
   
   
   CREATE TABLE
   
   
   postgres=# insert into walminer_t values(1,'w1'),(2,'w2');
   
   
   INSERT 0 2
   
   
   postgres=# select * from walminer_t ;
   
   
    id | col1
   
   
   ----+------
   
   
     1 | w1
   
   
     2 | w2
   
   
   (2 rows)
   
   
   
   
   
   postgres=# select pg_current_wal_lsn();
   
   
    pg_current_wal_lsn
   
   
   --------------------
   
   
    0/166EFD8
   
   
   (1 row)
   
   
   
   
   
   postgres=# select pg_walfile_name(pg_current_wal_lsn());
   
   
        pg_walfile_name
   
   
   --------------------------
   
   
    000000010000000000000001
   
   
   (1 row)

添加需要解析的wal文件

我们可以添加wal目录，也可以添加指定的wal文件（默认情况下此步骤可以省略，因为会直接加载当前数据字典和当前wal路径下的所有wal文件,但不建议省略）。

  
  
  
   
   
   postgres=# select walminer_wal_add('/data/pgdata13/pg_wal');
   
   
     walminer_wal_add
   
   
   --------------------
   
   
    1 file add success
   
   
   (1 row)
   
   
   
   
   
   postgres=# select walminer_wal_list();
   
   
                   walminer_wal_list
   
   
   --------------------------------------------------
   
   
    (/data/pgdata13/pg_wal/000000010000000000000001)
   
   
   (1 row)

生成及加载数据字典

此步骤常用于异地实例解析wal文件的场景，需要源端数据库与目标数据库的版本相同，源端数据库需要添加walminer扩展后再生成数据字典，将数据字典传输到目标端数据库进行导入。

  
  
  
   
   
   --源端数据库
   
   
   create extension walminer;
   
   
   select walminer_build_dictionary('/tmp/store_dictionary');
   
   
   
   
   
   --目标端数据库
   
   
   create extension walminer;
   
   
   select walminer_load_dictionary('/tmp/store_dictionary');

开始解析wal文件

wal文件被解析之后，会被删除。

  
  
  
   
   
   postgres=# select walminer_all();
   
   
   NOTICE:  Switch wal to 000000010000000000000001 on time 2022-03-17 23:46:01.345277+08
   
   
       walminer_all
   
   
   ---------------------
   
   
    pg_minerwal success
   
   
   (1 row)
   
   
   
   
   
   postgres=# select walminer_wal_list();
   
   
   ERROR:  wal list has not been loaded or has been removed.
   
   
   postgres=#

查看解析结果

walminer_contents是walminer自动生成的unlogged表，用来存储解析结果，在一次解析开始会先create或truncate此表，在表中我们看到这两条数据的xid、执行时间、执行操作、开始/结束lsn，甚至undo sql文本都写好了，很强大。

  
  
  
   
   
   postgres=# select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                         op_text                         |                       undo_text                        | complete | schema |  relation  | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+---------------------------------------------------------+--------------------------------------------------------+----------+--------+------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(1 ,'w1') | DELETE FROM public.walminer_t WHERE id=1 AND col1='w1' | t        | public | walminer_t | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(2 ,'w2') | DELETE FROM public.walminer_t WHERE id=2 AND col1='w2' | t        | public | walminer_t | 0/166EE50 | 0/166EEB8
   
   
   (2 rows)

范围解析

生产上wal的数量可能非常多，解析全部wal文件可能不现实，walminer支持范围解析,这里做一下简单的展示。

基于时间范围解析

  
  
  
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                          op_text                          |                        undo_text                         | complete | schema |  relation   | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+-----------------------------------------------------------+----------------------------------------------------------+----------+--------+-------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(1 ,'w1')   | DELETE FROM public.walminer_t WHERE id=1 AND col1='w1'   | t        | public | walminer_t  | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(2 ,'w2')   | DELETE FROM public.walminer_t WHERE id=2 AND col1='w2'   | t        | public | walminer_t  | 0/166EE50 | 0/166EEB8
   
   
        1 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(1 ,'ww1') | DELETE FROM public.walminer_t2 WHERE id=1 AND col1='ww1' | t        | public | walminer_t2 | 0/16A0828 | 0/16A08D0
   
   
        2 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(2 ,'ww2') | DELETE FROM public.walminer_t2 WHERE id=2 AND col1='ww2' | t        | public | walminer_t2 | 0/16A0868 | 0/16A08D0
   
   
   (4 rows)
   
   
   
   
   
   postgres=# select walminer_by_time('2022-03-17 23:39:00','2022-03-17 23:40:00');
   
   
   NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
   
   
   NOTICE:  Switch wal to 000000010000000000000001 on time 2022-03-18 00:46:44.177991+08
   
   
     walminer_by_time
   
   
   ---------------------
   
   
    pg_minerwal success
   
   
   (1 row)
   
   
   
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                         op_text                         |                       undo_text                        | complete | schema |  relation  | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+---------------------------------------------------------+--------------------------------------------------------+----------+--------+------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(1 ,'w1') | DELETE FROM public.walminer_t WHERE id=1 AND col1='w1' | t        | public | walminer_t | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(2 ,'w2') | DELETE FROM public.walminer_t WHERE id=2 AND col1='w2' | t        | public | walminer_t | 0/166EE50 | 0/166EEB8
   
   
   (2 rows)

基于lsn范围解析

  
  
  
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                          op_text                          |                        undo_text                         | complete | schema |  relation   | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+-----------------------------------------------------------+----------------------------------------------------------+----------+--------+-------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(1 ,'w1')   | DELETE FROM public.walminer_t WHERE id=1 AND col1='w1'   | t        | public | walminer_t  | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(2 ,'w2')   | DELETE FROM public.walminer_t WHERE id=2 AND col1='w2'   | t        | public | walminer_t  | 0/166EE50 | 0/166EEB8
   
   
        1 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(1 ,'ww1') | DELETE FROM public.walminer_t2 WHERE id=1 AND col1='ww1' | t        | public | walminer_t2 | 0/16A0828 | 0/16A08D0
   
   
        2 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(2 ,'ww2') | DELETE FROM public.walminer_t2 WHERE id=2 AND col1='ww2' | t        | public | walminer_t2 | 0/16A0868 | 0/16A08D0
   
   
   (4 rows)
   
   
   
   
   
   postgres=# select walminer_by_lsn('0/16A0828','0/16A08D0');
   
   
   NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
   
   
   NOTICE:  Switch wal to 000000010000000000000001 on time 2022-03-18 00:50:12.706725+08
   
   
      walminer_by_lsn
   
   
   ---------------------
   
   
    pg_minerwal success
   
   
   (1 row)
   
   
   
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                          op_text                          |                        undo_text                         | complete | schema |  relation   | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+-----------------------------------------------------------+----------------------------------------------------------+----------+--------+-------------+-----------+------------
   
   
        1 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(1 ,'ww1') | DELETE FROM public.walminer_t2 WHERE id=1 AND col1='ww1' | t        | public | walminer_t2 | 0/16A0828 | 0/16A08D0
   
   
        2 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(2 ,'ww2') | DELETE FROM public.walminer_t2 WHERE id=2 AND col1='ww2' | t        | public | walminer_t2 | 0/16A0868 | 0/16A08D0
   
   
   (2 rows)
   
   
   
   
   
   postgres=#

基于指定xid解析

  
  
  
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                          op_text                          |                        undo_text                         | complete | schema |  relation   | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+-----------------------------------------------------------+----------------------------------------------------------+----------+--------+-------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(1 ,'w1')   | DELETE FROM public.walminer_t WHERE id=1 AND col1='w1'   | t        | public | walminer_t  | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(2 ,'w2')   | DELETE FROM public.walminer_t WHERE id=2 AND col1='w2'   | t        | public | walminer_t  | 0/166EE50 | 0/166EEB8
   
   
        1 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(1 ,'ww1') | DELETE FROM public.walminer_t2 WHERE id=1 AND col1='ww1' | t        | public | walminer_t2 | 0/16A0828 | 0/16A08D0
   
   
        2 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(2 ,'ww2') | DELETE FROM public.walminer_t2 WHERE id=2 AND col1='ww2' | t        | public | walminer_t2 | 0/16A0868 | 0/16A08D0
   
   
   (4 rows)
   
   
   
   
   
   postgres=# select walminer_by_xid('499');
   
   
   NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
   
   
   NOTICE:  Switch wal to 000000010000000000000001 on time 2022-03-18 00:52:29.379106+08
   
   
      walminer_by_xid
   
   
   ---------------------
   
   
    pg_minerwal success
   
   
   (1 row)
   
   
   
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                         op_text                         |                       undo_text                        | complete | schema |  relation  | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+---------------------------------------------------------+--------------------------------------------------------+----------+--------+------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(1 ,'w1') | DELETE FROM public.walminer_t WHERE id=1 AND col1='w1' | t        | public | walminer_t | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(2 ,'w2') | DELETE FROM public.walminer_t WHERE id=2 AND col1='w2' | t        | public | walminer_t | 0/166EE50 | 0/166EEB8
   
   
   (2 rows)

范围精确解析

在范围解析的基础上，更加解析的信息更加精确（暂未看出普通解析与精确解析的区别）

  
  
  
   
   
   select walminer_by_time(starttime, endtime,'true');
   
   
   select walminer_by_lsn(startlsn, endlsn,'true');
   
   
   select walminer_by_xid(xid,'true');
   
   
   
   
   
   或
   
   
   select wal2sql(starttime, endtime,'true');
   
   
   select wal2sql(startlsn, endlsn,'true');
   
   
   select wal2sql(xid,'true');

单表解析

通过指定reloid来解析指定的表

  
  
  
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                          op_text                          |                        undo_text                         | complete | schema |  relation   | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+-----------------------------------------------------------+----------------------------------------------------------+----------+--------+-------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(1 ,'w1')   | DELETE FROM public.walminer_t WHERE id=1 AND col1='w1'   | t        | public | walminer_t  | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(2 ,'w2')   | DELETE FROM public.walminer_t WHERE id=2 AND col1='w2'   | t        | public | walminer_t  | 0/166EE50 | 0/166EEB8
   
   
        1 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(1 ,'ww1') | DELETE FROM public.walminer_t2 WHERE id=1 AND col1='ww1' | t        | public | walminer_t2 | 0/16A0828 | 0/16A08D0
   
   
        2 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(2 ,'ww2') | DELETE FROM public.walminer_t2 WHERE id=2 AND col1='ww2' | t        | public | walminer_t2 | 0/16A0868 | 0/16A08D0
   
   
   (4 rows)
   
   
   
   
   
   postgres=# select oid,relname from pg_class where relname='walminer_t';
   
   
     oid  |  relname
   
   
   -------+------------
   
   
    16497 | walminer_t
   
   
   (1 row)
   
   
   
   
   
   postgres=# select walminer_by_time('2022-03-17 23:39:00','2022-03-18 01:40:00','true','16497');
   
   
   NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
   
   
   NOTICE:  Switch wal to 000000010000000000000001 on time 2022-03-18 01:01:57.94789+08
   
   
     walminer_by_time
   
   
   ---------------------
   
   
    pg_minerwal success
   
   
   (1 row)
   
   
   
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                         op_text                         |                       undo_text                        | complete | schema |  relation  | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+---------------------------------------------------------+--------------------------------------------------------+----------+--------+------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(1 ,'w1') | DELETE FROM public.walminer_t WHERE id=1 AND col1='w1' | t        | public | walminer_t | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(2 ,'w2') | DELETE FROM public.walminer_t WHERE id=2 AND col1='w2' | t        | public | walminer_t | 0/166EE50 | 0/166EEB8
   
   
   (2 rows)

替身解析

当我们执行drop 或者 truncate 操作后，数据库的数据字典中的relfilenode可能已经被移除，无法解析出原来wal中的一些内容，在知道旧表表结构的基础上，我们可以使用替身的解析方法。

  
  
  
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                          op_text                          |                        undo_text                         | complete | schema |  relation   | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+-----------------------------------------------------------+----------------------------------------------------------+----------+--------+-------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(1 ,'w1')   | DELETE FROM public.walminer_t WHERE id=1 AND col1='w1'   | t        | public | walminer_t  | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_t(id ,col1) VALUES(2 ,'w2')   | DELETE FROM public.walminer_t WHERE id=2 AND col1='w2'   | t        | public | walminer_t  | 0/166EE50 | 0/166EEB8
   
   
        1 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(1 ,'ww1') | DELETE FROM public.walminer_t2 WHERE id=1 AND col1='ww1' | t        | public | walminer_t2 | 0/16A0828 | 0/16A08D0
   
   
        2 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(2 ,'ww2') | DELETE FROM public.walminer_t2 WHERE id=2 AND col1='ww2' | t        | public | walminer_t2 | 0/16A0868 | 0/16A08D0
   
   
   (4 rows)
   
   
   
   
   
   postgres=# select pg_relation_filenode('walminer_t'::regclass);
   
   
    pg_relation_filenode
   
   
   ----------------------
   
   
                   16497
   
   
   (1 row)
   
   
   
   
   
   postgres=# drop table walminer_t;
   
   
   DROP TABLE
   
   
   postgres=# create table walminer_n(id int,col1 varchar(8));
   
   
   CREATE TABLE
   
   
   postgres=# select walminer_table_avatar('walminer_n', 16497);
   
   
       walminer_table_avatar
   
   
   ------------------------------
   
   
    MAP[walminer_n:16560]->16497
   
   
   (1 row)
   
   
   
   
   
   postgres=# select walminer_all();
   
   
   NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
   
   
   NOTICE:  Switch wal to 000000010000000000000001 on time 2022-03-18 01:11:32.466401+08
   
   
       walminer_all
   
   
   ---------------------
   
   
    pg_minerwal success
   
   
   (1 row)
   
   
   
   
   
   postgres=#  select * from walminer_contents ;
   
   
    sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                          op_text                          |                        undo_text                         | complete | schema |  relation   | start_lsn | commit_lsn
   
   
   -------+-----+--------+---------+--------+-------------------------------+-----------------------------------------------------------+----------------------------------------------------------+----------+--------+-------------+-----------+------------
   
   
        1 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_n(id ,col1) VALUES(1 ,'w1')   | DELETE FROM public.walminer_n WHERE id=1 AND col1='w1'   | t        | public | walminer_n  | 0/166EE10 | 0/166EEB8
   
   
        2 | 499 |      0 |       1 | t      | 2022-03-17 23:39:08.891413+08 | INSERT INTO public.walminer_n(id ,col1) VALUES(2 ,'w2')   | DELETE FROM public.walminer_n WHERE id=2 AND col1='w2'   | t        | public | walminer_n  | 0/166EE50 | 0/166EEB8
   
   
        1 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(1 ,'ww1') | DELETE FROM public.walminer_t2 WHERE id=1 AND col1='ww1' | t        | public | walminer_t2 | 0/16A0828 | 0/16A08D0
   
   
        2 | 506 |      0 |       1 | t      | 2022-03-18 00:43:31.689753+08 | INSERT INTO public.walminer_t2(id ,col1) VALUES(2 ,'ww2') | DELETE FROM public.walminer_t2 WHERE id=2 AND col1='ww2' | t        | public | walminer_t2 | 0/16A0868 | 0/16A08D0
   
   
   (4 rows)

删除wal文件

只能删除wal文件，不能像添加一样直接删除整个目录。

  
  
  
   
   
   postgres=# select walminer_wal_list();
   
   
                   walminer_wal_list
   
   
   --------------------------------------------------
   
   
    (/data/pgdata13/pg_wal/000000010000000000000001)
   
   
   (1 row)
   
   
   
   
   
   postgres=# select walminer_wal_remove('/data/pgdata13/pg_wal/');
   
   
   ERROR:  Argument can be file only, an not be a directory
   
   
   postgres=# select walminer_wal_remove('/data/pgdata13/pg_wal/000000010000000000000001');
   
   
     walminer_wal_remove
   
   
   -----------------------
   
   
    1 file remove success
   
   
   (1 row)
   
   
   
   
   
   postgres=# select walminer_wal_list();
   
   
   ERROR:  wal list has not been loaded or has been removed.
   
   
   postgres=#

结束walminer

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

  
  
  
   
   
   postgres=# select walminer_stop();
   
   
     walminer_stop
   
   
   ------------------
   
   
    walminer stoped!

开发中的功能

开发中的功能暂不在这里展示，可以直接看作者网站

自apply解析

适用于主备切换，但是源主库部分wal日志未同步到新主库的场景，此种方法可以直接将结果解析到指定的表中。

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

DDL解析

这里分两部分，分别是系统表变化解析和普通DDL解析。

  
  
  
   
   
   目前walminer支持解析系统表的变化。也就是说如果在PG执行了DDL语句，walminer可以分析出DDL语句引起的系统表的变化，系统表变化解析和DDL解析不共存，总是接受最新确定的状态。
   
   
   
   
   
   -- 在执行解析之前，先执行如下语句，即可开启系统表解析功能
   
   
   select wal2sql_with_catalog();
   
   
   DDL解析
   
   
   
   
   
   -- 在执行解析之前，先执行如下语句，即可开启DDL解析功能
   
   
   select wal2sql_with_ddl();

数据页挽回

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

  
  
  
   
   
   select page_collect(relfilenode, reloid, pages);
   
   
   
   
   
   relfilenode：需要解析的wal日志中的relfilenode
   
   
   reloid：解析库中存在的表的OID，此命令将会将从wal中找到的page覆盖到reloid制定的表中
   
   
   pages：是字符串类型，制定想要挽回的目标page。格式为'0,1,2,7'或者'all'。

结束语

这个插件现在的功能现在已经足够处理常规的误操作，达到闪回的效果，且此工具还在不断开发完善中，让我们一起期待一个更强大更完善的救火工具。

本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

安装部署

获取软件

编译安装

测试场景

添加需要解析的wal文件

生成及加载数据字典

开始解析wal文件

查看解析结果

范围解析

基于时间范围解析

基于lsn范围解析

基于指定xid解析

范围精确解析

单表解析

替身解析

删除wal文件

结束walminer

开发中的功能

自apply解析

DDL解析

数据页挽回

结束语

关于作者

作者的专辑

作者的其它热门文章

热门资讯

热门软件

OSCHINA 社区

在线工具

攻略

公众号

视频号

问题反馈

PostgreSQL 扩展之 WalMiner

安装部署

获取软件

编译安装

测试场景

添加需要解析的wal文件

生成及加载数据字典

开始解析wal文件

查看解析结果

范围解析

基于时间范围解析

基于lsn范围解析

基于指定xid解析

范围精确解析

单表解析

替身解析

删除wal文件

结束walminer

开发中的功能

自apply解析

DDL解析

数据页挽回

结束语

关于作者

作者的专辑

作者的其它热门文章

热门资讯

推荐关注

热门软件

OSCHINA 社区

在线工具

攻略

公众号

视频号

问题反馈