基于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: 输入密码
- 监听的端口有哪些?
监听的端口如下:
主库:5432
备库:5432
pgpool:8001
pcp:9898
- 点赞
- 收藏
- 关注作者
评论(0)