基于repmgr的PG高可用方案

1.PostgreSQL部署1.1 安装前准备 关闭防火墙和selinuxsystemctl stop firewalldsystemctl disable firewalld sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config 1.2 创建用户和目录groupadd pg14useradd -g pg14 pg14echo 123| passwd --stdin pg14 软件安装路径 /home/pg14/soft数据目录     /home/pg14/datasu - pg14mkdir -p /home/pg14/softmkdir  -p  /home/pg14/data 1.3解压安装包  tar -zxf postgresql-14.6.tar.gz 1.4安装依赖包yum install -y gccyum install -y makeyum install -y readline*yum install -y zlib-develyum install -y libxml2-develyum install -y libxslt-develyum install -y openssl-devel1.5编译 ./configure --prefix=/home/pg14/soft --with-pgport=5666 --with-openssl 1.6安装 make world -j16make install-world -j161.7 配置环境变量 vi .bashrc  添加export PGHOME=/home/pg14/softexport PGDATA=/home/pg14/dataexport LD_LIBRARY_PATH=${PGHOME}/lib:${LD_LIBRARY_PATH}export PATH=${PGHOME}/bin:${PATH}source .bashrc 1.8初始化集簇  启用checksum  超级用户是postgres initdb -D /home/pg14/data -k -U postgres -W 1.9启动数据库pg_ctl start -D /home/pg14/data -l /tmp/logfile 1.2repmgr安装1.2.1 主机互信配置./sshusersetup.sh1.2.2上传repmgr安装包、解压、安装 yum  check-updateyum groupinstall "Development Tools" -yyum install yum-utils openjade docbook-dtds docbook-style-dsssl docbook-style-xsl -yyum-builddep postgresql96  tar -zxf repmgr-5.3.3.tar.gz./configuremake && make install 1.2.3创建复制账号 postgres=# create user repmgr with superuser password '1qaz@WSX';CREATE ROLEpostgres=# create database repmgrdb with owner=repmgr;CREATE DATABASEpostgres=# 1.2.4 参数配置 postgresql.auto.confmax_wal_senders=10max_replication_slots=10wal_level='logical'hot_standby=onarchive_mode=onwal_keep_size = '128'archive_command='cp %p /home/pg14/arch/%f' pg_hba_conf IPV4 一栏和replication 一栏分别添加下面两行[pg14@node1 data]$ egrep repmgr $PGDATA/pg_hba.confhost    repmgrdb        repmgr          192.168.18.0/24         trusthost    replication     repmgr          192.168.18.0/24         trust  1.2.5创建repmgr扩展psql -U repmgr -d repmgrdbrepmgrdb=# create extension repmgr;CREATE EXTENSION 1.2.6 repmgr配置文件 mkdir -p /home/pg14/confcd /home/pg14/conf node1:vi repmgr.conf[pg14@node1 conf]$ vi repmgr.conf#[base]node_id=1node_name=node1conninfo ='host=node1 dbname=repmgrdb user=repmgr connect_timeout=2 password=1qaz@WSX'data_directory='/home/pg14/data'#[optional]#[log]log_level=infolog_facility=stderrlog_file='/home/pg14/conf/repmgr.log'log_status_interval=300 node2#[base]node_id=2node_name=node2conninfo ='host=node2 dbname=repmgrdb user=repmgr connect_timeout=2 password=1qaz@WSX'data_directory='/home/pg14/data'#[optional]#[log]log_level=infolog_facility=stderrlog_file='/home/pg14/conf/repmgr.log'log_status_interval=300 1.3 主从数据初始化1.3.1 主节点注册repmgr -f /home/pg14/conf/repmgr.conf primary register1.3.2 备库克隆repmgr -h node1 -U repmgr -d repmgrdb -f /home/pg14/conf/repmgr.conf standby clone1.3.3从节点注册repmgr -f ~/conf/repmgr.conf standby register1.4 故障自动切换配置1.4.1 配置操作系统服务vi /usr/lib/systemd/system/postgresql.service [Unit]Description=PostgreSQL database serverAfter=network.target [Service]Type=forkingUser=pg14Group=pg14Environment=PGPORT=5666Environment=PGDATA=/home/pg14/dataEnvironment=PGLOG=/tmp/logfileOOMScoreAdjust=-1000ExecStart=/home/pg14/soft/bin/pg_ctl start -D ${PGDATA} -l ${PGLOG}ExecStop=/home/pg14/soft/bin/pg_ctl stop -D ${PGDATA} -l ${PGLOG}ExecRestart=/home/pg14/soft/bin/pg_ctl restart -D ${PGDATA} -l ${PGLOG}ExecReload=/home/pg14/soft/bin/pg_ctl reload -D ${PGDATA} -l ${PGLOG}TimeoutSec=300[Install]WantedBy=multi-user.target 1.4.2 数据库账号赋权vi /etc/sudoers# Refuse to run if unable to disable echo on the tty.##Defaults   !visiblepwDefaults    !requiretty ## Allow root to run any commands anywhereroot    ALL=(ALL)       ALLpg14    ALL=(ALL)       NOPASSWD:/bin/systemctl stop postgresql,\/bin/systemctl start postgresql,\/bin/systemctl restart postgresql,\/bin/systemctl reload postgresql 1.4.3 PostgreSQL参数添加vi /home/pg14/conf/repmgr.conf#[service]service_start_command='sudo systemctl start postgresql'service_stop_command='sudo systemctl stop postgresql'service_restart_command='sudo systemctl restart postgresql'service_reload_command='sudo systemctl relaod postgresql' 1.4.4 repmgr配置文件添加repmgr 配置文件添加 #[auto-failover] failover=automaticpromote_command='/home/pg14/soft/bin/repmgr standby promote -f /home/pg14/conf/repmgr.conf --log-to-file'follow_command='/home/pg14/soft/bin/repmgr standby follow  -f /home/pg14/conf/repmgr.conf --log-to-file upstream-node-id=%n' #[monitor]monitoring_history=yesmonitor_interval_secs=1reconnect_attempts=6reconnect_interval=1primary_notification_timeout=3repmgrd_standby_startup_timeout=1sibling_nodes_disconnect_timeout=12.5.5 repmgr 守护进程启动 repmgrd -f /home/pg14/conf/repmgr.conf -v -d  -p ~/conf/repmgrd.pid  2高可用测试 手动切换测试[pg14@node2 data]$ repmgr -f /home/pg14/conf/repmgr.conf standby switchoverNOTICE: executing switchover on node "node2" (ID: 2)NOTICE: attempting to pause repmgrd on 2 nodesNOTICE: local node "node2" (ID: 2) will be promoted to primary; current primary "node1" (ID: 1) will be demoted to standbyNOTICE: stopping current primary node "node1" (ID: 1)NOTICE: issuing CHECKPOINT on node "node1" (ID: 1)DETAIL: executing server command "sudo systemctl stop postgresql"INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")NOTICE: current primary has been cleanly shut down at location 0/15000028NOTICE: promoting standby to primaryDETAIL: promoting server "node2" (ID: 2) using pg_promote()NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to completeNOTICE: STANDBY PROMOTE successfulDETAIL: server "node2" (ID: 2) was successfully promoted to primaryNOTICE: node "node2" (ID: 2) promoted to primary, node "node1" (ID: 1) demoted to standbyNOTICE: switchover was successfulDETAIL: node "node2" is now primary and node "node1" is attached as standbyNOTICE: STANDBY SWITCHOVER has completed successfully