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

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