PG 15 集成 PGPool 4.3 基于springboot 2.5 的HA 测试

大家好， 这次大表哥带来的分享是 PG 15 集成 PGPool 4.3 基于springboot 2.5 的HA 测试。

**重要的声明！！！：
PG 15 beta 版本是 上个月中旬新发布的版本， PG Pool 4.3 也是目前（写文章的时间是2022-06-24）最新的版本。 本文是存技术方案测试案例篇。生产环境强烈不建议2者都使用最新的版本来部署。本文很有可能是全网第一篇都使用最新版本来测试的。 **

接下来我们开始愉快的安装测试吧：

PG 15的安装： 可以参考我之前的文章 https://www.modb.pro/db/411245

我们这次专注于PG Pool 的安装：(大表哥一如既往的保姆式 step by step 的style)

PG pool 的官网的下载地址:

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

同样PG pool 提供了rpm , yum 和 源码 的三种安装方式。

我们这里选择下载源码的版本是 pgpool-II 4.3 (Stable) 发布日期是 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]

解压源代码并进行重命名：

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语言开发的软件都是一样的

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

编译的参数简单说明：

–prefix=path 安装编译之后的路径 默认是

我们创建编译路径进行编译：

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, 我们进入到PG pool的源码路径下：

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

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

我们来创建我们的 extension : 注意我们需要安装在数据库 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 的版本，我们还需要在配置文件中设置参数 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 简单安装已经完毕，

下面我们要配置一下 1 节点的PG POOL + 3 节点的数据源 (1 主 + 2 从) 的架构

我们在启动pgpool 之前，还需要做如下的配置：

1）关闭机器之间的防火墙

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

2）打通机器之间的SSH 免密登录
默认的情况下， root 账户启动PGPOOL 我们要打通 root 账号 到 postgres 账号之间的免密

需要在 root 下执行：

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个节点（包含3节点），在发生主从切换的时候 ，其他的从节点需要重定向复制源。这个时候就需要这个脚本

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)创建数据库的健康监测账户

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' --流复制的模式
listen_addresses = '*'  -- 允许远程连接
port = 9999  -- pgpool 监听的端口
pid_file_name = '/opt/postgreSQL/pgpool4.3/pgpool.pid'
# Backend Connection Settings -- 后台PG数据库的配置
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'

### 复制检查延时设置

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

### 主从HA 健康监测账号

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

### 主从切换调用的 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）创建密码文件： 为了 failover 的时候 执行 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 创建密码文件
注意这个在 root 账户下, 以为我们是用root 启动的 PGPOOL, 这个为了 attach 节点用的

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

8）配置PGPOOL 账号信息 编辑文件 pool_passwd， 默认是在 PGPOOL_HOME/etc/下的
这里要注意了， 由于从PG14开始 默认的数据库加密变成 scram-sha-256 的方式。
所以我们不需要用MD5的方式 进行密码二次加密了， 对于 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

我们查看进程： 和 oracle, pg 一样的多进程的连接模式

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 的节点状态：

status: PG pool 中的节点状态
pg_status： PG instance 数据库本身的状态
lb_weight： 权重
role: PG pool 中的角色
pg_role: PG instance 数据库本身的角色

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 写一个小的网页，测试一下基于 PGPOOL的 HA 的 应用透明故障转移

POM 文件中，加入依赖的驱动jar 包

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

关于数据库的配置： 我们配置 连接池的上限是 200， 池子中的最小空闲连接时10

//数据库的地址以及端口号
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

这里我们配置的应用连接账号是 app_ha_user/app_ha_user .

我们必须在 PGPOOL 端和PG 数据库内 都要添加这个账户，才可以通过PGPOOL 连接到真正的PG实例上面。

a) PG 数据库创建账户 ： 这里我们需要注意，从PG 14 开始 默认的加密方式从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 数据端的PG_HBA.conf 添加账户并reload 生效

PG 数据库端： 需要在3个节点上都运行

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

我们启动网页小程序，很简单就是显示出当前连接的IP 地址

String sql = "select inet_server_addr()";
   // 通过jdbcTemplate查询数据库
   String hostname = (String)jdbcTemplate.queryForObject(
           sql, String.class);
return "Hello ，you are connecting to " + hostname;

http://127.0.0.1:8066/

我们查看PGPOOL 的连接池里面的连接：

我们查看PG 数据库 主库内的连接信息：我们可以看到和我们的springboot中 最小空闲数10 是一致的

我们接下来，手动关闭 主库测试一下 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

我们手动刷新一下 测试小程序的页面：我们可以看到主节点已经 指向了50 这个节点

我们来看一下 小程序端日志，在数据库发生failover 的时候也会 出现连接异常信息：正好是我们连接池中的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 的节点的状态： down 点的节点是我们 手动关闭的老的主节点， 剩下的新的节点 一主一从的状态是正常的

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 测试结果符合我们的预期。