通过代理pgpool来访问postgresql
环境说明
主postgresql:192.168.2.196
从postgresql:192.168.2.71
pgpool:192.168.2.102 #为了省机器可用放在从postgresql机器上
主从数据库上有能被pgpool服务器访问的账号密码
test 123456
如果没有的话,在主数据库上创建用户
create user test password '123456';
安装
下载源码包
wget http://www.pgpool.net/mediawiki/images/pgpool-II-3.6.5.tar.gz
tar -xf pgpool-II-3.6.5.tar.gz -C /usr/local/src
cd /usr/local/src/pgpool-II-3.6.5
依赖
yum -y install gcc postgresql-devel #如果是在从服务器安装的pgpool 不需要安装postgresql-devel
编译安装
./configure --prefix=/usr/local/pgpool --with-pgsql=/usr/include/pgsql/
#如果之在从postgresql安装的。请讲编译的路径指向postgresql的安装路径
make; make install
添加环境变量
echo "export PATH=$PATH:/usr/local/pgpool/bin" >> /etc/profile
source /etc/profile
pcp.conf配置文件(这个文件在这个服务中没起到作用,可以无视)
使用pg_md5生成加密的密码
pg_md5 123456
#结果如下
e10adc3949ba59abbe56e057f20f883e
假如pcp为账号
(pgpool-II 有一个控制接口,管理员可以通过它远程收集 pgpool-II 的状态信息或者终止 pgpool-II 进程。
pcp.conf
是用于这个接口认证的用户/密码文件)
cd /usr/local/pgpool/etc
cp pcp.conf.sample pcp.conf
echo "pcp:e10adc3949ba59abbe56e057f20f883e" >> pcp.conf
pgpool.conf配置文件(核心文件)
复制pgpool文件
cd /usr/local/pgpool/etc
cp pgpool.conf.sample pgpool.conf
需要修改的配置如下
vi pgpool.conf
listen_addresses = '*'
backend_hostname0 = '192.168.2.196' #主机ip
backend_port0 = 5432
backend_weight0 = 1 #loadbalance不开启
backend_data_directory0 = '/usr/local/postgresql/data' #数据目录
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.2.71' #备机ip
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/postgresql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
#这里是连接pgpool登录的账号密码
enable_pool_hba = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 1 #启用
sr_check_user = 'replicator' #流复制账号
sr_check_password = '123456' #流复制密码
#健康检测
health_check_period = 1 # Health check period Disabled (0) by default
health_check_timeout = 10 # Health check timeout 0 means no timeout
health_check_user = 'test' # Health check user
health_check_password = '123456' # 健康检查的密码
health_check_database = 'postgres' #数据库
主postgresql挂掉后执行的脚本
failover_command = 'sh /shell/failover.sh'
下面是failover.sh脚本的内容,很简单,ip自己改下
mkdir /shell
vi /shell/failover.sh
#!/bin/bash
ssh 192.168.2.71 'su postgres -c "/usr/local/postgresql/bin/pg_ctl promote -m fast -D /usr/local/postgresql/data"'
设置pool_hba.conf 类似postgresql的pg_hba.conf
复制pool_hba.conf
cp pool_hba.conf.sample pool_hba.conf
我的是这样的,其实可以把pg_hba.conf文件复制过来
local all all trust
# IPv4 local connections:
host all all 0.0.0.0/0 md5
host replication replicator 0.0.0.0/0 md5
host all all ::1/128 trust
设置登录pgpool的账号密码,请注意的是用户名和密码必须和 PostgreSQL 中注册的一样
pg_md5 -m -u test 123456
需要认证ssh,不然无法执行脚本,因为只需要操作从postgresql,所以只添加这一台(请参照博文另一篇linux登录免密设置)
ssh-keygen
ssh-copy-id 192.168.2.71
测试
pid默认文件是在这里 /var/run/pgpool
还有状态文件放在这里 /var/log/pgpool
mkdir /var/run/pgpool
mkdir /var/log/pgpool
启动服务前可以pgpool -n查看启动状态
[root@localhost etc]# pgpool -n
2017-07-25 10:00:21: pid 9227: LOG: Backend status file /var/log/pgpool/pgpool_status does not exist
2017-07-25 10:00:21: pid 9227: LOG: Setting up socket for 0.0.0.0:9999
2017-07-25 10:00:21: pid 9227: LOG: Setting up socket for :::9999
2017-07-25 10:00:21: pid 9227: LOG: find_primary_node_repeatedly: waiting for finding a primary node
2017-07-25 10:00:21: pid 9227: LOG: find_primary_node: checking backend no 0
2017-07-25 10:00:21: pid 9227: LOG: find_primary_node: primary node id is 0
2017-07-25 10:00:21: pid 9227: LOG: pgpool-II successfully started. version 3.6.5 (subaruboshi)
启动服务(pgpool设置的账号)
pgpool
psql postgres -h 192.168.2.102 -p 9999 -U 'test'
输入设置的密码123456
停止主postgresql服务之前
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | 192.168.2.196 | 5432 | up | 0.500000 | primary | 0 | true | 0
1 | 192.168.2.71 | 5432 | up | 0.500000 | standby | 0 | false | 0
(2 rows)
停止主postgresql服务后
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | 192.168.2.196 | 5432 | down | 0.500000 | standby | 0 | false | 0
1 | 192.168.2.71 | 5432 | up | 0.500000 | primary | 0 | true | 0
(2 rows)
补充部分
pgpool用户手册 有详细配置文件说明及其使用
原文链接:https://www.hyahm.com/article/150
文章评论