分类目录归档：PostgreSQL恢复

在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'
Persional 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日志