postgresql主从一键安装脚本分享

脚本内容

#!/bin/bash
set -euo pipefail  # 严格模式：错误立即停止+未定义变量报错+管道错误传递# -------------------- 全局变量配置 -------------------- #
HOSTS=("192.168.59.141" "192.168.59.136")  # 第一个为主节点，其余为从节点
USER="root"
PASSWORD="123123"                         # 所有节点的root密码（需一致）
SSH_DIR="/root/.ssh"
SOFT_DIR="/soft"                          # 统一安装包路径
PG_SRC_FILE="postgresql-14.12.tar.gz"     # 离线安装包文件名（需提前放主节点/soft）
PG_PORT=5785                              # 数据库端口
REPLICATOR_PASSWORD="repl123"             # 复制用户密码
PRIMARY_HOST=${HOSTS[0]}                  # 主节点IP# -------------------- 初始化检查 -------------------- #
check_prerequisites() {# 检查主节点是否有离线包if [ ! -f "${SOFT_DIR}/${PG_SRC_FILE}" ]; thenecho "错误：主节点${PRIMARY_HOST}的${SOFT_DIR}/${PG_SRC_FILE}不存在！"echo "请提前将PostgreSQL离线包放入主节点的${SOFT_DIR}目录"exit 1fi# 检查SSH免密是否正常（主节点到所有从节点）for host in "${HOSTS[@]}"; doif ! ssh -o ConnectTimeout=5 "${USER}@${host}" true 2>/dev/null; thenecho "错误：无法通过SSH连接节点${host}，请检查免密配置！"exit 1fidone
}# -------------------- SSH免密配置（修复伪终端问题） -------------------- #
configure_ssh() {# 生成所有节点的SSH密钥对（强制分配伪终端）for host in "${HOSTS[@]}"; do/usr/bin/expect <<-EOF  # 使用<<-允许Tab缩进（需确保编辑器用Tab而非空格）spawn ssh -t ${USER}@${host} "mkdir -p ${SSH_DIR} && chmod 700 ${SSH_DIR} && \ssh-keygen -t rsa -b 2048 -f ${SSH_DIR}/id_rsa -q -N ''"expect {"*yes/no" { send "yes\r"; exp_continue }"*password:" { send "${PASSWORD}\r"; exp_continue }}
EOFdone# 收集所有节点的公钥并分发（修复here文档格式）for src_host in "${HOSTS[@]}"; do# 获取源节点公钥pub_key=$(ssh ${USER}@${src_host} "cat ${SSH_DIR}/id_rsa.pub")# 分发到所有目标节点for dest_host in "${HOSTS[@]}"; doif [ "${src_host}" != "${dest_host}" ]; then/usr/bin/expect <<-EOFspawn ssh -t ${USER}@${dest_host} "echo '${pub_key}' >> ${SSH_DIR}/authorized_keys && chmod 600 ${SSH_DIR}/authorized_keys"expect {"*yes/no" { send "yes\r"; exp_continue }"*password:" { send "${PASSWORD}\r"; exp_continue }}
EOFfidonedone
}# -------------------- 同步安装包到所有节点（主库→从库） -------------------- #
sync_soft_dir() {echo "------------------- 开始同步安装包到所有节点 -------------------"for host in "${HOSTS[@]}"; doif [ "${host}" != "${PRIMARY_HOST}" ]; then# 创建从节点/soft目录（若不存在）ssh ${USER}@${host} "mkdir -p ${SOFT_DIR} && chmod 755 ${SOFT_DIR}"# 同步主节点/soft到从节点（-a保留权限，-v显示进度）scp -r "${SOFT_DIR}/"* "${USER}@${host}:${SOFT_DIR}/"echo "安装包已同步到节点${host}"fidone
}# -------------------- PostgreSQL安装函数（离线编译） -------------------- #
install_postgresql() {local host=$1echo "------------------- 开始在${host}安装PostgreSQL -------------------"ssh ${USER}@${host} <<-EOF  # 使用<<-允许Tab缩进（需确保编辑器用Tab）set -euo pipefail# 变量定义BASEPATH="/pgdb"PGHOME="\${BASEPATH}/pgsql"PGDATA="\${BASEPATH}/data"SOFT_DIR="${SOFT_DIR}"PG_SRC_FILE="${PG_SRC_FILE}"# 创建数据库目录mkdir -p "\${BASEPATH}"/{data,pg_archive,pg_backup,scripts,tmp}chmod 755 "\${BASEPATH}"chown -R postgres:postgres "\${BASEPATH}"  # 提前创建postgres用户# 解压离线安装包（检查文件是否存在）if [ ! -f "\${SOFT_DIR}/\${PG_SRC_FILE}" ]; thenecho "错误：\${SOFT_DIR}/\${PG_SRC_FILE}不存在！"exit 1fitar -zxf "\${SOFT_DIR}/\${PG_SRC_FILE}" -C "\${SOFT_DIR}"mv "\${SOFT_DIR}/postgresql-14.12" "\${SOFT_DIR}/postgresql-src"# 安装依赖（离线环境需提前准备yum本地源）yum install -y zlib-devel libaio-devel readline-devel openssl-devel# 编译安装（使用/soft下的源码）cd "\${SOFT_DIR}/postgresql-src"./configure --prefix="\${PGHOME}" --with-pgport=${PG_PORT} --with-opensslmake -j\$(nproc) && make install# 配置环境变量echo "export PGHOME=\${PGHOME}export PGDATA=\${PGDATA}export PATH=\${PGHOME}/bin:\${PATH}export LD_LIBRARY_PATH=\${PGHOME}/lib:\${LD_LIBRARY_PATH}" >> /home/postgres/.bash_profilesource /home/postgres/.bash_profile# 初始化数据库（提前创建postgres用户）id postgres >/dev/null 2>&1 || {groupadd postgresuseradd -g postgres postgresecho "123456" | passwd --stdin postgres}su - postgres -c "initdb -D \${PGDATA} -U postgres --encoding=UTF8"
EOF
}# -------------------- 主节点配置（流复制） -------------------- #
configure_primary() {local host=$1echo "------------------- 配置主节点${host} -------------------"ssh ${USER}@${host} <<-EOFset -ePGDATA="/pgdb/data"PG_HBA="\${PGDATA}/pg_hba.conf"POSTGRES_CONF="\${PGDATA}/postgresql.conf"# 允许远程连接sed -i 's/^#listen_addresses = .*/listen_addresses = '\''*'\''/' "\${POSTGRES_CONF}"sed -i "s/^port = .*/port = ${PG_PORT}/" "\${POSTGRES_CONF}"# 流复制配置cat >> "\${POSTGRES_CONF}" <<-EOF_CONFwal_level = replicamax_wal_senders = 10wal_keep_segments = 32archive_mode = onarchive_command = 'test ! -f /pgdb/pg_archive/%f && cp %p /pgdb/pg_archive/%f'
EOF_CONF# 创建复制用户su - postgres -c "psql -c \"CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '${REPLICATOR_PASSWORD}';\""# 允许从节点连接for host in "${HOSTS[@]:1}"; do  # 从节点列表（排除主节点）echo "host    replication     replicator      \${host}/32       md5" >> "\${PG_HBA}"doneecho "host    all             all             0.0.0.0/0       md5" >> "\${PG_HBA}"# 启动服务systemctl enable postgresqlsystemctl start postgresql
EOF
}# -------------------- 从节点配置（流复制） -------------------- #
configure_standby() {local host=$1echo "------------------- 配置从节点${host} -------------------"ssh ${USER}@${host} <<-EOFset -ePGDATA="/pgdb/data"PGHOME="/pgdb/pgsql"# 停止服务并清空数据目录systemctl stop postgresqlrm -rf "\${PGDATA}"/*# 从主节点同步数据（使用pg_basebackup）su - postgres -c "\${PGHOME}/bin/pg_basebackup \-h ${PRIMARY_HOST} \-p ${PG_PORT} \-U replicator \-D \${PGDATA} \-P \--wal-method=stream \--password=${REPLICATOR_PASSWORD}"# 创建standby配置cat > "\${PGDATA}/postgresql.auto.conf" <<-EOF_CONFprimary_conninfo = 'host=${PRIMARY_HOST} port=${PG_PORT} user=replicator password=${REPLICATOR_PASSWORD}'hot_standby = on
EOF_CONF# 启动服务systemctl enable postgresqlsystemctl start postgresql
EOF
}# -------------------- 主执行流程 -------------------- #
check_prerequisites
configure_ssh
sync_soft_dir# 安装PostgreSQL到所有节点
for host in "${HOSTS[@]}"; doinstall_postgresql "${host}"
done# 配置主节点和从节点
configure_primary "${PRIMARY_HOST}"
for host in "${HOSTS[@]:1}"; doconfigure_standby "${host}"
done# 验证主从状态
echo "------------------- 验证主从复制状态 -------------------"
ssh ${USER}@${PRIMARY_HOST} "su - postgres -c 'psql -c \"SELECT * FROM pg_stat_replication;\"'"
for host in "${HOSTS[@]:1}"; dossh ${USER}@${host} "su - postgres -c 'psql -c \"SELECT * FROM pg_is_in_recovery();\"'"
doneecho "------------------- 所有节点安装完成！ -------------------"

执行脚本

chmod +x install_pg_cluster.sh
./install_pg_cluster.sh

验证方法

主节点验证：

psql -h 主节点IP -p 5432 -U postgres -c "SELECT client_addr, state FROM pg_stat_replication;"

从节点验证：

psql -h 从节点IP -p 5432 -U postgres -c "SELECT pg_is_in_recovery();"