miketwais

work up

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

基于物理機的postgresql高可用架構

近期研究關系型數據庫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狀況