本文主要介紹 Windows 環(huán)境下搭建 PostgreSQL 的主從邏輯復(fù)制，關(guān)于 PostgreSQl 的相關(guān)運(yùn)維文章，網(wǎng)絡(luò)上大多都是 Linux 環(huán)境下的操作，鮮有在 Windows 環(huán)境下配置的教程，所以本文采用 Windows 環(huán)境作為演示系統(tǒng)來(lái)進(jìn)行 PostgreSQL 高可用數(shù)據(jù)庫(kù)服務(wù)的搭建。

關(guān)于 Windows 系統(tǒng) PostgreSQL 的安裝方法可以直接看之前的博客 http://www.dbjr.com.cn/article/261744.htm

在Windows 環(huán)境運(yùn)行 PostgreSQL 在連接數(shù)的配置需要注意以下這個(gè)知識(shí)點(diǎn)：

由于PostgreSQL所依賴的一些庫(kù)依賴于user32.dll，后者從內(nèi)存中一塊稱為桌面堆(Desktop Heap)的區(qū)域中分配內(nèi)存。桌面堆被分配給了每一個(gè)登錄的會(huì)話，在 Windows 10 以上的操作系統(tǒng)通常一個(gè)非交互的會(huì)話將會(huì)分配給768KB。每個(gè)交互登錄會(huì)話將會(huì)分配給20MB的桌面堆，每個(gè)postgres進(jìn)程典型的桌面堆消耗是3.2KB。

由于PostgreSQL所依賴的一些庫(kù)依賴于user32.dll，后者從內(nèi)存中一塊稱為桌面堆(Desktop Heap)的區(qū)域中分配內(nèi)存。桌面堆被分配給了每一個(gè)登錄的會(huì)話，在 Windows 10 以上的操作系統(tǒng)通常一個(gè)非交互的會(huì)話將會(huì)分配給768KB。每個(gè)交互登錄會(huì)話將會(huì)分配給20MB的桌面堆，每個(gè)postgres進(jìn)程典型的桌面堆消耗是3.2KB。

• 當(dāng)作為Windows服務(wù)運(yùn)行時(shí)，因?yàn)槊總€(gè)非交互會(huì)話將會(huì)分配768KB的桌面堆，所以大約可以支撐 200-220個(gè)連接。
• 當(dāng)在命令行方式運(yùn)行時(shí)，因?yàn)槊總€(gè)交互登錄會(huì)話將會(huì)分配20MB的桌面堆，所以大約可以支撐 5000-6000個(gè)連接。

以上說(shuō)的值是操作系統(tǒng)默認(rèn)的值，這個(gè)堆分配大小可以通過(guò)調(diào)整注冊(cè)表來(lái)進(jìn)行修改，但是此操作需要非常小心，一旦指定的值過(guò)大，系統(tǒng)將無(wú)法啟動(dòng)，所以不推薦去手動(dòng)修改這個(gè)值。如果想要了解更多這個(gè)知識(shí)點(diǎn)可以看 微軟官方的這個(gè)說(shuō)明：https://learn.microsoft.com/zh-CN/troubleshoot/windows/win32/user32-kernel32-not-initialize

結(jié)合上面的知識(shí)點(diǎn)，所以我們?cè)赪indows環(huán)境下配置 PostgreSQL 的最大連接數(shù)時(shí)，如果采用 Windows 服務(wù)模式運(yùn)行則配置最大連接數(shù)為200比較好，如果需要200個(gè)以上的并發(fā)連接，則最好采用控制臺(tái)形式啟動(dòng)運(yùn)行PostgreSQL，同時(shí)結(jié)合計(jì)算機(jī)的可以內(nèi)存去綜合考慮一般100個(gè)連接數(shù)會(huì)消耗 1G內(nèi)存，所以假設(shè)我們電腦內(nèi)存為8G，考慮到系統(tǒng)本身運(yùn)行需要2G內(nèi)存，所以這是 PostgreSQL 的最大連接數(shù)配置為500 左右即可。

PostgreSQL控制臺(tái)運(yùn)行，啟動(dòng)和停止命令如下：

啟動(dòng) pg_ctl.exe -D "D:\Software\PostgreSQL\data" start
停止 pg_ctl.exe -D "D:\Software\PostgreSQL\data" stop

-D 的參數(shù) "D:\Software\PostgreSQL\data" 是 PostgreSQL 的數(shù)據(jù)實(shí)例位置

今天我們采用邏輯同步復(fù)制來(lái)實(shí)現(xiàn)主從數(shù)據(jù)庫(kù)的配置，首選在主數(shù)據(jù)庫(kù)上調(diào)整 postgresql.conf 配置文件啟用邏輯復(fù)制功能
wal_level = logical

配置調(diào)整之后需要重新啟動(dòng)主數(shù)據(jù)庫(kù)實(shí)例。

然后在主數(shù)據(jù)庫(kù)對(duì)我們需要配置的數(shù)據(jù)庫(kù)執(zhí)行邏輯發(fā)布
CREATE PUBLICATION p FOR ALL TABLES WITH (publish = 'insert, update, delete, truncate', publish_via_partition_root = false);
其中 p 為這個(gè)發(fā)布設(shè)置的名稱，也可以自定義為其它。

然后在從數(shù)據(jù)庫(kù)實(shí)例上選擇我們要用來(lái)訂閱主實(shí)例的數(shù)據(jù)庫(kù)，然后執(zhí)行創(chuàng)建邏輯訂閱

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 為這個(gè)訂閱設(shè)置的名稱 'host=127.0.0.1 port=5432 user=postgres dbname=xxxxx connect_timeout=10 password=xxxxxx' 為主庫(kù)的連接信息

因?yàn)槲覀儎?chuàng)建的訂閱 synchronous_commit 采用的是 remote_apply 模式，該模式可以理解為同步復(fù)制，當(dāng)客戶端像主庫(kù)提交事務(wù)之后，需要等 synchronous_standby_names 總配置的節(jié)點(diǎn)全部完成 remote_apply 收到數(shù)據(jù)之后，主庫(kù)才會(huì)給備庫(kù)返回事務(wù)成功提交的狀態(tài)，創(chuàng)建好名為 s 的訂閱創(chuàng)建之后，我們?cè)俅未蜷_(kāi) 主庫(kù)的 postgresql.conf 文件進(jìn)行調(diào)整設(shè)置
synchronous_standby_names = 's'

調(diào)整配置文件之后記得重啟主庫(kù)的數(shù)據(jù)庫(kù)實(shí)例
這樣我們就完成本地 localhost 的 PostgreSQL 實(shí)例中 csapp 數(shù)據(jù)庫(kù)和 遠(yuǎn)程服務(wù)器上 caspp 數(shù)據(jù)庫(kù)的主從配置。

這里要注意的一點(diǎn)是 PostgreSQL 的 邏輯訂閱并不會(huì)同步DDL操作 ，所以對(duì)于數(shù)據(jù)庫(kù)的建表等操作是不會(huì)進(jìn)行主從同步的，我們需要手動(dòng)維護(hù) 主從實(shí)例數(shù)據(jù)庫(kù)上的表結(jié)構(gòu)使其保持一致。
當(dāng)在主和從數(shù)據(jù)庫(kù)都創(chuàng)建完成表之后需要在從庫(kù)上執(zhí)行以下刷新訂閱的 SQL，每次主庫(kù)新增或者刪除了表，從庫(kù)都需要執(zhí)行這個(gè)操作。

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

然后我們?cè)谥鲙?kù)的數(shù)據(jù)庫(kù)表中進(jìn)行數(shù)據(jù)操作，所有的操作就會(huì)被同步到 從庫(kù)的數(shù)據(jù)表中。

下面介紹如果想要?jiǎng)h除發(fā)布設(shè)置和訂閱設(shè)置的操作
在主庫(kù)執(zhí)行
SELECT * FROM pg_publication 可以查詢當(dāng)前主庫(kù)的所有發(fā)布信息

DROP PUBLICATION p 刪除名字為 p 的發(fā)布信息

在從庫(kù)執(zhí)行：
SELECT * FROM pg_subscription 可以查詢當(dāng)前從庫(kù)的所有訂閱信息

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

然后記得去主庫(kù)的 postgresql.conf 找到 synchronous_standby_names 刪除 s 節(jié)點(diǎn)的配置
#synchronous_standby_names='s'
如果只有一個(gè)從節(jié)點(diǎn)的，則直接添加 # 對(duì) synchronous_standby_names 進(jìn)行注釋即可

當(dāng)有多個(gè)從庫(kù)訂閱的時(shí)候synchronous_standby_names 還可以采用以下配置模式

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

這里有一點(diǎn)需要注意，這是 PostgreSQL 在同步復(fù)制時(shí)的一個(gè)已知問(wèn)題，假設(shè) 一個(gè)主庫(kù)，一個(gè)備庫(kù) s1，采用同步模式，然后 synchronous_standby_names 配置為 synchronous_standby_names='s1',雖然從配置上來(lái)看似乎數(shù)據(jù)必須要提交到s1并且s1成功響應(yīng)之后，主庫(kù)才會(huì)為客戶端返回事務(wù)操作成功的響應(yīng)，但是實(shí)際情況下，當(dāng)備庫(kù)掛掉的情況下，主庫(kù)在收到一個(gè)事務(wù)操作時(shí)，在等待 s1 備庫(kù)的返回時(shí)因?yàn)?s1庫(kù)已經(jīng)掛掉了所以這個(gè)操作肯定會(huì)超時(shí)，當(dāng)主備節(jié)點(diǎn)通信超時(shí)之后，主節(jié)點(diǎn)還是會(huì)像客戶端返回事務(wù)成功提交的命令，客戶端的操作還是會(huì)成功，同時(shí)因?yàn)槊總€(gè)事務(wù)操作都要經(jīng)歷這個(gè)超時(shí)的流程，所以客戶端的所有事務(wù)操作都會(huì)相對(duì)很卡。

比如每個(gè) insert 都會(huì)經(jīng)過(guò)主庫(kù)和備庫(kù)的這個(gè)通信超時(shí)過(guò)程，所以每個(gè) insert 動(dòng)作都變成了大約30秒次才能完成，就會(huì)導(dǎo)致應(yīng)用程序很卡。這時(shí)候就相當(dāng)于主庫(kù)在以（很卡的）獨(dú)立模式運(yùn)行，這個(gè)情況在備庫(kù)重新上線之后就會(huì)恢復(fù)正常（如果備庫(kù)短期之內(nèi)無(wú)法恢復(fù)，可以調(diào)整主庫(kù)的 synchronous_standby_names設(shè)置 移除對(duì)于s1備庫(kù)的事務(wù)等待驗(yàn)證，變?yōu)閱螏?kù)運(yùn)行模式重啟實(shí)例之后也就不會(huì)卡了），但是要注意當(dāng)主庫(kù)脫離備庫(kù)獨(dú)立運(yùn)行時(shí)，如果這個(gè)時(shí)候主庫(kù)發(fā)生災(zāi)難比如硬盤壞掉，則就會(huì)產(chǎn)生數(shù)據(jù)丟失。所以建議至少有2個(gè)備庫(kù)來(lái)提升保障級(jí)別

至此 Windows 環(huán)境搭建 PostgreSQL 邏輯復(fù)制高可用架構(gòu)數(shù)據(jù)庫(kù)服務(wù) 就講解完了，有任何不明白的，可以在文章下面評(píng)論或者私信我，歡迎大家積極的討論交流，有興趣的朋友可以關(guān)注我目前在維護(hù)的一個(gè) .NET 基礎(chǔ)框架項(xiàng)目，項(xiàng)目地址如下
https://github.com/berkerdong/NetEngine.git

https://gitee.com/berkerdong/NetEngine.git

到此這篇關(guān)于Windows 環(huán)境搭建 PostgreSQL 邏輯復(fù)制高可用架構(gòu)數(shù)據(jù)庫(kù)服務(wù)的文章就介紹到這了,更多相關(guān)Windows 環(huán)境搭建 PostgreSQL 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論