快捷導(dǎo)航

postgresql邏輯復(fù)制的實現(xiàn)

更新時間：2025年06月16日 09:50:57 作者：24K老游

本文介紹PostgreSQL邏輯復(fù)制實現(xiàn),基于發(fā)布訂閱模型,需設(shè)置WAL_LEVEL為logical,支持跨庫備份,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

邏輯復(fù)制基本概念

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必須相同。

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

邏輯復(fù)制的限制

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

測試

發(fā)布和訂閱端都新建相同的表t1,t2
發(fā)布端為t1創(chuàng)建發(fā)布
訂閱端為t1創(chuàng)建訂閱

新建一個庫，做為訂閱端
[pg@localhost data]$ pg_ctl init -D /data/db3

發(fā)布與訂閱端都建相關(guān)同步結(jié)構(gòu)的表t1,t2
postgres=# create table t1(id int,name varchar(20));
CREATE TABLE
postgres=# create table t2(id int,name varchar(20));
CREATE TABLE

在發(fā)布端為t1創(chuàng)建發(fā)布

修改發(fā)布端wal_level = logical，修改后要restart訂閱端，reload不生效。
postgres=# show wal_level;
 wal_level
-----------
 logical
(1 row)


postgres=# create publication pub_test for table t1;
CREATE PUBLICATION

在訂閱端為t1創(chuàng)建訂閱

注：訂閱端有wal_level也必須是logical

postgres=# create subscription sub_test connection 'host=127.0.0.1 port=15431 dbname=postgres user=pg' publication pub_test;
NOTICE:  created replication slot "sub_test" on publisher
CREATE SUBSCRIPTION

觀察同步效果

在發(fā)布端分別向 t1 t2表插入數(shù)據(jù)
postgres=# insert into t1(id,name) values (1,'name1');
INSERT 0 1
postgres=# insert into t2(id,name) values (1,'name1');
INSERT 0 1

查看訂閱端，只有已新建訂閱的表數(shù)據(jù)同步
postgres=# select * from t1;
 id | name
----+-------
  1 | name1
(1 row)

postgres=# select * from t2;
 id | name
----+------
(0 rows)

為新表添加同步

發(fā)布端，將t2加入到發(fā)布
postgres=# alter publication pub_test add table t2;
ALTER PUBLICATION

查看發(fā)布表
postgres=# select * from pg_publication_tables;
 pubname  | schemaname | tablename
----------+------------+-----------
 pub_test | public     | t1
 pub_test | public     | t2
(2 rows)

訂閱端，刷新訂閱配置
postgres=# alter subscription sub_test refresh publication;
ALTER SUBSCRIPTION

發(fā)布端，分別向t1 t2插入數(shù)據(jù)
postgres=# insert into t1(id,name) values (2,'name2');
INSERT 0 1
postgres=# insert into t2(id,name) values (2,'name2');
INSERT 0 1

訂閱端，查看t1 t2的數(shù)據(jù)
postgres=# select * from t1;
 id | name
----+-------
  1 | name1
  2 | name2
(2 rows)

postgres=# select * from t2;
 id | name
----+-------
  1 | name1
  2 | name2
(2 rows)

復(fù)制標(biāo)識 REPLICA IDENTITY

REPLICA IDENTITY，復(fù)制標(biāo)識，共有4種配置模式，分別為，

默認(rèn)模式(default)：
非系統(tǒng)表采用的默認(rèn)模式，如果有主鍵，則用主鍵列作為身份標(biāo)識，否則用完整模式。
索引模式(index)：將某一個符合條件的索引中的列，用作身份標(biāo)識。
完整模式(full)：將整行記錄中的所有列作為復(fù)制標(biāo)識(類似于整個表上每一列共同組成主鍵)。
無身份模式(nothing)：不記錄任何復(fù)制標(biāo)識，這意味著UPDATE|DELETE操作無法復(fù)制到訂閱者上。

表改復(fù)制標(biāo)識可以通過ALTER TABLE進(jìn)行修改。
ALTER TABLE T1 REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING};

T1沒有主建，直接刪除，提示需要設(shè)置 REPLICA IDENTITY
postgres=# delete from t1 where id=1;
ERROR:  cannot delete from table "t1" because it does not have a replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

修改為full，刪除成功
postgres=# alter table t1 replica identity full;
ALTER TABLE
postgres=# delete from t1 where id=1;
DELETE 1

到此這篇關(guān)于postgresql 邏輯復(fù)制的實現(xiàn)的文章就介紹到這了,更多相關(guān)postgresql 邏輯復(fù)制內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: