为开源数据库集群配置VIP（虚拟IP）的2种方案-平芜编程栈

为开源数据库集群配置VIP（虚拟IP），Patroni和repmgr确实是最主流的两种方案。它们提供了完整的HA解决方案，包括VIP管理。以下是基于这两个工具的详细配置方案：

一、Patroni + VIP 方案

Patroni内置VIP管理功能，通过REST API和回调脚本实现。

方案架构

Primary (Node1) : 192.168.1.10 + VIP 192.168.1.100 Replica (Node2) : 192.168.1.11 Replica (Node3) : 192.168.1.12

配置步骤

1. 安装依赖

# 所有节点安装sudoapt-getinstall-y patroni etcd-client python3-psycopg2

2. Patroni配置文件 (patroni.yml)

scope:pg_clustername:node1# 各节点分别改为node1、node2、node3restapi:listen:0.0.0.0:8008connect_address:192.168.1.10:8008# 各节点使用自己的IPetcd:hosts:["192.168.1.10:2379","192.168.1.11:2379","192.168.1.12:2379"]bootstrap:dcs:ttl:30loop_wait:10retry_timeout:10maximum_lag_on_failover:1048576postgresql:use_pg_rewind:trueparameters:max_connections:100shared_buffers:128MBinitdb:-encoding:UTF8-data-checksumspg_hba:-host replication replicator 192.168.1.0/24 md5-host all all 0.0.0.0/0 md5postgresql:listen:0.0.0.0:5432connect_address:192.168.1.10:5432# 各节点使用自己的IPdata_dir:/var/lib/postgresql/14/mainpgpass:/tmp/pgpassauthentication:replication:username:replicatorpassword:rep_passwordsuperuser:username:postgrespassword:postgres_passwordparameters:logging_collector:true# VIP配置 - 关键部分tags:vip:192.168.1.100/24vip_interface:eth0vip_skip_check:falsenofailover:false

3. VIP管理脚本 (/etc/patroni/vip_manager.sh)

#!/bin/bashVIP=$1INTERFACE=$2ACTION=$3case$ACTIONinon_start)# 当成为主节点时添加VIPipaddradd$VIPdev$INTERFACE;;on_stop)# 当不再是主节点时移除VIPipaddr del$VIPdev$INTERFACE;;on_role_change)# 角色变更时的处理NEW_ROLE=$4if["$NEW_ROLE"="master"];thenipaddradd$VIPdev$INTERFACE2>/dev/null||trueelseipaddr del$VIPdev$INTERFACE2>/dev/null||truefi;;esac

4. 配置systemd服务

# /etc/systemd/system/patroni.service[Unit]Description=Runners to orchestrate a high-availability PostgreSQLAfter=syslog.target network.target[Service]Type=simpleUser=postgresGroup=postgresExecStart=/usr/bin/patroni /etc/patroni/patroni.ymlKillMode=processTimeoutSec=30Restart=no[Install]WantedBy=multi-user.target

5. 启动集群

sudosystemctl daemon-reload# 从第一个节点开始启动sudosystemctl start patroni

二、repmgr + Keepalived 方案

repmgr本身不直接管理VIP，通常与Keepalived结合使用。

方案架构

Primary + Keepalived MASTER : 192.168.1.10 + VIP 192.168.1.100 Replica + Keepalived BACKUP : 192.168.1.11

配置步骤

1. 安装软件包

# 所有节点安装sudoapt-getinstall-y postgresql-14 repmgrd keepalived

2. 配置repmgr (主节点)

# /etc/repmgr.confnode_id=1node_name=node1conninfo='host=192.168.1.10 user=repmgr dbname=repmgr connect_timeout=2'data_directory='/var/lib/postgresql/14/main'log_level=INFOlog_file='/var/log/postgresql/repmgr.log'failover=automaticpromote_command='repmgr standby promote -f /etc/repmgr.conf'follow_command='repmgr standby follow -f /etc/repmgr.conf'# 监控设置monitor_interval_secs=10monitor_history_minutes=90

3. 配置repmgrd服务

# /etc/default/repmgrdREPMGRD_ENABLED=yesREPMGRD_CONF="/etc/repmgr.conf"REPMGRD_OPTS="--daemonize=false"

4. 配置Keepalived (主节点配置)

# /etc/keepalived/keepalived.confglobal_defs{router_id pg_cluster}vrrp_script chk_pg_master{script"/usr/bin/pg_isready -h 192.168.1.10 -p 5432"interval2fall2rise2}vrrp_instance VI_1{state MASTER interface eth0 virtual_router_id51priority100advert_int1authentication{auth_type PASS auth_pass postgres}virtual_ipaddress{192.168.1.100/24 dev eth0}track_script{chk_pg_master}# 当状态变更时执行脚本notify_master"/etc/keepalived/notify_master.sh"notify_backup"/etc/keepalived/notify_backup.sh"notify_fault"/etc/keepalived/notify_fault.sh"}

5. Keepalived状态变更脚本

# /etc/keepalived/notify_master.sh#!/bin/bash# 成为主节点时的处理LOGFILE="/var/log/keepalived/state.log"echo"`date`Becoming MASTER">>$LOGFILE# 可以在这里触发repmgr的强制切换# sudo -u postgres repmgr standby promote -f /etc/repmgr.conf

6. 从节点Keepalived配置

# /etc/keepalived/keepalived.conf (从节点)global_defs{router_id pg_cluster_node2}vrrp_instance VI_1{state BACKUP interface eth0 virtual_router_id51priority90# 优先级低于主节点advert_int1authentication{auth_type PASS auth_pass postgres}virtual_ipaddress{192.168.1.100/24 dev eth0}track_script{chk_pg_master}}

三、两种方案对比与选择建议

特性	Patroni方案	repmgr+Keepalived方案
VIP管理	内置支持，简单配置	需配合Keepalived
架构复杂度	较低，一体化方案	较高，多组件配合
学习曲线	中等，需理解DCS概念	较高，需掌握多个工具
功能完整性	完整HA方案	需要额外组件
社区支持	非常活跃	稳定但相对传统
适用场景	新部署，云环境	传统部署，已有经验