SQL Replication 詳解：從問題根源到 PostgreSQL 實戰演練

發表於 分類於

在現代應用程式架構中，資料庫的穩定性與效能是支撐服務的基石。然而，當服務只有一台資料庫伺服器時，就像把所有雞蛋放在同一個籃子裡，充滿了風險。這時，「Replication」（複寫）就成了保障系統穩健的關鍵技術。

這篇文章將深入探討：

  1. SQL Replication 究竟解決了哪些核心問題？
  2. 它的運作原理與應用場景是什麼？
  3. 如何以 PostgreSQL 為例，一步步搭建起一套複寫環境？

問題的根源：單一資料庫的困境

想像一下，如果整個服務只依賴一台資料庫伺服器，可能會面臨以下幾個令人頭痛的問題：

  1. 單點故障 (Single Point of Failure)

    • 問題：如果這台唯一的資料庫因硬體故障、系統崩潰或網路中斷而停機，整個應用程式將會完全癱瘓。這意味著服務中斷、用戶流失與營收損失。
    • Replication 的解法：透過建立一個或多個備用資料庫（Replica），當主要資料庫（Primary）發生故障時，可以快速切換到備用資料庫，實現高可用性 (High Availability)，大幅縮短服務中斷時間。

  2. 讀取效能瓶頸 (Read Performance Bottleneck)

    • 問題：隨著用戶量增長，讀取請求（例如：查詢商品、瀏覽文章）會遠多於寫入請求（例如：下訂單、發表評論）。所有讀寫請求都集中在同一台伺服器上，會導致資料庫負載過高，查詢變慢，影響用戶體驗。
    • Replication 的解法：可以將讀取請求導向到 Replica 伺服器，而 Primary 伺服器專注處理寫入請求。這種讀寫分離 (Read-Write Splitting) 的架構能有效分攤負載 (Load Balancing)，提升整體的讀取效能與擴展性。

  3. 數據分析與報表產生的效能衝擊

    • 問題：營運或數據分析團隊經常需要執行複雜且耗時的 SQL 查詢來產生報表。這些重量級的查詢會佔用大量 CPU 和 I/O 資源，拖慢線上正在服務用戶的資料庫效能。
    • Replication 的解法：可以指定一台 Replica 作為專門的報表或分析伺服器。分析團隊可以在這台伺服器上盡情執行複雜查詢，而不會影響到線上服務的穩定性。

  4. 異地備份與災難恢復 (Disaster Recovery)

    • 問題：如果整個機房因天災（如火災、地震）或人為事故而損毀，即使有本地備份也無濟於事。
    • Replication 的解法：可以在不同地理位置的機房建立 Replica。這樣即使主要機房發生毀滅性災難，遠端的 Replica 依然保有完整的資料，確保業務能夠在最短時間內恢復。

Replication 的應用原理

SQL Replication 的核心思想很簡單：將一台資料庫（Primary）上的資料變更，持續、自動地複製到另一台或多台資料庫（Replica）上。

這個過程通常是這樣運作的：

  1. 寫入日誌 (Write-Ahead Log, WAL)：當 Primary 資料庫執行一個寫入操作（INSERT, UPDATE, DELETE）時，它會先將這個變更記錄到一個稱為「交易日誌」的檔案中。在 PostgreSQL 中，這個日誌被稱為 WAL。
  2. 傳送日誌 (Log Shipping)：Primary 伺服器會將這些新產生的 WAL 記錄，透過網路傳送給所有連接它的 Replica 伺服器。
  3. 重放日誌 (Log Replay)：Replica 伺服器收到 WAL 記錄後，會按照順序「重放」(Replay) 這些變更，從而將自己的資料狀態與 Primary 保持一致。

根據傳送與確認機制的不同，Replication 主要分為兩種類型：

  • 非同步複寫 (Asynchronous Replication)：Primary 執行完交易後，不等待 Replica 確認收到，直接回應客戶端。優點是效能高，對主資料庫影響小；缺點是在主庫故障的瞬間，可能有極少數已提交的交易還沒傳到備庫，造成微量資料遺失。這是最常見的設定。
  • 同步複寫 (Synchronous Replication)：Primary 執行完交易後，必須等待至少一台 Replica 確認已收到並寫入日誌，才回應客戶端。優點是能保證資料零遺失；缺點是會增加寫入延遲，影響主資料庫效能。

實戰演練：設定 PostgreSQL Streaming Replication

接下來，將動手設定 PostgreSQL 最常見的 串流複寫 (Streaming Replication)，它是一種高效的非同步複寫方式。

假設環境：

  • Primary Server: IP 192.168.1.100
  • Replica Server: IP 192.168.1.101
  • 兩台伺服器都已安裝相同主版本的 PostgreSQL (e.g., PostgreSQL 17)。

步驟一：設定 Primary Server (192.168.1.100)

1. 修改 postgresql.conf

找到並修改以下參數，讓資料庫監聽所有網路介面，並啟用複寫功能。

# /var/lib/pgsql/17/data/postgresql.conf

listen_addresses = '*'       # 允許來自任何 IP 的連線
wal_level = replica          # 設定 WAL 記錄等級為 replica，包含足夠複寫的資訊
max_wal_senders = 5          # 設定最多允許多少個複寫連線
wal_keep_size = 512MB        # 保留一定量的 WAL 檔案，防止 Replica 落後太多而連不上

2. 修改 pg_hba.conf 以允許 Replica 連線

加入一行設定，允許我們為複寫建立的特定使用者從 Replica 的 IP 連接。

# /var/lib/pgsql/17/data/pg_hba.conf
# 在檔案末尾加入下面這行
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      192.168.1.101/32        scram-sha-256

注意: METHOD 在較新版 PostgreSQL 預設為 scram-sha-256，舊版可能是 md5。請依據 password_encryption 設定。

3. 建立一個複寫專用的使用者

登入 psql，建立一個具備 REPLICATION 權限的使用者。

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'YourStrongPassword';

4. 重啟 Primary PostgreSQL 服務

讓所有設定生效。

sudo systemctl restart postgresql-17

步驟二：設定 Replica Server (192.168.1.101)

1. 停止 Replica 上的 PostgreSQL 服務

因為要用 Primary 的資料覆蓋它。

sudo systemctl stop postgresql-17

2. 清空資料目錄並從 Primary 備份資料

使用 pg_basebackup 工具，它會將 Primary 當前的所有資料完整地複製過來。

# 先把舊的資料目錄清空或備份
sudo mv /var/lib/pgsql/17/data /var/lib/pgsql/17/data_old

# 執行基礎備份
sudo -u postgres pg_basebackup -h 192.168.1.100 -U replicator -p 5432 -D /var/lib/pgsql/17/data -Fp -Xs -P -R

參數說明:

  • -h: Primary Server 的 IP
  • -U: 複寫使用者的名稱
  • -D: Replica Server 的資料存放目錄
  • -Fp: 格式為純文字 (plain)
  • -Xs: 備份期間產生的 WAL 也一併串流過來
  • -P: 顯示進度
  • -R: 非常重要！ 此參數會自動在資料目錄中建立 standby.signal 檔案，並在 postgresql.auto.conf 中寫入連線資訊 (primary_conninfo)，讓 PostgreSQL 啟動時知道自己是備用伺服器。

3. 檢查設定檔 (由 -R 參數自動生成)

pg_basebackup 的 -R 參數會自動幫我們完成大部分設定。

  • 它會在 /var/lib/pgsql/17/data/ 目錄下建立一個空的 standby.signal 檔案，這個檔案是告知 PostgreSQL 以備用模式啟動的標記。
  • 它會在 /var/lib/pgsql/17/data/postgresql.auto.conf 中寫入類似這樣的連線字串：
    primary_conninfo = 'user=replicator password=YourStrongPassword host=192.168.1.100 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

步驟三：啟動並驗證複寫

1. 啟動 Replica Server 的 PostgreSQL 服務

sudo systemctl start postgresql-17

2. 驗證複寫狀態

  • 在 Replica Server 上檢查日誌：

    sudo tail -f /var/lib/pgsql/17/pg_log/postgresql-*.log

    會看到類似 started streaming WAL from primary at... 的訊息。

  • 在 Primary Server 上查詢複寫狀態：
    登入 psql，執行以下查詢：

    SELECT * FROM pg_stat_replication;

    如果一切正常，會看到一行記錄，其中 client_addr 是 192.168.1.101，state 是 streaming。

3. 進行測試

  • 在 Primary Server 上建立一個新的資料表並插入資料：
    CREATE TABLE replication_test (id INT, message TEXT);
INSERT INTO replication_test VALUES (1, 'Hello from Primary!');
  • 稍等一兩秒，在 Replica Server 上查詢該資料表：
    -- 連接到 Replica 的 psql
SELECT * FROM replication_test;
    能立即看到剛剛插入的資料，代表已成功搭建起一套 PostgreSQL Streaming Replication。

結論

SQL Replication 是現代化應用程式架構中不可或缺的一環。它從根本上解決了單點故障、效能瓶頸和災難恢復等核心痛點。基於此架構，可以進一步探索自動故障轉移 (Automatic Failover)、負載平衡等更進階的主題。