背景
当所有请求都由主库进行处理时，可能造成主库压力过大，响应变慢，影响系统稳定性和扩展能力。如果能够将写请求发送到主库，并且将读请求发送到从库，就可以实现分摊主库压力，提高查询性能。这样的方式就称为读写分离。pgpool 工具可以作为客户端和 PostgreSQL 集群之间的代理层，智能分发 SQL 请求。
本文介绍如何配置通过 pgpool 实现读写分离。
前提条件
两个数据库节点，分别作为主节点和只读节点。
已经完成 PostgreSQL 的安装的云服务器，用于部署 pgpool 。云服务器、两个数据库节点的 PostgreSQL 版本需一致。
步骤1: 安装 pgpool
上传完成后，依次执行以下命令，完成 pgpool 的安装。 其中， pgpool 的版本号可根据需要进行修改。
[root@VM-10-6-tencentos ~]# tar -zxvf pgpool-II-4.4.5.tar.gz[root@VM-10-6-tencentos ~]# cd pgpool-II-4.4.5[root@VM-10-6-tencentos pgpool-II-4.4.5]# ./configure[root@VM-10-6-tencentos pgpool-II-4.4.5]# make[root@VM-10-6-tencentos pgpool-II-4.4.5]# make install
完成后，您可执行以下命令查询是否安装成功。若返回 pgpool 的版本信息，则安装成功。
[root@VM-10-6-tencentos pgpool-II-4.4.5]# pgpool --versionpgpool-II version 4.4.5 (nurikoboshi)
步骤2: 修改配置文件
说明：
使用 pgpool 实现负载均衡访问，所有认证发生在客户端和 pgpool 之间，同时客户端仍然需要继续通过 PostgreSQL 的认证过程。
1. 配置 pgpool.conf 文件
安装 pgpool-II 将自动生成文件 pgpool.conf.sample ，执行以下命令，将其拷贝并重命名为 pgpool.conf ，从而进行配置文件的修改。
[root@VM-0-15-tencentos pgpool-II-4.4.5]#cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf[root@VM-10-6-tencentos pgpool-II-4.4.5]# vi /usr/local/etc/pgpool.conf
按i键进入编辑模式，进行以下项的修改。
#listen_addresses = '0.0.0.0'#backend_hostname0 = '云数据库实例 IP 地址'#backend_port0 = 5432#enable_pool_hba = on
您可参考以下 pgpool.conf 文件的重要参数。
注意：
如下配置为重点参数示例配置，请您根据自身业务特点进行调整，并在上线前严格测试。
云数据库 PostgreSQL 主实例已经具备 HA 切换能力，不需要 pgpool 进行切换，因此对于 backend_flag0 参数，主节点需要配置为ALWAYS_PRIMARY，备节点需配置为 DISALLOW_TO_FAILOVER。
#------------------------------------------------------------------------------# CONNECTIONS#------------------------------------------------------------------------------# - pgpool Connection Settings -#listen_addresses = '0.0.0.0'# what host name(s) or IP address(es) to listen on;# comma-separated list of addresses;# defaults to 'localhost'; use '*' for all# (change requires restart)#port = 9989# Port number# (change requires restart)#unix_socket_directories = '/tmp'# Unix domain socket path(s)# The Debian package defaults to# /var/run/postgresql# (change requires restart)#unix_socket_group = ''# The Owner group of Unix domain socket(s)# (change requires restart)#reserved_connections = 0# Number of reserved connections.# Pgpool-II does not accept connections if over# num_init_chidlren - reserved_connections.# - pgpool Communication Manager Connection Settings -#pcp_listen_addresses = 'localhost'# what host name(s) or IP address(es) for pcp process to listen on;# comma-separated list of addresses;# defaults to 'localhost'; use '*' for all# (change requires restart)#pcp_port = 9898# Port number for pcp# (change requires restart)#pcp_socket_dir = '/tmp'# Unix domain socket path for pcp# The Debian package defaults to# /var/run/postgresql# (change requires restart)#listen_backlog_multiplier = 2# Set the backlog parameter of listen(2) to# num_init_children * listen_backlog_multiplier.# (change requires restart)#serialize_accept = off# whether to serialize accept() call to avoid thundering herd problem# (change requires restart)# - Backend Connection Settings -#backend_hostname0 = '主节点数据库 ip 地址'# Host name or IP address to connect to for backend 0#backend_port0 = 5432# Port number for backend 0#backend_weight0 = 1# Weight for backend 0 (only in load balancing mode)#backend_data_directory0 = '/data'# Data directory for backend 0#backend_flag0 = 'ALWAYS_PRIMARY'# Controls various backend behavior# ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER# or ALWAYS_PRIMARY#backend_application_name0 = 'server0'# walsender's application_name, used for "show pool_nodes" command#backend_hostname1 = '备节点数据库 ip 地址'#backend_port1 = 5433#backend_weight1 = 1#backend_data_directory1 = '/data1'#backend_flag1 = 'DISALLOW_TO_FAILOVER'#backend_application_name1 = 'server1'# - Authentication -#enable_pool_hba = on# Use pool_hba.conf for client authentication#pool_passwd = 'pool_passwd'# File name of pool_passwd for md5 authentication.# "" disables pool_passwd.# (change requires restart)#allow_clear_text_frontend_auth = off# Allow Pgpool-II to use clear text password authentication# with clients, when pool_passwd does not# contain the user password# - SSL Connections -#ssl = off# Enable SSL support# (change requires restart)#------------------------------------------------------------------------------# POOLS#------------------------------------------------------------------------------# - Concurrent session and pool size -#num_init_children = 32# Maximum Number of concurrent sessions allowed# (change requires restart)#max_pool = 4# Number of connection pool caches per connection# (change requires restart)# - Life time -#child_life_time = 5min# Pool exits after being idle for this many seconds#child_max_connections = 0# Pool exits after receiving that many connections# 0 means no exit#connection_life_time = 0# Connection to backend closes after being idle for this many seconds# 0 means no close#client_idle_limit = 0# Client is disconnected after being idle for that many seconds# (even inside an explicit transactions!)# 0 means no disconnection#------------------------------------------------------------------------------# FILE LOCATIONS#------------------------------------------------------------------------------#pid_file_name = '/var/run/pgpool/pgpool.pid'# PID file name# Can be specified as relative to the"# location of pgpool.conf file or# as an absolute path# (change requires restart)#logdir = '/tmp'# Directory of pgPool status file# (change requires restart)#------------------------------------------------------------------------------# CONNECTION POOLING#------------------------------------------------------------------------------#connection_cache = on# Activate connection pools# (change requires restart)# Semicolon separated list of queries# to be issued at the end of a session# The default is for 8.3 and later#reset_query_list = 'ABORT; DISCARD ALL'# The following one is for 8.2 and before#------------------------------------------------------------------------------# LOAD BALANCING MODE#------------------------------------------------------------------------------#load_balance_mode = on# Activate load balancing mode# (change requires restart)#ignore_leading_white_space = on# Ignore leading white spaces of each query#write_function_list = ''# Comma separated list of function names# that write to database# Regexp are accepted# If both read_only_function_list and write_function_list# is empty, function's volatile property is checked.# If it's volatile, the function is regarded as a# writing function.#disable_load_balance_on_write = 'transaction'# Load balance behavior when write query is issued# in an explicit transaction.## Valid values:## 'transaction' (default):# if a write query is issued, subsequent# read queries will not be load balanced# until the transaction ends.## 'trans_transaction':# if a write query is issued, subsequent# read queries in an explicit transaction# will not be load balanced until the session ends.### 'dml_adaptive':# Queries on the tables that have already been# modified within the current explicit transaction will# not be load balanced until the end of the transaction.## 'always':# if a write query is issued, read queries will# not be load balanced until the session ends.## Note that any query not in an explicit transaction# is not affected by the parameter except 'always'.#statement_level_load_balance = off# Enables statement level load balancing#------------------------------------------------------------------------------# HEALTH CHECK GLOBAL PARAMETERS#------------------------------------------------------------------------------#health_check_period = 0# Health check period# Disabled (0) by default#health_check_timeout = 20# Health check timeout# 0 means no timeout#health_check_user = 'nobody'# Health check user#health_check_password = ''# Password for health check user# Leaving it empty will make Pgpool-II to first look for the# Password in pool_passwd file before using the empty password#health_check_database = ''# Database name for health check. If '', tries 'postgres' frist,#health_check_max_retries = 0# Maximum number of times to retry a failed health check before giving up.#health_check_retry_delay = 1# Amount of time to wait (in seconds) between retries.#connect_timeout = 10000# Timeout value in milliseconds before giving up to connect to backend.# Default is 10000 ms (10 second). Flaky network user may want to increase# the value. 0 means no timeout.# Note that this value is not only used for health check,# but also for ordinary conection to backend.
2. 配置 pool_passwd 密码文件
pool_passwd 密码文件是通过 pgpool 连接数据库时需要使用的密码文件。请使用如下命令生成密码文件。其中 --username 参数使用连接云数据库的账号名称，示例为 dbadmin ，示例密码为 password ，请根据实际需要修改命令。返回的信息将自动写入 Pgpool-II 的 pool_passwd 文件中。
[root@VM-10-6-tencentos pgpool-II-4.4.5]# cd /usr/local/bin[root@VM-10-6-tencentos bin]# pg_md5 --md5auth --username=dbadmin password[root@VM-10-6-tencentos bin]# more /usr/local/etc/pool_passwddbadmin:md50b0cdb5c1d1f30fe83e5a*******
步骤3: 配置 PCP 命令（可选）
PCP 命令是 pgpool-II 用于管理功能的接口，是完成启停后端数据库节点、查看节点状态、重载配置等操作的必要工具。若您仅将 pgpool 用于负载均衡等操作，则可跳过本步骤。
要使用 PCP 命令，必须进行用户认证。这种认证需要在 pcp.conf 文件中另外定义一个用户和密码。首先，执行以下命令，将 pcp.conf.sample 文件拷贝并重命名为 pcp.conf ，从而进行配置文件的修改。
[root@VM-10-6-tencentos bin]# cd /usr/local/etc[root@VM-10-6-tencentos etc]# cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf
执行以下命令，获取 pcp 用户和密码。
[root@VM-10-6-tencentos etc]# pg_md5 --md5auth --username=pcpuser password2[root@VM-10-6-tencentos etc]# more /usr/local/etc/pool_passwddbadmin:md50b0cdb5c1d1f30fe83e5a*******pcpuser:md5907cf835939adc1c736e2*******
复制返回的 pcpuser:******** ，执行以下命令，编辑 pcp.conf 文件。
[root@VM-10-6-tencentos etc]# vi /usr/local/etc/pcp.conf
按 i 进入编辑模式，将复制的内容粘贴到文件末尾，再按 esc 键退出编辑模式，直接输入 :wq 保存修改并退出文件。
步骤4: 配置数据库节点
Pgpool-II 通过配置多个后端节点，才能实现负载均衡、故障转移、高可用等功能。如果要实现读写分离，则使用两个后端数据库节点，分别为主节点和只读节点，两个节点要求如下：
节点 | 作用 |
主节点 | 负责写操作和读操作（如果开启读写分离） |
备节点 | 只处理读操作 |
执行以下命令，编辑 pgpool.conf 配置文件。
[root@VM-10-6-tencentos etc]# vi /usr/local/etc/pgpool.confg
按 i 进入编辑模式，找到 Backend Connection Settings ，进行以下修改：
# - Backend Connection Settings -#backend_hostname0 = '主节点 ip 地址'# Host name or IP address to connect to for backend 0#backend_port0 = 5432# Port number for backend 0#backend_weight0 = 1# Weight for backend 0 (only in load balancing mode)#backend_data_directory0 = '/data'# Data directory for backend 0#backend_flag0 = 'ALWAYS_PRIMARY'# Controls various backend behavior# ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER# or ALWAYS_PRIMARY#backend_application_name0 = 'server0'# walsender's application_name, used for "show pool_nodes" command#backend_hostname1 = '备节点 ip 地址'#backend_port1 = 5433#backend_weight1 = 1#backend_data_directory1 = '/data1'#backend_flag1 = 'DISALLOW_TO_FAILOVER'#backend_application_name1 = 'server1'
再找到 LOAD BALANCING MODE ，当 load_balance_mode 被设置为 true，客户进行的 SELECT 查询将分发到所设置的多个数据库节点执行，实现读写分离和负载均衡。
再按 esc 键退出编辑模式，直接输入 :wq 保存修改并退出文件。
步骤5: 启动 pgpool-II 并验证读写分离
由于本文使用源码编译的方式安装 pgpool-II ，不会自动生成 systemd 服务文件，需要手动创建。执行以下命令，创建 pgpool 用户，再创建并进入 pgpool.service 服务文件：
[root@VM-10-6-tencentos etc]# sudo useradd -r -s /sbin/nologin pgpool[root@VM-10-6-tencentos etc]# vi /etc/systemd/system/pgpool.service
按 i 进入编辑模式，将以下内容粘贴进文件：
[Unit]Description=Pgpool-II connection pool serverAfter=network.target[Service]Type=simpleUser=pgpoolGroup=pgpoolExecStart=/usr/local/bin/pgpool -n -f /usr/local/etc/pgpool.conf #此处需填写pgpool.conf文件路径，可根据实际情况调整Restart=on-failure[Install]WantedBy=multi-user.target
再依次执行以下命令，加载 systemd 配置并启动服务：
[root@VM-10-6-tencentos etc]# sudo mkdir -p /var/run/pgpool[root@VM-10-6-tencentos etc]# sudo systemctl daemon-reload[root@VM-10-6-tencentos etc]# sudo systemctl enable pgpool[root@VM-10-6-tencentos etc]# sudo systemctl start pgpool
返回信息出现 process started 代表服务启动成功。
说明：
在云服务器连接主节点并执行语句" SELECT pg_is_in_recovery(); "，然后断开重连再查询 pg_is_in_recovery()，如果交替返回 false 和 true，说明是交替将请求发送给了主库和从库，即读写分离成功。
[root@VM-0-15-tencentos ~]# /usr/local/pgsql/bin/psql -h127.0.0.1 -p9989 -U dbadmin -d postgresPassword for user dbadmin:psql (15.1)Type "help" for help.postgres=> 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 | *.*.*.* | 5432 | up | unknown | 0.500000 | primary | unknown | 4 | false | 0 | | | 2024-02-27 20:04:131 | *.*.*.* | 5432 | up | unknown | 0.500000 | standby | unknown | 13 | true | 0 | | | 2024-02-27 20:04:13(2 rows)postgres=>
读写分离设置成功。
