PG Logical Replication 邏輯複製
- 2019 年 10 月 4 日
- 筆記
PG10 到 PG11 的邏輯複製
我下面演示的PG環境是單機多實例的方式部署在同一台物理機上的。部署方式可以參考 上一篇博客。
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 );
創建一個複製用的賬號
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的 導出數據導入到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中的每個數據庫中，創建一個捕獲所有表的發佈
注意：邏輯複製在每個數據庫中分別工作，因此需要在每個數據庫中重複。另一方面，您不必一次升級所有數據庫，因此可以一次完成一個數據庫，甚至不升級某些數據庫。
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 中的每個數據庫中，創建訂閱剛剛創建的發佈的訂閱。確保與源數據庫和目標數據庫匹配正確。
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上，造些數據：
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庫裏面，可以看到 數據已同步了。
8、後續如果在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做了複製的配置， 因此新加表後這裡不需要執行添加到發佈者的命令。 testdb1=# dRp+ p_upgrade -- 查看發佈者的詳細信息 Publication p_upgrade Owner | All tables | Inserts | Updates | Deletes ----------+------------+---------+---------+--------- postgres | t | t | t | t (1 row)
PG10上加完表後，我們可以看到PG11上這個 tb_new 表是不存在的。 需要我們到PG11上手工創建下：
在PG11上執行如下命令：
c testdb1 create table tb_new( a int ,b int ); select count(*) from tb_new; -- 這時候數據還是為0的 ALTER SUBSCRIPTION s_sub REFRESH PUBLICATION; -- 刷新一下訂閱者 select count(*) from tb_new; -- 這時候數據變成了4條了
9、 清除複製設置（在PG11新庫上執行）
c testdb1 DROP SUBSCRIPTION s_sub; c testdb2 DROP SUBSCRIPTION s_sub2;
然後， 也可以刪除源實例PG10上的發佈，但這不是必需的。
10、最後，如果老的PG10上流量都切到PG11後，可以將PG10這個實例下線。
原生logical複製的限制【非常關鍵】:
1、只支持普通表生效，不支持序列、視圖、物化視圖、外部表、分區表和大對象
關於邏輯複製不支持的事項的變通方法的一些附加註釋。如果您正在使用大型對象，則可以使用pg_dump移動它們，當然只要它們在升級過程中不會更改。這是一個重要的限制，因此如果您是大型對象的重度用戶，那麼此方法可能不適合您。如果您的應用程序在升級過程中發出TRUNCATE，則不會複製這些操作。也許您可以調整應用程序以防止它在升級時執行此操作，或者您可以替換DELETE。PostgreSQL 11將支持複製TRUNCATE，但這隻有在源和目標實例都是PostgreSQL 11或更新版本時才有效。
2、只支持普通表的DML(INSERT、UPDATE、DELETE)操作,不支持truncate、DDL操作
3、需要同步的表必須設置 REPLICA IDENTITY 不能為noting(默認值是default),同時表中必須包含主鍵，否則delete和update報錯
4、一個publisher可以包含一張或多張表，一張表可以有一個或多個publishers
5、一個發佈者可以有多個訂閱者訂閱，一個訂閱者也可以同時訂閱多個發佈者，在同一個數據庫下訂閱者不能對同一個發佈者的表重複訂閱（避免數據衝突）
6、邏輯複製不同於流複製，不是嚴格的主從關係，訂閱者端的普通表依然可以進行增刪改操作
7、同步表的表結構需要在發佈者和訂閱者兩邊保持一致（列的順序允許不一樣，但是列對應的數據類型必須一致）
8、如果訂閱者端的數據被誤刪，想要從發佈者重新copy同步表的數據，只能以重建同步表所在的訂閱者的方式來實現
其它注意事項：
publication – 發佈者
邏輯複製的前提是將數據庫 wal_level 參數設置成 logical；
源庫上邏輯複製的用戶必須具有 replicatoin 或 superuser 角色；
邏輯複製目前僅支持數據庫表邏輯複製，其它對象例如函數、視圖不支持；
邏輯複製支持DML(UPDATE、INSERT、DELETE)操作，TRUNCATE 和 DDL 操作不支持；
需要發佈邏輯複製的表，須配置表的 REPLICA IDENTITY 特性；
一個數據庫中可以有多個publication，通過 pg_publication 查看；
允許一次發佈所有表，語法： CREATE PUBLICATION alltables FOR ALL TABLES;
subscription – 訂閱者
訂閱節點需要指定發佈者的連接信息；
一個數據庫中可以有多個訂閱者；
可以使用enable/disable啟用/暫停該訂閱；
發佈節點和訂閱節點表的模式名、表名必須一致，訂閱節點允許表有額外字段；
發佈節點增加表名，訂閱節點需要執行： 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
