Harbor（外置db+redis） + K8s 高可用集群实施手册（独立LB + Calico + kubeasz版）一-CSDN博客

个人更新难免有疏漏，有建议或者疏漏也请留言，共同进步。

 3.部署harbor（10.0.0.104/10.0.0.105）

3.1介绍参考：

https://blog.csdn.net/m0_66705547/article/details/143044790?spm=1011.2415.3001.5331

3.2下载harbor

wget https://github.com/goharbor/harbor/releases/download/v2.14.2/harbor-offline-installer-v2.14.2.tgz

#国内加速地址
wget https://githubfast.com/goharbor/harbor/releases/download/v2.14.2/harbor-offline-installer-v2.14.2.tgz

3.3安装 docker docker compose （10.0.0.104/105）

3.3.1更新阿里云镜像源

#!/bin/bash

# 配置 Ubuntu 24.04 阿里云镜像源脚本
# 说明: 本脚本将配置 Ubuntu 24.04 (noble) 使用阿里云镜像源

# 检查是否为 root 用户
if [ "$(id -u)" -ne 0 ]; then
    echo "错误: 该脚本需要 root 权限才能执行。请使用 sudo 运行此脚本。"
    exit 1
fi

# 检查是否为 Ubuntu 24.04
source /etc/os-release
if [ "$VERSION_ID" != "24.04" ] || [ "$ID" != "ubuntu" ]; then
    echo "警告: 检测到系统可能不是 Ubuntu 24.04 (noble)"
    echo "当前系统: $PRETTY_NAME"
    read -p "是否继续配置阿里云镜像源? (y/n): " confirm
    if [ "$confirm" != "y" ] && [ "$confirm" != "Y" ]; then
        echo "已取消配置。"
        exit 1
    fi
fi

# 备份原有的 sources.list
echo "正在备份原有的 sources.list 文件到/etc/apt/sources.list.d/ubuntu.sources.bak..."
cp /etc/apt/sources.list.d/ubuntu.sources /etc/apt/sources.list.d/ubuntu.sources.bak
if [ $? -ne 0 ]; then
    echo "错误: 备份 sources.list 文件失败。"
    exit 1
fi
echo "备份完成。"

# 写入阿里云镜像源
echo "正在配置阿里云镜像源..."
sudo sed -i "s@http://.*archive.ubuntu.com@https://mirrors.aliyun.com/@g" /etc/apt/sources.list.d/ubuntu.sources
sudo sed -i "s@http://.*security.ubuntu.com@https://mirrors.aliyun.com/@g" /etc/apt/sources.list.d/ubuntu.sources

if [ $? -ne 0 ]; then
    echo "错误: 写入镜像源配置失败。"
    echo "正在恢复备份..."
    cp /etc/apt/sources.list.d/ubuntu.sources.bak /etc/apt/sources.list.d/ubuntu.sources
    exit 1
fi
echo "阿里云镜像源配置完成。"

# 更新 apt 缓存
echo "正在更新软件包列表..."
apt update
if [ $? -ne 0 ]; then
    echo "警告: apt update 执行失败，可能是网络问题。"
    echo "建议检查网络连接后手动执行 'sudo apt update'"
fi

echo ""
echo "=============================================="
echo "阿里云镜像源配置成功完成！"
echo "原始 sources.list 已备份为 /etc/apt/sources.list.d/ubuntu.sources.bak"
echo "如需恢复原配置，请执行: sudo cp /etc/apt/sources.list.d/ubuntu.sources.bak /etc/apt/sources.list.d/ubuntu.sources"
echo "=============================================="

3.2.2 安装 docker docker compose （10.0.0.104/105）

#!/bin/bash

# =============================================================================
# Docker 和 Docker Compose 安装脚本 (APT + 二进制)
#
# 此脚本使用 APT 包管理器从阿里云镜像源安装 Docker CE、containerd 和相关插件，
# 并从二进制文件安装独立的 Docker Compose。
# 支持 Ubuntu/Debian 系统。
# 脚本执行 root 权限检查，设置阿里云镜像源，添加 Docker GPG 密钥，
# 安装 Docker 组件，安装 Docker Compose，将当前用户添加到 docker 组，
# 并包含全面的错误处理。
#
# 使用方法：
#   1. chmod +x install-docker-apt.sh
#   2. sudo ./install-docker-apt.sh
#
# =============================================================================

set -e  # 如果命令退出状态非零，则立即退出

# Colors for output / 输出颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color / 无颜色

# Print colored output / 打印带颜色的输出
print_info() {
    echo -e "${BLUE}[信息]${NC} $1"
}

print_success() {
    echo -e "${GREEN}[成功]${NC} $1"
}

print_warning() {
    echo -e "${YELLOW}[警告]${NC} $1"
}

print_error() {
    echo -e "${RED}[错误]${NC} $1"
}

# Check if running as root / 检查是否以 root 权限运行
check_root_privileges() {
    print_info "检查 root 权限..."
    if [[ $EUID -eq 0 ]]; then
        print_success "以 root 身份运行"
    else
        print_error "此脚本必须以 root 或 sudo 运行"
        exit 1
    fi
}

# Check if running on Ubuntu/Debian / 检查是否运行在 Ubuntu/Debian 上
check_os() {
    print_info "检查操作系统..."
    if [[ -f /etc/debian_version ]] || grep -qi "ubuntu" /etc/os-release; then
        print_success "检测到 Debian/Ubuntu 系统"
    else
        print_error "此脚本仅支持 Ubuntu/Debian 系统。"
        exit 1
    fi
}

# Install necessary system tools / 安装必要的系统工具
install_prerequisites() {
    print_info "安装必要的系统工具 (ca-certificates, curl, gnupg, apt-transport-https)..."
    apt-get update
    apt-get install -y ca-certificates curl gnupg apt-transport-https
    print_success "系统工具安装完成"
}

# Add Docker's official GPG key / 添加 Docker 的官方 GPG 密钥
add_docker_gpg_key() {
    print_info "添加 Docker 的 GPG 公钥..."

    # Create directory for keys / 创建密钥目录
    install -m 0755 -d /etc/apt/keyrings

    # Download and install GPG key from Aliyun mirror / 从阿里云镜像下载并安装 GPG 密钥
    if ! curl -fsSL "https://mirrors.aliyun.com/docker-ce/linux/ubuntu/gpg" | gpg --dearmor -o /etc/apt/keyrings/docker.gpg; then
        print_error "下载或安装 Docker GPG 密钥失败"
        exit 1
    fi

    # Set correct permissions / 设置正确权限
    chmod a+r /etc/apt/keyrings/docker.gpg
    print_success "Docker GPG 密钥已添加"
}

# Add Docker repository / 添加 Docker 仓库
add_docker_repository() {
    print_info "添加 Docker 软件源..."

    # Determine architecture / 确定架构
    ARCH=$(dpkg --print-architecture)

    # Determine OS codename / 确定 OS 代号
    if [[ -f /etc/os-release ]]; then
        . /etc/os-release
        OS_CODENAME="$VERSION_CODENAME"
    else
        print_error "无法确定 OS 代号 (/etc/os-release 不存在)"
        exit 1
    fi

    # Write repository info / 写入仓库信息
    REPO_URL="deb [arch=${ARCH} signed-by=/etc/apt/keyrings/docker.gpg] https://mirrors.aliyun.com/docker-ce/linux/ubuntu ${OS_CODENAME} stable"
    if ! echo "$REPO_URL" | tee /etc/apt/sources.list.d/docker.list > /dev/null; then
        print_error "写入 Docker 仓库信息失败"
        exit 1
    fi

    print_success "Docker 仓库已添加"
}

# Update package index / 更新包索引
update_package_index() {
    print_info "更新 APT 包索引..."
    apt-get update
    print_success "APT 包索引已更新"
}

# Install Docker packages / 安装 Docker 包
install_docker_packages() {
    print_info "安装 Docker CE, containerd.io, 和相关插件..."

    # Install the packages / 安装包
    if ! apt-get install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin; then
        print_error "Docker 包安装失败"
        exit 1
    fi

    print_success "Docker CE, containerd.io, 和 docker-buildx-plugin 已安装"
}

# Install Docker Compose binary / 安装 Docker Compose 二进制文件
install_docker_compose() {
    print_info "正在安装 Docker Compose v5.0.2 (二进制文件)..."
    
    # Download Docker Compose binary / 下载 Docker Compose 二进制文件
    COMPOSE_URL="https://github.com/docker/compose/releases/download/v5.0.2/docker-compose-linux-x86_64"
    COMPOSE_BIN="/usr/local/bin/docker-compose"
    
    if ! curl -L -o "$COMPOSE_BIN" "$COMPOSE_URL"; then
        print_error "下载 Docker Compose 失败"
        exit 1
    fi
    
    # Set executable permissions / 设置可执行权限
    chmod +x "$COMPOSE_BIN"
    
    print_success "Docker Compose (二进制文件) 安装成功"
}

# Start and enable Docker service / 启动并启用 Docker 服务
setup_docker_service() {
    print_info "启动并启用 Docker 服务..."
    
    # Enable and start Docker service / 启用并启动 Docker 服务
    systemctl enable docker
    systemctl start docker
    
    print_success "Docker 服务已启动并启用"
}

# Add current user to docker group / 将当前用户添加到 docker 组
add_user_to_docker_group() {
    print_info "将当前用户添加到 docker 组..."
    
    # Get the current user (before sudo) / 获取当前用户（sudo 之前）
    ORIGINAL_USER=$(logname 2>/dev/null || whoami | awk '{print $1}')
    
    # Create docker group if it doesn't exist / 如果不存在则创建 docker 组
    if ! getent group docker > /dev/null 2>&1; then
        print_info "创建 docker 组..."
        groupadd docker
    fi
    
    # Add user to docker group / 将用户添加到 docker 组
    usermod -aG docker "$ORIGINAL_USER"
    
    print_success "已将 $ORIGINAL_USER 添加到 docker 组"
    print_warning "您需要注销并重新登录才能使组更改生效。"
    print_info "或者，您可以运行: newgrp docker"
}

# Verify installations / 验证安装
verify_installations() {
    print_info "验证安装..."
    
    # Check Docker version / 检查 Docker 版本
    print_info "检查 Docker 版本..."
    if command -v docker >/dev/null 2>&1; then
        DOCKER_VERSION=$(docker --version 2>/dev/null || echo "未知")
        print_success "Docker 版本: $DOCKER_VERSION"
    else
        print_error "Docker 安装验证失败"
        exit 1
    fi
    
    # Check Docker Compose version (binary) / 检查 Docker Compose 版本（二进制文件）
    print_info "检查 Docker Compose (二进制文件) 版本..."
    if command -v docker-compose >/dev/null 2>&1; then
        COMPOSE_VERSION=$(docker-compose --version 2>/dev/null || echo "未知")
        print_success "Docker Compose (二进制文件) 版本: $COMPOSE_VERSION"
    else
        print_warning "Docker Compose (二进制文件) 安装验证失败或未安装 (这可能不是错误，取决于您的需求)"
        # Optionally, you could make this an error if docker-compose is strictly required
        # print_error "Docker Compose (二进制文件) 安装验证失败"
        # exit 1
    fi

    # Check Docker Compose plugin version / 检查 Docker Compose 插件版本
    print_info "检查 Docker Compose (插件) 版本..."
    if docker compose version >/dev/null 2>&1; then
        PLUGIN_COMPOSE_VERSION=$(docker compose version 2>/dev/null || echo "未知")
        print_success "Docker Compose (插件) 版本: $PLUGIN_COMPOSE_VERSION"
    else
        print_warning "Docker Compose (插件) 安装验证失败或未安装 (这可能不是错误，取决于您的需求)"
    fi
    
    # Test Docker daemon / 测试 Docker 守护进程
    print_info "测试 Docker 守护进程..."
    if timeout 10 docker info >/dev/null 2>&1; then
        print_success "Docker 守护进程运行正常"
    else
        print_error "Docker 守护进程测试失败"
        exit 1
    fi
    
    print_success "所有验证都通过了！"
}

# Main function / 主函数
main() {
    print_info "开始 Docker (APT) 和 Docker Compose (二进制) 安装..."
    print_info "日期: $(date)"
    
    check_root_privileges          # 检查 root 权限
    check_os                       # 检查操作系统
    install_prerequisites          # 安装必要工具
    add_docker_gpg_key             # 添加 GPG 密钥
    add_docker_repository          # 添加仓库
    update_package_index           # 更新包索引
    install_docker_packages        # 安装 Docker 包
    install_docker_compose         # 安装 Docker Compose 二进制文件
    setup_docker_service           # 设置并启动 Docker 服务
    add_user_to_docker_group       # 将用户添加到 docker 组
    verify_installations           # 验证安装结果
    
    print_info ""
    print_success "安装成功完成！"
    print_info ""
    print_info "下一步操作："
    print_info "1. 注销并重新登录（或运行: newgrp docker）以便无需 sudo 即可使用 Docker"
    print_info "2. 测试 Docker: docker run hello-world"
    print_info "3. 测试 Docker Compose (插件): docker compose version"
    print_info "4. 测试 Docker Compose (二进制): docker-compose --version"
    print_info ""
    print_info "Docker 更多信息: https://docs.docker.com/"
    print_info "Docker Compose 更多信息: https://docs.docker.com/compose/"
}

# Run main function / 运行主函数
main "$@"

3.3核实内置pgsql，redis版本

尽量兼容自带数据库大版本

#解压
tar xzvf harbor-offline-installer-v2.14.2.tgz
cd harbor/

#加载镜像
docker load -i harbor.v2.14.2.tar.gz
#查看pgsql版本
docker images

docker run --rm goharbor/harbor-db:v2.14.2 postgres --version
#注意下面这行
2026-02-10 06:00:03.757 UTC [1] LOG:  starting PostgreSQL 15.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.0, 64-bit
#查看redis版本
docker run --rm goharbor/redis-photon:v2.14.2 redis-server --version
Redis server v=7.2.11 sha=00000000:0 malloc=jemalloc-5.3.0 bits=64 build=7d2a79c22df711d4

#核实系统自带的版本
root@DESKTOP-B1I6R18:~# apt list -a postgresql
Listing... Done
postgresql/noble-updates 16+257build1.1 all
postgresql/noble 16+257build1 all

root@DESKTOP-B1I6R18:~# apt list -a redis
Listing... Done
redis/noble-updates,noble-security 5:7.0.15-1ubuntu0.24.04.2 all
redis/noble 5:7.0.15-1build2 all
#pg为16.11 redis为7.0.15 由于版本相差不大理论上也不会有bug。稳妥起见直接二进制安装对应版本

3.4编译部署pgsql集群（1主一从）

3.4.1基于流复制部署pgsql 集群

3.4.2流复制类似于MySQL的的同步机制,支持以下两种同步机制

异步流复制
同步流复制
在实际生产环境中，建议需要根据环境的实际情况来选择同步或异步模式，同步模式需要等待备库的写盘才能返回成功，如此一来，在主备库复制正常的情况下，能够保证备库的数据不会丢失，但是带来的一个负面问题，一旦备库宕机，主库就会挂起而无法进行正常操作，即在同步模式中，备库对主库的有很大的影响，而异步模式就不会。因此，在生产环境中，大多会选择异步复制模式,而非同步模式.
3.4.3 流复制特点
延迟极低，不怕大事务
支持断点续传
支持多副本
配置简单
备库与主库物理完全一致，并支持只读

3.4.4 PostgreSQL 15.15 高可用集群完整部署

1. 系统基础准备与优化

cat > set_pgos.sh <<eof

#!/bin/bash

# =============================================
# 系统优化配置脚本（专为 PostgreSQL 优化）
# 注意：必须以 root 用户运行！
# =============================================

# 颜色定义
RED='\033[0;31m'
YELLOW='\033[1;33m'
GREEN='\033[0;32m'
NC='\033[0m' # No Color

# 检查是否为 root 用户
if [[ $EUID -ne 0 ]]; then
    echo -e "${RED}错误：此脚本必须以 root 权限运行！${NC}" >&2
    exit 1
fi

# 设置 PostgreSQL 用户名（请根据实际情况修改）
PG_USER="postgres"

# 检查 PG_USER 是否存在
if ! id "$PG_USER" &>/dev/null; then
    echo -e "${YELLOW}警告：用户 '$PG_USER' 不存在，仍将继续配置 limits.conf...${NC}"
fi

echo -e "${YELLOW}执行系统优化配置...${NC}"

# 1. 内核参数优化（追加到 /etc/sysctl.conf）
cat >> /etc/sysctl.conf << EOF

# === PostgreSQL 推荐内核参数 (added by $(basename $0) on $(date)) ===
kernel.shmmax = 17179869184      # 16GB shared memory max size
kernel.shmall = 4194304          # shared memory total pages (page size * this = bytes)
kernel.shmmni = 4096             # max number of shared memory segments
kernel.sem = 250 256000 32 1024  # semaphores: SEMMSL SEMMNS SEMOPM SEMMNI
net.core.rmem_max = 16777216     # max receive buffer size
net.core.wmem_max = 16777216     # max send buffer size
net.ipv4.tcp_wmem = 4096 65536 16777216   # min/default/max send buffer
net.ipv4.tcp_rmem = 4096 87380 16777216   # min/default/max recv buffer
vm.swappiness = 1                # 减少交换分区使用（1 表示几乎不用 swap）
vm.dirty_ratio = 15              # 脏页占内存百分比上限（同步写入触发点）
vm.dirty_background_ratio = 5    # 后台异步写入脏页的阈值
EOF

# 应用新的 sysctl 参数
echo -e "${YELLOW}正在应用新的内核参数...${NC}"
if sysctl -p; then
    echo -e "${GREEN}✓ 内核参数已成功加载。${NC}"
else
    echo -e "${RED}✗ 应用 sysctl 参数失败，请手动检查 /etc/sysctl.conf。${NC}"
    exit 1
fi

# 2. 文件描述符与进程数限制优化
cat >> /etc/security/limits.conf << EOF

# === PostgreSQL 用户资源限制 (added by $(basename $0) on $(date)) ===
${PG_USER} soft nofile 65536
${PG_USER} hard nofile 65536
${PG_USER} soft nproc 32768
${PG_USER} hard nproc 32768
EOF

echo -e "${GREEN}✓ 系统优化配置已完成！${NC}"
echo -e "${YELLOW}注意：${NC}新 limits 配置将在 ${PG_USER} 下次登录时生效。"
echo "建议重启系统或重新登录 PostgreSQL 用户以确保 ulimit 生效。"

eof

1.2 I/O 调度器优化

cat  set_NUMA.sh 
#!/bin/bash
set -e

echo "=== 系统优化：关闭 NUMA + 智能设置 I/O 调度器 ==="

# 1. 检测当前是否已关闭 NUMA
if grep -q "numa=off" /proc/cmdline; then
    echo "[✓] NUMA 已关闭"
else
    echo "[!] NUMA 未关闭，将修改 GRUB 配置（需重启生效）"
    # 备份原配置
    cp /etc/default/grub /etc/default/grub.bak.$(date +%Y%m%d)
    
    # 添加 numa=off 参数（避免重复添加）
    if grep -q "GRUB_CMDLINE_LINUX_DEFAULT" /etc/default/grub; then
        sed -i 's/GRUB_CMDLINE_LINUX_DEFAULT="\(.*\)"/GRUB_CMDLINE_LINUX_DEFAULT="\1 numa=off"/' /etc/default/grub
    else
        echo 'GRUB_CMDLINE_LINUX_DEFAULT="quiet splash numa=off"' >> /etc/default/grub
    fi
    
    # 更新 GRUB
    if command -v update-grub &>/dev/null; then
        update-grub
    elif command -v grub2-mkconfig &>/dev/null; then
        grub2-mkconfig -o /boot/grub2/grub.cfg
    fi
    echo "[✓] GRUB 已更新，请重启系统使 NUMA 关闭生效"
fi

# 2. 为所有磁盘设置 I/O 调度器（临时生效）
echo -e "\n=== 设置 I/O 调度器 ==="
for disk in /sys/block/sd* /sys/block/nvme*; do
    [[ -d "$disk" ]] || continue
    dev=$(basename "$disk")
    
    # 跳过分区（只处理磁盘设备）
    [[ "$dev" =~ [0-9]$ ]] && continue
    
    # 检测是否为旋转磁盘（1=机械盘，0=SSD）
    if [[ -f "$disk/queue/rotational" ]]; then
        rotational=$(cat "$disk/queue/rotational")
    else
        rotational=0  # NVMe 默认视为 SSD
    fi
    
    # 选择调度器（Ubuntu 24.04 内核 6.x 使用 mq-deadline/none）
    if [[ "$rotational" -eq 1 ]]; then
        scheduler="mq-deadline"
    else
        scheduler="none"  # 替代 noop
    fi
    
    # 检查调度器是否可用
    if [[ -f "$disk/queue/scheduler" ]]; then
        available=$(cat "$disk/queue/scheduler")
        if [[ "$available" == *"$scheduler"* ]]; then
            echo "$scheduler" > "$disk/queue/scheduler" 2>/dev/null && \
                echo "[✓] $dev ($([[ $rotational -eq 1 ]] && echo 'HDD' || echo 'SSD')) → $scheduler" || \
                echo "[✗] $dev: 无法设置调度器（权限不足？）"
        else
            echo "[!] $dev: $scheduler 不可用，可用选项: $available"
        fi
    fi
done

# 3. 永久生效：创建 udev 规则
echo -e "\n=== 配置永久生效规则 ==="
cat > /etc/udev/rules.d/60-io-scheduler.rules <<'EOF'
# SSD/NVMe 使用 none 调度器
ACTION=="add|change", KERNEL=="nvme[0-9]*", ATTR{queue/scheduler}="none"
ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/rotational}=="0", ATTR{queue/scheduler}="none"

# 机械盘使用 mq-deadline
ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/rotational}=="1", ATTR{queue/scheduler}="mq-deadline"
EOF

udevadm control --reload-rules && udevadm trigger
echo "[✓] udev 规则已部署，新插拔设备将自动应用调度器"

# 4. 验证当前状态
echo -e "\n=== 当前调度器状态 ==="
for disk in /sys/block/sd* /sys/block/nvme*; do
    [[ -d "$disk" && ! "$(basename $disk)" =~ [0-9]$ ]] || continue
    dev=$(basename "$disk")
    sched=$(cat "$disk/queue/scheduler" 2>/dev/null | sed -n 's/.*\[\(.*\)\].*/\1/p')
    rot=$(cat "$disk/queue/rotational" 2>/dev/null)
    type=$([[ "$rot" == "1" ]] && echo "HDD" || echo "SSD")
    echo "$dev: $type → [$sched]"
done

echo -e "\n=== 操作完成 ==="
echo "• NUMA: 已配置（重启后生效）"
echo "• I/O 调度器: 已临时设置 + udev 永久规则部署"
echo "• 请执行 'sudo reboot' 使 NUMA 设置完全生效"

1.2.1使用dns私服

见 2.2.2.1小节

Harbor（外置db+redis） + K8s 高可用集群实施手册（独立LB + Calico + kubeasz版）一（初始化+ dns私服+minio集群）-CSDN博客

2. PostgreSQL 二进制安装
2.1 编译安装 PostgreSQL（所有数据库节点）

​
vim   install_pg15.sh 
#!/bin/bash

set -euo pipefail

POSTGRESQL_VERSION="15.15"
INSTALL_DIR="/apps/pgsql"
DATA_DIR="/pgsql/data"
PGHOME="$INSTALL_DIR"
SOCKET_DIR="/pgsql/run" 
PARENT_DIR=$(dirname "$DATA_DIR")
DB_USER="postgres"

color() {
    local msg="$1"
    local status="$2"
    local RES_COL=60
    printf "%-${RES_COL}s" "$msg"
    if [[ "$status" == "0" || "$status" == "success" ]]; then
        echo -e "[\033[1;32m  OK  \033[0m]"
    elif [[ "$status" == "1" || "$status" == "failure" ]]; then
        echo -e "[\033[1;31mFAILED\033[0m]"
    else
        echo -e "[\033[1;33mWARNING\033[0m]"
    fi
}

if [[ $EUID -ne 0 ]]; then
    color "请以 root 用户运行此脚本！" 1
    exit 1
fi

if [[ -f /etc/os-release ]]; then
    source /etc/os-release
else
    color "无法识别操作系统" 1
    exit 1
fi

CPUs=$(nproc 2>/dev/null || grep -c '^processor' /proc/cpuinfo || echo 2)
[[ -z "$CPUs" ]] && CPUs=2

install_deps() {
    color "正在安装编译依赖..." 2
    if [[ "$ID" == "centos" || "$ID" == "rocky" || "$ID" == "rhel" ]]; then
        yum install -y gcc make readline-devel zlib-devel tar wget
    elif [[ "$ID" == "ubuntu" || "$ID" == "debian" ]]; then
        apt update
        apt install -y gcc make libreadline-dev zlib1g-dev tar wget libssl-dev
    else
        color "不支持的操作系统: $ID" 1
        exit 1
    fi
}

install_postgresql() {
    local tarball="postgresql-${POSTGRESQL_VERSION}.tar.gz"
    local url="https://mirrors.aliyun.com/postgresql/source/v${POSTGRESQL_VERSION}/${tarball}"

    mkdir -p "${INSTALL_DIR%/*}" "${DATA_DIR%/*}"

    if [[ ! -f "$tarball" ]]; then
        color "正在从阿里云下载 PostgreSQL ${POSTGRESQL_VERSION}..." 2
        wget -O "$tarball" "$url" || { color "下载失败！" 1; exit 1; }
    fi

    tar -xf "$tarball"
    cd "postgresql-${POSTGRESQL_VERSION}"

    color "配置编译选项..." 2
    ./configure --prefix="${INSTALL_DIR}" --with-openssl

    color "编译 PostgreSQL（使用 $CPUs 核心）..." 2
    make -j "$CPUs" world

    color "安装 PostgreSQL..." 2
    make install-world

    if ! id "$DB_USER" &>/dev/null; then
        useradd -m -s /bin/bash -d "/home/$DB_USER" "$DB_USER"
    fi

    mkdir -p "$DATA_DIR"
    chown -R "$DB_USER:$DB_USER" "$PARENT_DIR" 

    cat > /etc/profile.d/pgsql.sh <<EOF
export PGHOME=${INSTALL_DIR}
export PATH=\${PGHOME}/bin:\$PATH
export PGDATA=${DATA_DIR}
export PGHOST=${SOCKET_DIR}     # ← 关键：让 psql 默认用这个 socket
export PGUSER=postgres
export MANPATH="${PGHOME}/share/man${MANPATH:+:${MANPATH}}"
alias pgstart='pg_ctl -D \$PGDATA start'
alias pgstop='pg_ctl -D \$PGDATA stop'
alias pgrestart='pg_ctl -D \$PGDATA restart'
alias pgstatus='pg_ctl -D \$PGDATA status'
EOF
    chmod +x /etc/profile.d/pgsql.sh
    sleep 1

    if [[ -z "$(ls -A "$DATA_DIR" 2>/dev/null)" ]]; then
        color "初始化数据库集群..." 2
        su - "$DB_USER" -c "${INSTALL_DIR}/bin/initdb -D '${DATA_DIR}' --locale=en_US.UTF-8 -E UTF8"
    else
        color "数据目录非空，跳过 initdb。" 2
    fi
}

start_service() {
    color "配置 systemd 服务..." 2
    cat > /lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking
User=${DB_USER}
Group=${DB_USER}
ExecStart=${INSTALL_DIR}/bin/pg_ctl -D ${DATA_DIR} -l ${DATA_DIR}/logfile start
ExecStop=${INSTALL_DIR}/bin/pg_ctl -D ${DATA_DIR} stop
ExecReload=${INSTALL_DIR}/bin/pg_ctl -D ${DATA_DIR} reload
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

    systemctl daemon-reload
    systemctl enable --now postgresql.service

    if systemctl is-active --quiet postgresql.service; then
        color "PostgreSQL ${POSTGRESQL_VERSION} 安装并启动成功！" 0
        echo "--------------------------------------------------"
        echo "安装目录: $INSTALL_DIR"
        echo "数据目录: $DATA_DIR"
        echo "服务命令: systemctl start|stop|restart postgresql"
        echo "切换用户: su - postgres"
        echo "查看状态: pgstatus （需重新登录或 source /etc/profile）"
        echo "--------------------------------------------------"
    else
        color "PostgreSQL 启动失败，请检查日志！" 1
        exit 1
    fi
}

main() {
    install_deps
    install_postgresql
    start_service
}

main "$@"

source /etc/profile.d/pgsql.sh

3. 主节点配置（10.0.0.102）

#!/bin/bash
# configure_primary.sh - PostgreSQL 15.15 主库配置脚本

set -euo pipefail

DATA_DIR="/pgsql/data"
INSTALL_DIR="/apps/pgsql"
DB_USER="postgres"
PRIMARY_IP=$(ip -4 route get 8.8.8.8 2>/dev/null | awk '{print $7; exit}' || hostname -I 2>/dev/null | cut -d' ' -f1)
STANDBY_IP="10.0.0.103" #按实际使用更改
STANDBY_NAME="standby1"

# 生成密码并保存到变量和文件
REPL_PASSWORD=$(openssl rand -base64 12)

color() {
    local msg="$1"; local status="$2"
    printf "%-60s" "$msg"
    if [[ "$status" == "0" ]]; then
        echo -e "[\033[1;32m  OK  \033[0m]"
    else
        echo -e "[\033[1;31mFAILED\033[0m]"
    fi
}

if [[ $EUID -ne 0 ]]; then
    color "请以 root 用户运行！" 1
    exit 1
fi

# 检查数据库是否已初始化
color "检查数据库是否已初始化..." 0
if [ ! -d "${DATA_DIR}/base" ]; then
    color "❌ 数据库未初始化！" 1
    echo "请先运行安装脚本安装PostgreSQL"
    exit 1
fi

# 确保必要目录存在
color "确保必要目录存在..." 0
mkdir -p "${DATA_DIR}/pg_wal_archive" "${DATA_DIR}/pg_log" /pgsql/run
chown -R "$DB_USER:$DB_USER" "${DATA_DIR}/pg_wal_archive" "${DATA_DIR}/pg_log" /pgsql/run

# 应用主库 postgresql.conf
color "应用主库 postgresql.conf..." 0
cat > "${DATA_DIR}/postgresql.conf" <<EOF
# 主库核心配置
listen_addresses = '*'
port = 5432
max_connections = 200
shared_buffers = 4GB

# 流复制配置
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
hot_standby = off  # 主库不需要此参数，但保留以防万一

# 归档配置
archive_mode = on
archive_command = 'test ! -f ${DATA_DIR}/pg_wal_archive/%f && cp %p ${DATA_DIR}/pg_wal_archive/%f'

# 日志配置
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_timezone = 'Asia/Shanghai'
EOF

chown "$DB_USER:$DB_USER" "${DATA_DIR}/postgresql.conf"

# 配置 pg_hba.conf
color "配置 pg_hba.conf..." 0
cat > "${DATA_DIR}/pg_hba.conf" <<EOF
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    replication     repl            ${STANDBY_IP}/32        scram-sha-256
host    all             repl            ${STANDBY_IP}/32        scram-sha-256
EOF
chown "$DB_USER:$DB_USER" "${DATA_DIR}/pg_hba.conf"

# 重启服务使配置生效
color "重启 PostgreSQL 服务使配置生效..." 0
systemctl restart postgresql || {
    color "❌ 重启服务失败！" 1
    echo "查看详细错误："
    journalctl -u postgresql -n 50 --no-pager
    exit 1
}

# 等待服务就绪
color "等待 PostgreSQL 完全重启..." 0
for i in {1..20}; do
    if su - "$DB_USER" -c "${INSTALL_DIR}/bin/pg_isready -h localhost -p 5432" >/dev/null 2>&1; then
        break
    fi
    sleep 2
done

if ! su - "$DB_USER" -c "${INSTALL_DIR}/bin/pg_isready -h localhost -p 5432"; then
    color "❌ PostgreSQL 启动失败，配置可能有误" 1
    echo "请检查日志："
    journalctl -u postgresql -n 50 --no-pager | grep -A 20 "ERROR\|FATAL"
    exit 1
fi

# 创建复制用户
color "创建复制用户 repl..." 0
su - "$DB_USER" -c "${INSTALL_DIR}/bin/psql -c \"DO \\$\\$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_user WHERE usename = 'repl') THEN
    CREATE USER repl WITH REPLICATION LOGIN ENCRYPTED PASSWORD '${REPL_PASSWORD}';
  ELSE
    ALTER USER repl WITH REPLICATION ENCRYPTED PASSWORD '${REPL_PASSWORD}';
  END IF;
END
\\$\\$;\" postgres"

# ========================
# 7. 完成提示 - 修复：现在正确显示密码
# ========================
color "✅ 主库配置完成！" 0
echo "--------------------------------------------------"
echo "主库 IP: ${PRIMARY_IP}"
echo "复制用户: repl"
echo "复制密码: ${REPL_PASSWORD}"
echo "从库名称: ${STANDBY_NAME}"
echo "WAL归档目录: ${DATA_DIR}/pg_wal_archive"
echo "--------------------------------------------------"
echo "请将上述信息保存好，备库配置时需要使用复制密码！"
echo "--------------------------------------------------"

4. 从节点配置（10.0.0.103）

#!/bin/bash
# configure_standby_optimized.sh -  PostgreSQL 15.15 备库配置脚本

set -euo pipefail

DATA_DIR="/pgsql/data"
TEMP_BACKUP_DIR="/tmp/pg_backup_temp_$(date +%s)"
ARCHIVE_DIR="${DATA_DIR}/pg_wal_archive"
INSTALL_DIR="/apps/pgsql"
DB_USER="postgres"
PRIMARY_IP="10.0.0.24"
STANDBY_IP=$(hostname -I | awk '{print $1}')
REPL_USER="repl"
REPL_PASSWORD=""

color() {
    local msg="$1"; local status="$2"
    printf "%-60s" "$msg"
    if [[ "$status" == "0" ]]; then
        echo -e "[\033[1;32m  OK  \033[0m]"
    else
        echo -e "[\033[1;31mFAILED\033[0m]"
    fi
}

if [[ $EUID -ne 0 ]]; then
    color "❌ 请以 root 用户运行！" 1
    exit 1
fi

# 获取复制密码
echo "请输入主节点复制用户的密码（从主节点 configure_primary.sh 输出中获取）："
read -s REPL_PASSWORD
echo

if [[ -z "$REPL_PASSWORD" ]]; then
    color "❌ 复制密码不能为空！" 1
    exit 1
fi

# 测试主库连接
color "测试主库连接..." 0
if ! timeout 10 bash -c "echo > /dev/tcp/${PRIMARY_IP}/5432" 2>/dev/null; then
    color "❌ 无法连接到主库 ${PRIMARY_IP}:5432！" 1
    exit 1
fi

# 验证主库复制用户连接
color "验证复制用户连接..." 0
if su - "$DB_USER" -c "PGPASSWORD='${REPL_PASSWORD}' /apps/pgsql/bin/pg_isready -h '${PRIMARY_IP}' -p 5432 -U '${REPL_USER}'" >/dev/null 2>&1; then
    color "✅ 主库连接验证成功！" 0
else
    color "❌ 复制用户连接验证失败！" 1
    echo "请确认主库已正确配置复制用户和pg_hba.conf"
    exit 1
fi

# 停止 PostgreSQL 服务
color "停止 PostgreSQL 服务..." 0
systemctl stop postgresql 2>/dev/null || true

# 清空现有数据目录 - 完全删除并重建
color "清空现有数据目录..." 0
rm -rf "${DATA_DIR}" 2>/dev/null || true
mkdir -p "$DATA_DIR"
chown "$DB_USER:$DB_USER" "$DATA_DIR"

# 创建临时备份目录
mkdir -p "$TEMP_BACKUP_DIR"
chown "$DB_USER:$DB_USER" "$TEMP_BACKUP_DIR"

# 使用临时目录进行备份，然后移动到最终位置
color "使用 pg_basebackup 初始化备库数据..." 0
if ! su - "$DB_USER" -c "
PGPASSWORD='${REPL_PASSWORD}' ${INSTALL_DIR}/bin/pg_basebackup \
  -D '${TEMP_BACKUP_DIR}' \
  -Fp \
  -P \
  -R \
  -W \
  -X stream \
  -h '${PRIMARY_IP}' \
  -p 5432 \
  -U '${REPL_USER}'
"; then
    color "❌ pg_basebackup 执行失败！" 1
    rm -rf "$TEMP_BACKUP_DIR" 2>/dev/null || true
    exit 1
fi

# 将临时目录内容移动到最终位置
color "移动数据到最终目录..." 0
mv "${TEMP_BACKUP_DIR}"/* "${DATA_DIR}/"
rmdir "$TEMP_BACKUP_DIR"

# 确保 standby.signal 存在
if [ ! -f "${DATA_DIR}/standby.signal" ]; then
    touch "${DATA_DIR}/standby.signal"
    chown "$DB_USER:$DB_USER" "${DATA_DIR}/standby.signal"
fi

# 配置备库 postgresql.conf
color "配置备库 postgresql.conf..." 0
cat > "${DATA_DIR}/postgresql.conf" <<EOF
# 备库核心配置
listen_addresses = '*'
port = 5432
max_connections = 200
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 26214kB
min_wal_size = 1GB
max_wal_size = 4GB

# 热备配置
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
max_standby_archive_delay = 30s

# 日志配置
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_timezone = 'Asia/Shanghai'
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

# Unix socket
unix_socket_directories = '/pgsql/run'

# 其他配置
dynamic_shared_memory_type = posix
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
EOF

chown "$DB_USER:$DB_USER" "${DATA_DIR}/postgresql.conf"

# 确保日志目录存在
mkdir -p "${DATA_DIR}/pg_log"
chown "$DB_USER:$DB_USER" "${DATA_DIR}/pg_log"

# 确保归档目录存在
mkdir -p "$ARCHIVE_DIR"
chown "$DB_USER:$DB_USER" "$ARCHIVE_DIR"

# 设置正确的权限
chown -R "$DB_USER:$DB_USER" "$DATA_DIR"

# 设置数据目录权限 - 关键修复点
chmod 700 "$DATA_DIR"
find "$DATA_DIR" -type d -exec chmod 700 {} \;
find "$DATA_DIR" -type f -exec chmod 600 {} \;

# 启动备库服务
color "启动 PostgreSQL 备库服务..." 0
systemctl start postgresql

# 等待服务启动
color "等待服务启动完成..." 0
for i in {1..30}; do
    if systemctl is-active --quiet postgresql; then
        break
    fi
    sleep 2
done

if systemctl is-active --quiet postgresql; then
    color "✅ 备库启动成功！" 0
    
    # 验证流复制状态
    color "验证流复制状态..." 0
    if su - "$DB_USER" -c "PGHOST=/pgsql/run ${INSTALL_DIR}/bin/psql -c \"SELECT pg_is_in_recovery();\" postgres" 2>/dev/null | grep -q "t"; then
        color "✅ 备库已成功进入恢复模式！" 0
        echo "流复制正在运行..."
    else
        color "⚠️  备库可能未正确配置为只读模式" 1
    fi
    
    # 显示备库状态
    echo ""
    echo "📊 备库状态信息："
    su - "$DB_USER" -c "PGHOST=/pgsql/run ${INSTALL_DIR}/bin/psql -c \"SELECT now() as current_time, pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_is_in_recovery();\" postgres"
    
else
    color "❌ 备库启动失败！" 1
    echo ""
    echo "🔍 详细错误信息："
    journalctl -u postgresql.service --no-pager -n 20
    echo ""
    echo "📋 数据目录权限检查："
    ls -la "$DATA_DIR" | head -5
    echo ""
    echo "📋 PostgreSQL 错误日志："
    tail -20 "$DATA_DIR/pg_log/"* 2>/dev/null || echo "未找到日志文件"
    exit 1
fi

# 显示连接信息
color "✅ 备库配置完成！" 0
echo "=================================================="
echo "备库 IP: ${STANDBY_IP}"
echo "主库 IP: ${PRIMARY_IP}"
echo "复制用户: ${REPL_USER}"
echo "数据目录: ${DATA_DIR}"
echo "WAL归档目录: ${ARCHIVE_DIR}"
echo "安装目录: ${INSTALL_DIR}"
echo "=================================================="
echo ""
echo "🔧 验证命令（本地连接使用）："
echo "# 确认处于恢复模式（需要重新登录或 source /etc/profile）"
echo "psql -U postgres -c \"SELECT pg_is_in_recovery();\""
echo ""
echo "# 或者显式指定socket路径"
echo "PGHOST=/pgsql/run psql -U postgres -c \"SELECT pg_is_in_recovery();\""
echo ""
echo "🔧 验证命令（主库上执行）："
echo "# 查看复制状态"
echo "psql -U postgres -c \"SELECT pid, application_name, client_addr, state, sync_state FROM pg_stat_replication;\""
echo ""
echo "# 查看WAL接收器状态（在备库执行）"
echo "PGHOST=/pgsql/run psql -U postgres -c \"SELECT pid, status, received_lsn, last_msg_receipt_time FROM pg_stat_wal_receiver;\""
echo ""
echo "# 查看复制延迟（在备库执行）"
echo "PGHOST=/pgsql/run psql -U postgres -c \"SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds;\""
echo "=================================================="

5.主库创建表测试

-- 1. 创建一个名为 "zz" 的测试表
CREATE TABLE zz (
    id SERIAL PRIMARY KEY,
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. 插入几条测试数据
INSERT INTO zz (description) VALUES 
('这是来自主库的第一条测试数据'), 
('流复制同步测试 - 第二条'), 
('验证成功！');

-- 3. 立刻查询一下，确认数据已经在主库了
SELECT * FROM zz;


psql
psql (15.15)
Type "help" for help.

postgres=# CREATE TABLE replication_test (
    id SERIAL PRIMARY KEY,
    info TEXT,
    test_time TIMESTAMP DEFAULT NOW()
);
CREATE TABLE
postgres=# INSERT INTO replication_test (info) VALUES ('This is a test from Master'), ('Second row inserted');
INSERT 0 2
postgres=# SELECT * FROM replication_test;
 id |            info            |         test_time          
----+----------------------------+----------------------------
  1 | This is a test from Master | 2026-02-12 07:54:36.665403
  2 | Second row inserted        | 2026-02-12 07:54:36.665403
(2 rows)

postgres=# CREATE TABLE zz (
    id SERIAL PRIMARY KEY,
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE
postgres=# INSERT INTO zz (description) VALUES 
('这是来自主库的第一条测试数据'), 
('流复制同步测试 - 第二条'), 
('验证成功！');
INSERT 0 3
postgres=# SELECT * FROM zz;
 id |         description          |         created_at         
----+------------------------------+----------------------------
  1 | 这是来自主库的第一条测试数据 | 2026-02-12 08:17:14.878815
  2 | 流复制同步测试 - 第二条      | 2026-02-12 08:17:14.878815
  3 | 验证成功！                   | 2026-02-12 08:17:14.878815
(3 rows)

6.从库测试

su - postgres 
postgres@master-03:~$ psql
psql (15.15)
Type "help" for help.

postgres=# SELECT * FROM replication_test;
 id |            info            |         test_time          
----+----------------------------+----------------------------
  1 | This is a test from Master | 2026-02-12 07:54:36.665403
  2 | Second row inserted        | 2026-02-12 07:54:36.665403
(2 rows)

postgres=# SELECT * FROM zz;
 id |         description          |         created_at         
----+------------------------------+----------------------------
  1 | 这是来自主库的第一条测试数据 | 2026-02-12 08:17:14.878815
  2 | 流复制同步测试 - 第二条      | 2026-02-12 08:17:14.878815
  3 | 验证成功！                   | 2026-02-12 08:17:14.878815
(3 rows)

5. HAProxy + Keepalived 配置

部署说明
1. 节点分布
HAProxy1 节点 (IP: 10.0.0.101)
运行: install_ha_proxy_keepalived.sh
角色: Keepalived MASTER
优先级: 100
HAProxy2 节点 (IP: 10.0.0.102)
运行: install_ha_proxy_keepalived.sh
角色: Keepalived BACKUP
优先级: 90
Monitor 节点 (任意节点，如应用服务器)
运行: monitor_cluster.sh, failover_handler.sh
用于集群监控和故障转移
应用服务器
使用: connection_examples.sh 作为连接参考

1.系统优化+安装配置 HAProxy + Keepalived

#!/bin/bash
# install_ha_proxy_keepalived.sh - 安装配置 HAProxy + Keepalived 高可用 (含系统优化)

set -euo pipefail

# 参数配置
VIP="10.0.0.100"
NODE1_IP="10.0.0.101"  # HAProxy1 IP (MASTER)
NODE2_IP="10.0.0.102"  # HAProxy2 IP (BACKUP)
INTERFACE="ens3"        # 网络接口名，根据实际情况调整
PG_PRIMARY="10.0.0.24"  # PostgreSQL 主库
PG_STANDBY="10.0.0.182" # PostgreSQL 备库
PG_PORT="5432"

color() {
    local msg="$1"; local status="$2"
    printf "%-60s" "$msg"
    if [[ "$status" == "0" ]]; then
        echo -e "[\033[1;32m  OK  \033[0m]"
    else
        echo -e "[\033[1;31mFAILED\033[0m]"
    fi
}

if [[ $EUID -ne 0 ]]; then
    color "❌ 请以 root 用户运行！" 1
    exit 1
fi

# 检查网络接口
if ! ip addr show "$INTERFACE" &>/dev/null; then
    color "❌ 网络接口 $INTERFACE 不存在！" 1
    echo "请修改 INTERFACE 变量为正确的网络接口名"
    exit 1
fi


# 1. 安装 HAProxy 和 Keepalived
color "安装 HAProxy 和 Keepalived..." 0
apt update
apt install -y haproxy keepalived

# 2. 系统参数优化
color "开始系统参数优化..." 0

# 内核参数优化
color "配置内核参数..." 0
cat >> /etc/sysctl.conf <<EOF

# HAProxy + Keepalived 优化参数
# 增加最大连接数
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.core.netdev_max_backlog = 5000

# 优化TCP参数
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 1200
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1

# 网络缓冲区优化
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 65536 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

# VRRP 相关优化
net.ipv4.ip_nonlocal_bind = 1
net.ipv4.ip_forward = 1

# 系统文件描述符限制
fs.file-max = 1048576

# 增加可用端口范围
net.ipv4.ip_local_port_range = 1024 65535

# 增加TIME_WAIT状态连接的回收
net.ipv4.tcp_max_tw_buckets = 5000

# TCP快速回收
net.ipv4.tcp_fastopen = 3

# 增加路由缓存大小
net.ipv4.route.flush = 1

# 网络监控优化
net.netfilter.nf_conntrack_max = 1048576
net.netfilter.nf_conntrack_tcp_timeout_established = 1800
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120
EOF

# 应用内核参数
sysctl -p

# 文件描述符优化
color "配置文件描述符限制..." 0
cat >> /etc/security/limits.conf <<EOF
# HAProxy + Keepalived 文件描述符优化
haproxy soft nofile 102400
haproxy hard nofile 102400
keepalived soft nofile 102400
keepalived hard nofile 102400
root soft nofile 102400
root hard nofile 102400
EOF

# HAProxy 服务优化
color "配置 HAProxy 服务参数..." 0
mkdir -p /etc/systemd/system/haproxy.service.d
cat > /etc/systemd/system/haproxy.service.d/override.conf <<EOF
[Service]
LimitNOFILE=102400
LimitNPROC=32768
Restart=always
RestartSec=5
OOMScoreAdjust=-1000
EOF

# Keepalived 服务优化
color "配置 Keepalived 服务参数..." 0
mkdir -p /etc/systemd/system/keepalived.service.d
cat > /etc/systemd/system/keepalived.service.d/override.conf <<EOF
[Service]
LimitNOFILE=102400
LimitNPROC=32768
Restart=always
RestartSec=5
OOMScoreAdjust=-1000
EOF

# 重新加载 systemd 配置
systemctl daemon-reload



# 3. 配置 HAProxy
color "配置 HAProxy..." 0
cat > /etc/haproxy/haproxy.cfg <<EOF
global
    log stdout local0
    log stdout local1 notice
    chroot /var/lib/haproxy
    stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
    stats timeout 30s
    user haproxy
    group haproxy
    daemon
    nbthread 4
    cpu-map auto:1/1-4 0-3

defaults
    log global
    mode tcp
    option tcplog
    option dontlognull
    retries 3
    timeout connect 5000
    timeout client 50000
    timeout server 50000

# PostgreSQL 读写连接 (主库优先)
listen postgresql_rw
    bind *:5432
    mode tcp
    balance roundrobin
    option pgsql-check user=postgres
    server primary $PG_PRIMARY:$PG_PORT check port=$PG_PORT inter=2000 rise=2 fall=3 maxconn 5000
    server standby $PG_STANDBY:$PG_PORT check port=$PG_PORT inter=2000 rise=2 fall=3 backup maxconn 5000

# PostgreSQL 只读连接 (备库优先) 
listen postgresql_ro
    bind *:5433
    mode tcp
    balance roundrobin
    option pgsql-check user=postgres
    server standby $PG_STANDBY:$PG_PORT check port=$PG_PORT inter=2000 rise=2 fall=3 maxconn 5000
    server primary $PG_PRIMARY:$PG_PORT check port=$PG_PORT inter=2000 rise=2 fall=3 backup maxconn 5000

# 状态检查页面
listen stats
    bind *:9696
    mode http
    stats enable
    stats uri /stats
    stats refresh 30s
    stats admin if LOCALHOST
EOF

# 4. 配置 Keepalived
color "配置 Keepalived..." 0

# 获取当前节点角色
if ip addr show | grep -q "$NODE1_IP"; then
    ROLE="MASTER"
    PRIORITY=100
    NODE_ID="1"
elif ip addr show | grep -q "$NODE2_IP"; then
    ROLE="BACKUP"
    PRIORITY=90
    NODE_ID="2"
else
    color "❌ 当前IP不匹配预设的节点IP！" 1
    echo "当前IP: $(hostname -I)"
    echo "期望IP1: $NODE1_IP (MASTER), IP2: $NODE2_IP (BACKUP)"
    exit 1
fi

cat > /etc/keepalived/keepalived.conf <<EOF
vrrp_script chk_haproxy {
    script "killall -0 haproxy"
    interval 2
    weight 2
    fall 3
    rise 2
}

vrrp_instance VI_POSTGRES_$NODE_ID {
    state $ROLE
    interface $INTERFACE
    virtual_router_id 51
    priority $PRIORITY
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        $VIP/24
    }
    track_script {
        chk_haproxy
    }
    notify_master "/etc/keepalived/master.sh"
    notify_backup "/etc/keepalived/backup.sh"
    notify_fault "/etc/keepalived/fault.sh"
    preempt_delay 10
}
EOF

# 创建通知脚本
mkdir -p /etc/keepalived/

cat > /etc/keepalived/master.sh <<'EOF'
#!/bin/bash
NODE=$(hostname)
echo "$(date): Node $NODE became MASTER" >> /var/log/keepalived.log
systemctl start haproxy
echo "$(date): Started HAProxy on MASTER node" >> /var/log/keepalived.log
EOF

cat > /etc/keepalived/backup.sh <<'EOF'
#!/bin/bash
NODE=$(hostname)
echo "$(date): Node $NODE became BACKUP" >> /var/log/keepalived.log
systemctl stop haproxy
echo "$(date): Stopped HAProxy on BACKUP node" >> /var/log/keepalived.log
EOF

cat > /etc/keepalived/fault.sh <<'EOF'
#!/bin/bash
NODE=$(hostname)
echo "$(date): Node $NODE entered FAULT state" >> /var/log/keepalived.log
systemctl stop haproxy
echo "$(date): Stopped HAProxy due to fault" >> /var/log/keepalived.log
EOF

chmod +x /etc/keepalived/*.sh

# 配置 HAProxy 允许绑定特权端口
color "配置 HAProxy 权限..." 0
sed -i 's/^ENABLED=.*/ENABLED=1/' /etc/default/haproxy

# 5. 启动服务
color "启动 HAProxy 和 Keepalived..." 0
systemctl enable haproxy keepalived
systemctl restart haproxy keepalived

# 等待服务启动
sleep 3

if systemctl is-active --quiet haproxy && systemctl is-active --quiet keepalived; then
    color "✅ HAProxy + Keepalived 启动成功！" 0
    echo ""
    echo "📊 高可用配置信息："
    echo "VIP: $VIP"
    echo "本节点: $(hostname -I | awk '{print $1}') ($ROLE)"
    echo "VIP当前位置: $(ip addr show | grep "$VIP" | head -1 | awk '{print $NF}' || echo "NOT FOUND")"
    echo "网络接口: $INTERFACE"
    echo ""
    echo "🔌 连接信息："
    echo "读写连接: psql -h $VIP -p 5432"  # 自动连接主库
    echo "只读连接: psql -h $VIP -p 5433"  # 自动连接备库
    echo "监控页面: http://$VIP:9696/stats"
    echo ""
    echo "🔄 服务管理："
    echo "systemctl status haproxy keepalived"
    echo "systemctl restart haproxy keepalived"
    echo ""
    echo "🔍 验证命令："
    echo "ip addr show | grep $VIP  # 检查VIP位置"
    echo "curl http://$VIP:9696/stats  # 检查监控页面"
    echo "haproxy -v  # 检查HAProxy版本"
    echo ""
    echo "🔧 系统优化已完成："
    echo "• 内核参数已调整以支持高并发连接"
    echo "• 文件描述符限制已设置为 102400"
    echo "• TCP 参数已优化以提高网络性能"
    echo "• HAProxy 和 Keepalived 服务已配置资源限制"
    echo "• 网络连接跟踪参数已优化"
else
    color "❌ 服务启动失败！" 1
    echo "查看日志："
    journalctl -u haproxy -n 20
    journalctl -u keepalived -n 20
    exit 1
fi

3. 集群监控脚本 (monitor_cluster.sh)

#!/bin/bash
# monitor_cluster.sh - PostgreSQL 集群监控脚本

set -euo pipefail

PRIMARY_HOST="10.0.0.24"
STANDBY_HOST="10.0.0.182"
VIP="10.0.0.100"
PG_PORT="5432"
LOG_FILE="/var/log/postgresql_monitor.log"

log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$LOG_FILE"
}

check_cluster_health() {
    local primary_healthy=false
    local standby_healthy=false
    local current_primary=""
    
    # 检查主库连接
    if pg_isready -h "$PRIMARY_HOST" -p "$PG_PORT" -U "postgres" 2>/dev/null; then
        # 检查是否是主库
        if sudo -u postgres psql -h "$PRIMARY_HOST" -p "$PG_PORT" -U "postgres" -tAc "SELECT pg_is_in_recovery();" 2>/dev/null | grep -q "f"; then
            primary_healthy=true
            current_primary="$PRIMARY_HOST"
        fi
    fi
    
    # 检查备库连接
    if pg_isready -h "$STANDBY_HOST" -p "$PG_PORT" -U "postgres" 2>/dev/null; then
        # 检查是否是备库
        if sudo -u postgres psql -h "$STANDBY_HOST" -p "$PG_PORT" -U "postgres" -tAc "SELECT pg_is_in_recovery();" 2>/dev/null | grep -q "t"; then
            standby_healthy=true
            if [ "$primary_healthy" = false ]; then
                current_primary="$STANDBY_HOST"
            fi
        fi
    fi
    
    # 记录状态
    log_message "Cluster Health Check:"
    log_message "  Primary ($PRIMARY_HOST): $(if [ "$primary_healthy" = true ]; then echo "HEALTHY"; else echo "UNHEALTHY"; fi)"
    log_message "  Standby ($STANDBY_HOST): $(if [ "$standby_healthy" = true ]; then echo "HEALTHY"; else echo "UNHEALTHY"; fi)"
    log_message "  Current Primary: $current_primary"
    log_message "  VIP Location: $(ip addr show | grep "$VIP" | head -1 | awk '{print $NF}' || echo "NOT_FOUND")"
    
    # 检查VIP和主库是否在同一节点
    local vip_node=$(ip addr show | grep "$VIP" | head -1 | awk '{print $NF}')
    if [ -n "$current_primary" ] && [ "$vip_node" != "$current_primary" ]; then
        log_message "⚠️  WARNING: VIP and current primary are on different nodes!"
    fi
    
    # 返回状态
    if [ "$primary_healthy" = true ] && [ "$standby_healthy" = true ]; then
        echo "HEALTHY"
        return 0
    elif [ "$primary_healthy" = false ] && [ "$standby_healthy" = true ]; then
        echo "NEEDS_FAILOVER"
        return 1
    else
        echo "CRITICAL"
        return 2
    fi
}

# 执行健康检查
cluster_status=$(check_cluster_health)
echo "Cluster Status: $cluster_status"

# 根据状态采取行动
case "$cluster_status" in
    "NEEDS_FAILOVER")
        echo "❌ 主库故障，需要故障转移！"
        log_message "Automatic failover triggered due to primary failure"
        # 这里可以调用故障转移脚本
        # /path/to/failover_handler.sh failover
        ;;
    "CRITICAL")
        echo "❌ 集群严重故障！"
        log_message "CRITICAL: Both primary and standby are unavailable"
        ;;
    *)
        echo "✅ 集群状态正常"
        ;;
esac

4. 故障转移脚本 (failover_handler.sh)

#!/bin/bash
# failover_handler.sh - PostgreSQL 故障转移处理脚本

set -euo pipefail

PRIMARY_HOST="10.0.0.24"
STANDBY_HOST="10.0.0.182"
VIP="10.0.0.100"
SSH_KEY="/root/.ssh/id_rsa"
PG_USER="postgres"
PG_PORT="5432"

color() {
    local msg="$1"; local status="$2"
    printf "%-60s" "$msg"
    if [[ "$status" == "0" ]]; then
        echo -e "[\033[1;32m  OK  \033[0m]"
    else
        echo -e "[\033[1;31mFAILED\033[0m]"
    fi
}

promote_standby() {
    local standby_host="$1"
    color "提升备库为新主库: $standby_host" 0
    
    # 停止备库服务
    ssh "$standby_host" "systemctl stop postgresql"
    
    # 移除 standby.signal 文件以允许提升
    ssh "$standby_host" "rm -f /pgsql/data/standby.signal"
    
    # 启动为新主库
    ssh "$standby_host" "systemctl start postgresql"
    
    # 等待服务启动
    sleep 10
    
    # 验证是否已提升为主库
    if ssh "$standby_host" "sudo -u postgres psql -tAc 'SELECT pg_is_in_recovery();'" | grep -q "f"; then
        color "✅ 备库已成功提升为主库！" 0
        return 0
    else
        color "❌ 备库提升失败！" 1
        return 1
    fi
}

demote_old_primary() {
    local old_primary="$1"
    color "降级旧主库: $old_primary" 0
    
    # 停止旧主库服务
    ssh "$old_primary" "systemctl stop postgresql"
    
    # 等待一段时间确保不再接受连接
    sleep 5
}

update_haproxy_config() {
    local new_primary="$1"
    color "更新 HAProxy 配置..." 0
    
    # 更新HAProxy配置，将新主库设为主要服务器
    sed -i "s/server primary .*/server primary $new_primary:$PG_PORT check port=$PG_PORT inter=2000 rise=2 fall=3 maxconn 5000/" /etc/haproxy/haproxy.cfg
    sed -i "s/server standby .*/server standby $new_primary:$PG_PORT check port=$PG_PORT inter=2000 rise=2 fall=3 backup maxconn 5000/" /etc/haproxy/haproxy.cfg
    
    # 重新加载HAProxy配置
    systemctl reload haproxy
}

perform_failover() {
    color "开始故障转移程序..." 0
    
    # 检查主库状态
    if pg_isready -h "$PRIMARY_HOST" -p "$PG_PORT" -U "$PG_USER" 2>/dev/null; then
        color "⚠️  主库似乎仍在运行，跳过故障转移" 2
        return 0
    fi
    
    color "❌ 检测到主库故障，开始故障转移..." 0
    
    # 检查备库是否健康
    if ! pg_isready -h "$STANDBY_HOST" -p "$PG_PORT" -U "$PG_USER" 2>/dev/null; then
        color "❌ 备库也不可用，无法进行故障转移！" 1
        exit 1
    fi
    
    # 提升备库为主库
    if promote_standby "$STANDBY_HOST"; then
        # 降级旧主库
        demote_old_primary "$PRIMARY_HOST"
        
        # 更新HAProxy配置
        update_haproxy_config "$STANDBY_HOST"
        
        color "🎉 故障转移完成！" 0
        color "新的主库: $STANDBY_HOST" 0
        color "VIP $VIP 现在指向新主库" 0
        
        # 发送告警邮件（可选）
        echo "PostgreSQL 故障转移完成: $PRIMARY_HOST -> $STANDBY_HOST" | mail -s "PostgreSQL Failover Alert" admin@example.com 2>/dev/null || true
        
    else
        color "❌ 故障转移失败！" 1
        exit 1
    fi
}

case "$1" in
    "check")
        # 检查集群状态
        if pg_isready -h "$PRIMARY_HOST" -p "$PG_PORT" -U "$PG_USER" 2>/dev/null; then
            echo "Primary is healthy: $PRIMARY_HOST"
            if pg_isready -h "$STANDBY_HOST" -p "$PG_PORT" -U "$PG_USER" 2>/dev/null; then
                echo "Standby is healthy: $STANDBY_HOST"
                echo "Status: HEALTHY"
            else
                echo "Standby is unhealthy: $STANDBY_HOST"
                echo "Status: DEGRADED"
            fi
        else
            echo "Primary is unhealthy: $PRIMARY_HOST"
            echo "Status: NEEDS_FAILOVER"
        fi
        ;;
    "failover")
        perform_failover
        ;;
    *)
        echo "Usage: $0 {check|failover}"
        exit 1
        ;;
esac

部署步骤总结：

在 HAProxy1 节点 (10.0.0.101)：

#  安装 HAProxy + Keepalived
chmod +x install_ha_proxy_keepalived.sh
./install_ha_proxy_keepalived.sh

在 HAProxy2 节点 (10.0.0.102)：

# 安装 HAProxy + Keepalived
chmod +x install_ha_proxy_keepalived.sh
./install_ha_proxy_keepalived.sh

在监控节点（可选，通常在应用服务器）：

# 监控脚本
chmod +x monitor_cluster.sh failover_handler.sh

在所有节点上设置定时任务：

# 添加到 crontab
echo "*/5 * * * * /path/to/monitor_cluster.sh >> /var/log/monitor.log 2>&1" >> /etc/crontab