Postgres邏輯復(fù)制構(gòu)建

IT那活兒發(fā)布于2023-01-11 13:19 / 1558人閱讀

點擊上方“IT那活兒”公眾號，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！

概述

Postgresql流復(fù)制是物理復(fù)制技術(shù)，跟Oracle ADG一樣，要求主備數(shù)據(jù)庫在數(shù)據(jù)塊級完全一樣，這樣就大大保證了數(shù)據(jù)同步的低延遲和數(shù)據(jù)一致性。但是在某些場景下，物理復(fù)制技術(shù)并不適用，比如基于庫級或者表級的復(fù)制、多對一匯總的數(shù)據(jù)倉庫、要求備機可寫等。

Postgresql邏輯復(fù)制技術(shù)基于WAL日志解析實現(xiàn)，可實現(xiàn)表級同步，并且可以跨版本同步數(shù)據(jù)。

邏輯復(fù)制相關(guān)概念

PG邏輯復(fù)制基于發(fā)布和訂閱模型，一個發(fā)布可以有多個訂閱者。

發(fā)布publication可以創(chuàng)建在任意單機上，也可以在物理復(fù)制的主庫上，發(fā)布端要求數(shù)據(jù)庫參數(shù)WAL_LEVEL為logical。一個發(fā)布可以被多個數(shù)據(jù)庫訂閱。一個發(fā)布只能包含一個數(shù)據(jù)庫中的表，但是這些表可以分布在不同的schema下。

訂閱處于邏輯復(fù)制的下游端，一個訂閱只能對應(yīng)一個發(fā)布。訂閱端的數(shù)據(jù)庫名可以跟發(fā)布端不同，但是訂閱發(fā)布的表名和schema必須相同。

當訂閱創(chuàng)建成功后，就會自動在發(fā)布端創(chuàng)建多個邏輯復(fù)制槽，其中一個用于后續(xù)的增量數(shù)據(jù)同步，永久保留，另外還會創(chuàng)建一個或者多個的臨時復(fù)制槽，用于復(fù)制表的全量快照數(shù)據(jù)。是當發(fā)布端是物理復(fù)制架構(gòu)時，如果發(fā)生主從切換，發(fā)布是不會跟隨數(shù)據(jù)庫failover的。但是可以通過手工拷貝文件或者利用插件實現(xiàn)。

2.1 邏輯復(fù)制的限制

數(shù)據(jù)庫的表結(jié)構(gòu)不會被復(fù)制，所以需要在訂閱端先創(chuàng)建對應(yīng)的schema和表
DDL不會不復(fù)制，如果要進行DDL變更，需要先在訂閱端執(zhí)行DDL，然后在發(fā)布端執(zhí)行。
Sequence不會不復(fù)制，當發(fā)布端使用了sequence數(shù)據(jù)，其寫入了表的值會同步到訂閱端，但是目標庫的sequence并不會發(fā)生變化。
不支持大對象。
復(fù)制只能是基表到基表，不支持視圖、物化視圖、外部表等。支持DDL，如果表是分區(qū)表，需要基于分區(qū)進行復(fù)制。

2.2 邏輯復(fù)制參數(shù)要求

發(fā)布端：

wal_level = logical
max_replication_slots ##大于訂閱數(shù)+物理復(fù)制槽數(shù)量+少量剩余用作臨時快照復(fù)制
max_wal_senders #大于max_replication_slots+物理復(fù)制的備機數(shù)量

訂閱端：

max_replication_slots = 訂閱數(shù)加上保留給表同步的連接數(shù)
max_logical_replication_workers =訂閱數(shù)加上保留給表同步的連接數(shù)
max_worker_processes =至少max_logical_replication_workers+1

2.3 邏輯復(fù)制構(gòu)建

需要進行邏輯復(fù)制的表，要求最好是有主鍵或者唯一索引。為了能夠復(fù)制UPDATE和DELETE操作，被發(fā)布的表必須配置有一個“復(fù)制標識”，這樣在訂閱者那一端才能標識對于更新或刪除合適的行。默認情況下，復(fù)制標識就是主鍵，如果既沒有主鍵也沒有唯一索引，就需要配置復(fù)制標識為full

復(fù)制標識配置：

Alter table table_name
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

Default表示使用主鍵，默認標識，可以不設(shè)置；
Using index index_name表示使用唯一索引；
Full 表示表中無主鍵和唯一索引，full標識影響復(fù)制效率；
Noting 標識的表不能進行delete和update復(fù)制。

創(chuàng)建發(fā)布：

Create publication pub_name for table customize_prod.t_migu_scenelooptone_11 ,
customize_prod.t_migu_scenelooptone_12 ,
customize_prod.t_migu_scenelooptone_13 ;

這樣就成功創(chuàng)建了發(fā)布。

創(chuàng)建訂閱：

CREATE SUBSCRIPTION subscription_name CONNECTION conninfo PUBLICATION publication_name；

當在邏輯復(fù)制架構(gòu)下，需要增加同步的表時，我們可以使用如下命令：

Alter publication pub_name add table table_name;
Alter subscription sub_name refresh publication;

2.4 邏輯復(fù)制相關(guān)視圖和監(jiān)控

發(fā)布端：

pg_publication
pg_publication_tables;

pg_stat_replications
pg_replication_slots

復(fù)制延遲：

select application_name,client_addr,COALESCE(trunc(extract(epoch 
FROM (now() - (now()- replay_lag) 
))::numeric),0),trunc(pg_wal_lsn_diff(pg_current_wal_lsn(), 
replay_lsn)/1024) replay_delay from pg_stat_replication;