pg14.5+repmgr高可用手动切换报错：INFO: sleeping 1 of maximum 30 seconds waiting for standby to flush received WAL to disk

我来答

2023-07-09

pg14.5+repmgr高可用手动切换报错：INFO: sleeping 1 of maximum 30 seconds waiting for standby to flush received WAL to disk

pg14.5+repmgr高可用

PostgreSQL+repmgr高可用部署

一.部署环境

操作系统：CentOS Linux release 7.9.2009 (Core)

PostgreSQL版本：PostgreSQL 14.5

repmgr版本：repmgr-5.3.3

二.repmgr介绍

2.1.repmger下载

https://www.repmgr.org/downloads.html

https://www.repmgr.org/download/repmgr-5.3.3.tar.gz

2.2.repmgr介绍

repmgr 是一个开源工具套件，用于管理 PostgreSQL 服务器集群中的复制和故障转移。它增强了PostgreSQL的内置热备用功能，提供了设置备用服务器、监控复制和执行管理任务（如故障转移或手动切换操作）的工具。

自从PostgreSQL的内置复制机制在9.0中引入以来，repmgr已经为它们提供了高级支持。当前的 repmgr 系列 repmgr 5 支持从 PostgreSQL 9.3 引入的复制功能的最新发展，例如级联复制、时间轴切换和通过复制协议进行的基本备份。

它提供了两个主要工具：

工具	作用	具体用途
repmgr	用于执行管理任务的命令行工具	设置备用服务器，将备用服务器提升为主服务器，切换主服务器和备用服务器，显示复制群集中服务器的状态
repmgrd	主动监视复制群集中的服务器的守护程序	监视和记录复制性能，通过检测主数据库和提升最合适的备用服务器，向用户定义的群集中事件提供有关事件的通知可以执行任务的脚本，例如通过电子邮件发送警报

2.3.repmgr 版本对应支持的PostgreSQL版本

https://www.repmgr.org/docs/current/install-requirements.html

三.pg+repmgr安装

3.1.pg+repmgr安装配置(主节点)

cat pginstall_repmger_master.sh
#!/bin/bash
#上传操作系统镜像和安装包至当前目录
#更改参数：0.中package和iso
#更改参数：2.中目录变量和端口
#更改参数：3.密码
#更改参数：5.postgresql-14.5
#更改参数：8.密码
#更改参数：10.ip 端口  密码
#更改参数：12.repmgr密码
#更改参数：17.ip 端口
echo  "0.Determines whether the user is executing root"
isroot()
{
    if [ $USER != "root" ];then
        echo  "The user must be root,and now you user is $USER,please su to root."
        exit 1
    else
        echo  "Check root ... OK!"
    fi
}
isroot

install_directory=$(pwd)
package=postgresql-14.5.tar.gz
iso=CentOS-7-x86_64-DVD-2009.iso 
repmgr=repmgr-5.3.3.tar.gz


echo  "1.system parameter configure"
echo  "1.1.add sudo postgres"
sed -ri '/^root/a\postgres    ALL=(ALL)       NOPASSWD: ALL' /etc/sudoers

echo  "1.2.adjust system parameter"
optimizeSystemConf(){
conf_exist=$(cat /etc/sysctl.conf|grep postgres|wc -l)
if [ $conf_exist -eq 0 ]; then
    echo "optimize system core conf"
	cat >> /etc/sysctl.conf <<EOF
#add by postgres
sed -ri '/net.ipv4.ip_forward/s#0#1#' /etc/sysctl.conf
cat >> /etc/sysctl.conf <<-EOF
#关闭sysrq功能
kernel.sysrq = 0
#关闭路由转发
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
#处理无源路由的包
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
# 确保无人能修改路由表
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0
#关闭ipv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
#内核放弃建立连接之前发送SYN 包的数量
net.ipv4.tcp_syn_retries = 2
#内核放弃建立连接之前发送SYNACK 包的数量
net.ipv4.tcp_synack_retries = 2
#当keepalive 起用的时候，TCP 发送keepalive 消息的频度。缺省是2 小时
net.ipv4.tcp_keepalive_time = 600
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl =15
net.ipv4.tcp_retries1 = 3
net.ipv4.tcp_retries2 = 5
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_max_tw_buckets = 6000
#启用timewait 快速回收
net.ipv4.tcp_tw_recycle = 1
#开启重用。允许将TIME-WAIT sockets 重新用于新的TCP 连接
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 10
# 开启SYN洪水攻击保护
net.ipv4.tcp_syncookies = 1
#限制仅仅是为了防止简单的DoS 攻击
net.ipv4.tcp_max_orphans = 3276800
#未收到客户端确认信息的连接请求的最大值
net.ipv4.tcp_max_syn_backlog = 16384
#限制仅仅是为了防止简单的DoS 攻击
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
fs.file-max = 1024000
#允许系统打开的端口范围
net.ipv4.ip_local_port_range = 1024 65000
net.ipv4.route.gc_timeout = 100
#系统中每一个端口最大的监听队列的长度,这是个全局的参数,默认值为128
net.core.somaxconn=1024
#每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
net.core.netdev_max_backlog = 262144
EOF
else
   echo "system configuration is already optimized, so we do nothing"
fi
}
optimizeSystemConf

echo  "1.3.adjust Optimize Limit"
optimizeLimitConf(){
conf_exist=$(cat /etc/security/limits.conf|grep postgres|wc -l)
if [ $conf_exist -eq 0 ]; then
	echo "optimize limit configuration"
	cat >> /etc/security/limits.conf << "EOF"
#add by postgres
cat >> /etc/security/limits.conf << EOF
postgres    soft    nproc    16384
postgres    hard    nproc    16384
postgres    soft    nofile    65536
postgres    hard    nofile    65536
postgres    soft    stack    1024000
postgres    hard    stack    1024000
EOF
else
	echo "limit is already optimized, so we do nothing"
fi
}
optimizeLimitConf

echo  "1.4.adjust optimize selinux"
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
setenforce 0 

echo  "1.5.off firwalld -- this must user do it myself"
function conf_firewall() {
##################gt>0
if [ $(systemctl status firewalld.service | grep -c running) -gt 0 ]; then  
     systemctl stop firewalld.service
     systemctl disable firewalld.service 
     #firewall-cmd --zone=public --add-port=15400/tcp --permanent
     #firewall-cmd --zone=public --add-port=22/tcp --permanent
     #firewall-cmd --reload
     #禁用防火墙区域偏移
     sed -i 's/^AllowZoneDrifting=yes/AllowZoneDrifting=no/' /etc/firewalld/firewalld.conf 
   else
   echo "firewall not open"
fi
}
conf_firewall


echo  ""1.6.IPC, some system have this, so do it by user 配置防火墙策略"logind进程cpu占用100%处理"
#CentOS openEuler操作系统默认为关闭，可以跳过该步骤
#sed -i 's/#RemoveIPC=no/RemoveIPC=no/g' /etc/systemd/logind.conf
#systemctl daemon-reload
#systemctl restart systemd-logind
sed -i 's/#RemoveIPC=no/RemoveIPC=no/g' /etc/systemd/logind.conf
systemctl daemon-reload
systemctl restart systemd-logind

echo  "1.7.挂载操作系统镜像"
mount $install_directory/$iso /mnt/
cat << EOF >> /etc/fstab
/dev/sr0    /mnt        iso9660 loop            0 0
EOF

mkdir -p /etc/yum.repos.d/bak
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
cat >> /etc/yum.repos.d/os.repo <<"EOF"
[OS1]
name=OS
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF

echo  "2. db variable list"
FILE_CONF=/data/pgdb/data5785/postgresql.conf
HBA_CONF=/data/pgdb/data5785/pg_hba.conf
PGDATA=/data/pgdb/data5785
PGHOME=/data/pgdb/pgsql
SCRIPTS_DIR=/data/pgdb/scripts
LOGPATH=/data/pgdb/data5785/log
PORT=5785

echo  "3. postgres exits"
id $postgres >& /dev/null
if [ $? -ne 0 ]
then
        echo "postgres already exits"
else 
        echo "postgres not exits，please create"
        groupadd postgres
        useradd -g postgres postgres
		echo "Admin@2023"|passwd --stdin  postgres
		sed -ri '/^root/a\postgres ALL=(ALL) ALL' /etc/sudoers
fi

echo  "4.create directory"
if [ ! -d /data/ ]
then
        mkdir -p /data
fi
if [ ! -d /data/pgdb ]
then
        cd /data
	    mkdir -p pgdb/{data5785,pg_archive,pg_backup,scripts,tmp}
fi

echo "5. unzip"
cd $install_directory
tar -zxf $package -C /data/pgdb
echo "pgsql upzip success"
echo "directory rights"
mv /data/pgdb/postgresql-14.5 /data/pgdb/pgsql
chown -R postgres:postgres /data/pgdb
#-------------------------------install pgsql------------------------------------
echo "6.install dependency package"
#强制关掉yum进程
rm -f /var/run/yum.pid 
yum install -y zlib zlib-devel libaio gcc gcc-c++ readline readline-devel perl bison flex libyaml python* net-tools expect
echo "start configure"
cd /data/pgdb/pgsql
./configure --prefix=/data/pgdb/pgsql --with-pgport=$PORT
if [ $? == 0 ]
then
        echo "configure配置通过，开始进行make编译"
		#gmake一次性将文档及附加模块全部进行编译和安装,保证所有数据库软件的一致性，避免给后期维护操作带来麻烦
        gmake world
        if [ $? == 0 ]
        then
                echo "make编译通过，开始进行make install安装步骤"
                gmake install-world
                if [ $? != 0 ];then
                        echo "make install安装失败"
                fi
                echo "安装成功"
        else
                echo "make编译失败，检查错误。"
        fi
else
        echo "configure检查配置失败，请查看错误进行安装库文件"
fi
echo "7.添加环境变量,进入postgres用户的家目录"
cd /home/postgres
if [ -f .bash_profile ] ;then
    cp .bash_profile .bash_profile.bak
	sed -i 's/^export PATH/#export PATH/' .bash_profile
	echo "export PGHOME=/data/pgdb/pgsql" >> .bash_profile
    echo "export PGDATA=/data/pgdb/data5785" >> .bash_profile
    echo "export PATH=$PGHOME/bin:$PATH" >> .bash_profile
    echo "export MANPATH=$PGHOME/share/man:$MANPATH" >> .bash_profile
    echo "export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH" >> .bash_profile
	echo "export LANG="en_US.UTF-8"" >> .bash_profile
	echo "export DATE=`date +"%Y%m%d%H%M"`" >> .bash_profile
	source /home/postgres/.bash_profile	
fi
echo  "8. 开始进行pgsql的配置"
echo "切换至postgres用户来初始化数据库，设置密码文件"
su - postgres -c "echo "Admin@2023">> .pgpass"
su - postgres -c "echo "192.168.183.109:5785:repmgr:repmgr:Admin@2023">> .pgpass"
su - postgres -c "echo "192.168.183.110:5785:repmgr:repmgr:Admin@2023">> .pgpass"
su - postgres -c "chmod 0600 /home/postgres/.pgpass"
su - postgres -c "/data/pgdb/pgsql/bin/initdb  --username=postgres --pwfile=/home/postgres/.pgpass -D /data/pgdb/data5785 --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8"
if [ $? == 0 ]
 then
    echo "初始化成功"
 else 
    echo "初始化失败"
fi	
echo "configure param"

cp $FILE_CONF $PGDATA/postgresql.confbak
sed -i "/^#listen_addresses = 'localhost'/s/#listen_addresses = 'localhost'/listen_addresses = '*'/" $FILE_CONF
sed -i "s/^#port = 5432/port = $PORT/" $FILE_CONF
sed -i 's/max_connections = 100/max_connections = 500/' $FILE_CONF
sed -i "/^#wal_level/s/^#//" $FILE_CONF #去掉注释
sed -i 's/#archive_mode = off/archive_mode = on/' $FILE_CONF
sed -i "/^#archive_command = ''/s/#archive_command = ''/archive_command ='cp %p \/data\/pgdb\/pg_archive\/%f'/" $FILE_CONF  
sed -i "/^#log_destination = 'stderr'/s/#log_destination = 'stderr'/log_destination = 'csvlog'/" $FILE_CONF
sed -i "/^#logging_collector = off/s/#logging_collector = off/logging_collector = on/" $FILE_CONF
sed -i "/^#log_directory = 'log'/s/^#//" $FILE_CONF #去掉注释
sed -i "/^#log_filename/s/^#//" $FILE_CONF #去掉注释
sed -i "/^#log_file_mode/s/^#//" $FILE_CONF #去掉注释
sed -i "/^#log_rotation_age/s/^#//" $FILE_CONF #去掉注释
sed -i "/^#log_rotation_size/s/^#//" $FILE_CONF #去掉注释
sed -i "/^shared_buffers = 128MB/s/shared_buffers = 128MB/shared_buffers = 1024MB/" $FILE_CONF #物理内存25~40%
sed -i "/^#work_mem = 4MB/s/#work_mem = 4MB/work_mem = 30MB/" $FILE_CONF
sed -i "/^#maintenance_work_mem = 64MB/s/#maintenance_work_mem = 64MB/maintenance_work_mem = 256MB/" $FILE_CONF
sed -i "/^#temp_buffers = 8MB/s/#temp_buffers = 8MB/temp_buffers = 256MB/" $FILE_CONF
sed -i "/^#max_wal_senders/s/^#//" $FILE_CONF #去掉注释
sed -i "/^#max_replication_slots/s/^#//" $FILE_CONF #去掉注释
sed -i "/^#hot_standby/s/^#//" $FILE_CONF #去掉注释


echo "host    all             all             0.0.0.0/0               md5" >> $HBA_CONF 
echo "local   replication     repmgr                                  trust" >> $HBA_CONF 
echo "host    replication     repmgr          127.0.0.1/32            trust" >> $HBA_CONF 
echo "host    replication     repmgr          192.168.183.0/24               trust" >> $HBA_CONF 
echo "local   repmgr          repmgr                                  trust" >> $HBA_CONF 
echo "host    repmgr          repmgr          127.0.0.1/32            trust" >> $HBA_CONF 
echo "host    repmgr          repmgr          192.168.183.0/24               trust" >> $HBA_CONF 

echo  "9. auto starting up"
cat > /usr/lib/systemd/system/postgres.service << "EOF"
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5785
Environment=PGDATA=/data/pgdb/data5785
OOMScoreAdjust=-1000
ExecStart=/data/pgdb/pgsql/bin/pg_ctl start -D $PGDATA
ExecStop=/data/pgdb/pgsql/pg_ctl stop -D $PGDATA -s -m fast
ExecReload=/data/pgdb/pgsql/bin/pg_ctl reload -D $PGDATA -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF
chmod +x /usr/lib/systemd/system/postgres.service
systemctl daemon-reload
systemctl start postgres.service
systemctl enable postgres.service
#判断是否启动成功
process=$(ps -ef | grep  -v 'grep'| grep  '/data/pgdb/pgsql/bin/postgres'|awk '{print $2}')
if [ -n "$process"  ];then  #检测字符串长度是否不为 0，不为 0 返回 true。
    echo "install success ans start success"
else
    echo "install fail"
fi
echo "---------------------------切归档------------------------------------------------------"
su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"select pg_switch_wal();\""
echo  "10.configure crontab"
if [[ -e /var/spool/cron/postgres ]]; then
  cp /var/spool/cron/postgres /var/spool/cron/postgresbak
else
  touch /var/spool/cron/postgres
fi
chown -R postgres:postgres $SCRIPTS_DIR
chmod +x $SCRIPTS_DIR/*
cat >> /var/spool/cron/postgres << "EOF"
# PostgresBegin
#设置归档策略:每晚零点10分删除7天前归档文件，视情况更改成1个月
10 00 * * * find /data/pgdb/pg_archive -type f -name "0000000*" -mtime +7 -exec rm {} \; > /dev/null 2>&1
#设置日志保留2天，视情况更改成半年或3个月
00 01 * * * find /data/pgdb/data5785/log -type f -name "postgresql*.log" -mtime +2 -exec rm {} \; > /dev/null 2>&1
00 01 * * * find /data/pgdb/data5785/log -type f -name "postgresql*.csv" -mtime +2 -exec rm {} \; > /dev/null 2>&1
EOF
echo "11.数据库信息"
echo "操作系统数据库用户：postgres;密码：Admin@2023"
echo "数据库信息:postgres;密码：Admin@2023;port:5785"
echo "12.主库创建repmgr库存储元数据"
su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"create user repmgr with superuser password 'Admin@2023';\""
su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"create database repmgr owner  repmgr;\""
#echo "13.创建业务库和用户"
#su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"create user zcst with encrypted password 'Admin@2023';\""
#su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"create database test owner zcst;\""
#su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"grant all privileges on database test to zcst;\""
#echo "14.创建只读用户"
#su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"create user zcst_read with encrypted password 'Admin@2023';\""
#设置用户默认开启只读事务
#su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"alter user zcst_read set default_transaction_read_only=on;\""
#将public schema中usage权限赋予给zcst_read用户,访问所有已存在的表
#su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"grant usage on schema public to zcst_read;\""
#将public schema中表的查询权限赋予给zcst_read用户,访问所有已存在的表
#su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"grant select on all tables in schema public to zcst_read;\""
#未来访问public模式下所有新建的表
#su - postgres -c "/data/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5785 -c \"alter default privileges in schema public grant select on tables to zcst_read;\""
echo "15.安装repmger依赖"
yum check-update
yum groupinstall -y "Development Tools" 
yum install -y yum-utils openjade docbook-dtds docbook-style-dsssl docbook-style-xsl
yum install -y  yum-builddep flex libselinux-devel libxml2-devel libxslt-devel openssl-devel pam-devel readline-devel
echo "16.解压安装repmgr（主备库都安装）"
tar -xvf $install_directory/$repmgr -C /data/pgdb
chown -R postgres:postgres /data/pgdb
su - postgres -c "cd /data/pgdb/repmgr-5.3.3/ && ./configure"
su - postgres -c "cd /data/pgdb/repmgr-5.3.3/ && make install"
echo "17.主库创建配置文件repmgr.conf"
cat > /data/pgdb/repmgr-5.3.3/repmgr.conf << "EOF"
node_id=1
node_name='node1'
conninfo='host=192.168.183.109 port=5785  user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/data/pgdb/data5785'
pg_bindir='/data/pgdb/pgsql/bin'
EOF
chown -R postgres:postgres /data/pgdb

3.2.pg+repmgr安装(备节点)

cat pginstall_repmger_slave.sh
#!/bin/bash
#上传操作系统镜像和安装包至当前目录
#更改参数：0.中package和iso
#更改参数：2.中目录变量和端口
#更改参数：3.密码
#更改参数：5.postgresql-14.5
#更改参数：8.密码
#更改参数：10.端口  密码
echo  "0.Determines whether the user is executing root"
isroot()
{
    if [ $USER != "root" ];then
        echo  "The user must be root,and now you user is $USER,please su to root."
        exit 1
    else
        echo  "Check root ... OK!"
    fi
}
isroot

install_directory=$(pwd)
package=postgresql-14.5.tar.gz
iso=CentOS-7-x86_64-DVD-2009.iso 
repmgr=repmgr-5.3.3.tar.gz


echo  "1.system parameter configure"
echo  "1.1.add sudo postgres"
sed -ri '/^root/a\postgres    ALL=(ALL)       NOPASSWD: ALL' /etc/sudoers

echo  "1.2.adjust system parameter"
optimizeSystemConf(){
conf_exist=$(cat /etc/sysctl.conf|grep postgres|wc -l)
if [ $conf_exist -eq 0 ]; then
    echo "optimize system core conf"
	cat >> /etc/sysctl.conf <<EOF
#add by postgres
sed -ri '/net.ipv4.ip_forward/s#0#1#' /etc/sysctl.conf
cat >> /etc/sysctl.conf <<-EOF
#关闭sysrq功能
kernel.sysrq = 0
#关闭路由转发
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
#处理无源路由的包
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
# 确保无人能修改路由表
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0
#关闭ipv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
#内核放弃建立连接之前发送SYN 包的数量
net.ipv4.tcp_syn_retries = 2
#内核放弃建立连接之前发送SYNACK 包的数量
net.ipv4.tcp_synack_retries = 2
#当keepalive 起用的时候，TCP 发送keepalive 消息的频度。缺省是2 小时
net.ipv4.tcp_keepalive_time = 600
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl =15
net.ipv4.tcp_retries1 = 3
net.ipv4.tcp_retries2 = 5
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_max_tw_buckets = 6000
#启用timewait 快速回收
net.ipv4.tcp_tw_recycle = 1
#开启重用。允许将TIME-WAIT sockets 重新用于新的TCP 连接
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 10
# 开启SYN洪水攻击保护
net.ipv4.tcp_syncookies = 1
#限制仅仅是为了防止简单的DoS 攻击
net.ipv4.tcp_max_orphans = 3276800
#未收到客户端确认信息的连接请求的最大值
net.ipv4.tcp_max_syn_backlog = 16384
#限制仅仅是为了防止简单的DoS 攻击
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
fs.file-max = 1024000
#允许系统打开的端口范围
net.ipv4.ip_local_port_range = 1024 65000
net.ipv4.route.gc_timeout = 100
#系统中每一个端口最大的监听队列的长度,这是个全局的参数,默认值为128
net.core.somaxconn=1024
#每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
net.core.netdev_max_backlog = 262144
EOF
else
   echo "system configuration is already optimized, so we do nothing"
fi
}
optimizeSystemConf

echo  "1.3.adjust Optimize Limit"
optimizeLimitConf(){
conf_exist=$(cat /etc/security/limits.conf|grep postgres|wc -l)
if [ $conf_exist -eq 0 ]; then
	echo "optimize limit configuration"
	cat >> /etc/security/limits.conf << "EOF"
#add by postgres
cat >> /etc/security/limits.conf << EOF
postgres    soft    nproc    16384
postgres    hard    nproc    16384
postgres    soft    nofile    65536
postgres    hard    nofile    65536
postgres    soft    stack    1024000
postgres    hard    stack    1024000
EOF
else
	echo "limit is already optimized, so we do nothing"
fi
}
optimizeLimitConf

echo  "1.4.adjust optimize selinux"
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
setenforce 0 

echo  "1.5.off firwalld -- this must user do it myself"
function conf_firewall() {
##################gt>0
if [ $(systemctl status firewalld.service | grep -c running) -gt 0 ]; then  
     systemctl stop firewalld.service
     systemctl disable firewalld.service 
     #firewall-cmd --zone=public --add-port=15400/tcp --permanent
     #firewall-cmd --zone=public --add-port=22/tcp --permanent
     #firewall-cmd --reload
     #禁用防火墙区域偏移
     sed -i 's/^AllowZoneDrifting=yes/AllowZoneDrifting=no/' /etc/firewalld/firewalld.conf 
   else
   echo "firewall not open"
fi
}
conf_firewall


echo  ""1.6.IPC, some system have this, so do it by user 配置防火墙策略"logind进程cpu占用100%处理"
#CentOS openEuler操作系统默认为关闭，可以跳过该步骤
#sed -i 's/#RemoveIPC=no/RemoveIPC=no/g' /etc/systemd/logind.conf
#systemctl daemon-reload
#systemctl restart systemd-logind
sed -i 's/#RemoveIPC=no/RemoveIPC=no/g' /etc/systemd/logind.conf
systemctl daemon-reload
systemctl restart systemd-logind

echo  "1.7.挂载操作系统镜像"
mount $install_directory/$iso /mnt/
cat << EOF >> /etc/fstab
/dev/sr0    /mnt        iso9660 loop            0 0
EOF

mkdir -p /etc/yum.repos.d/bak
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
cat >> /etc/yum.repos.d/os.repo <<"EOF"
[OS1]
name=OS
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF

echo  "2. db variable list"
FILE_CONF=/data/pgdb/data5785/postgresql.conf
HBA_CONF=/data/pgdb/data5785/pg_hba.conf
PGDATA=/data/pgdb/data5785
PGHOME=/data/pgdb/pgsql
SCRIPTS_DIR=/data/pgdb/scripts
LOGPATH=/data/pgdb/data5785/log
PORT=5785

echo  "3. postgres exits"
id $postgres >& /dev/null
if [ $? -ne 0 ]
then
        echo "postgres already exits"
else 
        echo "postgres not exits，please create"
        groupadd postgres
        useradd -g postgres postgres
		echo "Admin@2023"|passwd --stdin  postgres
		sed -ri '/^root/a\postgres ALL=(ALL) ALL' /etc/sudoers
fi

echo  "4.create directory"
if [ ! -d /data/ ]
then
        mkdir -p /data
fi
if [ ! -d /data/pgdb ]
then
        cd /data
	    mkdir -p pgdb/{data5785,pg_archive,pg_backup,scripts,tmp}
fi

echo "5. unzip"
cd $install_directory
tar -zxf $package -C /data/pgdb
echo "pgsql upzip success"
echo "directory rights"
mv /data/pgdb/postgresql-14.5 /data/pgdb/pgsql
chown -R postgres:postgres /data/pgdb
#-------------------------------install pgsql------------------------------------
echo "6.install dependency package"
#强制关掉yum进程
rm -f /var/run/yum.pid 
yum install -y zlib zlib-devel libaio gcc gcc-c++ readline readline-devel perl bison flex libyaml python* net-tools expect
echo "start configure"
cd /data/pgdb/pgsql
./configure --prefix=/data/pgdb/pgsql --with-pgport=$PORT
if [ $? == 0 ]
then
        echo "configure配置通过，开始进行make编译"
		#gmake一次性将文档及附加模块全部进行编译和安装,保证所有数据库软件的一致性，避免给后期维护操作带来麻烦
        gmake world
        if [ $? == 0 ]
        then
                echo "make编译通过，开始进行make install安装步骤"
                gmake install-world
                if [ $? != 0 ];then
                        echo "make install安装失败"
                fi
                echo "安装成功"
        else
                echo "make编译失败，检查错误。"
        fi
else
        echo "configure检查配置失败，请查看错误进行安装库文件"
fi
echo "7.添加环境变量,进入postgres用户的家目录"
cd /home/postgres
if [ -f .bash_profile ] ;then
    cp .bash_profile .bash_profile.bak
	sed -i 's/^export PATH/#export PATH/' .bash_profile
	echo "export PGHOME=/data/pgdb/pgsql" >> .bash_profile
    echo "export PGDATA=/data/pgdb/data5785" >> .bash_profile
    echo "export PATH=$PGHOME/bin:$PATH" >> .bash_profile
    echo "export MANPATH=$PGHOME/share/man:$MANPATH" >> .bash_profile
    echo "export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH" >> .bash_profile
	echo "export LANG="en_US.UTF-8"" >> .bash_profile
	echo "export DATE=`date +"%Y%m%d%H%M"`" >> .bash_profile
	source /home/postgres/.bash_profile	
fi
echo  "8. 开始进行pgsql的配置"
echo "切换至postgres用户来初始化数据库，设置密码文件"
su - postgres -c "echo "Admin@2023">> .pgpass"
su - postgres -c "echo "192.168.183.109:5785:repmgr:repmgr:Admin@2023">> .pgpass"
su - postgres -c "echo "192.168.183.110:5785:repmgr:repmgr:Admin@2023">> .pgpass"
su - postgres -c "chmod 0600 /home/postgres/.pgpass"

echo  "9. auto starting up"
cat > /usr/lib/systemd/system/postgres.service << "EOF"
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5785
Environment=PGDATA=/data/pgdb/data5785
OOMScoreAdjust=-1000
ExecStart=/data/pgdb/pgsql/bin/pg_ctl start -D $PGDATA
ExecStop=/data/pgdb/pgsql/pg_ctl stop -D $PGDATA -s -m fast
ExecReload=/data/pgdb/pgsql/bin/pg_ctl reload -D $PGDATA -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF
chmod +x /usr/lib/systemd/system/postgres.service
systemctl daemon-reload
systemctl enable postgres.service

echo "10.安装repmger依赖"
yum check-update
yum groupinstall -y "Development Tools" 
yum install -y yum-utils openjade docbook-dtds docbook-style-dsssl docbook-style-xsl
yum install -y  yum-builddep flex libselinux-devel libxml2-devel libxslt-devel openssl-devel pam-devel readline-devel
echo "11.解压安装repmgr（主备库都安装）"
tar -xvf $install_directory/$repmgr -C /data/pgdb
chown -R postgres:postgres /data/pgdb
su - postgres -c "cd /data/pgdb/repmgr-5.3.3/ && ./configure"
su - postgres -c "cd /data/pgdb/repmgr-5.3.3/ && make install"
echo "13.备库创建配置文件repmgr.conf"
cat > /data/pgdb/repmgr-5.3.3/repmgr.conf << "EOF"
node_id=2
node_name='node2'
conninfo='host=192.168.183.110 port=5785  user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/data/pgdb/data5785'
pg_bindir='/data/pgdb/pgsql/bin'
EOF
chown -R postgres:postgres /data/pgdb

四.免密配置（主备节点）

cat ssh.sh
#!/bin/bash
echo '192.168.183.109' >> $install_directory/hostname
echo '192.168.183.110' >> $install_directory/hostname
# 该脚本用于实现配置root免密登录到其他主机的功能
###################################################################
filepath=~/.ssh/id_rsa.pub
user=postgres
password=Admin@2023

auto_ssh_copy_id() {
  expect -c "set timeout -1;
  spawn ssh-copy-id -i $4 $2@$1;
  expect {
    *(yes/no)* {send -- yes\r;exp_continue;}
    *password:* {send -- $3\r;exp_continue;}
    eof { exit 0;}
  }";
}
# 判断本地的公钥是否存在，如果不存在则需要生成公钥
[ ! -f $filepath ] && {
  ssh-keygen -t rsa -P '' -f ~/.ssh/id_rsa
}

hosts=`cat $install_directory/hostname | grep -v "^#"`
# 对配置文件中的每一台主机进行免密登录操作
for serverIp in $hosts
do
  # echo $serverIp--$user--$password
  # 将公钥发送给需要免密登录的主机
  auto_ssh_copy_id $serverIp $user $password $filepath
done

验证免密登录

[root@localhost ~]# ssh 192.168.183.110
[root@localhost ~]# exit
[root@localhost ~]# ssh 192.168.183.109
[root@localhost ~]# exit

五.测试repmgr是否能免密访问主库(主节点)

[postgres@localhost ~]$ psql -p5785 -h 192.168.183.109 -Urepmgr
psql (14.5)
Type "help" for help.
repmgr=# \q

六.使用repmgr命令注册主库(主节点)

##注册主库
[postgres@localhost ~]$ /data/pgdb/pgsql/bin/repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf primary register --force
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered

##检查主节点状态
[postgres@localhost ~]$ repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                          
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 1        | host=192.168.183.109 port=5785  user=repmgr dbname=repmgr connect_timeout=2
 
 ##查看主节点repmgr库中元数据
 [postgres@localhost ~]$ psql -p5785 -h 192.168.183.109 -Urepmgr
psql (14.5)
Type "help" for help.

repmgr=# \x
Expanded display is on.
repmgr=# SELECT * FROM repmgr.nodes;
-[ RECORD 1 ]----+----------------------------------------------------------------------------
node_id          | 1
upstream_node_id | 
active           | t
node_name        | node1
type             | primary
location         | default
priority         | 100
conninfo         | host=192.168.183.109 port=5785  user=repmgr dbname=repmgr connect_timeout=2
repluser         | repmgr
slot_name        | 
config_file      | /data/pgdb/repmgr-5.3.3/repmgr.conf

七.使用repmgr命令克隆备库(备节点)

注意备库repmgr.conf文件中的目录要为空

7.1.克隆前检查(备节点)

[postgres@localhost ~]$/data/pgdb/pgsql/bin/repmgr -h 192.168.183.109 -p5785 -U repmgr -d repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf standby clone --dry-run
NOTICE: destination directory "/data/pgdb/data5785" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.183.109 port=5785 user=repmgr dbname=repmgr
DETAIL: current installation size is 33 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
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
NOTICE: standby will attach to upstream node 1
HINT: consider using the -c/--fast-checkpoint option
INFO: would execute:
  pg_basebackup -l "repmgr base backup"  -D /data/pgdb/data5785 -h 192.168.183.109 -p 5785 -U repmgr -X stream 
INFO: all prerequisites for "standby clone" are met

7.2.克隆(备节点)

[postgres@localhost ~]$ /data/pgdb/pgsql/bin/repmgr -h 192.168.183.109 -p5785 -U repmgr -d repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf standby clone 
NOTICE: destination directory "/data/pgdb/data5785" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.183.109 port=5785 user=repmgr dbname=repmgr
DETAIL: current installation size is 33 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)
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
INFO: checking and correcting permissions on existing directory "/data/pgdb/data5785"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
  pg_basebackup -l "repmgr base backup"  -D /data/pgdb/data5785 -h 192.168.183.109 -p 5785 -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/pgdb/data5785 start
HINT: after starting the server, you need to register this standby with "repmgr standby register"

7.3.启动备库(备节点)

[postgres@localhost ~]$ pg_ctl start -D /data/pgdb/data5785
waiting for server to start....2023-07-08 18:39:33.113 CST [48507] LOG:  redirecting log output to logging collector process
2023-07-08 18:39:33.113 CST [48507] HINT:  Future log output will appear in directory "log".
 done
server started

7.4.验证复制是否正常工作(备节点)

##验证复制是否正常工作，连接到主服务器并执行
[postgres@localhost ~]$ psql -p5785 -h 192.168.183.109 -Urepmgr 
psql (14.5)
Type "help" for help.
repmgr=# \x
Expanded display is on.
repmgr=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 28116
usesysid         | 16384
usename          | repmgr
application_name | node2
client_addr      | 192.168.183.110
client_hostname  | 
client_port      | 35782
backend_start    | 2023-07-08 18:45:30.624177+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/6000110
write_lsn        | 0/6000110
flush_lsn        | 0/6000110
replay_lsn       | 0/6000110
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-07-08 18:46:30.951872+08

八.使用repmgr命令注册备库(备节点)

##使用repmgr命令注册备库
[postgres@localhost ~]$ /data/pgdb/pgsql/bin/repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf standby  register 
INFO: connecting to local node "node2" (ID: 2)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID: 1)
INFO: standby registration complete
NOTICE: standby node "node2" (ID: 2) successfully registered

##查看集群主备状态
[postgres@localhost ~]$ repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                          
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 1        | host=192.168.183.109 port=5785  user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | standby |   running | node1    | default  | 100      | 1        | host=192.168.183.110 port=5785  user=repmgr dbname=repmgr connect_timeout=2

十.主备机切换(切换192.168.183.110为主)

9.1.主备机手动切换

[postgres@localhost ~]$ /data/pgdb/pgsql/bin/repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf  standby switchover -U repmgr --verbose  --force-rewind

9.2.查看集群主备状态

repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf cluster show

9.3.相关的表和视图

表名视图	用途	用途
repmgr.events	记录集群操作事件	table
repmgr.monitoring_history	历史备用监控信息	table
repmgr.nodes	每个服务器的连接和状态信息	table
repmgr.replication_status	启用 repmgrd 的监控后，会显示每个备用数据库的当前监视状态	view
repmgr.show_nodes	基于repmgr.nodes ，显示服务器连接状态信息	view

pg_stat_replication是一个视图，主要用于监控一个基于流的设置

十.问题

10.1.node “node2” (ID: 2) is not attached to its upstream node “node1” (ID: 1)

[postgres@localhost ~]$ repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf cluster show
WARNING: node "node2" not found in "pg_stat_replication"
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                          
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 1        | host=192.168.183.109 port=5785  user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | standby |   running | ! node1  | default  | 100      | 1        | host=192.168.183.110 port=5785  user=repmgr dbname=repmgr connect_timeout=2

WARNING: following issues were detected
  - node "node2" (ID: 2) is not attached to its upstream node "node1" (ID: 1)

10.2.主备机手动切换

[postgres@localhost ~]$ /data/pgdb/pgsql/bin/repmgr -f /data/pgdb/repmgr-5.3.3/repmgr.conf  standby switchover -U repmgr --verbose  --force-rewind 
NOTICE: using provided configuration file "/data/pgdb/repmgr-5.3.3/repmgr.conf"
WARNING: following problems with command line parameters detected:
  database connection parameters not required when executing STANDBY SWITCHOVER
NOTICE: executing switchover on node "node2" (ID: 2)
INFO: searching for primary node
INFO: checking if node 1 is primary
INFO: current primary node is 1
INFO: prerequisites for using pg_rewind are met
INFO: SSH connection to host "192.168.183.109" succeeded
INFO: 0 pending archive files
INFO: replication lag on this standby is 0 seconds
NOTICE: attempting to pause repmgrd on 2 nodes
NOTICE: local node "node2" (ID: 2) will be promoted to primary; current primary "node1" (ID: 1) will be demoted to standby
NOTICE: stopping current primary node "node1" (ID: 1)
NOTICE: issuing CHECKPOINT on node "node1" (ID: 1) 
DETAIL: executing server command "/data/pgdb/pgsql/bin/pg_ctl  -D '/data/pgdb/data5785' -W -m fast stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
INFO: checking for primary shutdown; 2 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/5000028
NOTICE: waiting up to 30 seconds (parameter "wal_receive_check_timeout") for received WAL to flush to disk
INFO: sleeping 1 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 2 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 3 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 4 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 5 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 6 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 7 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 8 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 9 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 10 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 11 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 12 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 13 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 14 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 15 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 16 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 17 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 18 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 19 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 20 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 21 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 22 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 23 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 24 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 25 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 26 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 27 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 28 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 29 of maximum 30 seconds waiting for standby to flush received WAL to disk
INFO: sleeping 30 of maximum 30 seconds waiting for standby to flush received WAL to disk
WARNING: local node "node2" is behind shutdown primary "node1"
DETAIL: local node last receive LSN is 0/4124000, primary shutdown checkpoint LSN is 0/5000028
NOTICE: aborting switchover
HINT: use --always-promote to force promotion of standby

我来答

添加附件

问题补充

2条回答

默认