PostgreSQL high availability 高可用集群--架构&搭建

近期研究关系型数据库postgreSQL 的高可用集群，经调研决定使用下面技术栈：

VIP
keepalived
haproxy
patroni
etcd
postgreSQL 15

整体架构图如下：

主机配置情况如下：

关于patroni+etcd+postgreSQL 15的安装，可以借鉴网络上的安装步骤，相差不多。这里推荐GitHub上一个使用ansible封装的安装脚本，选择type A就行。

下面介绍keepalive的安装和配置：

apt install keepalived.

进入/etc/keepalived/创建keepalived.conf

master的配置如下：

global_defs {
    script_user root
    router_id LVS_DEVEL
    vrrp_skip_check_adv_addr
    vrrp_garp_interval 0
    vrrp_gna_interval 0
}

vrrp_script chk_http_port {
    script "killall -0 haproxy"   # 检测当前机器的服务是否故障，如果故障则关闭 keepalived
    interval 2
    weight -5
    fall 2
    rise 1
}

vrrp_instance VI_1 {
    state MASTER    # 主备配置不一致
    interface ens160
    virtual_router_id 51
    priority 100
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass postgres   # 主备该配置必须一样
    }
    virtual_ipaddress {
        129.184.13.161/24 dev ens160
    }
    track_script {
        chk_http_port   # 在 vrrp_script 定义的名字
    }
    notify_master ""  # 当这台机器成为 Master 时发送通知
    notify_backup ""
    notify_fault  ""
}
virtual_server 129.184.13.161 5000 {
    lb_algo rr
    #lb_kind DR
    lb_kind NAT
    protocol TCP
    persistence_timeout 50
    delay_loop 10
    real_server 129.184.13.158 5000 {
        weight 100
        TCP_CHECK {
          connect_timeout 5
          connect_port 5000
        }
    }
    real_server 129.184.13.159 5000 {
        weight 90
        TCP_CHECK {
          connect_timeout 5
          connect_port 5000
        }
    }
    real_server 129.184.13.160 5000 {
        weight 80
        TCP_CHECK {
          connect_timeout 5
          connect_port 5000
        }
    }
}
virtual_server 129.184.13.161 5001 {
    #lvs_sched rr
    #lvs_method DR
    lb_algo rr
    lb_kind NAT
    protocol TCP
    persistence_timeout 50
    delay_loop 10
    real_server 129.184.13.158 5001 {
        weight 80
        TCP_CHECK {
          connect_timeout 5
          connect_port 5001
        }
    }
    real_server 129.184.13.159 5001 {
        weight 90
        TCP_CHECK {
          connect_timeout 5
          connect_port 5001
        }
    }
    real_server 129.184.13.160 5001 {
        weight 100
        TCP_CHECK {
          connect_timeout 5
          connect_port 5001
        }
    }
}

View Code

replicate节点配置：

global_defs {
    script_user root
    router_id LVS_DEVEL
    vrrp_skip_check_adv_addr
    vrrp_garp_interval 0
    vrrp_gna_interval 0
}

vrrp_script chk_http_port {
    script "/usr/bin/killall -0 haproxy"   # 检测当前机器的服务是否故障，如果故障则关闭 keepalived
    interval 2
    weight -5
    fall 2
    rise 1
}

vrrp_instance VI_1 {
    state BACKUP    # 主备配置不一致
    interface ens160
    virtual_router_id 51
    priority 95   #low then master
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass postgres   # 主备该配置必须一样
    }
    virtual_ipaddress {
        129.184.13.161/24 dev ens160
    }
    track_script {
        chk_http_port   # 在 vrrp_script 定义的名字
    }
    notify_master ""  # 当这台机器成为 Master 时发送通知
    notify_backup ""
    notify_fault  ""
}
virtual_server 129.184.13.161 5000 {
    #lvs_sched rr
    #lvs_method DR
    #lb_algo rr
    #lb_kind NAT
    protocol TCP
    persistence_timeout 50
    delay_loop 10
    real_server 129.184.13.158 5000 {
        weight 100
        TCP_CHECK {
          connect_timeout 5
          connect_port 5000
        }
    }
    real_server 129.184.13.159 5000 {
        weight 90
        TCP_CHECK {
          connect_timeout 5
          connect_port 5000
        }
    }
    real_server 129.184.13.160 5000 {
        weight 80
        TCP_CHECK {
          connect_timeout 5
          connect_port 5000
        }
    }
}
virtual_server 129.184.13.161 5001 {
    #lvs_sched rr
    #lvs_method DR
    #lb_algo rr
    #lb_kind NAT
    protocol TCP
    persistence_timeout 50
    delay_loop 10
    real_server 129.184.13.158 5001 {
        weight 80
        TCP_CHECK {
          connect_timeout 5
          connect_port 5001
        }
    }
    real_server 129.184.13.159 5001 {
        weight 90
        TCP_CHECK {
          connect_timeout 5
          connect_port 5001
        }
    }
    real_server 129.184.13.160 5001 {
        weight 100
        TCP_CHECK {
          connect_timeout 5
          connect_port 5001
        }
    }
}

View Code

接下来可以使用虚拟IP访问，

5000端口用于写/读

5001主要用于读取数据（读写分离）

----------------高可用性验证----------------------

1.查看node状况