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 行记录)