postgresql 10+pgpool3.6安装部署

环境说明

主postgresql：192.168.2.70
从postgresql：192.168.2.71

pgpool:192.168.2.22 #为了省机器可用放在从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

编译安装

./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

pgpool.conf配置文件（核心文件）

listen_addresses = '*'
backend_hostname0 = '192.168.2.70' #主机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'

设置pool_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 -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.22 -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.70 | 5432 | up     | 0.500000  | primary | 0          | true              | 0
1       | 192.168.2.71  | 5432 | up     | 0.500000  | standby | 0          | false             | 0
(2 rows)