HighGo Database v4 Xlogminer的使用
1、测试数据库版本
highgo=# select kernel_version();
kernel_version
---------------------------------------------------------------------------
HighGo Database V4.1 Enterprise EditionRelease 4.1.1 - 64-bit Production
(1 行记录)
2、开启数据库归档模式并设置归档路径
wal_level = logical --注：此处设置为archive将无法完整挖掘操作记录，需设置为logical
archive_mode = on
archive_directory ='D:\\highgo\database\arch'
3、创建测试表及测试数据
highgo=# create table test (id int,namevarchar(2));
CREATE TABLE
highgo=# insert into test values (1,'aa');
INSERT 0 1
highgo=# insert into test values (2,'bb');
INSERT 0 1
highgo=# insert into test values (3,'cc');
INSERT 0 1
4、将表设置为full模式
highgo=# alter table test replica identityFULL;
ALTER TABLE
5、切换xlog
highgo=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/210067C8
(1 行记录)
此时D:\highgo\database\arch下有文件:
000000010000000000000021
6、对测试数据进行操作
highgo=# insert into test values (4,'cc');
INSERT 0 1
highgo=# delete from test where id=3;
DELETE 1
highgo=# update test set id=5 where id=4;
UPDATE 1
highgo=# create table testa as select *from test;
SELECT 3
highgo=# select * from test;
id |name
----+------
1 |aa
2 |bb
5 |cc
(3 行记录)
7、再次切换xlog
highgo=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/22015860
(1 行记录)
此时D:\highgo\database\arch下有文件:
000000010000000000000021
000000010000000000000022
8、开始从归档中挖掘创建的测试数据
（1）创建pg_xlogminer的extension
highgo=# create extension xlogminer;
（2）生成数据字典
highgo=# selectxlogminer_build_dictionary('D:\highgo\database\store_dict');
xlogminer_build_dictionary
----------------------------
Dictionary build success!
(1 行记录)
此操作会在D:\highgo\database\store_dict下生成文件highgo_dictionary.d
（3）添加xlog日志文件
补充相关语法如下：
增加wal文件：
selectxlogminer_xlogfile_add('D:\arch\000000010000000000000003');
移除wal文件：
selectxlogminer_xlogfile_remove('D:\arch\000000010000000000000003');
列出wal文件：
selectxlogminer_xlogfile_list();
highgo=# selectxlogminer_xlogfile_add('D:\highgo\database\arch\000000010000000000000021');
NOTICE: L0011: Get data dictionary from current database.
xlogminer_xlogfile_add
------------------------
1file add success
(1 行记录)
highgo=# selectxlogminer_xlogfile_add('D:\highgo\database\arch\000000010000000000000022');
xlogminer_xlogfile_add
------------------------
1file add success
(1 行记录)
（4）查看确认已添加的xlog文件
highgo=# select xlogminer_xlogfile_list();
xlogminer_xlogfile_list
----------------------------------------------------------
("D:\\highgo\\database\\arch\\000000010000000000000021")
("D:\\highgo\\database\\arch\\000000010000000000000022")
(2 行记录)
（5）执行解析
highgo=# selectxlogminer_start('NULL','NULL','0','0');
xlogminer_start
------------------
xlogminer start!
(1 行记录)
（6）解析结果查看（确认可挖掘出相关的DML语句机器undo语句）
highgo=# select * from xlogminer_contents;
xid| virtualxid | timestamptz | record_database | record_user
| record_tablespace | record_schema |op_type | op_
text | op_und
o
-----+------------+-----------------------------+-----------------+-------------
+-------------------+---------------+---------+---------------------------------
----------------------------------+---------------------------------------------
-----------------------------------------
668| 1 | 2017-10-2723:03:56.9495+08 | highgo |highgo
| pg_default | public | DROP | DROP TABLE "testa";
| NULL
669| 1 | 2017-10-2723:04:13.5645+08 | highgo |highgo
| pg_default | public | DELETE | DELETE FROM"public"."test" WHER
E "id"=5 AND"name"='cc'; | INSERTINTO "public"."test"("id", "name") VA
LUES(5, 'cc');
670| 1 | 2017-10-2723:04:28.2885+08 | highgo | highgo
| pg_default | public | INSERT | INSERT INTO"public"."test"("id"
, "name") VALUES(3, 'cc'); | DELETE FROM"public"."test" WHERE "id"=3 AND
"name"='cc' AND ctid = '(0,6)';
672| 1 | 2017-10-27 23:05:02.7815+08| highgo | highgo
| pg_default | public | INSERT | INSERT INTO"public"."test"("id"
, "name") VALUES(4, 'cc'); | DELETE FROM"public"."test" WHERE "id"=4 AND
"name"='cc' AND ctid = '(0,7)';
673| 1 | 2017-10-27 23:05:07.4385+08| highgo | highgo
| pg_default | public | DELETE | DELETE FROM"public"."test" WHER
E "id"=3 AND"name"='cc'; | INSERTINTO "public"."test"("id", "name") VA
LUES(3, 'cc');
674| 1 | 2017-10-2723:05:11.5785+08 | highgo |highgo
| pg_default | public | UPDATE | UPDATE "public"."test"SET "id"
= 5 WHERE "id"=4 AND"name"='cc'; | UPDATE "public"."test" SET"id" = 4 WHERE "i
d"=5 AND "name"='cc' ANDctid = '(0,8)';
675| 1 | 2017-10-2723:05:16.1665+08 | highgo |highgo
| pg_default | public | CREATE | CREATE TABLE"testa"("id" int4,"
name" varchar(2)); | NULL
675| 2 | 2017-10-27 23:05:16.1665+08| highgo | highgo
| pg_default | public | INSERT | INSERT INTO"public"."testa"("id
", "name") VALUES(1,'aa'); | DELETE FROM"public"."testa" WHERE "id"=1 AN
D "name"='aa' AND ctid = '(0,1)';
675| 3 | 2017-10-2723:05:16.1665+08 | highgo |highgo
| pg_default | public | INSERT | INSERT INTO"public"."testa"("id
", "name") VALUES(2,'bb'); | DELETE FROM"public"."testa" WHERE "id"=2 AN
D "name"='bb' AND ctid = '(0,2)';
675| 4 | 2017-10-27 23:05:16.1665+08 |highgo | highgo
| pg_default | public | INSERT | INSERT INTO"public"."testa"("id
", "name") VALUES(5,'cc'); | DELETE FROM"public"."testa" WHERE "id"=5 AN
D "name"='cc' AND ctid = '(0,3)';
(10 行记录)
注意：xlogminer_contents是xlogminer自动生成的临时表，因此当session断开再重新进入或其他session中解析数据不可见。这么做主要是基于安全考虑。 如果希望保留解析结果，可利用createxxx as select * from xlogminer_contents;写入普通表中。
（7）数据清除
highgo=# select xlogminer_stop();
xlogminer_stop
-----------------
xlogminer stop!
(1 行记录)