walminer安装部署

环境:
Os:Centos 7
db:pg14

1.下载地址
https://gitee.com/movead/XLogMiner/releases

2.安装
[root@dsc1 soft]# tar -xvf walminer_x86_64_centos_v4.6.0.tar.gz
[root@dsc1 bin]#mv walminer_x86_64_centos_v4.6.0 /usr/local/walminer
[root@dsc1 opt]# chown -R postgres:postgres /usr/local/walminer

3.尝试使用
[root@dsc1 bin]# ./walminer help
"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise. See the documentation for
more information on how to properly start the server.

需要在postgres用户下执行
-bash-4.2$ ./walminer help

4.设置postgres用户环境变量

-bash-4.2$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
export PATH=$PATH:/usr/local/walminer/bin/

5.操作数据库

-bash-4.2$ psql -h localhost -U postgres -p5432
psql (15.6, server 14.11)
Type "help" for help.
postgres=# create user u1 password 'pwd123456';
CREATE ROLE

postgres=# alter user u1 with superuser;
ALTER ROLE

postgres=# create database mytestdb with owner u1;
CREATE DATABASE

postgres=# \c mytestdb u1
psql (15.6, server 14.11)
You are now connected to database "mytestdb" as user "u1".

mytestdb=# create table t (id serial primary key, name varchar(20));
CREATE TABLE

CREATE OR REPLACE FUNCTION random_string(
  num INTEGER,
  chars TEXT default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
) RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  res_str TEXT := '';
BEGIN
  IF num < 1 THEN
      RAISE EXCEPTION 'Invalid length';
  END IF;
  FOR __ IN 1..num LOOP
    res_str := res_str || substr(chars, floor(random() * length(chars))::int + 1, 1);
  END LOOP;
  RETURN res_str;
END $$;


mytestdb=# insert into t(name) select random_string(8);
mytestdb=# insert into t(name) select random_string(8);
mytestdb=# insert into t(name) select random_string(8);
mytestdb=# insert into t(name) select random_string(8);
mytestdb=# insert into t(name) select random_string(8);
mytestdb=# insert into t(name) select random_string(8);
mytestdb=# insert into t(name) select random_string(8);
mytestdb=# insert into t(name) select random_string(8);

update t set name='xiugai' where id=5;

delete from t where id=4;

6.生成新的数据字典

例子1(mytestdb):
su - postgres
walminer builtdic -D /tmp/walminer/walminer.dic -f -h localhost -p 5432 -d mytestdb -u u1 -W pwd123456

例子2(db_re[)
su - postgres
walminer builtdic -D /tmp/walminer/walminer.dic -f -h localhost -p 5432 -d db_rep -u postgres -W postgres

7.解析并输出到数据库临时表

例子1
walminer wal2sql -D /tmp/walminer/walminer.dic -w /opt/pg14/data/pg_wal -t 3 -h localhost -p 5432 -d mytestdb -u u1 -W pwd123456

例子2
walminer wal2sql -D /tmp/walminer/walminer.dic -w /opt/pg14/data/pg_wal -t 3 -h localhost -p 5432 -d db_rep -u postgres -W postgres

8.登录查看

-bash-4.2$ psql -h localhost -U u1 -p5432 -d mytestdb
psql (15.6, server 14.11)
Type "help" for help.

db_rep=# \dt
               List of relations
 Schema |       Name        | Type  |  Owner   
--------+-------------------+-------+----------
 public | tb_aa             | table | hxl
 public | walminer_contents | table | postgres
(2 rows)


mytestdb=# select * from walminer_contents;
 sqlno | xid | topxid | database | sqlkind | minerd |           timestamp           |                       op_text  
                      |                      undo_text                       | complete | relation | start_lsn  | com
mit_lsn 
-------+-----+--------+----------+---------+--------+-------------------------------+--------------------------------
----------------------+------------------------------------------------------+----------+----------+------------+----
--------
     1 | 753 |      0 | mytestdb | INSERT  | t      | 2024-05-23 14:25:06.463531+08 | INSERT INTO public.t(id ,name) 
VALUES(1 ,'5CoGAgZ0') | DELETE FROM public.t WHERE id=1                      | t        | t        | 0/1405DF30 | 0/1
405E030
     1 | 754 |      0 | mytestdb | INSERT  | t      | 2024-05-23 14:25:12.126681+08 | INSERT INTO public.t(id ,name) 
VALUES(2 ,'ezX5UFqX') | DELETE FROM public.t WHERE id=2                      | t        | t        | 0/1405E098 | 0/1
405E120
     1 | 755 |      0 | mytestdb | INSERT  | t      | 2024-05-23 14:25:21.355169+08 | INSERT INTO public.t(id ,name) 
VALUES(3 ,'tOVskWoo') | DELETE FROM public.t WHERE id=3                      | t        | t        | 0/1405E150 | 0/1
405E1D8
     1 | 756 |      0 | mytestdb | INSERT  | t      | 2024-05-23 14:25:26.381719+08 | INSERT INTO public.t(id ,name) 
VALUES(4 ,'jVQ3Rkj7') | DELETE FROM public.t WHERE id=4                      | t        | t        | 0/1405E208 | 0/1
405E290
     1 | 757 |      0 | mytestdb | INSERT  | t      | 2024-05-23 14:25:32.598497+08 | INSERT INTO public.t(id ,name) 
VALUES(5 ,'CZoZrLy2') | DELETE FROM public.t WHERE id=5                      | t        | t        | 0/1405E2F8 | 0/1
405E380
     1 | 758 |      0 | mytestdb | INSERT  | t      | 2024-05-23 14:25:37.961822+08 | INSERT INTO public.t(id ,name) 
VALUES(6 ,'GuNs6eHl') | DELETE FROM public.t WHERE id=6                      | t        | t        | 0/1405E3B0 | 0/1
405E438
     1 | 759 |      0 | mytestdb | INSERT  | t      | 2024-05-23 14:25:43.552159+08 | INSERT INTO public.t(id ,name) 
VALUES(7 ,'Z******p') | DELETE FROM public.t WHERE id=7                      | t        | t        | 0/1405E468 | 0/1
405E4F0
     1 | 760 |      0 | mytestdb | INSERT  | t      | 2024-05-23 14:25:48.461407+08 | INSERT INTO public.t(id ,name) 
VALUES(8 ,'G******G') | DELETE FROM public.t WHERE id=8                      | t        | t        | 0/1405E558 | 0/1
405E5E0
     1 | 761 |      0 | mytestdb | UPDATE  | t      | 2024-05-23 14:26:05.352521+08 | UPDATE public.t SET name='x****
i' WHERE id=5         | UPDATE public.t SET name='C******2' WHERE id=5       | t        | t        | 0/1405E610 | 0/1
405E660
     1 | 762 |      0 | mytestdb |         | t      | 2024-05-23 14:26:14.834933+08 | DELETE FROM public.t WHERE id=4
                      | INSERT INTO public.t(id ,name) VALUES(4 ,'j******7') | t        | t        | 0/1405E6C8 | 0/1
405E708
(10 rows)