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

• 2022 年 10 月 26 日
• 筆記

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

關於 Windows 系統 PostgreSQL 的安裝方法可以直接看之前的部落格 //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個連接。

以上說的值是作業系統默認的值，這個堆分配大小可以通過調整註冊表來進行修改，但是此操作需要非常小心，一旦指定的值過大，系統將無法啟動，所以不推薦去手動修改這個值。如果想要了解更多這個知識點可以看 微軟官方的這個說明：
//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’ 為主庫的連接資訊

這樣我們就完成本地 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 的訂閱資訊

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