pgpool集群搭建

编译工作

编译依赖包

sudo yum install -y arping gcc openssl-devel postgresql-devel 

运行pgpool如果有安装postgresql服务的话，则不需要 postgresql-devel 包

pgpool源码

curl -L -o pgpool-II-4.4.5.tar.gz https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.4.5.tar.gz
tar xvf pgpool-II-4.4.5.tar.gz
cd pgpool-II-4.4.5

配置编译参数

./configure --prefix=/app/pgpool --with-openssl

编译按照

make
sudo make install

变更目录拥有者

sudo chown -R postgres. /app/pgpool

pgpool配置

设置环境变量

sudo su - postgres

cat <<'EOF' | tee -a ~/.bashrc > /dev/null
# pgpool-II Service Environment variable
export PGPOOL_HOME=/app/pgpool
export PATH=$PGPOOL_HOME/bin:$PATH

EOF

source ~/.bashrc 

主机免密

ssh-keygen -P '' -f ~/.ssh/id_rsa_pgpool
ssh-copy-id -i ~/.ssh/id_rsa_pgpool postgres@x.x.x.x

pgpool 和 postgresql 主机都需要免密登录

配置访问权限

与postgresql的 pg_hba.conf 内容一致

cat <<'EOF' | tee $PGPOOL_HOME/etc/pool_hba.conf > /dev/null
# 配置postgresql集群主机 replication 免密登录
host    replication     all             192.168.32.127/32       trust
host    replication     all             192.168.32.128/32       trust
host    replication     all             192.168.32.132/32       trust
# 配置 同网段 主机密码登录
host    all             all             192.168.32.0/24         scram-sha-256
EOF

设置pcp密码

pcp_xxx相关命令执行的密码，连接pcp服务(pgpool子进程)的

cat <<EOF | tee $PGPOOL_HOME/etc/pcp.conf > /dev/null
postgres:$(pg_md5 123456)
EOF

pgpool编号

echo 0 > $PGPOOL_HOME/etc/pgpool_node_id

pgpool认证密码

业务连接pgpool的密码

echo 123456 > /app/pgpool/.pgpoolkey
chmod 600 /app/pgpool/.pgpoolkey
echo postgres:Yx.0810. > /app/pgpool/.user
echo repl:oLfex^5pfe >> /app/pgpool/.user
echo pgpool:Ufwx@8fsd >> /app/pgpool/.user

pg_enc -m -k /app/pgpool/.pgpoolkey -f /app/pgpool/etc/pgpool.conf -i /app/pgpool/.user 

配置修改

cp $PGPOOL_HOME/etc/pgpool.conf.sample $PGPOOL_HOME/etc/pgpool.conf

$ grep -E -v "^$|^[[:space:]]{2,}|^#[a-z]" $PGPOOL_HOME/etc/pgpool.conf
# ----------------------------
# pgPool-II configuration file
# ----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# Whitespace may be used.  Comments are introduced with "#" anywhere on a line.
# The complete list of parameter names and allowed values can be found in the
# pgPool-II documentation.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pgpool reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
#------------------------------------------------------------------------------
# BACKEND CLUSTERING MODE
# Choose one of: 'streaming_replication', 'native_replication',
#       'logical_replication', 'slony', 'raw' or 'snapshot_isolation'
# (change requires restart)
#------------------------------------------------------------------------------
backend_clustering_mode = 'streaming_replication'
#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
# - pgpool Connection Settings -
listen_addresses = '*'
port = 9999
# - pgpool Communication Manager Connection Settings -
pcp_listen_addresses = '*'
pcp_port = 9898
# - Backend Connection Settings -
backend_hostname0 = '192.168.32.129'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data/pg_data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'pg01'
backend_hostname1 = '192.168.32.127'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/pg_data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'pg02'
backend_hostname2 = '192.168.32.128'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/data/pg_data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'pg03'
# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
# - SSL Connections -
#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
# - Concurrent session and pool size -
# - Life time -
#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------
# - Where to log -
# - What to log -
# - Syslog specific -
# - Debug -
# This is used when logging to stderr:
logging_collector = on
# -- Only used if logging_collector is on ---
log_directory = '/app/pgpool/logs'
log_filename = 'pgpool-%Y-%m-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = off
log_rotation_age = 1d
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/app/pgpool/pgpool.pid'
#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
# - Degenerate handling -
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# STREAMING REPLICATION MODE
#------------------------------------------------------------------------------
# - Streaming -
sr_check_period = 10
sr_check_user = 'repl'
sr_check_password = ''
sr_check_database = 'postgres'
# - Special commands -
#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS
#------------------------------------------------------------------------------
health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = ''
health_check_database = 'postgres'
#------------------------------------------------------------------------------
# HEALTH CHECK PER NODE PARAMETERS (OPTIONAL)
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '/app/pgpool/etc/failover_stream.sh %H %h'
#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
# - Enabling -
use_watchdog = on
# -Connection to up stream servers -
# - Watchdog communication Settings -
hostname0 = '192.168.32.129'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = '192.168.32.127'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = '192.168.32.128'
wd_port2 = 9000
pgpool_port2 = 9999
# - Virtual IP control Setting -
delegate_ip = '192.168.32.188'
if_cmd_path = '/sbin'
if_up_cmd = '/sbin/ip addr add $_IP_$/32 dev ens33 label ens33:0'
if_down_cmd = '/sbin/ip addr del $_IP_$/32 dev ens33'
arping_path = '/usr/sbin'
arping_cmd = '/usr/sbin/arping -U $_IP_$ -w 1 -I ens33'
ping_path = '/bin'
# - Behaivor on escalation Setting -
clear_memqcache_on_escalation = on
wd_escalation_command = '/app/pgpool/etc/escalation.sh'
# - Watchdog consensus settings for failover -
# - Watchdog cluster membership settings for quorum computation -
# - Lifecheck Setting -
# -- common --
# -- heartbeat mode --
heartbeat_hostname0 = '192.168.32.129'
heartbeat_port0 = 9694
heartbeat_hostname1 = '192.168.32.127'
heartbeat_port1 = 9694
heartbeat_hostname2 = '192.168.32.128'
heartbeat_port2 = 9694
# -- query mode --
#------------------------------------------------------------------------------
# OTHERS
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# IN MEMORY QUERY MEMORY CACHE
#------------------------------------------------------------------------------

PG切主脚本

pgpool主节点执行

cat <<'EOF' | tee $PGPOOL_HOME/etc/failover_stream.sh > /dev/null
# This script is run by failover_command.
# Parameter description
#   $1: new master hostname
NEW_MASTER=$1
OLD_MASTER=$2
PG_STARTUP_USER=postgres
SSH_KEY_FILE=id_rsa_pgpool
SSH_OPTIONS="-o StrictHostKeyChecking=no -i ~/.ssh/${SSH_KEY_FILE}"

## Test passwordless SSH
echo `date "+%Y-%m-%d %H:%M:%S"` [INFO] Test ssh connection to the new master server is ${NEW_MASTER}. >> /app/pgpool/logs/failover.log
ssh -T ${SSH_OPTIONS} ${PG_STARTUP_USER}@${NEW_MASTER} ls /tmp > /dev/null
if [ $? -ne 0 ]; then
    echo `date "+%Y-%m-%d %H:%M:%S"` [ERROR] passwordless SSH to ${POSTGRESQL_STARTUP_USER}@${NEW_MAIN_NODE_HOST} failed. Please setup passwordless SSH. >> /app/pgpool/logs/failover.log
    echo >> /app/pgpool/logs/failover.log 
    exit 1
fi

## Promote Standby node.
echo `date "+%Y-%m-%d %H:%M:%S"` [INFO] primary node is abnormal, promote new master is ${NEW_MASTER}. old master is ${OLD_MASTER} >> /app/pgpool/logs/failover.log

ssh -T ${SSH_OPTIONS} ${PG_STARTUP_USER}@${NEW_MASTER} \${PGHOME}/bin/pg_ctl --pgdata=\${PGDATA} promote &>> /app/pgpool/logs/failover.log
if [ $? -ne 0 ]; then
    echo `date "+%Y-%m-%d %H:%M:%S"` [ERROR] promote failed >> /app/pgpool/logs/failover.log
    echo >> /app/pgpool/logs/failover.log
    exit 1
else
    echo >> /app/pgpool/logs/failover.log
    exit 0
fi
EOF

chmod +x $PGPOOL_HOME/etc/failover_stream.sh

清理脚本

新pgpool主节点，在挂载VIP前执行的脚本

cat <<'EOF' | tee $PGPOOL_HOME/etc/escalation.sh > /dev/null
#!/bin/bash
# This script is run by wd_escalation_command to bring down the virtual IP on other pgpool nodes
# before bringing up the virtual IP on the new active pgpool node.

PG_STARTUP_USER=postgres
SSH_KEY_FILE=id_rsa_pgpool
SSH_OPTIONS="-o StrictHostKeyChecking=no -i ~/.ssh/${SSH_KEY_FILE}"
PGPOOLS=(192.168.32.132 192.168.32.127 192.168.32.128)
DEVICES=(br0 ens33 ens33)

VIP=192.168.32.188

for index in "${!PGPOOLS[@]}"; do
    ssh -T ${SSH_OPTIONS} ${PG_STARTUP_USER}@${PGPOOLS[index]} "/sbin/ip addr del ${VIP}/24 dev ${DEVICES[index]} 2> /dev/null"
    if [ $? -eq 0 ]; then
        echo `date "+%Y-%m-%d %H:%M:%S"` INFO: Successful to release VIP on ${PGPOOLS[index]}. >> /app/pgpool/logs/escalation.log
    fi
done
exit 0
EOF

chmod +x $PGPOOL_HOME/etc/escalation.sh

命令赋权

sudo chmod +s /sbin/ip
sudo chmod +s /sbin/arping

集群其他节点配置

获取pgpool

scp -P 22 -r postgres@x.x.x.x:/app/pgpool /app/
sudo chown -R postgres. /app/pgpool

依赖包安装

sudo yum install -y arping openssl-devel postgresql-devel 

设置环境变量

sudo su - postgres

cat <<'EOF' | tee -a ~/.bashrc > /dev/null
# pgpool-II Service Environment variable
export PGPOOL_HOME=/app/pgpool
export PATH=$PGPOOL_HOME/bin:$PATH

EOF

source ~/.bashrc 

主机免密

ssh-keygen -P '' -f ~/.ssh/id_rsa_pgpool
ssh-copy-id -i ~/.ssh/id_rsa_pgpool postgres@x.x.x.x

pgpool 和 postgresql 主机都需要免密登录

pgpool编号

vi $PGPOOL_HOME/etc/pgpool_node_id

命令赋权

sudo chmod +s /sbin/ip
sudo chmod +s /sbin/arping

启动服务

cat <<'EOF' | sudo tee /usr/lib/systemd/system/pgpool.service >> /dev/null
[Unit]
Description=Pgpool Server
Documentation=https://www.pgpool.net/docs/latest/en/html/
Wants=network.service
After=network.service

[Service]
Type=forking
User=ops
ExecStart=/app/pgpool/bin/pgpool -k /app/pgpool/.pgpoolkey
ExecStop=/app/pgpool/bin/pgpool -k /app/pgpool/.pgpoolkey stop
ExecReload=/app/pgpool/bin/pgpool -k /app/pgpool/.pgpoolkey reload
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl start pgpool.service

查看日志

tail -f $PGPOOL_HOME/logs/pgpool-`date +%Y-%m-%d`.log

常用命令

# 查看pg节点状态
pcp_node_info -h 192.168.32.188 -p 9898 -U postgres
psql -h 192.168.32.188 -p 9999 -U postgres -c "show pool_nodes;"

# 查看看门狗状态
pcp_watchdog_info -h 192.168.32.188 -p 9898 -U postgres

# pgpool加入集群
pcp_attach_node -h 192.168.32.188 -p 9898 -U postgres 0