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)
说明:
1.truncate不会被解析
2.ddl不会被解析
