联系：手机/微信(+86 17813235971) QQ(107644445)
标题：PostgreSQL解析wal日志之—walminer
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在oracle数据库中可以通过logminer实现对归档日志的解析，从而分析执行sql语句和undo sql,可以实现某些情况下数据库一些操作的定位（比如日志突然增加，数据突然丢失）以及一些故障的恢复（比如需要把update/delete执行的数据找回）等。在PostgreSQL数据库中walminer可以实现该需求，对pg的预写式日志(wal)的解析,具体见官网:https://gitee.com/movead/XLogMiner/
walminer安装
[postgres@localhost tmp]$ ls -l walminer_x86_64_centos_v4.6.0.tar.gz
-rw-r--r--. 1 root root 3866437 Apr 18 10:08 walminer_x86_64_centos_v4.6.0.tar.gz
[postgres@localhost tmp]$ tar xzvf walminer_x86_64_centos_v4.6.0.tar.gz
walminer_x86_64_centos_v4.6.0/
walminer_x86_64_centos_v4.6.0/bin/
walminer_x86_64_centos_v4.6.0/bin/walminer
walminer_x86_64_centos_v4.6.0/lib/
walminer_x86_64_centos_v4.6.0/lib/libpq.so.5.15
walminer_x86_64_centos_v4.6.0/lib/libpq.so.5
walminer_x86_64_centos_v4.6.0/lib/libpq.so
walminer_x86_64_centos_v4.6.0/share/
…………
[root@localhost ~]# mkdir -p /usr/local/walminer/
[root@localhost ~]# chown postgres:postgres /usr/local/walminer/
[root@localhost ~]# cp /tmp/walminer/walminer.license /usr/local/walminer/
[postgres@localhost bin]$ cd /tmp/walminer
[postgres@localhost walminer]$ cp -rp * /usr/local/walminer/
[postgres@localhost bin]$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/walminer/
[postgres@localhost bin]$ cd /usr/local/walminer/bin
[postgres@localhost bin]$ ./walminer help
walminer [command] [options]
COMMANDS
---------
#wal2sql
options
-D dic file for miner
-a out detail info for catalog change
-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
-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
---------
#builtdic
options
-d target database name for connect(default postgres)
-h target database host(default localhost)
-p target database port(default 5432)
-u target database user(default postgres)
-W target user password
-D dic produce path
-f rewrite walminer dic if exists
-s only database pointed by -d
---------
#showdic
options
-D dic file to show
---------
#avatardic
options
-r avatar rel that new created
-n avatared relfilenode
-D avatared walminer dic path
-b target database name which contain rel pointed by -r
---------
#regress(not support for user)
options
-w test database wal path(default postgres)
-d test database name(default postgres)
-h test database host(default localhost)
-p test database port(default 5432)
-u test database user(default postgres)
-P apply database port
-W test user password
---------
#fosync
options
-D dic file for miner
-w wal file path to miner
-t dest of miner result(1 stdout, 2 file, 3 db, 4 apply)(stdout default)
-f file to store miner result if t = 2
-l lsn it start fync
-d target database name if t=3 or 4(default postgres)
-h target database host if t=3 or 4(default localhost)
-p target database port if t=3 or 4(default 5432)
-u target database user if t=3 or 4(default postgres)
-W target user password if t=3 or 4
---------
#pgto
options
-c configure path
-i to init a CDC configure
-r to run a CDC configure
Below is needed when -i
-d source database name(default postgres)
-h source database host(default localhost)
-p source database port(default 5432)
-u source database user(default postgres)
-w source user password
-D target database name
-H target database host
-P target database port
-U target database user
-W target user password
-K target database type(1 postgres) (support postgres only currently)
-s slot name need for CDC
---------
#waldump
options
-D dic file for miner
-w wal file path to dump
-t dest of miner result(1 stdout, 2 file)(stdout default)
-s start lsn to dump
-e end lsn to dump
-f file to store miner result if t = 2
-v verbose
---------
#################################################
[postgres@localhost bin]$
postgresql创建测试表和插入数据
[postgres@localhost ~]$ psql
psql (16.2)
Type "help" for help.
postgres=# select now();
now
-------------------------------
2024-04-25 10:48:00.602067-04
(1 row)
postgres=#
postgres=# create table t_walminer(id int,name varchar(100));
CREATE TABLE
postgres=# insert into t_walminer values(1,'www.xifenfei.com');
INSERT 0 1
postgres=# insert into t_walminer values(2,'www.orasos.com');
INSERT 0 1
postgres=# insert into t_walminer values(3,'xifenfei');
INSERT 0 1
postgres=# select * from t_walminer;
id | name
----+------------------
1 | www.xifenfei.com
2 | www.orasos.com
3 | xifenfei
(3 rows)
postgres=# select now();
now
-------------------------------
2024-04-25 10:49:47.036881-04
(1 row)
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/D5023E8
(1 row)
walminer 生成字典
[postgres@localhost bin]$ ./walminer builtdic -D /usr/local/walminer/xifenfei.dic ################################################# Walminer for PostgreSQL wal Contact Author by mail 'lchch1990@sina.cn' Persional License for posgress ################################################# DIC INFO# sysid:7357852038421105818 timeline:1 dbversion:160002 walminer:4.6
walminer解析这个时间段wal操作
[postgres@localhost bin]$ ./walminer wal2sql -D /usr/local/walminer/xifenfei.dic -w /pg/database/data/pg_arch \ -k 3 -s 2024-04-24 -e 2024-04-26 ################################################# Walminer for PostgreSQL wal Contact Author by mail 'lchch1990@sina.cn' Vip License for posgress ################################################# Switch wal to /pg/database/data/pg_arch/000000010000000000000001 on time 2024-04-25 23:27:07.42721+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000002 on time 2024-04-25 23:27:07.45369+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000003 on time 2024-04-25 23:27:07.453891+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000004 on time 2024-04-25 23:27:07.486403+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000005 on time 2024-04-25 23:27:07.513144+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000006 on time 2024-04-25 23:27:07.538212+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000007 on time 2024-04-25 23:27:07.561455+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000008 on time 2024-04-25 23:27:07.584488+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000009 on time 2024-04-25 23:27:07.606598+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000A on time 2024-04-25 23:27:07.609195+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000B on time 2024-04-25 23:27:07.609344+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000C on time 2024-04-25 23:27:07.609364+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000D on time 2024-04-25 23:27:07.66233+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000E on time 2024-04-25 23:27:07.684666+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000F on time 2024-04-25 23:27:07.684877+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000001 on time 2024-04-25 23:27:07.684899+08 Get start lsn 0/d4eb380 for time range Switch wal to /pg/database/data/pg_arch/00000001000000000000000D on time 2024-04-25 23:27:07.694947+08 [XID]=425507, [TOPXID]=0 [SQLNO]=1 [SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(1 ,'www.xifenfei.com') [UNDO]=DELETE FROM public.t_walminer WHERE id=1 AND name='www.xifenfei.com' [database]=postgres [COMPLETE]=true [LSN]=0/d5021c8 [COMMITLSN]=0/d502218 [COMMITTIME]=2024-04-25 22:48:55.775279+08 ------------------------------------------------------ [XID]=425508, [TOPXID]=0 [SQLNO]=1 [SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(2 ,'www.orasos.com') [UNDO]=DELETE FROM public.t_walminer WHERE id=2 AND name='www.orasos.com' [database]=postgres [COMPLETE]=true [LSN]=0/d502278 [COMMITLSN]=0/d5022c8 [COMMITTIME]=2024-04-25 22:49:10.769752+08 ------------------------------------------------------ [XID]=425509, [TOPXID]=0 [SQLNO]=1 [SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(3 ,'xifenfei') [UNDO]=DELETE FROM public.t_walminer WHERE id=3 AND name='xifenfei' [database]=postgres [COMPLETE]=true [LSN]=0/d502328 [COMMITLSN]=0/d502370 [COMMITTIME]=2024-04-25 22:49:23.382642+08 ------------------------------------------------------ Switch wal to /pg/database/data/pg_arch/00000001000000000000000E on time 2024-04-25 23:27:07.696041+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000F on time 2024-04-25 23:27:07.696062+08 [postgres@localhost bin]$
通过上述测试证明walminer可以非常好的解析pg的wal日志