Back to Blogs
postgresql

Replication 與 High Availability

介紹 PostgreSQL 的 Streaming Replication、Logical Replication、以及 Patroni / PgBouncer 組成的生產級 HA 架構

Aaron

一套 WAL 驅動兩種複製

PostgreSQL 的複製建立在 WAL 之上，分兩種：

類型傳輸內容Standby 可寫？跨版本？選擇性複製？
Streaming ReplicationWAL（物理）✗✗✗（全庫）
Logical Replication邏輯變更（row-level）✓✓✓（per-table）

Streaming Replication 是主流的 HA 方案；Logical Replication 用於跨版本升級、部分表同步、資料分發等場景。

Streaming Replication

架構

上圖展示 Streaming Replication 的完整架構。Primary 端的 WAL Sender 程序透過 TCP 串流把 WAL 物理日誌即時傳送給 Standby；Standby 端的 WAL Receiver 接收 WAL 並寫入本地磁碟，Startup Process 持續重播 WAL 使 Standby 保持最新狀態。開啟 hot_standby = on 後 Standby 可同時接受唯讀查詢（但 WAL 重播可能 cancel 正在執行的查詢）。

圖的下方列出四種同步模式：Async（預設，只等本地 fsync）、Remote Write（等 Standby 收到 WAL）、Remote Flush（等 Standby fsync）、Remote Apply（等 Standby 重播完，讀 Standby 保證一致）。

和 MySQL 的主從複製概念類似，但傳輸的是 WAL（物理日誌）而非 Binlog（邏輯日誌）。

設定

Primary 端：

# postgresql.conf
wal_level = replica              # 必須 >= replica
max_wal_senders = 10             # 最大 WAL sender 數
wal_keep_size = 1GB              # 保留的 WAL 最小量（防 standby 來不及拉）

# pg_hba.conf — 允許 replication 連線
# host replication replicator 10.0.0.0/24 scram-sha-256

Standby 端：

# postgresql.conf
primary_conninfo = 'host=primary port=5432 user=replicator password=xxx'
hot_standby = on                 # 允許 Standby 處理唯讀查詢

同步模式

模式synchronous_commit 設定Primary COMMIT 等什麼資料安全
Async（預設）on只等本地 WAL fsyncStandby 可能落後
Remote Writeremote_write等 Standby 收到 WAL網路斷不丟
Remote Flushon + synchronous_standby_names等 Standby fsync WAL幾乎零丟失
Remote Applyremote_apply等 Standby 重播完 WAL讀 Standby 一致
# 指定同步 standby（支援 quorum）
synchronous_standby_names = 'FIRST 1 (standby1, standby2)'
# 或 quorum：ANY 1 (standby1, standby2)

Hot Standby

Standby 在重播 WAL 的同時可以接受唯讀查詢（hot_standby = on）。這是讀寫分離的基礎。

但要注意 replication conflict：Standby 上的查詢可能因為 WAL 重播需要清理它正在讀的 tuple 而被 cancel。用 max_standby_streaming_delay（預設 30 秒）控制容忍度——超過就 cancel query 讓 WAL 重播繼續。

與 MySQL 主從複製的差異

面向PostgreSQL StreamingMySQL Replication
傳輸內容WAL（物理 page diff）Binlog（邏輯 SQL/row）
一致性byte-level 一致邏輯一致
Standby 可寫不可（物理副本）不可（但可設為 read-only）
並行重播PG 15+ 有改善8.0 WRITESET 做得較好
跨版本不行大版本間有時可以

Logical Replication

PostgreSQL 10 引入。把 WAL 中的物理變更解碼成邏輯的行變更（INSERT/UPDATE/DELETE），再傳給 subscriber。

架構

上圖展示 Logical Replication 的資料流：Publisher 端的 Logical Decoding 模組將 WAL 中的物理變更解碼成邏輯的行變更（INSERT/UPDATE/DELETE），透過網路傳送給 Subscriber；Subscriber 端的 Logical Replication Worker 接收行變更並 apply 到本地表。與 Streaming Replication 不同，Subscriber 的表是可讀可寫的獨立表。

圖的下方整理了適用場景（跨版本升級、部分表同步、多源匯聚、跨雲同步）和限制（不複製 DDL、不複製 sequence、初始同步慢、衝突需手動處理）。

設定

-- Publisher 端
-- postgresql.conf: wal_level = logical

CREATE PUBLICATION my_pub FOR TABLE orders, users;
-- 或 FOR ALL TABLES

-- Subscriber 端
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher port=5432 dbname=mydb user=replicator'
    PUBLICATION my_pub;

適用場景

  • 跨大版本升級：PG 14 → PG 16，用 logical replication 做 zero-downtime migration
  • 部分表同步：只複製需要的表到分析用的副本
  • 多個來源匯聚：多個 publisher 的資料匯到一個 subscriber
  • 跨雲同步：不同雲的 PostgreSQL 之間

限制

  • 不複製 DDL（需要手動同步 schema）
  • 不複製 sequence 值
  • 大交易的初始同步（initial table sync）可能很慢
  • 衝突需要手動處理（subscriber 上也可以寫，衝突時 subscription 會暫停）

生產級 HA 架構

Patroni + etcd + PgBouncer

這是目前 PostgreSQL 社群最推薦的 HA 方案。上圖展示完整架構：

  • 最上層：Application 連接到 PgBouncer（連線池），PgBouncer 負責連線池管理（200 客戶端 → 20 backends）和讀寫分離（寫走 Leader、讀走 Replica）
  • 中間層：三個 PostgreSQL 節點，每個都跑一個 Patroni Agent。Patroni 管理 PostgreSQL 的生命週期、監控健康狀態、處理 Streaming Replication 設定。Leader 負責讀寫，Replica 透過 WAL 保持同步並提供唯讀查詢
  • 底層：etcd Cluster 做分散式共識。Patroni Leader 持續 renew etcd 中的 leader key（TTL 機制），一旦停止 renew，其他 Patroni 就會發起 leader election

圖的底部展示 Failover 流程：leader key TTL 到期 → Replica 贏得選舉 → pg_promote() 升為 Primary → 其他 Replica 重新指向 → PgBouncer callback 更新連線，全程約 5-15 秒。

Patroni

Python 寫的 HA agent，跑在每個 PostgreSQL 節點上：

  • 透過 etcd/ZooKeeper/Consul 做 leader election
  • 監控 PostgreSQL 健康狀態
  • Leader 掛了自動 promote replica 為新 leader
  • 處理 Streaming Replication 的設定
  • 提供 REST API 查詢 cluster 狀態
# patroni.yml 範例（簡化）
scope: my-cluster
name: node1
restapi:
  listen: 0.0.0.0:8008
etcd:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379
bootstrap:
  dcs:
    synchronous_mode: true
    postgresql:
      parameters:
        max_connections: 200
        shared_buffers: 4GB
postgresql:
  listen: 0.0.0.0:5432
  data_dir: /var/lib/postgresql/data
  replication:
    username: replicator
    password: xxx

PgBouncer

負責：

  • 連線池：200 個應用連線 → 20 個 PostgreSQL backend
  • 讀寫分離：寫走 leader、讀走 replica
  • 自動切換：配合 Patroni 的 callback，failover 時更新連線指向

Failover 流程

  1. etcd 的 leader key TTL 到期（Patroni leader 停止 renew）
  2. 某個 replica 的 Patroni 贏得 leader election
  3. 該 replica 執行 pg_promote() 升為 primary
  4. 其他 replica 重新指向新 primary
  5. PgBouncer 透過 callback 更新連線
  6. 應用層短暫中斷（通常 5-15 秒）後恢復

與 MySQL 高可用方案的對比

方案適用failover 時間資料安全
PG: Patroni + etcd主流 HA5-15 秒同步模式下零丟失
PG: pg_auto_failover簡單場景秒級同步模式下零丟失
MySQL: InnoDB Cluster主流 HA秒級Paxos 多數決
MySQL: MHA/Orchestrator傳統方案秒到分鐘半同步下趨近零

監控複製狀態

-- Primary 端：查看所有 standby 的狀態
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- Standby 端：查看自己的延遲
SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;

-- Logical Replication：查看 subscription 狀態
SELECT subname, pid, received_lsn, latest_end_lsn
FROM pg_stat_subscription;