Pgpool-II实现高可用+读写分离+负载均衡（五）---- follow_primary.sh分析

#!/bin/bashset-o xtrace
# Special values:# 1)  %d = standby节点ID# 2)  %h = 主机名# 3)  %p = 端口号# 4)  %D = PG_DATA目录# 5)  %m = 新主节点ID# 6)  %H = 新主节点主机名# 7)  %M = old main node id(未使用，和$8一样的含义)# 8)  %P = 旧主节点ID# 9)  %r = 新主节点端口号# 10) %R = 新主节点数据目录（PG_DATA）# 11) %N = 旧主节点主机名# 12) %S = 旧主节点端口号# 13) %% = '%' characterNODE_ID="$1"NODE_HOST="$2"NODE_PORT="$3"NODE_PGDATA="$4"NEW_PRIMARY_NODE_ID="$5"NEW_PRIMARY_NODE_HOST="$6"OLD_MAIN_NODE_ID="$7"OLD_PRIMARY_NODE_ID="$8"NEW_PRIMARY_NODE_PORT="$9"NEW_PRIMARY_NODE_PGDATA="${10}"# 以下参数根据自己的环境设置# pg安装目录PGHOME=/usr/local/pgsql16
# archive log目录ARCHIVEDIR=/data01/pgsql16/archive
# 复制用户名REPLUSER=replica
# PCP用户名PCP_USER=pgpool
# Pgpool bin目录PGPOOL_PATH=/home/postgres/pgpool/bin
PCP_PORT=9898# 复制槽名REPL_SLOT_NAME=${NODE_HOST//[-.]/_}# pg服务启动用户POSTGRESQL_STARTUP_USER=postgres
SSH_KEY_FILE=id_rsa
SSH_OPTIONS="-o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i ~/.ssh/${SSH_KEY_FILE}"echo follow_primary.sh: start: Standby node${NODE_ID}# 检查standby节点连接状态${PGHOME}/bin/pg_isready -h${NODE_HOST}-p${NODE_PORT} > /dev/null 2>&1
# 检查失败退出if [ $?-ne0 ]; thenecho follow_primary.sh: node_id=${NODE_ID} is not running. skipping follow primary command
exit0fi# 测试免密sshssh-T${SSH_OPTIONS}${POSTGRESQL_STARTUP_USER}@${NEW_PRIMARY_NODE_HOST}ls /tmp > /dev/null
# 测试失败退出if [ $?-ne0 ]; thenecho follow_main.sh: passwordless SSH to ${POSTGRESQL_STARTUP_USER}@${NEW_PRIMARY_NODE_HOST} failed. Please setup passwordless SSH.
exit1fi# 获取PostgreSQL主版本号PGVERSION=`${PGHOME}/bin/initdb -V | awk '{print $3}' | sed 's/\..*//' | sed 's/\([0-9]*\)[a-zA-Z].*/\1/'`# pg版本>=12创建myrecovery.conf，否则创建recovery.confif [ $PGVERSION-ge12 ]; thenRECOVERYCONF=${NODE_PGDATA}/myrecovery.conf
elseRECOVERYCONF=${NODE_PGDATA}/recovery.conf
fi# standby节点同新主节点同步数据echo follow_primary.sh: pg_rewind fornode${NODE_ID}# 运行pg_rewind前先在新主节点上执行checkpoint更新控制文件${PGHOME}/bin/psql -h${NEW_PRIMARY_NODE_HOST}-p${NEW_PRIMARY_NODE_PORT} postgres -c"checkpoint;"# 创建复制槽 "${REPL_SLOT_NAME}"${PGHOME}/bin/psql -h${NEW_PRIMARY_NODE_HOST}-p${NEW_PRIMARY_NODE_PORT} postgres \
-c"SELECT pg_create_physical_replication_slot('${REPL_SLOT_NAME}');"  >/dev/null 2>&1
if [ $?-ne0 ]; thenecho follow_primary.sh: create replication slot \"${REPL_SLOT_NAME}\" failed. You may need to create replication slot manually.
fi# ssh到从节点ssh-T${SSH_OPTIONS}${POSTGRESQL_STARTUP_USER}@${NODE_HOST}"    set -o errexit    # 停止PostgreSQL${PGHOME}/bin/pg_ctl -w -m f -D ${NODE_PGDATA} stop    # 执行pg_rewind${PGHOME}/bin/pg_rewind -D ${NODE_PGDATA} --source-server=\"user=${POSTGRESQL_STARTUP_USER} host=${NEW_PRIMARY_NODE_HOST} port=${NEW_PRIMARY_NODE_PORT} dbname=postgres\"    [ -d \"${NODE_PGDATA}\" ] && rm -rf ${NODE_PGDATA}/pg_replslot/*    # 生成流复制配置文件    cat > ${RECOVERYCONF} << EOTprimary_conninfo = 'host=${NEW_PRIMARY_NODE_HOST} port=${NEW_PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${NODE_HOST} passfile=''/home/postgres/.pgpass'''recovery_target_timeline = 'latest'restore_command = 'scp ${SSH_OPTIONS}${NEW_PRIMARY_NODE_HOST}:${ARCHIVEDIR}/%f %p'primary_slot_name = '${REPL_SLOT_NAME}'EOT    # 修改postgresql.conf文件加入include_if_exists指令以便pg启动时加载recovery配置    # include_if_exists = '/data01/pgsql16/db/myrecovery.conf'    if [ ${PGVERSION} -ge 12 ]; then        sed -i -e \"\\\$ainclude_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'\" \               -e \"/^include_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'/d\" ${NODE_PGDATA}/postgresql.conf        # 生成standby节点标志文件                       touch ${NODE_PGDATA}/standby.signal    else        echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}    fi    # 启动PostgreSQL${PGHOME}/bin/pg_ctl -l /dev/null -w -D ${NODE_PGDATA} start"# 如果pg_rewind失败，尝试pg_basebackupif [ $?-ne0 ]; thenecho follow_primary.sh: end: pg_rewind failed. Try pg_basebackup.
ssh-T${SSH_OPTIONS}${POSTGRESQL_STARTUP_USER}@${NODE_HOST}"        set -o errexit        # 删除原来的PG_DATA目录        [ -d \"${NODE_PGDATA}\" ] && rm -rf ${NODE_PGDATA}        # 删除原来的archive log目录        [ -d \"${ARCHIVEDIR}\" ] && rm -rf ${ARCHIVEDIR}/*${PGHOME}/bin/pg_basebackup -h ${NEW_PRIMARY_NODE_HOST} -U $REPLUSER -p ${NEW_PRIMARY_NODE_PORT} -D ${NODE_PGDATA} -X stream        # 生成配置文件        cat > ${RECOVERYCONF} << EOTprimary_conninfo = 'host=${NEW_PRIMARY_NODE_HOST} port=${NEW_PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${NODE_HOST} passfile=''/home/postgres/.pgpass'''recovery_target_timeline = 'latest'restore_command = 'scp ${SSH_OPTIONS}${NEW_PRIMARY_NODE_HOST}:${ARCHIVEDIR}/%f %p'primary_slot_name = '${REPL_SLOT_NAME}'EOT        # 修改postgresql.conf文件加入include_if_exists指令以便pg启动时加载recovery配置        # include_if_exists = '/data01/pgsql16/db/myrecovery.conf'        if [ ${PGVERSION} -ge 12 ]; then            sed -i -e \"\\\$ainclude_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'\" \                   -e \"/^include_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'/d\" ${NODE_PGDATA}/postgresql.conf            touch ${NODE_PGDATA}/standby.signal        else            echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}        fi        sed -i \            -e \"s/#*port = .*/port = ${NODE_PORT}/\" \            -e \"s@#*archive_command = .*@archive_command = 'cp \\\"%p\\\" \\\"${ARCHIVEDIR}/%f\\\"'@\" \${NODE_PGDATA}/postgresql.conf    "# 检查pg_basebackup是否成功if [ $?-ne0 ]; then# 失败处理# 删除失效的复制槽${PGHOME}/bin/psql -h${NEW_PRIMARY_NODE_HOST}-p${NEW_PRIMARY_NODE_PORT} postgres \
-c"SELECT pg_drop_replication_slot('${REPL_SLOT_NAME}');"  >/dev/null 2>&1
if [ $?-ne0 ]; thenecho ERROR: follow_primary.sh: drop replication slot \"${REPL_SLOT_NAME}\" failed. You may need to drop replication slot manually.
fi# 提示失败并退出echo follow_primary.sh: end: pg_basebackup failed
exit1fi# 启动新的standby节点 - ${NODE_HOST}ssh-T${SSH_OPTIONS}${POSTGRESQL_STARTUP_USER}@${NODE_HOST}$PGHOME/bin/pg_ctl -l /dev/null -w-D${NODE_PGDATA}startfi# 如果启动成功，调用pcp_attach_node将新的standby节点加入到Pgpool集群if [ $?-eq0 ]; then# 调用pcp_attach_node将新的standby节点加入到Pgpool集群${PGPOOL_PATH}/pcp_attach_node -w-h localhost -U$PCP_USER-p${PCP_PORT}-n${NODE_ID}if [ $?-ne0 ]; thenecho ERROR: follow_primary.sh: end: pcp_attach_node failed
exit1fielse# 启动失败处理# 删除复制槽 "${REPL_SLOT_NAME}"${PGHOME}/bin/psql -h${NEW_PRIMARY_NODE_HOST}-p${NEW_PRIMARY_NODE_PORT} postgres \
-c"SELECT pg_drop_replication_slot('${REPL_SLOT_NAME}');"  >/dev/null 2>&1
if [ $?-ne0 ]; thenecho ERROR: follow_primary.sh: drop replication slot \"${REPL_SLOT_NAME}\" failed. You may need to drop replication slot manually.
fi# 报错并退出echo ERROR: follow_primary.sh: end: follow primary command failed
exit1fiecho follow_primary.sh: end: follow primary command is completed successfully
exit0

primary_conninfo ='host=db02 port=5432 user=replica application_name=db01 passfile=''/home/postgres/.pgpass'''recovery_target_timeline ='latest'restore_command ='scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i ~/.ssh/id_rsa zsy-db02:/data01/pgsql14/archive/%f %p'primary_slot_name ='db01'

include_if_exists ='/data01/pgsql16/db/myrecovery.conf'