环境

部署拓扑

Pgpool-II 版本和配置

示例脚本

简介

本文档仅演示只使用pgpool做读写分离的场景，尽可能以最小的配置运行读写分离，并验证是否生效。

pgpool 4.0 以下版本默认运行在 root用户中，以上版本默认运行在 postgres 用户。这里是用 postgres 运行 pgpool 服务。

搭建流复制集群

略

在使用前，请验证你的流复制是否正常

主库操作

[postgres@pg01 ~]$ psql -c "select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;"

pid | state | client_addr | sync_priority | sync_state

-------+-----------+-------------+---------------+------------

25897 | streaming | 10.10.9.214 | 0 | async

25909 | streaming | 10.10.9.215 | 0 | async

从库操作

[root@pg02 pg_backup]# psql -c "\x" -c "select * from pg_stat_wal_receiver;"

Expanded display is on.

-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

pid | 10815

status | streaming

receive_start_lsn | 0/3000000

receive_start_tli | 1

written_lsn | 0/5465F20

flushed_lsn | 0/5465F20

received_tli | 1

last_msg_send_time | 2024-09-04 21:54:01.304081+08

last_msg_receipt_time | 2024-09-04 21:54:01.288948+08

latest_end_lsn | 0/5465F20

latest_end_time | 2024-09-04 21:50:31.239286+08

slot_name |

sender_host | 10.10.9.213

sender_port | 5432

conninfo | user=repl password=******** channel_binding=disable dbname=replication host=10.10.9.213 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable

主库创建库

psql -c 'create database test;'

从库查看库是否同步成功

psql -c "\l"

在 root 用户设置全局变量 根据自己的配置修改 // rpm 安装的话不用配置

cat >/etc/profile.d/pgsql.sh<<'EOF'

export PGDATA=/pgdata/16/data

export LANG=en_US.utf8

export PGHOME=/usr/local/pg16

export LD_LIBRARY_PATH=$PGHOME:$LD_LIBRARY_PATH

export PGUSER=postgres

export PATH=/usr/local/pg16/bin:$PATH

EOF

source /etc/profile.d/pgsql.sh

安装 pgpool

# 安装依赖

yum install -y openssl-devel

# 下载包

wget https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.5.3.tar.gz

# 解压

tar xf download.php?f=pgpool-II-4.5.3.tar.gz

mkdir /usr/local/pgpool

chown -R postgres:postgres /usr/local/pgpool

# 配置

cd pgpool-II-4.5.3

./configure --prefix=/usr/local/pgpool --with-pgsql=/usr/local/pg16 --with-openssl

# --prefix # 指定安装路径

# --with-pgsql # 指定postgresql的安装路径

# --with-openssl # 支持 openssl 必选

# 编译 && 安装

make && make install

# 授权

chown -R postgres:postgres /usr/local/pgpool

# 配置环境变量

cat >/etc/profile.d/pgpool.sh<<'EOF'

export PATH=/usr/local/pgpool/bin:$PATH

EOF

source /etc/profile.d/pgpool.sh

创建数据库用户

主库操作

-- 登录数据库

psql

-- 设置 SHA-256 加密密码

SET password_encryption = 'scram-sha-256';

-- 创建 pgpool 管理账号

CREATE ROLE pgpool WITH LOGIN;

alter user pgpool with password 'pgpool';

alter user postgres with password 'postgres';

-- 授权监控权限

GRANT pg_monitor TO pgpool;

-- 查看账号密码

select usename,passwd from pg_shadow;\q

添加数据库认证

根据自己 pgsql 的路径修改文件内容，允许 md5/sha-256 加密认证通过

// 所有节点

cat >/pgdata/16/data/pg_hba.conf<<'EOF'

# TYPE DATABASE USER ADDRESS METHOD 可设置/reject/md5/trust/scram-sha-256

# 允许具有复制权限的用户从本地主机进行复制连接

local all all trust

host replication all 127.0.0.1/32 trust

host replication all 0.0.0.0/0 md5

host all all 0.0.0.0/0 md5

host replication all 0.0.0.0/0 scram-sha-256

host all all 0.0.0.0/0 scram-sha-256

EOF

# 重载配置文件

psql -c 'SELECT pg_reload_conf();'

免密连接pgsql

方便查看信息、无须提供密码进行流复制和故障转移

以下为每个节点的用户和密码信息 // 在用户家目录创建

cat >/home/postgres/.pgpass<<'EOF'

# 地址:端口:库:用户:密码

10.10.9.213:5432:replication:repl:repl

10.10.9.214:5432:replication:repl:repl

10.10.9.215:5432:replication:repl:repl

10.10.9.213:5432:postgres:postgres:postgres

10.10.9.214:5432:postgres:postgres:postgres

10.10.9.215:5432:postgres:postgres:postgres

10.10.9.213:5432:postgres:pgpool:pgpool

10.10.9.214:5432:postgres:pgpool:pgpool

10.10.9.215:5432:postgres:pgpool:pgpool

# 方便连接 pgpool

10.10.9.213:9999:postgres:pgpool:pgpool

10.10.9.213:9999:postgres:postgres:postgres

10.10.9.214:9999:postgres:pgpool:pgpool

10.10.9.214:9999:postgres:postgres:postgres

10.10.9.215:9999:postgres:pgpool:pgpool

10.10.9.215:9999:postgres:postgres:postgres

EOF

chmod 600 /home/postgres/.pgpass

PCP 管理工具配置账号密码

pcp工具的密码需要用 pg_md5 加密，和数据库 pgpool 用户的用户名和密码保持一致即可。

# 这里直接用 pg_md5 加密后写入pcp.conf文件中

echo 'pgpool:'`pg_md5 pgpool` > /usr/local/pgpool/etc/pcp.conf

免密连接 PCP 管理工具

在用户家目录创建

cat >/home/postgres/.pcppass<<'EOF'

# 地址:端口:用户:密码

localhost:9898:pgpool:pgpool

10.10.8.213:9898:pgpool:pgpool

10.10.8.214:9898:pgpool:pgpool

10.10.8.215:9898:pgpool:pgpool

EOF

chmod 600 /home/postgres/.pcppass

编写 pgpool 主配置文件

cat >/usr/local/pgpool/etc/pgpool.conf<<'EOF'

# 流复制 (推荐配置)

backend_clustering_mode = 'streaming_replication'

listen_addresses = '*'

port = 9999

pid_file_name = '/usr/local/pgpool/pgpool.pid'

unix_socket_directories = '/usr/local/pgpool'

# pcp 监听

pcp_listen_addresses = '*'

pcp_port = 9898

pcp_socket_dir = '/usr/local/pgpool'

# 后端数据库节点信息

###############################################

backend_hostname0 = '10.10.9.213'

backend_port0 = 5432

backend_weight0 = 1

backend_data_directory0 = '/pgdata/16/data'

backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_application_name0 = 'pg01'

###############################################

backend_hostname1 = '10.10.9.214'

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '/pgdata/16/data'

backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_application_name1 = 'pg02'

###############################################

backend_hostname2 = '10.10.9.215'

backend_port2 = 5432

backend_weight2 = 1

backend_data_directory2 = '/pgdata/16/data'

backend_flag2 = 'ALLOW_TO_FAILOVER'

backend_application_name2 = 'pg03'

###############################################

# 启用身份验证

enable_pool_hba = on

# 日志相关

log_destination = 'stderr'

logging_collector = on

log_directory = '/usr/local/pgpool/log'

log_filename = 'pgpool-%Y-%m-%d.log'

log_truncate_on_rotation = on

log_rotation_age = 1d

# 流复制检查

sr_check_user = 'pgpool' # 数据库用户

sr_check_password = ''

# 开启负载均衡 (读写分离)

#load_balance_mode = on

#ignore_leading_white_space = on

# 健康检查

health_check_period = 5

health_check_timeout = 30

health_check_user = 'pgpool' # 数据库用户

health_check_password = ''

health_check_max_retries = 3

# 连接池优化

num_init_children = 500

max_pool = 10

child_life_time = 300

connection_life_time = 0

client_idle_limit = 0

child_max_connections = 0

EOF

编写认证文件配置

cat >/usr/local/pgpool/etc/pool_hba.conf<<'EOF'

local all all trust

host all all 0.0.0.0/0 scram-sha-256

host replication all 0.0.0.0/0 scram-sha-256

EOF

配置身份验证密码文件

加密字符

# 加盐配置 // 这里随意写一串字符即可

echo '123' > /home/postgres/.pgpoolkey

chmod 600 /home/postgres/.pgpoolkey

chown -R postgres:postgres /home/postgres/.pgpoolkey

在 postgres 用户家目录创建该文件

# 将密码注册到 pool_passwd 文件

pg_enc -m -k /home/postgres/.pgpoolkey -u pgpool -p # 这里的用户密码就是数据库的用户密码

# trying to read key from file /home/postgres/.pgpoolkey # 显示这个就成功了

pg_enc -m -k /home/postgres/.pgpoolkey -u postgres -p

# 查看注册的密码

cat /usr/local/pgpool/etc/pool_passwd

#pgpool:AESUxKB1QeoYbvljmoNribL0A==

#postgres:AESbMkn10eldoyK8HX5S6tsJQ==

授权目录

因为以上配置都是在 root 进行，还需要授权一遍，防止报错

chown -R postgres:postgres /usr/local/pgpool/ /home/postgres/

启动

sudo -u postgres /usr/local/pgpool/bin/pgpool -f /usr/local/pgpool/etc/pgpool.conf -F /usr/local/pgpool/etc/pcp.conf

# 查看端口

netstat -lntp

# 查看日志

tail -f /usr/local/pgpool/log/pgpool-*.log

# 停止

#pgpool -f /usr/local/pgpool/etc/pgpool.conf -F /usr/local/pgpool/etc/pcp.conf -m fast stop

# 重载

#pgpool -f /usr/local/pgpool/etc/pgpool.conf -F /usr/local/pgpool/etc/pcp.conf reload

基础管理

su - postgres

# 查看节点信息

psql -h 10.10.9.213 -p 9999 -U pgpool -d postgres -c "show pool_nodes"

# pcp 工具查看节点信息

pcp_node_info -h 10.10.9.213 -p 9898 -U pgpool -W

# 查看所有参数信息

pcp_pool_status -h 10.10.9.213 -p 9898 -U pgpool -W

测试读写分离

测试写

使用 pgpool 的端口写入数据， 并检查每个节点是否一致

# 建个表

psql -h 10.10.9.213 -p 9999 -U postgres -d postgres -c "create table t1(id int);"

# 写点数

psql -h 10.10.9.213 -p 9999 -U postgres -d postgres -c "insert into t1 values(1);"

psql -h 10.10.9.213 -p 9999 -U postgres -d postgres -c "insert into t1 values(2);"

psql -h 10.10.9.213 -p 9999 -U postgres -d postgres -c "insert into t1 values(3);"

psql -h 10.10.9.213 -p 9999 -U postgres -d postgres -c "insert into t1 values(4);"

psql -h 10.10.9.213 -p 9999 -U postgres -d postgres -c "insert into t1 values(5);"

在每个数据库节点执行以下命令 查看数据是否相同

[postgres@pg01 ~]$ psql -c 'select * from t1;'

id

----

1

2

3

4

5

(5 rows)

相同则成功。

测试读

通过查看 node 信息，关注 select_cnt 列的变化，确定是否使用了读写分离。

# 先查看节点信息 重点关注 select_cnt 列

psql -h 10.10.9.213 -p 9999 -U pgpool -d postgres -c "show pool_nodes"

通过多次执行查询获取 select_cnt 列变化

# 通过执行 select 1 模拟执行 // 多执行几次

psql -h 10.10.9.213 -p 9999 -U pgpool -d postgres -c "select 1"

psql -h 10.10.9.213 -p 9999 -U pgpool -d postgres -c "select 1"

psql -h 10.10.9.213 -p 9999 -U pgpool -d postgres -c "select 1"

psql -h 10.10.9.213 -p 9999 -U pgpool -d postgres -c "select 1"

psql -h 10.10.9.213 -p 9999 -U pgpool -d postgres -c "select 1"

再次查看 select_cnt 列，流复制节点有变化则读写分离成功

psql -h 10.10.9.213 -p 9999 -U pgpool -d postgres -c "show pool_nodes"

# select_cnt 信息显示的是读写分离

END