腳本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目錄下
)
![[git]如何關聯本地分支和遠程分支](http://pic.xiahunao.cn/[git]如何關聯本地分支和遠程分支)
：實際案例)
