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

本文主要介紹 Windows 環境下搭建 PostgreSQL 的主從邏輯複製，關於 PostgreSQl 的相關運維文章，網路上大多都是 Linux 環境下的操作，鮮有在 Windows 環境下配置的教程，所以本文采用 Windows 環境作為演示系統來進行 PostgreSQL 高可用資料庫服務 ...

本文主要介紹 Windows 環境下搭建 PostgreSQL 的主從邏輯複製，關於 PostgreSQl 的相關運維文章，網路上大多都是 Linux 環境下的操作，鮮有在 Windows 環境下配置的教程，所以本文采用 Windows 環境作為演示系統來進行 PostgreSQL 高可用資料庫服務的搭建。

關於 Windows 系統 PostgreSQL 的安裝方法可以直接看之前的博客 https://www.cnblogs.com/berkerdong/p/16645493.html

在Windows 環境運行 PostgreSQL 在連接數的配置需要註意以下這個知識點：

由於PostgreSQL所依賴的一些庫依賴於user32.dll，後者從記憶體中一塊稱為桌面堆(Desktop Heap)的區域中分配記憶體。桌面堆被分配給了每一個登錄的會話，在 Windows 10 以上的操作系統通常一個非交互的會話將會分配給768KB。每個交互登錄會話將會分配給20MB的桌面堆，每個postgres進程典型的桌面堆消耗是3.2KB。

當作為Windows服務運行時，因為每個非交互會話將會分配768KBMB的桌面堆，所以大約可以支撐 200-220個連接。

當在命令行方式運行時，因為每個交互登錄會話將會分配20MB的桌面堆，所以大約可以支撐 5000-6000個連接。

以上說的值是操作系統預設的值，這個堆分配大小可以通過調整註冊表來進行修改，但是此操作需要非常小心，一旦指定的值過大，系統將無法啟動，所以不推薦去手動修改這個值。如果想要瞭解更多這個知識點可以看微軟官方的這個說明：
https://learn.microsoft.com/zh-CN/troubleshoot/windows/win32/user32-kernel32-not-initialize

結合上面的知識點，所以我們在Windows環境下配置 PostgreSQL 的最大連接數時，如果採用 Windows 服務模式運行則配置最大連接數為200比較好，如果需要200個以上的併發連接，則最好採用控制台形式啟動運行PostgreSQL，同時結合電腦的可以記憶體去綜合考慮一般100個連接數會消耗 1G記憶體，所以假設我們電腦記憶體為8G，考慮到系統本身運行需要2G記憶體，所以這是 PostgreSQL 的最大連接數配置為500 左右即可。

PostgreSQL控制台運行，啟動和停止命令如下：

啟動 pg_ctl.exe -D "D:\Software\PostgreSQL\data" start
停止 pg_ctl.exe -D "D:\Software\PostgreSQL\data" stop

-D 的參數 "D:\Software\PostgreSQL\data" 是 PostgreSQL 的數據實例位置

今天我們採用邏輯同步複製來實現主從資料庫的配置，首選在主資料庫上調整 postgresql.conf 配置文件啟用邏輯複製功能
wal_level = logical

配置調整之後需要重新啟動主資料庫實例。

然後在主資料庫對我們需要配置的資料庫執行邏輯發佈
CREATE PUBLICATION p FOR ALL TABLES WITH (publish = 'insert, update, delete, truncate', publish_via_partition_root = false);
其中 p 為這個發佈設置的名稱，也可以自定義為其它。

然後在從資料庫實例上選擇我們要用來訂閱主實例的資料庫，然後執行創建邏輯訂閱

CREATE SUBSCRIPTION s
    CONNECTION 'host=127.0.0.1 port=5432 user=postgres dbname=xxxxx connect_timeout=10 password=xxxxxx'
    PUBLICATION p
    WITH (connect = true, enabled = true, copy_data = true, create_slot = true, synchronous_commit = 'remote_apply');

其中 s 為這個訂閱設置的名稱 'host=127.0.0.1 port=5432 user=postgres dbname=xxxxx connect_timeout=10 password=xxxxxx' 為主庫的連接信息

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

調整配置文件之後記得重啟主庫的資料庫實例
這樣我們就完成本地 localhost 的 PostgreSQL 實例中 csapp 資料庫和遠程伺服器上 caspp 資料庫的主從配置。

這裡要註意的一點是 PostgreSQL 的邏輯訂閱並不會同步DDL操作，所以對於資料庫的建表等操作是不會進行主從同步的，我們需要手動維護主從實例資料庫上的表結構使其保持一致。
當在主和從資料庫都創建完成表之後需要在從庫上執行以下刷新訂閱的 SQL，每次主庫新增或者刪除了表，從庫都需要執行這個操作。

ALTER SUBSCRIPTION s
    REFRESH PUBLICATION WITH (copy_data = true);

然後我們在主庫的資料庫表中進行數據操作，所有的操作就會被同步到從庫的數據表中。

下麵介紹如果想要刪除發佈設置和訂閱設置的操作
在主庫執行
SELECT * FROM pg_publication 可以查詢當前主庫的所有發佈信息

DROP PUBLICATION p 刪除名字為 p 的發佈信息

在從庫執行：
SELECT * FROM pg_subscription 可以查詢當前從庫的所有訂閱信息

DROP SUBSCRIPTION s 刪除名字為 s 的訂閱信息

然後記得去主庫的 postgresql.conf 找到 synchronous_standby_names 刪除 s 節點的配置
**#synchronous_standby_names = **
如果只有一個從節點的，則直接添加 # 對 synchronous_standby_names 進行註釋即可

當有多個從庫訂閱的時候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個備庫來提升保障級別

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