pgpool + postgres 读写分离配置(5)

0. chown postgres:postgres /usr/pgpool 

1.进入pgpool的etc目录

 cd /usr/pgpool/etc/

2.拷贝配置

  

cp pgpool.conf.sample-stream  pgpool.conf

3.编辑参数 vi pgpool.conf 

listen_addresses = '*'

port = 9999

backend_hostname0 = '192.168.1.108'

backend_port0 = 5432

backend_weight0 = 1

backend_data_directory0 = '/var/lib/pgsql/9.2/data'

backend_hostname1 = '192.168.1.109'

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '/var/lib/pgsql/9.2/data'

log_connections = on

log_hostname = on

log_statement = on

log_per_node_statement = on

debug_level = 1

pid_file_name = '/usr/pgpool/pgpool.pid'

logdir = '/usr/pgpool/log'

#后头配置 必须跟recover.conf必须一致 

failover_command = '/usr/pgpool/etc/failedcommand.sh'

sr_check_user = 'repuser'

sr_check_password = 'repuser'

delay_threshold = 10000

                       

# - Authentication -

enable_pool_hba = on

                        system_db_hostname ="192.168.1.108"

  

4 .在本服务器的postgresql中，为pgpool创建系统数据库以及schema 

      su postgres

      cd  /usr/pgsql-9.2/bin

      ./createuser -p 5432 pgpool             

      //在本地的postgresql数据库创建pgpool用户

       ./createdb -p 5432 -O pgpool pgpool           

      //在本地的postgresql数据库创建pgpool数据库,所有者为pgpool

5.建立日志目录

  

  mkdir /usr/pgpool/log

    

6.failedcommand.sh 编辑Master库失败强制转到slave文件

cd /usr/pgpool/etc/

vi failedcommand.sh 

#! /bin/sh

# Failover command for streaming replication.

# This script assumes that DB node 0 is primary, and 1 is standby.

#

# If standby goes down, do nothing. If primary goes down, create a

# trigger file so that standby takes over primary node.

#

# Arguments: $1: failed node id. $2: new master hostname. $3: path to

# trigger file.

failed_node=$1

new_master=$2

trigger_file=$3

# Do nothing if standby goes down.

if [ $failed_node = 1 ]; then

        exit 0;

fi

 /usr/bin/ssh -T $new_master /bin/touch $trigger_file

exit 0;

7.赋予文件可执行权限

chmod a+x  /usr/pgpool/etc/failedcommand.sh 

8.暂时没用设置Master服务器与Slave密钥登录认证，请查看http://blog.163.com/czg_e/blog/static/46104561201101922246729/

9.配置pgpool连接认证

cp /usr/pgpool/etc/pool_hba.conf.sample  /usr/pgpool/etc/pool_hba.conf

vi  /usr/pgpool/etc/pool_hba.conf

host    all         all         0.0.0.0/0      md5

9.设置pgpool pcp用户管理权限

        

        通过pg md5对密码加密

/usr/pgpool/bin/pg_md5 repuser

提示你输入密码 我输入的是

         424b116a28bd35e1e21b81872fb003d5

cp /usr/pgpool/etc/pcp.conf.sample  /usr/pgpool/etc/pcp.conf

repuser:424b116a28bd35e1e21b81872fb003d5

        echo  "repuser:424b116a28bd35e1e21b81872fb003d5"  /usr/pgpool/etc/pool_passwd

        

vi  /usr/pgpool/etc/pcp.conf

postgres:e8a48653851e28c69d0506508fb27fc5

==================

        echo  "postgres:e8a48653851e28c69d0506508fb27fc5"  /usr/pgpool/etc/pool_passwd

        echo  "postgres:e8a48653851e28c69d0506508fb27fc5"  /usr/pgpool/etc/pool_passwd

       

配置PCP命令

pgpool-II有一个出于管理目的可以通过网络查询数据库节点信息、关闭pgpool-II等功能的接口（interface）。使用PCP命令首先必须通过用户验证，它不同于PostgreSQL的用户验证，需要在pcp.conf中定义用户名和密码。文件中的用户名/密码成对出现在每一行，用冒号隔开，密码是md5格式：

postgres:e8a48653851e28c69d0506508fb27fc5

   ==================

10.启动pgpool

cd /usr/pgpool/bin

./pgpool -f /usr/pgpool/etc/pgpool.conf -a /usr/pgpool/etc/pool_hba.conf  -F /usr/pgpool/etc/pcp.conf

停止命令 pgpool stop 

测试pgpool 是否正常运行

回到安装postgresql目录

/usr/pgsql-9.2/bin

./psql -h 192.168.1.108 -p 9999 -U repuser -d pgpool

psql: ERROR:  MD5 authentication is unsupported in replication, master-slave and parallel modes.

HINT:  check pg_hba.conf

解决上面错误需将pgpool.conf中 enable_pool_hba设置为 on；

CREATE TABLE testa

(

  id bigint NOT NULL,

  name character varying

);

插入了2个数据库表

======异常处理==========================

异常处理

2012-03-24 12:52:47 ERROR: pid 2424: pool_read_message_length: message length (8) in slot 1 does not match with slot 0(12)

2012-03-24 12:52:47 ERROR: pid 2424: Failed to read the authentication packet length. This is likely caused by the inconsistency of auth method among DB nodes. In this case you can check the previous error messages (hint: length field) from pool_read_message_length and recheck the pg_hba.conf settings.

通过pgsql不能登录且日志中输入如上信息，经排查发现为master库中pg_hba.conf设置采用md5 方式认证，而slave pg_hba.con配置的为trust认证方式，后将slave的pg_hba.conf文件配置为了md5方式即可通过验证登录。

psql: ERROR:  MD5 authentication is unsupported in replication, master-slave and parallel modes.

HINT:  check pg_hba.conf

通过pgsql不能登录且提示如上信息，解决上面错误需将pgpool.conf中 enable_pool_hba设置为 on；pool_hba.conf中加入md5验证（host    all         all         10.10.10.0/24         md5）；pool_passwd中设置用户名密码验证（/usr/pgpool/bin/pg_md5 -m -p -u postgres pool_passwd ）。