PostgreSQL高可用集群搭建指南

1. 环境规划

1.1 服务器规划

主机名	外网IP	内网IP	角色	配置要求	硬盘配置
pg-etcd-01	192.168.199.120	10.123.123.120	etcd节点	2C2G	系统盘40G + 数据盘10G
pg-master-01	192.168.199.121	10.123.123.121	PostgreSQL主节点	4C8G	系统盘40G + 数据盘100G + WAL盘50G
pg-slave-01	192.168.199.122	10.123.123.122	PostgreSQL从节点	4C8G	系统盘40G + 数据盘100G + WAL盘50G
pg-proxy-01	192.168.199.123	10.123.123.123	HAProxy代理	2C2G	系统盘40G + 日志盘10G

1.2 PVE虚拟化硬盘配置详解

1.2.1 etcd节点 (pg-etcd-01)

磁盘配置：
- 系统盘：40GB (virtio-scsi, /dev/sda)
  - 挂载点：/ (根分区)
  - 文件系统：ext4
  - 用途：操作系统和基础软件

- 数据盘：10GB (virtio-scsi, /dev/sdb)
  - 挂载点：/var/lib/etcd
  - 文件系统：ext4
  - 用途：etcd数据存储

1.2.2 PostgreSQL节点 (pg-master-01, pg-slave-01)

磁盘配置：
- 系统盘：40GB (virtio-scsi, /dev/sda)
  - 挂载点：/ (根分区)
  - 文件系统：ext4
  - 用途：操作系统、基础软件和系统日志

- 数据盘：100GB (virtio-scsi, /dev/sdb)
  - 挂载点：/data/postgresql
  - 文件系统：ext4
  - 用途：PostgreSQL数据文件存储
  - 建议：使用SSD存储以提高性能

- WAL日志盘：50GB (virtio-scsi, /dev/sdc)
  - 挂载点：/data/postgresql/wal
  - 文件系统：ext4
  - 用途：PostgreSQL WAL日志文件
  - 建议：独立高速存储，提高写入性能

1.2.3 HAProxy节点 (pg-proxy-01)

磁盘配置：
- 系统盘：40GB (virtio-scsi, /dev/sda)
  - 挂载点：/ (根分区)
  - 文件系统：ext4
  - 用途：操作系统、基础软件和系统日志

- 日志盘：10GB (virtio-scsi, /dev/sdb)
  - 挂载点：/var/log
  - 文件系统：ext4
  - 用途：HAProxy日志和系统日志

1.2.4 PVE创建虚拟机建议

# 创建虚拟机的基本配置
# etcd节点
qm create 120 --name pg-etcd-01 --memory 2048 --cores 2 --net0 virtio,bridge=vmbr0 --net1 virtio,bridge=vmbr1

# PostgreSQL主节点
qm create 121 --name pg-master-01 --memory 8192 --cores 4 --net0 virtio,bridge=vmbr0 --net1 virtio,bridge=vmbr1

# PostgreSQL从节点
qm create 122 --name pg-slave-01 --memory 8192 --cores 4 --net0 virtio,bridge=vmbr0 --net1 virtio,bridge=vmbr1

# HAProxy节点
qm create 123 --name pg-proxy-01 --memory 2048 --cores 2 --net0 virtio,bridge=vmbr0 --net1 virtio,bridge=vmbr1

1.2.5 存储性能建议

系统盘：可使用普通存储，对性能要求不高
PostgreSQL数据盘：强烈建议使用SSD存储
WAL日志盘：建议使用高速SSD，独立于数据盘
etcd数据盘：建议使用SSD，保证低延迟
备份存储：可使用大容量机械硬盘，建议至少500GB

1.3 网络规划说明

外网网段：192.168.199.0/24 - 用于客户端访问和管理
内网网段：10.123.123.0/24 - 用于集群内部通信（数据库复制、etcd通信等）
网络策略：
- 客户端通过外网IP访问HAProxy
- 集群内部通信使用内网IP，提高安全性和性能
- etcd、PostgreSQL复制、Patroni API使用内网通信

1.4 软件版本与资源需求

操作系统：Ubuntu 22.04.5 LTS (Jammy Jellyfish)
内存需求说明：
- etcd节点：最低2GB内存，轻量级部署足够
- PostgreSQL节点：建议8GB以上内存，数据库性能与内存直接相关
- HAProxy节点：最低2GB内存，主要用于连接转发
PostgreSQL：16.x（最新稳定版）
Patroni：3.2.0
etcd：3.5.9
HAProxy：2.4
Python：3.10（Ubuntu 22.04自带）

2. 系统初始化

2.1 更换Ubuntu软件源（所有节点执行）

# 备份原有软件源配置
sudo cp /etc/apt/sources.list /etc/apt/sources.list.backup

# 更换为阿里云镜像源（推荐，国内访问速度快）
# sudo tee /etc/apt/sources.list <<EOF
# # 阿里云Ubuntu 22.04 LTS镜像源
# deb http://mirrors.aliyun.com/ubuntu/ jammy main restricted universe multiverse
# deb-src http://mirrors.aliyun.com/ubuntu/ jammy main restricted universe multiverse

# deb http://mirrors.aliyun.com/ubuntu/ jammy-security main restricted universe multiverse
# deb-src http://mirrors.aliyun.com/ubuntu/ jammy-security main restricted universe multiverse

# deb http://mirrors.aliyun.com/ubuntu/ jammy-updates main restricted universe multiverse
# deb-src http://mirrors.aliyun.com/ubuntu/ jammy-updates main restricted universe multiverse

# deb http://mirrors.aliyun.com/ubuntu/ jammy-backports main restricted universe multiverse
# deb-src http://mirrors.aliyun.com/ubuntu/ jammy-backports main restricted universe multiverse
# EOF

# 或者使用清华大学镜像源（备选方案）
# sudo tee /etc/apt/sources.list <<EOF
# # 清华大学Ubuntu 22.04 LTS镜像源
# deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy main restricted universe multiverse
# 
# deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy-updates main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy-updates main restricted universe multiverse
# 
# deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy-backports main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy-backports main restricted universe multiverse
# 
# deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy-security main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ jammy-security main restricted universe multiverse
# EOF

# 或者使用中科大镜像源（备选方案）
sudo tee /etc/apt/sources.list <<EOF
# 中科大Ubuntu 22.04 LTS镜像源
deb https://mirrors.ustc.edu.cn/ubuntu/ jammy main restricted universe multiverse
deb-src https://mirrors.ustc.edu.cn/ubuntu/ jammy main restricted universe multiverse

deb https://mirrors.ustc.edu.cn/ubuntu/ jammy-updates main restricted universe multiverse
deb-src https://mirrors.ustc.edu.cn/ubuntu/ jammy-updates main restricted universe multiverse

deb https://mirrors.ustc.edu.cn/ubuntu/ jammy-backports main restricted universe multiverse
deb-src https://mirrors.ustc.edu.cn/ubuntu/ jammy-backports main restricted universe multiverse

deb https://mirrors.ustc.edu.cn/ubuntu/ jammy-security main restricted universe multiverse
deb-src https://mirrors.ustc.edu.cn/ubuntu/ jammy-security main restricted universe multiverse
EOF

# 清理apt缓存
sudo apt clean
sudo apt autoclean

# 更新软件包索引
sudo apt update

# 如果仍然有问题，可以尝试以下命令修复
# sudo apt --fix-broken install
# sudo dpkg --configure -a

2.2 所有节点执行基础配置

# 升级系统（可选，建议在生产环境中谨慎执行）
# sudo apt upgrade -y

# 安装基础工具
sudo apt install -y curl wget vim net-tools htop tree gnupg2 lsb-release

# 配置主机名解析
sudo tee -a /etc/hosts <<EOF
# 外网IP
192.168.199.120 pg-etcd-01-ext
192.168.199.121 pg-master-01-ext
192.168.199.122 pg-slave-01-ext
192.168.199.123 pg-proxy-01-ext

# 内网IP（集群内部通信）
10.123.123.120 pg-etcd-01
10.123.123.121 pg-master-01
10.123.123.122 pg-slave-01
10.123.123.123 pg-proxy-01
EOF

# 配置时间同步
sudo apt install -y chrony
sudo systemctl enable --now chrony

# 优化内核参数（所有节点执行）
sudo tee -a /etc/sysctl.conf <<EOF
# PostgreSQL优化
vm.swappiness = 1
vm.overcommit_memory = 2
vm.overcommit_ratio = 80
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.file-max = 2097152
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
EOF

# 应用内核参数
sudo sysctl -p

# 配置用户限制（PostgreSQL节点执行）
# 在pg-master-01和pg-slave-01节点执行
sudo tee -a /etc/security/limits.conf <<EOF
postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 32768
postgres hard nproc 32768
EOF

2.3 配置防火墙

2.3.1 所有节点通用配置

# 安装并配置ufw
sudo apt install -y ufw
sudo ufw --force enable

# 所有节点都需要开放的端口
sudo ufw allow 22/tcp      # SSH - 所有节点都需要

2.3.2 etcd节点防火墙配置 (pg-etcd-01)

# 在pg-etcd-01节点执行
sudo ufw allow 2379/tcp    # etcd客户端API端口
sudo ufw allow 2380/tcp    # etcd节点间通信端口

2.3.3 PostgreSQL节点防火墙配置 (pg-master-01, pg-slave-01)

# 在pg-master-01和pg-slave-01节点执行
sudo ufw allow 5432/tcp    # PostgreSQL数据库端口
sudo ufw allow 8008/tcp    # Patroni REST API端口

2.3.4 HAProxy节点防火墙配置 (pg-proxy-01)

# 在pg-proxy-01节点执行
sudo ufw allow 5000/tcp    # HAProxy写端口(主节点连接)
sudo ufw allow 5001/tcp    # HAProxy读端口(从节点连接)
sudo ufw allow 8404/tcp    # HAProxy统计页面

2.3.5 验证防火墙配置

# 在所有节点执行，检查防火墙状态
sudo ufw status verbose

3. 安装etcd集群

3.1 在pg-etcd-01节点安装etcd

# 创建etcd用户
sudo useradd --system --shell /bin/false --home /var/lib/etcd etcd

# 下载etcd（请访问 https://github.com/etcd-io/etcd/releases 查看最新稳定版本）
ETCD_VER=v3.6.3  # 使用与文档中指定的版本一致

# 方法1：从GitHub官方下载（国外网络）
wget https://github.com/etcd-io/etcd/releases/download/${ETCD_VER}/etcd-${ETCD_VER}-linux-amd64.tar.gz

# 方法2：如果GitHub下载太慢，使用国内镜像（推荐）
# wget https://mirror.ghproxy.com/https://github.com/etcd-io/etcd/releases/download/${ETCD_VER}/etcd-${ETCD_VER}-linux-amd64.tar.gz

# 方法3：使用清华大学镜像（备选）
# wget https://mirrors.tuna.tsinghua.edu.cn/github-release/etcd-io/etcd/LatestRelease/etcd-${ETCD_VER}-linux-amd64.tar.gz

tar xzf etcd-${ETCD_VER}-linux-amd64.tar.gz

# 安装etcd
sudo cp etcd-${ETCD_VER}-linux-amd64/etcd* /usr/local/bin/
sudo chown root:root /usr/local/bin/etcd*
sudo chmod +x /usr/local/bin/etcd*

# 创建数据目录
sudo mkdir -p /var/lib/etcd
sudo chown etcd:etcd /var/lib/etcd
sudo chmod 700 /var/lib/etcd

# 创建配置目录
sudo mkdir -p /etc/etcd

3.2 配置etcd

# 创建etcd配置文件
sudo tee /etc/etcd/etcd.conf <<EOF
# etcd配置文件
ETCD_NAME="pg-etcd-01"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_LISTEN_PEER_URLS="http://10.123.123.120:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.123.123.120:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.123.123.120:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://10.123.123.120:2379"
ETCD_INITIAL_CLUSTER="pg-etcd-01=http://10.123.123.120:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-pg"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_AUTO_COMPACTION_RETENTION="1"
ETCD_QUOTA_BACKEND_BYTES="8589934592"
# 启用etcd v2 API以兼容Patroni
ETCD_ENABLE_V2="true"
EOF

# 创建systemd服务文件
sudo tee /etc/systemd/system/etcd.service <<EOF
[Unit]
Description=etcd key-value store
Documentation=https://github.com/etcd-io/etcd
After=network.target

[Service]
Type=notify
User=etcd
EnvironmentFile=/etc/etcd/etcd.conf
ExecStart=/usr/local/bin/etcd
Restart=always
RestartSec=10s
LimitNOFILE=40000

[Install]
WantedBy=multi-user.target
EOF

# 启动etcd服务
sudo systemctl daemon-reload
sudo systemctl enable etcd
sudo systemctl start etcd

# 检查etcd状态
sudo systemctl status etcd
# 使用v3 API检查etcd健康状态
ETCDCTL_API=3 etcdctl --endpoints=http://127.0.0.1:2379 endpoint health

4. 安装PostgreSQL

4.1 在pg-master-01和pg-slave-01节点安装PostgreSQL

# 添加PostgreSQL官方源（使用推荐的现代方法）
# 创建密钥目录（如果不存在）
sudo mkdir -p /etc/apt/keyrings

# 下载并添加PostgreSQL GPG密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/keyrings/postgresql.gpg

# 方法1：添加PostgreSQL官方源（国外网络）
echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# 方法2：如果官方源太慢，使用清华大学镜像（推荐，国内网络）
# echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ jammy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# 方法3：使用阿里云镜像（备选）
# echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] http://mirrors.aliyun.com/postgresql/repos/apt/ jammy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# 方法4：使用中科大镜像（备选）
# echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] https://mirrors.ustc.edu.cn/postgresql/repos/apt/ jammy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# 更新包索引
sudo apt update -y

# 安装PostgreSQL 16
sudo apt install -y postgresql-16 postgresql-client-16 postgresql-contrib-16

# 停止并禁用默认的PostgreSQL服务
# 注意：在Ubuntu 22.04上，PostgreSQL服务名称可能是postgresql或postgresql@16-main
sudo systemctl stop postgresql
sudo systemctl disable postgresql
sudo systemctl stop postgresql@16-main
sudo systemctl disable postgresql@16-main

# 验证PostgreSQL服务已停止
systemctl status postgresql.service
systemctl status postgresql@16-main.service

# 创建数据目录和WAL目录
sudo mkdir -p /data/postgresql/16/main
sudo mkdir -p /data/postgresql/wal/16
sudo chown -R postgres:postgres /data/postgresql
sudo chmod 700 /data/postgresql/16/main
sudo chmod 700 /data/postgresql/wal/16

# 创建WAL目录的软链接（PostgreSQL会使用这个路径）
sudo ln -sf /data/postgresql/wal/16 /data/postgresql/16/main/pg_wal

5. 安装和配置Patroni

5.1 在pg-master-01和pg-slave-01节点安装Patroni

# 安装Python和pip (Ubuntu 22.04.5自带Python 3.10)
sudo apt install -y python3 python3-pip python3-venv python3-dev build-essential

# 创建Patroni虚拟环境
sudo mkdir -p /opt/patroni
sudo python3 -m venv /opt/patroni/venv
sudo chown -R postgres:postgres /opt/patroni

# 切换到postgres用户安装Patroni
sudo -u postgres bash -c "
source /opt/patroni/venv/bin/activate
pip install --upgrade pip
pip install patroni[etcd] psycopg2-binary
"

# 创建Patroni配置目录
sudo mkdir -p /etc/patroni
sudo chown postgres:postgres /etc/patroni

5.2 配置Patroni主节点（pg-master-01）

sudo tee /etc/patroni/patroni.yml <<EOF
scope: postgres-cluster
namespace: /db/
name: pg-master-01

restapi:
  listen: 10.123.123.121:8008
  connect_address: 10.123.123.121:8008

etcd3:
  hosts:
  - 10.123.123.120:2379
  protocol: http

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 30
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_connections: 200
        max_wal_senders: 10
        wal_keep_size: 1024
        max_replication_slots: 10
        max_worker_processes: 8
        wal_log_hints: "on"
        track_commit_timestamp: "on"
        archive_mode: "on"
        archive_command: '/bin/true'
        shared_buffers: 2GB
        work_mem: 64MB
        maintenance_work_mem: 512MB
        effective_cache_size: 6GB
        checkpoint_completion_target: 0.9
        wal_buffers: 16MB
        default_statistics_target: 100
        random_page_cost: 1.1
        effective_io_concurrency: 200
        log_destination: 'csvlog'
        logging_collector: 'on'
        log_directory: 'log'
        log_filename: 'postgresql-%Y-%m-%d_%H%M%S.log'
        log_min_duration_statement: 1000
        log_checkpoints: 'on'
        log_connections: 'on'
        log_disconnections: 'on'
        log_lock_waits: 'on'
        log_temp_files: 0
        track_activities: 'on'
        track_counts: 'on'
        track_io_timing: 'on'
        track_functions: 'pl'
  initdb:
  - encoding: UTF8
  - data-checksums
  pg_hba:
  - host replication replicator 10.123.123.0/24 md5
  - host replication replicator 192.168.199.0/24 md5
  - host all all 10.123.123.0/24 md5
  - host all all 192.168.199.0/24 md5
  - host all all 127.0.0.1/32 trust
  users:
    admin:
      password: admin123
      options:
        - createrole
        - createdb
    replicator:
      password: replica123
      options:
        - replication

postgresql:
  listen: 10.123.123.121:5432
  connect_address: 10.123.123.121:5432
  data_dir: /data/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replica123
    superuser:
      username: postgres
      password: postgres123
  parameters:
    unix_socket_directories: '/var/run/postgresql'

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false
EOF

# 设置配置文件权限
sudo chown postgres:postgres /etc/patroni/patroni.yml
sudo chmod 600 /etc/patroni/patroni.yml

5.3 配置Patroni从节点（pg-slave-01）

sudo tee /etc/patroni/patroni.yml <<EOF
scope: postgres-cluster
namespace: /db/
name: pg-slave-01

restapi:
  listen: 10.123.123.122:8008
  connect_address: 10.123.123.122:8008

etcd3:
  hosts:
  - 10.123.123.120:2379
  protocol: http

postgresql:
  listen: 10.123.123.122:5432
  connect_address: 10.123.123.122:5432
  data_dir: /data/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replica123
    superuser:
      username: postgres
      password: postgres123
  parameters:
    unix_socket_directories: '/var/run/postgresql'

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false
EOF

# 设置配置文件权限
sudo chown postgres:postgres /etc/patroni/patroni.yml
sudo chmod 600 /etc/patroni/patroni.yml

5.4 创建Patroni systemd服务

在两个PostgreSQL节点上执行：

sudo tee /etc/systemd/system/patroni.service <<EOF
[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target

[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/opt/patroni/venv/bin/patroni /etc/patroni/patroni.yml
KillMode=process
TimeoutSec=30
Restart=no

[Install]
WantedBy=multi-user.target
EOF

# 重载systemd配置
sudo systemctl daemon-reload

5.5 彻底清理并初始化PostgreSQL数据目录

# 完全停止所有相关服务
sudo systemctl stop patroni 2>/dev/null || true
sudo systemctl stop postgresql 2>/dev/null || true
sudo systemctl stop postgresql@16-main 2>/dev/null || true

# 设置正确的所有权和权限
sudo rm -rf /data/postgresql/16/main
sudo rm -rf /data/postgresql/wal/16
    
# 重新创建
sudo -u postgres mkdir -p /data/postgresql/16/main
sudo -u postgres mkdir -p /data/postgresql/wal/16
sudo -u postgres chmod 700 /data/postgresql/16/main
sudo -u postgres chmod 700 /data/postgresql/wal/16
sudo -u postgres chown -R postgres:postgres /data/postgresql
sudo -u postgres ln -sf /data/postgresql/wal/16 /data/postgresql/16/main/pg_wal

# 第五步：启动主节点（仅在pg-master-01执行）
# 在pg-master-01上执行：
sudo systemctl enable patroni
sudo systemctl start patroni

# 检查Patroni状态和日志
sudo systemctl status patroni --no-pager -l
# 检查集群状态
sudo -u postgres /opt/patroni/venv/bin/patronictl -c /etc/patroni/patroni.yml list

5.6 从节点启动步骤（在pg-slave-01执行）

# 确保主节点已经成功初始化后再执行此步骤
# 首先检查主节点状态
curl -s http://10.123.123.121:8008/master

# 完全停止所有相关服务
sudo systemctl stop patroni 2>/dev/null || true
sudo systemctl stop postgresql 2>/dev/null || true
sudo systemctl stop postgresql@16-main 2>/dev/null || true

# 设置正确的所有权和权限
sudo rm -rf /data/postgresql/16/main
sudo rm -rf /data/postgresql/wal/16
    
# 重新创建
sudo -u postgres mkdir -p /data/postgresql/16/main
sudo -u postgres mkdir -p /data/postgresql/wal/16
sudo -u postgres chmod 700 /data/postgresql/16/main
sudo -u postgres chmod 700 /data/postgresql/wal/16
sudo -u postgres chown -R postgres:postgres /data/postgresql
sudo -u postgres ln -sf /data/postgresql/wal/16 /data/postgresql/16/main/pg_wal

# 如果主节点正常，启动从节点
sudo systemctl enable patroni
sudo systemctl start patroni
sudo systemctl restart patroni

# 检查集群状态
sudo -u postgres /opt/patroni/venv/bin/patronictl -c /etc/patroni/patroni.yml list

5.7 验证复制状态

# 验证复制状态
sudo -u postgres psql -h 10.123.123.121 -p 5432 -c "SELECT * FROM pg_stat_replication;"

6. 安装和配置HAProxy

6.1 在pg-proxy-01节点安装HAProxy

# 安装HAProxy
sudo apt install -y haproxy

# 备份原配置文件
sudo cp /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.bak

6.2 配置HAProxy

sudo tee /etc/haproxy/haproxy.cfg <<EOF
global
    log /dev/log local0
    log /dev/log local1 notice
    chroot /var/lib/haproxy
    stats socket /run/haproxy/admin.sock mode 660 level admin
    stats timeout 30s
    user haproxy
    group haproxy
    daemon

defaults
    log global
    mode tcp
    option tcplog
    option dontlognull
    timeout connect 5000
    timeout client 50000
    timeout server 50000
    errorfile 400 /etc/haproxy/errors/400.http
    errorfile 403 /etc/haproxy/errors/403.http
    errorfile 408 /etc/haproxy/errors/408.http
    errorfile 500 /etc/haproxy/errors/500.http
    errorfile 502 /etc/haproxy/errors/502.http
    errorfile 503 /etc/haproxy/errors/503.http
    errorfile 504 /etc/haproxy/errors/504.http

# HAProxy统计页面
listen stats
    bind *:8404
    mode http
    stats enable
    stats uri /stats
    stats refresh 30s
    stats admin if TRUE

# PostgreSQL写连接（主节点）
listen postgres_write
    bind *:5000
    mode tcp
    option httpchk GET /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-master-01 10.123.123.121:5432 maxconn 100 check port 8008
    server pg-slave-01 10.123.123.122:5432 maxconn 100 check port 8008 backup

# PostgreSQL读连接（从节点）
listen postgres_read
    bind *:5001
    mode tcp
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-master-01 10.123.123.121:5432 maxconn 100 check port 8008
    server pg-slave-01 10.123.123.122:5432 maxconn 100 check port 8008
EOF

# 启动HAProxy
sudo systemctl enable haproxy
sudo systemctl restart haproxy
sudo systemctl status haproxy

7. 集群验证和测试

7.1 检查集群状态

# 检查Patroni集群状态
sudo -u postgres /opt/patroni/venv/bin/patronictl -c /etc/patroni/patroni.yml list

# 检查etcd状态
ETCDCTL_API=3 etcdctl --endpoints=http://10.123.123.120:2379 endpoint health

# 验证etcd API版本兼容性
# 检查etcd是否支持v3 API
ETCDCTL_API=3 etcdctl --endpoints=http://10.123.123.120:2379 version

# 检查HAProxy状态
curl http://192.168.199.123:8404/stats

7.2 数据库连接测试

# 测试写连接（连接到主节点）
psql -h 192.168.199.123 -p 5000 -U admin -d postgres

# 测试读连接（连接到从节点）
psql -h 192.168.199.123 -p 5001 -U admin -d postgres

# 在主节点创建测试数据
psql -h 192.168.199.123 -p 5000 -U admin -d postgres -c "
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO test_table (name) VALUES ('test1'), ('test2'), ('test3');
"

# 在从节点验证数据同步
psql -h 192.168.199.123 -p 5001 -U admin -d postgres -c "SELECT * FROM test_table;"

7.3 故障转移测试

# 模拟主节点故障
# 在pg-master-01上执行：
sudo systemctl stop patroni

# 检查集群状态，应该看到自动故障转移
sudo -u postgres /opt/patroni/venv/bin/patronictl -c /etc/patroni/patroni.yml list

# 恢复原主节点
sudo systemctl start patroni

# 再次检查集群状态
sudo -u postgres /opt/patroni/venv/bin/patronictl -c /etc/patroni/patroni.yml list

8. 监控配置

8.1 安装PostgreSQL Exporter

在PostgreSQL节点上安装：

# 下载postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
tar xzf postgres_exporter-0.15.0.linux-amd64.tar.gz
sudo cp postgres_exporter-0.15.0.linux-amd64/postgres_exporter /usr/local/bin/

# 创建监控用户
sudo -u postgres psql -c "
CREATE USER postgres_exporter WITH PASSWORD 'exporter123';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;
"

# 创建systemd服务
sudo tee /etc/systemd/system/postgres_exporter.service <<EOF
[Unit]
Description=Prometheus PostgreSQL Exporter
After=network.target

[Service]
Type=simple
Restart=always
User=postgres
Environment=DATA_SOURCE_NAME=postgresql://postgres_exporter:exporter123@localhost:5432/postgres?sslmode=disable
ExecStart=/usr/local/bin/postgres_exporter

[Install]
WantedBy=multi-user.target
EOF

# 启动服务
sudo systemctl daemon-reload
sudo systemctl enable postgres_exporter
sudo systemctl start postgres_exporter

9. 日常维护

9.1 常用管理命令

# 查看集群状态
sudo -u postgres /opt/patroni/venv/bin/patronictl -c /etc/patroni/patroni.yml list

# 手动故障转移
sudo -u postgres /opt/patroni/venv/bin/patronictl -c /etc/patroni/patroni.yml failover

# 重启集群
sudo -u postgres /opt/patroni/venv/bin/patronictl -c /etc/patroni/patroni.yml restart postgres-cluster

# 重新加载配置
sudo -u postgres /opt/patroni/venv/bin/patronictl -c /etc/patroni/patroni.yml reload postgres-cluster

9.2 备份策略

# 创建备份脚本
sudo tee /opt/backup_postgres.sh <<EOF
#!/bin/bash
BACKUP_DIR="/backup/postgresql"
DATE=\$(date +%Y%m%d_%H%M%S)

mkdir -p \$BACKUP_DIR

# 全量备份
pg_basebackup -h 192.168.199.123 -p 5001 -U admin -D \$BACKUP_DIR/base_\$DATE -Ft -z -Xs -v

# 清理7天前的备份
find \$BACKUP_DIR -name "base_*" -mtime +7 -exec rm -rf {} \;
EOF

sudo chmod +x /opt/backup_postgres.sh

# 添加到crontab（每天凌晨2点备份）
echo "0 2 * * * /opt/backup_postgres.sh" | sudo crontab -

9.3 性能优化建议

内存配置优化：
- shared_buffers: 系统内存的25%
- effective_cache_size: 系统内存的75%
- work_mem: 根据并发连接数调整
磁盘I/O优化：
- 使用SSD存储
- 将WAL日志和数据文件分离到不同磁盘
- 调整checkpoint_completion_target
连接池配置：
- 考虑使用PgBouncer作为连接池
- 限制最大连接数
定期维护：
- 定期执行VACUUM和ANALYZE
- 监控表膨胀情况
- 定期更新统计信息

10. 故障排除

10.1 常见问题

数据目录系统ID无效问题：

错误信息：data dir for the cluster is not empty, but system ID is invalid; consider doing reinitialize
原因：PostgreSQL数据目录已存在但不是有效的PostgreSQL数据目录，或者系统ID不匹配，或者存在隐藏文件

完整解决方案：

# 第一步：完全停止所有服务
sudo systemctl stop patroni
sudo systemctl stop postgresql
sudo systemctl stop postgresql@16-main

# 第二步：卸载并重新挂载磁盘（确保清理所有缓存）
sudo umount /data/postgresql/wal 2>/dev/null || true
sudo umount /data/postgresql 2>/dev/null || true
sudo mount -a

# 第三步：彻底删除数据目录（包括隐藏文件）
sudo rm -rf /data/postgresql/16
sudo rm -rf /data/postgresql/wal/16

# 第四步：重新创建目录结构
sudo mkdir -p /data/postgresql/16/main
sudo mkdir -p /data/postgresql/wal/16

# 第五步：设置正确的权限
sudo chown -R postgres:postgres /data/postgresql
sudo chmod 700 /data/postgresql
sudo chmod 700 /data/postgresql/16
sudo chmod 700 /data/postgresql/16/main
sudo chmod 700 /data/postgresql/wal
sudo chmod 700 /data/postgresql/wal/16

# 第六步：重新创建软链接
sudo -u postgres ln -sf /data/postgresql/wal/16 /data/postgresql/16/main/pg_wal

# 第七步：清理etcd中的集群信息（重要！）
ETCDCTL_API=3 etcdctl --endpoints=http://10.123.123.120:2379 del --prefix /db/

# 第八步：验证目录为空
echo "验证数据目录为空："
sudo ls -la /data/postgresql/16/main/
sudo ls -la /data/postgresql/wal/16/

# 第九步：重启Patroni服务
sudo systemctl start patroni

# 第十步：监控初始化过程
journalctl -u patroni -f

如果问题仍然存在，使用强制重新初始化：

# 在Patroni配置中添加强制初始化选项（临时使用）
sudo -u postgres tee -a /etc/patroni/patroni.yml <<EOF

# 临时添加强制初始化选项
bootstrap:
  method: initdb
  initdb:
    - encoding: UTF8
    - data-checksums
EOF

# 重启Patroni
sudo systemctl restart patroni

# 初始化完成后，移除临时配置
sudo -u postgres sed -i '/# 临时添加强制初始化选项/,$d' /etc/patroni/patroni.yml

Patroni与etcd API版本不匹配问题：
- 错误信息：Failed to get list of machines from http://10.123.123.120:2379/v2: EtcdException('Bad response : 404 page not found\n')
- 原因：Patroni默认使用etcd v2 API，而新版etcd默认只启用v3 API
- 解决方案：
  - 方法1：在etcd配置中启用v2 API：ETCD_ENABLE_V2="true"
  - 方法2：使用Patroni的etcd3配置项而不是etcd配置项：
    # 替换 etcd: hosts: 10.123.123.120:2379 # 为 etcd3: hosts: - 10.123.123.120:2379
  - 确保两个PostgreSQL节点的配置文件中都添加了相同的设置
  - 重启etcd和Patroni服务：
    # 在etcd节点上 sudo systemctl restart etcd # 在PostgreSQL节点上 sudo systemctl restart patroni
Patroni集群初始化问题：
- 如果集群状态显示为"uninitialized"且两个节点都是"stopped"状态
- 原因：Patroni无法自动初始化集群，通常是配置问题或etcd连接问题
- 解决方案：
  - 检查etcd连接是否正常：
    # 测试etcd连接 curl http://10.123.123.120:2379/health
  - 检查Patroni配置文件语法
  - 查看Patroni日志：
    journalctl -u patroni -f
  - 确保只有主节点配置了bootstrap部分
  - 重启Patroni服务：
    sudo systemctl restart patroni
Patroni无法启动：
- 检查etcd连接
- 检查配置文件语法
- 查看日志：journalctl -u patroni -f
主从同步延迟：
- 检查网络连接
- 调整wal_keep_size参数
- 监控复制槽状态
HAProxy健康检查失败：
- 检查Patroni REST API是否正常
- 验证端口8008是否开放
- 检查防火墙设置

10.2 日志查看

# 查看Patroni日志
journalctl -u patroni -f

# 查看PostgreSQL日志
sudo -u postgres tail -f /data/postgresql/16/main/log/postgresql-*.log

# 查看HAProxy日志
journalctl -u haproxy -f

# 查看etcd日志
journalctl -u etcd -f

11. 总结

本文档提供了一个完整的PostgreSQL高可用集群搭建指南，包括：

11.1 架构组件

etcd：作为分布式配置存储和集群协调服务
Patroni：作为PostgreSQL集群管理工具，提供自动故障转移
HAProxy：作为负载均衡器，实现读写分离和高可用访问
PostgreSQL 16：数据库核心服务

11.2 实现功能

自动故障检测和转移：当主节点故障时，自动提升从节点为主节点
读写分离：写操作路由到主节点，读操作可以路由到从节点
零停机维护：支持滚动升级和维护操作
数据一致性保证：通过流复制确保数据同步

11.3 部署要点

网络规划：使用双网卡配置，内外网分离
存储配置：数据盘和WAL日志盘分离，提高I/O性能
安全配置：防火墙规则、用户权限、密码策略
监控配置：集成PostgreSQL Exporter进行性能监控

11.4 运维建议

定期备份：建议每日进行全量备份
性能监控：监控数据库性能指标和集群状态
日志管理：定期清理和归档日志文件
安全更新：及时更新系统和软件补丁

11.5 注意事项

在生产环境中使用时，请根据实际情况调整配置参数
建议在测试环境中充分验证故障转移和恢复流程
定期进行灾难恢复演练，确保备份和恢复流程的有效性
关注PostgreSQL和相关组件的版本更新和安全公告

通过本指南搭建的PostgreSQL高可用集群，能够为生产环境提供稳定、可靠的数据库服务。

1. 环境规划​

1.1 服务器规划​

1.2 PVE虚拟化硬盘配置详解​

1.2.1 etcd节点 (pg-etcd-01)​

1.2.2 PostgreSQL节点 (pg-master-01, pg-slave-01)​

1.2.3 HAProxy节点 (pg-proxy-01)​

1.2.4 PVE创建虚拟机建议​

1.2.5 存储性能建议​

1.3 网络规划说明​

1.4 软件版本与资源需求​

2. 系统初始化​

2.1 更换Ubuntu软件源（所有节点执行）​

2.2 所有节点执行基础配置​

2.3 配置防火墙​

2.3.1 所有节点通用配置​

2.3.2 etcd节点防火墙配置 (pg-etcd-01)​

2.3.3 PostgreSQL节点防火墙配置 (pg-master-01, pg-slave-01)​

2.3.4 HAProxy节点防火墙配置 (pg-proxy-01)​

2.3.5 验证防火墙配置​

3. 安装etcd集群​

3.1 在pg-etcd-01节点安装etcd​

3.2 配置etcd​

4. 安装PostgreSQL​

4.1 在pg-master-01和pg-slave-01节点安装PostgreSQL​

5. 安装和配置Patroni​

5.1 在pg-master-01和pg-slave-01节点安装Patroni​

5.2 配置Patroni主节点（pg-master-01）​

5.3 配置Patroni从节点（pg-slave-01）​

5.4 创建Patroni systemd服务​

5.5 彻底清理并初始化PostgreSQL数据目录​

5.6 从节点启动步骤（在pg-slave-01执行）​

5.7 验证复制状态​

6. 安装和配置HAProxy​

6.1 在pg-proxy-01节点安装HAProxy​

6.2 配置HAProxy​

7. 集群验证和测试​

7.1 检查集群状态​

7.2 数据库连接测试​

7.3 故障转移测试​

8. 监控配置​

8.1 安装PostgreSQL Exporter​

9. 日常维护​

9.1 常用管理命令​

9.2 备份策略​

9.3 性能优化建议​

10. 故障排除​

10.1 常见问题​

10.2 日志查看​

11. 总结​

11.1 架构组件​

11.2 实现功能​

11.3 部署要点​

11.4 运维建议​

11.5 注意事项​

1. 环境规划

1.1 服务器规划

1.2 PVE虚拟化硬盘配置详解

1.2.1 etcd节点 (pg-etcd-01)

1.2.2 PostgreSQL节点 (pg-master-01, pg-slave-01)

1.2.3 HAProxy节点 (pg-proxy-01)

1.2.4 PVE创建虚拟机建议

1.2.5 存储性能建议

1.3 网络规划说明

1.4 软件版本与资源需求

2. 系统初始化

2.1 更换Ubuntu软件源（所有节点执行）

2.2 所有节点执行基础配置

2.3 配置防火墙

2.3.1 所有节点通用配置

2.3.2 etcd节点防火墙配置 (pg-etcd-01)

2.3.3 PostgreSQL节点防火墙配置 (pg-master-01, pg-slave-01)

2.3.4 HAProxy节点防火墙配置 (pg-proxy-01)

2.3.5 验证防火墙配置

3. 安装etcd集群

3.1 在pg-etcd-01节点安装etcd

3.2 配置etcd

4. 安装PostgreSQL

4.1 在pg-master-01和pg-slave-01节点安装PostgreSQL

5. 安装和配置Patroni

5.1 在pg-master-01和pg-slave-01节点安装Patroni

5.2 配置Patroni主节点（pg-master-01）

5.3 配置Patroni从节点（pg-slave-01）

5.4 创建Patroni systemd服务

5.5 彻底清理并初始化PostgreSQL数据目录

5.6 从节点启动步骤（在pg-slave-01执行）

5.7 验证复制状态

6. 安装和配置HAProxy

6.1 在pg-proxy-01节点安装HAProxy

6.2 配置HAProxy

7. 集群验证和测试

7.1 检查集群状态

7.2 数据库连接测试

7.3 故障转移测试

8. 监控配置

8.1 安装PostgreSQL Exporter

9. 日常维护

9.1 常用管理命令

9.2 备份策略

9.3 性能优化建议

10. 故障排除

10.1 常见问题

10.2 日志查看

11. 总结

11.1 架构组件

11.2 实现功能

11.3 部署要点

11.4 运维建议

11.5 注意事项