PG10 到 PG11 的邏輯復制

成都創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供嘉峪關網(wǎng)站建設、嘉峪關做網(wǎng)站、嘉峪關網(wǎng)站設計、嘉峪關網(wǎng)站制作等企業(yè)網(wǎng)站建設、網(wǎng)頁設計與制作、嘉峪關企業(yè)網(wǎng)站模板建站服務，十余年嘉峪關做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡服務。

我下面演示的PG環(huán)境是單機多實例的方式部署在同一臺物理機上的。部署方式可以參考 上一篇博客。

1、當前老的PG10主庫(需要先設置wal_level = logical）：

su?-?postgres? cd?/usr/local/pgsql-10.10/ ./bin/psql?--port?5433 postgres=#?create?database?testdb1; CREATE?DATABASE postgres=#?create?database?testdb2; CREATE?DATABASE postgres=#?\c?testdb1 You?are?now?connected?to?database?"testdb1"?as?user?"postgres". testdb1=#?create?table?tb1(a?int?,b?int,?c?int?); testdb1=#?create?table?tb2(a?int?,b?int,?c?int?); testdb1=#?create?table?tb3(a?int?,b?int,?c?int?);

創(chuàng)建一個復制用的賬號

CREATE?USER?repuser?REPLICATION?LOGIN?CONNECTION?LIMIT?10?ENCRYPTED?PASSWORD?'repuser'; 另外，還需要給?repuser?用戶對源庫、源表、源schmea?賦權 \c?testdb1 grant?connect?on?database?testdb1?to?repuser; grant?usage?on?schema?public?to?repuser; grant?select?on?all?tables?in?schema?public?to?repuser;??--?這個授權有點大，但是問題不算太嚴重

然后，還要去pg_hba.conf 放開 repuser賬號的訪問地址，如下：

host????all?????repuser????192.168.2.1/24??????md5

然后，reload下pg的配置。

2、初始化PG11新庫

cd?/usr/local/pgsql-11.5 mkdir?data ./bin/initdb?-D?data ./bin/pg_ctl?-D?data/?-o?"-p?5434"?-l?pg.log?start ./bin/psql?--port?5434

3、導出PG10的 schema definitions

cd?/usr/local/pgsql-10.10 ./bin/pg_dumpall?-s??--port?5433?--no-subscriptions?>?./schemadump.sql

4、 將PG10的 導出數(shù)據(jù)導入到PG11中

su?-?postgres? cd?/usr/local/pgsql-11.5 ./bin/psql?--port?5434?-d?postgres?-f?/usr/local/pgsql-10.10/schemadump.sql

5、在源實例PG10中的每個數(shù)據(jù)庫中，創(chuàng)建一個捕獲所有表的發(fā)布

注意：邏輯復制在每個數(shù)據(jù)庫中分別工作，因此需要在每個數(shù)據(jù)庫中重復。另一方面，您不必一次升級所有數(shù)據(jù)庫，因此可以一次完成一個數(shù)據(jù)庫，甚至不升級某些數(shù)據(jù)庫。

cd?/usr/local/pgsql-10.10 ./bin/psql?--port?5433 postgres=#?\c?testdb1 testdb1=#?CREATE?PUBLICATION?p_upgrade?FOR?ALL?TABLES; testdb1=#??\dRp+ ????????????????Publication?p_upgrade ??Owner???|?All?tables?|?Inserts?|?Updates?|?Deletes? ----------+------------+---------+---------+--------- ?postgres?|?t??????????|?t???????|?t???????|?t (1?row) testdb1=#?\c?testdb2 testdb2=#?CREATE?PUBLICATION?p_upgrade2?FOR?ALL?TABLES; testdb2=#??\dRp+ ???????????????Publication?p_upgrade2 ??Owner???|?All?tables?|?Inserts?|?Updates?|?Deletes? ----------+------------+---------+---------+--------- ?postgres?|?t??????????|?t???????|?t???????|?t (1?row)

6、 在目標實例 PG11 中的每個數(shù)據(jù)庫中，創(chuàng)建訂閱剛剛創(chuàng)建的發(fā)布的訂閱。確保與源數(shù)據(jù)庫和目標數(shù)據(jù)庫匹配正確。

su?-?postgres? cd?/usr/local/pgsql-11.5? ./bin/psql?--port?5434

testdb2=#?\c?testdb1 testdb2=#?CREATE?SUBSCRIPTION?s_sub?CONNECTION?'host=192.168.2.4?port=5433?dbname=testdb1?user=repuser?password=repuser'?PUBLICATION?p_upgrade; testdb1=#?\dRs+ ??????????????????????????????????????????????????????????List?of?subscriptions ?Name??|??Owner???|?Enabled?|?Publication?|?Synchronous?commit?|????????????????????????????????Conninfo????????????????????????????????? -------+----------+---------+-------------+--------------------+------------------------------------------------------------------------- ?s_sub?|?postgres?|?t???????|?{p_upgrade}?|?off????????????????|?host=192.168.2.4?port=5433?dbname=testdb1?user=repuser?password=repuser (1?row) testdb2=#?\c?testdb2 testdb2=#?CREATE?SUBSCRIPTION?s_sub2?CONNECTION?'host=192.168.2.4?port=5433?dbname=testdb2?user=repuser?password=repuser'?PUBLICATION?p_upgrade2; testdb2=#?\dRs+ ???????????????????????????????????????????????????????????List?of?subscriptions ??Name??|??Owner???|?Enabled?|?Publication??|?Synchronous?commit?|????????????????????????????????Conninfo????????????????????????????????? --------+----------+---------+--------------+--------------------+------------------------------------------------------------------------- ?s_sub2?|?postgres?|?t???????|?{p_upgrade2}?|?off????????????????|?host=192.168.2.4?port=5433?dbname=testdb2?user=repuser?password=repuser (1?row)

7、在PG10上，造些數(shù)據(jù)：

postgres=#?\c?testdb1 testdb1=#?insert?into?tb1(a,b,c)?values?(1,1,1),(2,2,2),(3,3,3); testdb1=#?insert?into??tb2(a,b,c)?values?(1,1,1);

然后，到 PG11上的testdb1庫里面，可以看到 數(shù)據(jù)已同步了。

8、后續(xù)如果在PG10上有加表操作，類似如下：

PG10上，我們加一個表 testdb1=#?\c?testdb1 testdb1=#?create?table?tb_new(?a?int?,b?int?); testdb1=#?insert?into?tb_new?values(1,1)?,(2,2)?,(3,3),(4,4); testdb1=#?GRANT?SELECT?ON?tb_new?to?repuser;????需要授權下 因為我們第五步的時候，給了all?table做了復制的配置，?因此新加表后這里不需要執(zhí)行添加到發(fā)布者的命令。 testdb1=#?\dRp+?p_upgrade??--?查看發(fā)布者的詳細信息 ????????????????Publication?p_upgrade Owner???|?All?tables?|?Inserts?|?Updates?|?Deletes? ----------+------------+---------+---------+--------- ?postgres?|?t??????????|?t???????|?t???????|?t (1?row)

PG10上加完表后，我們可以看到PG11上這個 tb_new 表是不存在的。 需要我們到PG11上手工創(chuàng)建下：

在PG11上執(zhí)行如下命令：

\c?testdb1 create?table?tb_new(?a?int?,b?int?); select?count(*)?from?tb_new;???--?這時候數(shù)據(jù)還是為0的 ALTER?SUBSCRIPTION?s_sub?REFRESH?PUBLICATION;??--?刷新一下訂閱者 select?count(*)?from?tb_new;???--?這時候數(shù)據(jù)變成了4條了

9、 清除復制設置（在PG11新庫上執(zhí)行）

\c?testdb1 DROP?SUBSCRIPTION?s_sub; \c?testdb2 DROP?SUBSCRIPTION?s_sub2;

然后， 也可以刪除源實例PG10上的發(fā)布，但這不是必需的。

10、最后，如果老的PG10上流量都切到PG11后，可以將PG10這個實例下線。

原生logical復制的限制【非常關鍵】:?

1、只支持普通表生效，不支持序列、視圖、物化視圖、外部表、分區(qū)表和大對象

關于邏輯復制不支持的事項的變通方法的一些附加注釋。如果您正在使用大型對象，則可以使用pg_dump移動它們，當然只要它們在升級過程中不會更改。這是一個重要的限制，因此如果您是大型對象的重度用戶，那么此方法可能不適合您。如果您的應用程序在升級過程中發(fā)出TRUNCATE，則不會復制這些操作。也許您可以調(diào)整應用程序以防止它在升級時執(zhí)行此操作，或者您可以替換DELETE。PostgreSQL 11將支持復制TRUNCATE，但這只有在源和目標實例都是PostgreSQL 11或更新版本時才有效。

2、只支持普通表的DML(INSERT、UPDATE、DELETE)操作,不支持truncate、DDL操作

3、需要同步的表必須設置 REPLICA IDENTITY 不能為noting(默認值是default),同時表中必須包含主鍵，否則delete和update報錯

4、一個publisher可以包含一張或多張表，一張表可以有一個或多個publishers

5、一個發(fā)布者可以有多個訂閱者訂閱，一個訂閱者也可以同時訂閱多個發(fā)布者，在同一個數(shù)據(jù)庫下訂閱者不能對同一個發(fā)布者的表重復訂閱（避免數(shù)據(jù)沖突）

6、邏輯復制不同于流復制，不是嚴格的主從關系，訂閱者端的普通表依然可以進行增刪改操作

7、同步表的表結(jié)構(gòu)需要在發(fā)布者和訂閱者兩邊保持一致（列的順序允許不一樣，但是列對應的數(shù)據(jù)類型必須一致）

8、如果訂閱者端的數(shù)據(jù)被誤刪，想要從發(fā)布者重新copy同步表的數(shù)據(jù)，只能以重建同步表所在的訂閱者的方式來實現(xiàn)

其它注意事項：

publication - 發(fā)布者

邏輯復制的前提是將數(shù)據(jù)庫 wal_level 參數(shù)設置成 logical；

源庫上邏輯復制的用戶必須具有 replicatoin 或 superuser 角色；

邏輯復制目前僅支持數(shù)據(jù)庫表邏輯復制，其它對象例如函數(shù)、視圖不支持；

邏輯復制支持DML(UPDATE、INSERT、DELETE)操作，TRUNCATE 和 DDL 操作不支持；

需要發(fā)布邏輯復制的表，須配置表的 REPLICA IDENTITY 特性；

一個數(shù)據(jù)庫中可以有多個publication，通過 pg_publication 查看；

允許一次發(fā)布所有表，語法： CREATE PUBLICATION alltables FOR ALL TABLES;

subscription - 訂閱者

訂閱節(jié)點需要指定發(fā)布者的連接信息；

一個數(shù)據(jù)庫中可以有多個訂閱者；

可以使用enable/disable啟用/暫停該訂閱；

發(fā)布節(jié)點和訂閱節(jié)點表的模式名、表名必須一致，訂閱節(jié)點允許表有額外字段；

發(fā)布節(jié)點增加表名，訂閱節(jié)點需要執(zhí)行： ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION

參考資料：

https://www.postgresql.org/docs/10/sql-createpublication.html

https://www.postgresql.org/docs/10/sql-createsubscription.html

https://www.postgresql.org/docs/10/sql-altersubscription.html

https://yq.aliyun.com/articles/585446?spm=a2c4e.11153940.0.0.48e86e272CVXQp

https://postgres.fun/20170528142004.html

另外有需要云服務器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

文章題目：PGLogicalReplication邏輯復制-創(chuàng)新互聯(lián)
URL分享：http://jinyejixie.com/article36/dedesg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、外貿(mào)建站、定制網(wǎng)站、標簽優(yōu)化、域名注冊、建站公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)