部署PostgreSQL 12流复制集群(with Repmgr)

本文主要讲解如何构建一个基于主备架构(Master/Standby)的一主多从PostgreSQL Server流复制集群。

1.概述

1.1 流复制和热备

在构建PostgreSQL Server集群的过程中,由于底层复制技术采用了PostgreSQL提供的流复制(Streaming Replication)技术,我们构造的Standby Server具有HOT Standby的相关特性,可适用于需要“读写分离”的应用场景。

关于Hot Standby,PostgreSQL官方有如下说明:

Hot Standby is the term used to describe the ability to connect to the server and run read-only queries while the server is in archive recovery or standby mode. This is useful both for replication purposes and for restoring a backup to a desired state with great precision. The term Hot Standby also refers to the ability of the server to move from recovery through to normal operation while users continue running queries and/or keep their connections open.

Running queries in hot standby mode is similar to normal query operation, though there are several usage and administrative differences.

1.2 Repmgr

本文中,我们利用Repmgr来管理PostgreSQL的流复制集群,并提供HA功能。

**Repmgr是2010年由2ndQuadrant推出的PostgreSQL故障切换最流行的工具。**它是一个开源工具套件,用于管理PostgreSQL服务器集群中的复制和故障转移。它使用扩展来增强PostgreSQL的内置热备份功能,以设置备用服务器,监控复制以及执行管理任务,例如故障转移或手动切换操作。

repmgr帮助DBA和系统管理员管理PostgreSQL数据库集群。通过利用PostgreSQL 9中引入的Hot Standby功能,repmgr极大地简化了设置和管理具有高可用性和可伸缩性要求的数据库的过程。

Repmgr通过以下方式简化了管理和日常管理,提高了生产力并降低了PostgreSQL集群的总体成本:

  • 监视复制过程;
  • 允许DBA发布高可用性操作,例如切换和故障切换。

2.基础环境

本例的环境中有4个服务器(以下简称节点),其相关信息如下表所示:

主机名IP地址操作系统软件包规划集群角色规划
repmgr01192.168.29.191/24CentOS 7.8 64-bitpostgresql12+repmgr_12master(初始角色)
repmgr02192.168.29.192/24CentOS 7.8 64-bitpostgresql12+repmgr_12standby1(初始角色)
repmgr03192.168.29.193/24CentOS 7.8 64-bitpostgresql12+repmgr_12standby2(初始角色)
repmgr03192.168.29.194/24CentOS 7.8 64-bitpostgresql12+repmgr_12witness

3.基础配置

3.1 系统环境配置

  1. 以root用户身份在所有节点上执行如下操作,配置服务器时区、语言环境,并根据个人使用习惯选择是否配置系统别名:

    echo "export TZ='Asia/Shanghai'" >> /etc/profile
    echo "export LANG=en_US.utf8" >> /etc/profile
    echo "alias df='df -hTP'" >> /etc/profile
    echo "alias ll='ls -l --color=auto '" >> /etc/profile
    source /etc/profile
    
  2. 以root用户身份在所有节点上执行如下操作,关闭服务系统SELinux功能:

    cat << EOF > /etc/selinux/config 
    SELINUX=disabled
    SELINUXTYPE=targeted
    EOF
    setenforce 0
    
  3. 以root用户身份在所有节点上执行如下操作,关闭系统防火墙:

    systemctl disable firewalld.service && systemctl stop firewalld.service
    

    生产环境根据实际需要决定是否关闭。若启用防火墙,需要开放相关端口,如54321

  4. 以root用户身份在所有节点上执行如下操作,配置本地主机名解析:

    cat << EOF >> /etc/hosts
    192.168.29.191  repmgr01
    192.168.29.192  repmgr02
    192.168.29.193  repmgr03
    192.168.29.194  repmgr04
    EOF
    
  5. 为加快软件包下载速度,以root用户身份在所有节点上执行如下操作,配置系统使用阿里云CentOS软件仓库:

    mkdir -p /etc/yum.repos.d/repo_backup && mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/repo_backup
    wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
    yum clean all
    yum makecache fast
    
  6. 以root用户身份在所有节点上执行如下操作,安装NTP客户端软件包,并配置系统时钟与阿里云时钟服务器进行时钟同步,实现集群时钟的一致性:

    yum install -y ntpdate createrepo sshpass
    ntpdate ntp1.aliyun.com
    echo "ntpdate ntp1.aliyun.com" >> /etc/rc.local
    chmod +x /etc/rc.local /etc/rc.d/rc.local
    

    生产环境中,建议将集群其中一个节点配置为NTP服务器,其余节点向该节点进行同步。具体配置请参考相关手册说明。

  7. 为方便操作,我们配置了所有节点的root用户之间SSH互信。此操作是可选操作:

    ssh-keygen -t rsa -P '' -f '/root/.ssh/id_rsa'
    for h in `grep repmgr /etc/hosts |awk '{print $2}'`; do sshpass -p r00tr00t ssh-copy-id -o StrictHostKeyChecking=no root@$h; done
    for h in `grep repmgr /etc/hosts |awk '{print $2}'`; do ssh root@$h date; done
    
  8. 以root用户身份在所有节点上执行如下操作,创建postgres用户用来运行pg和repmgr进程,并为postgres用户配置ssh两两互信:

    groupadd -g 5432 postgres
    useradd -u 5432 -g postgres postgres
    echo postgres |passwd --force --stdin postgres
    su - postgres
    ssh-keygen -t rsa -P '' -f '/home/postgres/.ssh/id_rsa'
    for h in `grep repmgr /etc/hosts |awk '{print $2}'`; do sshpass -p postgres ssh-copy-id -o StrictHostKeyChecking=no postgres@$h; done
    for h in `grep repmgr /etc/hosts |awk '{print $2}'`; do ssh postgres@$h date; done
    exit
    
  9. 以root用户身份在所有节点上执行如下操作,配置用户的Shell资源限制:

    sed -i "/^postgres/d" /etc/security/limits.conf && \
    sed -i "/^postgres/d" /etc/security/limits.d/20-nproc.conf && \
    cat << EOF >> /etc/security/limits.conf
    #### Added by root:
    *        soft    core      unlimited
    @root    hard    core      unlimited
    @root    soft    nproc     unlimited
    @root    hard    nproc     unlimited
    @root    soft    nofile    300000
    @root    hard    nofile    300000
    #### Added by root:
    @postgres    hard     nofile   65536
    @postgres    soft     nofile   65536
    @postgres    hard     nproc    65536
    @postgres    soft     nproc    65536
    @postgres    soft     core     unlimited
    @postgres    hard     core     unlimited
    EOF
    echo "@postgres    soft    nproc    65536" >> /etc/security/limits.d/20-nproc.conf
    

上述系统内核参数仅为经验值,并非针对所有系统适用。需要酌情配置和使用。

  1. 以root用户身份在所有节点上执行如下操作,优化系统内核参数:

    cat << EOF >> /etc/sysctl.conf
    kernel.sem = 5010 641280 5010 256
    #kernel.sem = 50100 64128000 50100 1280
    fs.aio-max-nr = 1048576
    fs.file-max = 6815744
    kernel.shmall = 2097152
    kernel.shmmax = 4294967295
    kernel.shmmni = 4096
    net.ipv4.ip_local_port_range = 9000 65500
    net.core.rmem_default = 262144
    net.ipv4.tcp_syncookies = 1
    net.ipv4.tcp_tw_reuse = 1
    net.ipv4.tcp_tw_recycle = 1
    net.ipv4.tcp_tw_timestamps=1
    net.ipv4.tcp_fin_timeout = 30
    net.core.rmem_max = 4194304
    net.core.wmem_default = 262144
    net.core.wmem_max = 1048576
    net.core.somaxconn=1024
    vm.swappiness=0
    vm.overcommit_memory = 2
    vm.overcommit_ratio = 90        #mem/(mem+swap)
    vm.dirty_background_ratio=1     #DirtyPageTotal/Memory
    vm.dirty_ratio = 2              #WriteCache/Memory
    net.ipv4.tcp_keepalive_time = 1200
    net.ipv4.tcp_keepalive_probes = 3
    net.ipv4.tcp_keepalive_intvl = 30
    net.ipv4.tcp_max_syn_backlog = 8192
    net.ipv4.tcp_max_tw_buckets = 6000
    net.core.netdev_max_backlog = 32768
    net.core.wmem_default = 8388608
    net.core.rmem_default = 8388608
    net.core.rmem_max = 16777216
    net.ipv4.tcp_synack_retries = 2
    net.ipv4.tcp_syn_retries = 2
    net.ipv4.route.gc_timeout = 100
    net.ipv4.tcp_wmem = 8192 436600 873200
    net.ipv4.tcp_rmem  = 32768 436600 873200
    net.ipv4.tcp_mem = 94500000 91500000 92700000
    net.ipv4.tcp_max_orphans = 3276800
    EOF
    
    /sbin/sysctl -p
    systemctl set-property crond.service TasksMax=65535
    

    上述系统内核参数仅为经验值,并非针对所有系统适用。需要酌情配置和使用。

  2. 以root用户身份在所有节点上执行如下操作,配置数据磁盘的IO调度策略:

    echo deadline > /sys/block/sda/queue/scheduler && \
    cat << EOF >> /etc/rc.d/rc.local
    echo deadline > /sys/block/sda/queue/scheduler
    #echo deadline > /sys/block/sdb/queue/scheduler
    EOF
    

    本例中,我们采用的是虚拟机,仅一块磁盘(sda)。实际环境请根据情况进行调整。

  3. 以root用户身份在所有节点上执行如下操作,配置系统,防止某些情况下系统自动删除用户IPC资源,导致服务不可用:

    sed -i "/RemoveIPC=/d" /etc/systemd/logind.conf
    echo "RemoveIPC=no" >> /etc/systemd/logind.conf
    systemctl daemon-reload
    systemctl restart systemd-logind.service
    

4.软件安装

本例中我们从在线YUM源仓库安装所需要的软件包,包括PostgreSQL Server相关软件包和Repmgr相关软件包:

  1. 以root用户身份在所有节点上执行如下操作,添加YUM仓库:

    cat << EOF > /etc/yum.repos.d/c7-devtoolset-7-x86_64.repo
    [c7-devtoolset-7]
    name=c7-devtoolset-7
    baseurl=https://buildlogs.centos.org/c7-devtoolset-7.x86_64/
    gpgcheck=0
    enabled=1
    [c7-llvm-toolset-7]
    name=c7-llvm-toolset-7
    baseurl=https://buildlogs.centos.org/c7-llvm-toolset-7.x86_64/
    gpgcheck=0
    enabled=1
    EOF
    
    cat << EOF > /etc/yum.repos.d/fedoraproject-epel-7.repo
    [fedoraproject-epel-7]
    name=fedoraproject-epel-7
    baseurl=https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/
    gpgcheck=0
    enabled=1
    EOF
    
    rpm -ivh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    
  2. 以root用户身份在所有节点上执行如下操作,安装软件包:

    yum install -y postgresql12-server postgresql12-contrib postgresql12-test postgresql12-devel repmgr_12 repmgr_12-devel
    rpm -qa |grep -E '(postgres|repmgr)'
    

5.集群配置及初始化

为方便操作,我们在所有节点上执行如下操作,配置postgres用户环境变量:

su - postgres
cat << EOF >> /home/postgres/.bashrc
# Added by root for postgres user:
export PGHOME=/usr/pgsql-12
export PGDATA=/data/pgdata/12
export PATH=\$PGHOME/bin:\$PATH
alias cdhome="cd \$PGHOME"
alias cddata="cd \$PGDATA"
#alias tailf_log="tailf -n40 \$(ls -ltr \$PGDATA/log/*.log |tail -n1 |awk '{print \$NF}')"
EOF

Repmgr软件运行时通过postgres用户来运行,但我们安装后的Repmgr软件包配置文件默认归属root用户所有,postgres用户默认只能读取该文件,因此为方便后续编辑配置文件,建议修改repmgr配置文件属主为postgres。以root用户身份在在所有节点上执行如下操作:

chown -R postgres.postgres /etc/repmgr

5.1 初始化流复制集群

5.1.1 初始化Master节点

使用root用户身份,在repmgr01节点上初始化数据库实例。该数据库将作为流复制集群的源数据库,后续克隆至其他Standby节点上。在repmgr01节点上:

mkdir -p /data/pgdata/12
chmod 700 /data/pgdata/12
chown -R postgres:postgres /data
su - postgres
echo "postgres" > ~/.postgres_passwd
chmod 600 ~/.postgres_passwd
initdb -U postgres -E UTF8 --locale=en_US.utf8 -D $PGDATA --wal-segsize=16 --data-checksums --auth=md5 --pwfile=/home/postgres/.postgres_passwd
rm -f ~/.postgres_passwd

修改postgresql.conf和pg_hba.conf配置文件:

cat << EOF >> $PGDATA/postgresql.conf
# Added by root user:
listen_addresses = '*'
max_connections = 400
max_wal_senders = 10
max_replication_slots = 10
wal_level = 'replica'
hot_standby = on
archive_mode = on
archive_command = '/bin/true'
wal_keep_segments = 64
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
track_activity_query_size = 4096
EOF
sed -i "/^shared_buffer/s/128/256/g" $PGDATA/postgresql.conf
sed -i "/^max_wal_size/s/1GB/2GB/g" $PGDATA/postgresql.conf
sed -i "/^min_wal_size/s/80MB/512MB/g" $PGDATA/postgresql.conf

echo "host    all             all         0.0.0.0/0               md5" >> $PGDATA/pg_hba.conf

为方便操作,配置几个系统别名:

cat << EOF >> /home/postgres/.bashrc
# ALIAS FOR master & standby DB:
alias start_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log start"
alias stop_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log -m fast stop"
alias restart_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log -m fast restart"
alias reload_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log reload"
alias show_repmgr_cluster="repmgr cluster show --compact"
EOF
source /home/postgres/.bashrc

启动Master节点的PostgreSQL Server实例:

start_pgsql

Repmgr使用一个独立的数据库(repmgr)来存储其元数据,通常建议对流复制配置一个专用的复制用户,如repmgr用户。用户名和数据库名称可以是任何名称,为简单起见,我们统一配置为“ repmgr”。同时,repmgr用户将被创建为超级用户。这是2ndQuadrant的推荐做法,因为某些Repmgr操作需要一些更高的特权。

这里,我们仅需要在主节点上运行以下命令即可。在Master节点(repmgr01)上,用postgres用户操作:

  • 添加pg_stat_statements扩展插件:

    su - postgres
    psql -Upostgres -dtemplate1 -h127.0.0.1 -c "CREATE EXTENSION pg_stat_statements;"
    psql -Upostgres -dpostgres -h127.0.0.1 -c "CREATE EXTENSION pg_stat_statements;"
    
  • 配置postgresql.conf文件,启用同步复制参数:

    cat << EOF >> $PGDATA/postgresql.conf
    synchronous_commit = remote_apply
    synchronous_standby_names = '1(repmgr01,repmgr02,repmgr03)'
    EOF
    

    后面我们将通过Clone方式从主库将数据库完整克隆到备库,因此Standby节点上的配置文件中primary_slot_name这个参数需要后续手动修改。

  • 创建repmgr用户、repmgr元数据库、修改repmgr用户search_path参数:

    psql -Upostgres -dpostgres -h127.0.0.1 -c "CREATE USER repmgr WITH SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION ENCRYPTED PASSWORD 'repmgr';"
    psql -Upostgres -dpostgres -h127.0.0.1 -c "CREATE DATABASE repmgr WITH OWNER repmgr TEMPLATE template1;"
    psql -Upostgres -dpostgres -h127.0.0.1 -c "ALTER USER repmgr SET search_path TO repmgr, public;"
    echo "shared_preload_libraries = 'repmgr, pg_stat_statements'" >> $PGDATA/postgresql.conf
    

5.2 准备Standby节点

以root用户身份登录系统,在所有Standby节点(本例为:repmgr02, repmgr03)上,准备数据目录,以及创建系统别名:

mkdir -p /data/pgdata/12
chmod 700 /data/pgdata/12
chown -R postgres:postgres /data

cat << EOF >> /home/postgres/.bashrc
# ALIAS FOR master & standby DB:
alias start_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log start"
alias stop_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log -m fast stop"
alias restart_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log -m fast restart"
alias reload_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log reload"
alias show_repmgr_cluster="repmgr cluster show --compact"
EOF

5.3 配置Repmgr

针对从PGDG软件仓库中安装的PostgreSQL 12以及Repmgr软件,Repmgr的默认配置文件的路径是/etc/repmgr/12,配置文件为repmgr.conf

我们需要在所有主节点和备用节点中配置Repmgr软件参数。本例中,这里配置的是使用Repmgr来管理流复制的最低要求参数。我们需要为每台机器分配唯一的节点ID并为其命名。名称可以是任意的,但是建议将其与主机名保持相同,以方便区分。

我们还需要为每个节点指定一个连接字符串,并指定PostgreSQL数据目录的位置。

  1. 以root用户身份登录repmgr01节点,执行如下操作:

    配置/etc/repmgr/12/repmgr.conf文件:

    cat << EOF >> /etc/repmgr/12/repmgr.conf
    # Added by root user:
    node_id=1
    node_name='repmgr01'
    conninfo='host=192.168.29.191 port=5432 user=repmgr dbname=repmgr password=repmgr connect_timeout=3 application_name=repmgr01 keepalives=1 keepalives_idle=6 keepalives_interval=3 keepalives_count=3'
    data_directory='/data/pgdata/12'
    location='my-dc1'
    EOF
    

    创建密码文件.pgpass,避免在配置文件中直接暴露密码:

    cat << EOF > /home/postgres/.pgpass
    # hostname:port:database:username:password
    # For postgres superuser:
    127.0.0.1:5432:postgres:postgres:postgres
    localhost:5432:postgres:postgres:postgres
    repmgr01:5432:postgres:postgres:postgres
    192.168.29.191:postgres:postgres:postgres
    repmgr02:5432:postgres:postgres:postgres
    192.168.29.192:5432:postgres:postgres:postgres
    repmgr03:5432:postgres:postgres:postgres
    192.168.29.193:5432:postgres:postgres:postgres
    # For repmgr user:
    127.0.0.1:5432:repmgr:repmgr:repmgr
    localhost:5432:repmgr:repmgr:repmgr
    repmgr01:5432:repmgr:repmgr:repmgr
    192.168.29.191:5432:repmgr:repmgr:repmgr
    repmgr02:5432:repmgr:repmgr:repmgr
    192.168.29.192:5432:repmgr:repmgr:repmgr
    repmgr03:5432:repmgr:repmgr:repmgr
    192.168.29.193:5432:repmgr:repmgr:repmgr
    # For stream replication:
    repmgr01:5432:replication:repmgr:repmgr
    192.168.29.191:5432:replication:repmgr:repmgr
    repmgr02:5432:replication:repmgr:repmgr
    192.168.29.192:5432:replication:repmgr:repmgr
    repmgr03:5432:replication:repmgr:repmgr
    192.168.29.193:5432:replication:repmgr:repmgr
    EOF
    
    chown postgres:postgres /home/postgres/.pgpass
    chmod 600 /home/postgres/.pgpass
    

    修改/data/pgdata/12/pg_hba.conf配置文件,允许来自repmgr用户的连接:

    sed -i "/  replication /d" /data/pgdata/12/pg_hba.conf
    cat << EOF >> /data/pgdata/12/pg_hba.conf
    # Added by root user:
    local   replication     repmgr                              md5
    host    replication     repmgr      127.0.0.1/32            md5
    host    replication     repmgr      192.168.29.0/24         md5
    local   repmgr          repmgr                              md5
    host    repmgr          repmgr      127.0.0.1/32            md5
    host    repmgr          repmgr      192.168.29.0/24         md5
    EOF
    

    重启Master节点的PostgreSQL Server数据库实例:

    su - postgres
    restart_pgsql
    
  2. 以root用户身份登录repmgr02节点,执行如下操作:

    配置/etc/repmgr/12/repmgr.conf文件:

    cat << EOF >> /etc/repmgr/12/repmgr.conf
    # Added by root user:
    node_id=2
    node_name='repmgr02'
    conninfo='host=192.168.29.192 port=5432 user=repmgr dbname=repmgr password=repmgr connect_timeout=3 application_name=repmgr02 keepalives=1 keepalives_idle=6 keepalives_interval=3 keepalives_count=3'
    data_directory='/data/pgdata/12'
    location='my-dc1'
    EOF
    

    创建密码文件.pgpass,避免在配置文件中直接暴露密码:

    cat << EOF > /home/postgres/.pgpass
    #LINE-FORMAT: hostname:port:database:username:password
    # For postgres superuser:
    127.0.0.1:5432:postgres:postgres:postgres
    localhost:5432:postgres:postgres:postgres
    repmgr01:5432:postgres:postgres:postgres
    192.168.29.191:postgres:postgres:postgres
    repmgr02:5432:postgres:postgres:postgres
    192.168.29.192:5432:postgres:postgres:postgres
    repmgr03:5432:postgres:postgres:postgres
    192.168.29.193:5432:postgres:postgres:postgres
    # For repmgr user:
    127.0.0.1:5432:repmgr:repmgr:repmgr
    localhost:5432:repmgr:repmgr:repmgr
    repmgr01:5432:repmgr:repmgr:repmgr
    192.168.29.191:5432:repmgr:repmgr:repmgr
    repmgr02:5432:repmgr:repmgr:repmgr
    192.168.29.192:5432:repmgr:repmgr:repmgr
    repmgr03:5432:repmgr:repmgr:repmgr
    192.168.29.193:5432:repmgr:repmgr:repmgr
    # For stream replication:
    repmgr01:5432:replication:repmgr:repmgr
    192.168.29.191:5432:replication:repmgr:repmgr
    repmgr02:5432:replication:repmgr:repmgr
    192.168.29.192:5432:replication:repmgr:repmgr
    repmgr03:5432:replication:repmgr:repmgr
    192.168.29.193:5432:replication:repmgr:repmgr
    EOF
    chown postgres:postgres /home/postgres/.pgpass
    chmod 600 /home/postgres/.pgpass
    
  3. 以root用户身份登录repmgr03节点,执行如下操作:

    配置/etc/repmgr/12/repmgr.conf文件:

    cat << EOF >> /etc/repmgr/12/repmgr.conf
    # Added by root user:
    node_id=3
    node_name='repmgr03'
    conninfo='host=192.168.29.193 port=5432 user=repmgr dbname=repmgr password=repmgr connect_timeout=3 application_name=repmgr03 keepalives=1 keepalives_idle=6 keepalives_interval=3 keepalives_count=3'
    data_directory='/data/pgdata/12'
    location='my-dc1'
    EOF
    

    创建密码文件.pgpass,避免在配置文件中直接暴露密码:

    cat << EOF > /home/postgres/.pgpass
    #LINE-FORMAT: hostname:port:database:username:password
    # For postgres superuser:
    127.0.0.1:5432:postgres:postgres:postgres
    localhost:5432:postgres:postgres:postgres
    repmgr01:5432:postgres:postgres:postgres
    192.168.29.191:postgres:postgres:postgres
    repmgr02:5432:postgres:postgres:postgres
    192.168.29.192:5432:postgres:postgres:postgres
    repmgr03:5432:postgres:postgres:postgres
    192.168.29.193:5432:postgres:postgres:postgres
    # For repmgr user:
    127.0.0.1:5432:repmgr:repmgr:repmgr
    localhost:5432:repmgr:repmgr:repmgr
    repmgr01:5432:repmgr:repmgr:repmgr
    192.168.29.191:5432:repmgr:repmgr:repmgr
    repmgr02:5432:repmgr:repmgr:repmgr
    192.168.29.192:5432:repmgr:repmgr:repmgr
    repmgr03:5432:repmgr:repmgr:repmgr
    192.168.29.193:5432:repmgr:repmgr:repmgr
    # For stream replication:
    repmgr01:5432:replication:repmgr:repmgr
    192.168.29.191:5432:replication:repmgr:repmgr
    repmgr02:5432:replication:repmgr:repmgr
    192.168.29.192:5432:replication:repmgr:repmgr
    repmgr03:5432:replication:repmgr:repmgr
    192.168.29.193:5432:replication:repmgr:repmgr
    EOF
    chown postgres:postgres /home/postgres/.pgpass
    chmod 600 /home/postgres/.pgpass
    

5.4 测试Repmgr连接

在所有Master和Standby节点(本例中为repmgr01, repmgr02和repmgr03)上,以postgres用户身份测试各节点是否能连接Master节点:

su - postgres
/usr/pgsql-12/bin/psql 'host=repmgr01 user=repmgr dbname=repmgr connect_timeout=3' -c "select version();"

5.5 注册Master节点到Repmgr

使用postgres用户身份登录Master节点(repmgr01),执行如下操作将当前PostgreSQL Server实例注册到Repmgr,并检查状态:

su - postgres
/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf primary register
/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf cluster show

5.6 克隆Master节点到Standby节点

使用postgres用户身份登录两个Standby节点,在两个Standby节点上执行如下操作,从Master节点将数据库完整克隆至Standby节点上:

su - postgres
/usr/pgsql-12/bin/repmgr -h 192.168.29.191 -U repmgr -d repmgr -f /etc/repmgr/12/repmgr.conf standby clone --dry-run
/usr/pgsql-12/bin/repmgr -h 192.168.29.191 -U repmgr -d repmgr -f /etc/repmgr/12/repmgr.conf standby clone

如下所示为一个完整克隆操作的输出过程实例:

$ /usr/pgsql-12/bin/repmgr -h 192.168.29.191 -U repmgr -d repmgr -f /etc/repmgr/12/repmgr.conf standby clone --dry-run
NOTICE: destination directory "/data/pgdata/12" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.29.191 user=repmgr dbname=repmgr
DETAIL: current installation size is 32 MB
INFO: "repmgr" extension is installed in database "repmgr"
INFO: replication slot usage not requested;  no replication slot will be set up for this standby
INFO: parameter "max_wal_senders" set to 10
NOTICE: checking for available walsenders on the source node (2 required)
INFO: sufficient walsenders available on the source node
DETAIL: 2 required, 10 available
NOTICE: checking replication connections can be made to the source server (2 required)
INFO: required number of replication connections could be made to the source server
DETAIL: 2 replication connections required
NOTICE: standby will attach to upstream node 1
HINT: consider using the -c/--fast-checkpoint option
INFO: all prerequisites for "standby clone" are met

$ /usr/pgsql-12/bin/repmgr -h 192.168.29.191 -U repmgr -d repmgr -f /etc/repmgr/12/repmgr.conf standby clone
NOTICE: destination directory "/data/pgdata/12" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.29.191 user=repmgr dbname=repmgr
DETAIL: current installation size is 32 MB
INFO: replication slot usage not requested;  no replication slot will be set up for this standby
NOTICE: checking for available walsenders on the source node (2 required)
NOTICE: checking replication connections can be made to the source server (2 required)
INFO: checking and correcting permissions on existing directory "/data/pgdata/12"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
  /usr/pgsql-12/bin/pg_basebackup -l "repmgr base backup"  -D /data/pgdata/12 -h 192.168.29.191 -p 5432 -U repmgr -X stream 
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /data/pgdata/12 start
HINT: after starting the server, you need to register this standby with "repmgr standby register"

5.7 注册Standby节点

接下来,我们需要在所有Standby节点上执行如下操作启动PostgreSQL Server数据库实例,并将其作为Standby节点注册到Repmgr中:

start_pgsql
/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf standby register --upstream-node-id=1 --dry-run
/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf standby register --upstream-node-id=1

5.8 检查流复制状态

使用postgres用户身份登录Master节点,执行如下操作检查集群流复制状态:

psql -Upostgres -dpostgres -c "select * from pg_stat_replication;"
psql -Upostgres -dpostgres -c "select * from pg_replication_slots;"

5.9 检查Repmgr集群状态

使用postgres用户身份登录任意Master或者Standby节点,执行如下操作检查Repmgr集群状态:

show_repmgr_cluster        #此命令是我们在前面配置的系统别名。
/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf cluster show --compact
/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf cluster event
/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf cluster matrix

6.启用集群HA功能

使用Repmgr来管理流复制集群只是完成了一半的工作。为了获得真正的自动故障转移,我们必须在repmgr.conf文件中配置其他参数,并启动repmgr守护程序用来监控集群中各节点状态。这就是我们将在下面将要执行的内容。

6.1 启用Witness节点

使用root用户身份登录Witness节点(repmgr04),执行如下操作,初始化Witness节点:

mkdir -p /data/pgdata/12
chmod 700 /data/pgdata/12
chown -R postgres:postgres /data
chown -R postgres:postgres /etc/repmgr
su - postgres
echo "postgres" > ~/.postgres_passwd && chmod 600 ~/.postgres_passwd
initdb -U postgres -E UTF8 --locale=en_US.utf8 -D /data/pgdata/12 --wal-segsize=16 --data-checksums --auth=md5 --pwfile=/home/postgres/.postgres_passwd
rm -f /home/postgres/.postgres_passwd
echo "listen_addresses = '*'" >> /data/pgdata/12/postgresql.conf
cat << EOF >> /home/postgres/.bashrc
# ALIAS FOR witness DB:
alias start_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log start"
alias stop_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log -m fast stop"
alias restart_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log -m fast restart"
alias reload_pgsql="pg_ctl -D \$PGDATA -l \$PGDATA/postgresql.log reload"
alias show_repmgr_cluster="repmgr cluster show --compact"
EOF
source /home/postgres/.bashrc
start_pgsql

执行如下操作配置Witness节点的repmgr.conf配置文件:

cat << EOF >> /etc/repmgr/12/repmgr.conf
node_id=9
node_name='repmgr04'
conninfo='host=192.168.29.194 port=5432 user=repmgr dbname=repmgr password=repmgr connect_timeout=3 application_name=repmgr04 keepalives=1 keepalives_idle=6 keepalives_interval=3 keepalives_count=3'
data_directory='/var/lib/pgsql/12/data'
location='my-dc1'
EOF

执行如下操作,为Witness节点安装有用的扩展,并配置Witness节点pg_hba.conf文件,允许来自repmgr用户的连接:

su - postgres
psql -Upostgres -dtemplate1 -c "CREATE EXTENSION pg_stat_statements;"
psql -Upostgres -dpostgres -c "CREATE EXTENSION pg_stat_statements;"
echo "shared_preload_libraries ='repmgr, pg_stat_statements'" >> /data/pgdata/12/postgresql.conf
sed -i "/  replication /d" /data/pgdata/12/pg_hba.conf
cat << EOF >> /data/pgdata/12/pg_hba.conf
local   replication    repmgr                      trust
host    replication    repmgr   127.0.0.1/32       trust
host    replication    repmgr   192.168.29.0/24    trust
local   repmgr         repmgr                      trust
host    repmgr         repmgr   127.0.0.1/32       trust
host    repmgr         repmgr   192.168.29.0/16    trust
EOF

执行如下操作,在Witness节点上创建repmgr专用复制用户,并配置密码文件,防止在配置文件中直接暴露用户密码:

psql -Upostgres -dpostgres -W -c "CREATE USER repmgr WITH SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION ENCRYPTED PASSWORD 'repmgr';"
psql -Upostgres -dpostgres -W -c "CREATE DATABASE repmgr WITH OWNER repmgr TEMPLATE template1;"
psql -Upostgres -dpostgres -W -c "ALTER USER repmgr SET search_path TO repmgr, public;"
psql -Urepmgr -drepmgr -W -c "show search_path;"

cat << EOF > /home/postgres/.pgpass
#LINE-FORMAT: hostname:port:database:username:password
# For repmgr user:
127.0.0.1:5432:repmgr:repmgr:repmgr
localhost:5432:repmgr:repmgr:repmgr
repmgr01:5432:repmgr:repmgr:repmgr
192.168.29.191:5432:repmgr:repmgr:repmgr
repmgr02:5432:repmgr:repmgr:repmgr
192.168.29.192:5432:repmgr:repmgr:repmgr
repmgr03:5432:repmgr:repmgr:repmgr
192.168.29.193:5432:repmgr:repmgr:repmgr
EOF
chown postgres:postgres /home/postgres/.pgpass
chmod 600 /home/postgres/.pgpass
restart_pgsql

在Master节点(repmgr01)上,用postgres用户登录并检查是否能连接Witness节点:

/usr/pgsql-12/bin/psql 'host=192.168.29.194 port=5432 user=repmgr dbname=repmgr connect_timeout=3' -c "show port;"

回到Witness节点上,以postgres用户身份运行repmgr witness register命令,注册Witness节点至Repmgr集群。注意到下面的命令中我们使用的是主节点的IP地址,而不是见证节点的地址:

/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf witness register -h 192.168.29.191

以postgres用户身份,在集群中任意节点上执行如下操作,检查当前集群状态:

repmgr cluster show --compact
repmgr cluster matrix

如下所示为本实例的集群状态示例:

$ repmgr cluster show --compact
 ID | Name     | Role    | Status    | Upstream | Location | Prio. | TLI
----+----------+---------+-----------+----------+----------+-------+-----
 1  | repmgr01 | primary | * running |          | default  | 100   | 1   
 2  | repmgr02 | standby |   running | repmgr01 | default  | 100   | 1   
 3  | repmgr03 | standby |   running | repmgr01 | default  | 100   | 1   
 9  | repmgr04 | witness | * running | repmgr01 | default  | 0     | n/a

$ repmgr cluster matrix
INFO: connecting to database
 Name     | ID | 1 | 2 | 3 | 9
----------+----+---+---+---+---
 repmgr01 | 1  | * | * | * | * 
 repmgr02 | 2  | * | * | * | * 
 repmgr03 | 3  | * | * | * | * 
 repmgr04 | 9  | * | * | * | * 

6.2 提升postgres用户权限

为了让Repmgr能执行一些系统命令来完成故障转移功能,postgres用户需要具备sudo权限,并且需要能无需密码执行一些系统命令。因此我们将postgres用户加入到系统的sudo用户配置文件中。使用root用户在所有节点上(repmgr01、repmgr02、repmgr03、repmgr04)上操作,让postgres用户具有无需密码启停系统服务(包括postgresql和repmgr服务)的权限:

chmod +w /etc/sudoers
cat << EOF >> /etc/sudoers 
postgres  ALL=NOPASSWD:ALL
EOF
chmod -w /etc/sudoers

6.3 启用HA相关参数

下面我们使用postgres用户登录集群的各个节点,分别在各节点修改repmgr.conf配置文件启用故障转移所需的参数配置。具体参数的含义请参考Repmgr官方文档的描述。

  1. 节点repmgr01:

    cat << EOF >> /etc/repmgr/12/repmgr.conf
    failover='automatic'
    promote_command='/usr/pgsql-12/bin/repmgr standby promote -f /etc/repmgr/12/repmgr.conf --log-to-file'
    follow_command='/usr/pgsql-12/bin/repmgr standby follow -f /etc/repmgr/12/repmgr.conf --log-to-file --upstream-node-id=%n'
    priority=60
    monitor_interval_secs=2
    connection_check_type='ping'
    reconnect_attempts=3
    reconnect_interval=3
    primary_visibility_consensus=true
    standby_disconnect_on_failover=true
    repmgrd_service_start_command='sudo /usr/bin/systemctl start repmgr12.service'
    repmgrd_service_stop_command='sudo /usr/bin/systemctl stop repmgr12.service'
    service_start_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log start'
    service_stop_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast stop'
    service_restart_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast restart'
    service_reload_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log reload'
    monitoring_history=yes
    log_status_interval=60
    log_level='INFO'
    log_facility='STDERR'
    log_file='/var/log/repmgr/repmgrd.log'
    replication_user='repmgr'
    replication_type='physical'
    use_replication_slots=yes
    EOF
    
  2. 节点repmgr02:

    cat << EOF >> /etc/repmgr/12/repmgr.conf
    failover='automatic'
    promote_command='/usr/pgsql-12/bin/repmgr standby promote -f /etc/repmgr/12/repmgr.conf --log-to-file'
    follow_command='/usr/pgsql-12/bin/repmgr standby follow -f /etc/repmgr/12/repmgr.conf --log-to-file --upstream-node-id=%n'
    priority=60
    monitor_interval_secs=2
    connection_check_type='ping'
    reconnect_attempts=3
    reconnect_interval=3
    primary_visibility_consensus=true
    standby_disconnect_on_failover=true
    repmgrd_service_start_command='sudo /usr/bin/systemctl start repmgr12.service'
    repmgrd_service_stop_command='sudo /usr/bin/systemctl stop repmgr12.service'
    service_start_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log start'
    service_stop_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast stop'
    service_restart_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast restart'
    service_reload_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log reload'
    monitoring_history=yes
    log_status_interval=60
    log_level='INFO'
    log_facility='STDERR'
    log_file='/var/log/repmgr/repmgrd.log'
    replication_user='repmgr'
    replication_type='physical'
    use_replication_slots=yes
    EOF
    
  3. 节点repmgr03:

    cat << EOF >> /etc/repmgr/12/repmgr.conf
    failover='automatic'
    promote_command='/usr/pgsql-12/bin/repmgr standby promote -f /etc/repmgr/12/repmgr.conf --log-to-file'
    follow_command='/usr/pgsql-12/bin/repmgr standby follow -f /etc/repmgr/12/repmgr.conf --log-to-file --upstream-node-id=%n'
    priority=40
    monitor_interval_secs=2
    connection_check_type='ping'
    reconnect_attempts=3
    reconnect_interval=3
    primary_visibility_consensus=true
    standby_disconnect_on_failover=true
    repmgrd_service_start_command='sudo /usr/bin/systemctl start repmgr12.service'
    repmgrd_service_stop_command='sudo /usr/bin/systemctl stop repmgr12.service'
    service_start_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log start'
    service_stop_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast stop'
    service_restart_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast restart'
    service_reload_command='/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log reload'
    monitoring_history=yes
    log_status_interval=60
    log_level='INFO'
    log_facility='STDERR'
    log_file='/var/log/repmgr/repmgrd.log'
    replication_user='repmgr'
    replication_type='physical'
    use_replication_slots=yes
    EOF
    
  4. 节点repmgr04:

    cat << EOF >> /etc/repmgr/12/repmgr.conf
    failover='automatic'
    promote_command='/usr/pgsql-12/bin/repmgr standby promote -f /etc/repmgr/12/repmgr.conf --log-to-file'
    follow_command='/usr/pgsql-12/bin/repmgr standby follow -f /etc/repmgr/12/repmgr.conf --log-to-file --upstream-node-id=%n'
    #priority=60
    monitor_interval_secs=2
    connection_check_type='ping'
    reconnect_attempts=3
    reconnect_interval=3
    primary_visibility_consensus=true
    standby_disconnect_on_failover=true
    repmgrd_service_start_command='sudo /usr/bin/systemctl start repmgr12.service'
    repmgrd_service_stop_command='sudo /usr/bin/systemctl stop repmgr12.service'
    service_start_command='sudo /usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log start'
    service_stop_command='sudo /usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast stop'
    service_restart_command='sudo /usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast restart'
    service_reload_command='sudo /usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log reload'
    monitoring_history=yes
    log_status_interval=60
    log_level='INFO'
    log_facility='STDERR'
    log_file='/var/log/repmgr/repmgrd.log'
    replication_user='repmgr'
    replication_type='physical'
    use_replication_slots=yes
    repmgrd_pid_file='/var/run/repmgr/repmgrd-12.pid'
    EOF
    

6.4 启动repmgr守护进程

上面我们已经在集群所有节点和见证节点中设置了相关参数,接下来我们可以以守护进程方式运行Repmgr。在正式启动Repmgr之前,我们先执行命令的--dry-run选项以测试启动Repmgr守护程序中可能存在的问题。

我们首先需要在主节点中进行测试,然后在两个备用节点中进行测试,然后在见证节点中进行测试。注意,该命令必须以postgres用户身份执行:

su - postgres
/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf daemon start --dry-run

如果所有节点以--dry-run的方式启动Repmgr进程都没有错误。则接下来,我们在所有四个节点中启动守护程序:

/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf daemon start

如下所示为以守护进程方式启动Repmgr进程,并检查后台进程状态的命令输出示例:

$ /usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf daemon start
NOTICE: executing: "sudo /usr/bin/systemctl start repmgr12.service"
NOTICE: repmgrd was successfully started

$ ps -ef |grep repmgr |grep -v grep
postgres  1537  1270  0 19:17 ?        00:00:00 postgres: repmgr repmgr 192.168.29.194(53823) idle
postgres  1539     1  0 19:17 ?        00:00:00 /usr/pgsql-12/bin/repmgrd -f /etc/repmgr/12/repmgr.conf -p /run/repmgr/repmgrd-12.pid -d --verbose

另外,我们还可以从主节点或备节点检查服务启动事件:

/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf cluster event --event=repmgrd_start

最后,我们可以在任何主节点、备节点上检查syslog的守护程序输出。默认情况下Repmgr进程的日志将输出到系统日志文件(/var/log/message)中,例如:

cat /var/log/messages |grep --color=auto repmgrd |more

本例中,我们已将Repmgr进程的日志独立输出到var/log/repmgr/repmgr.log中,因此,只需检查这个文件即可:

cat /var/log/repmgr/repmgrd.log

7.集群启动与关闭

集群的启动与关闭均使用postgres用户在各节点上进行操作。

7.1 启动集群

在所有节点(repmgr01-repmgr04)上,启动PostgreSQL Server数据库实例:

start_pgsql
ps -ef |grep postgres

在主节点检查流复制状态:

psql -Upostgres -dpostgres -c "select * from pg_stat_replication;"
psql -Upostgres -dpostgres -c "select * from pg_replication_slots;"

在所有节点(repmgr01-repmgr04)上,启动repmgr进程:

/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf daemon start

7.2 关闭集群

在所有节点(repmgr01-repmgr04)上,停止repmgr进程:

repmgr daemon stop

在所有节点(repmgr01-repmgr04)上,停止PostgreSQL Server数据库实例:

stop_pgsql
ps -ef |grep postgres

8.集群切换测试

8.1 Switchover测试

repmgr提供switchover命令,可以在线将从节点提升为主节点,并保持其他节点自动跟随新的主节点的功能。

首先,检查当前集群状态如下:

[postgres@repmgr01 ~]$ show_repmgr_cluster 
 ID | Name     | Role    | Status    | Upstream | Location | Prio. | TLI
----+----------+---------+-----------+----------+----------+-------+-----
 1  | repmgr01 | standby |   running | repmgr03 | my-dc1   | 30    | 8   
 2  | repmgr02 | standby |   running | repmgr03 | my-dc1   | 20    | 8   
 3  | repmgr03 | primary | * running |          | my-dc1   | 10    | 8   
 9  | repmgr04 | witness | * running | repmgr03 | my-dc1   | 0     | n/

当前集群中repmgr03为Master节点。假设我们需要将repmgr01节点提升为新的主节点,那么我们需要在repmgr01节点上执行如下操作:

[postgres@repmgr01 ~]$ repmgr standby switchover --siblings-follow --dry-run
NOTICE: checking switchover on node "repmgr01" (ID: 1) in --dry-run mode
INFO: SSH connection to host "192.168.29.193" succeeded
INFO: able to execute "repmgr" on remote host "192.168.29.193"
INFO: all sibling nodes are reachable via SSH
INFO: 3 walsenders required, 10 available
INFO: demotion candidate is able to make replication connection to promotion candidate
INFO: 0 pending archive files
INFO: replication lag on this standby is 0 seconds
INFO: 3 replication slots required, 10 available
INFO: would pause repmgrd on node "repmgr01" (ID 1)
INFO: would pause repmgrd on node "repmgr02" (ID 2)
INFO: would pause repmgrd on node "repmgr03" (ID 3)
INFO: would pause repmgrd on node "repmgr04" (ID 9)
NOTICE: local node "repmgr01" (ID: 1) would be promoted to primary; current primary "repmgr03" (ID: 3) would be demoted to standby
INFO: following shutdown command would be run on node "repmgr03":
  "/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast stop"
INFO: parameter "shutdown_check_timeout" is set to 60 seconds
INFO: prerequisites for executing STANDBY SWITCHOVER are met

可以看到,使用--dry-run命令进行预切换检查正常,接下来可以正式进行swiitchover操作:

[postgres@repmgr01 ~]$ repmgr standby switchover --siblings-follow
NOTICE: executing switchover on node "repmgr01" (ID: 1)
NOTICE: local node "repmgr01" (ID: 1) will be promoted to primary; current primary "repmgr03" (ID: 3) will be demoted to standby
NOTICE: stopping current primary node "repmgr03" (ID: 3)
NOTICE: issuing CHECKPOINT on node "repmgr03" (ID: 3) 
DETAIL: executing server command "/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log -m fast stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/AD000028
NOTICE: promoting standby to primary
DETAIL: promoting server "repmgr01" (ID: 1) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "repmgr01" (ID: 1) was successfully promoted to primary
INFO: local node 3 can attach to rejoin target node 1
DETAIL: local node's recovery point: 0/AD000028; rejoin target node's fork point: 0/AD0000A0
INFO: creating replication slot as user "repmgr"
NOTICE: setting node 3's upstream to node 1
WARNING: unable to ping "host=192.168.29.193 port=5432 user=repmgr dbname=repmgr password=repmgr connect_timeout=3 application_name=repmgr03 keepalives=1 keepalives_idle=6 keepalives_interval=3 keepalives_count=3"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
NOTICE: starting server using "/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log start"
NOTICE: replication slot "repmgr_slot_1" deleted on node 3
WARNING: 1 inactive replication slots detected
DETAIL: inactive replication slots:
  - repmgr_slot_2 (physical)
HINT: these replication slots may need to be removed manually
NOTICE: NODE REJOIN successful
DETAIL: node 3 is now attached to node 1
NOTICE: node  "repmgr01" (ID: 1) promoted to primary, node "repmgr03" (ID: 3) demoted to standby
NOTICE: executing STANDBY FOLLOW on 2 of 2 siblings
INFO:  node 9 received notification to follow node 1
INFO: STANDBY FOLLOW successfully executed on all reachable sibling nodes
NOTICE: switchover was successful
DETAIL: node "repmgr01" is now primary and node "repmgr03" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully

再次检查集群状态:

[postgres@repmgr01 ~]$ show_repmgr_cluster 
 ID | Name     | Role    | Status    | Upstream | Location | Prio. | TLI
----+----------+---------+-----------+----------+----------+-------+-----
 1  | repmgr01 | primary | * running |          | my-dc1   | 30    | 9   
 2  | repmgr02 | standby |   running | repmgr01 | my-dc1   | 20    | 8   
 3  | repmgr03 | standby |   running | repmgr01 | my-dc1   | 10    | 8   
 9  | repmgr04 | witness | * running | repmgr01 | my-dc1   | 0     | n/a 

可以看到,已经switchover成功。

8.2 Failover测试

本节中我们通过关闭主节点数据库来模拟主库故障,观察集群时候能够正常进行故障转移:

首先,查看当前集群状态,以及节点的优先级设置:

[postgres@repmgr01 ~]$ show_repmgr_cluster 
 ID | Name     | Role    | Status    | Upstream | Location | Prio. | TLI
----+----------+---------+-----------+----------+----------+-------+-----
 1  | repmgr01 | standby |   running | repmgr02 | my-dc1   | 30    | 9   
 2  | repmgr02 | primary | * running |          | my-dc1   | 20    | 10  
 3  | repmgr03 | standby |   running | repmgr01 | my-dc1   | 10    | 8   
 9  | repmgr04 | witness | * running | repmgr01 | my-dc1   | 0     | n/a

从上可知,当前集群主节点为repmgr02,两个备节点repmgr01的优先级为30,repmgr03的优先级为10。因此,若主库故障,集群能够顺利进行故障转移,则预计节点repmgr01会自动成为新的主节点。

接下来我们在节点repmgr02上关闭主库:

[postgres@repmgr02 12]$ stop_pgsql 
waiting for server to shut down...... done
server stopped

在节点repmgr01上观察集群状态,根据配置文件参数配置的不同,持续的时间可能有所不同。最终节点repmgr01成为新的主节点,但repmgr02被标记为failed状态,如下所示:

[postgres@repmgr01 ~]$ show_repmgr_cluster 
 ID | Name     | Role    | Status    | Upstream | Location | Prio. | TLI
----+----------+---------+-----------+----------+----------+-------+-----
 1  | repmgr01 | primary | * running |          | my-dc1   | 30    | 11  
 2  | repmgr02 | primary | - failed  | ?        | my-dc1   | 20    |     
 3  | repmgr03 | standby |   running | repmgr01 | my-dc1   | 10    | 8   
 9  | repmgr04 | witness | * running | repmgr01 | my-dc1   | 0     | n/a 

WARNING: following issues were detected
  - unable to connect to node "repmgr02" (ID: 2)

HINT: execute with --verbose option to see connection error messages

接下来我们需要恢复故障的repmgr02节点。在repmgr02节点上执行如下操作:

[postgres@repmgr02 12]$ repmgr node rejoin -hrepmgr01 -Urepmgr -drepmgr --dry-run
INFO: replication slots in use, 1 free slots on node 9
INFO: replication connection to the rejoin target node was successful
INFO: local and rejoin target system identifiers match
DETAIL: system identifier is 6907151792586435991
INFO: local node 2 can attach to rejoin target node 1
DETAIL: local node's recovery point: 0/AF000028; rejoin target node's fork point: 0/AF0000A0
INFO: prerequisites for executing NODE REJOIN are met

检查通过,可以直接rejoin repmgr02节点到集群中:

[postgres@repmgr02 12]$ repmgr node rejoin -hrepmgr01 -Urepmgr -drepmgr 
INFO: local node 2 can attach to rejoin target node 1
DETAIL: local node's recovery point: 0/AF000028; rejoin target node's fork point: 0/AF0000A0
INFO: creating replication slot as user "repmgr"
NOTICE: setting node 2's upstream to node 1
WARNING: unable to ping "host=192.168.29.192 port=5432 user=repmgr dbname=repmgr password=repmgr connect_timeout=3 application_name=repmgr02 keepalives=1 keepalives_idle=6 keepalives_interval=3 keepalives_count=3"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
NOTICE: starting server using "/usr/pgsql-12/bin/pg_ctl -D /data/pgdata/12 -l /data/pgdata/12/postgresql.log start"
NOTICE: replication slot "repmgr_slot_1" deleted on node 2
NOTICE: NODE REJOIN successful
DETAIL: node 2 is now attached to node 1

然后检查集群状态:

[postgres@repmgr02 12]$ show_repmgr_cluster 
 ID | Name     | Role    | Status    | Upstream | Location | Prio. | TLI
----+----------+---------+-----------+----------+----------+-------+-----
 1  | repmgr01 | primary | * running |          | my-dc1   | 30    | 11  
 2  | repmgr02 | standby |   running | repmgr01 | my-dc1   | 20    | 10  
 3  | repmgr03 | standby |   running | repmgr01 | my-dc1   | 10    | 8   
 9  | repmgr04 | witness | * running | repmgr01 | my-dc1   | 0     | n/a 
  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值