複寫與高可用（Replication）：從 Streaming 到 Logical Replication | PostgreSQL

PostgreSQL 的複寫（Replication）是高可用架構的核心技術。透過將 Primary 節點的資料變更持續同步到 Standby 節點，系統在 Primary 故障時能快速切換。本文將完整解析 Physical Replication 與 Logical Replication 兩大機制，從設定到 Failover 全流程。

Physical vs Logical Replication

PostgreSQL 提供兩種截然不同的複寫機制，各有適用場景：

Physical Replication（物理複寫）：
  Primary → WAL stream (binary) → Standby
  • 複寫整個叢集（byte-for-byte）
  • Standby 只讀
  • DDL 自動同步
  • 延遲極低（毫秒級）

Logical Replication（邏輯複寫）：
  Publisher → Decoded change stream (rows) → Subscriber
  • 可選擇特定 table / 操作
  • Subscriber 可讀寫
  • DDL 不自動複寫
  • 支援跨版本、跨平台

面向	Physical	Logical
複寫單位	WAL 記錄（bytes）	行（row）變更
複寫範圍	整個叢集	可選特定 table
Standby 可寫	否（只讀）	是
DDL 複寫	自動	不支援
跨版本	不支援	支援
資料過濾	不支援	Row Filter（PG15+）
Failover	簡單（pg_promote）	複雜
典型用途	HA / DR / 讀取擴展	版本升級 / 跨平台整合

WAL 在複寫中的角色

WAL（Write-Ahead Log）是所有複寫機制的基礎。Physical Replication 直接傳送 WAL 記錄，Logical Replication 則先透過 Logical Decoding 解碼為行級變更再傳送：

Primary：
  Transaction COMMIT
      │
      ▼
  生成 WAL 記錄
      │
      ▼
  WAL Sender process
      │ Physical → 直接傳送 WAL 記錄
      │ Logical  → 先解碼，再傳送行級變更
      ▼
  Network → Standby WAL Receiver

LSN（Log Sequence Number）追蹤進度：
  sent_lsn    → 已傳送到的位置
  write_lsn   → Standby 已寫入的位置
  flush_lsn   → Standby 已 fsync 的位置
  replay_lsn  → Standby 已重放的位置

Timeline 概念

Timeline 是 PostgreSQL 避免資料分歧（split-brain）的機制，類似版本控制的分支：

Timeline 1：
  ──●──●──●──●──●── (Primary 正常運作)
                 │ Failover（Primary 宕機）
                 ▼
Timeline 2：
                 ──●──●──●──●── (Standby 晉升為新 Primary)

每個 WAL segment 檔名包含 Timeline ID：
  000000010000000100000001  → Timeline 1
  000000020000000100000005  → Timeline 2（Failover 後）

Streaming Replication 設定

Streaming Replication 是 Physical Replication 的主要實現方式，延遲通常在毫秒級別。

Primary 端設定

# postgresql.conf（Primary）
wal_level = replica          -- 至少 replica（Logical 需要 logical）
max_wal_senders = 10         -- 每個 Standby 佔用一個
wal_keep_size = 1024         -- 保留的 WAL 量（MB），PG13+
hot_standby = on             -- 允許 Standby 接受唯讀查詢
listen_addresses = '*'

# pg_hba.conf（Primary）
# TYPE  DATABASE    USER         ADDRESS          METHOD
host    replication replicator   192.168.1.0/24   scram-sha-256

-- 建立複寫專用使用者
CREATE ROLE replicator
  LOGIN
  REPLICATION
  PASSWORD 'strong_password_here';

用 pg_basebackup 建立 Standby

# 在 Standby 伺服器上執行
# 清空 Standby 資料目錄
rm -rf /var/lib/postgresql/data/*

# 執行 pg_basebackup
pg_basebackup \
  --host=192.168.1.10 \
  --port=5432 \
  --username=replicator \
  --pgdata=/var/lib/postgresql/data \
  --wal-method=stream \
  --write-recovery-conf \
  --checkpoint=fast \
  --progress \
  --verbose

--write-recovery-conf 會自動建立 standby.signal 並寫入 postgresql.auto.conf。若需手動設定：

# 建立 standby.signal（PG12+）
touch /var/lib/postgresql/data/standby.signal

# postgresql.conf（Standby）
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=strong_password_here application_name=standby1'
primary_slot_name = 'standby1_slot'
recovery_target_timeline = 'latest'

啟動並驗證

# 啟動 Standby
pg_ctl start -D /var/lib/postgresql/data

# 確認正在接收 WAL
tail -f /var/lib/postgresql/data/log/postgresql-*.log
# 應看到：LOG: started streaming WAL from primary at 0/4000000 on timeline 1

在 Primary 查看複寫狀態：

-- 查看所有 Standby 狀態
SELECT
    application_name,
    client_addr,
    state,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replication_lag,
    replay_lag,
    sync_state
FROM pg_stat_replication;

在 Standby 確認：

-- 確認 Standby 身份
SELECT pg_is_in_recovery();     -- 應回傳 true

-- 查看接收狀態
SELECT
    pg_last_wal_receive_lsn()  AS received_lsn,
    pg_last_wal_replay_lsn()   AS replayed_lsn,
    EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
        AS lag_seconds;

同步 vs 非同步複寫

非同步（預設）

Primary 的 COMMIT 在 WAL 寫入本地磁碟後立即返回，不等待 Standby 確認。適用於效能優先、跨機房（高 RTT）場景。

同步複寫

Primary 的 COMMIT 必須等到指定 Standby 確認後才返回：

# postgresql.conf（Primary）

# 等待名為 standby1 的確認
synchronous_standby_names = 'standby1'

# FIRST 語法：等待前 N 個最快確認的
synchronous_standby_names = 'FIRST 1 (standby1, standby2, standby3)'

# ANY 語法：等待任意 N 個確認（更高容錯）
synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'

同步確認級別可在 session 或 transaction 層級調整：

-- 最強：等待 Standby 已 apply
SET synchronous_commit = remote_apply;

-- 等待 Standby 已寫入 OS 緩衝（但未 fsync）
SET synchronous_commit = remote_write;

-- 只等 Primary 本地 flush，忽略 Standby
SET synchronous_commit = local;

-- 針對單一 transaction
BEGIN;
SET LOCAL synchronous_commit = remote_apply;
UPDATE orders SET status = 'confirmed' WHERE id = 42;
COMMIT;

synchronous_commit	零資料遺失	效能代價
off	否	最高（無額外等待）
local	否	高
on（預設）	是	中（1x RTT）
remote_write	接近是	中
remote_apply	是	低（需 Standby apply）

Replication Slots

Replication Slot 確保 WAL 不被過早清除——只要 Slot 存在，Primary 就不會刪除 consumer 尚未消費的 WAL。

-- 建立 Physical Replication Slot
SELECT pg_create_physical_replication_slot('standby1_slot');

-- 建立 Logical Replication Slot
SELECT pg_create_logical_replication_slot('my_logical_slot', 'pgoutput');

-- 查看所有 Slot 狀態
SELECT
    slot_name,
    slot_type,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS retained_wal
FROM pg_replication_slots;

-- 刪除 Slot
SELECT pg_drop_replication_slot('standby1_slot');

防止 WAL 暴增

# postgresql.conf（PG13+）
# 限制 Slot 可保留的最大 WAL
# 超過限制，Slot 會被 invalidate
max_slot_wal_keep_size = 10GB

監控 WAL 積壓：

SELECT
    slot_name,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS wal_lag,
    CASE
        WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 5*1024^3
        THEN 'WARNING: >5GB'
        ELSE 'OK'
    END AS status
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

Cascading Replication

允許 Standby 再向下游提供 WAL 串流，減輕 Primary 負擔：

Primary（主機房）
    │ WAL stream
    ▼
Standby A（主機房，同步）
    │ WAL stream（A 作為中繼）
    ▼
Standby B（DR 機房，非同步）
    ├──► Standby C（報表用）
    └──► Standby D（備份用）

設定方式與一般 Standby 相同，只是 primary_conninfo 指向上游 Standby 而非 Primary。

Logical Replication

Logical Replication 在行（row）層級傳送變更，適用於跨版本升級、部分表格複寫、雙向複寫等場景。

建立 Publication

-- 在 Publisher 端

-- 複寫特定 table
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

-- 複寫整個資料庫的所有 table
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- 只複寫 INSERT 操作
CREATE PUBLICATION pub_insert_only
FOR TABLE audit_log
WITH (publish = 'insert');

-- PG15+：複寫特定欄位
CREATE PUBLICATION pub_partial
FOR TABLE customers (id, email, updated_at);

-- PG15+：Row Filter（只複寫符合條件的資料）
CREATE PUBLICATION pub_active_users
FOR TABLE users WHERE (status = 'active');

-- 查看所有 Publication
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

建立 Subscription

-- 在 Subscriber 端（需事先建立相同結構的 table）
-- DDL 不自動複寫，可用 pg_dump --schema-only 匯出 schema

-- 建立 Subscription
CREATE SUBSCRIPTION sub_orders
CONNECTION 'host=publisher-ip port=5432 dbname=mydb user=replicator password=xxx'
PUBLICATION pub_orders;

-- 不做初始資料同步（資料已透過其他方式同步）
CREATE SUBSCRIPTION sub_orders
CONNECTION 'host=publisher-ip ...'
PUBLICATION pub_orders
WITH (copy_data = false);

-- 查看 Subscription 狀態
SELECT subname, subenabled, subslotname, subpublications
FROM pg_subscription;

-- 查看 per-table 同步狀態
SELECT * FROM pg_subscription_rel;
-- srsubstate: i=初始化, d=資料複製中, s=已同步, r=正常複寫

-- 管理 Subscription
ALTER SUBSCRIPTION sub_orders DISABLE;    -- 暫停
ALTER SUBSCRIPTION sub_orders ENABLE;     -- 恢復
ALTER SUBSCRIPTION sub_orders REFRESH PUBLICATION;  -- 更新 table 清單
DROP SUBSCRIPTION sub_orders;             -- 刪除

重要限制

DDL 不複寫：ALTER TABLE、CREATE INDEX 等需在兩端手動執行
Sequence 不同步：Failover 後需手動調整 setval('seq', MAX(id) + 1000)
Large Object 不支援：改用 bytea 欄位
需要 REPLICA IDENTITY：UPDATE/DELETE 需要主鍵或唯一鍵

-- 若 table 無主鍵，需設定 REPLICA IDENTITY
ALTER TABLE mytable REPLICA IDENTITY FULL;  -- 使用所有欄位（效能影響大）
-- 或使用唯一索引
ALTER TABLE mytable REPLICA IDENTITY USING INDEX mytable_unique_idx;

Failover / Switchover

Failover：Primary 意外宕機，強制晉升 Standby（可能有少量資料遺失）。 Switchover：計劃性切換，確認同步後再切換（零資料遺失）。

pg_promote()（PG12+）

-- 在 Standby 上執行
SELECT pg_promote(wait => true, wait_seconds => 60);
-- 回傳 true 表示晉升成功

-- 確認晉升完成
SELECT pg_is_in_recovery();   -- 應回傳 false

計劃性 Switchover 步驟

# 1. 確認 Standby 已同步（lag 接近 0）
psql -c "SELECT application_name, replay_lag FROM pg_stat_replication;"

# 2. 強制 checkpoint
psql -c "CHECKPOINT;"

# 3. 確認 Standby LSN 與 Primary 一致
# Primary：
psql -c "SELECT pg_current_wal_lsn();"
# Standby：
psql -h standby-ip -c "SELECT pg_last_wal_replay_lsn();"

# 4. 優雅關閉 Primary
pg_ctl stop -D /var/lib/postgresql/data -m fast

# 5. 晉升 Standby
psql -h standby-ip -c "SELECT pg_promote();"

# 6. 更新應用程式連線字串

# 7. 將舊 Primary 設為新 Standby（使用 pg_rewind）
pg_rewind \
  --target-pgdata=/var/lib/postgresql/data \
  --source-server='host=new-primary-ip port=5432 user=replicator' \
  --progress
touch /var/lib/postgresql/data/standby.signal
pg_ctl start -D /var/lib/postgresql/data

緊急 Failover

# 1. 確認 Primary 確實無法服務（避免 split-brain）
# 2. 晉升最接近 Primary 的 Standby
pg_ctl promote -D /var/lib/postgresql/standby_data
# 3. 更新 DNS / Load Balancer
# 4. 通知其他 Standby 切換到新 Primary
psql -h other-standby -c "ALTER SYSTEM SET primary_conninfo = 'host=new-primary-ip ...';"
psql -h other-standby -c "SELECT pg_reload_conf();"
# 5. 嘗試用 pg_rewind 恢復舊 Primary

Logical Decoding 與 CDC

Logical Decoding 是 Logical Replication 的底層機制，允許外部程式讀取 WAL 的解碼輸出，實現 CDC（Change Data Capture）：

WAL records (binary)
    │
    ▼
Output Plugin（解碼器）
    ├── pgoutput（內建）
    ├── wal2json（JSON 輸出）
    └── decoderbufs（Protocol Buffers）
    │
    ▼
Change stream → Consumer
    ├── Logical Replication Subscriber
    ├── Debezium → Kafka
    └── 自訂應用程式

使用 wal2json

-- 建立 Logical Slot
SELECT pg_create_logical_replication_slot('cdc_slot', 'wal2json');

-- 讀取變更（不消費：peek）
SELECT * FROM pg_logical_slot_peek_changes('cdc_slot', NULL, NULL,
    'pretty-print', '1',
    'include-timestamp', '1'
);

-- 讀取並消費（consume）
SELECT * FROM pg_logical_slot_get_changes('cdc_slot', NULL, NULL,
    'pretty-print', '1'
);

-- 輸出範例（JSON）：
-- {"change":[
--   {"kind":"insert","schema":"public","table":"orders",
--    "columnnames":["id","customer","amount"],
--    "columnvalues":[101,"Alice",599.99]}
-- ]}

常見陷阱

1. Replication Slot WAL 積壓（最危險）

Standby 斷線後 Slot 持續累積 WAL，可能撐爆 Primary 磁碟：

# 設定 max_slot_wal_keep_size 防止暴增
max_slot_wal_keep_size = 10GB
# 搭配監控告警：WAL 積壓 > 可用空間 50% 時告警

2. 同步 Standby 全部離線導致 Primary 掛起

所有同步 Standby 斷線時，Primary 的所有 COMMIT 都會無限等待：

# 使用 ANY 語法提高容錯
synchronous_standby_names = 'ANY 1 (s1, s2, s3)'
# 準備緊急回退：SET synchronous_standby_names = ''

3. Timeline 不匹配

Failover 後舊 Primary 重啟可能產生衝突：

# Standby 設定自動追蹤最新 Timeline
recovery_target_timeline = 'latest'
# 舊 Primary 使用 pg_rewind 回到正確歷史

4. hot_standby_feedback 未啟用

Standby 上的長查詢被 Primary 的 VACUUM 取消：

# 開啟回饋機制
hot_standby_feedback = on
max_standby_streaming_delay = 30s

5. Logical Replication 的 Sequence 陷阱

Failover 到 Subscriber 後，sequence 值可能衝突：

-- 晉升 Subscriber 前，手動推進 sequence
SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders) + 1000);

版本演進

版本	複寫相關新功能
PG 9.0	Streaming Replication、Hot Standby
PG 9.1	Synchronous Replication
PG 9.2	Cascading Replication
PG 9.4	Replication Slots、Logical Decoding
PG 10	Logical Replication（Publication/Subscription）
PG 12	pg_promote()、standby.signal 取代 recovery.conf
PG 13	wal_keep_size 取代 wal_keep_segments；max_slot_wal_keep_size
PG 15	Row Filter / Column Filter for Publication
PG 16	Standby 可做 pg_basebackup
PG 17	Logical Replication Failover Slots；pg_createsubscriber

總結

複寫是 PostgreSQL 高可用架構的根基。關鍵要點：

Physical Replication 適合 HA/DR，設定簡單、延遲極低
Logical Replication 適合跨版本升級、部分表格同步、CDC 整合
同步複寫確保零資料遺失，但有效能代價——用 ANY 語法提高容錯
Replication Slot 防止 WAL 被清除，但必須搭配 max_slot_wal_keep_size 監控
Failover 使用 pg_promote()，Switchover 用 pg_rewind 讓舊 Primary 回歸
善用 Cascading Replication 減輕 Primary 負擔

下一篇，我們將深入探討 高可用架構方案——掌握 Patroni 自動 Failover、HAProxy 負載均衡與 etcd 共識機制，打造真正的零停機 PostgreSQL 叢集。

複寫與高可用（Replication）：從 Streaming 到 Logical Replication | PostgreSQL

Physical vs Logical Replication

WAL 在複寫中的角色

Timeline 概念

Streaming Replication 設定

Primary 端設定

用 pg_basebackup 建立 Standby

啟動並驗證

同步 vs 非同步複寫

非同步（預設）

同步複寫

Replication Slots

防止 WAL 暴增

Cascading Replication

Logical Replication

建立 Publication

建立 Subscription

重要限制

Failover / Switchover

pg_promote()（PG12+）

計劃性 Switchover 步驟

緊急 Failover

Logical Decoding 與 CDC

使用 wal2json

常見陷阱

版本演進

總結

AI 自動化入門包

相關文章

複寫與高可用（Replication）：從 Streaming 到 Logical Replication | PostgreSQL

Physical vs Logical Replication

WAL 在複寫中的角色

Timeline 概念

Streaming Replication 設定

Primary 端設定

用 pg_basebackup 建立 Standby

啟動並驗證

同步 vs 非同步複寫

非同步（預設）

同步複寫

Replication Slots

防止 WAL 暴增

Cascading Replication

Logical Replication

建立 Publication

建立 Subscription

重要限制

Failover / Switchover

pg_promote()（PG12+）

計劃性 Switchover 步驟

緊急 Failover

Logical Decoding 與 CDC

使用 wal2json

常見陷阱

版本演進

總結

AI 自動化入門包

訂閱 BenzHub 電子報

相關文章