技術文章 » Windows 環境搭建 PostgreSQL 物理複製高可用架構資料庫服務

Windows 環境搭建 PostgreSQL 物理複製高可用架構資料庫服務

2022-11-02 15:01:05

PostgreSQL 高可用資料庫的常見搭建方式主要有兩種，邏輯複製和物理複製，上週已經寫過了關於在Windows環境搭建PostgreSQL邏輯複製的教學，這周來記錄一下 物理複製的搭建方法。

首先介紹一下邏輯複製和物理複製的一些基本區別：

  • 物理複製要求多個範例之間大版本一致,並且作業系統平臺一致，如主範例是 Windows環境下的 PostgreSQL15 則 從範例也必須是這個環境和版本，邏輯複製則沒有要求。
  • 物理複製是直接傳遞 WAL歸檔 檔案，在從範例進行重放執行，可以理解為實時的 WAL歸檔恢復，所以延遲低，效能高。，
  • 邏輯複製可以簡單理解為解析了WAL歸檔檔案中的資訊，處理成為 標準的SQL語句，傳遞給存庫進行執行，相對於直接傳遞WAL效能較低，延遲高。
  • 物理複製不需要像邏輯複製一些去手動的建立資料庫，資料表，因為物理複製是直接恢復WAL所以包含了DDL操作，邏輯複製則需要自己進行DDL操作。
  • 邏輯複製更加靈活，可以自己指定需要複製的庫，從範例，還可以建立其他庫用於其他業務，而物理複製則是面向整個範例進行的，從範例和主範例100%一致最多隻能進行唯讀操作。

關於 Windows 系統 PostgreSQL 的安裝方法可以直接看之前的部落格 Windows 系統 PostgreSQL 手工安裝設定方法

如果追求高效能，高一致性的資料庫複製備份方案建議採用物理複製的方式。

搭建物理複製模式的主從訂閱首先要調整主範例的 postgresql.conf 檔案
wal_level = replica
synchronous_commit = remote_apply

因為我們採用的 synchronous_commit = remote_apply 是同步複製的模式，該模式可以理解為同步複製，當用戶端像主範例提交事務之後，需要等 synchronous_standby_names 總設定的節點全部完成 remote_apply 收到資料之後，主範例才會給備庫返回事務成功提交的狀態，建立好名為 s 的訂閱建立之後，我們再次開啟 主範例的 postgresql.conf 檔案進行調整設定
synchronous_standby_names = 's'

當有多個從範例從主範例同步的時候synchronous_standby_names 還可以採用以下設定模式

  • synchronous_standby_names='s1' 代表s1備機返回就可以提交。
  • synchronous_standby_names='FIRST 2 (s1,s2,s3)' 代表s1，s2，s3三個備機中前兩個s1和s2返回主範例就可以提交。
  • synchronous_standby_names='ANY 2 (s1,s2,s3)' 代表s1，s2，s3三個備機中任意兩個備機返回主範例就可以提交。
  • synchronous_standby_names='ANY 2 (*)' 代表所有備機中任意兩個備機返回主範例就可以提交。
  • synchronous_standby_names='*' 代表匹配任意主機，也就是任意主機返回就可以提交。

這裡有一點需要注意，這是 PostgreSQL 在同步複製時的一個已知問題，假設 一個主範例，一個備庫 s1，採用同步模式，然後 synchronous_standby_names 設定為 synchronous_standby_names='s1',雖然從設定上來看似乎資料必須要提交到s1並且s1成功響應之後，主範例才會為使用者端返回事務操作成功的響應，但是實際情況下，當備庫掛掉的情況下，主範例在收到一個事務操作時，在等待 s1 備庫的返回時因為 s1庫已經掛掉了所以這個操作肯定會超時，當主備節點通訊超時之後，主節點還是會像使用者端返回事務成功提交的命令，使用者端的操作還是會成功，同時因為每個事務操作都要經歷這個超時的流程，所以使用者端的所有事務操作都會相對很卡。

比如每個 insert 都會經過主範例和備庫的這個通訊超時過程，所以每個 insert 動作都變成了大約30秒次才能完成，就會導致應用程式很卡。這時候就相當於主範例在以（很卡的）獨立模式執行，這個情況在備庫重新上線之後就會恢復正常（如果備庫短期之內無法恢復，可以調整主範例的 synchronous_standby_names設定 移除對於s1備庫的事務等待驗證，變為單庫執行模式重啟範例之後也就不會卡了），但是要注意當主範例脫離備庫獨立執行時，如果這個時候主範例發生災難比如硬碟壞掉，則就會產生資料丟失。所以建議至少有2個從範例來提升保障級別。

然後還需要調整主範例的 pg_hba.conf，新增 replication 模式的連線白名單設定。
host replication all 0.0.0.0/0 scram-sha-256

調整組態檔之後記得重啟主範例。

主範例重啟之後，我們還需要連線到主範例建立複製槽，預設情況下WAL歸檔檔案是迴圈捲動清理，這就會導致一個問題如果我們的從範例掛機之後離線的時間較長，就有可能因為主範例的WAL檔案已經迴圈捲動刪除了，這種情況下就算從範例修復好之後重新上線，因為主範例的部分WAL歸檔檔案已經清理了，也無法再追趕上我們主範例的資料進度，從範例會直接報錯。因為有這種場景的存在所以 PostgreSQL 裡面出現了一個複製槽的概念，主範例可以建立多個複製槽，一個複製槽繫結給一個從範例使用，複製槽的好處在於會確保從範例獲取到WAL檔案之後才會進行清理，不會有前面說的捲動迴圈自動清理的問題。

複製槽的維護都在主範例進行：建立，查詢，刪除的語句如下
建立複製槽
SELECT * FROM pg_create_physical_replication_slot('slot1');

查詢全部的複製槽
SELECT slot_name, slot_type, active FROM pg_replication_slots; slot_name | slot_type | active

刪除複製槽
SELECT * FROM pg_drop_replication_slot('slot1')

至此主範例的設定就都完成了，接下來就是準備我們的從範例，可以直接停止主範例的執行，然後把PostgreSQL資料夾和Data整體打包壓縮複製一份到新的伺服器上啟動起來作為從範例。
我這裡選擇直接把雲伺服器上的 PostgreSQL 打包壓縮然後複製到本地解壓，作為從範例


在本地解壓之後，做為 從範例 需要做如下的調整，postgresql.conf
primary_conninfo = 'host=x.x.x.x port=5432 user=postgres password=xxxxxx application_name=s'
primary_slot_name = 'slot1'


primary_conninfo 主要內容就是我們主範例的連線字串資訊然後加一個 application_name ，application_name 和我們前面在主範例上設定的 synchronous_standby_names 關聯，前面我們設定了主範例的所有事務操作都需要同步等待 名字為 s 的備庫執行完成
primary_slot_name 則是複製槽的名稱我們前面建立了一個 slot1 的複製槽，給我們的這個從範例使用。

這裡需要注意一點，在設定的時候如果有多個從範例，則一個從範例對應一個複製槽，繫結一個 application_name。
然後在 data 目錄下新建一個空檔案
standby.signal

這個檔案的其實一個訊號標記，標識我們當前的範例時一個唯讀範例，不可以用於資料插入。
然後啟動備庫就可以了，正常情況會看到如下介面

這時候我們可以嘗試去主範例建立一個資料庫做一些操作，然後連線從範例，就會發現兩邊都是互相同步的。

如果要解除從範例和主範例的關聯，操作如下：
從主範例的 postgresql.conf 找到 synchronous_standby_names 刪除 s 節點的設定
#synchronous_standby_names='s'
如果只有一個從節點的，則直接新增 # 對 synchronous_standby_names 進行註釋即可
調整之後重啟主範例。

然後開啟從範例的 postgresql.conf，註釋
#primary_conninfo
#primary_slot_name
設定節點的資訊，然後刪除 data 目錄下的 standby.signal 檔案，重新啟動從範例即可。

至此 Windows 環境搭建 PostgreSQL 物理複製高可用架構資料庫服務 就講解完了，有任何不明白的，可以在文章下面評論或者私信我，歡迎大家積極的討論交流，有興趣的朋友可以關注我目前在維護的一個 .NET 基礎框架專案，專案地址如下
https://github.com/berkerdong/NetEngine.git
https://gitee.com/berkerdong/NetEngine.git