基于pgpool实现读写分离

举报
大象数据库 发表于 2023/05/30 17:47:30 2023/05/30
【摘要】 部署环境:如果已经购买PostgreSQL 12 SSD盘及只读实例,只需在ECS安装配置pgpool即可

部署环境:如果已经购买PostgreSQL 12 SSD盘及只读实例,只需在ECS安装配置pgpool即可。
一、 安装pgpool
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum search all pgpool
yum install -y pgpool-II-12-extensions

二、 配置pgpool
步骤1 查询pgpool安装位置。命令如下:
rpm -qa|grep pgpool

步骤2 修改配置文件pgpool.conf。命令如下:
cd /etc/pgpool-II-12/
cp pgpool.conf.sample-stream pgpool.conf
vi pgpool.conf --pgpool.conf全文参考见附件,请参考配置

步骤3 创建业务用户。示例如下:
说明:在购买的Postgresql数据库读写库(主库)上执行以下sql创建用户
1.创建业务用户。示例如下:
create role digoal login encrypted password ‘xxxxxxx’;
create database digoal owner digoal;
2.创建pgpool数据库健康心跳用户,检查只读节点回放延迟(wal replay),只要能登录postgres数据库或指定的库即可,配合pgpool参数使用。示例如下:
create role nobody login encrypted password ‘xxxxxxx’;

步骤4 配置pool_passwd密码文件。命令如下:
说明 通过pgpool连接数据库时需要使用密码文件,可以理解为pgpool支持了PostgreSQL的认证协议。
cd /etc/pgpool-II-12
#用法
#pg_md5 --md5auth --username=username password
#生成digoal, nobody密码, 自动写入pool_passwd。
pg_md5 --md5auth --username=digoal “xxxxxxx”
pg_md5 --md5auth --username=nobody “xxxxxxx”

步骤5 配置pool_passwd密码文件。命令如下:
自动生成pool_passwd文件。命令如下:
cd /etc/pgpool-II-12
cat pool_passwd

步骤6 配置pgpool_hba文件。命令如下:
cd /etc/pgpool-II-12
cp pool_hba.conf.sample pool_hba.conf
vi pool_hba.conf
在pool_hba.conf文件中添加以下内容
host all all 0.0.0.0/0 md5

步骤7 配置pcp管理密码文件。命令如下:
说明 这里是用来管理pgpool的密码和用户,不是数据库的用户和密码。
cd /etc/pgpool-II-12
pg_md5 abc # 例如密码是abc。
900150983cd24fb0d6963f7d28e17f72
cp pcp.conf.sample pcp.conf
vi pcp.conf

USERID:MD5PASSWD
pcp.conf文件添加以下内容,密码abc为例
manage:900150983cd24fb0d6963f7d28e17f72 #表示使用manage用户来管理pcp。

步骤8 启动pgpool。命令如下:
cd /etc/pgpool-II-12
pgpool -f ./pgpool.conf -a ./pool_hba.conf -F ./pcp.conf
步骤9 通过pgpool连接数据库。命令如下:
psql -h 127.0.0.1 -p 8001 -U digoal postgres

常见问题:

如何测试读写分离是否成功?
连接并查询pg_is_in_recovery(),然后断开重连再查询pg_is_in_recovery(),如果交替返回false和true,说明是交替将请求发送给了主库和从库,即读写分离成功。

说明:通过pgpool连接数据库,查询pg_is_in_recovery()时,若一直返回t时,则意味这pgpool只连接到了从库,同理,若一直返回f时,则意味这pgpool只连接到了主库,可以尝试pcp_attach_node重新注册主库或从库。
pcp_attach_node工具是pgpool自带的集群管理工具,pcp_attach_node工具可以将集群中的节点重新注册到集群中,对于通过show_pools显示的状态不正常的node,可以尝试通过此工具进行处理。

通过pcp_attach_node工具注册备库
步骤1 查看pcp工具的连接用户
cat pcp.conf

步骤2 使用pcp_attach_node重新注册
用法:pcp_attach_node -h 本地ip -U pcp_user node_id
示例:pcp_attach_node –h 127.0.0.1 –U manage 0

2.备库如何停止、重新加载pgpool配置?
您可以使用pgpool --help查看帮助命令,例如:
cd /etc/pgpool-II-12
停止命令:
pgpool -f ./pgpool.conf -m fast stop
3. 如果有多个只读实例, 应该如何配置?
修改pgpool.conf文件,补充多个只读实例的配置,示例如下:
backend_hostname1 = ‘xx.xx.xxx.xx’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/var/chroot/var/lib/pgsql/data’
backend_flag1 = ‘DISALLOW_TO_FAILOVER’
backend_application_name1 = ‘server1’

backend_hostname2 = ‘xx.xx.xx.xx’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/var/chroot/var/lib/pgsql/data’
backend_flag1 = ‘DISALLOW_TO_FAILOVER’
backend_application_name1 = ‘server1’
4. 如何通过pcp查询后端状态?
示例命令如下:

pcp_node_info -U manage -h /tmp -p 9898 -n 1 -v
Password: 输入密码

  1. 监听的端口有哪些?
    监听的端口如下:
    主库:5432
    备库:5432
    pgpool:8001
    pcp:9898
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。