如何在Ubuntu上实现PostgreSQL的高可用性

在 Ubuntu 上实现 PostgreSQL 高可用

一、方案选型与对比

• 原生流复制 + 手动切换：基于 WAL 的异步/同步复制，部署简单，适合中小规模与可控的维护窗口。
• Patroni + etcd/Consul + VIP：自动化故障检测与切换、REST API、支持同步复制与回滚（pg_rewind），适合生产级高可用。
• pg_repmgr：围绕流复制的命令行与守护进程，支持自动故障转移（repmgrd），运维友好。
• Keepalived VIP：在主备之上提供虚拟 IP，对应用透明；可单独使用，也可与 Patroni/repmgr 组合，实现更平滑的对外地址漂移。

二、方案一 原生流复制与手动切换（Ubuntu 快速落地）

• 环境约定：主库 192.168.22.131，备库 192.168.22.215，PostgreSQL 14，复制用户 replicator，数据目录 /var/lib/postgresql/14/main。
• 主库配置
  • 安装：sudo apt install -y postgresql-14 postgresql-client-14
  • 创建复制用户：
    • sudo -u postgres psql -c “CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD ‘StrongPass123’;”
  • 修改 /etc/postgresql/14/main/postgresql.conf：
    • listen_addresses = ‘*’
    • wal_level = replica
    • max_wal_senders = 10
    • max_replication_slots = 5
    • wal_keep_size = 128MB（或保留 wal_keep_segments）
    • hot_standby = on（备库用）
  • 修改 /etc/postgresql/14/main/pg_hba.conf（允许备库复制）：
    • host replication replicator 192.168.22.215/32 md5
  • 重启：sudo systemctl restart postgresql@14-main
• 备库配置
  • 清空数据目录并克隆：
    • sudo systemctl stop postgresql@14-main
    • sudo -u postgres pg_basebackup -h 192.168.22.131 -U replicator -D /var/lib/postgresql/14/main -P --wal-method=stream
  • 创建恢复配置（Ubuntu 14/15 为 recovery.conf，16+ 使用 standby.signal + postgresql.auto.conf）：
    • echo “standby_mode = ‘on’” | sudo tee /var/lib/postgresql/14/main/recovery.conf
    • echo “primary_conninfo = ‘host=192.168.22.131 port=5432 user=replicator password=StrongPass123’” | sudo tee -a /var/lib/postgresql/14/main/recovery.conf
    • echo “primary_slot_name = ‘standby1’” | sudo tee -a /var/lib/postgresql/14/main/recovery.conf
  • 启动：sudo systemctl start postgresql@14-main
• 验证与切换
  • 在主库：sudo -u postgres psql -c “SELECT * FROM pg_stat_replication;”
  • 计划内切换（在主库执行，备库已配置 primary_slot_name）：
    • 提升备库：sudo -u postgres psql -c “SELECT pg_promote();”
    • 原主恢复为备库：在旧主创建复制槽 standby1，清空数据目录后再次 pg_basebackup 跟随新主。

三、方案二 Patroni + etcd + VIP 实现自动故障转移

• 架构要点：Patroni 管理 PostgreSQL 生命周期与切换；etcd 作为 DCS（Raft 一致性）；对外通过 VIP（如 vip-manager 或 Keepalived）提供稳定地址。
• 部署步骤
  • 安装组件（示例）：sudo apt install -y postgresql-14 etcd patroni
  • 配置 Patroni（/etc/patroni.yml 关键项）：
    • scope: postgres
    • name: node1/node2
    • restapi: listen 0.0.0.0:8008
    • etcd: host: 192.168.22.131:2379
    • bootstrap.dcs:
      • ttl: 30, loop_wait: 10, retry_timeout: 10
      • postgresql.parameters:
        • wal_level: replica
        • max_wal_senders: 10
        • max_replication_slots: 5
        • wal_keep_size: 128MB
        • hot_standby: on
        • synchronous_commit: remote_write（或 on/remote_apply 视一致性需求）
        • synchronous_standby_names: ‘ANY 1 (node2)’（示例：至少 1 个同步备）
      • use_pg_rewind: true, use_slots: true
    • postgresql.listen: 0.0.0.0:5432
    • authentication.replication.username: replicator（配密码）
  • 启动：patroni /etc/patroni.yml（建议以 systemd 托管）
  • VIP 方案（二选一）
    • vip-manager：监听 etcd 的 leader 变化，在主机上挂载 VIP（如 192.168.22.140），对外统一访问。
    • Keepalived：配置 VRRP 实例与健康检查脚本（检测 5432 端口或执行 SELECT 1），主备优先级切换，VIP 漂移。
• 验证
  • 查看集群：patronictl list
  • 故障演练：停止当前主库 Patroni/PostgreSQL，观察新主提升与 VIP 漂移。

四、方案三 pg_repmgr 实现高可用

• 适用场景：希望基于流复制获得自动故障转移与运维命令集的团队。
• 快速步骤
  • 安装：sudo apt install -y postgresql-14 repmgr
  • 主库：
    • 创建 repmgr 用户与库：CREATE USER repmgr SUPERUSER ENCRYPTED PASSWORD ‘xxx’; CREATE DATABASE repmgr OWNER repmgr;
    • postgresql.conf：wal_level=replica，max_wal_senders≥10，max_replication_slots≥5
    • pg_hba.conf：host replication repmgr 192.168.22.0/24 md5；host repmgr repmgr 192.168.22.0/24 md5
  • 备库：
    • 编辑 /etc/repmgr/14/repmgr.conf（node_id、node_name、conninfo、data_directory）
    • 克隆：repmgr -f /etc/repmgr/14/repmgr.conf standby clone -h 主库IP -U repmgr
    • 启动：systemctl start postgresql@14-main
    • 跟随：repmgr -f /etc/repmgr/14/repmgr.conf standby follow
  • 自动故障转移
    • 启动守护进程：repmgrd -f /etc/repmgr/14/repmgr.conf --daemonize
    • 故障演练：停止主库，观察 repmgrd 提升新主，并注册到集群
  • 常用命令：repmgr cluster show、repmgr standby switchover。

五、关键实践与运维要点

• 复制与一致性
  • 数据安全优先：建议启用同步复制（如 synchronous_commit=remote_write，配合 synchronous_standby_names），在性能与零数据丢失间平衡。
  • 防止 WAL 被回收：使用复制槽（max_replication_slots）或合理设置 wal_keep_size，避免备库追不上导致断流。
• 连接与负载
  • 读写分离：备库参数 hot_standby=on 可承担读流量；应用侧使用中间件（如 Pgpool-II）或驱动级读写分离策略。
• 监控与验证
  • 主库：SELECT * FROM pg_stat_replication;（检查 replication lag、state）
  • 备库：SELECT pg_is_in_recovery();（应返回 true）
  • 切换演练：定期进行计划内切换与故障演练，验证 VIP 漂移、应用重连、数据一致性。
• 升级与回滚
  • 使用 Patroni 的pg_rewind加速原主回切；变更前备份关键配置与 DCS 状态。