PostgreSQL物理複製的部署,其有如下主要優點

• 物理層面完全一致，是主要的複製方式，其類似於Oracle的DG
• 延遲低，事務執行過程中產生REDO record，實時的在備庫apply，事務結束時，備庫立馬能見到資料
• 物理複製的一致性、可靠性高，不必擔心資料邏輯層面不一致

但是其又在實際使用的場景中存在一些無法滿足的需求，例如：

• 無法滿足指定庫或部分表的複製需求
• 將多個資料庫例項的資料匯聚到同一個目標庫或將一個庫的資料分發到多個不同的庫
• 不同的版本之間的複製
• 不同庫名之間的表同步
  對於以上場景,物理複製時無法滿足的，因此邏輯複製應運而生了。
  邏輯複製的複製架構圖如下：

邏輯複製是基於邏輯解析，其核心原理是邏輯主庫將Publication中表的WAL日誌解析成一定格式併傳送給邏輯備庫，邏輯備庫Subscription接收到解析後的WAL日誌後進行重做，從而實現表資料同步。

2. 邏輯複製的部署

PS： 以下的邏輯從庫可以在新的機器上部署，如在原先的從庫上修改，需停止原例項，並將recovery.conf檔案刪除或重新命名（如修改為recovery.conf.done）

2.1 修改主庫的postgresql.conf

可以基於原先的物理複製的配置檔案進行修改,配置邏輯複製主要需調整如下引數

wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

引數簡要說明如下
wal_level：設定成logical才支援邏輯複製
max_wal_senders：由於每個訂閱節點和流複製備庫在主庫上都會佔用主庫上一個WAL傳送程式，因此此引數設定值需大於max_replication_slots引數值加上物理備庫數量
max_replication_slots：設定值需大於訂閱節點的數量

2.2 修改邏輯從庫的postgresql.conf

邏輯從庫的postgresql.conf也可以在物理複製的基礎上修改，與主庫不同的是主要修改如下引數

wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

引數簡要說明
wal_level：設定成logical才支援邏輯複製,邏輯從庫可以視情況設定
max_replication_slots：設定資料庫複製槽數量，應大於訂閱節點的數量
max_logical_replication_workers：設定邏輯複製程式數，應大於訂閱節點的數量，並且給表同步預留一些程式數量，此引數預設值為4

2.3 建立邏輯複製賬號

lightdb@postgres=# alter USER test REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'test'; CREATE ROLE

邏輯複製使用者需要REPLICATION許可權即可，可以不需要SUPERUSER許可權，之後需要在釋出節點上將需要同步的表賦權給logical_repl使用者，使logical_repl賬號具有對這些表的讀許可權。

2.4 在邏輯主庫上建立庫及表

/** 建立用於邏輯複製的主庫 */ lightdb@postgres=# \c test testPassword for user test: 
You are now connected to database "test" as user "test".
test@test=> create schema test;CREATE SCHEMAtest@test=> create table test (id int,name varchar(100)) ;CREATE TABLE

2.5 邏輯從庫上建立庫及表

/** 在邏輯從庫上建立不同的庫 */lightdb@lt_test=# \c  test_subscription test_subscriptionPassword for user test_subscription: 
You are now connected to database "test_subscription" as user "test_subscription".
test_subscription@test_subscription=> create table test_subscription (id int,name varchar(100));CREATE TABLE

注：邏輯複製的表結構需要手動在從庫建立

2.6 在邏輯主庫上建立釋出

/** 在釋出主庫上建立釋出pub1,注意實在test庫下執行 */test@test=> CREATE PUBLICATION pub1 FOR TABLE test; 
CREATE PUBLICATION

如果需釋出多張表 則表名間用逗號（,）分割，如果需釋出所有庫，則將FOR TABLE 調整為FOR ALL TABLES。
此時可以檢視到如下資訊

test@test=> SELECT * FROM pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 24523 | pub1    |    24512 | f            | t         | t         | t         | t           | f
(1 row)

欄位說明如下：

• pubname： 指釋出的名稱
• pubowner： 指釋出的屬主，可以和pg_user檢視的usesysid欄位關聯查詢得到屬主具體資訊
• puballtables：是否釋出資料庫中的所有表，t表示釋出資料庫中所有已存在的表和以後新建的表
• pubinsert： t表示僅釋出表上的INSERT操作
• pubupdate： t表示僅釋出表上的UPDATE操作
• pubdelete： t表示僅釋出表上的DELETE操作

2.7 在邏輯從庫上建立訂閱

lightdb@lt_test=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test' PUBLICATION pub1;ERROR:  could not connect to the publisher: FATAL:  must be superuser or replication role to start walsender-- 在主庫上更改test使用者的許可權lightdb@postgres=# alter USER test REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'test'; ALTER ROLElightdb@lt_test=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test' PUBLICATION pub1;ERROR:  schema "test" does not exist
報錯 schema test不存在，說明發布端和訂閱段使用者名稱和庫可以不相同，但是表所在的schema名字必須相同，重建訂閱端表
test_subscription@test_subscription=> create table test.test_subscription(id int,name varchar(100));CREATE TABLElightdb@test_subscription=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test' PUBLICATION pub1;ERROR:  relation "test.test" does not exist
lightdb@test_subscription=# create table test.test(id int,name varchar(100));CREATE TABLElightdb@test_subscription=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test' PUBLICATION pub1;NOTICE:  created replication slot "sub1" on publisherCREATE SUBSCRIPTION

建立成功後，可以在邏輯主庫上查詢到如下資訊：

test@test=> SELECT slot_name,plugin,slot_type,database,active,restart_lsn    FROM pg_replication_slots where slot_name='sub1';
 slot_name |  plugin  | slot_type | database | active | restart_lsn 
-----------+----------+-----------+----------+--------+-------------
 sub1      | ltoutput | logical   | test     | t      | 0/23E902D0
(1 row)

在邏輯從庫上可以查詢到如下資訊：

lightdb@test_subscription# SELECT * FROM pg_subscription;
  oid  | subdbid | subname | subowner | subenabled |                            subconninfo                             | subslotname | subsynccommit | subpu
blications 
-------+---------+---------+----------+------------+--------------------------------------------------------------------+-------------+---------------+-----------------
 24283 |   24225 | sub1    |       10 | t          | hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test | sub1        | off           | {pub1
}
(1 row)

2.8 給複製賬號授權

如果不是同使用者的，需要將表給到publication使用者查詢許可權
因為步驟2.3中只建立了複製賬號並未對需要複製的表進行授權，因此，此時邏輯從庫的日誌中有如下錯誤
2023-02-14 15:57:21.847 CST [27443] ERROR: could not start initial contents copy for table “test.test”: ERROR: permission denied for table test
2023-02-14 15:57:21.848 CST [24722] LOG: background worker “logical replication worker” (PID 27443) exited with exit code 1
因此 ，現在需要對複製使用者授權。

sourcedb=# GRANT USAGE ON SCHEMA public TO test;GRANT sourcedb=# GRANT SELECT ON logical_tb1 TO test; GRANT

授權後,顯示正常
2023-02-14 16:00:25.959 CST [28204] LOG: logical replication table synchronization worker for subscription “sub1”, table “test” has started
2023-02-14 16:00:25.967 CST [28204] LOG: logical replication table synchronization worker for subscription “sub1”, table “test” has finished

2.9 測試資料同步

在邏輯主庫插入資料

/** 在主庫插入資料 */ alter table test add  primary key (id);ALTER TABLEtest@test=> insert into test values (1,'aaa');INSERT 0 1

在邏輯從庫檢視結果

/** 檢視資料是否同步完成 */ lightdb@test_subscription=# select * from test.test;
 id | name 
----+------
  1 | aaa
(1 row)

由此可見資料已同步完成。

2.10 新增複製所需的表

在邏輯主庫和邏輯從庫均新增一張新表，並新增到釋出列表中

/** 主庫上建立表結構 */ sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100)); CREATE TABLE sourcedb=# /** 從庫上建立表結構 */desdb=# create table logical_tb2(id int primary key ,addr varchar(100)); CREATE TABLE /** 在主庫上給邏輯複製賬號授權 */ sourcedb=# GRANT SELECT ON logical_tb2 TO logical_repl; GRANT/** 新增新表至釋出列表 */ sourcedb=# ALTER PUBLICATION pub1 ADD TABLE logical_tb2; ALTER PUBLICATION 
/** 在主庫檢視釋出列表中的表名 */sourcedb=# SELECT * FROM pg_publication_tables;
 pubname | schemaname |  tablename  
---------+------------+-------------
 pub1    | public     | logical_tb1
 pub1    | public     | logical_tb2
(2 rows)

此時已加入一張表進入釋出列表中。
此時在主庫寫入資料，檢視從庫情況如下：

/** 主庫插入一條記錄 */ sourcedb=# insert into logical_tb2(id,addr) values(1,'beijing'); INSERT 0 1 /** 此時在邏輯從庫檢視，結果卻沒有資料 */desdb=# select  * from logical_tb2;
 id | addr 
----+------(0 rows)

因為還需要在從庫重新整理一下訂閱

/** 此時在從庫重新整理訂閱 */ test@test=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;/** 重新整理完成後再查資料已經有資料了 */ ALTER SUBSCRIPTION
desdb=# select  * from logical_tb2;
 id |  addr   
----+---------
  1 | beijing
(1 row)

至此，PostgreSQL的邏輯複製也部署完畢。

3. 物理複製與邏輯複製特點和應用場景

PostgreSQL的邏輯複製與物理複製的差異比較突出，在使用中可以根據其特點選擇使用哪種複製方式。

• 邏輯訂閱，適合於釋出端與訂閱端都有讀寫的情況。
• 邏輯訂閱，更適合於小事務，或者低密度寫（輕度寫）的同步。如果有大事務、高密度寫，邏輯訂閱的延遲相比物理複製更高。
• 邏輯訂閱，適合於雙向，多向同步；但是 schema名和表名訂閱端必須和釋出端相同
• 物理複製，適合於單向同步。
• 物理複製，適合於任意事務，任意密度寫（重度寫）的同步。
• 物理複製，適合於HA、容災、讀寫分離。
• 物理複製，適合於備庫沒有寫，只有讀的場景。