Postgres - logical replication 示範

發表於 更新於 分類於 Disqus： 文章字數： 4.9k 所需閱讀時間 ≈ 9 分鐘

今天來記錄一下 logical replication 實際 demo 的方式，之後會先出一系列實際架設各種 Postgres 內的功能來玩玩。

What is Logical Replication in Postgres

Logical Replication 功能是從 Postgres 10.0 開始的，這個概念其實是相對於 Physical Replication 的功能，以前在做 Replication 的時候都是採用 Physical Replication，是要將整個 wal 上面的資料 copy 到 replication server 上，所以吃的是實際底層的儲存結構，但是 Logical Replication 則是 send SQL command 給 replication server 來得到同樣的結果。

那 Physical Replication 的缺點在於：

  1. replica server 會需要接收到所有 database, table, role 等所有資料，但是無法 replicate 單一個 table 之類的功能，當然只能借助於其他 third-party 的 plugin 才能做到，但是 logical replication 就能做到 only replicate single table 的功能。
  2. 不同版本的 Postgres Server 是不能進行 physical replication 的，但是 logical replication 可以
  3. physical replication copy 過去的 data 兩邊的 Postgres 的位置要一模一樣，但 logical replication 沒有這種限制

Logical Replication 的原理

Postgres 會利用 Decoding Plugin 將 WAL Records decode 為 statement，接著透過 WAL Sender 送到 replication server，在 apply statement 到對應的 table 上。

所以概念分成兩段，一段叫做 publisher，另一段叫做 subscriber，因此一個 publisher 其實可以被多個 subscriber 來 subscribe，概念跟 message queue 有異曲同工之妙，當然也可以有多個 publisher to one subscriber 的方式，根據我們想要的需求去而定。

但 logical replication 缺點在於：

  1. 無法支援 publish DDL 操作，只能支援一般的 CRUD，因此如果今天我在 publisher db 上新增了一個 column，那麼我在 subscriber 也要手動新增一個 column 才可以對應到，但如果你先改了 publisher db 但 subscriber db 卻還沒更動 schema，那麼 replication 過程就會出現錯誤，無法正常的 sync，因此會建議先在 subscriber db 做 column 的改動，再 apply 到 publisher db，才不會造成 sync 上的錯誤
  2. 如果 copy 的 column 是那種 series column，例如 auto increment，是無法複製過去的： the sequence itself would still show the start value on the subscriber. 但如果 subscriber 只能拿來當作 read only 那應該是沒差。
  3. 從 Postgres 13 開始支援 partitioned tables，但是如果是 replicate other types of relations, such as views, materialized views, or foreign tables, will result in an error.

Logical Replication Demo

來示範一下怎麼在 localhost 架設簡單的 logical replication 的範例：

  1. 建立兩個 Postgres Server，分別是 publication 跟 subscription 之用途

    1
    2
    initdb -D /tmp/publication_db
    initdb -D /tmp/subscription_db

  2. 接著更改兩個 Postgres Server port 分別聽 5433 跟 5434，以及 publication 這台更改 wal_level 為 logical

    1
    2
    3
    nano /tmp/publication_db/postgresql.conf                                   
    wal_level = logical                     # minimal, replica, or logical
    port = 5433
    1
    2
    nano /tmp/subscription_db/postgresql.conf                                   
    port = 5433

  3. 啟動這兩台 Postgres Server

    1
    2
    pg_ctl -D /tmp/publication_db start
    pg_ctl -D /tmp/subscription_db start

  4. 透過 psql 進入 publication 這台 postgres server，並建立 table 及 records

    1
    2
    3
    4
    psql --port=5433 postgres
    create database pub;
    insert into table_1 values(generate_series(1,10),'data'||generate_series(1,10));
    select * from table_1;

  5. 透過 psql dump 剛建立好的 table 給 subscription postgres server

    1
    pg_dump -t table_1 -s pub -p 5433 | psql -p 5434 sub

  6. 透過 psql 進入 publication 這台 postgres server，並建立 publication：

    1
    create publication mypub for table table_1;

  7. 透過 psql 進入 subscription 這台 postgres server 並建立 subscription：

    1
    create subscription mysub connection 'dbname=pub host=localhost user=kenny port=5433' publication mypub;

    這時可以看到 log：

    1
    2
    3
    4
    5
    6
    7
    NOTICE:  created replication slot "mysub" on publisher                  
    CREATE SUBSCRIPTION                                                     
    sub=# 2022-09-25 15:47:40.646 CST [76376] LOG:  logical replication apply worker for subscription "mysub" has started                           
    2022-09-25 15:47:40.656 CST [76378] LOG:  logical replication table sync
    hronization worker for subscription "mysub", table "table_1" has started
    2022-09-25 15:47:40.671 CST [76378] LOG:  logical replication table sync
    ronization worker for subscription "mysub", table "table_1" has finished

    可以看到 sync table_1 的工作已經完成了

    1
    select * from table_1;

    就能看到 sync 過來的資料了。

可以透過 pg_stat_replication 來監控 replication 的進度：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
pub=# select * from pg_stat_replication;        
-[ RECORD 1 ]----+------------------------------
pid              | 76377                        
usesysid         | 10                           
usename          | kenny                        
application_name | mysub                        
client_addr      | ::1                          
client_hostname  |                              
client_port      | 51943                        
backend_start    | 2022-09-25 15:47:40.648956+08
backend_xmin     |                              
state            | streaming                    
sent_lsn         | 0/16905A0                    
write_lsn        | 0/16905A0                    
flush_lsn        | 0/16905A0                    
replay_lsn       | 0/16905A0                    
write_lag        |                              
flush_lag        |                              
replay_lag       |                              
sync_priority    | 0                            
sync_state       | async                        
reply_time       | 2022-09-25 15:54:42.481277+08

pub=# \dRp          
List of publications
-[ RECORD 1 ]-----  
Name       | mypub  
Owner      | kenny  
All tables | f      
Inserts    | t      
Updates    | t      
Deletes    | t      
Truncates  | t

上面可以看到 write_log 跟 flush_lag 跟 replay_lag 它們代表的含義是：

  • write_lag：Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written it (but not yet flushed it or applied it).
  • flush_lag：Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it (but not yet applied it).
  • replay_lag：Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written, flushed and applied it.

Best use-case for Logical Replication

最適合應用於 Logical Replication 的情境有以下：

  1. 可以透過 logical replication 來同步不同 OS 下的 Postgres Server

  2. 可以使用多 Publisher to one subscriber 的情境，也可以很有彈性的控制哪些 operation 要被 replica 過去，例如只 replica insert or update operation，而 delete operation 不要。

  3. 可以拿來做 fine-grained control，控制某些欄位不要 replica 過去，適用於在 read only 的 Server 且只能開放某些欄位讓不同權限的 user 存取。

  4. logical replication 有更好的 performance，因為不需要 replicate physical level for operation 例如 vacuum and cluster 等指令

    VACUUM (both manual and auto) do not run on a physical replica. The results of a VACUUM running on the master get replicated over, just like other changes do. Per-table vacuum settings are stored in database catalog tables, and so get replicated over, but they have no effect unless/until the replica gets promoted.

    For logical replica, its VACUUM settings are completely separate from the publisher. Both the server-level and table-level settings are just whatever you set them to.

總結

今天主要是筆記 logical replication 的功能，沒有到很詳細，只是做個紀錄，之後會希望慢慢補齊每一個細節。