postgresql|数据库|centos7下基于postgresql-12的主从复制的pgpool-4.4的部署和使用

前言：

postgresql数据库只用自身的一些配置是无法做到最优的优化的，需要通过一些外置插件（中间件）来提高服务器的整体性能，通俗的说就是数据库仅仅依靠自身是无法达到性能最优的，很多时候需要更改数据库的整体架构，使用一些目前来说成熟的技术，比如，读写分离技术，负载均衡技术，高速缓存技术等等集群方面的技术。

下图是一些较为成熟的集群方案：

从上表可以看到，pgpool是比较全面的一个中间件，什么连接池，负载均衡都有，还有没有写出来的缓存功能，其实使用此中间件的不能拒绝的诱惑就是负载均衡和缓存了，其它的功能倒是没有什么。

本文将就pgpool的负载均衡和高速缓存功能做一个详细的介绍。

一，

pgpool的简单介绍

Pgpool-II是一个在PostgreSQL服务器和PostgreSQL数据库客户端之间工作的中间件。它是根据BSD许可证授权的。它提供以下功能。

连接池

Pgpool-II保存与PostgreSQL服务器的连接，并在具有相同属性（即用户名，数据库，协议版本）的新连接进入时重用它们。它减少了连接开销，并提高了系统的整体吞吐量。

复制

Pgpool-II可以管理多个PostgreSQL服务器。使用复制功能可以在2个或更多物理磁盘上创建实时备份，以便在磁盘发生故障时服务可以继续运行而不会停止服务器。

负载均衡

如果复制了数据库，则在任何服务器上执行SELECT查询都将返回相同的结果。Pgpool-II利用复制功能，通过在多个服务器之间分配SELECT查询来减少每个PostgreSQL服务器的负载，从而提高系统的整体吞吐量。充其量，性能与PostgreSQL服务器的数量成比例地提高。在许多用户同时执行许多查询的情况下，负载平衡最有效。

限制超出连接

PostgreSQL的最大并发连接数有限制，连接在这么多连接后被拒绝。但是，设置最大连接数会增加资源消耗并影响系统性能。pgpool-II对最大连接数也有限制，但额外连接将排队，而不是立即返回错误。

看门狗

Watchdog可以协调多个Pgpool-II，创建一个强大的集群系统，避免单点故障或脑裂。看门狗可以对其他pgpool-II节点执行生命检查，以检测Pgpoll-II的故障。如果活动Pgpool-II发生故障，则可以将备用Pgpool-II提升为活动状态，并接管虚拟IP。

查询缓存

在内存中查询缓存允许保存一对SELECT语句及其结果。如果有相同的SELECT，Pgpool-II将从缓存中返回值。由于不涉及SQL解析或访问PostgreSQL，因此在内存缓存中使用速度非常快。另一方面，在某些情况下，它可能比正常路径慢，因为它增加了存储缓存数据的一些开销。

Pgpool-II讲PostgreSQL的后端和前端协议，并在后端和前端之间传递消息。因此，数据库应用程序（前端）认为Pgpool-II是实际的PostgreSQL服务器，服务器（后端）将Pgpool-II视为其客户端之一。因为Pgpool-II对服务器和客户端都是透明的，所以现有的数据库应用程序可以与Pgpool-II一起使用。Pgpool-II讲述PostgreSQL的后端和前端协议，并在它们之间传递连接。因此，数据库应用程序（前端）认为Pgpool-II是实际的PostgreSQL服务器，服务器（后端）将Pgpool-II视为其客户端之一。因为Pgpool-II对服务器和客户端都是透明的，所以现有的数据库应用程序可以与Pgpool-II一起使用，不需要对现有的业务系统进行更改。

---

---

---

那么，架构方面应该是通过看门狗，利用一个虚拟IP（也就是VIP）代理一个自身就是主从流复制的集群，VIP可以看做是前端，数据库可以看做后端，而主从流复制的数据库集群是具有这么一个特点：主服务器可读可写，从服务只读不可写。VIP通过pgpool的负载均衡功能就可以即可读也可以写了，负载均衡的策略是主从根据特定算法分配读任务，写任务仍然是交给主服务器完成。

这样，集群的使用率就自然的增高了，一些常用的查询语句通过pgpool的缓存功能，缓存起来，自然的整个集群的查询效率就提高了。

OK，下面开始讲述如何搭建pgpool。

二，

pgpool的官方网址：pgpool Wiki

下载和安装教程都有，里面也提供了一些比较新的rpm安装包，rpm仓库地址是：Index of /yum/rpms/4.4/redhat/rhel-7-x86_64

直接将该地址配置成yum仓库就可以了

postgresql的版本和大体情况如下：

11服务器是主服务器，12是从服务器

主从复制的搭建见我的博客：postgresql|数据库|【postgresql-12的基于pg_basebackup的主从复制部署】_postgresql12 主备_晚风_END的博客-CSDN博客

三，

pgpool的部署

该部署工作是比较繁琐的，难度是比较高的，主要是权限问题需要小心处理，其次是参数众多，很多地方需要根据实际的情况调整，最后是pgpool的功能比较多，如何配置好是需要比较多的耐心。

首先，大概介绍一下pgpool的组件，该中间件的管理组件有三个，一个是数据库侧使用的pool工具，该工具是以插件的形式安装在postgresql数据库内的，第二个是操作系统侧的pcp工具，这个工具需要在pgpool的主配置文件内配置，第三个是pgpoolAdm，此工具是PHP编写的web端管理工具，可以在web端方便的查看pgpool并且管理配置pgpool，目前的版本应该是需要高版本的PHP支持，暂时没有使用。

1，

管理工具的安装

本案例中，只安装数据库侧的管理工具pool和pcp，pool工具在源码包内。

pgpool-II-4.4.4.tar.gz这个文件上传到服务器解压后，和普通的postgresql插件没什么区别，一样的make && make install 就可以了，前提是环境变量里有定义PGHOME和PGDATA这两个变量。

2，

yum安装

配置好本地仓库和上面提到的官网仓库就可以运行以下命令安装了，这里安装了一个memcached服务，该服务后面作为缓存服务使用的。

yum install pgpool-II-pg12-debuginfo-4.4.2 pgpool-II-pg12-4.4.2 pgpool-II-pg12-devel-4.4.2 pgpool-II-pg12-extensions-4.4.2 -y

yum install memcached -y &&systemctl enable memcached && systemctl enable pgpool && systemctl start pgpool memcached

安装完毕后将会在/etc目录下看到pgpool-II，此目录里是pgpool的配置文件和一些高可用故障转移脚本，本案例中这些脚本不打算使用，只配置pgpool服务，另外需要注意，两个服务器都需要安装，memcached只在一个服务器安装就可以了

3，

配置文件

可以看到，这些文件都是postgres的属组，这些一定要注意哦

[root@node1 pgpool-II]# ls -al
total 144
drwxr-xr-x.  3 root     root       202 Sep 18 06:18 .
drwxr-xr-x. 83 root     root      8192 Sep 17 19:16 ..
-rw-------   1 postgres postgres   900 Sep 17 11:15 pcp.conf
-rw-------.  1 postgres postgres   858 Jan 22  2023 pcp.conf.sample
-rw-------   1 postgres postgres 52960 Sep 18 02:01 pgpool.conf
-rw-------.  1 postgres postgres 52964 Jan 22  2023 pgpool.conf.sample
-rw-------   1 postgres postgres     2 Sep 17 10:21 pgpool_node_id
-rw-------   1 postgres postgres  3537 Sep 17 11:54 pool_hba.conf
-rw-------.  1 postgres postgres  3476 Jan 22  2023 pool_hba.conf.sample
-rw-------.  1 postgres postgres    45 Sep 17 11:05 pool_passwd
drwxr-xr-x.  2 root     root      4096 Sep 17 10:02 sample_scripts

pcp.conf 的配置

该文件是存放pgpool的管理密码，此密码可以和postgresql数据库的密码不一样，也就是说随便定，定义方式非常简单，用户名:密码的形式添加在该文件末尾即可，只是需要注意一点，密码是md5加密的，不能明文(两种方式都可以，嫌麻烦的话就第三行那个命令，用户是postgres，密码是123456)

[root@node1 pgpool-II]# pg_md5 123456
e10adc3949ba59abbe56e057f20f883e
[root@node1 pgpool-II]# echo "postgres:e10adc3949ba59abbe56e057f20f883e">>./pcp.conf
[root@node1 pgpool-II]# echo "postgres:`pg_md5 123456`">>./pcp.conf

[root@node1 pgpool-II]# cat pcp.conf
# PCP Client Authentication Configuration File
# ============================================
#
# This file contains user ID and his password for pgpool
# communication manager authentication.
#
# Note that users defined here do not need to be PostgreSQL
# users. These users are authorized ONLY for pgpool 
# communication manager.
#
# File Format
# ===========
#
# List one UserID and password on a single line. They must
# be concatenated together using ':' (colon) between them.
# No spaces or tabs are allowed anywhere in the line.
#
# Example:
# postgres:e8a48653851e28c69d0506508fb27fc5
#
# Be aware that there will be no spaces or tabs at the
# beginning of the line! although the above example looks
# like so.
#
# Lines beginning with '#' (pound) are comments and will
# be ignored. Again, no spaces or tabs allowed before '#'.# USERID:MD5PASSWD
postgres:e10adc3949ba59abbe56e057f20f883e

pgpool.conf文件的配置：

该文件是pgpool的主配置文件，其中注释的行已经全部去掉了，只保留了放开的内容

说明：该配置文件内定义的文件路径需要手动建立，/var/run/postgresql  属组是postgres

sr_check_user = 'nobody'  这个nobody用户需要在主数据库创建，创建命令为create role nobody login replication encrypted password 'replica';

为什么是主数据库呢？因为是流复制，主数据库创建了 ，从数据库自然就有了嘛，上面提到的插件也是如此的哦。

[root@node1 pgpool-II]# sed -e '/^$/d' pgpool.conf |grep -v "\#"
backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 15433
unix_socket_directories = '/var/run/postgresql'
pcp_listen_addresses = '*'
pcp_port = 19999
pcp_socket_dir = '/var/run/postgresql'
backend_hostname0 = '192.168.123.11'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'node1'
backend_hostname1 = '192.168.123.12'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'node2'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
process_management_mode = dynamic
num_init_children = 32
min_spare_children = 5
max_spare_children = 10
max_pool = 4
child_life_time = 5min
log_destination = 'stderr'
log_connections = on
log_disconnections = on
log_hostname = on
log_statement = on
log_per_node_statement = on
log_client_messages = on
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%a.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0 
pid_file_name = '/var/run/postgresql/pgpool.pid'
logdir = '/tmp'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
load_balance_mode = on
database_redirect_preference_list = 'postgres:1'
sr_check_period = 10
sr_check_user = 'nobody'
sr_check_password = 'replica'
sr_check_database = 'postgres'
delay_threshold = 1
delay_threshold_by_time = 1
prefer_lower_delay_standby = on
use_watchdog = on
hostname0 = '192.168.123.11'
wd_port0 = 9000
pgpool_port0 = 15433
hostname1 = '192.168.123.12'
wd_port1 = 9000
pgpool_port1 = 15433
wd_ipc_socket_dir = '/var/run/postgresql'
delegate_ip = '192.168.123.222'
if_cmd_path = '/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev ens33 label ens33:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev ens33'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1 -I ens33'
wd_monitoring_interfaces_list = ''
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
heartbeat_hostname0 = '192.168.123.11'
heartbeat_port0 = 19694
heartbeat_device0 = 'ens33'
heartbeat_hostname1 = '192.168.123.12'
heartbeat_port1 = 19694
heartbeat_device1 = 'ens33'
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
memory_cache_enabled = off
memqcache_method = 'memcached'
memqcache_memcached_host = '192.168.123.11'
memqcache_memcached_port = 11211
memqcache_total_size = 64MB
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_cache_block_size = 1MB

pool_passwd文件的配置：

重要：
su - postgres
pg_md5 -m -p -u postgres pool_passwd 
#此时会提示输入密码，此密码是postgresql服务器的postgres用户的密码，一会会用此命令登录postgresql数据库的哦

[root@node1 pgpool-II]# su - postgres
Last login: Mon Sep 18 06:34:54 CST 2023 on pts/1
[postgres@node1 ~]$ pg_md5 -m -p -u postgres pool_passwd
password: 
[postgres@node1 ~]$ logout
[root@node1 pgpool-II]# cat pool_passwd 
postgres:md5a3556571e93b0d20722ba62be61e8c2d

pool_hab.conf文件的配置

该文件的作用是定义pgpool哪些用户可以访问哪些后端的postgresql数据库，功能和postgresql数据库的pg_hba.conf文件类似

如果不想太麻烦（也就是不太安全），那么，如下配置即可：

# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         0.0.0.0/0               md5

pgpool_node_id文件的配置

此文件是标识文件，表明该pgpool 对应于哪个后端，因此，11服务器上此文件内容就一个0,12服务器上次文件内容就一个1即可，当然了，如果有其它的节点，就依次增加数字即可，最多好像是127个节点。

[root@node1 pgpool-II]# cat pgpool_node_id 
0

未完待续！！！