PG10 到 PG11 的邏輯復(fù)制
我下面演示的PG環(huán)境是單機(jī)多實(shí)例的方式部署在同一臺(tái)物理機(jī)上的。部署方式可以參考 上一篇博客。
1、當(dāng)前老的PG10主庫(需要先設(shè)置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)建一個(gè)復(fù)制用的賬號(hào)
CREATE?USER?repuser?REPLICATION?LOGIN?CONNECTION?LIMIT?10?ENCRYPTED?PASSWORD?'repuser'; 另外，還需要給?repuser?用戶對(duì)源庫、源表、源schmea?賦權(quán) \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;??--?這個(gè)授權(quán)有點(diǎn)大，但是問題不算太嚴(yán)重然后，還要去pg_hba.conf 放開 repuser賬號(hào)的訪問地址，如下：
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?54343、導(dǎo)出PG10的 schema definitions
cd?/usr/local/pgsql-10.10 ./bin/pg_dumpall?-s??--port?5433?--no-subscriptions?>?./schemadump.sql4、 將PG10的 導(dǎo)出數(shù)據(jù)導(dǎo)入到PG11中
su?-?postgres? cd?/usr/local/pgsql-11.5 ./bin/psql?--port?5434?-d?postgres?-f?/usr/local/pgsql-10.10/schemadump.sql5、在源實(shí)例PG10中的每個(gè)數(shù)據(jù)庫中，創(chuàng)建一個(gè)捕獲所有表的發(fā)布
注意：邏輯復(fù)制在每個(gè)數(shù)據(jù)庫中分別工作，因此需要在每個(gè)數(shù)據(jù)庫中重復(fù)。另一方面，您不必一次升級(jí)所有數(shù)據(jù)庫，因此可以一次完成一個(gè)數(shù)據(jù)庫，甚至不升級(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、 在目標(biāo)實(shí)例 PG11 中的每個(gè)數(shù)據(jù)庫中，創(chuàng)建訂閱剛剛創(chuàng)建的發(fā)布的訂閱。確保與源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫匹配正確。
su?-?postgres? cd?/usr/local/pgsql-11.5? ./bin/psql?--port?5434testdb2=#?\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上，我們加一個(gè)表 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;????需要授權(quán)下 因?yàn)槲覀兊谖宀降臅r(shí)候，給了all?table做了復(fù)制的配置，?因此新加表后這里不需要執(zhí)行添加到發(fā)布者的命令。 testdb1=#?\dRp+?p_upgrade??--?查看發(fā)布者的詳細(xì)信息 ????????????????Publication?p_upgrade Owner???|?All?tables?|?Inserts?|?Updates?|?Deletes? ----------+------------+---------+---------+--------- ?postgres?|?t??????????|?t???????|?t???????|?t (1?row)PG10上加完表后，我們可以看到PG11上這個(gè) tb_new 表是不存在的。 需要我們到PG11上手工創(chuàng)建下：
在PG11上執(zhí)行如下命令：
\c?testdb1 create?table?tb_new(?a?int?,b?int?); select?count(*)?from?tb_new;???--?這時(shí)候數(shù)據(jù)還是為0的 ALTER?SUBSCRIPTION?s_sub?REFRESH?PUBLICATION;??--?刷新一下訂閱者 select?count(*)?from?tb_new;???--?這時(shí)候數(shù)據(jù)變成了4條了9、 清除復(fù)制設(shè)置（在PG11新庫上執(zhí)行）
\c?testdb1 DROP?SUBSCRIPTION?s_sub; \c?testdb2 DROP?SUBSCRIPTION?s_sub2;然后， 也可以刪除源實(shí)例PG10上的發(fā)布，但這不是必需的。
10、最后，如果老的PG10上流量都切到PG11后，可以將PG10這個(gè)實(shí)例下線。
原生logical復(fù)制的限制【非常關(guān)鍵】:?
1、只支持普通表生效，不支持序列、視圖、物化視圖、外部表、分區(qū)表和大對(duì)象
關(guān)于邏輯復(fù)制不支持的事項(xiàng)的變通方法的一些附加注釋。如果您正在使用大型對(duì)象，則可以使用pg_dump移動(dòng)它們，當(dāng)然只要它們?cè)谏?jí)過程中不會(huì)更改。這是一個(gè)重要的限制，因此如果您是大型對(duì)象的重度用戶，那么此方法可能不適合您。如果您的應(yīng)用程序在升級(jí)過程中發(fā)出TRUNCATE，則不會(huì)復(fù)制這些操作。也許您可以調(diào)整應(yīng)用程序以防止它在升級(jí)時(shí)執(zhí)行此操作，或者您可以替換DELETE。PostgreSQL 11將支持復(fù)制TRUNCATE，但這只有在源和目標(biāo)實(shí)例都是PostgreSQL 11或更新版本時(shí)才有效。
2、只支持普通表的DML(INSERT、UPDATE、DELETE)操作,不支持truncate、DDL操作
3、需要同步的表必須設(shè)置 REPLICA IDENTITY 不能為noting(默認(rèn)值是default),同時(shí)表中必須包含主鍵，否則delete和update報(bào)錯(cuò)
4、一個(gè)publisher可以包含一張或多張表，一張表可以有一個(gè)或多個(gè)publishers
5、一個(gè)發(fā)布者可以有多個(gè)訂閱者訂閱，一個(gè)訂閱者也可以同時(shí)訂閱多個(gè)發(fā)布者，在同一個(gè)數(shù)據(jù)庫下訂閱者不能對(duì)同一個(gè)發(fā)布者的表重復(fù)訂閱（避免數(shù)據(jù)沖突）
6、邏輯復(fù)制不同于流復(fù)制，不是嚴(yán)格的主從關(guān)系，訂閱者端的普通表依然可以進(jìn)行增刪改操作
7、同步表的表結(jié)構(gòu)需要在發(fā)布者和訂閱者兩邊保持一致（列的順序允許不一樣，但是列對(duì)應(yīng)的數(shù)據(jù)類型必須一致）
8、如果訂閱者端的數(shù)據(jù)被誤刪，想要從發(fā)布者重新copy同步表的數(shù)據(jù)，只能以重建同步表所在的訂閱者的方式來實(shí)現(xiàn)
其它注意事項(xiàng)：
publication - 發(fā)布者
邏輯復(fù)制的前提是將數(shù)據(jù)庫 wal_level 參數(shù)設(shè)置成 logical；
源庫上邏輯復(fù)制的用戶必須具有 replicatoin 或 superuser 角色；
邏輯復(fù)制目前僅支持?jǐn)?shù)據(jù)庫表邏輯復(fù)制，其它對(duì)象例如函數(shù)、視圖不支持；
邏輯復(fù)制支持DML(UPDATE、INSERT、DELETE)操作，TRUNCATE 和 DDL 操作不支持；
需要發(fā)布邏輯復(fù)制的表，須配置表的 REPLICA IDENTITY 特性；
一個(gè)數(shù)據(jù)庫中可以有多個(gè)publication，通過 pg_publication 查看；
允許一次發(fā)布所有表，語法： CREATE PUBLICATION alltables FOR ALL TABLES;
subscription - 訂閱者
訂閱節(jié)點(diǎn)需要指定發(fā)布者的連接信息；
一個(gè)數(shù)據(jù)庫中可以有多個(gè)訂閱者；
可以使用enable/disable啟用/暫停該訂閱；
發(fā)布節(jié)點(diǎn)和訂閱節(jié)點(diǎn)表的模式名、表名必須一致，訂閱節(jié)點(diǎn)允許表有額外字段；
發(fā)布節(jié)點(diǎn)增加表名，訂閱節(jié)點(diǎn)需要執(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
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。
重慶分公司
重慶分公司
