通过代理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


欢迎留言