PostgreSQL + pgpool-II 实现读写分离
pgpool-II 简介
支持 连接池、准备切换、负载均衡、读写分离
支持原始模式，复制模式，主备模式，并行模式多种模式
写性能不好，不支持部分查询
pgpool-II单点只能绑定在主库。
配置文件简介
escalation.sh.sample
failover.sh.sample
follow_primary.sh.sample
pcp.conf.sample # 管理pgpool用户相关的配置
pgpool.conf.sample # 主配置文件
pgpool.conf.sample-logical
pgpool.conf.sample-raw
pgpool.conf.sample-replication
pgpool.conf.sample-slony
pgpool.conf.sample-snapshot
pgpool.conf.sample-stream
pgpool_remote_start.sample
pool_hba.conf.sample # 用户白名单及监控用户配置
pool_password # 加密存储密码（后创建）
recovery_1st_stage.sample
recovery_2nd_stage.sample
安装
# 下载包
wget https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.2.5.tar.gz
# 解压
tar xf download.php?f=pgpool-II-4.2.5.tar.gz
# 配置
cd pgpool-II-4.2.5
./configure --prefix=/pgdata/pgpool --with-pgsql=/usr/local/pg12
# --prefix # 指定安装路径
# --with-pgsql # 指定postgresql的安装路径
# 编译
make
# 安装
make install
修改配置文件
-- 主库创建心跳检测用户
create role nobody with login password 'dyh666';
-- 创建业务用户
create database yewu;
create role yewu with login password 'dyh666';
grant all on database yewu to yewu;
\c yewu
create table t1(id int);
# 生成加密密码 // 创建数据库的用户密码
/pgdata/pgpool/bin/pg_md5 -m -p -u nobody pool_passwd
/pgdata/pgpool/bin/pg_md5 -m -p -u yewu pool_passwd
# 编辑数据库密码文件
vim /pgdata/pgpool/etc/pool_passwd
# 以下为用户及加密的密码
nobody:md5fc056767b2f222f4f4697a99267612a4
yewu:md5fc056767b2f222f4f4697a99267612a4
# 创建 pgpool.conf 配置文件
cp /pgdata/pgpool/etc/pgpool.conf.sample /pgdata/pgpool/etc/pgpool.conf
# 编辑配置文件
vim /pgdata/pgpool/etc/pgpool.conf
backend_clustering_mode = 'streaming_replication'
# 配置监听地址
listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
reserved_connections = 0
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off
################################################################
# 节点信息配置
backend_hostname0 = '10.10.8.176'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/pgdata/12/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'pg-01'
################################################################
backend_hostname1 = '10.10.8.106'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/pgdata/12/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'pg-02'
################################################################
backend_hostname2 = '10.10.8.177'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/pgdata/12/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'pg-03'
################################################################
# 开启身份验证
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 1min
allow_clear_text_frontend_auth = off
ssl = off
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = off
ssl_ecdh_curve = 'prime256v1'
ssl_dh_params_file = ''
num_init_children = 32
max_pool = 4
child_life_time = 5min
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
# 将日志写入到系统日志
log_destination = 'syslog'
log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.
log_connections = off
log_disconnections = off
log_hostname = off
log_statement = all
log_per_node_statement = on
log_client_messages = on
log_standby_delay = 'if_over_threshold'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
# 日志相关配置
client_min_messages = notice
log_min_messages = info
log_directory = '/tmp/pgpool_logs'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/tmp'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
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
read_only_function_list = ''
write_function_list = ''
primary_routing_query_pattern_list = ''
database_redirect_preference_list = ''
app_name_redirect_preference_list = ''
allow_sql_comments = off
disable_load_balance_on_write = 'transaction'
dml_adaptive_object_relationship_list= ''
statement_level_load_balance = off
sr_check_period = 10
sr_check_user = 'nobody'
sr_check_password = ''
sr_check_database = 'postgres'
delay_threshold = 10000000
follow_primary_command = ''
health_check_period = 0
health_check_timeout = 20
health_check_user = 'nobody'
health_check_password = ''
health_check_database = ''
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000
failover_command = ''
failback_command = ''
failover_on_backend_error = on
detach_false_primary = off
search_primary_node_timeout = 5min
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
auto_failback = off
auto_failback_interval = 1min
use_watchdog = off
trusted_servers = ''
ping_path = '/bin'
hostname0 = ''
wd_port0 = 9000
pgpool_port0 = 9999
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
delegate_IP = ''
if_cmd_path = '/sbin'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
arping_path = '/usr/sbin'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_de_escalation_command = ''
failover_when_quorum_exists = on
failover_require_consensus = on
allow_multiple_failover_requests_from_node = off
enable_consensus_with_half_votes = off
wd_monitoring_interfaces_list = ''
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
heartbeat_hostname0 = ''
heartbeat_port0 = 9694
heartbeat_device0 = ''
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
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 = catalog
check_unlogged_table = on
enable_shared_relcache = on
relcache_query_target = primary
memory_cache_enabled = off
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 64MB
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 400kB
memqcache_cache_block_size = 1MB
memqcache_oiddir = '/var/log/pgpool/oiddir'
cache_safe_memqcache_table_list = ''
cache_unsafe_memqcache_table_list = ''
# 密码加密
/pgdata/pgpool/bin/pg_md5 dyh666
# 修改 pcp.conf 用户密码文件
cp -a /pgdata/pgpool/etc/pcp.conf.sample /pgdata/pgpool/etc/pcp.conf
vim /pgdata/pgpool/etc/pcp.conf
pgpool:fc056767b2f222f4f4697a99267612a4
# 修改 pool_hba.conf 防火墙文件
cp -a /pgdata/pgpool/etc/pool_hba.conf.sample /pgdata/pgpool/etc/pool_hba.conf
vim /pgdata/pgpool/etc/pool_hba.conf
host all all 0.0.0.0/0 md5
host replication all 0.0.0.0/0 md5
启停pg_pool
# 启动
/pgdata/pgpool/bin/pgpool -f /pgdata/pgpool/etc/pgpool.conf
# 停止
/pgdata/pgpool/bin/pgpool -m fast stop
# 重载配置
/pgdata/pgpool/bin/pgpool reload
验证
# 登录监控用户查看节点信息状态
PGPASSWORD=dyh666 psql -h10.10.8.177 -d postgres -p 9999 -U nobody -c "\x" -c"show pool_nodes;"
# 查看pgpool 状态信息
/pgdata/pgpool/bin/pcp_node_info -U pgpool -h10.10.8.177 -p9898 -n 0 -v
# 验证读写分离功能 // 多开几个session
PGPASSWORD=dyh666 psql -h10.10.8.177 -d yewu -p 9999 -U yewu -c "select * from t1;"
PGPASSWORD=dyh666 psql -h10.10.8.177 -d yewu -p 9999 -U yewu -c "insert into t1 values(1);"
# server 端实时查看日志
tail -f /var/log/messages
