安装部署postgresql-15 高可用（pgpool） 安装部署postgresql-15 高可用（pgpool） 文章目录 前言部署环境：下载地址：安装pg15安装pgpool配置pgpass文件创建pgpool_node_idPgpool-II参数配置 配置PostgreSQL主备库1. 启动Pgpool-II2. 3个节点都启动pgpool3. 利用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 directory Environment=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=5432 logging_collector = on log_truncate_on_rotation = on archive_mode = on archive_command = 'cp "%p" "/u01/apps/pgsql15/archivedir/%f"' max_wal_senders = 10 max_replication_slots = 10 wal_level = replica hot_standby = on wal_log_hints = on password_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 ~/.ssh ssh-keygen -t rsa -f id_rsa_pgpool ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11 ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12 ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.13 #postgres用户 su - postgres cd ~/.ssh ssh-keygen -t rsa -f id_rsa_pgpool ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11 ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12 ssh-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 postgres 394 3月 10 18:27 id_rsa_pgpool.pub -rw-r--r--. 1 postgres postgres 516 3月 3 11:03 known_hosts -bash-4.2$ ssh-keygen -t rsa -f id_rsa_pgpool Generating public/private rsa key pair. id_rsa_pgpool already exists. Overwrite (y/n)? y Enter passphrase (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@pg1 The 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 keys postgres@10.10.3.11's password: Number of key(s) added: 1 Now 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 keys postgres@10.10.3.12's password: Number of key(s) added: 1 Now 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 keys postgres@10.10.3.13's password: Number of key(s) added: 1 Now 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:postgres123 10.10.3.12:5432:replication:repl:postgres123 10.10.3.13:5432:replication:repl:postgres123 10.10.3.100:9999:postgres:pgpool:postgres123 10.10.3.100:9999:postgres:postgres:postgres123 10.10.3.11:5432:postgres:postgres:postgres123 10.10.3.12:5432:postgres:postgres:postgres123 10.10.3.13:5432:postgres:postgres:postgres123 EOF chown postgres:postgres /u01/apps/pgsql15/data/.pgpass chmod 600 /u01/apps/pgsql15/data/.pgpass echo 'localhost:9898:pgpool:postgres123' > /u01/apps/pgsql15/data/.pcppass chmod 600 /u01/apps/pgsql15/data/.pcppass chown postgres.postgres /u01/apps/pgsql15/data/.pcppass echo '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=9999 socket_dir = '/u01/apps/pgsql15/data/' pcp_listen_addresses = '*' pcp_port = 9898 pcp_socket_dir = '/u01/apps/pgsql15/data/' # - Streaming Replication Check sr_check_user = 'pgpool' sr_check_password = '' # - Health Check health_check_period = 5 health_check_timeout = 30 health_check_user = 'pgpool' health_check_password = 'postgres123' health_check_max_retries = 3 search_primary_node_timeout = 10min # - Backend Connection Settings - backend_hostname0 = '10.10.3.11' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/u01/apps/pgsql15/data' backend_flag0 = 'ALLOW_TO_FAILOVER' #backend_application_name0 = 'pg1' backend_hostname1 = '10.10.3.12' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/u01/apps/pgsql15/data' backend_flag1 = 'ALLOW_TO_FAILOVER' #backend_application_name0 = 'pg2' backend_hostname2 = '10.10.3.13' backend_port2 = 5432 backend_weight2 = 1 backend_data_directory2 = '/u01/apps/pgsql15/data' backend_flag2 = 'ALLOW_TO_FAILOVER' #backend_application_name0 = 'pg3' # - Failover configuration failover_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 Configurations recovery_user = 'postgres' recovery_password = 'postgres123' recovery_1st_stage_command = 'recovery_1st_stage' # - Client Authentication Configuration enable_pool_hba = on pool_passwd = '/etc/pgpool-II/pool_passwd' # - Watchdog Configuration use_watchdog = on delegate_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 = 9000 pgpool_port0 = 9999 hostname1 = '10.10.3.12' wd_port1 = 9000 pgpool_port1 = 9999 hostname2 = '10.10.3.13' wd_port2 = 9000 pgpool_port2 = 9999 wd_lifecheck_method = 'heartbeat' wd_interval = 10 heartbeat_hostname0 = '10.10.3.11' heartbeat_port0 = 9694 heartbeat_device0 = '' heartbeat_hostname1 = '10.10.3.12' heartbeat_port1 = 9694 heartbeat_device1 = '' heartbeat_hostname2 = '10.10.3.13' heartbeat_port2 = 9694 heartbeat_device2 = '' wd_heartbeat_keepalive = 2 wd_heartbeat_deadtime = 30 wd_escalation_command = '/etc/pgpool-II/escalation.sh' # - Where to log - log_destination = 'stderr' logging_collector = on log_directory = '/var/log/pgpool-II' log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 10MB pid_file_name = '/var/run/postgresql/pgpool.pid' #------------------------------------------------------------------------------ # LOAD BALANCING MODE #------------------------------------------------------------------------------ load_balance_mode = on EOF 修改配置 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_stage cp -p /etc/pgpool-II/pgpool_remote_start.sample /u01/apps/pgsql15/data/pgpool_remote_start chown 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 - postgres psql 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_passwd pg_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 xtrace PGPOOLS=(10.10.3.11 10.10.3.12 10.10.3.13) VIP=10.10.3.100 DEVICE=eth0 for pgpool in "${PGPOOLS[@]}"; do [ "$HOSTNAME" = "$pgpool" ] && continue ssh -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 " done exit 0 EOF chown postgres:postgres /etc/pgpool-II/escalation.sh chmod +x /etc/pgpool-II/*.sh chown postgres:postgres /etc/pgpool-II/*.conf 参数分析： 故障转移参数： # - Failover configuration failover_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 Configurations recovery_user = 'postgres' recovery_password = 'postgres123' recovery_1st_stage_command = 'recovery_1st_stage' 配置PostgreSQL主备库 1. 启动Pgpool-II systemctl start pgpool.service systemctl status pgpool.service 节点1已经有VIP了。 2. 3个节点都启动pgpool systemctl start pgpool.service systemctl 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 1 Password: pcp_recovery_node -- Command Successful [postgres@lhrpg30 data]$ pcp_recovery_node -h 172.72.6.30 -p 9898 -U pgpool -n 2 Password: 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 pgpool Password: 3 3 YES 10.10.3.13:9999 Linux pg13 10.10.3.13 10.10.3.13:9999 Linux pg13 10.10.3.13 9999 9000 4 LEADER 0 MEMBER 10.10.3.11:9999 Linux pg1 10.10.3.11 9999 9000 7 STANDBY 0 MEMBER 10.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 pgpool Password: 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 pgpool Password: 3 3 YES 10.10.3.12:9999 Linux pg12 10.10.3.12 10.10.3.12:9999 Linux pg12 10.10.3.12 9999 9000 4 LEADER 0 MEMBER 10.10.3.11:9999 Linux pg1 10.10.3.11 9999 9000 7 STANDBY 0 MEMBER 10.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 行记录)