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