安装部署postgresql-15 高可用（pgpool）

文章目录

• 前言
• • 部署环境：
  • 下载地址：
  • 安装pg15
  • 安装pgpool
  • • 配置pgpass文件
    • 创建pgpool_node_id
    • Pgpool-II参数配置
• 配置PostgreSQL主备库
• • • 1. 启动Pgpool-II
    • 2. 3个节点都启动pgpool
    • 3. 利用pgpool的在线恢复功能配置数据库主从
    • 4. 配置从的服务器
  • 测试 切换active/standby watchdog

前言

装部署postgresql-15 高可用（pgpool）一主二从

部署环境：

centos7 x86_64
数据库版本： postgresql 15
服务器IP：
10.10.3.11
10.10.3.12
10.10.3.13
vip : 10.10.3.100

下载地址：

官网地址：
https://www.postgresql.org/download/linux/redhat/

RPM包下载：

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装pg15

sudo yum install -y postgresql15-server

1 创建pg15的安装目录
mkdir -p /u01/apps/pgsql15/data
赋权
chown -R postgres:postgres /u01/apps/pgsql15/
2 yum安装数据库
yum install -y postgresql15-server
yum install epel-release – 依赖
3 初始化数据库

su – postgres
/usr/pgsql-15/bin/initdb -D /u01/apps/pgsql15/data

4.修改服务启动脚本
用root用户修改 /usr/lib/systemd/system/postgresql-15.service 文件，将其中的PGDATA修改为新的数据目录

vim /usr/lib/systemd/system/postgresql-15.service

# Location of database directoryEnvironment=PGDATA=/u01/apps/pgsql15/data/

sed -i "/^Environment=PGDATA/cEnvironment=PGDATA=\/u01\/apps\/pgsql15\/data\/" /usr/lib/systemd/system/postgresql-15.service

打开端口

firewall-cmd –permanent –zone=public –add-service=postgresql

firewall-cmd –permanent –zone=public –add-port=5432/tcp

firewall-cmd –reload

安装pgpool

官网下载地址：
https://www.pgpool.net/yum/rpms/4.3/redhat/rhel-7-x86_64/

1 创建文件目录
mkdir -p /u01/apps/pgpool
chown -R postgres:postgres /u01/apps/pgpool/
2.yum安装 pgpool

yum localinstall *.rpm -y

配置自启

systemctl enable postgresql-15
systemctl disable pgpool.service
systemctl disable postgresql-15

配置参数

修改 postgresql.conf 文件

su - postgres mkdir -p /u01/apps/pgsql15/archivedir/ --归档日志目录-- 配置允许PG远程登录，注意版本：cat >> /u01/apps/pgsql15/data/postgresql.conf << "EOF"listen_addresses = '*'port=5432logging_collector = onlog_truncate_on_rotation = on archive_mode = onarchive_command = 'cp "%p" "/u01/apps/pgsql15/archivedir/%f"'max_wal_senders = 10max_replication_slots = 10wal_level = replicahot_standby = onwal_log_hints = onpassword_encryption = 'md5'EOF

修改 pg_hba.conf文件

sed -i '$a host\tall\t\tall\t\t0.0.0.0\/0\t\tmd5' /u01/apps/pgsql15/data/pg_hba.conf

sed -i '$a host\treplication\t\all\t\t10.10.3.0\/24\t\tmd5' /u01/apps/pgsql15/data/pg_hba.conf

启动数据库
systemctl start postgresql-15

创建用户，并修改密码
psql -U postgres -p 5432
CREATE ROLE pgpool WITH LOGIN;
CREATE ROLE repl WITH REPLICATION LOGIN;
ALTER USER postgres ENCRYPTED PASSWORD ‘postgres123’;
alter user postgres with encrypted password ‘postgres123’;
alter user pgpool with encrypted password ‘postgres123’;
alter user repl with encrypted password ‘postgres123’;
GRANT pg_monitor TO pgpool;

配置互信

配置SSH，实现无密码登录效果
需要在每台服务器都配置一遍

########################root用户cd ~/.sshssh-keygen -t rsa -f id_rsa_pgpoolssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.13#########################postgres用户su - postgrescd ~/.sshssh-keygen -t rsa -f id_rsa_pgpoolssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.13

[root@pg1 .ssh]# su - postgres上一次登录：五 3月 10 18:26:41 CST 2023pts/0 上最后一次失败的登录：一 3月 13 10:27:37 CST 2023从 pg1ssh:notty 上最有一次成功登录后有 1 次失败的登录尝试。-bash-4.2$ cd ~/.ssh-bash-4.2$ ll总用量 20-rw-------. 1 postgres postgres 5484 3月13 10:27 authorized_keys-rw-------. 1 postgres postgres 1679 3月10 18:27 id_rsa_pgpool-rw-r--r--. 1 postgres postgres394 3月10 18:27 id_rsa_pgpool.pub-rw-r--r--. 1 postgres postgres516 3月 3 11:03 known_hosts-bash-4.2$ ssh-keygen -t rsa -f id_rsa_pgpoolGenerating public/private rsa key pair.id_rsa_pgpool already exists.Overwrite (y/n)" />(empty for no passphrase): Enter same passphrase again: Your identification has been saved in id_rsa_pgpool.Your public key has been saved in id_rsa_pgpool.pub.The key fingerprint is:SHA256:757CudETkbmp7nZJgXTx1ZMww5/DSGGW3nTjpc7QPbY postgres@pg1The key's randomart image is:+---[RSA 2048]----+|...B=..||. .+o=o=+|| . o+ +.Bo*||. .+.ooX.||S +. +. +|| +..oE || .oo+. || .=ooo || o+=+|+----[SHA256]-----+-bash-4.2$ ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa_pgpool.pub"/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keyspostgres@10.10.3.11's password: Number of key(s) added: 1Now try logging into the machine, with: "ssh 'postgres@10.10.3.11'"and check to make sure that only the key(s) you wanted were added.-bash-4.2$ ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa_pgpool.pub"/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keyspostgres@10.10.3.12's password: Number of key(s) added: 1Now try logging into the machine, with: "ssh 'postgres@10.10.3.12'"and check to make sure that only the key(s) you wanted were added.-bash-4.2$ ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.13/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa_pgpool.pub"/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keyspostgres@10.10.3.13's password: Number of key(s) added: 1Now try logging into the machine, with: "ssh 'postgres@10.10.3.13'"and check to make sure that only the key(s) you wanted were added.-bash-4.2$ exit

使用ssh postgres@10.10.3.12-i ~/.ssh/id_rsa_pgpool指令检验是否需要密码

若需要密码，上述目录均执行restorecon -R -v ~/.ssh

配置pgpass文件

-- 配置psql的密码cat > /u01/apps/pgsql15/.pgpass <<"EOF"10.10.3.11:5432:replication:repl:postgres12310.10.3.12:5432:replication:repl:postgres12310.10.3.13:5432:replication:repl:postgres12310.10.3.100:9999:postgres:pgpool:postgres12310.10.3.100:9999:postgres:postgres:postgres12310.10.3.11:5432:postgres:postgres:postgres12310.10.3.12:5432:postgres:postgres:postgres12310.10.3.13:5432:postgres:postgres:postgres123EOFchown postgres:postgres /u01/apps/pgsql15/data/.pgpasschmod 600/u01/apps/pgsql15/data/.pgpassecho 'localhost:9898:pgpool:postgres123' > /u01/apps/pgsql15/data/.pcppasschmod 600 /u01/apps/pgsql15/data/.pcppasschown postgres.postgres /u01/apps/pgsql15/data/.pcppassecho 'pgpool:'`pg_md5 -u=pgpool postgres123` >> /etc/pgpool-II/pcp.conf

创建pgpool_node_id

10.10.3.11
echo “0” > /etc/pgpool-II/pgpool_node_id

10.10.3.12
echo “1” > /etc/pgpool-II/pgpool_node_id
10.10.3.13
echo “2” > /etc/pgpool-II/pgpool_node_id

Pgpool-II参数配置

cat > /etc/pgpool-II/pgpool.conf <<"EOF"backend_clustering_mode = 'streaming_replication'# - pgpool Connection Settings -listen_addresses = '*'port=9999socket_dir = '/u01/apps/pgsql15/data/'pcp_listen_addresses = '*'pcp_port = 9898pcp_socket_dir = '/u01/apps/pgsql15/data/'# - Streaming Replication Checksr_check_user = 'pgpool'sr_check_password = ''# - Health Checkhealth_check_period = 5health_check_timeout = 30health_check_user = 'pgpool'health_check_password = 'postgres123'health_check_max_retries = 3search_primary_node_timeout = 10min# - Backend Connection Settings -backend_hostname0 = '10.10.3.11'backend_port0 = 5432backend_weight0 = 1backend_data_directory0 = '/u01/apps/pgsql15/data'backend_flag0 = 'ALLOW_TO_FAILOVER'#backend_application_name0 = 'pg1'backend_hostname1 = '10.10.3.12'backend_port1 = 5432backend_weight1 = 1backend_data_directory1 = '/u01/apps/pgsql15/data'backend_flag1 = 'ALLOW_TO_FAILOVER'#backend_application_name0 = 'pg2'backend_hostname2 = '10.10.3.13'backend_port2 = 5432backend_weight2 = 1backend_data_directory2 = '/u01/apps/pgsql15/data'backend_flag2 = 'ALLOW_TO_FAILOVER'#backend_application_name0 = 'pg3'# - Failover configurationfailover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'# - Online Recovery Configurationsrecovery_user = 'postgres'recovery_password = 'postgres123'recovery_1st_stage_command = 'recovery_1st_stage'# - Client Authentication Configurationenable_pool_hba = onpool_passwd = '/etc/pgpool-II/pool_passwd'# - Watchdog Configurationuse_watchdog = ondelegate_IP = '10.10.3.100'if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:1'if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'hostname0 = '10.10.3.11'wd_port0 = 9000pgpool_port0 = 9999hostname1 = '10.10.3.12'wd_port1 = 9000pgpool_port1 = 9999hostname2 = '10.10.3.13'wd_port2 = 9000pgpool_port2 = 9999wd_lifecheck_method = 'heartbeat'wd_interval = 10heartbeat_hostname0 = '10.10.3.11'heartbeat_port0 = 9694heartbeat_device0 = ''heartbeat_hostname1 = '10.10.3.12'heartbeat_port1 = 9694heartbeat_device1 = ''heartbeat_hostname2 = '10.10.3.13'heartbeat_port2 = 9694heartbeat_device2 = ''wd_heartbeat_keepalive = 2wd_heartbeat_deadtime = 30wd_escalation_command = '/etc/pgpool-II/escalation.sh' # - Where to log -log_destination = 'stderr'logging_collector = onlog_directory = '/var/log/pgpool-II'log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'log_truncate_on_rotation = onlog_rotation_age = 1dlog_rotation_size = 10MBpid_file_name = '/var/run/postgresql/pgpool.pid'#------------------------------------------------------------------------------# LOAD BALANCING MODE#------------------------------------------------------------------------------load_balance_mode = onEOF##修改配置cp -p /etc/pgpool-II/failover.sh{.sample,}cp -p /etc/pgpool-II/follow_primary.sh{.sample,}chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}chmod +x/etc/pgpool-II/{failover.sh,follow_primary.sh}## Pgpool-II在线恢复配置##**把recovery_First_stage和pgpool_remote_start脚本放在pgpool安装目录上**cp -p /etc/pgpool-II/recovery_1st_stage.sample /u01/apps/pgsql15/data/recovery_1st_stagecp -p /etc/pgpool-II/pgpool_remote_start.sample /u01/apps/pgsql15/data/pgpool_remote_startchown postgres:postgres /u01/apps/pgsql15/data/{recovery_1st_stage,pgpool_remote_start}chmod +x/u01/apps/pgsql15/data/{recovery_1st_stage,pgpool_remote_start}#注意脚本recovery_1st_stage和pgpool_remote_start中的PGHOME是否配置正确。脚本follow_primary.sh中的PCP_USER为pgpool。#为了使用在线恢复功能，需要pgpool_recovery、pgpool_remote_start、pgpool_switch_xlog等功能，因此我们需要在PostgreSQL server server1的template1上安装pgpool_recovery。# 创建pg插件su - postgrespsql template1 -c "CREATE EXTENSION pgpool_recovery"psql postgres -c "CREATE EXTENSION pgpool_recovery"## 修改 pool_hba.conf文件echo "host all all 0.0.0.0/0 md5" >> /etc/pgpool-II/pool_hba.conf## 配置pool_passwd-- cat/etc/pgpool-II/pool_passwdpg_md5 --md5auth --username=pgpool "postgres123"pg_md5 --md5auth --username=postgres "postgres123"## 看门狗cat > /etc/pgpool-II/escalation.sh <<"EOF"#!/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.set -o xtracePGPOOLS=(10.10.3.11 10.10.3.1210.10.3.13)VIP=10.10.3.100DEVICE=eth0for pgpool in "${PGPOOLS[@]}"; do[ "$HOSTNAME" = "$pgpool" ] && continuessh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$pgpool -i ~/.ssh/id_rsa_pgpool "/usr/bin/sudo /sbin/ip addr del $VIP/24 dev $DEVICE"doneexit 0EOFchown postgres:postgres /etc/pgpool-II/escalation.shchmod +x/etc/pgpool-II/*.shchown postgres:postgres /etc/pgpool-II/*.conf

参数分析：
故障转移参数：

# - Failover configurationfailover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

pgpool在线恢复功能：
为了使用Pgpool II执行在线恢复，我们指定PostgreSQL用户名和在线恢复命令recovery_1st_stage。因为执行在线恢复需要PostgreSQL中的超级用户权限，所以我们在recovery_user中指定postgres_user。然后，我们在PostgreSQL主服务器（server1）的数据库集群目录中创建recovery_1st_stage和pgpool_remote_start，并添加执行权限

# - Online Recovery Configurationsrecovery_user = 'postgres'recovery_password = 'postgres123'recovery_1st_stage_command = 'recovery_1st_stage'

配置PostgreSQL主备库

1. 启动Pgpool-II

systemctl start pgpool.servicesystemctl status pgpool.service

节点1已经有VIP了。

2. 3个节点都启动pgpool

systemctl start pgpool.servicesystemctl status pgpool.service

3. 利用pgpool的在线恢复功能配置数据库主从

确保pcp_recovery_node命令使用的recovery_1st_stage和pgpool_remote_start脚本位于PostgreSQL主服务器（server1）的数据库群集目录中。

[postgres@lhrpg30 data]$ pcp_recovery_node -h 10.10.3.12 -p 9898 -U pgpool -n 1Password: pcp_recovery_node -- Command Successful[postgres@lhrpg30 data]$ pcp_recovery_node -h 172.72.6.30 -p 9898 -U pgpool -n 2Password: pcp_recovery_node -- Command Successful

验证节点2和节点3是否作为PostgreSQL备用服务器启动

4. 配置从的服务器

scp -p /etc/pgpool-II/pgpool.conf root@10.10.3.12:/etc/pgpool-II/pgpool.conf
scp -p /etc/pgpool-II/pgpool.conf root@10.10.3.13:/etc/pgpool-II/pgpool.conf

scp -p /etc/pgpool-II/pool_passwd root@10.10.3.12:/etc/pgpool-II/pool_passwd
scp -p /etc/pgpool-II/pool_passwd root@10.10.3.13:/etc/pgpool-II/pool_passwd

scp -p /etc/pgpool-II/pcp.conf root@10.10.3.12:/etc/pgpool-II/pcp.conf
scp -p /etc/pgpool-II/pcp.conf root@10.10.3.13:/etc/pgpool-II/pcp.conf

scp -p /etc/pgpool-II/pool_hba.conf root@10.10.3.12:/etc/pgpool-II/pool_hba.conf
scp -p /etc/pgpool-II/pool_hba.conf root@10.10.3.13:/etc/pgpool-II/pool_hba.conf

scp -p /etc/pgpool-II/escalation.sh.sample root@10.10.3.12:/etc/pgpool-II/escalation.sh.sample
scp -p /etc/pgpool-II/escalation.sh.sample root@10.10.3.13:/etc/pgpool-II/escalation.sh.sample
scp -p /etc/pgpool-II/failover.sh.sample root@10.10.3.12:/etc/pgpool-II/failover.sh.sample
scp -p /etc/pgpool-II/failover.sh.sample root@10.10.3.13:/etc/pgpool-II/failover.sh.sample
scp -p /etc/pgpool-II/follow_primary.sh.sample root@10.10.3.12:/etc/pgpool-II/follow_primary.sh.sample
scp -p /etc/pgpool-II/follow_primary.sh.sample root@10.10.3.13:/etc/pgpool-II/follow_primary.sh.sample

测试 切换active/standby watchdog

pcp_watchdog_info -h 10.10.3.100 -p 9898 -U pgpool

停止server1节点或者停止server1上的Pgpool-II服务，vip切换到其他服务器，在此启动
server1上的Pgpool-II服务， 该服务会以standby角色运行。

[root@pg13 pgpool-II]# pcp_watchdog_info -h 10.10.3.100 -p 9898 -U pgpoolPassword: 3 3 YES 10.10.3.13:9999 Linux pg13 10.10.3.1310.10.3.13:9999 Linux pg13 10.10.3.13 9999 9000 4 LEADER 0 MEMBER10.10.3.11:9999 Linux pg1 10.10.3.11 9999 9000 7 STANDBY 0 MEMBER10.10.3.12:9999 Linux pg12 10.10.3.12 9999 9000 7 STANDBY 0 MEMBER[root@pg13 pgpool-II]# systemctl stop pgpool[root@pg13 pgpool-II]# pcp_watchdog_info -h 10.10.3.100 -p 9898 -U pgpoolPassword: FATAL:authentication failed for user "pgpool"DETAIL:username and/or password does not match[root@pg13 pgpool-II]# systemctl start pgpool[root@pg13 pgpool-II]# pcp_watchdog_info -h 10.10.3.100 -p 9898 -U pgpoolPassword: 3 3 YES 10.10.3.12:9999 Linux pg12 10.10.3.1210.10.3.12:9999 Linux pg12 10.10.3.12 9999 9000 4 LEADER 0 MEMBER10.10.3.11:9999 Linux pg1 10.10.3.11 9999 9000 7 STANDBY 0 MEMBER10.10.3.13:9999 Linux pg13 10.10.3.13 9999 9000 7 STANDBY 0 MEMBER

root@pg12 pgpool-II]# psql -h 10.10.3.100 -p 9999 -U pgpool postgres -c "show pool_nodes"用户 pgpool 的口令： node_id |hostname| port | status | pg_status | lb_weight |role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change---------+------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | 10.10.3.11 | 5432 | up | up| 0.333333| primary | primary | 0| false | 0 | || 2023-03-14 08:44:33 1 | 10.10.3.12 | 5432 | up | up| 0.333333| standby | standby | 0| true| 0 | || 2023-03-14 08:44:33 2 | 10.10.3.13 | 5432 | up | up| 0.333333| standby | standby | 0| false | 0 | || 2023-03-14 08:44:33(3 行记录)