postgresql主从集群一键搭建脚本分享
脚本1：
cat pg_ms_install.sh
#!/bin/bash
# 基础环境配置（保持不变）
setenforce 0 >/dev/null 2>&1 || true
sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
systemctl stop firewalld >/dev/null 2>&1 || true
systemctl disable firewalld >/dev/null 2>&1 || true
cd /etc/yum.repos.d/
rm -f ./*.repo
cat > centos.repo <<-EOF
[centos]
name=Local Repo
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF
cd
mount /dev/sr0 /mnt >/dev/null 2>&1 || true
yum clean all >/dev/null
yum makecache >/dev/null
yum install -y expect wget >/dev/null# 核心参数（根据实际环境修改）
LOCAL_HOST="192.168.59.141" # 本机IP
REMOTE_HOSTS=("192.168.59.136") # 远程主机列表
ALL_HOSTS=("$LOCAL_HOST" "${REMOTE_HOSTS[@]}")
USER="root"
PASSWORD="123123" # 远程主机密码（确保正确）
SSH_DIR="/root/.ssh"
CONNECT_TIMEOUT=5 # SSH连接超时时间（秒）# 函数：安全SSH交互（通用版，返回命令输出）
ssh_with_expect() {local host=$1local cmd=$2local output=$(/usr/bin/expect <<-EOFset timeout $CONNECT_TIMEOUTspawn ssh -o StrictHostKeyChecking=no $USER@$host "$cmd"expect {"*yes/no" { send "yes\r"; exp_continue }"*password:" { send "$PASSWORD\r"; exp_continue }timeout { send_user "连接超时：$host\n"; exit 1 }eof}set result \$expect_out(buffer)send_user \$resultexit 0
EOF)echo "$output"
}# 函数：生成SSH密钥对（支持本机/远程）
generate_ssh_key() {local host=$1if [ "$host" == "$LOCAL_HOST" ]; thenecho "生成本机[$host] SSH密钥对..."mkdir -p "$SSH_DIR" && chmod 700 "$SSH_DIR"ssh-keygen -t rsa -b 2048 -f "$SSH_DIR/id_rsa" -q -N ''elseecho "生成远程主机[$host] SSH密钥对..."ssh_with_expect "$host" "mkdir -p $SSH_DIR && chmod 700 $SSH_DIR && ssh-keygen -t rsa -b 2048 -f $SSH_DIR/id_rsa -q -N ''"fi
}# 函数：获取主机公钥（支持本机/远程）
get_host_pubkey() {local host=$1if [ "$host" == "$LOCAL_HOST" ]; thencat "$SSH_DIR/id_rsa.pub"elsessh_with_expect "$host" "cat $SSH_DIR/id_rsa.pub"fi
}# 函数：向目标主机写入公钥（支持本机/远程）
append_pubkey_to_host() {local src_host=$1local dest_host=$2local pubkey=$(get_host_pubkey "$src_host")if [ "$dest_host" == "$LOCAL_HOST" ]; thenecho "本机[$dest_host] 写入 $src_host 公钥..."echo "$pubkey" >> "$SSH_DIR/authorized_keys"chmod 600 "$SSH_DIR/authorized_keys"elseecho "远程主机[$dest_host] 写入 $src_host 公钥..."ssh_with_expect "$dest_host" "echo '$pubkey' >> $SSH_DIR/authorized_keys && chmod 600 $SSH_DIR/authorized_keys"fi
}# 步骤1：清理旧密钥（避免重复）
echo "清理旧SSH密钥..."
rm -f "$SSH_DIR/id_rsa" "$SSH_DIR/id_rsa.pub" "$SSH_DIR/authorized_keys"
for host in "${REMOTE_HOSTS[@]}"; dossh_with_expect "$host" "rm -f $SSH_DIR/id_rsa $SSH_DIR/id_rsa.pub $SSH_DIR/authorized_keys"
done# 步骤2：为所有主机生成新密钥对
for host in "${ALL_HOSTS[@]}"; dogenerate_ssh_key "$host"
done# 步骤3：配置本机自免密（核心修复）
echo "配置本机[$LOCAL_HOST]自免密..."
cat "$SSH_DIR/id_rsa.pub" >> "$SSH_DIR/authorized_keys"
chmod 600 "$SSH_DIR/authorized_keys"
chmod 700 "$SSH_DIR"# 步骤4：配置所有主机互免密
for src_host in "${ALL_HOSTS[@]}"; dofor dest_host in "${ALL_HOSTS[@]}"; doif [ "$src_host" != "$dest_host" ]; thenappend_pubkey_to_host "$src_host" "$dest_host"fidone
done# 验证免密（关键验证步骤）
echo -e "\n===== 免密登录验证 ====="
for host in "${ALL_HOSTS[@]}"; doecho -n "验证本机到 $host 免密："ssh -o ConnectTimeout=$CONNECT_TIMEOUT -o StrictHostKeyChecking=no $USER@$host "echo 验证成功" >/dev/null 2>&1 && echo "✅" || echo "❌"
done
echo "SSH key distribution completed. All hosts should be able to SSH into each other without a password."
echo "-----------------------------开始PG数据库安装--------------------------------------"
dir=$(pwd)
echo "db variable list"
BASEPATH=/pgdb
FILE_CONF=/pgdb/data/postgresql.conf
HBA_CONF=/pgdb/data/pg_hba.conf
PGDATA=/pgdb/data
PGHOME=/pgdb/pgsql
SCRIPTS_DIR=/pgdb/scripts
LOGPATH=/pgdb/data/log
PORT=5785
PASSWD="123456"
cpu=$(cat /proc/cpuinfo | grep 'physical id' | sort | uniq | wc -l)
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 ]; thenecho "optimize system core conf"sed -ri '/net.ipv4.ip_forward/s#0#1#' /etc/sysctl.confcat >> /etc/sysctl.conf <<EOF
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
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_synack_retries = 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
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_max_syn_backlog = 16384
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
net.core.somaxconn=1024
net.core.netdev_max_backlog = 262144
EOF
elseecho "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 ]; thenecho "optimize limit configuration"cat >> /etc/security/limits.conf << "EOF"
#add by postgres
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
elseecho "limit is already optimized, so we do nothing"
fi
}
optimizeLimitConfecho "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=5785/tcp --permanentfirewall-cmd --zone=public --add-port=22/tcp --permanentfirewall-cmd --reload#禁用防火墙区域偏移sed -i 's/^AllowZoneDrifting=yes/AllowZoneDrifting=no/' /etc/firewalld/firewalld.conf elseecho "firewall not open"
fi
}
conf_firewall
echo ""1.6.IPC, some system have this, so do it by user 配置防火墙策略"logind进程cpu占用100%处理"
sed -i 's/#RemoveIPC=no/RemoveIPC=no/g' /etc/systemd/logind.conf
systemctl daemon-reload
systemctl restart systemd-logind
echo "1.7.安装相关依赖"
# 获取当前所在目录位置
current_dir=$(pwd)
echo "当前所在目录位置: $current_dir"
# 目标路径
target_dir="/soft"
# 检查目标路径是否存在，如果不存在则创建
if [ ! -d "$target_dir" ]; thenmkdir -p "$target_dir"echo "已创建目录: $target_dir"
fi
# 移动当前目录下的所有文件到目标路径
mv $current_dir/pg_yum.tar.gz $target_dir
echo "已将当前目录下所有文件移动至 $target_dir"
yum install -y zlib-devel libaio cmake make gcc gcc-c++ readline readline-devel perl bison flex libyaml net-tools expect openssh-clients tcl openssl openssl-devel ncurses-devel python python-devel openldap pam systemtap-sdt-devel perl-ExtUtils-Embed libxml2 libxml2-devel libxslt libxslt-devel uuid-devel
echo "2. postgres exits"
id $postgres >& /dev/null
if [ $? -ne 0 ]
thenecho "postgres already exits"
else echo "postgres not exits，please create"groupadd postgresuseradd -g postgres postgresecho "$PASSWD"|passwd --stdin postgressed -ri '/^root/a\postgres ALL=(ALL) ALL' /etc/sudoers
fiecho "3.create directory"
if [ ! -d $BASEPATH ]
thenmkdir -p $BASEPATH/{data,pg_archive,pg_backup,scripts,tmp}
fiecho "4. unzip"
tar -zxf /opt/postgresql*.tar.gz -C $BASEPATH/
echo "pgsql upzip success"
echo "directory rights"
cd $BASEPATH
mv postgresql-14.12/ pgsql
chown -R postgres:postgres $BASEPATH
chmod -R 755 $BASEPATH
#-------------------------------install pgsql------------------------------------
echo "5.install dependency package"
cd $PGHOME
./configure --prefix=$PGHOME --with-pgport=$PORT --with-openssl --with-perl --with-python --with-blocksize=32 --with-readline --with-libxml --with-libxslt
#./configure --prefix=$PGHOME --with-pgport=$PORT --with-openssl --with-perl --with-python --with-blocksize=128 --with-wal-blocksize=128 --with-wal-segsize=100 --with-readline --with-libxml --with-libxslt --with-uuid=ossp
if [ $? == 0 ]
thenecho "configure配置通过，开始进行make编译"#gmake一次性将文档及附加模块全部进行编译和安装,保证所有数据库软件的一致性，避免给后期维护操作带来麻烦gmake world -j $cpuif [ $? == 0 ]thenecho "make编译通过，开始进行make install安装步骤"gmake install-world -j $cpuif [ $? != 0 ];thenecho "make install安装失败"fiecho "安装成功"elseecho "make编译失败，检查错误。"fi
elseecho "configure检查配置失败，请查看错误进行安装库文件"
fi
echo "6.添加环境变量,进入postgres用户的家目录"
cd /home/postgres
postgresenvConf(){
conf_exist=$(cat .bash_profile |grep postgres|wc -l)
if [ $conf_exist -eq 0 ]; thenecho "postgres user env configuration"cp .bash_profile .bash_profile.baksed -i 's/^export PATH/#export PATH/' .bash_profileecho "#add by postgres" >> .bash_profileecho "export PGHOME=$PGHOME" >> .bash_profileecho "export PGDATA=$PGDATA" >> .bash_profileecho "export PGPORT=5785" >> .bash_profile echo "export PGPASSWORD=123456" >> .bash_profile echo 'export PATH=$PGHOME/bin:$PATH' >> .bash_profileecho 'export MANPATH=$PGHOME/share/man:$MANPATH' >> .bash_profileecho 'export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH' >> .bash_profileecho 'SCRIPTS_DIR=/pgdb/scripts' >> .bash_profileecho "export LANG="en_US.UTF-8"" >> .bash_profileecho 'export DATE=`date +"%Y%m%d%H%M"`' >> .bash_profilesource /home/postgres/.bash_profile
elseecho "postgres user env is already config, so we do nothing"
fi
}
postgresenvConfecho "7. 开始进行pgsql的配置"
echo "切换至postgres用户来初始化数据库，设置密码文件"
su - postgres -c 'echo "$PASSWD">> .pgpass'
su - postgres -c "chmod 0600 /home/postgres/.pgpass"
su - postgres -c "$PGHOME/bin/initdb --username=postgres --pwfile=/home/postgres/.pgpass -D $PGDATA --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8"
if [ $? == 0 ]thenecho "初始化成功"chown -R postgres:postgres $BASEPATHchmod -R 755 $BASEPATHchmod -R 700 $PGDATAelse 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 = 5785/port = $PORT/" $FILE_CONF
sed -i 's/max_connections = 100/max_connections = 1000/' $FILE_CONF #max_connections*work_mem 上千个连接，建议配置连接池
sed -i 's/^#superuser_reserved_connections = 3/superuser_reserved_connections=10/' $FILE_CONF #为超级用户保留的连接数
sed -i "/^#max_prepared_transactions = 0/s/#max_prepared_transactions = 0/max_prepared_transactions = 500/" $FILE_CONF #等于
sed -i "/^shared_buffers = 128MB/s/shared_buffers = 128MB/shared_buffers = 1024MB/" $FILE_CONF #物理内存1/4，小于1/2
sed -i "/^#effective_cache_size = 4GB/s/#effective_cache_size = 4GB/effective_cache_size = 3GB/" $FILE_CONF #查询优化器可用的OS CACHE实际不占用内存 物理内存1/3~1/2
sed -i "/^#work_mem = 4MB/s/^#work_mem = 4MB/work_mem = 30MB/" $FILE_CONF #在写入临时磁盘文件之前查询操作(例如排序或哈希表)可使用的最大内存容量 # max(min(规格内存/4096, 64MB), 4MB)
sed -i "/^#maintenance_work_mem = 64MB/s/#maintenance_work_mem = 64MB/maintenance_work_mem = 256MB/" $FILE_CONF # min( 8G, (主机内存*1/8)/max_parallel_maintenance_workers )
sed -i 's/^#vacuum_cost_limit = 200/vacuum_cost_limit = 500/' $FILE_CONF #清理delete后的空间，此时对io影响较大，增加该值可以缩小对性能的影响
sed -i "/^#max_parallel_maintenance_workers = 2/s/#max_parallel_maintenance_workers = 2/max_parallel_maintenance_workers = 4/" $FILE_CONF #CPU核数/4
sed -i "/^#max_parallel_workers_per_gather = 2/s/#max_parallel_workers_per_gather = 2/max_parallel_workers_per_gather = 4/" $FILE_CONF #CPU核数/4 每个执行节点的最大并行处理过程数，应用并行查询时设置该值大于1，不建议超过主机cores-2
sed -i "/^#max_parallel_workers = 8/s/^#//" $FILE_CONF #CPU核数
sed -i "/^#max_worker_processes = 8/s/^#//" $FILE_CONF #CPU核数
sed -i 's/^min_wal_size = 80MB/min_wal_size = 1GB/' $FILE_CONF #建议值shared_buffers/2
sed -i 's/^max_wal_size = 1GB/max_wal_size = 2GB/' $FILE_CONF #该值越小，wal日志写入量越大，wal日志恢复时间越长
sed -i 's/^#checkpoint_timeout = 5min/checkpoint_timeout = 10min/' $FILE_CONF
sed -i "/^#checkpoint_completion_target = 0.9/s/^#//" $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 ='\/usr\/bin\/lz4 -q -z %p \/pgdb\/pg_archive\/%f.lz4'/" $FILE_CONF #-q取消警告-z强制压缩
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_disconnections = off/s/#log_disconnections = off/log_disconnections = on/" $FILE_CONF #用户退出时是否写入日志
sed -i "/^#log_connections = off/s/#log_connections = off/log_connections = on/" $FILE_CONF #用户session登录时写入日志
sed -i "/^#authentication_timeout = 1min/s/#authentication_timeout = 1min/authentication_timeout = 59s/" $FILE_CONF #用户session登录时写入日志
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 "/^#temp_buffers = 8MB/s/#temp_buffers = 8MB/temp_buffers = 256MB/" $FILE_CONFcp $HBA_CONF $PGDATA/pg_hba.confbak
echo "host all all 0.0.0.0/0 md5" >> $HBA_CONF
echo "8. 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=/pgdb/data
OOMScoreAdjust=-1000
ExecStart=/pgdb/pgsql/bin/pg_ctl start -D $PGDATA
ExecStop=/pgdb/pgsql/bin/pg_ctl stop -D $PGDATA -s -m fast
ExecReload=/pgdb/pgsql/bin/pg_ctl reload -D $PGDATA -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF
sed -i "s/^Environment=PGPORT=5785/Environment=PGPORT=$PORT/" /usr/lib/systemd/system/postgres.service
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 '$PGHOME/bin/postgres'|awk '{print $2}')
if [ -n "$process" ];then #检测字符串长度是否不为 0，不为 0 返回 true。echo "install success ans start success"
elseecho "install fail"
fi
echo "-----------------------------恭喜主库完成安装--------------------------------------"
echo "---------------------------9.切归档日志------------------------------------------------------"
su - postgres -c "$PGHOME/bin/psql -d postgres -h127.0.0.1 -p$PORT -c \"select pg_switch_wal();\""
echo "---------------------------------------------------------------------------------------"
echo "12.数据库信息"
echo "操作系统数据库用户：postgres;密码：postgres"
echo "数据库信息:postgres;密码：postgres;port:5785"
echo "-----------------------------开始从库安装--------------------------------------"
scp /opt/pginstall.sh root@192.168.59.136:/opt
scp /opt/*.tar.gz root@192.168.59.136:/opt/
ssh -tt -p 22 root@192.168.59.136 "source /opt/pginstall.sh"
REMOTE_HOST="192.168.59.136"
#REMOTE_SCRIPT_PATH="/opt/mysql_install.sh"
#ssh 192.168.59.250 "source /opt/mysql_install.sh"
ssh =$REMOTE_HOST <<EOF
if [ $? -eq 0 ]; thenecho "PG安装已成功完成."
elseecho "PG安装失败，请检查日志或手动排查问题."
fi
# 添加其他命令，如清理临时文件或重启服务
# 例如： rm /tmp/temp_file
exit
EOF
sleep 10
/usr/bin/expect <<EOF
set timeout 60
# 启动SSH命令
spawn ssh root@192.168.59.141
# 处理首次连接提示的 "yes/no" 和密码输入
expect {"Are you sure you want to continue connecting (yes/no)?" { send "yes\r"; exp_continue }"password:" { send "123123\r"; exp_continue }eof
}
EOF
sleep 10
source /etc/profile
# -------------------- 主节点配置（新增服务文件） -------------------- #
local host=192.168.59.141echo "------------------- 配置主节点 -------------------"# 使用expect处理首次SSH连接确认和密码输入
/usr/bin/expect <<EOF
set timeout 60
spawn ssh root@192.168.59.141
expect {"Are you sure you want to continue connecting (yes/no)?" { send "yes\r"; exp_continue }"password:" { send "123123\r"; exp_continue }eof
}
EOF# 执行远程配置命令
ssh root@192.168.59.141 <<EOFset -ex # 遇错退出+打印执行过程# 加载环境变量（明确检查是否加载成功）source /etc/profile || { echo "Error: 加载环境变量失败!" >&2; exit 1; }PG_PORT=5785PGDATA=/pgdb/data# 检查关键变量（强化错误提示）if [ -z "\${PGDATA}" ] || [ -z "\${PG_PORT}" ]; thenecho "Error: PGDATA（\${PGDATA}）或 PG_PORT（\${PG_PORT}）未设置!" >&2exit 1fiif [ ! -d "\${PGDATA}" ]; thenecho "Error: PGDATA 目录不存在: \${PGDATA}" >&2exit 1fi# 配置 postgresql.conf（添加备份，避免配置错误）cp \${PGDATA}/postgresql.conf \${PGDATA}/postgresql.conf.baksed -i "s/^#listen_addresses = .*/listen_addresses = '*'/" \${PGDATA}/postgresql.confsed -i "s/^port = .*/port = \${PG_PORT}/" \${PGDATA}/postgresql.confcat >> \${PGDATA}/postgresql.conf <<-EOF_CONF
listen_addresses = '*' # 监听所有ip
wal_level = hot_standby # 热备模式
archive_mode = on #允许归档
max_wal_senders = 10
wal_keep_size = 1GB
archive_mode = on
archive_command = 'test ! -f /pgdb/pg_archive/%f && cp %p /pgdb/pg_archive/%f'
synchronous_commit = on
wal_sender_timeout = 60s #流复制主机发送数据的超时时间
max_connections = 200 #最大连接数，从库的max_connections必须要大于主库的
EOF_CONFecho "postgresql.conf 配置完成（备份：postgresql.conf.bak）"# 配置 pg_hba.conf（添加备份+格式校验）cp \${PGDATA}/pg_hba.conf \${PGDATA}/pg_hba.conf.bakecho "host replication replicator 192.168.59.136/32 md5" >> \${PGDATA}/pg_hba.confecho "host all all 0.0.0.0/0 md5" >> \${PGDATA}/pg_hba.confsu - postgres -c "psql -c 'SELECT pg_reload_conf();'" # 重载配置（无需重启）echo "pg_hba.conf 配置完成（备份：pg_hba.conf.bak）"# 创建归档目录（强化权限检查）mkdir -p /pgdb/pg_archivechown postgres:postgres /pgdb/pg_archive || { echo "Error: 归档目录授权失败!" >&2; exit 1; }echo "归档目录创建完成"chown -R postgres.postgres /pgdb/data/# 重启服务（优先用 systemctl，失败则用 pg_ctl，并验证状态）echo "正在重启 PostgreSQL 服务..."if systemctl restart postgres.service; thenecho "systemctl 重启成功，等待服务就绪..."su - postgres -c "pg_ctl wait -D \${PGDATA} -t 30" # 等待30秒直到服务就绪elseecho "systemctl 重启失败，尝试 pg_ctl 重启..."su - postgres -c "pg_ctl restart -D \${PGDATA} -t 30" # 指定超时30秒fi# 验证服务状态（关键！避免假成功）su - postgres -c "pg_ctl status -D \${PGDATA}" || { echo "Error: 服务未启动!" >&2; exit 1; }role_exists=\$(su - postgres -c "psql -tAc 'SELECT 1 FROM pg_roles WHERE rolname = '\''replicator'\'';'")if [ -z "\${role_exists}" ]; then# 角色不存在，执行创建su - postgres -c 'psql -c "CREATE ROLE replicator WITH LOGIN REPLICATION ENCRYPTED PASSWORD '\''postgres'\'';"'echo "复制用户创建成功"else# 角色已存在，跳过创建echo "复制用户已存在，跳过创建"fiecho "===== 所有任务执行完成 ====="
EOF
date# -------------------- 从节点配置（修复pg_basebackup路径） -------------------- #
ssh 192.168.59.136echo "------------------- 配置从节点 -------------------"
export PRIMARY_HOST=192.168.59.141
export PG_PORT=5785
export USER=root
export PGDATA=/pgdb/data
rm -rf /pgdb/data
export PGPASSWORD='postgres'
pg_basebackup -D /pgdb/data -h 192.168.59.141 -p 5785 -U replicator -X stream -P
# 检测 PostgreSQL 服务名（优先使用 systemd 服务）
PG_SERVICE="postgresql.service"
if systemctl list-units --type=service | grep -q "postgresql-.*\.service"; thenPG_SERVICE=$(systemctl list-units --type=service | grep "postgresql-.*\.service" | awk '{print $1}')
fissh root@192.168.59.136 <<EOFset -ex# 加载环境变量source /etc/profile || { echo "环境变量加载失败"; exit 1; }# 停止服务（优先 systemctl，失败则 pg_ctl）if systemctl is-enabled "${PG_SERVICE}" &>/dev/null; thensystemctl stop "${PG_SERVICE}" || truefisu - postgres -c "pg_ctl stop -D ${PGDATA} -m fast" # 强制快速停止# 写入 standby 配置（避免覆盖原配置，改为追加关键参数）cp "${PGDATA}/postgresql.conf" "${PGDATA}/postgresql.conf.bak" # 备份原配置cat >> "${PGDATA}/postgresql.conf" <<-EOF_CONF
primary_conninfo = 'host=${PRIMARY_HOST} port=${PG_PORT} user=replicator password=postgres'
hot_standby = on
wal_receiver_status_interval = 10s # 控制 walreceiver 进程向主服务器发送心跳消息的时间间隔
hot_standby_feedback = on # 控制备用服务器是否会向主服务器发送关于自己的复制状态和进度的信息
EOF_CONF# 校验配置文件语法（关键！避免启动失败）su - postgres -c "pg_ctl check -D ${PGDATA}" || { echo "配置文件语法错误"; exit 1; }# 修正数据目录权限chown -R postgres:postgres "${PGDATA}" || { echo "权限修改失败"; exit 1; }# 启动服务（优先 systemctl，失败则 pg_ctl）systemctl start postgres.serviceif systemctl start "${PG_SERVICE}"; thenecho "systemctl 启动服务成功"elsesu - postgres -c "pg_ctl start -D ${PGDATA}" || { echo "pg_ctl 启动失败"; exit 1; }fiexport PGDATA=/pgdb/datals $PGDATA/standby.signal &> /dev/null || touch $PGDATA/standby.signal# 验证服务状态su - postgres -c "pg_ctl status -D ${PGDATA}" || { echo "服务未启动"; exit 1; }echo "PostgreSQL 备用节点配置完成"
EOF# 验证（确保环境变量生效）
echo "------------------- 验证主从状态 -------------------"ssh root@192.168.59.141 "source /etc/profile && su - postgres -c 'psql -c \"SELECT * FROM pg_stat_replication;\"'"
for host in "${HOSTS[@]:1}"; dossh root@192.168.59.136 "source /etc/profile && su - postgres -c 'psql -c \"SELECT * FROM pg_is_in_recovery();\"'"
done脚本2：
cat pginstall.sh
#!/bin/bash
echo "-----------------------------开始PG数据库安装--------------------------------------"
dir=$(pwd)
echo "db variable list"
BASEPATH=/pgdb
FILE_CONF=/pgdb/data/postgresql.conf
HBA_CONF=/pgdb/data/pg_hba.conf
PGDATA=/pgdb/data
PGHOME=/pgdb/pgsql
SCRIPTS_DIR=/pgdb/scripts
LOGPATH=/pgdb/data/log
PORT=5785
PASSWD="123456"
cpu=$(cat /proc/cpuinfo | grep 'physical id' | sort | uniq | wc -l)
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 ]; thenecho "optimize system core conf"sed -ri '/net.ipv4.ip_forward/s#0#1#' /etc/sysctl.confcat >> /etc/sysctl.conf <<EOF
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
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_synack_retries = 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
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_max_syn_backlog = 16384
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
net.core.somaxconn=1024
net.core.netdev_max_backlog = 262144
EOF
elseecho "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 ]; thenecho "optimize limit configuration"cat >> /etc/security/limits.conf << "EOF"
#add by postgres
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
elseecho "limit is already optimized, so we do nothing"
fi
}
optimizeLimitConfecho "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=5785/tcp --permanentfirewall-cmd --zone=public --add-port=22/tcp --permanentfirewall-cmd --reload#禁用防火墙区域偏移sed -i 's/^AllowZoneDrifting=yes/AllowZoneDrifting=no/' /etc/firewalld/firewalld.conf elseecho "firewall not open"
fi
}
conf_firewall
echo ""1.6.IPC, some system have this, so do it by user 配置防火墙策略"logind进程cpu占用100%处理"
sed -i 's/#RemoveIPC=no/RemoveIPC=no/g' /etc/systemd/logind.conf
systemctl daemon-reload
systemctl restart systemd-logind
echo "1.7.安装相关依赖"
# 获取当前所在目录位置
current_dir=$(pwd)
echo "当前所在目录位置: $current_dir"
# 目标路径
target_dir="/soft"
# 检查目标路径是否存在，如果不存在则创建
if [ ! -d "$target_dir" ]; thenmkdir -p "$target_dir"echo "已创建目录: $target_dir"
fi
# 移动当前目录下的所有文件到目标路径
mv $current_dir/pg_yum.tar.gz $target_dir
echo "已将当前目录下所有文件移动至 $target_dir"
cd /etc/yum.repos.d/
rm -rf ./*
cat >> /etc/yum.repos.d/centos.repo <<-EOF
[centos]
name=oracle
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF
cd
mount /dev/sr0 /mnt
yum clean all|wc -l
yum makecache
yum install -y zlib-devel libaio cmake make gcc gcc-c++ readline readline-devel perl bison flex libyaml net-tools expect openssh-clients tcl openssl openssl-devel ncurses-devel python python-devel openldap pam systemtap-sdt-devel perl-ExtUtils-Embed libxml2 libxml2-devel libxslt libxslt-devel uuid-devel
echo "2. postgres exits"
id $postgres >& /dev/null
if [ $? -ne 0 ]
thenecho "postgres already exits"
else echo "postgres not exits，please create"groupadd postgresuseradd -g postgres postgresecho "$PASSWD"|passwd --stdin postgressed -ri '/^root/a\postgres ALL=(ALL) ALL' /etc/sudoers
fiecho "3.create directory"
if [ ! -d $BASEPATH ]
thenmkdir -p $BASEPATH/{data,pg_archive,pg_backup,scripts,tmp}
fiecho "4. unzip"
tar -zxf /opt/postgresql*.tar.gz -C $BASEPATH/
echo "pgsql upzip success"
echo "directory rights"
cd $BASEPATH
mv postgresql-14.12/ pgsql
chown -R postgres:postgres $BASEPATH
chmod -R 755 $BASEPATH
#-------------------------------install pgsql------------------------------------
echo "5.install dependency package"
cd $PGHOME
./configure --prefix=$PGHOME --with-pgport=$PORT --with-openssl --with-perl --with-python --with-blocksize=32 --with-readline --with-libxml --with-libxslt
#./configure --prefix=$PGHOME --with-pgport=$PORT --with-openssl --with-perl --with-python --with-blocksize=128 --with-wal-blocksize=128 --with-wal-segsize=100 --with-readline --with-libxml --with-libxslt --with-uuid=ossp
if [ $? == 0 ]
thenecho "configure配置通过，开始进行make编译"#gmake一次性将文档及附加模块全部进行编译和安装,保证所有数据库软件的一致性，避免给后期维护操作带来麻烦gmake world -j $cpuif [ $? == 0 ]thenecho "make编译通过，开始进行make install安装步骤"gmake install-world -j $cpuif [ $? != 0 ];thenecho "make install安装失败"fiecho "安装成功"elseecho "make编译失败，检查错误。"fi
elseecho "configure检查配置失败，请查看错误进行安装库文件"
fi
echo "6.添加环境变量,进入postgres用户的家目录"
cd /home/postgres
postgresenvConf(){
conf_exist=$(cat .bash_profile |grep postgres|wc -l)
if [ $conf_exist -eq 0 ]; thenecho "postgres user env configuration"cp .bash_profile .bash_profile.baksed -i 's/^export PATH/#export PATH/' .bash_profileecho "#add by postgres" >> .bash_profileecho "export PGHOME=$PGHOME" >> .bash_profileecho "export PGDATA=$PGDATA" >> .bash_profileecho "export PGPORT=5785" >> .bash_profile echo "export PGPASSWORD=123456" >> .bash_profile echo 'export PATH=$PGHOME/bin:$PATH' >> .bash_profileecho 'export MANPATH=$PGHOME/share/man:$MANPATH' >> .bash_profileecho 'export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH' >> .bash_profileecho 'SCRIPTS_DIR=/pgdb/scripts' >> .bash_profileecho "export LANG="en_US.UTF-8"" >> .bash_profileecho 'export DATE=`date +"%Y%m%d%H%M"`' >> .bash_profilesource /home/postgres/.bash_profile
elseecho "postgres user env is already config, so we do nothing"
fi
}
postgresenvConfecho "7. 开始进行pgsql的配置"
echo "切换至postgres用户来初始化数据库，设置密码文件"
su - postgres -c 'echo "$PASSWD">> .pgpass'
su - postgres -c "chmod 0600 /home/postgres/.pgpass"
su - postgres -c "$PGHOME/bin/initdb --username=postgres --pwfile=/home/postgres/.pgpass -D $PGDATA --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8"
if [ $? == 0 ]thenecho "初始化成功"chown -R postgres:postgres $BASEPATHchmod -R 755 $BASEPATHchmod -R 700 $PGDATAelse 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 = 5785/port = $PORT/" $FILE_CONF
sed -i 's/max_connections = 100/max_connections = 1000/' $FILE_CONF #max_connections*work_mem 上千个连接，建议配置连接池
sed -i 's/^#superuser_reserved_connections = 3/superuser_reserved_connections=10/' $FILE_CONF #为超级用户保留的连接数
sed -i "/^#max_prepared_transactions = 0/s/#max_prepared_transactions = 0/max_prepared_transactions = 500/" $FILE_CONF #等于
sed -i "/^shared_buffers = 128MB/s/shared_buffers = 128MB/shared_buffers = 1024MB/" $FILE_CONF #物理内存1/4，小于1/2
sed -i "/^#effective_cache_size = 4GB/s/#effective_cache_size = 4GB/effective_cache_size = 3GB/" $FILE_CONF #查询优化器可用的OS CACHE实际不占用内存 物理内存1/3~1/2
sed -i "/^#work_mem = 4MB/s/^#work_mem = 4MB/work_mem = 30MB/" $FILE_CONF #在写入临时磁盘文件之前查询操作(例如排序或哈希表)可使用的最大内存容量 # max(min(规格内存/4096, 64MB), 4MB)
sed -i "/^#maintenance_work_mem = 64MB/s/#maintenance_work_mem = 64MB/maintenance_work_mem = 256MB/" $FILE_CONF # min( 8G, (主机内存*1/8)/max_parallel_maintenance_workers )
sed -i 's/^#vacuum_cost_limit = 200/vacuum_cost_limit = 500/' $FILE_CONF #清理delete后的空间，此时对io影响较大，增加该值可以缩小对性能的影响
sed -i "/^#max_parallel_maintenance_workers = 2/s/#max_parallel_maintenance_workers = 2/max_parallel_maintenance_workers = 4/" $FILE_CONF #CPU核数/4
sed -i "/^#max_parallel_workers_per_gather = 2/s/#max_parallel_workers_per_gather = 2/max_parallel_workers_per_gather = 4/" $FILE_CONF #CPU核数/4 每个执行节点的最大并行处理过程数，应用并行查询时设置该值大于1，不建议超过主机cores-2
sed -i "/^#max_parallel_workers = 8/s/^#//" $FILE_CONF #CPU核数
sed -i "/^#max_worker_processes = 8/s/^#//" $FILE_CONF #CPU核数
sed -i 's/^min_wal_size = 80MB/min_wal_size = 1GB/' $FILE_CONF #建议值shared_buffers/2
sed -i 's/^max_wal_size = 1GB/max_wal_size = 2GB/' $FILE_CONF #该值越小，wal日志写入量越大，wal日志恢复时间越长
sed -i 's/^#checkpoint_timeout = 5min/checkpoint_timeout = 10min/' $FILE_CONF
sed -i "/^#checkpoint_completion_target = 0.9/s/^#//" $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 ='\/usr\/bin\/lz4 -q -z %p \/pgdb\/pg_archive\/%f.lz4'/" $FILE_CONF #-q取消警告-z强制压缩
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_disconnections = off/s/#log_disconnections = off/log_disconnections = on/" $FILE_CONF #用户退出时是否写入日志
sed -i "/^#log_connections = off/s/#log_connections = off/log_connections = on/" $FILE_CONF #用户session登录时写入日志
sed -i "/^#authentication_timeout = 1min/s/#authentication_timeout = 1min/authentication_timeout = 59s/" $FILE_CONF #用户session登录时写入日志
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 "/^#temp_buffers = 8MB/s/#temp_buffers = 8MB/temp_buffers = 256MB/" $FILE_CONFcp $HBA_CONF $PGDATA/pg_hba.confbak
echo "host all all 0.0.0.0/0 md5" >> $HBA_CONF
echo "8. 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=/pgdb/data
OOMScoreAdjust=-1000
ExecStart=/pgdb/pgsql/bin/pg_ctl start -D $PGDATA
ExecStop=/pgdb/pgsql/bin/pg_ctl stop -D $PGDATA -s -m fast
ExecReload=/pgdb/pgsql/bin/pg_ctl reload -D $PGDATA -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF
sed -i "s/^Environment=PGPORT=5785/Environment=PGPORT=$PORT/" /usr/lib/systemd/system/postgres.service
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 '$PGHOME/bin/postgres'|awk '{print $2}')
if [ -n "$process" ];then #检测字符串长度是否不为 0，不为 0 返回 true。echo "install success ans start success"
elseecho "install fail"
fi
echo "-----------------------------恭喜完成安装--------------------------------------"
echo "---------------------------9.切归档日志------------------------------------------------------"
su - postgres -c "$PGHOME/bin/psql -d postgres -h127.0.0.1 -p$PORT -c \"select pg_switch_wal();\""
echo "---------------------------------------------------------------------------------------"
echo "12.数据库信息"
echo "操作系统数据库用户：postgres;密码：postgres"
echo "数据库信息:postgres;密码：postgres;port:5785"
直接在主节点执行脚本1即可。
注意：
脚本1和脚本2在同一个目录下，且安装包在opt目录下