读写分离的配置步骤

下面在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;