渡鴉之丘: Postgres 10 的 Logical Replication 操作筆記

在 PGSQL 10 引進了一個重要的新功能，透過 WAL 進行的資料表層級的同步功能。這個功能可以
在 PGSQL 10 之後的版本之間直接跨版本同步資料，或是用 pglogical以向下同步
跨作業系統平台間（Windows，Linux，IBM PowerLinux LE，Mac，32/64 位元）同步資料
同步指定表格資料

原本在 PGSQL 提供的相類似方案，是基於 Trigger 的 Slony，或是 PGSQL9.4 之後的 pglogical 模組，以及 EnterpriseDB 的 Replication Server。現在內建在資料庫裡面，除了 Streaming Replication 之外，還支援這個新功能，增加配置的彈性，簡化配置的作業。

Logical Replication 運作的架構：
分為發布端（Publisher）與訂閱端（Subcriber），在發布端有一個程序，負責透過 Logical Replication Slot 派送 WAL；到訂閱端由 WAL Receiver 程序收取來源端的 WAL Segment，從被同步表格 Database 的交易紀錄中過濾該表格的資料變動。此外，一個發布端可以送資料到多個訂閱端；一個訂閱端也可以同時接收數個發布端的資料。
由於 Logical Replication 的特性緣故，發布端與接收端之間網路必須要互通才行，因此若是位在相互隔離的子網路，就無法進行 Logical replication。

Logical Replication 與 Streaming Replication 是個別的功能，因此一個資料庫，可以同時是 Streaming Rplication Master，同時作為 Logical Replication Publisher。不過 Streaming Rplication Standby 只有唯讀的功能，因此不能當 Logical Replication Subscriber。

PostgreSQL 10
Logical Replication

要求

發布端 Publisher

不能用 Streaming Replication Standby 作為發布端
需要留 Replication Slot 的量
（建議）表格需要有 Primary Key 或 Unique Key

訂閱端 Subscriber

不能是同一個資料庫 Instance
不可能用 Streaming Replication Standby 作為訂閱端

以下是目前 PGSQL 10 支援的組合

Streaming → Logical ↓	Master	Standby
Publisher	Ok	×
Subscriber	Ok	×

除了 Streaming Replication Standby 先天就是 Read-only Instance，不可能作為 Logical Replication Subscriber 之外，或許可以期待未來有類似 Cascading Streaming Replication 一樣，用 Streaming Replication Standby 作為 Logical Replication Publisher 的功能～

以下為進行設置的練習紀錄

首先，在兩台 CentOS 7.4 的 VM 準備兩個 Postgres Instance，PGDATA 都位在 /var/lib/postgres/10/data/，以下是快速環境建立小抄

sudo yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
sudo yum install -y postgresql10*
sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
sudo service postgresql-10 start

以下的練習紀錄，共有兩部主機，分別用深綠色與深藍色表示
Publisher IP：10.128.0.6
Subscriber IP：10.128.0.7

Publisher DB 的 pg_hba 設定：使用普通的連線規則就好，不用 replication 協定，這裡設定子網段之間可互通

postgres=# select * from pg_hba_file_rules ;
 line_number | type  |   database | user_name |  address   |              netmask              | auth_method | options | error
-------------+-------+---------------+-----------+------------+-----------------------------------------+-------------+---------+-------
       80 | local | {all}      | {all}  |         |                                      | peer     |      |
       82 | host  | {all}      | {all}  | 127.0.0.1  | 255.255.255.255                      | ident    |      |
       83 | host  | {all}      | {all}  | 10.128.0.6 | 255.255.255.0                        | trust    |      |
       85 | host  | {all}      | {all}  | ::1     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | ident    |      |
       88 | local | {replication} | {all}  |         |                                      | peer     |      |
       89 | host  | {replication} | {all}  | 127.0.0.1  | 255.255.255.255                      | ident    |      |
       90 | host  | {replication} | {all}  | ::1     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | ident    |      |
(7 rows)

postgres=#

首先，先在 Publisher DB 裡面進行系統設定：主要是先調整 wal_level 使之允許 logical replication。
由於從 PGSQL10 開始，已經預設啟用 max_wal_senders 與 max_replication_slots 等參數為 10 個，因此這裡不用再設定。

-bash-4.2$ cat << EOF | psql
ALTER SYSTEM SET listen_addresses to '*';
ALTER SYSTEM SET wal_level = 'logical';
SHOW max_wal_senders;
SHOW max_replication_slots;
EOF
ALTER SYSTEM
 max_wal_senders 
-----------------
 10
(1 row)

 max_replication_slots 
-----------------------
 10
(1 row)

-bash-4.2$ /usr/pgsql-10/bin/pg_ctl -D $PGDATA restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2018-02-28 04:25:02.889 UTC [29772] LOG:  listening on IPv6 address "::1", port 5432
2018-02-28 04:25:02.889 UTC [29772] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-02-28 04:25:02.891 UTC [29772] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-02-28 04:25:02.894 UTC [29772] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-02-28 04:25:02.905 UTC [29772] LOG:  redirecting log output to logging collector process
2018-02-28 04:25:02.905 UTC [29772] HINT:  Future log output will appear in directory "log".
 done
server started
-bash-4.2$

然後建立被複製的測試表格

CREATE TABLE test(
id int primary key,
txt text
);
INSERT INTO test SELECT generate_series(1,10), random()::text;

postgres=# select * from test;
 id |     txt      
----+--------------------
  1 | 0.924086822196841
  2 | 0.323981294874102
  3 | 0.0224499166943133
  4 | 0.664758448954672
  5 | 0.676995183806866
  6 | 0.97190154530108
  7 | 0.42196790734306
  8 | 0.479063506238163
  9 | 0.515575231984258
 10 | 0.688043723814189
(10 rows)

postgres=#

以上只有建立最簡單的狀況：針對 Logical Replication，一個表格有四種 REPLICA IDENTITY 參數，可以控制準備用來作「發布端/訂閱端比對」的變動前資料（會寫入 WAL 內），作為同步時 UPDATE/DELETE 檢查條件：

USING INDEX ：使用指定的 NOT NULL Constraint 的欄位資料作 UPDATE/DELETE 條件檢查
FULL ：此選項允許使用沒有 Unique Key / Primary Key 的表格作為發布端來源，會使用整筆資料作為同步時檢查的 Key，作為異動/刪除的檢查條件。會太佔用 WAL 大小
NOTHING ：不作任何紀錄到 WAL 內
DEFAULT：對非系統表，會用表格的 Primary Key 的資料作 UPDATE/DELEtE 條件比對；而對於系統表則是 NOTHING，也就是不做任何紀錄

原本下一步要建立 Logical Replication Slot，但是 Logical Replication 指令有貼心的設計，可以透過 Logical Replication 指令進行指定，所以這裡可以省去。

接著建立發布集合：這裡可以指定要同步的 DML 類型，預設是新增修改刪除全部，若有西藥的話，也可以依照需求選填。這裡把預設的狀況明顯地寫出來～

CREATE PUBLICATION testpub FOR TABLE test
WITH (publish = 'insert, update, delete');

接著查看系統表：系統表有靜態的設置資訊，以及同步的即時狀態（即 pg_stat_replication）兩部份。目前還沒有同步活動發生

postgres=# select * from pg_publication;
 pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
---------+----------+--------------+-----------+-----------+-----------
 testpub |    10 | f         | t      | t      | t
(1 row)

postgres=# select * from pg_publication_tables ;
 pubname | schemaname | tablename
---------+------------+-----------
 testpub | public  | test
(1 row)

postgres=#
postgres=# select * from pg_stat_replication;
 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------
(0 rows)

postgres=#

以上便把發布端設定完畢。
現在換到 Subscriber DB 進行接收的設定。
Subscriber DB 的設定，其實跟 Foreign Data Wrapper（FDW）的設定風格很像～
不過這裡需要先建立空的實體表格，才建立 Publication 連線設定（對比 FDW，是先建立連線資訊與遠端登入帳密，才匯入外部表）。

這裡把一樣的表格 DDL 拿來執行：

CREATE TABLE test(
id int primary key,
txt text
);

然後建立 Subscription：這裡把選項都明顯地寫出來

postgres=# CREATE SUBSCRIPTION testsub 
CONNECTION 'host=10.128.0.6 dbname=postgres' 
PUBLICATION testpub
WITH (copy_data='true',create_slot='true', 
 enabled='true',synchronous_commit='off');
NOTICE:  created replication slot "testsub" on publisher
CREATE SUBSCRIPTION
postgres=# select * from test;
 id |     txt      
----+--------------------
  1 | 0.924086822196841
  2 | 0.323981294874102
  3 | 0.0224499166943133
  4 | 0.664758448954672
  5 | 0.676995183806866
  6 | 0.97190154530108
  7 | 0.42196790734306
  8 | 0.479063506238163
  9 | 0.515575231984258
 10 | 0.688043723814189
(10 rows)

postgres=#

上面可以看到資料初步被同步完成。
接著就可以測試同步～
一）在 Publisher DB 上面新增資料

INSERT INTO test SELECT generate_series(11,20), random()::text;

在 Subscriber DB 上面就會收到資料

postgres=# select * from test;
 id |        txt         
----+--------------------
  1 | 0.924086822196841
  2 | 0.323981294874102
  3 | 0.0224499166943133
  4 | 0.664758448954672
  5 | 0.676995183806866
  6 | 0.97190154530108
  7 | 0.42196790734306
  8 | 0.479063506238163
  9 | 0.515575231984258
 10 | 0.688043723814189
 11 | 0.0687491330318153
 12 | 0.185999659821391
 13 | 0.529485877137631
 14 | 0.488357505295426
 15 | 0.802844601217657
 16 | 0.392165890429169
 17 | 0.333068483509123
 18 | 0.183304892852902
 19 | 0.874984054360539
 20 | 0.706090013496578
(20 rows)

postgres=#

二）如果在 Subscriber 上面填資料，同步會發生什麼事？
首先在 Subscriber 上面塞一筆資料

postgres=# insert into test values(21,'subscriber');
INSERT 0 1
postgres=#

回頭在 Publisher 輸入同樣一筆 Primary Key 的資料，id 為 21 的資料在接收端已經被建立了

postgres=# INSERT INTO test SELECT generate_series(21,30), random()::text;
INSERT 0 10
postgres=# select * from test;
 id |     txt      
----+--------------------
  1 | 0.924086822196841
  2 | 0.323981294874102
  3 | 0.0224499166943133
  4 | 0.664758448954672
  5 | 0.676995183806866
  6 | 0.97190154530108
  7 | 0.42196790734306
  8 | 0.479063506238163
  9 | 0.515575231984258
 10 | 0.688043723814189
 11 | 0.0687491330318153
 12 | 0.185999659821391
 13 | 0.529485877137631
 14 | 0.488357505295426
 15 | 0.802844601217657
 16 | 0.392165890429169
 17 | 0.333068483509123
 18 | 0.183304892852902
 19 | 0.874984054360539
 20 | 0.706090013496578
 21 | 0.214609659742564
 22 | 0.976877133827657
 23 | 0.719678375869989
 24 | 0.106605648994446
 25 | 0.872024555690587
 26 | 0.874162934720516
 27 | 0.497271074913442
 28 | 0.842536006588489
 29 | 0.719646297395229
 30 | 0.407564226072282
(30 rows)

postgres=# select * from pg_stat_replication ;
 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------
(0 rows)

postgres=#

上面我們同時查看同步狀況，發現同步好像停下來了。。。

趕緊回頭看 Subscriber

postgres=# select * from test;
 id |     txt      
----+--------------------
  1 | 0.924086822196841
  2 | 0.323981294874102
  3 | 0.0224499166943133
  4 | 0.664758448954672
  5 | 0.676995183806866
  6 | 0.97190154530108
  7 | 0.42196790734306
  8 | 0.479063506238163
  9 | 0.515575231984258
 10 | 0.688043723814189
 11 | 0.0687491330318153
 12 | 0.185999659821391
 13 | 0.529485877137631
 14 | 0.488357505295426
 15 | 0.802844601217657
 16 | 0.392165890429169
 17 | 0.333068483509123
 18 | 0.183304892852902
 19 | 0.874984054360539
 20 | 0.706090013496578
 21 | subscriber
(21 rows)

postgres=# 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
-------+---------+-----+-------+--------------+--------------------+-----------------------+----------------+-----------------
 16398 | testsub |  |    |           |                 |                    |             |
(1 row)

postgres=#

我們會發現同步中斷了，查看即時同步狀況的系統表也暫停了。。。

不過要是我們把 Subscriber 衝突的資料刪掉，同步就會正常：

postgres=# delete from test where id=21 and txt='subscriber';
DELETE 1
postgres=# select * from test;
 id |     txt      
----+--------------------
  1 | 0.924086822196841
  2 | 0.323981294874102
  3 | 0.0224499166943133
  4 | 0.664758448954672
  5 | 0.676995183806866
  6 | 0.97190154530108
  7 | 0.42196790734306
  8 | 0.479063506238163
  9 | 0.515575231984258
 10 | 0.688043723814189
 11 | 0.0687491330318153
 12 | 0.185999659821391
 13 | 0.529485877137631
 14 | 0.488357505295426
 15 | 0.802844601217657
 16 | 0.392165890429169
 17 | 0.333068483509123
 18 | 0.183304892852902
 19 | 0.874984054360539
 20 | 0.706090013496578
 21 | 0.214609659742564
 22 | 0.976877133827657
 23 | 0.719678375869989
 24 | 0.106605648994446
 25 | 0.872024555690587
 26 | 0.874162934720516
 27 | 0.497271074913442
 28 | 0.842536006588489
 29 | 0.719646297395229
 30 | 0.407564226072282
(30 rows)

postgres=# 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     
-------+---------+------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
 16398 | testsub | 5924 |    | 0/16B8F58 | 2018-03-01 16:05:50.981718+00 | 2018-03-01 16:05:50.982763+00 | 0/16B8F58   | 2018-03-01 16:05:50.981718+00
(1 row)

postgres=#

回頭看 Publisher 也會發現 Replication 恢復了

postgres=# select * from pg_stat_replication ;
 pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |      backend_start      | backend_xmin |  state  | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+---------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------
 6084 |    10 | postgres | testsub       | 10.128.0.7  |              |    60586 | 2018-03-01 16:03:30.651586+00 |           | catchup | 0/16B8E78 | 0/16B8E78 | 0/16B8E78 | 0/16B8E78  |        |        |         |          0 | async
(1 row)
postgres=#

上面的狀況，由於 Replication Slot 會不斷等待同步的恢復，所以會造成發布端的 WAL 檔案一直被留在 pg_wal/ 裡面。若一直沒處理，可能導致 pg_wal 滿出來，使資料庫停止運作...

三）接收端的資料被更新，之後來源端又被更新
先在接收端更新

postgres=# update test set txt='change' where id=30;
UPDATE 1
postgres=#

然後到發布端更新同一個 id 的資料

postgres=# update test set txt='hello' where id=30;
UPDATE 1
postgres=#

回到接收端看看會怎樣

postgres=# select * from test ;
 id |  txt  
----+-------
 30 | hello
(1 row)

postgres=#

結論：來源異動會覆蓋掉

四）接收端的某筆資料被刪除，後來在來源端同一筆資料發生更新
此時，由於在接收端已經沒有標的了，更新語句套用上去，只有發生 UPDATE 0，因此無從被更新：肉既然被割掉了，就沒得變動了（比喻用的怪怪的）～

五）接收端的表格被 TRUNCATE 會怎樣
來源端發生 TRUNCATE，不會被傳遞到下游的接收端，這是比較特別的地方。
原因是 Logical Replication 只有同步 DML 語句（INSERT，UPDATE，DELETE），不同步 DDL 語句，而 TRUNCATE 是一個 DDL 語句，需要特別有給相關權限才能進行～
Update：PGSQL 11 支援 TRUNCATE 了，使用上要注意～

六）接收端的表格被 TRUNCATE 會怎樣
當接收端的內容被 TRUNCATE 清除之後，只會從此後新增的資料被同步到接收端。接收端被刪掉的內容，對應回發布端的資料，就算有進行更新，也不會讓接收端的相對應資料長出來。當然發布端也刪掉的話，同步到接收端也無事～

注意

直接把上述步驟操作在 Windows 跟 Linux 之間同步嘛ㄟ通（Mac 也行）～

目前 PGSQL10 為單向同步。如果需要雙向同步，現階段只能考慮企業版的 EDB Replication Server 或是 2ndQuadrant Bi-Directional Replication

沒辦法同一個 Instance 的不同 database 之間進行 logical replication，目前在 PGSQL10 會造成迴圈的效應（會這樣用也很怪～）

初始同步的 Snapshot COPY 會進入 Temp Replication Slot 進行，進行完 COPY 之後，會再用 COPY 期間產生的 WAL 補上變動，最後才進入穩定的 Logical Replication。因此可以免除初始複製發生資料不一致的問題～

目前還沒有支援從 Streaming Replication 的 Read-Only Standby 建立 Logical Replication 的功能

WAL log 內容，會依照 instance 的個別 Database 去派送，因此不建議拿

塞很多表格而且

活動很大的 Database 作為發布端

以免 WAL 太大量，卻只有過濾一點點要同步的內容

接收端同步的資料表可以被讀寫，但是資料衝突會造成暫停

來源端的新增資料總是會同步到接收端
接收端對同步過來的資料作更新，會被來源端的更新覆蓋掉
接收端對同步過來的資料進行刪除，不再會有來源端資料的異動施加在相對應的 Tuple

表格訂閱集（Publication Set）如果要增減表格的話，可以用 ALTER PUBLICATION ADD/DROP TABLE 處理

刪除接收端整張表格，後續需要重新初始化（Refresh）：ALTER SUBSCRIPTION REFRESH PUBLICATION

以下為上面驗證行為的結果表：對於一個有 Primary Key 的表格會面對的情況（Replication Identity 為 DEFAULT）

Publication → Subscription ↓	INSERT	UPDATE	DELETE	TRUNCATE
無變動	同步	同步	同步	不同步
先 INSERT 衝突	同步停止等衝突解除	N/A (無從作 UPDATE)	N/A (無從作 DELETE)	不同步
先 UPDATE 衝突	N/A (無從作 UPDATE)	發布端覆蓋接收端	發布端覆蓋接收端	不同步
先 DELETE 衝突	同步	不同步	無事	不同步
先 TRUNCATE	同步	不同步	無事	無事

參考資料

Demystifying Postgres Logical Replication - YouTube

Demystifying-Postgres-Logical-Replication (pdf) (pdf2)

The Easy Way to Setup PostgreSQL 10 Logical Replication | OpenSCG

Logical replication with PostgreSQL 10 – Hacker Noon

Highlighting Postgres 10 new features: Logical Replication and Partitioning. | 3manuek.github.io

Logical Replication in PostgreSQL 10 - OpsDash

select * from depesz; » Blog Archive » Waiting for PostgreSQL 10 – Logical replication

pglogical 2.1 and Logical Replication in PostgreSQL 10 | 2ndQuadrant Blog

How to Setup a Logical Replication on PostgreSQL 10 – YallaLabs

PostgreSQL 10 Beta 1: Logical replication - Blog dbi services

Postgresql and logical replication - Raveland

Postgres Miktzoanim Israel | What’s new in PostgreSQL 10? Part 3 – Native Logical Replication

PGCon2017: Logical Replication in PostgreSQL 10

Logical Replication in PostgreSQL 10 // Speaker Deck

Logical replication - PostgreSQL Administration Cookbook, 9.5/9.6 Edition

PostgreSQL 10: Setting up Logical Replication · zaiste.net

postgres 10 logical replication – shadman – Medium

In-core logical replication will hit PostgreSQL 10 - Blog dbi services

PostgreSQL 10.0 逻辑复制原理与最佳实践-博客-云栖社区-阿里云

Holly Orr: The Easy Way to Setup PostgreSQL 10 Logical Replication – Cloud Data Architect

Are large objects supported in PostgreSQL 10 logical replication? - Blog dbi services

PostgreSQL 10 - Logical replication | Cộng đồng PostgreSQL Việt Nam's blog

PostgreSQL: Documentation: 10: Chapter 31. Logical Replication

PostgreSQL: Documentation: 10: 52.5. Logical Streaming Replication Protocol

Logical Replication with PostgreSQL 10 - YouTube (pdf)

database - Difference between Stream Replication and logical replication - Stack Overflow

postgresql 10 pub/sub使用简明教程-博客-云栖社区-阿里云

Postgresql and logical replication - Raveland Blog

An Overview of Logical Replication in PostgreSQL | Severalnines

包含運作架構圖的文章

PostgreSQL 10 and Logical replication - Overview | Select * from Adrien

PGConf.ASIA 2017 Logical Replication Internals (English) (日文版～)

效能影響：下面兩篇寫的蠻仔細的～（感謝 PostgreSQL.TW 的社群～）

Logical replication internals | Select * from Adrien

Performance limits of logical replication solutions | 2ndQuadrant

2 則留言:

Unknown2019年5月29日晚上10:44
請問 logical replication 是一個一個table 去新增到發布？不能新增整個DB嗎？
回覆刪除
回覆
長滿渡鴉的山丘2019年6月3日清晨6:02
可以參考一下手冊喔
https://www.postgresql.org/docs/11/logical-replication-publication.html
裡面提到 "Objects must be added explicitly, except when a publication is created for ALL TABLES."

這邊也有一點關於增加所有表格到 Logical Replication 的教學分享
https://pgdash.io/blog/postgres-replication-gotchas.html
回覆刪除
回覆

新增留言

渡鴉之丘

MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

2018年4月30日星期一

Postgres 10 的 Logical Replication 操作筆記

2 則留言:

MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

2018年4月30日 星期一

Postgres 10 的 Logical Replication 操作筆記

2 則留言:

2018年4月30日星期一