PostgreSQL 邏輯複製（Logical Replication）是一種基於資料變更的複製方式，允許更靈活的資料同步架構。本文將詳細介紹如何在 Ubuntu 22.04 上設定和管理 PostgreSQL 邏輯複製。
邏輯複製概念與用途
什麼是邏輯複製
邏輯複製是 PostgreSQL 10 引入的一種複製機制，它透過解碼 WAL（Write-Ahead Log）來擷取邏輯資料變更，並將這些變更以邏輯格式傳送給訂閱者。與傳統的串流複製不同，邏輯複製只複製特定資料表的資料變更，而非整個資料庫叢集。
邏輯複製的核心概念
- Publication（發布）：定義要複製的資料表集合，位於發布端（Publisher）
- Subscription（訂閱）：連接到發布端並接收資料變更，位於訂閱端（Subscriber）
- Replication Slot（複製插槽）：確保 WAL 記錄不會被過早清除
- Logical Decoding（邏輯解碼）：將 WAL 記錄轉換為邏輯變更串流
主要用途
邏輯複製適用於以下場景：
- 選擇性資料複製：只複製特定資料表或資料庫的子集
- 跨版本升級：在不同 PostgreSQL 版本之間進行資料遷移
- 資料整合：將多個資料庫的資料整合到一個中央資料庫
- 即時資料分析：複製資料到分析系統進行報表處理
- 多主架構：實現雙向或多向複製
- 跨平台複製：在不同作業系統之間進行資料複製
與串流複製的差異
架構比較
| 特性 | 邏輯複製 | 串流複製 |
|---|
| 複製單位 | 資料表層級 | 整個叢集 |
| 複製格式 | 邏輯變更 | 二進位 WAL |
| 跨版本支援 | 支援 | 不支援 |
| 訂閱端可寫入 | 是 | 否（唯讀） |
| 複製方向 | 可雙向 | 單向 |
| DDL 複製 | 不支援 | 自動複製 |
| 初始快照 | 需要設定 | 使用 pg_basebackup |
| 衝突處理 | 需手動處理 | 不適用 |
選擇建議
- 選擇串流複製：需要完整的災難復原方案、唯讀副本
- 選擇邏輯複製：需要選擇性複製、跨版本遷移、多主架構
環境準備
系統架構
本文範例使用兩台 Ubuntu 22.04 伺服器：
- 發布端（Publisher）：192.168.1.10
- 訂閱端（Subscriber）：192.168.1.20
安裝 PostgreSQL
在兩台伺服器上安裝 PostgreSQL：
1
2
| sudo apt update
sudo apt install postgresql postgresql-contrib -y
|
確認服務狀態：
1
| sudo systemctl status postgresql
|
Publication 與 Subscription 設定
發布端設定
1. 修改 postgresql.conf
編輯 PostgreSQL 設定檔：
1
| sudo nano /etc/postgresql/14/main/postgresql.conf
|
修改以下參數：
1
2
3
4
5
6
7
8
9
| # 啟用邏輯複製
wal_level = logical
# 設定最大複製連線數
max_replication_slots = 10
max_wal_senders = 10
# 監聽所有介面
listen_addresses = '*'
|
2. 修改 pg_hba.conf
編輯客戶端認證設定：
1
| sudo nano /etc/postgresql/14/main/pg_hba.conf
|
新增複製連線規則：
1
2
3
| # 允許訂閱端進行複製連線
host all replication_user 192.168.1.20/32 scram-sha-256
host replication replication_user 192.168.1.20/32 scram-sha-256
|
3. 重新啟動服務
1
| sudo systemctl restart postgresql
|
4. 建立複製使用者
1
2
3
4
5
6
7
8
9
10
11
| -- 建立具有複製權限的使用者
CREATE USER replication_user WITH REPLICATION PASSWORD 'your_secure_password';
-- 授予資料庫連線權限
GRANT CONNECT ON DATABASE mydb TO replication_user;
-- 授予 schema 使用權限
GRANT USAGE ON SCHEMA public TO replication_user;
-- 授予資料表讀取權限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;
|
5. 建立測試資料庫和資料表
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
| -- 建立資料庫
CREATE DATABASE mydb;
-- 連接到資料庫
\c mydb
-- 建立測試資料表
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
department VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入測試資料
INSERT INTO employees (name, email, department) VALUES
('張三', 'zhangsan@example.com', '研發部'),
('李四', 'lisi@example.com', '行銷部'),
('王五', 'wangwu@example.com', '財務部');
-- 建立另一個資料表
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
employee_id INTEGER REFERENCES employees(id),
order_date DATE NOT NULL,
amount DECIMAL(10,2),
status VARCHAR(20) DEFAULT 'pending'
);
|
6. 建立 Publication
1
2
3
4
5
6
7
8
9
10
11
| -- 為特定資料表建立 Publication
CREATE PUBLICATION my_publication FOR TABLE employees, orders;
-- 或者為所有資料表建立 Publication
-- CREATE PUBLICATION my_publication FOR ALL TABLES;
-- 查看 Publication 資訊
SELECT * FROM pg_publication;
-- 查看 Publication 包含的資料表
SELECT * FROM pg_publication_tables;
|
訂閱端設定
1. 修改 postgresql.conf
1
| sudo nano /etc/postgresql/14/main/postgresql.conf
|
1
2
3
4
| # 訂閱端也需要設定（如果要支援雙向複製）
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
|
2. 重新啟動服務
1
| sudo systemctl restart postgresql
|
3. 建立對應的資料庫和資料表
在訂閱端必須先建立相同結構的資料表：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| -- 建立資料庫
CREATE DATABASE mydb;
-- 連接到資料庫
\c mydb
-- 建立相同結構的資料表（不含資料）
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
department VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
employee_id INTEGER REFERENCES employees(id),
order_date DATE NOT NULL,
amount DECIMAL(10,2),
status VARCHAR(20) DEFAULT 'pending'
);
|
4. 建立 Subscription
1
2
3
4
5
6
7
8
9
10
| -- 建立 Subscription
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=192.168.1.10 port=5432 dbname=mydb user=replication_user password=your_secure_password'
PUBLICATION my_publication;
-- 查看 Subscription 狀態
SELECT * FROM pg_subscription;
-- 查看複製狀態
SELECT * FROM pg_stat_subscription;
|
初始資料同步
預設行為
建立 Subscription 時，PostgreSQL 預設會執行初始資料同步（copy_data = true）。這會將發布端現有的資料完整複製到訂閱端。
控制初始同步
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| -- 建立 Subscription 時不執行初始同步
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=192.168.1.10 port=5432 dbname=mydb user=replication_user password=your_secure_password'
PUBLICATION my_publication
WITH (copy_data = false);
-- 建立 Subscription 時不立即啟用
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=192.168.1.10 port=5432 dbname=mydb user=replication_user password=your_secure_password'
PUBLICATION my_publication
WITH (enabled = false);
-- 手動啟用 Subscription
ALTER SUBSCRIPTION my_subscription ENABLE;
|
重新同步特定資料表
如果需要重新同步某個資料表：
1
2
3
4
5
6
7
8
9
| -- 在訂閱端執行
-- 先重新整理 Subscription
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;
-- 如果需要完全重新同步，可以先清空資料
TRUNCATE TABLE employees;
-- 然後重新同步
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION WITH (copy_data = true);
|
監控初始同步進度
1
2
3
4
5
6
7
8
9
10
11
12
13
| -- 查看同步狀態
SELECT
srsubid,
srrelid::regclass AS table_name,
srsublsn,
srsubstate
FROM pg_subscription_rel;
-- 狀態說明：
-- 'i' = 初始化中
-- 'd' = 資料複製中
-- 's' = 已同步
-- 'r' = 準備就緒
|
衝突處理機制
常見衝突類型
- 主鍵衝突：訂閱端已存在相同主鍵的資料
- 唯一約束衝突：違反唯一性約束
- 外鍵約束衝突：參照的資料不存在
- 權限衝突：訂閱端缺少必要權限
衝突檢測
當發生衝突時，複製會暫停。查看錯誤日誌：
1
| sudo tail -f /var/log/postgresql/postgresql-14-main.log
|
衝突處理策略
方法一：手動解決衝突
1
2
3
4
5
6
7
8
| -- 在訂閱端查看問題資料
SELECT * FROM employees WHERE id = 1;
-- 刪除衝突的資料
DELETE FROM employees WHERE id = 1;
-- 或更新衝突資料
UPDATE employees SET email = 'new_email@example.com' WHERE id = 1;
|
方法二：跳過衝突的交易
1
2
3
4
5
6
7
8
9
10
11
12
| -- 在訂閱端執行
-- 先查看當前的 LSN
SELECT * FROM pg_stat_subscription;
-- 停用 Subscription
ALTER SUBSCRIPTION my_subscription DISABLE;
-- 跳過衝突的交易（將 LSN 往前推進）
ALTER SUBSCRIPTION my_subscription SKIP (lsn = '0/1234567');
-- 重新啟用 Subscription
ALTER SUBSCRIPTION my_subscription ENABLE;
|
方法三：使用觸發器處理
在訂閱端建立衝突處理觸發器：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| -- 建立衝突處理函數
CREATE OR REPLACE FUNCTION handle_replication_conflict()
RETURNS TRIGGER AS $$
BEGIN
-- 如果是 INSERT 衝突，改為 UPDATE
IF TG_OP = 'INSERT' THEN
UPDATE employees
SET name = NEW.name,
email = NEW.email,
department = NEW.department
WHERE id = NEW.id;
RETURN NULL; -- 跳過 INSERT
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 建立觸發器
CREATE TRIGGER tr_handle_conflict
BEFORE INSERT ON employees
FOR EACH ROW
WHEN (EXISTS (SELECT 1 FROM employees WHERE id = NEW.id))
EXECUTE FUNCTION handle_replication_conflict();
|
監控與故障排除
監控複製狀態
發布端監控
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
| -- 查看所有 Publication
SELECT * FROM pg_publication;
-- 查看 Publication 包含的資料表
SELECT
pubname,
schemaname,
tablename
FROM pg_publication_tables;
-- 查看複製插槽狀態
SELECT
slot_name,
plugin,
slot_type,
active,
restart_lsn,
confirmed_flush_lsn
FROM pg_replication_slots;
-- 查看 WAL 發送者狀態
SELECT
pid,
usename,
application_name,
client_addr,
state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn
FROM pg_stat_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
| -- 查看 Subscription 狀態
SELECT
subname,
subenabled,
subconninfo,
subpublications
FROM pg_subscription;
-- 查看複製進度
SELECT
subname,
pid,
relid::regclass AS table_name,
received_lsn,
last_msg_send_time,
last_msg_receipt_time,
latest_end_lsn,
latest_end_time
FROM pg_stat_subscription;
-- 查看各資料表的同步狀態
SELECT
srsubid,
srrelid::regclass AS table_name,
srsubstate,
srsublsn
FROM pg_subscription_rel;
|
複製延遲監控
1
2
3
4
5
6
7
8
| -- 在發布端查看複製延遲
SELECT
application_name,
client_addr,
state,
pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag_bytes,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;
|
常見問題排除
問題一：Subscription 無法連線
1
2
3
4
5
6
7
8
| # 檢查網路連線
telnet 192.168.1.10 5432
# 檢查防火牆
sudo ufw status
# 檢查 pg_hba.conf 設定
sudo cat /etc/postgresql/14/main/pg_hba.conf | grep replication
|
問題二：複製插槽滿了
1
2
3
4
5
6
7
8
| -- 在發布端查看複製插槽
SELECT * FROM pg_replication_slots;
-- 刪除不再使用的複製插槽
SELECT pg_drop_replication_slot('slot_name');
-- 檢查 WAL 保留設定
SHOW max_slot_wal_keep_size;
|
問題三：複製停止
1
2
3
4
5
6
7
8
9
| -- 在訂閱端檢查狀態
SELECT * FROM pg_stat_subscription;
-- 查看錯誤日誌
-- sudo tail -f /var/log/postgresql/postgresql-14-main.log
-- 嘗試重新啟用
ALTER SUBSCRIPTION my_subscription DISABLE;
ALTER SUBSCRIPTION my_subscription ENABLE;
|
問題四：資料不一致
1
2
3
4
5
6
7
8
9
10
11
| -- 比較資料筆數
-- 發布端
SELECT COUNT(*) FROM employees;
-- 訂閱端
SELECT COUNT(*) FROM employees;
-- 重新同步資料表
-- 訂閱端執行
TRUNCATE TABLE employees;
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION WITH (copy_data = true);
|
雙向複製設定
雙向複製架構
雙向複製允許兩個節點互為發布者和訂閱者，適用於多主寫入場景。
設定步驟
節點 A（192.168.1.10）
1
2
3
4
5
6
7
8
9
10
| -- 確保 wal_level = logical
-- 建立 Publication
CREATE PUBLICATION pub_from_a FOR TABLE employees, orders;
-- 建立 Subscription 訂閱節點 B
CREATE SUBSCRIPTION sub_from_b
CONNECTION 'host=192.168.1.20 port=5432 dbname=mydb user=replication_user password=your_secure_password'
PUBLICATION pub_from_b
WITH (copy_data = false, origin = none);
|
節點 B（192.168.1.20）
1
2
3
4
5
6
7
8
| -- 建立 Publication
CREATE PUBLICATION pub_from_b FOR TABLE employees, orders;
-- 建立 Subscription 訂閱節點 A
CREATE SUBSCRIPTION sub_from_a
CONNECTION 'host=192.168.1.10 port=5432 dbname=mydb user=replication_user password=your_secure_password'
PUBLICATION pub_from_a
WITH (copy_data = false, origin = none);
|
避免複製迴圈
使用 origin = none 參數可以避免複製迴圈，它會忽略從其他訂閱來源產生的變更。
衝突避免策略
策略一：分區寫入
1
2
3
4
5
6
7
| -- 節點 A 使用奇數 ID
CREATE SEQUENCE employees_id_seq_a START 1 INCREMENT 2;
ALTER TABLE employees ALTER COLUMN id SET DEFAULT nextval('employees_id_seq_a');
-- 節點 B 使用偶數 ID
CREATE SEQUENCE employees_id_seq_b START 2 INCREMENT 2;
ALTER TABLE employees ALTER COLUMN id SET DEFAULT nextval('employees_id_seq_b');
|
策略二：使用 UUID
1
2
3
4
5
6
7
8
9
10
| -- 使用 UUID 作為主鍵避免衝突
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE employees_v2 (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
department VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
|
策略三：最後寫入者獲勝
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| -- 新增修改時間戳記
ALTER TABLE employees ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
-- 建立觸發器實現 Last-Write-Wins
CREATE OR REPLACE FUNCTION last_write_wins()
RETURNS TRIGGER AS $$
BEGIN
-- 如果現有資料的 updated_at 較新，則忽略此次更新
IF EXISTS (
SELECT 1 FROM employees
WHERE id = NEW.id
AND updated_at > NEW.updated_at
) THEN
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_last_write_wins
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION last_write_wins();
|
效能調校建議
WAL 相關設定
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| # postgresql.conf
# 增加 WAL 發送者數量
max_wal_senders = 20
# 增加複製插槽數量
max_replication_slots = 20
# 設定 WAL 保留大小
max_slot_wal_keep_size = 10GB
# WAL 寫入優化
wal_buffers = 64MB
wal_writer_delay = 200ms
|
訂閱端優化
1
2
3
4
5
6
7
| # postgresql.conf
# 增加邏輯複製工作者數量
max_logical_replication_workers = 8
# 每個訂閱的同步工作者數量
max_sync_workers_per_subscription = 4
|
網路優化
1
2
3
4
5
6
7
8
9
10
| # postgresql.conf
# TCP 保活設定
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 6
# 增加接收緩衝區
wal_receiver_timeout = 60s
wal_receiver_status_interval = 10s
|
批次處理優化
1
2
3
4
5
6
7
8
9
10
11
| -- 使用批次 INSERT 減少複製開銷
INSERT INTO employees (name, email, department)
VALUES
('員工1', 'emp1@example.com', '部門A'),
('員工2', 'emp2@example.com', '部門B'),
('員工3', 'emp3@example.com', '部門C');
-- 使用 COPY 命令進行大量資料載入
COPY employees (name, email, department)
FROM '/path/to/data.csv'
WITH (FORMAT csv, HEADER true);
|
資料表優化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| -- 確保資料表有適當的索引
CREATE INDEX idx_employees_department ON employees(department);
-- 對於大型資料表，考慮分區
CREATE TABLE orders_partitioned (
id SERIAL,
employee_id INTEGER,
order_date DATE NOT NULL,
amount DECIMAL(10,2),
status VARCHAR(20) DEFAULT 'pending',
PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);
-- 建立分區
CREATE TABLE orders_2024 PARTITION OF orders_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders_partitioned
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
|
監控效能指標
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| -- 建立效能監控視圖
CREATE VIEW replication_performance AS
SELECT
s.subname AS subscription_name,
s.subenabled AS enabled,
ss.pid,
ss.relid::regclass AS table_name,
ss.received_lsn,
ss.latest_end_lsn,
pg_wal_lsn_diff(ss.latest_end_lsn, ss.received_lsn) AS lag_bytes,
ss.last_msg_send_time,
ss.last_msg_receipt_time,
ss.latest_end_time
FROM pg_subscription s
LEFT JOIN pg_stat_subscription ss ON s.oid = ss.subid;
-- 查詢效能
SELECT * FROM replication_performance;
|
進階管理操作
新增資料表到現有 Publication
1
2
3
4
5
| -- 在發布端
ALTER PUBLICATION my_publication ADD TABLE new_table;
-- 在訂閱端重新整理
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;
|
從 Publication 移除資料表
1
2
| -- 在發布端
ALTER PUBLICATION my_publication DROP TABLE old_table;
|
變更 Subscription 連線資訊
1
2
3
| -- 變更連線字串
ALTER SUBSCRIPTION my_subscription
CONNECTION 'host=new_host port=5432 dbname=mydb user=replication_user password=new_password';
|
暫停和恢復複製
1
2
3
4
5
| -- 暫停
ALTER SUBSCRIPTION my_subscription DISABLE;
-- 恢復
ALTER SUBSCRIPTION my_subscription ENABLE;
|
刪除複製設定
1
2
3
4
5
6
7
8
| -- 在訂閱端刪除 Subscription
DROP SUBSCRIPTION my_subscription;
-- 在發布端刪除 Publication
DROP PUBLICATION my_publication;
-- 清理複製插槽（如果需要）
SELECT pg_drop_replication_slot('my_subscription');
|
結語
PostgreSQL 邏輯複製提供了比串流複製更靈活的資料同步方案，特別適合需要選擇性複製、跨版本遷移或多主架構的場景。透過正確的設定和監控，邏輯複製可以成為建構高可用性資料庫架構的重要工具。
在實際部署時，需要特別注意衝突處理機制的設計、效能調校以及監控策略。建議在正式環境上線前，先在測試環境進行充分的驗證和壓力測試。
參考資源