PostgreSQL读写分离——pgpool-ll

1、配置流复制环境
参考：PostgreSQL流复制

2、安装pgpool
[email protected]> cd pgpool-II-4.0.2
[email protected]>./configure –prefix=/home/itm_pg/pgpool -with
[email protected]>make&make install

3、配置pgpool
[email protected]> cp pgpool.conf.sample pgpool.conf
[email protected]> vi pgpool.conf

listen_addresses = '0.0.0.0'    
port = 9999    
socket_dir = '/tmp'    
pcp_port = 9898    
pcp_socket_dir = '/tmp'    
backend_hostname0 = '192.168.17.190'    
backend_port0 = 1921    
backend_weight0 = 1    
backend_flag0 = 'ALLOW_TO_FAILOVER'    
backend_hostname1 = '192.168.7.177'    
backend_port1 = 1921    
backend_weight1 = 1    
backend_flag1 = 'ALLOW_TO_FAILOVER'    
enable_pool_hba = on    
pool_passwd = 'pool_passwd'    
authentication_timeout = 60    
ssl = off    
num_init_children = 32    
max_pool = 4    
child_life_time = 300    
child_max_connections = 0    
connection_life_time = 0    
client_idle_limit = 0    
log_destination = 'syslog'    
print_timestamp = on    
log_connections = on    
log_hostname = off    

# 以下两个参数用于调试, 能看到sql balance的情况.    
log_statement = on    
log_per_node_statement = on    
  
log_standby_delay = 'always'    
syslog_facility = 'LOCAL0'    
syslog_ident = 'pgpool'    
debug_level = 0    
pid_file_name = '/home/itm_pg/pgpool/pgpool.pid'    
logdir = '/tmp'    
connection_cache = on    
reset_query_list = 'ABORT; DISCARD ALL'    
replication_mode = off    
replicate_select = off    
insert_lock = off    
lobj_lock_table = ''    
replication_stop_on_mismatch = off    
failover_if_affected_tuples_mismatch = off    
load_balance_mode = on    
ignore_leading_white_space = on    
white_function_list = ''    
black_function_list = 'currval,lastval,nextval,setval'    
master_slave_mode = on    
master_slave_sub_mode = 'stream'    
sr_check_period = 2    
sr_check_user = 'srcheck'    
sr_check_password = 'SRCHECK'    
delay_threshold = 1024000    
follow_master_command = ''    
parallel_mode = off    
pgpool2_hostname = ''    
health_check_period = 0    
health_check_timeout = 20    
health_check_user = 'nobody'    
health_check_password = ''    
health_check_max_retries = 0    
health_check_retry_delay = 1    
failover_command = ''    
failback_command = ''    
fail_over_on_backend_error = on    
search_primary_node_timeout = 10    
recovery_user = 'nobody'    
recovery_password = ''    
recovery_1st_stage_command = ''    
recovery_2nd_stage_command = ''    
recovery_timeout = 90    
client_idle_limit_in_recovery = 0    
use_watchdog = off    
trusted_servers = ''    
ping_path = '/bin'    
wd_hostname = ''    
wd_port = 9000    
wd_authkey = ''    
delegate_IP = ''    
ifconfig_path = '/sbin'    
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'    
if_down_cmd = 'ifconfig eth0:0 down'    
arping_path = '/usr/sbin'           # arping command path    
arping_cmd = 'arping -U $_IP_$ -w 1'    
clear_memqcache_on_escalation = on    
wd_escalation_command = ''    
wd_lifecheck_method = 'heartbeat'    
wd_interval = 10    
wd_heartbeat_port = 9694    
wd_heartbeat_keepalive = 2    
wd_heartbeat_deadtime = 30    
heartbeat_destination0 = 'host0_ip1'    
heartbeat_destination_port0 = 9694     
heartbeat_device0 = ''    
wd_life_point = 3    
wd_lifecheck_query = 'SELECT 1'    
wd_lifecheck_dbname = 'template1'    
wd_lifecheck_user = 'nobody'    
wd_lifecheck_password = ''    
relcache_expire = 0    
relcache_size = 256    
check_temp_table = on    
memory_cache_enabled = off    
memqcache_method = 'shmem'    
memqcache_memcached_host = 'localhost'    
memqcache_memcached_port = 11211    
memqcache_total_size = 67108864    
memqcache_max_num_cache = 1000000    
memqcache_expire = 0    
memqcache_auto_cache_invalidation = on    
memqcache_maxcache = 409600    
memqcache_cache_block_size = 1048576    
memqcache_oiddir = '//home/itm_pg/pgpool/oiddir'    
white_memqcache_table_list = ''    
black_memqcache_table_list = ''  

生成pgpool管理秘钥, 并配置:
[email protected]> /home/itm_pg/pgpool/bin/pg_md5 -u sup_pool sup_pool_pwd
c21777db255631573e4233403773bb3b
[email protected]> vi pcp.conf

配置pgpool pool_hba.conf:
[email protected]> vi pool_hba.conf

# trust会导致错误    
local   all         all                               trust    
host    all         all         127.0.0.1/32          trust    
host    all         all         0.0.0.0/0             md5    

配置pgpool-ii密码文件, 从postgresql数据库pg_authid获取：

postgres=# select rolname,rolpassword from pg_authid;
       rolname        |             rolpassword             
----------------------+-------------------------------------
 pg_monitor           | 
 pg_read_all_settings | 
 pg_read_all_stats    | 
 pg_stat_scan_tables  | 
 pg_signal_backend    | 
 osdba                | md53b95a76d6aa5d605770a53120673337b
 postgres             | md53175bce1d3201d16594cebf9d7eb3f9d
(7 rows)

[email protected]> vi pool_passwd
postgres:md53175bce1d3201d16594cebf9d7eb3f9d

创建check用户，pgpool用它连接数据库并检查数据库是否正常：

itm_pg@docker-> psql 
psql (10.3)
Type "help" for help.

postgres=# create role srcheck nosuperuser login encrypted password 'SRCHECK';  
CREATE ROLE

启动pgpool：
[email protected]> /home/itm_pg/pgpool/bin/pgpool -f pgpool.conf

至此，流复制模式的pgpool-ll搭建完成！