大家好， 這次大表哥帶來的分享是 PG 15 集成 PGPool 4.3 基于springboot 2.5 的HA 測試。

**重要的聲明！?。。?br /> PG 15 beta 版本是 上個月中旬新發(fā)布的版本， PG Pool 4.3 也是目前（寫文章的時間是2022-06-24）最新的版本。 本文是存技術(shù)方案測試案例篇。生產(chǎn)環(huán)境強烈不建議2者都使用最新的版本來部署。本文很有可能是全網(wǎng)第一篇都使用最新版本來測試的。 **

接下來我們開始愉快的安裝測試吧：

PG 15的安裝： 可以參考我之前的文章 http://www.sunline.cc/db/411245

我們這次專注于PG Pool 的安裝：(大表哥一如既往的保姆式 step by step 的style)

PG pool 的官網(wǎng)的下載地址:

https://www.pgpool.net/mediawiki/index.php/Downloads

同樣PG pool 提供了rpm , yum 和 源碼 的三種安裝方式。

我們這里選擇下載源碼的版本是 pgpool-II 4.3 (Stable) 發(fā)布日期是 2022-05-19

下載：

INFRA [postgres@wqdcsrv3352 postgreSQL]# wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.3.2.tar.gz
--2022-06-19 19:41:40--  https://www.pgpool.net/mediawiki/images/pgpool-II-4.3.2.tar.gz
Resolving www.pgpool.net (www.pgpool.net)... 13.249.146.41, 13.249.146.110, 13.249.146.6, ...
Connecting to www.pgpool.net (www.pgpool.net)|13.249.146.41|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5846769 (5.6M) [application/x-gzip]
Saving to: ‘pgpool-II-4.3.2.tar.gz’


100%[============================================================================================>] 5,846,769   5.52MB/s   in 1.0s  


2022-06-19 19:41:42 (5.52 MB/s) - ‘pgpool-II-4.3.2.tar.gz’ saved [5846769/5846769]

解壓源代碼并進(jìn)行重命名：

INFRA [postgres@wqdcsrv3352 postgreSQL]# tar -xvf pgpool-II-4.3.2.tar.gz
INFRA [postgres@wqdcsrv3352 postgreSQL]# mv pgpool-II-4.3.2 pgpool

安裝步驟可以依賴官方文檔： 這個源碼安裝和其他的C語言開發(fā)的軟件都是一樣的

https://www.pgpool.net/docs/latest/en/html/install-pgpool.html

編譯的參數(shù)簡單說明：

–prefix=path 安裝編譯之后的路徑 默認(rèn)是

我們創(chuàng)建編譯路徑進(jìn)行編譯：

INFRA [postgres@wqdcsrv3352 postgreSQL]# mkdir -p /opt/postgreSQL/pgpool4.3
INFRA [postgres@wqdcsrv3352 pgpool]# ./configure --prefix=/opt/postgreSQL/pgpool4.3
INFRA [postgres@wqdcsrv3352 pgpool]# make && make install

編譯完成后，我們查看以一下版本：

INFRA [postgres@wqdcsrv3352 bin]# /opt/postgreSQL/pgpool4.3/bin/pgpool --version
pgpool-II version 4.3.2 (tamahomeboshi)

接下來，我們需要安裝 pgpool-recovery, 我們進(jìn)入到PG pool的源碼路徑下：

INFRA [postgres@wqdcsrv3352 src]# pwd
/opt/postgreSQL/pgpool/src/sql/pgpool-recovery

INFRA [postgres@wqdcsrv3352 pgpool-recovery]# make && make install

我們來創(chuàng)建我們的 extension : 注意我們需要安裝在數(shù)據(jù)庫 template1 下面

INFRA [postgres@wqdcsrv3352 pgpool-recovery]# psql -h /tmp -p 1992
postgres@[local:/tmp]:1992=#14277 \c template1
template1@[local:/tmp]:1992=#14286 create extension pgpool_recovery;
CREATE EXTENSION

對于 Pgpool-II 3.3 or later 的版本，我們還需要在配置文件中設(shè)置參數(shù) pgpool.pg_ctl

vi postgresql.conf 中添加
pgpool.pg_ctl='/opt/postgreSQL/pg15/bin/pg_ctl'

reload 生效

postgres@[local:/tmp]:1992=#15017 select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

okay。 至此我們單機版的PG POOL 簡單安裝已經(jīng)完畢，

下面我們要配置一下 1 節(jié)點的PG POOL + 3 節(jié)點的數(shù)據(jù)源 (1 主 + 2 從) 的架構(gòu)

我們在啟動pgpool 之前，還需要做如下的配置：

1）關(guān)閉機器之間的防火墻

sudo systemctl stop firewalld.service
sudo systemctl disable firewalld.service

2）打通機器之間的SSH 免密登錄
默認(rèn)的情況下， root 賬戶啟動PGPOOL 我們要打通 root 賬號 到 postgres 賬號之間的免密

需要在 root 下執(zhí)行：

INFRA [root@wqdcsrv3352 .ssh]# ssh-copy-id -i id_rsa_pgpool.pub postgres@10.67.38.50
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa_pgpool.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys


Number of key(s) added: 1


Now try logging into the machine, with:   "ssh 'postgres@10.67.38.50'"
and check to make sure that only the key(s) you wanted were added.



INFRA [root@wqdcsrv3352 .ssh]# ssh-copy-id -i id_rsa_pgpool.pub postgres@10.67.39.149
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa_pgpool.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys


Number of key(s) added: 1


Now try logging into the machine, with:   "ssh 'postgres@10.67.39.149'"
and check to make sure that only the key(s) you wanted were added.


INFRA [root@wqdcsrv3352 .ssh]# ssh-copy-id -i id_rsa_pgpool.pub postgres@10.67.39.49
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa_pgpool.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys


Number of key(s) added: 1


Now try logging into the machine, with:   "ssh 'postgres@10.67.39.49'"
and check to make sure that only the key(s) you wanted were added.

3）HA 自動切換的腳本的改動： 一共有2個

配置 failover 和 follow_primary 的 腳本

failover.sh

INFRA [postgres@wqdcsrv3352 etc]# cp failover.sh.sample failover.sh
INFRA [postgres@wqdcsrv3352 etc]# vi failover.sh

修要修改 PGHOME=/opt/postgreSQL/pg15

follow_primary.sh ： 注意如果是一主一從，則不需要配置這個腳本。 3個節(jié)點（包含3節(jié)點），在發(fā)生主從切換的時候 ，其他的從節(jié)點需要重定向復(fù)制源。這個時候就需要這個腳本

INFRA [postgres@wqdcsrv3352 etc]# cp follow_primary.sh.sample follow_primary.sh
INFRA [postgres@wqdcsrv3352 etc]# vi follow_primary.sh

PGHOME=/opt/postgreSQL/pg15
ARCHIVEDIR=/data/postgreSQL/1992/archive_wals
REPLUSER=repluser
PCP_USER=postgres
PGPOOL_PATH=/opt/postgreSQL/pgpool4.3
PCP_PORT=9898

4)創(chuàng)建數(shù)據(jù)庫的健康監(jiān)測賬戶

postgres@[local:/tmp]:1992=#63922 CREATE ROLE pgpool  WITH LOGIN password 'pgpool';
CREATE ROLE
postgres@[local:/tmp]:1992=#63922 grant pg_monitor TO pgpool;
GRANT ROLE

5)配置核心配置文件 pgpool.conf

INFRA [postgres@wqdcsrv3352 etc]# cp pgpool.conf.sample pgpool.conf
INFRA [postgres@wqdcsrv3352 etc]# vi pgpool.conf

backend_clustering_mode = 'streaming_replication' --流復(fù)制的模式
listen_addresses = '*'  -- 允許遠(yuǎn)程連接
port = 9999  -- pgpool 監(jiān)聽的端口
pid_file_name = '/opt/postgreSQL/pgpool4.3/pgpool.pid'
# Backend Connection Settings -- 后臺PG數(shù)據(jù)庫的配置
pool_passwd = 'pool_passwd'


backend_hostname0 = '10.67.38.50'
backend_port0 = 1992
backend_weight0 = 1
backend_data_directory0 = '/data/postgreSQL/1992/backups'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'pg50'




backend_hostname1 = '10.67.39.149'
backend_port1 = 1992
backend_weight1 = 1
backend_data_directory1 = '/data/postgreSQL/1992/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'pg149'


backend_hostname1 = '10.67.39.49'
backend_port1 = 1992
backend_weight1 = 1
backend_data_directory1 = '/data/postgreSQL/1992/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'pg49'

### 復(fù)制檢查延時設(shè)置

sr_check_period = 10
sr_check_user = 'pgpool'
sr_check_password = 'pgpool'
sr_check_database = 'postgres'

### 主從HA 健康監(jiān)測賬號

health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = 'pgpool'
health_check_database = 'postgres'

### 主從切換調(diào)用的 shell 腳本配置

failover_command = '/opt/postgreSQL/pgpool4.3/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/opt/postgreSQL/pgpool4.3/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

6）創(chuàng)建密碼文件： 為了 failover 的時候 執(zhí)行 promote的命令用

注意這個在 root 賬戶下, 以為我們是用root 啟動的 PGPOOL

INFRA [root@wqdcsrv3352 ~]# cat .pgpass
10.67.38.50:1992:postgres:repluser:repluser
10.67.39.49:1992:postgres:repluser:repluser
10.67.39.149:1992:postgres:repluser:repluser
10.67.38.50:1992:postgres:postgres:postgres
10.67.39.49:1992:postgres:postgres:postgres
10.67.39.149:1992:postgres:postgres:postgres

INFRA [root@wqdcsrv3352 ~]# chmod 600 ./.pgpass

7）為PCP 創(chuàng)建密碼文件
注意這個在 root 賬戶下, 以為我們是用root 啟動的 PGPOOL, 這個為了 attach 節(jié)點用的

INFRA [root@wqdcsrv3352 ~]# vi .pcppass
INFRA [root@wqdcsrv3352 ~]# chmod 600 .pcppass
INFRA [root@wqdcsrv3352 ~]# cat .pcppass
localhost:9898:postgres:postgres

8）配置PGPOOL 賬號信息 編輯文件 pool_passwd， 默認(rèn)是在 PGPOOL_HOME/etc/下的
這里要注意了， 由于從PG14開始 默認(rèn)的數(shù)據(jù)庫加密變成 scram-sha-256 的方式。
所以我們不需要用MD5的方式 進(jìn)行密碼二次加密了， 對于 scram-sha-256 這加密方式， PG的 pool_passwd直接寫成明文也是可以工作的。

具體詳情可以參考文檔 ： https://www.pgpool.net/docs/42/en/html/auth-methods.html

INFRA [postgres@wqdcsrv3352 etc]# cat pool_passwd
postgres:postgres

9)配置PGPOOL的HBA 文件

host    all         postgres         0.0.0.0/0        scram-sha-256

10. PCP conf 里面添加管理員的賬號和密碼 (注意這個是MD5的模式)

INFRA [postgres@wqdcsrv3352 etc]# view pcp.conf
# USERID:MD5PASSWD
postgres:e8a48653851e28c69d0506508fb27fc5

11）啟動PGPOOL

/opt/postgreSQL/pgpool4.3/bin/pgpool -f /opt/postgreSQL/pgpool4.3/etc/pgpool.conf -F /opt/postgreSQL/pgpool4.3/etc/pcp.conf > /tmp/pgpool.log

我們查看進(jìn)程： 和 oracle, pg 一樣的多進(jìn)程的連接模式

INFRA [root@wqdcsrv3352 pgpool4.3]# ps -ef| grep pgpool
root      80663      1  1 15:19 ?        00:00:00 /opt/postgreSQL/pgpool4.3/bin/pgpool -f /opt/postgreSQL/pgpool4.3/etc/pgpool.conf -F /opt/postgreSQL/pgpool4.3/etc/pcp.conf
root      80665  80663  0 15:19 ?        00:00:00 pgpool: wait for connection request
root      80666  80663  0 15:19 ?        00:00:00 pgpool: wait for connection request
root      80667  80663  0 15:19 ?        00:00:00 pgpool: wait for connection request
root      80668  80663  0 15:19 ?        00:00:00 pgpool: wait for connection request

查看PG pool 的節(jié)點狀態(tài)：

status: PG pool 中的節(jié)點狀態(tài)
pg_status： PG instance 數(shù)據(jù)庫本身的狀態(tài)
lb_weight： 權(quán)重
role: PG pool 中的角色
pg_role: PG instance 數(shù)據(jù)庫本身的角色

INFRA [postgres@wqdcsrv3352 backups]# psql -h 10.67.38.50 -p 9999 -Upostgres  postgres
postgres@10.67.38.50:9999=#37226 show pool_nodes;
node_id |   hostname   | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_
status_change  
---------+--------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+------
---------------
0       | 10.67.38.50  | 1992 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 |                   |                        | 2022-
06-24 14:59:44
1       | 10.67.39.149 | 1992 | up     | up        | 0.333333  | primary | primary | 3          | true              | 0                 |                   |                        | 2022-
06-24 14:59:44
2       | 10.67.39.49  | 1992 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 |                   |                        | 2022-
06-24 14:59:44
(3 rows)

我們用springboot 寫一個小的網(wǎng)頁，測試一下基于 PGPOOL的 HA 的 應(yīng)用透明故障轉(zhuǎn)移

POM 文件中，加入依賴的驅(qū)動jar 包

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

關(guān)于數(shù)據(jù)庫的配置： 我們配置 連接池的上限是 200， 池子中的最小空閑連接時10

//數(shù)據(jù)庫的地址以及端口號
spring.datasource.url=jdbc:postgresql://10.67.38.50:9999/postgres
spring.datasource.username=app_ha_user
spring.datasource.password=app_ha_user
spring.datasource.driverClassName=org.postgresql.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.datasource.maximum-pool-size=200
spring.datasource.min-idle=10

這里我們配置的應(yīng)用連接賬號是 app_ha_user/app_ha_user .

我們必須在 PGPOOL 端和PG 數(shù)據(jù)庫內(nèi) 都要添加這個賬戶，才可以通過PGPOOL 連接到真正的PG實例上面。

a) PG 數(shù)據(jù)庫創(chuàng)建賬戶 ： 這里我們需要注意，從PG 14 開始 默認(rèn)的加密方式從MD5 改成了 scram-sha-256。

postgres@10.67.38.50:9999=#37226 show password_encryption ;
password_encryption
---------------------
scram-sha-256
(1 row)

postgres@10.67.38.50:9999=#37226 create user app_ha_user with login password 'app_ha_user';

b)添加到 pgpool etc 目錄下的 (/opt/postgreSQL/pgpool4.3/etc) 的 pool_passwd
這里我們需要特別注意一下, PGPOOL 對于 scram-sha-256 的加密方式，目前支持2種模式：
1） text password: 明文文本
2）基于機密key 的模式。 可以使用自帶的機密工具 pg_enc

具體詳情可以參考文檔 ： https://www.pgpool.net/docs/42/en/html/auth-methods.html
這里我們采用簡單的明文文本模式，方便測試

postgres:postgres
app_ha_user:app_ha_user

c) 分別在PGPOOL和PG 數(shù)據(jù)端的PG_HBA.conf 添加賬戶并reload 生效

PG 數(shù)據(jù)庫端： 需要在3個節(jié)點上都運行

host    all             app_ha_user     0.0.0.0/0               scram-sha-256

PG pool 端：

host    all             app_ha_user     0.0.0.0/0               scram-sha-256

PG reload：

INFRA [postgres@wqdcsrv3353 data]# psql -h /tmp -p 1992
psql (14.3, server 15beta1)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
Type "help" for help.


postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

PG pool reload:

INFRA [root@wqdcsrv3352 ~]# /opt/postgreSQL/pgpool4.3/bin/pgpool reload

我們啟動網(wǎng)頁小程序，很簡單就是顯示出當(dāng)前連接的IP 地址

String sql = "select inet_server_addr()";
   // 通過jdbcTemplate查詢數(shù)據(jù)庫
   String hostname = (String)jdbcTemplate.queryForObject(
           sql, String.class);
return "Hello ，you are connecting to " + hostname;

http://127.0.0.1:8066/

我們查看PGPOOL 的連接池里面的連接：

我們查看PG 數(shù)據(jù)庫 主庫內(nèi)的連接信息：我們可以看到和我們的springboot中 最小空閑數(shù)10 是一致的

我們接下來，手動關(guān)閉 主庫測試一下 HA

INFRA [postgres@wqdcsrv3354 data]# /opt/postgreSQL/pg15/bin/pg_ctl -D /data/postgreSQL/1992/data stop -m fast
waiting for server to shut down.... done
server stopped

我們手動刷新一下 測試小程序的頁面：我們可以看到主節(jié)點已經(jīng) 指向了50 這個節(jié)點

我們來看一下 小程序端日志，在數(shù)據(jù)庫發(fā)生failover 的時候也會 出現(xiàn)連接異常信息：正好是我們連接池中的10個連接信息

2022-06-24 17:08:19,060 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@11268563 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-06-24 17:08:19,070 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@241d07b (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-06-24 17:08:19,071 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@27e950cd (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-06-24 17:08:19,073 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@a6e0135 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-06-24 17:08:19,074 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@2b8b2551 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-06-24 17:08:19,075 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@27c5fa53 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-06-24 17:08:19,075 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@4ac7a95e (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-06-24 17:08:19,075 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@30da6e9a (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-06-24 17:08:19,076 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@4289647d (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2022-06-24 17:08:19,077 WARN (PoolBase.java:184)- HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@4a96091d (This connection has been closed.). Possibly consider using a shorter maxLifetime value.

我們查看PG pool 的節(jié)點的狀態(tài)： down 點的節(jié)點是我們 手動關(guān)閉的老的主節(jié)點， 剩下的新的節(jié)點 一主一從的狀態(tài)是正常的

postgres@10.67.38.50:9999=#42271 show pool_nodes;
\ node_id |   hostname   | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_
status_change  
---------+--------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+------
---------------
0       | 10.67.38.50  | 1992 | up     | up        | 0.333333  | primary | primary | 1          | true              | 0                 |                   |                        | 2022-
06-24 17:06:12
1       | 10.67.39.149 | 1992 | down   | down      | 0.333333  | standby | unknown | 11         | false             | 0                 |                   |                        | 2022-
06-24 17:06:12
2       | 10.67.39.49  | 1992 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 |                   |                        | 2022-
06-24 17:07:46
(3 rows)

目前來說 PG 15 集成 PGPool 4.3 基于springboot 2.5 的HA 測試結(jié)果符合我們的預(yù)期。