repmgr+PostgreSQL故障自动转移


创建dockerfile

# 使用 CentOS 7 作为基础镜像
FROM centos:7

# 设置环境变量
ENV PG_MODE=primary \
 PG_USER=postgres \
 PG_PASSWORD=postgres \
 NODE_ROLE=master \
 MASTER_NAME=master \
 MASTER_PORT=5432 \
 RE_USER=repmgr \
 NODE_ID=1 \
 #NET_SEGMENT=192.168.0 \
 NODE_NAME=master1 \
 PG_DATADIR=/home/postgres/pgdata \
 PG_REPMGR_CONF=/home/postgres/repmgr.conf \
 PG_BINDIR=/usr/pgsql-12/bin \
 PG_CONFIGDIR=/home/postgres/pgdata/postgresql.conf \
 PRIORITY=1 \
 CONNINFO_HOST=master \
 CONNINFO_PORT=5432 \
 PATH="/usr/pgsql-12/bin:${PATH}"

# 创建postgres用户和组
RUN groupadd -r postgres \
 && useradd -r -g postgres postgres

# 安装所需的软件和工具
RUN yum -y install epel-release \
 && yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm \
 && yum -y install postgresql12 postgresql12-server postgresql12-contrib repmgr12 \
 && yum -y install which sudo iproute hostname \
 && yum -y clean all

# 创建脚本存放目录
RUN mkdir -p /home/runtime/ \
 && chown -R postgres:postgres /home/runtime/

# 将 functions 文件和 entrypoint.sh 添加到镜像中
#COPY functions /home/postgres/runtime/functions
COPY functions /home/runtime/functions
#COPY entrypoint.sh /home/postgres/runtime/entrypoint.sh
COPY entrypoint.sh /home/runtime/entrypoint.sh

# 修改文件权限与属性
RUN mkdir -p /home/postgres/pgdata \
 && chown -R postgres:postgres /home/postgres \
 #&& chown -R postgres:postgres /home/postgres/pgdata \
 #&& chown 777 /home/postgres/pgdata \
 #&& chmod +x /home/postgres/runtime/entrypoint.sh \
 && chmod +x /home/runtime/entrypoint.sh \
 #&& chmod +x /home/postgres/runtime/functions
 && chmod +x /home/runtime/functions 
 #&& usermod -a -G root postgres \
 #&& chmod 770 /home/postgres

RUN echo 'postgres ALL=(ALL) NOPASSWD: /bin/chown' >> /etc/sudoers

# 切换到 postgres 用户
USER postgres

# 初始化 PostgreSQL 数据目录
#RUN initdb -D ${PG_DATADIR} -U${PG_USER}

# 暴露 PostgreSQL 端口
EXPOSE 5432

# 设置启动脚本
#ENTRYPOINT ["/home/postgres/runtime/entrypoint.sh"]
ENTRYPOINT ["/home/runtime/entrypoint.sh"]

打包成镜像

docker run build -t centos7-pgsql-repmgr:v1.0

docker-compose部署
master节点的dockercompose.yaml

version: "3"

services:
 pg-master:
 image: centos7-pgsql-repmgr:v1.9.27
 hostname: master1
 container_name: pg-master
 environment:
 PG_MODE: primary
 PG_USER: postgres
 PG_PASSWORD: postgres
 NODE_ROLE: master
 NODE_ID: 1
 NODE_NAME: master1
 #MASTER_NAME: 172.18.41.8
 #MASTER_PORT: 25432
 CONNINFO_HOST: 172.18.41.8
 CONNINFO_PORT: 25432
 PRIORITY: 10
 POSTGRES_DB: repmgr
 ports:
 - "25432:5432"
 volumes:
 - ./pg-data:/home/postgres

slave节点的dockercompose.yaml
version: "3"

services:
 pg-slave:
 image: centos7-pgsql-repmgr:v1.9.27
 container_name: pg-salve
 hostname: salve1
 #network_mode: host
 environment:
 PG_MODE: salve
 PG_USER: postgres
 PG_PASSWORD: postgres
 PGPASSWORD: postgres
 RE_USER: repmgr
 NODE_ROLE: slave
 MASTER_NAME: 172.18.41.8
 MASTER_PORT: 25432
 NODE_ID: 2
 NODE_NAME: salve1
 CONNINFO_HOST: 172.18.41.2
 CONNINFO_PORT: 25432
 PRIORITY: 15
 POSTGRES_DB: repmgr
 ports:
 - "25432:5432"
 volumes:
 - ./pg-data:/home/postgres
~ 

master故障修复后已salve启动的dockercompose.yaml
version: "3"

services:
 pg-slave:
 image: centos7-pgsql-repmgr:v1.9.27
 hostname: salve1
 container_name: pg-salve
 environment:
 MASTER_NAME: 172.18.41.8
 PGPASSWORD: postgres
 MASTER_PORT: 25432
 CONNINFO_HOST: 172.18.41.2
 CONNINFO_PORT: 25432
 NODE_ID: 1
 PRIORITY: 15
 POSTGRES_DB: repmgr
 ports:
 - "25432:5432"
 volumes:
 - ./pg-data:/home/postgres

注：恢复前请做好数据备份，防止数据丢失
附：functions脚本函数
#!/bin/bash

# 容器调用入口函数，根据传入命令不同，执行注册或启动主库、注册或启动备库，从新加入集群的操作。
configure_repmgr()
{
 case ${NODE_ROLE} in
 master)
 echo 'master'
 initialize_master
 ;;
 slave)
 echo 'slave'
 initialize_slave
 ;;
 # rejoin)
 # echo 'rejoin'
 # rejoin_node
 # ;;
 master_slave)
 echo 'master_slave'
 master_slave
 ;;
 esac
}

# 用于设置 PostgreSQL 配置文件 'postgresql.conf' 中的参数。
# 该函数接受两个参数：参数名 (param_name) 和参数值 (param_value)，
# 并使用 'sed' 命令在 'postgresql.conf' 文件中查找以参数名开头的行
# （行前可能有一个 # 注释符号），然后用新的参数值替换该行。

set_postgresql_param() {
 param_name="$1"
 param_value="$2"
 sed -i "/^#${param_name} =/c ${param_name} = ${param_value}" ${PG_DATADIR}/postgresql.conf
}

# 这个函数用来向 PostgreSQL 的 pg_hba.conf 文件中添加一行新的配置
# ${1}：你想要添加的配置行
# ${PG_DATADIR}：pg_hba.conf 文件的位置
set_hba_param() {
 # 判断是否传入了配置行
 if [ -z "${1}" ]; then
 echo "No configuration line provided."
 return 1
 fi

 # 检查 pg_hba.conf 文件是否存在
 if [ ! -e "${PG_DATADIR}/pg_hba.conf" ]; then
 echo "${PG_DATADIR}/pg_hba.conf does not exist."
 return 1
 fi

 # 检查是否已经拥有写权限，如果没有则尝试获取
 if [ ! -w "${PG_DATADIR}/pg_hba.conf" ]; then
 chmod u+w ${PG_DATADIR}/pg_hba.conf || {
 echo "Could not set write permissions on ${PG_DATADIR}/pg_hba.conf"
 return 1
 }
 fi

 # 添加配置行到 pg_hba.conf 文件
 echo "${1}" >> ${PG_DATADIR}/pg_hba.conf || {
 echo "Could not write to ${PG_DATADIR}/pg_hba.conf"
 return 1
 }

 return 0
}

master_slave() {
 # 检查pgdata-bak是否存在，如果存在则警告并直接启动pgsql
 if [ -d "${PG_DATADIR}-bak" ]; then
 echo "Warning: ${PG_DATADIR}-bak already exists. Starting pgsql directly."
 pg_ctl -D $PG_DATADIR start
 else
 #export PGPASSFILE=~/.pgpass
 #write_pgpass
 IP=`ping ${MASTER_NAME} -c 1 -w 1 | sed '1{s/[^(]*(//;s/).*//;q}'`
 # 1. 备份pgsql数据
 mv $PG_DATADIR ${PG_DATADIR}-bak

 # 2. 创建pgsql数据目录
 mkdir $PG_DATADIR

 # 3. 从现在的主库获取备份数据
 #repmgr -f $PG_REPMGR_CONF -h ${MASTER_NAME} -U repmgr -d repmgr -D $PG_DATADIR standby clone
 echo "repmgr -h ${IP} -p ${MASTER_PORT} -U repmgr -d repmgr -f ${PG_REPMGR_CONF} standby clone --dry-run"
 repmgr -h ${IP} -p ${MASTER_PORT} -U repmgr -d repmgr -f ${PG_REPMGR_CONF} standby clone --dry-run
 echo "repmgr -h ${IP} -p ${MASTER_PORT} -U repmgr -d repmgr -f ${PG_REPMGR_CONF} standby clone"
 repmgr -h ${IP} -p ${MASTER_PORT} -U repmgr -d repmgr -f ${PG_REPMGR_CONF} standby clone

 # 4. 启动pgsql
 echo "pg_ctl -D $PG_DATADIR start"
 pg_ctl -D $PG_DATADIR start

 # 5. 重新注册节点
 echo "repmgr -f $PG_REPMGR_CONF standby register --force"
 repmgr -f $PG_REPMGR_CONF standby register --force
 fi
 echo "repmgrd -f ${PG_REPMGR_CONF} --pid-file /tmp/repmgrd.pid --daemonize=false"
 repmgrd -f ${PG_REPMGR_CONF} --pid-file /tmp/repmgrd.pid --daemonize=false
}

# 注册和执行主库操作，包括修改配置文件、创建用户、数据库及插件等，并设置守护进程用于自动故障转移。
initialize_master()
{
 if [[ ! -f ${PG_DATADIR}/PG_VERSION ]]; then
 initdb -D /home/${PG_USER}/pgdata -U${PG_USER}
 write_postgresql_config
 write_pg_hba_conf
 write_pgpass
 pg_ctl -D /home/${PG_USER}/pgdata -w start >/dev/null
 psql -U ${PG_USER} -d postgres -h localhost -c "ALTER USER ${PG_USER} WITH PASSWORD '${PG_PASSWORD}';" >/dev/null
 psql -U ${PG_USER} -d postgres -h localhost -c "create database repmgr;" >/dev/null
 psql -U ${PG_USER} -d postgres -h localhost -c "create extension repmgr;" >/dev/null
 psql -U ${PG_USER} -d repmgr -h localhost -c "create user repmgr with superuser;" >/dev/null
 psql -U ${PG_USER} -d repmgr -h localhost -c "alter user repmgr password '${PG_PASSWORD}';" >/dev/null
 write_repmgr_conf
 repmgr -f ${PG_REPMGR_CONF} primary register
 else
 pg_ctl -D /home/${PG_USER}/pgdata -w start >/dev/null
 fi
 repmgrd -f ${PG_REPMGR_CONF} --pid-file /tmp/repmgrd.pid --daemonize=false
}

# 注册和执行备库操作，克隆主库，加入repmgr集群，并设置守护进程用于自动故障转移。
initialize_slave()
{
 if [[ ! -f ${PG_DATADIR}/PG_VERSION ]]; then
 write_repmgr_conf
 write_pgpass
 IP=`ping ${MASTER_NAME} -c 1 -w 1 | sed '1{s/[^(]*(//;s/).*//;q}'`
 repmgr -h ${IP} -p ${MASTER_PORT} -U repmgr -d repmgr -f ${PG_REPMGR_CONF} standby clone --dry-run
 repmgr -h ${IP} -p ${MASTER_PORT} -U repmgr -d repmgr -f ${PG_REPMGR_CONF} standby clone
 pg_ctl -D ${PG_DATADIR} -w start >/dev/null
 repmgr -f ${PG_REPMGR_CONF} standby register 
 repmgrd -f ${PG_REPMGR_CONF} --pid-file /tmp/repmgrd.pid --daemonize=false
 else
 pg_ctl -D ${PG_DATADIR} -w start >/dev/null
 repmgrd -f ${PG_REPMGR_CONF} --pid-file /tmp/repmgrd.pid --daemonize=false
 fi
}

# 将已有节点重新加入到集群，启动守护进行用于自动切换。
rejoin_node()
{
 if [[ -f ${PG_DATADIR}/PG_VERSION ]]; then
 IP=`ping ${MASTER_NAME} -c 1 -w 1 | sed '1{s/[^(]*(//;s/).*//;q}'`
 if [[ -d /home/${PG_USER}/pgdata-bak ]];then
 rm -fr /home/${PG_USER}/pgdata-bak
 fi
 cp -a /home/${PG_USER}/pgdata /home/${PG_USER}/pgdata-bak
 rm -fr /home/postgres/pgdata/postmaster.pid
 # pg_resetwal -f /home/${PG_USER}/pgdata
 repmgr node rejoin -d "host=${IP} dbname=repmgr user=repmgr" --force-rewind --config-files="postgresql.conf,postgresql.auto.conf" -f ${PG_REPMGR_CONF} --verbose --dry-run
 repmgr node rejoin -d "host=${IP} dbname=repmgr user=repmgr" --force-rewind --config-files="postgresql.conf,postgresql.auto.conf" -f ${PG_REPMGR_CONF} --verbose
 fi
}

# 修改postgresql.conf文件
write_postgresql_config()
{
 set_postgresql_param "wal_log_hints" "on"
 set_postgresql_param "archive_mode" "on"
 set_postgresql_param "archive_command" "\'test ! -f /home/${PG_USER}/pgarch/%f && cp %p /home/${PG_USER}/pgarch/%f\'"
 set_postgresql_param "wal_level" "hot_standby"
 set_postgresql_param "listen_addresses" "\'*\'"
 set_postgresql_param "hot_standby" "on"
 set_postgresql_param "max_wal_senders" "10"
 set_postgresql_param "wal_keep_segments" "10"
 set_postgresql_param "port" "${PG_PORT:-5432}"
 set_postgresql_param "max_connections " "100"
 set_postgresql_param "superuser_reserved_connections" "10"
 set_postgresql_param "full_page_writes" "on"
 set_postgresql_param "max_replication_slots" "10"
 set_postgresql_param "synchronous_commit" "on"
 set_postgresql_param "shared_preload_libraries" "repmgr"
 set_postgresql_param "log_destination" "csvlog"
 set_postgresql_param "logging_collector" "on"
 set_postgresql_param "log_directory" "on"
 set_postgresql_param "log_filename" "postgresql-%Y-%m-%d_%H%M%S"
 set_postgresql_param "log_rotation_age" "1d"
 set_postgresql_param "log_rotation_size" "10MB"
 set_postgresql_param "log_statement" "mod"
 #set_postgresql_param "data_directory" "/home/pgsqlData"
}

# 修改repmgr.conf文件
write_repmgr_conf()
{
 echo "node_id=${NODE_ID}" > ${PG_REPMGR_CONF}
 echo "node_name='${NODE_NAME}'" >> ${PG_REPMGR_CONF}
 #echo "conninfo='host=${CONNINFO_HOST} user=repmgr dbname=repmgr connect_timeout=2'" >> ${PG_REPMGR_CONF}
 echo "conninfo='host=${CONNINFO_HOST} port=${CONNINFO_PORT} user=repmgr dbname=repmgr connect_timeout=2'" >> ${PG_REPMGR_CONF}
 echo "data_directory='${PG_DATADIR}'" >> ${PG_REPMGR_CONF}
 echo "config_directory='${PG_CONFIGDIR}'" >> ${PG_REPMGR_CONF}
 echo "use_replication_slots=true" >> ${PG_REPMGR_CONF}
 echo "reconnect_attempts=4" >> ${PG_REPMGR_CONF}
 echo "reconnect_interval=5" >> ${PG_REPMGR_CONF}
 echo "monitor_interval_secs=2" >> ${PG_REPMGR_CONF}
 echo "retry_promote_interval_secs=300" >> ${PG_REPMGR_CONF}
 echo "pg_bindir='${PG_BINDIR}'" >> ${PG_REPMGR_CONF}
 echo "log_level='INFO'" >> ${PG_REPMGR_CONF}
 echo "log_status_interval=300" >> ${PG_REPMGR_CONF}
 echo "log_facility='STDERR'" >> ${PG_REPMGR_CONF}
 #echo "event_notification_command='${PG_EVENT_NOTIFICATION_SCRIPT}'" >> ${PG_REPMGR_CONF}
 echo "promote_command='repmgr standby promote -f ${PG_REPMGR_CONF}'" >> ${PG_REPMGR_CONF}
 echo "follow_command='repmgr standby follow -f ${PG_REPMGR_CONF} -W --log-to-file'" >> ${PG_REPMGR_CONF}
 echo "failover='automatic'" >> ${PG_REPMGR_CONF}
 echo "priority=${PRIORITY}" >> ${PG_REPMGR_CONF}
 echo "degraded_monitoring_timeout=-1" >> ${PG_REPMGR_CONF}
}

# 修改pg_hba.conf文件
write_pg_hba_conf()
{
 set_hba_param " local replication ${PG_USER} trust "
 set_hba_param " host replication ${PG_USER} 127.0.0.1/32 trust "
 set_hba_param " local repmgr ${PG_USER} trust "
 set_hba_param " host repmgr ${PG_USER} 127.0.0.1/32 trust "
 #set_hba_param " host replication ${PG_USER} ${NET_SEGMENT}/24 md5 "
 #set_hba_param " host repmgr ${PG_USER} ${NET_SEGMENT}/24 md5 "
 #set_hba_param " host repmgr repmgr ${NET_SEGMENT}/24 md5 "
 set_hba_param " host replication ${RE_USER} 0.0.0.0/0 md5 "
 set_hba_param " host repmgr ${PG_USER} 0.0.0.0/0 md5 "
 set_hba_param " host repmgr repmgr 0.0.0.0/0 md5 "
 set_hba_param " host all all 0.0.0.0/0 md5 "
}

# 修改.pgpass文件
write_pgpass()
{
 if [ -f ~/.pgpass ]
 then
 rm -f ~/.pgpass
 fi
 echo "*:*:*:${PG_USER}:${PG_PASSWORD}" >> ~/.pgpass
 echo "*:*:repmgr:repmgr:${PG_PASSWORD}" >> ~/.pgpass
 chmod 600 ~/.pgpass
}

附2：entrypoint.sh
#!/bin/bash
set -e

# shellcheck source=runtime/functions
#source "/home/postgres/runtime/functions"
source "/home/runtime/functions"
sudo chown -R postgres:postgres /home/postgres
if [ ! -d /home/postgres/pgarch/ ];then
 mkdir -p /home/postgres/pgarch/
fi

if [ -f /tmp/repmgrd.pid ];then
 rm -fr /tmp/repmgrd.pid
fi
#运行repmgr
configure_repmgr