国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

利用WalMiner解析PostgreSQL的WAL日志

IT那活兒 / 1968人閱讀
利用WalMiner解析PostgreSQL的WAL日志
點擊上方“IT那活兒”公眾號,關注后了解更多內容,不管IT什么活兒,干就完了!??!

前奏

WalMiner是從PostgreSQL的WAL(write ahead logs)日志的解析工具,旨在挖掘wal日志所有的有用信息,從而提供PG的數據恢復支持。如果出現業務誤操作DML的情況,可以及時挽回數據。

1.1 安裝WalMiner

上來就踩坑,編譯報錯:
make: /opt/rh/llvm-toolset-7/root/bin/clang: Command not found
make: *** [pg_logminer.bc] Error 127
處理方式,注釋插件控制bc:
cd /app/pg/pg_156_5432/postgresql/lib/postgresql/pgxs/src/makefiles/
vi pgxs.mk

#
ifeq ($(with_llvm), yes)

#all: $(addsuffix .bc, $(MODULES)) $(patsubst %.o,%.bc, $(OBJS))
#endif

#ifeq ($(with_llvm), yes)
#       $(foreach mod, $(MODULES), $(call install_llvm_module,$(mod),$(mod).bc))
#endif # with_llvm

#
ifeq ($(with_llvm), yes)

#       $(call install_llvm_module,$(MODULE_big),$(OBJS))
#endif # with_llvm

#
ifeq ($(with_llvm), yes)

#       $(foreach mod, $(MODULES), $(call uninstall_llvm_module,$(mod)))
#endif # with_llvm

#
ifeq ($(with_llvm), yes)

#       $(call uninstall_llvm_module,$(MODULE_big))
#endif # with_llvm

1.2 重新編譯,記得刷新postgresql環境變量

/usr/bin/mkdir -p /app/pg/pg_156_5432/postgresql/lib/postgresql
/usr/bin/mkdir -p /app/pg/pg_156_5432/postgresql/share/postgresql/extension
/usr/bin/mkdir -p /app/pg/pg_156_5432/postgresql/share/postgresql/extension
/usr/bin/install -c -m 755  walminer.so /app/pg/pg_156_5432/postgresql/lib/postgresql/walminer.so
/usr/bin/install -c -m 644 .//walminer.control /app/pg/pg_156_5432/postgresql/share/postgresql/extension/
/usr/bin/install -c -m 644 .//walminer--1.0.sql /app/pg/pg_156_5432/postgresql/share/postgresql/extension/

1.3 進庫創建

postgres=# create extension walminer;
CREATE EXTENSION
postgres=# dx
List of installed extensions
Name | Version |   Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements |
 1.7     | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
telepg_monitor |
 2.7.6   | public | Expansion monitoring module
walminer | 1.0 | public | inspect the contents of database pages at a low level


解析步驟

2.1 添加要解析的wal日志文件

postgres=# select walminer_wal_add(/app/pg/data_156_5432/data/pg_wal);
NOTICE: Get data dictionary from current database.
walminer_wal_add
---------------------
13 file add success
(1 row)

2.2 查看可以解析的文件

postgres=# select walminer_wal_list();
walminer_wal_list
---------------------------------------------------------------
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000001)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000002)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000003)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000004)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000005)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000006)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000007)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000008)
(/app/pg/data_156_5432/data/pg_wal/000000010000000000000009)
(/app/pg/data_156_5432/data/pg_wal/00000001000000000000000A)
(/app/pg/data_156_5432/data/pg_wal/00000001000000000000000B)
(/app/pg/data_156_5432/data/pg_wal/00000001000000000000000C)
(/app/pg/data_156_5432/data/pg_wal/00000001000000000000000D)
(13 rows)

2.3 解析wal

postgres=# select walminer_start(null,null,0,0);
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000001
NOTICE: wal record after time 2022-02-24 18:17:44+08 or 0/166aff0 will be analyse completely
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000002
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000003
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000004
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000005
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000006
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000007
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000008
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/000000010000000000000009
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/00000001000000000000000A
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/00000001000000000000000B
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/00000001000000000000000C
NOTICE: Change Wal Segment To:/app/pg/data_156_5432/data/pg_wal/00000001000000000000000D
walminer_start
---------------------
walminer sucessful!
(1 row)

2.4 查看解析結果

postgres=# select * from walminer_contents;
-[ RECORD 1 ]-----+-------------------------------------------------------------
xid | 609
virtualxid |
 1
timestamptz | 2022-06-13 16:25:42.259986+08
record_database |
 postgres
record_user | root
record_tablespace |
 pg_default
record_schema | public
op_type |
 INSERT
op_text | INSERT INTO "public"."test"("id") VALUES(1);
op_undo |
 DELETE FROM "public"."test" WHERE "id"=1 AND ctid = (0,1);
lsn | 0/DAA6138
commit_end_lsn |
 0/DAA61A8

walminer_contents字段含義:

  • sqlno int, --本條sql在其事務內的序號;
  • xid bigint, --事務ID;
  • topxid bigint, --如果為子事務,這是是其父事務;否則為0;
  • sqlkind int, --sql類型1->insert;2->update;3->delete(待優化項目);
  • minerd bool, --解析結果是否完整(缺失checkpoint情況下可能無法解析出正確結果);
  • timestamp timestampTz,   --這個SQL所在事務提交的時間;
  • op_text text, --sql;
  • undo_text text,  --undo sql;
  • complete bool, --如果為false,說明有可能這個sql所在的事務是不完整解析的;
  • schema text, --目標表所在的模式;
  • relation text, --目標表表名;
  • start_lsn pg_lsn,  --這個記錄的開始LSN;
  • commit_lsn pg_lsn    --這個事務的提交LSN。

2.5 結束walminer操作

select walminer_stop();


常規操作

3.1 根據時間解析
select walminer_by_time(starttime, endtime);
3.2 精確到表的解析
select walminer_by_xid(xid,true,reloid);

注:true和‘false’代表是否為精確解析模式,reloid為目標表的oid(注意不是relfilenode)


適用性

4.1 優點
walminer可執行的騷操作:walminer是提供解析功能,可以挪用別的數據庫的wal日志到有插件的數據上執行(數據庫版本一致,要依靠數據字典),并查看。避免沒有插件,再安裝插件耽誤時間。

4.2 使用注意事項

  • 1)walminer對DDL支持力度不大,很多無法解析,不建議作為解析DDL依靠。
  • 2)walminer版本有三個,建議用2.0,telepg12.4不支持3.0。
  • 3)PG11/PG12中WAL文件大小必須16MB。


本文作者:饒茂林(上海新炬中北團隊)

本文來源:“IT那活兒”公眾號


文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。

轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/129118.html

相關文章

發表評論

0條評論

IT那活兒

|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<