PostgreSQL邏輯復(fù)制實現(xiàn)刪除歷史記錄的示例分析

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

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

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

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

大致的思路，我們建立三張復(fù)制的表在不同的數(shù)據(jù)庫中（因為復(fù)制的表名必須一致,三個數(shù)據(jù)庫分別是 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');

創(chuàng)建三個數(shù)據(jù)庫 test_insert  test_update test_delete

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

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();

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

創(chuàng)建邏輯復(fù)制槽

 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');

創(chuàng)建訂閱，在三個不同的庫中每個庫對應(yīng)不同的publication 創(chuàng)建一個訂閱

 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 數(shù)據(jù)庫中的表 log_save 插入數(shù)據(jù)，然后更改數(shù)據(jù)，刪除數(shù)據(jù)

下面是初始的數(shù)據(jù)

現(xiàn)在我們刪除 ID = 19 的數(shù)據(jù)

我們在查看test_delete 的數(shù)據(jù)庫 的log_save 表 我看可以看到，的確數(shù)據(jù)少了

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

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

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

1  檢索，檢索今天log_save 插入多少數(shù)據(jù)庫

2  今天log_save 表到底更新了多少行數(shù)據(jù)，那些行被更新過 （這里默認(rèn)在update 里面的insert_date 為null的是沒有更新過的數(shù)據(jù)，當(dāng)然你也可以設(shè)置成其他的方式，例如插入的時候帶有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)

 ;

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

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

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