版本：Next

读写分离的配置步骤

下面在10.16.16.158上进行读写分离安装配置

配置、修改pgpool.conf文件：

vi /u01/app/halo/product/dlb/4.2/etc/pgpool.conf

listen_addresses = '*'

# Host name or IP address to listen on:

# '*' for all, '' for no TCP/IP connections

# (change requires restart)

port = 1921

# Port number

# (change requires restart)

# - Backend Connection Settings -

backend_hostname0 = '10.16.16.155'

# Host name or IP address to connect to for backend 0

backend_port0 = 1921

# Port number for backend 0

backend_weight0 = 1

# Weight for backend 0 (only in load balancing mode)

backend_data_directory0 = '/data'

# Data directory for backend 0

backend_flag0 = 'DISALLOW_TO_FAILOVER'

# Controls various backend behavior

# ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER

# or ALWAYS_PRIMARY

backend_application_name0 = 'server0'

# walsender's application_name, used for "show pool_nodes" command

backend_hostname1 = '10.16.16.156'
backend_port1 = 1921
backend_weight1 = 1
backend_data_directory1 = '/data1'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'

backend_hostname2 = '10.16.16.157'
backend_port2 = 1921
backend_weight2 = 1
backend_data_directory2 = '/data1'
backend_flag2 = 'DISALLOW_TO_FAILOVER'
backend_application_name2 = 'server2'
pid_file_name = '/var/run/halo/halo.pid'

# PID file name

# Can be specified as relative to the"

# location of pgpool.conf file or

# as an absolute path

# (change requires restart)

#----------------------------------------------------------------------
NATIVE REPLICATION MODE
#----------------------------------------------------------------------

# - Streaming -

sr_check_period = 10

# Streaming replication check period

# Disabled (0) by default

sr_check_user = 'halo_dlb'

# Streaming replication check user

# This is neccessary even if you disable streaming

# replication delay check by sr_check_period = 0

sr_check_password = 'halo_dlb'

# Password for streaming replication check user

# Leaving it empty will make Pgpool-II to first look for the

# Password in pool_passwd file before using the empty password

sr_check_database = 'halo0root'

# Database name for streaming replication check

配置环境变量

export PATH=/u01/app/halo/product/dlb/4.2/bin:\$PATH

需要在主库上创建halo_dlb超级用户

生成pgpool-II管理秘钥

[halo@localhost ~]$ pg_md5 -m -u halo_dlb halo_dlb

bfdc834dcd14ab28c7e1f2814b8f1e38

参数：-m 生成md5认证密码 -u User生成md5认证密码时，为User创建pool_passwd条目。

添加生成密钥

vi /u01/app/halo/product/dlb/4.2/etc/pcp.conf
halo_dlb:bfdc834dcd14ab28c7e1f2814b8f1e38

配置访问地址认证方式

vi /u01/app/halo/product/dlb/4.2/etc/pool_hba.conf
"local" is for Unix domain socket connections only
local all all trust
IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 0.0.0.0/0 md5

生成pool_passwd

pg_md5 --md5auth --username=halo_dlb halo_dlb

启动dlb

nohup dlb -n -d > /u01/app/halo/product/dlb/4.2/logs/dlb.log 2\>&1 &

实现连接分离

psql -h 10.16.16.158 -p 1921 -U halo_dlb

查看状态

show pool_nodes;