PostgreSQL邏輯復制實現刪除歷史記錄的示例分析

這篇文章給大家介紹PostgreSQL邏輯復制實現刪除歷史記錄的示例分析，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

有些數據庫是有歷史表的功能的，也就是你操作的數據的歷史會記錄到另一個表中,包含更新的和刪除的記錄，以防止某些意外的情況找回歷史的數據，或知道在什么時候表中的記錄變化。

PostgreSQL 本身沒有這樣的功能的，但這里有一個想法可以來通過邏輯復制的方法來模擬這樣的功能。當然通過trigger 也是可以完成這樣的功能，但是考慮到可能會有遇上性能上的問題。

這里我們在test 數據庫上建立log_save的表，我們的需求是通過邏輯復制的功能，將log_save 的插入的記錄，update 的記錄 都進行一個保留（update 只能保存最后一次修改的記錄），并且通過某些SQL 的方式也能追蹤到這個表到底刪除了那些記錄。

大致的思路，我們建立三張復制的表在不同的數據庫中（因為復制的表名必須一致,三個數據庫分別是 test_insert  test_update  test_delete），第一張僅僅記錄 log_save 表的insert 記錄，包含一個時間戳，同時另外兩張表一個記錄 insert,update 的記錄，最后一張記錄 insert  delete的操作。

create publication log_save_insert for table public.log_save with (publish = 'insert');

create publication log_save_update for table public.log_save with (publish = 'insert,update');

create publication log_save_delete for table public.log_save with (publish = 'insert,delete');

創建三個數據庫 test_insert  test_update test_delete

先添加3張歷史表，分別建立在不同的數據庫中，每個庫一張

CREATE TABLE public.log_save

test-# (

test(#     id serial,

test(#     log_type character varying(10),

test(#     log_content text,

test(#     insert_date timestamp without time zone,

test(#     PRIMARY KEY (id)

test(# )

test-# WITH (

test(#     OIDS = FALSE,

test(#     FILLFACTOR = 80,

test(#     autovacuum_enabled = TRUE

test(# );

alter table log_save add column date_time timestamp default now();

數據庫中創建邏輯復制槽 (可以僅僅創建一個復制槽即可，這里為了讓下面的操作更清晰，生成了三個復制槽)

創建邏輯復制槽

 SELECT * FROM pg_create_logical_replication_slot('log_save_insert', 'pgoutput');

 SELECT * FROM pg_create_logical_replication_slot('log_save_delete', 'pgoutput');

 SELECT * FROM pg_create_logical_replication_slot('log_save_update', 'pgoutput');

創建訂閱，在三個不同的庫中每個庫對應不同的publication 創建一個訂閱

 CREATE SUBSCRIPTION log_save_insert CONNECTION 'host=192.168.198.123 dbname=test user=repl password=123456 port=5432' PUBLICATION log_save_insert with (create_slot = false,slot_name = log_save_insert);

CREATE SUBSCRIPTION log_save_update CONNECTION 'host=192.168.198.123 dbname=test user=repl password=123456 port=5432' PUBLICATION log_save_update with (create_slot = false,slot_name = log_save_update);

CREATE SUBSCRIPTION log_save_delete CONNECTION 'host=192.168.198.123 dbname=test user=repl password=123456 port=5432' PUBLICATION log_save_delete with (create_slot = false,slot_name = log_save_delete);

下面我們可以做一個實驗

我們在 test 數據庫中的表 log_save 插入數據，然后更改數據，刪除數據

下面是初始的數據

現在我們刪除 ID = 19 的數據

我們在查看test_delete 的數據庫 的log_save 表 我看可以看到，的確數據少了

我們在修改ID = 17 列 log_content 的數據，可以很清晰的看到

update log_save set log_content = '09899', insert_date = now() where id = 17;

以上已經驗證了，三個庫中的 insert  update  delete 操作是針對三種操作，通過這些表我們可以做什么

1  檢索，檢索今天log_save 插入多少數據庫

2  今天log_save 表到底更新了多少行數據，那些行被更新過 （這里默認在update 里面的insert_date 為null的是沒有更新過的數據，當然你也可以設置成其他的方式，例如插入的時候帶有insert_date ，而update 操作不更新insert_date ，這樣 update的表中的insert_date  和insert 表的 insert_date 就會不同）

這里采用上面說的insert_date 為空的說明沒有更新

3  最后我們是要獲得今天到底刪除了多少記錄

select count(id) from log_save where id not in (

select id from (

select id from dblink ('host=192.168.198.123 dbname=test_delete user=repl password=123456','select id from log_save') 

as b(id int) ) as b)

 ;

以上的查詢比較粗糙，不過基本上大致的基礎工作都做完了。

通過上面的例子，我們可以對一個數據庫的所有的表都可以進行類似歷史方面的記錄統計，和數據的歷史記錄，通過一些查詢可以找到誤刪除的數據，或者誤更新的數據。當然上面說過其實通過 trigger 的方式也是可以完成這樣的工作的，但是在性能方面的影響不言而喻。如果是對一個庫的所有的表都進行這樣的設置，trigger 的方式顯然也是不合適的。并且在分析數據的時候還會對基表產生性能影響。

關于PostgreSQL邏輯復制實現刪除歷史記錄的示例分析就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。