PostgreSQL插件——Walminer

南風_入弦

于 2024-08-07 08:37:45 发布

阅读量310

点赞数 3

分类专栏： PostgreSQL插件文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/qq_29431123/article/details/140971545

版权

PostgreSQL插件专栏收录该内容

7 篇文章 0 订阅

订阅专栏

WalMiner是从PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持。WalMiner可以从waL日志中解析出SQL，包括DML和少量DDL，与传统的logical decode插件相比，walminer不要求logical日志级别且解析方式较为灵活。当数据库被执行了TRUNCATE等不被wal记录的数据清除操作，或者发生磁盘页损坏，可以使用此功能从wal日志中搜索数据，以期尽量挽回数据，达到数据页挽回的效果。

一、安装

1.下载地址

https://gitee.com/movead/XLogMiner/repository/archive/master.zip

2.解压

[pg14@node1 pgsoft]$ unzip XLogMiner-master.zip 
Archive:  XLogMiner-master.zip
f9b032100b9e84ebf35c8d7f40c6f5bf8177e328
   creating: XLogMiner-master/
 extracting: XLogMiner-master/.gitignore  
  inflating: XLogMiner-master/LICENSE  
 extracting: XLogMiner-master/README.EN.MD  
  inflating: XLogMiner-master/README.md  
   creating: XLogMiner-master/walminer/
 extracting: XLogMiner-master/walminer/.gitignore  
  inflating: XLogMiner-master/walminer/Makefile  
  inflating: XLogMiner-master/walminer/datadictionary.c  
  inflating: XLogMiner-master/walminer/datadictionary.h  
  inflating: XLogMiner-master/walminer/debug_info.md  
   creating: XLogMiner-master/walminer/expected/
  inflating: XLogMiner-master/walminer/expected/pc_base.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_avatar.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_base.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_ddl.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_front_search.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_lsn.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_missimage.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_other.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_self_apply.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_singletable.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_spill.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_subxid.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_time.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_toast.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_type.out  
  inflating: XLogMiner-master/walminer/expected/wal2sql_xid.out  
  inflating: XLogMiner-master/walminer/fetchcatalogtable.c  
   creating: XLogMiner-master/walminer/image/
  inflating: XLogMiner-master/walminer/image/搜索线程的工作.jpg  
  inflating: XLogMiner-master/walminer/image/解析线程的工作.jpg  
  inflating: XLogMiner-master/walminer/imagemanage.c  
  inflating: XLogMiner-master/walminer/pagecollect.c  
  inflating: XLogMiner-master/walminer/pagecollect.h  
   creating: XLogMiner-master/walminer/sql/
  inflating: XLogMiner-master/walminer/sql/pc_base.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_avatar.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_base.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_ddl.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_front_search.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_lsn.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_missimage.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_other.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_self_apply.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_singletable.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_spill.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_subxid.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_time.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_toast.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_type.sql  
  inflating: XLogMiner-master/walminer/sql/wal2sql_xid.sql  
  inflating: XLogMiner-master/walminer/temp.h  
  inflating: XLogMiner-master/walminer/todo.md  
  inflating: XLogMiner-master/walminer/wal2sql.c  
  inflating: XLogMiner-master/walminer/wal2sql.h  
  inflating: XLogMiner-master/walminer/wal2sql_ddl.c  
  inflating: XLogMiner-master/walminer/wal2sql_ddl.h  
  inflating: XLogMiner-master/walminer/wal2sql_spi.c  
  inflating: XLogMiner-master/walminer/wal2sql_spi.h  
  inflating: XLogMiner-master/walminer/wallist.c  
  inflating: XLogMiner-master/walminer/walminer--1.0.sql  
  inflating: XLogMiner-master/walminer/walminer--2.0.sql  
  inflating: XLogMiner-master/walminer/walminer--3.0.sql  
  inflating: XLogMiner-master/walminer/walminer.c  
  inflating: XLogMiner-master/walminer/walminer.conf  
  inflating: XLogMiner-master/walminer/walminer.control  
  inflating: XLogMiner-master/walminer/walminer_12.conf  
  inflating: XLogMiner-master/walminer/walminer_contents.c  
  inflating: XLogMiner-master/walminer/walminer_contents.h  
  inflating: XLogMiner-master/walminer/walminer_decode.c  
  inflating: XLogMiner-master/walminer/walminer_decode.h  
  inflating: XLogMiner-master/walminer/walminer_thread.c  
  inflating: XLogMiner-master/walminer/walreader.c  
  inflating: XLogMiner-master/walminer/wm_utils.c  
  inflating: XLogMiner-master/walminer/wm_utils.h 
  
[pg14@node1 pgsoft]$ cd XLogMiner-master/
[pg14@node1 XLogMiner-master]$ ls
LICENSE  README.EN.MD  README.md  walminer
[pg14@node1 XLogMiner-master]$ cp -rp walminer /pgsoft/postgresql-14.6/contrib/

3.编译

[pg14@node1 walminer]$ make PG_CONFIG=/pgsoft/pg14/bin/pg_config
make -C ../../src/backend generated-headers
make[1]: Entering directory `/pgsoft/postgresql-14.6/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/pgsoft/postgresql-14.6/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/pgsoft/postgresql-14.6/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/pgsoft/postgresql-14.6/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/pgsoft/postgresql-14.6/src/backend/utils'
make[1]: Leaving directory `/pgsoft/postgresql-14.6/src/backend'
[pg14@node1 walminer]$ make install  PG_CONFIG=/pgsoft/pg14/bin/pg_config
make -C ../../src/backend generated-headers
make[1]: Entering directory `/pgsoft/postgresql-14.6/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/pgsoft/postgresql-14.6/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/pgsoft/postgresql-14.6/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/pgsoft/postgresql-14.6/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/pgsoft/postgresql-14.6/src/backend/utils'
make[1]: Leaving directory `/pgsoft/postgresql-14.6/src/backend'
/bin/mkdir -p '/pgsoft/pg14/lib/postgresql'
/bin/mkdir -p '/pgsoft/pg14/share/postgresql/extension'
/bin/mkdir -p '/pgsoft/pg14/share/postgresql/extension'
/bin/install -c -m 755  walminer.so '/pgsoft/pg14/lib/postgresql/walminer.so'
/bin/install -c -m 644 ./walminer.control '/pgsoft/pg14/share/postgresql/extension/'
/bin/install -c -m 644 ./walminer--3.0.sql  '/pgsoft/pg14/share/postgresql/extension/'
[pg14@node1 walminer]$

4.安装

test2=#  \dx
                                       List of installed extensions
     Name     | Version |   Schema   |                             Description                             
--------------+---------+------------+---------------------------------------------------------------------
 pageinspect  | 1.9     | public     | inspect the contents of database pages at a low level
 pg_dirtyread | 2       | public     | Read dead but unvacuumed rows from table
 pg_recovery  | 1.0     | public     | recovery table data of update/delete/rollback rows and drop columns
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)
test2=# select * from pg_available_extensions() where name='walminer';
   name   | default_version |      comment       
----------+-----------------+--------------------
 walminer | 3.0             | analyse wal to SQL
(1 row)

test2=# create extension walminer;
CREATE EXTENSION
test2=#  \dx
                                       List of installed extensions
     Name     | Version |   Schema   |                             Description                             
--------------+---------+------------+---------------------------------------------------------------------
 pageinspect  | 1.9     | public     | inspect the contents of database pages at a low level
 pg_dirtyread | 2       | public     | Read dead but unvacuumed rows from table
 pg_recovery  | 1.0     | public     | recovery table data of update/delete/rollback rows and drop columns
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 walminer     | 3.0     | public     | analyse wal to SQL
(5 rows)

test2=#

5.查看walminer

test2=# \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, te
mpresult 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, t
empresult boolean DEFAULT false | func
 public | walminer_by_time           | text             | starttime text, endtime text, fsearch boolean DEFAULT false, reloid oid DEFAULT 0, tem
presult boolean DEFAULT false   | func
 public | walminer_by_xid            | text             | xid integer, fsearch boolean DEFAULT false, reloid oid DEFAULT 0, tempresult boolean D
EFAULT 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)

test2=# \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, tempres
ult boolean DEFAULT false      | func
 public | wal2sql              | text             | starttime text, endtime text, fsearch boolean DEFAULT false, reloid oid DEFAULT 0, tempresul
t 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)

test2=# select walminer_version();

            walminer_version             
-----------------------------------------

 Walminer 3.0.0(dev) for PostgreSQL 14.6
(1 row)

二、测试场景

1.环境准备

test2=# show full_page_writes;

 full_page_writes 
------------------

 on
(1 row)

test2=# \d
         List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
 public | fb_dirty | table | test2
 public | t1       | table | test2
 public | t_insert | table | test2
(3 rows)

test2=# create table walminer_t(id int,name varchar(8));
CREATE TABLE
test2=# insert into walminer_t values(1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
INSERT 0 4
test2=# select * from walminer_t ;
 id | name 
----+------
  1 | aaa
  2 | bbb
  3 | ccc
  4 | ddd
(4 rows)

test2=# select pg_cu

test2=# select pg_current_wal_lsn();

 pg_current_wal_lsn 
--------------------

 0/1AA1398
(1 row)

test2=# select pg_walfile_name(pg_current_wal_lsn());

     pg_walfile_name      
--------------------------

 000000010000000000000001
(1 row)

2.添加需要解析的wal文件

可以添加wal目录，也可以添加指定的wal文件

test2=# select walminer_wal_add('/pgdata02/pg_wal');
  walminer_wal_add  
--------------------
 1 file add success
(1 row)

test2=# select walminer_wal_list();
              walminer_wal_list              
---------------------------------------------
 (/pgdata02/pg_wal/000000010000000000000001)
(1 row)

3.生成及加载数据字典

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

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

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

4.开始解析wal文件

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

test2=# select walminer_all();
NOTICE:  Switch wal to 000000010000000000000001 on time 2023-08-18 00:35:04.565513+08
    walminer_all     
---------------------
 pg_minerwal success
(1 row)

test2=# select walminer_wal_list();
ERROR:  wal list has not been loaded or has been removed.

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

-[ RECORD 24 ]-------------------------------------------------------------------------------------------------
sqlno      | 1
xid        | 773
topxid     | 0
sqlkind    | 1
minerd     | t
timestamp  | 2023-08-18 00:20:09.273406+08
op_text    | INSERT INTO public.walminer_t(id ,name) VALUES(1 ,'aaa')
undo_text  | DELETE FROM public.walminer_t WHERE id=1 AND name='aaa'
complete   | t
schema     | public
relation   | walminer_t
start_lsn  | 0/1AA1238
commit_lsn | 0/1AA1360
-[ RECORD 25 ]-------------------------------------------------------------------------------------------------
sqlno      | 2
xid        | 773
topxid     | 0
sqlkind    | 1
minerd     | t
timestamp  | 2023-08-18 00:20:09.273406+08
op_text    | INSERT INTO public.walminer_t(id ,name) VALUES(2 ,'bbb')
undo_text  | DELETE FROM public.walminer_t WHERE id=2 AND name='bbb'
complete   | t
schema     | public
relation   | walminer_t
start_lsn  | 0/1AA1278
commit_lsn | 0/1AA1360
-[ RECORD 26 ]-------------------------------------------------------------------------------------------------
sqlno      | 3
xid        | 773
topxid     | 0
sqlkind    | 1
minerd     | t
timestamp  | 2023-08-18 00:20:09.273406+08
op_text    | INSERT INTO public.walminer_t(id ,name) VALUES(3 ,'ccc')
undo_text  | DELETE FROM public.walminer_t WHERE id=3 AND name='ccc'
complete   | t
schema     | public
relation   | walminer_t
start_lsn  | 0/1AA12B8
commit_lsn | 0/1AA1360
-[ RECORD 27 ]-------------------------------------------------------------------------------------------------
sqlno      | 4
xid        | 773
topxid     | 0
sqlkind    | 1
minerd     | t
timestamp  | 2023-08-18 00:20:09.273406+08
op_text    | INSERT INTO public.walminer_t(id ,name) VALUES(4 ,'ddd')
undo_text  | DELETE FROM public.walminer_t WHERE id=4 AND name='ddd'
complete   | t
schema     | public
relation   | walminer_t
start_lsn  | 0/1AA12F8
commit_lsn | 0/1AA1360

5.范围解析

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

test2=#    select walminer_wal_add('/pgdata02/pg_wal');
-[ RECORD 1 ]----+-------------------
walminer_wal_add | 1 file add success

test2=# truncate walminer_contents;
TRUNCATE TABLE
test2=# select walminer_by_time('2023-08-18 00:20:00','2023-08-18 00:21:00');
NOTICE:  Switch wal to 000000010000000000000001 on time 2023-08-18 00:42:49.06835+08
-[ RECORD 1 ]----+--------------------
walminer_by_time | pg_minerwal success

test2=# select * from walminer_contents;
-[ RECORD 1 ]--------------------------------------------------------
sqlno      | 1
xid        | 773
topxid     | 0
sqlkind    | 1
minerd     | t
timestamp  | 2023-08-18 00:20:09.273406+08
op_text    | INSERT INTO public.walminer_t(id ,name) VALUES(1 ,'aaa')
undo_text  | DELETE FROM public.walminer_t WHERE id=1 AND name='aaa'
complete   | t
schema     | public
relation   | walminer_t
start_lsn  | 0/1AA1238
commit_lsn | 0/1AA1360
-[ RECORD 2 ]--------------------------------------------------------
sqlno      | 2
xid        | 773
topxid     | 0
sqlkind    | 1
minerd     | t
timestamp  | 2023-08-18 00:20:09.273406+08
op_text    | INSERT INTO public.walminer_t(id ,name) VALUES(2 ,'bbb')
undo_text  | DELETE FROM public.walminer_t WHERE id=2 AND name='bbb'
complete   | t
schema     | public
relation   | walminer_t
start_lsn  | 0/1AA1278
commit_lsn | 0/1AA1360
-[ RECORD 3 ]--------------------------------------------------------
sqlno      | 3
xid        | 773
topxid     | 0
sqlkind    | 1
minerd     | t
timestamp  | 2023-08-18 00:20:09.273406+08
op_text    | INSERT INTO public.walminer_t(id ,name) VALUES(3 ,'ccc')
undo_text  | DELETE FROM public.walminer_t WHERE id=3 AND name='ccc'
complete   | t
schema     | public
relation   | walminer_t
start_lsn  | 0/1AA12B8
commit_lsn | 0/1AA1360
-[ RECORD 4 ]--------------------------------------------------------
sqlno      | 4
xid        | 773
topxid     | 0
sqlkind    | 1
minerd     | t
timestamp  | 2023-08-18 00:20:09.273406+08
op_text    | INSERT INTO public.walminer_t(id ,name) VALUES(4 ,'ddd')
undo_text  | DELETE FROM public.walminer_t WHERE id=4 AND name='ddd'
complete   | t
schema     | public
relation   | walminer_t
start_lsn  | 0/1AA12F8
commit_lsn | 0/1AA1360

test2=#

6.基于lsn范围解析

test2=# select walminer_by_lsn('0/1AA1238','0/1AA1360');
NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
NOTICE:  Switch wal to 000000010000000000000001 on time 2023-08-18 00:51:44.326247+08

   walminer_by_lsn   
---------------------

 pg_minerwal success
(1 row)

test2=# select * from walminer_contents ;
 sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                         op_text                          |           
             undo_text                        | complete | schema |  relation  | start_lsn | commit_lsn 
-------+-----+--------+---------+--------+-------------------------------+----------------------------------------------------------+-----------
----------------------------------------------+----------+--------+------------+-----------+------------
     1 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(1 ,'aaa') | DELETE FRO
M public.walminer_t WHERE id=1 AND name='aaa' | t        | public | walminer_t | 0/1AA1238 | 0/1AA1360
     2 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(2 ,'bbb') | DELETE FRO
M public.walminer_t WHERE id=2 AND name='bbb' | t        | public | walminer_t | 0/1AA1278 | 0/1AA1360
     3 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(3 ,'ccc') | DELETE FRO
M public.walminer_t WHERE id=3 AND name='ccc' | t        | public | walminer_t | 0/1AA12B8 | 0/1AA1360
     4 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(4 ,'ddd') | DELETE FRO
M public.walminer_t WHERE id=4 AND name='ddd' | t        | public | walminer_t | 0/1AA12F8 | 0/1AA1360
(4 rows)

test2=#

7.基于指定xid解析

test2=# truncate  walminer_contents ;
TRUNCATE TABLE
test2=# select * from walminer_contents ;
 sqlno | xid | topxid | sqlkind | minerd | timestamp | op_text | undo_text | complete | schema | relation | start_lsn | commit_lsn 
-------+-----+--------+---------+--------+-----------+---------+-----------+----------+--------+----------+-----------+------------
(0 rows)

test2=#  select walminer_by_xid('773');
NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
NOTICE:  Switch wal to 000000010000000000000001 on time 2023-08-18 01:09:00.947759+08

   walminer_by_xid   
---------------------

 pg_minerwal success
(1 row)

test2=# select * from walminer_contents ;
 sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                         op_text                          |           
             undo_text                        | complete | schema |  relation  | start_lsn | commit_lsn 
-------+-----+--------+---------+--------+-------------------------------+----------------------------------------------------------+-----------
----------------------------------------------+----------+--------+------------+-----------+------------
     1 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(1 ,'aaa') | DELETE FRO
M public.walminer_t WHERE id=1 AND name='aaa' | t        | public | walminer_t | 0/1AA1238 | 0/1AA1360
     2 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(2 ,'bbb') | DELETE FRO
M public.walminer_t WHERE id=2 AND name='bbb' | t        | public | walminer_t | 0/1AA1278 | 0/1AA1360
     3 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(3 ,'ccc') | DELETE FRO
M public.walminer_t WHERE id=3 AND name='ccc' | t        | public | walminer_t | 0/1AA12B8 | 0/1AA1360
     4 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(4 ,'ddd') | DELETE FRO
M public.walminer_t WHERE id=4 AND name='ddd' | t        | public | walminer_t | 0/1AA12F8 | 0/1AA1360
(4 rows)

test2=#

8.范围精确解析

在范围解析的基础上，更加解析的信息更加精确

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

9.单表解析

test2=#  select walminer_by_time('2023-08-18 00:20:00','2023-08-18 00:21:00','true','16471');
NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
NOTICE:  Switch wal to 000000010000000000000001 on time 2023-08-18 01:28:40.227339+08

  walminer_by_time   
---------------------

 pg_minerwal success
(1 row)

test2=# select * from walminer_contents ;
 sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                         op_text                          |           
             undo_text                        | complete | schema |  relation  | start_lsn | commit_lsn 
-------+-----+--------+---------+--------+-------------------------------+----------------------------------------------------------+-----------
----------------------------------------------+----------+--------+------------+-----------+------------
     1 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(1 ,'aaa') | DELETE FRO
M public.walminer_t WHERE id=1 AND name='aaa' | t        | public | walminer_t | 0/1AA1238 | 0/1AA1360
     2 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(2 ,'bbb') | DELETE FRO
M public.walminer_t WHERE id=2 AND name='bbb' | t        | public | walminer_t | 0/1AA1278 | 0/1AA1360
     3 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(3 ,'ccc') | DELETE FRO
M public.walminer_t WHERE id=3 AND name='ccc' | t        | public | walminer_t | 0/1AA12B8 | 0/1AA1360
     4 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_t(id ,name) VALUES(4 ,'ddd') | DELETE FRO
M public.walminer_t WHERE id=4 AND name='ddd' | t        | public | walminer_t | 0/1AA12F8 | 0/1AA1360
(4 rows)

10.替身解析

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

test2=# select pg_relation_filenode('walminer_t'::regclass);

 pg_relation_filenode 
----------------------

                16471

(1 row)

test2=# drop table walminer_t;
DROP TABLE
test2=# create table walminer_n(id int,name varchar(8));
CREATE TABLE
test2=# select walminer_t

test2=# select walminer_table_avatar('walminer_n',16471);
    walminer_table_avatar     
------------------------------
 MAP[walminer_n:16503]->16471
(1 row)

test2=# 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 2023-08-18 01:33:49.239724+08
    walminer_all     
---------------------
 pg_minerwal success
(1 row)
test2=# select * from walminer_contents  where relation='walminer_n';
 sqlno | xid | topxid | sqlkind | minerd |           timestamp           |                         op_text                          |           
             undo_text                        | complete | schema |  relation  | start_lsn | commit_lsn 
-------+-----+--------+---------+--------+-------------------------------+----------------------------------------------------------+-----------
----------------------------------------------+----------+--------+------------+-----------+------------
     1 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_n(id ,name) VALUES(1 ,'aaa') | DELETE FRO
M public.walminer_n WHERE id=1 AND name='aaa' | t        | public | walminer_n | 0/1AA1238 | 0/1AA1360
     2 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_n(id ,name) VALUES(2 ,'bbb') | DELETE FRO
M public.walminer_n WHERE id=2 AND name='bbb' | t        | public | walminer_n | 0/1AA1278 | 0/1AA1360
     3 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_n(id ,name) VALUES(3 ,'ccc') | DELETE FRO
M public.walminer_n WHERE id=3 AND name='ccc' | t        | public | walminer_n | 0/1AA12B8 | 0/1AA1360
     4 | 773 |      0 |       1 | t      | 2023-08-18 00:20:09.273406+08 | INSERT INTO public.walminer_n(id ,name) VALUES(4 ,'ddd') | DELETE FRO
M public.walminer_n WHERE id=4 AND name='ddd' | t        | public | walminer_n | 0/1AA12F8 | 0/1AA1360
(4 rows)

11.删除wal文件

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

test2=#  select walminer_wal_add('/pgdata02/pg_wal');
  walminer_wal_add  
--------------------
 1 file add success
(1 row)

test2=# select walminer_wal_list();
              walminer_wal_list              
---------------------------------------------
 (/pgdata02/pg_wal/000000010000000000000001)
(1 row)

test2=# select walminer_wal_remove('/pgdata02/pg_wal');
ERROR:  Argument can be file only, an not be a directory
test2=# select walminer_wal_remove('/pgdata02/pg_wal/000000010000000000000001');
  walminer_wal_remove  
-----------------------
 1 file remove success
(1 row)

test2=# select walminer_wal_list();
ERROR:  wal list has not been loaded or has been removed.

12.结束walminer

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

test2=# select walminer_stop();
  walminer_stop   
------------------
 walminer stoped!
(1 row)

三、开发中的功能

1.自apply解析

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

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

2.DDL解析

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

目前walminer支持解析系统表的变化。也就是说如果在PG执行了DDL语句，walminer可以分析出DDL语句引起的系统表的变化，系统表变化解析和DDL解析不共存，总是接受最新确定的状态。

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

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

3.数据页挽回

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

select page_collect(relfilenode, reloid, pages);

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

四、总结

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

参考：https://www.modb.pro/db/377608

南風_入弦

关注

3
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
PostgreSQL插件——Walminer

这个插件现在的功能现在已经足够处理常规的误操作，达到闪回的效果，且此工具还在不断开发完善中，让我们一起期待一个更强大更完善的救火工具。参考：https://www.modb.pro/db/377608。
复制链接

扫一扫