postgresql读写分离pgpool

2019-08-14 20:32 数据库 postgresql

"postgresql读写分离pgpool"

前提条件

假设已经装好了主从库，如果没有请参考文章 postgresql主从配置。

安装pgpool


xxxxxxxxxx
ubuntu@master:~$ sudo apt-get install pgpool2

编辑pgpool.conf


xxxxxxxxxx
ubuntu@master:~$ sudo vi /etc/pgpool2/pgpool.conf
listen_addresses = '*'
port = 5433
pcp_listen_addresses = '*'
pcp_port = 9898
# - Backend Connection Settings -
backend_hostname0 = '<master ip>'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data/pgsql/backend0/data'
backend_hostname1 = '<slave ip>'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/pgsql/backend1/data'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
log_destination = 'syslog'
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
# - Streaming -
sr_check_period = 0
sr_check_user = 'srcheck'
sr_check_password = 'SRCHECK'

配置pgpool管理秘钥


xxxxxxxxxx
ubuntu@master:~$ pg_md5 -u sup_pool sup_pool_pwd
c21777db255631573e4233403773bb3b
ubuntu@master:~$ sudo vi /etc/pgpool2/pcp.conf
sup_pool:c21777db255631573e4233403773bb3b

配置pool_hba.conf


xxxxxxxxxx
ubuntu@master:~$ sudo vim /etc/pgpool2/pool_hba.conf 
local   all         all                               trust  
host    all         all         127.0.0.1/32          trust  
host    all         all         0.0.0.0/0             md5

配置pgpool-ii密码文件


xxxxxxxxxx
postgres=# select rolname,rolpassword from pg_authid;
 rolname  |             rolpassword
----------+-------------------------------------
 replica  | md5c599e20c9f60783d8628861956cfc9ae
 srcheck  | md5662c10f61b27a9ab38ce69157186b25f
 postgres | md53175bce1d3201d16594cebf9d7eb3f9d
 
 
 ubuntu@master:~$ sudo vim /etc/pgpool2/pool_passwd
 postgres:md53175bce1d3201d16594cebf9d7eb3f9d

创建check用户


xxxxxxxxxx
postgres=# create role srcheck nosuperuser login encrypted password 'SRCHECK';  
CREATE ROLE

启动pgpool


xxxxxxxxxx
sudo /etc/init.d/pgpool2 restart

如何使用

将原先连接数据库的ip和port换成pgpool的ip和端口即可。