一.邏輯複寫 介紹
邏輯複寫（Logical Replication）
是一種依據複寫指標（通常是主鍵）複製資料物件及其更新的方法。
我們使用術語邏輯與物理複寫相對比，
物理複寫使用確切的區塊位址進行每一個字元組的複寫。
所以 subscribe 訂閱方 是真的會有資料在上頭, 可建立index
邏輯複寫使用 publish 發布（來源）和 subscribe 訂閱（目標）模式，
你把它想成 publish 是主，subscribe 是備
一個 publish 可以多個 subscribe
邏輯複寫設定好後，發佈者的變化就會即時發送給訂閱者。
訂閱者按照與發佈者所執行的SQL順序依序執行SQL
以確保單個訂閱內的發佈的交易事務一致性。
這種資料複寫方法有時被稱為交易事務複寫。
(有點像mysql 的那種主從設定, 所以會有資料衝突發生)
邏輯複寫 是從 Postgres 10以上開始支援 , 通常使用的時機為
1.將多個資料庫合併成一個資料庫（例如為了分析的需求）。
2.在不同的 PostgreSQL 版本之間複寫。
3.在不同平台（例如 Linux 到 Windows）上的 PostgreSQL 伺服器之間的複寫
4.將複寫的資料給予不同的使用者群組存取權限。
5.在多個資料庫之間共享資料庫的一部份。
所以說 想要升級Postgres版本也可以使用 Logical Replication 做資料移轉的動作
downtime 會比使用 pg_upgrade 更短
以下是 10 (publish ) to 14 (subscribe)
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 10.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=#
二.publish 配置相關設定
---- publish 主庫新增同步用帳號
CREATE USER logical_repl REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'logical_repl';
---- 可以不需要SUPERUSER，但要logical_repl帳號對這些表有Select授權
----修改 postgresql.conf
wal_level = logical
max_wal_senders = 10
max_replication_slots = 8
----修改 pg_hba.conf
host all logical_repl 10.10.10.2/32 md5
------ 只同步指定的表
CREATE PUBLICATION pub1 FOR TABLE dba_test;
CREATE PUBLICATION pub1 FOR TABLE dba_test WITH (publish = 'insert, update, delete');
---- 同步全表
CREATE PUBLICATION pub1 FOR ALL TABLES ;
--- 授權
GRANT USAGE ON SCHEMA public TO logical_repl;
GRANT SELECT ON dba_test TO logical_repl;
--- 同步狀態
SELECT * FROM pg_publication;
dba=#
dba=# SELECT * FROM pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
---------+----------+--------------+-----------+-----------+-----------
pub1 | 18397765 | f | t | t | t
(1 row)
dba=#
dba=#
pubname： 指釋出的名稱
pubowner： 指釋出的屬主，可以和pg_user檢視的usesysid欄位關聯查詢得到屬主具體資訊
puballtables：是否釋出資料庫中的所有表，t表示釋出資料庫中所有已存在的表和以後新建的表
pubinsert： t表示僅釋出表上的INSERT操作
pubupdate： t表示僅釋出表上的UPDATE操作
pubdelete： t表示僅釋出表上的DELETE操作
------ 查詢 訂閱的表
SELECT * FROM pg_publication_tables;
dba=# SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename
---------+------------+-----------
pub1 | public | dba_test
(1 row)
dba=#
dba=#
三.subscribe 配置相關設定
dba=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
dba=#
dba=#
dba=#
----修改 postgresql.conf
wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8
** 備庫不一定要設定 wal_level = logical , 只是這邊預防先做設定
** max_logical_replication_workers 設定邏輯複製程序數，應大於訂閱節點的數量，並且給表同步預留一些程序數量
這邊 subscribe 已經有建立與 publish 相同的table
------------------- 在備庫上 開啟 訂閱同步
CREATE SUBSCRIPTION sub1 CONNECTION 'host=10.10.10.1 port=5432user=logical_repl dbname=dba password=logical_repl' PUBLICATION pub1;
---- 測試 , 只要在主庫 publish 新增資料 ,備庫 subscribe 就會立刻出現資料
--- 查詢目前在 subscribe 備庫 已經有設定的資訊
dba=#
dba=# SELECT * FROM pg_subscription;
oid | subdbid | subname | subowner | subenabled | subbinary | substream | subconninfo | subslotname | subsynccommit | subpublications
-------+---------+---------+----------+------------+-----------+-----------+--------------------------------------------------------------------------------+-------------+---------------+-----------------
16464 | 16452 | sub2 | 10 | t | f | f | host=10.10.10.1 port=5432user=logical_repl dbname=dba password=logical_repl | sub2 | off | {pub1}
(1 rows)
dba=#
要特別注意 這邊有出現 logical_repl 的密碼 @@"
--- 查詢目前在 subscribe 備庫,同步的狀態
dba=#
dba=# select * from pg_stat_subscription ;
subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time
-------+---------+-------+-------+---------------+-------------------------------+-------------------------------+----------------+-------------------------------
16464 | sub2 | 20693 | | 57BC/3894A220 | 2022-01-07 12:07:01.734512+08 | 2022-01-07 12:06:36.672786+08 | 57BC/3894A220 | 2022-01-07 12:07:01.734512+08
(1 rows)
dba=#
包含最後的 lsn 及 最後一次同步時間 ,可以看出它有沒有在正常同步
四.未來要新增同步的表
--- 主庫後續新增 要同步的表
GRANT SELECT ON dba_test2 TO logical_repl;
ALTER PUBLICATION pub1 ADD TABLE dba_test2 ;
----- 新增完後 ,subscribe 備庫要在執行指令 更新資料
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
四.衝突解決
subscribe 其實是 write read 狀態下, 所以可以對同步過來的 table 進行異動
當同步中 subscribe 有出現 違反任何限制 pk uni key constraints , 同步就會停止 。
但當同步 UPDATE 或 DELETE 操作時 , 但subscribe 沒有該資料時, 並不會產生衝突,而這些操作將會簡單地跳過。
衝突會產生錯誤並停止複寫；它必須由使用者手動解決。
有關衝突的詳細訊息可以在使用者的伺服器日誌中找到。
解決方案有二
1.跳過與現有資料衝突的交易事務
透過呼叫 pg_replication_origin_advance() 函數以及與訂閱名稱和位置相對應的 node_name，可以跳過該交易事務。可以在 pg_replication_origin_status 系統檢視表中看到開始的目前位置。
2.修正subscribe 方table內的資料
---- 先關閉 訂閱 , 在備庫 先刪除資料 然後再開啟訂閱
alter subscription sub1 disable;
delete from t1 where id=3;
alter subscription sub1 enable ;
類似這種處理方式
五.限制及備注
1.資料庫結構和 DDL 指令不會被複寫。後續的結構變更需要手動保持同步。（但是，請注意，兩側的結構沒有必要完全相同。）當主要資料庫中的結構定義變更時，邏輯複寫是沒問題的：當發佈者上的結構産生變更並且複寫的資料開始到達訂閱戶但不符合資料表結構，複寫將産生錯誤，直到結構更新。在許多情況下，可以透過先將預定的架構變更套用於訂閱戶來避免間歇性的錯誤。
2.支援同步 TRUNCATE 指令，使用必須格外小心。
3. TABLE（包括 PARTITION TABLE）支援同步。
其他例如檢視表 VIEW，具體化檢視圖 MATERIALIZED VIEW 或外部資料表 FOREIGN TABLE，將會導致錯誤。
4.初期快照:執行同步指令後,table會先進行一次完整的資料同步,同步完後就會進行同步模式
初次同步subscribe 方會額外出現 process 開始同步
postgres: logical replication worker for subscription 16405 sync 16468
5.由於subscribe方是真實的table,可以額外新增index及數據修改
