PostgreSQL patroni高可用

PostgreSQL patroni 高可用 1：ectd 安裝和配置
PostgreSQL patroni 高可用 2：patroni安裝和配置
PostgreSQL patroni 高可用 3：patroni 運維
PostgreSQL patroni 高可用 4：HAProxy和Keepalived實現讀寫分離

PostgreSQL patroni 高可用 4：HAProxy和Keepalived實現讀寫分離

PostgreSQL ptroni的高可用架構圖如下所示，本文完成如下架構圖中紅色標記內的HAProxy安裝和配置，實際上是在每個節點都安裝了HAProxy，然後用keepalived的方式，實現HAProxy自身的高可用。

需要特別説明的是：

1，HAProxy只是一個請求轉發功能的中間件，可以單獨安裝在一台獨立的機器上，也可以跟PostgreSQL實例安裝在一台機器上。

2，HAProxy並不是只能適配於Patroni，可以是任意類型的集羣，比如基礎的流複製，repmgr，pg_auto_failover 集羣，或者實現MySQL集羣的代理等等。

3，HAproxy自身也是一個單點的應用，所以其自身也需要高可用，因此本文會基於keepalived對HAproxy做高可用。

4，HAProxy在patroni高可用環境中，客户端的訪問路徑為：Application---》keepalived虛擬IP---》HAProxy---》patroni實例---》etcd存儲---》PostgreSQL實例，可見這個鏈路比較長，每個組件都會帶來一定的性能損耗。

圖片來源於：https://docs.percona.com/postgresql/12/solutions/high-availability.html#architecture-layout

1，環境

Ubuntu08：192.168.152.115

Ubuntu09：192.168.152.116

Ubuntu10：192.168.152.117

patroni集羣環境：

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

2，AHProxy安裝

版本選擇

https://www.haproxy.org/，版本信息如下，這裏找一個長期支持版本（LTS）3.2

下載

wget https://www.haproxy.org/download/3.2/src/haproxy-3.2.5.tar.gz
tar -xzvf haproxy-3.2.5.tar.gz
cd haproxy-3.2.5/

編譯安裝

#編譯選項，make編譯會報錯，提示出編譯選項
root@ubuntu08:/usr/local/pg_install_package/haproxy-3.2.5# make

Building HAProxy without specifying a TARGET is not supported.
Usage:

    make help                       # To print a full explanation.
    make TARGET=xxx USE_<feature>=1 # To build HAProxy.

The most commonly used targets are:

    linux-glibc    - Modern Linux with glibc
    linux-musl     - Modern Linux with musl
    freebsd        - FreeBSD
    openbsd        - OpenBSD
    netbsd         - NetBSD
    osx            - macOS
    solaris        - Solaris

Choose the target which matches your OS the most in order to
gain the maximum performance out of it.

Common features you might want to include in your build are:

    USE_OPENSSL=1 - Support for TLS encrypted connections
    USE_ZLIB=1    - Support for HTTP response compression
    USE_PCRE=1    - Support for PCRE regular expressions
    USE_LUA=1     - Support for dynamic processing using Lua

Use 'make help' to print a full explanation of supported targets
and features, and 'make ... opts' to show the variables in use
for a given set of build options, in a reusable form.

make: *** [Makefile:933: all] Error 1

#編譯
make -j $(nproc) TARGET=linux-glibc USE_OPENSSL=1 USE_QUIC=1 USE_QUIC_OPENSSL_COMPAT=1

#安裝，安裝位置為：/usr/local/sbin
make install

3，HAProxy配置

haproxy三個節點完全一致，不需要修改，/etc/haproxy/haproxy.conf

global
    log         127.0.0.1 local2       
    pidfile     /var/run/haproxy.pid   
    maxconn     1000                   
    daemon                            

defaults
    mode                    tcp
    retries                 3
    timeout client          10m
    timeout connect         10s
    timeout server          10m
    timeout check           10s
    
listen  stats
        stats uri /
        mode http
        bind *:8080
        stats enable
        stats auth admin:admin
        stats refresh 10s
    
listen  pg_rw
        bind *:6432
        option httpchk
        http-check expect status 200
        default-server inter 3s rise 3 fall 2 on-marked-down shutdown-sessions
        server ubuntu05 192.168.152.115:9000 check port 8008
        server ubuntu06 192.168.152.116:9000 check port 8008
        server ubuntu07 192.168.152.117:9000 check port 8008
        
listen  pg_ro
        bind *:6433
        option httpchk GET /replica	
        http-check expect status 200
        default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
        balance roundrobin
        server ubuntu05 192.168.152.115:9000 check port 8008
        server ubuntu06 192.168.152.116:9000 check port 8008
        server ubuntu07 192.168.152.117:9000 check port 8008

關於HAProxy的另一種驗證方式，請參考這裏：https://blog.itpub.net/70041375/viewspace-3032961/

systemctl啓動文件haproxy.service

/etc/systemd/system/haproxy.service

# /etc/systemd/system/haproxy.service

[Unit]
Description=HAProxy Load Balancer
After=network.target

[Service]
Environment="CONFIG=/etc/haproxy/haproxy.conf" "PIDFILE=/var/run/haproxy.pid"
ExecStartPre=/usr/local/sbin/haproxy -f $CONFIG -c -q
ExecStart=/usr/local/sbin/haproxy -Ws -f $CONFIG -p $PIDFILE -d
ExecReload=/usr/local/sbin/haproxy -f $CONFIG -c -q
ExecReload=/bin/kill -USR2 $MAINPID
KillMode=mixed
Restart=always
SuccessExitStatus=143
Type=notify

# The following lines leverage SystemD's sandboxing options to provide
# defense in depth protection at the expense of restricting some flexibility
# in your setup (e.g. placement of your configuration files) or possibly
# reduced performance. See systemd.service(5) and systemd.exec(5) for further
# information.

# NoNewPrivileges=true
# ProtectHome=true
# If you want to use 'ProtectSystem=strict' you should whitelist the PIDFILE,
# any state files and any other files written using 'ReadWritePaths' or
# 'RuntimeDirectory'.
# ProtectSystem=true
# ProtectKernelTunables=true
# ProtectKernelModules=true
# ProtectControlGroups=true
# If your SystemD version supports them, you can add: @reboot, @swap, @sync
# SystemCallFilter=~@cpu-emulation @keyring @module @obsolete @raw-io

[Install]
WantedBy=multi-user.target

啓動服務

systemctl daemon-reload
systemctl enable haproxy
systemctl start haproxy
systemctl status haproxy

如果有異常，可以直接啓動調試驗證配置文件是否正常

/usr/local/sbin/haproxy -f /etc/haproxy/haproxy.conf -c -V

3，HAProxy代理使用

先從Ubuntu08：192.168.152.115開始安裝，目前集羣角色如下

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

3.1，PostgreSQL集羣的patroni狀態檢查

root@ubuntu08:/usr/local/pg_install_package#
root@ubuntu08:/usr/local/pg_install_package# curl -s "http://192.168.152.117:8008/leader" -v 2>&1|grep '200 OK'		#主節點檢查正常
< HTTP/1.0 200 OK
root@ubuntu08:/usr/local/pg_install_package# curl -s "http://192.168.152.117:8008/replica" -v 2>&1|grep '200 OK'
root@ubuntu08:/usr/local/pg_install_package#
root@ubuntu08:/usr/local/pg_install_package# curl -s "http://192.168.152.115:8008/replica" -v 2>&1|grep '200 OK'	#從節點1檢查正常
< HTTP/1.0 200 OK
root@ubuntu08:/usr/local/pg_install_package#
root@ubuntu08:/usr/local/pg_install_package# curl -s "http://192.168.152.116:8008/replica" -v 2>&1|grep '200 OK'	#從節點2檢查正常
< HTTP/1.0 200 OK
root@ubuntu08:/usr/local/pg_install_package#

3.2，啓動HAproxy

root@ubuntu08:/usr/local/pg_install_package# systemctl status haproxy
● haproxy.service - HAProxy Load Balancer
     Loaded: loaded (/etc/systemd/system/haproxy.service; disabled; vendor preset: enabled)
     Active: active (running) since Sun 2025-09-28 13:47:47 CST; 10s ago
    Process: 858613 ExecStartPre=/usr/local/sbin/haproxy -f $CONFIG -c -q (code=exited, status=0/SUCCESS)
   Main PID: 858635 (haproxy)
     Status: "Ready."
      Tasks: 3 (limit: 4550)
     Memory: 8.7M
     CGroup: /system.slice/haproxy.service
             ├─858635 /usr/local/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid -d
             └─858639 /usr/local/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid -d

Sep 28 13:47:47 ubuntu08 haproxy[858639]: Using epoll() as the polling mechanism.
Sep 28 13:47:47 ubuntu08 haproxy[858635]: 00000000:MASTER.accept(0003)=0007 from [unix:1] ALPN=<none>
Sep 28 13:47:47 ubuntu08 haproxy[858635]: [NOTICE]   (858635) : Loading success.
Sep 28 13:47:47 ubuntu08 haproxy[858635]: 00000000:MASTER.srvcls[0007:ffff]
Sep 28 13:47:47 ubuntu08 haproxy[858635]: 00000001:MASTER.clicls[0007:ffff]
Sep 28 13:47:47 ubuntu08 haproxy[858635]: 00000001:MASTER.closed[0007:ffff]
Sep 28 13:47:47 ubuntu08 systemd[1]: Started HAProxy Load Balancer.
Sep 28 13:47:47 ubuntu08 haproxy[858639]: [WARNING]  (858639) : Server pg_rw/ubuntu08 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 7ms. 2 active and 0>
Sep 28 13:47:47 ubuntu08 haproxy[858639]: [WARNING]  (858639) : Server pg_rw/ubuntu09 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 1ms. 1 active and 0>
Sep 28 13:47:49 ubuntu08 haproxy[858639]: [WARNING]  (858639) : Server pg_ro/ubuntu10 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 3ms. 2 active and 0>

root@ubuntu08:/usr/local/pg_install_package#

3.3，HAproxy管理後台

HAproxy管理後台：http://192.168.152.115:8080/

3.4，讀寫分離測試

patronictl -c /usr/local/pgsql16/patroni/patroni.yml list查看集羣狀態

root@ubuntu10:/usr/local/pg_install_package# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu10:/usr/local/pg_install_package#

測試讀寫分析

#6432 讀寫端口號，一直重定向到主節點 192.168.152.117
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6432 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.117  | f
(1 row)
#6432 讀寫端口號，一直重定向到主節點 192.168.152.117
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6432 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.117  | f
(1 row)
#6433 只讀端口號，一直重定向到主節點 192.168.152.115或者116
root@ubuntu10:/usr/local/pg_install_package#
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.115  | t
(1 row)

root@ubuntu10:/usr/local/pg_install_package#
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.116  | t
(1 row)

root@ubuntu10:/usr/local/pg_install_package#
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.115  | t
(1 row)

root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.116  | t
(1 row)

root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.115  | t
(1 row)

4，keepalived安裝

4.1 下載和安裝

首先從Ubuntu08這台主機開始安裝

wget https://keepalived.org/software/keepalived-2.3.4.tar.gz
#config
./configure --prefix=/usr/local/
#編譯和安裝
make && make install

#安裝psmisc
apt install -y psmisc

keepalived服務文件：/etc/systemd/system/keepalived.server

[Unit]
Description=Keepalive Daemon (LVS and VRRP)
After=network-online.target
Wants=network-online.target
[Service]
Type=forking
PIDFile=/run/keepalived.pid
KillMode=process
EnvironmentFile=/usr/local/keepalived/etc/sysconfig/keepalived
ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target

Ubuntu 08 keepalived配置文件：/usr/local/keepalived/etc/keepalived/keepalived.conf

global_defs {
    router_id ubunt08
    script_user root
    enable_script_security
    notification_syslog facility local1
}

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 5
    fall 30
    rise 5
    timeout 2
}

vrrp_instance VI_1 {
    state MASTER	#搶佔模式
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.152.119
    }
    track_script {
        chk_haproxy
    }
}

4.2 keepalived日誌設置

keepalived的環境變量配置默認在 yum/apt 安裝的在 /etc/sysconfig/keepalived ，源碼編譯安裝的在/usr/local/keepalived/etc/sysconfig/keepalived

1，修改keepalived.conf配置文件
global_defs {
    # 設置 syslog facility
    notification_syslog facility local1
}
這裏的 local1 可以換成 local0 ~ local7 任意一個，但要和 rsyslog 裏對應。


2，編輯 /etc/rsyslog.d/keepalived.conf，增加一條規則，把 local1.* 的日誌寫到獨立文件裏：
local1.*    /var/log/keepalived.log


3，保存後，重啓 rsyslog：
sudo systemctl restart rsyslog

啓動keepalived

systemctl daemon-reload
systemctl enable keepalived
systemctl start keepalived
systemctl status keepalived

4.3 keepalived綁定虛擬IP測試

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:af:4a:a4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.115/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.152.119/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feaf:4aa4/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# systemctl status keepalived
● keepalived.service - Keepalive Daemon (LVS and VRRP)
     Loaded: loaded (/etc/systemd/system/keepalived.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2025-09-28 14:46:40 CST; 2min 9s ago
    Process: 868947 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
   Main PID: 868960 (keepalived)
      Tasks: 2 (limit: 4550)
     Memory: 1.8M
     CGroup: /system.slice/keepalived.service
             ├─868960 /usr/local/keepalived/sbin/keepalived -D -S 0
             └─868961 /usr/local/keepalived/sbin/keepalived -D -S 0

Sep 28 14:46:43 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:43 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:43 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:43 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

ubunt09 keepalived配置文件（修改router_id，state，priority）

global_defs {
    router_id ubunt09
    script_user root
    enable_script_security
        notification_syslog facility local1
}

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 5
    fall 3
    rise 5
    timeout 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.152.119
    }
    track_script {
        chk_haproxy
    }
}

ubunt10 keepalived配置文件（修改router_id，state，priority）

global_defs {
    router_id ubunt10
    script_user root
    enable_script_security
        notification_syslog facility local1
}

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 5
    fall 3
    rise 5
    timeout 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 80
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.152.119
    }
    track_script {
        chk_haproxy
    }
}

4.4 keepalived虛擬IP飄移測試

1，Ubuntu08主節點關閉keepalived

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# systemctl stop keepalived
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:af:4a:a4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.115/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feaf:4aa4/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

2，Ubuntu09節點接替keepalived

root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5#
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# systemctl status keepalived
● keepalived.service - Keepalive Daemon (LVS and VRRP)
     Loaded: loaded (/etc/systemd/system/keepalived.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2025-09-28 16:16:21 CST; 33s ago
    Process: 847309 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
   Main PID: 847324 (keepalived)
      Tasks: 2 (limit: 4550)
     Memory: 2.5M
     CGroup: /system.slice/keepalived.service
             ├─847324 /usr/local/keepalived/sbin/keepalived -D -S 0
             └─847325 /usr/local/keepalived/sbin/keepalived -D -S 0

Sep 28 16:16:51 ubuntu09 Keepalived_vrrp[847325]: (VI_1) Backup received priority 0 advertisement
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: (VI_1) Receive advertisement timeout
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: (VI_1) Entering MASTER STATE
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: (VI_1) setting VIPs.
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5#
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:4e:c2:b0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.116/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.152.119/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe4e:c2b0/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5#

3，Ubuntu08主節點啓動keepalived，搶回虛擬ip

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# systemctl start keepalived
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:af:4a:a4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.115/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.152.119/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feaf:4aa4/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# systemctl status keepalived
● keepalived.service - Keepalive Daemon (LVS and VRRP)
     Loaded: loaded (/etc/systemd/system/keepalived.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2025-09-28 16:19:07 CST; 18s ago
    Process: 879342 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
   Main PID: 879356 (keepalived)
      Tasks: 2 (limit: 4550)
     Memory: 1.6M
     CGroup: /system.slice/keepalived.service
             ├─879356 /usr/local/keepalived/sbin/keepalived -D -S 0
             └─879358 /usr/local/keepalived/sbin/keepalived -D -S 0

Sep 28 16:19:11 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:11 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:11 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:11 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

4，Ubuntu09上的虛擬IP被搶回（Ubuntu08主節點啓動keepalived，搶回虛擬ip）

oot@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:4e:c2:b0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.116/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.152.119/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe4e:c2b0/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:4e:c2:b0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.116/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe4e:c2b0/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5#

4.5 通過虛擬IP連接至PostgreSQL集羣測試

#通過虛擬IP，RW端口號，總是轉發到主節點
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# psql "host=192.168.152.119 port=6432 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.117  | f
(1 row)

#通過虛擬IP，RW端口號，總是轉發到主節點
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# psql "host=192.168.152.119 port=6432 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.117  | f
(1 row)

#通過虛擬IP，RW端口號，總是轉發到主節點
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# psql "host=192.168.152.119 port=6432 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.117  | f
(1 row)

#通過虛擬IP，RO端口號，輪訓轉發到從節點
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# psql "host=192.168.152.119 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.116  | t
(1 row)

root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# psql "host=192.168.152.119 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.115  | t
(1 row)

root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# psql "host=192.168.152.119 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.116  | t
(1 row)

root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# psql "host=192.168.152.119 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.115  | t
(1 row)

PostgreSQL patroni 高可用 4：HAProxy和Keepalived實現讀寫分離

需要特別説明的是：

1，HAProxy只是一個請求轉發功能的中間件，可以單獨安裝在一台獨立的機器上，也可以跟PostgreSQL實例安裝在一台機器上。

2，HAProxy並不是只能適配於Patroni，可以是任意類型的集羣，比如基礎的流複製，repmgr，pg_auto_failover 集羣，或者實現MySQL集羣的代理等等。

3，HAproxy自身也是一個單點的應用，所以其自身也需要高可用，因此本文會基於keepalived對HAproxy做高可用。

圖片來源於：https://docs.percona.com/postgresql/12/solutions/high-availability.html#architecture-layout

1，環境

Ubuntu08：192.168.152.115

Ubuntu09：192.168.152.116

Ubuntu10：192.168.152.117

patroni集羣環境：

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

2，AHProxy安裝

版本選擇

https://www.haproxy.org/，版本信息如下，這裏找一個長期支持版本（LTS）3.2

下載

wget https://www.haproxy.org/download/3.2/src/haproxy-3.2.5.tar.gz
tar -xzvf haproxy-3.2.5.tar.gz
cd haproxy-3.2.5/

編譯安裝

#編譯選項，make編譯會報錯，提示出編譯選項
root@ubuntu08:/usr/local/pg_install_package/haproxy-3.2.5# make

Building HAProxy without specifying a TARGET is not supported.
Usage:

    make help                       # To print a full explanation.
    make TARGET=xxx USE_<feature>=1 # To build HAProxy.

The most commonly used targets are:

    linux-glibc    - Modern Linux with glibc
    linux-musl     - Modern Linux with musl
    freebsd        - FreeBSD
    openbsd        - OpenBSD
    netbsd         - NetBSD
    osx            - macOS
    solaris        - Solaris

Choose the target which matches your OS the most in order to
gain the maximum performance out of it.

Common features you might want to include in your build are:

    USE_OPENSSL=1 - Support for TLS encrypted connections
    USE_ZLIB=1    - Support for HTTP response compression
    USE_PCRE=1    - Support for PCRE regular expressions
    USE_LUA=1     - Support for dynamic processing using Lua

Use 'make help' to print a full explanation of supported targets
and features, and 'make ... opts' to show the variables in use
for a given set of build options, in a reusable form.

make: *** [Makefile:933: all] Error 1

#編譯
make -j $(nproc) TARGET=linux-glibc USE_OPENSSL=1 USE_QUIC=1 USE_QUIC_OPENSSL_COMPAT=1

#安裝，安裝位置為：/usr/local/sbin
make install

3，HAProxy配置

haproxy三個節點完全一致，不需要修改，/etc/haproxy/haproxy.conf

global
    log         127.0.0.1 local2       
    pidfile     /var/run/haproxy.pid   
    maxconn     1000                   
    daemon                            

defaults
    mode                    tcp
    retries                 3
    timeout client          10m
    timeout connect         10s
    timeout server          10m
    timeout check           10s
    
listen  stats
        stats uri /
        mode http
        bind *:8080
        stats enable
        stats auth admin:admin
        stats refresh 10s
    
listen  pg_rw
        bind *:6432
        option httpchk
        http-check expect status 200
        default-server inter 3s rise 3 fall 2 on-marked-down shutdown-sessions
        server ubuntu05 192.168.152.115:9000 check port 8008
        server ubuntu06 192.168.152.116:9000 check port 8008
        server ubuntu07 192.168.152.117:9000 check port 8008
        
listen  pg_ro
        bind *:6433
        option httpchk GET /replica	
        http-check expect status 200
        default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
        balance roundrobin
        server ubuntu05 192.168.152.115:9000 check port 8008
        server ubuntu06 192.168.152.116:9000 check port 8008
        server ubuntu07 192.168.152.117:9000 check port 8008

關於HAProxy的另一種驗證方式，請參考這裏：https://blog.itpub.net/70041375/viewspace-3032961/

systemctl啓動文件haproxy.service

/etc/systemd/system/haproxy.service

# /etc/systemd/system/haproxy.service

[Unit]
Description=HAProxy Load Balancer
After=network.target

[Service]
Environment="CONFIG=/etc/haproxy/haproxy.conf" "PIDFILE=/var/run/haproxy.pid"
ExecStartPre=/usr/local/sbin/haproxy -f $CONFIG -c -q
ExecStart=/usr/local/sbin/haproxy -Ws -f $CONFIG -p $PIDFILE -d
ExecReload=/usr/local/sbin/haproxy -f $CONFIG -c -q
ExecReload=/bin/kill -USR2 $MAINPID
KillMode=mixed
Restart=always
SuccessExitStatus=143
Type=notify

# The following lines leverage SystemD's sandboxing options to provide
# defense in depth protection at the expense of restricting some flexibility
# in your setup (e.g. placement of your configuration files) or possibly
# reduced performance. See systemd.service(5) and systemd.exec(5) for further
# information.

# NoNewPrivileges=true
# ProtectHome=true
# If you want to use 'ProtectSystem=strict' you should whitelist the PIDFILE,
# any state files and any other files written using 'ReadWritePaths' or
# 'RuntimeDirectory'.
# ProtectSystem=true
# ProtectKernelTunables=true
# ProtectKernelModules=true
# ProtectControlGroups=true
# If your SystemD version supports them, you can add: @reboot, @swap, @sync
# SystemCallFilter=~@cpu-emulation @keyring @module @obsolete @raw-io

[Install]
WantedBy=multi-user.target

啓動服務

systemctl daemon-reload
systemctl enable haproxy
systemctl start haproxy
systemctl status haproxy

如果有異常，可以直接啓動調試驗證配置文件是否正常

/usr/local/sbin/haproxy -f /etc/haproxy/haproxy.conf -c -V

3，HAProxy代理使用

先從Ubuntu08：192.168.152.115開始安裝，目前集羣角色如下

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

3.1，PostgreSQL集羣的patroni狀態檢查

root@ubuntu08:/usr/local/pg_install_package#
root@ubuntu08:/usr/local/pg_install_package# curl -s "http://192.168.152.117:8008/leader" -v 2>&1|grep '200 OK'		#主節點檢查正常
< HTTP/1.0 200 OK
root@ubuntu08:/usr/local/pg_install_package# curl -s "http://192.168.152.117:8008/replica" -v 2>&1|grep '200 OK'
root@ubuntu08:/usr/local/pg_install_package#
root@ubuntu08:/usr/local/pg_install_package# curl -s "http://192.168.152.115:8008/replica" -v 2>&1|grep '200 OK'	#從節點1檢查正常
< HTTP/1.0 200 OK
root@ubuntu08:/usr/local/pg_install_package#
root@ubuntu08:/usr/local/pg_install_package# curl -s "http://192.168.152.116:8008/replica" -v 2>&1|grep '200 OK'	#從節點2檢查正常
< HTTP/1.0 200 OK
root@ubuntu08:/usr/local/pg_install_package#

3.2，啓動HAproxy

root@ubuntu08:/usr/local/pg_install_package# systemctl status haproxy
● haproxy.service - HAProxy Load Balancer
     Loaded: loaded (/etc/systemd/system/haproxy.service; disabled; vendor preset: enabled)
     Active: active (running) since Sun 2025-09-28 13:47:47 CST; 10s ago
    Process: 858613 ExecStartPre=/usr/local/sbin/haproxy -f $CONFIG -c -q (code=exited, status=0/SUCCESS)
   Main PID: 858635 (haproxy)
     Status: "Ready."
      Tasks: 3 (limit: 4550)
     Memory: 8.7M
     CGroup: /system.slice/haproxy.service
             ├─858635 /usr/local/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid -d
             └─858639 /usr/local/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid -d

Sep 28 13:47:47 ubuntu08 haproxy[858639]: Using epoll() as the polling mechanism.
Sep 28 13:47:47 ubuntu08 haproxy[858635]: 00000000:MASTER.accept(0003)=0007 from [unix:1] ALPN=<none>
Sep 28 13:47:47 ubuntu08 haproxy[858635]: [NOTICE]   (858635) : Loading success.
Sep 28 13:47:47 ubuntu08 haproxy[858635]: 00000000:MASTER.srvcls[0007:ffff]
Sep 28 13:47:47 ubuntu08 haproxy[858635]: 00000001:MASTER.clicls[0007:ffff]
Sep 28 13:47:47 ubuntu08 haproxy[858635]: 00000001:MASTER.closed[0007:ffff]
Sep 28 13:47:47 ubuntu08 systemd[1]: Started HAProxy Load Balancer.
Sep 28 13:47:47 ubuntu08 haproxy[858639]: [WARNING]  (858639) : Server pg_rw/ubuntu08 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 7ms. 2 active and 0>
Sep 28 13:47:47 ubuntu08 haproxy[858639]: [WARNING]  (858639) : Server pg_rw/ubuntu09 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 1ms. 1 active and 0>
Sep 28 13:47:49 ubuntu08 haproxy[858639]: [WARNING]  (858639) : Server pg_ro/ubuntu10 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 3ms. 2 active and 0>

root@ubuntu08:/usr/local/pg_install_package#

3.3，HAproxy管理後台

HAproxy管理後台：http://192.168.152.115:8080/

3.4，讀寫分離測試

patronictl -c /usr/local/pgsql16/patroni/patroni.yml list查看集羣狀態

root@ubuntu10:/usr/local/pg_install_package# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu10:/usr/local/pg_install_package#

測試讀寫分析

#6432 讀寫端口號，一直重定向到主節點 192.168.152.117
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6432 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.117  | f
(1 row)
#6432 讀寫端口號，一直重定向到主節點 192.168.152.117
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6432 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.117  | f
(1 row)
#6433 只讀端口號，一直重定向到主節點 192.168.152.115或者116
root@ubuntu10:/usr/local/pg_install_package#
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.115  | t
(1 row)

root@ubuntu10:/usr/local/pg_install_package#
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.116  | t
(1 row)

root@ubuntu10:/usr/local/pg_install_package#
root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.115  | t
(1 row)

root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.116  | t
(1 row)

root@ubuntu10:/usr/local/pg_install_package# psql "host=192.168.152.115 port=6433 user=postgres dbname=postgres password=******" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.152.115  | t
(1 row)

4，keepalived安裝

4.1 下載和安裝

首先從Ubuntu08這台主機開始安裝

wget https://keepalived.org/software/keepalived-2.3.4.tar.gz
#config
./configure --prefix=/usr/local/
#編譯和安裝
make && make install

#安裝psmisc
apt install -y psmisc

keepalived服務文件：/etc/systemd/system/keepalived.server

[Unit]
Description=Keepalive Daemon (LVS and VRRP)
After=network-online.target
Wants=network-online.target
[Service]
Type=forking
PIDFile=/run/keepalived.pid
KillMode=process
EnvironmentFile=/usr/local/keepalived/etc/sysconfig/keepalived
ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target

Ubuntu 08 keepalived配置文件：/usr/local/keepalived/etc/keepalived/keepalived.conf

global_defs {
    router_id ubunt08
    script_user root
    enable_script_security
    notification_syslog facility local1
}

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 5
    fall 30
    rise 5
    timeout 2
}

vrrp_instance VI_1 {
    state MASTER	#搶佔模式
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.152.119
    }
    track_script {
        chk_haproxy
    }
}

4.2 keepalived日誌設置

keepalived的環境變量配置默認在 yum/apt 安裝的在 /etc/sysconfig/keepalived ，源碼編譯安裝的在/usr/local/keepalived/etc/sysconfig/keepalived

1，修改keepalived.conf配置文件
global_defs {
    # 設置 syslog facility
    notification_syslog facility local1
}
這裏的 local1 可以換成 local0 ~ local7 任意一個，但要和 rsyslog 裏對應。


2，編輯 /etc/rsyslog.d/keepalived.conf，增加一條規則，把 local1.* 的日誌寫到獨立文件裏：
local1.*    /var/log/keepalived.log


3，保存後，重啓 rsyslog：
sudo systemctl restart rsyslog

啓動keepalived

systemctl daemon-reload
systemctl enable keepalived
systemctl start keepalived
systemctl status keepalived

4.3 keepalived綁定虛擬IP測試

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:af:4a:a4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.115/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.152.119/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feaf:4aa4/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# systemctl status keepalived
● keepalived.service - Keepalive Daemon (LVS and VRRP)
     Loaded: loaded (/etc/systemd/system/keepalived.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2025-09-28 14:46:40 CST; 2min 9s ago
    Process: 868947 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
   Main PID: 868960 (keepalived)
      Tasks: 2 (limit: 4550)
     Memory: 1.8M
     CGroup: /system.slice/keepalived.service
             ├─868960 /usr/local/keepalived/sbin/keepalived -D -S 0
             └─868961 /usr/local/keepalived/sbin/keepalived -D -S 0

Sep 28 14:46:43 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:43 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:43 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:43 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 14:46:48 ubuntu08 Keepalived_vrrp[868961]: Sending gratuitous ARP on ens33 for 192.168.152.119
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

ubunt09 keepalived配置文件（修改router_id，state，priority）

global_defs {
    router_id ubunt09
    script_user root
    enable_script_security
        notification_syslog facility local1
}

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 5
    fall 3
    rise 5
    timeout 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.152.119
    }
    track_script {
        chk_haproxy
    }
}

ubunt10 keepalived配置文件（修改router_id，state，priority）

global_defs {
    router_id ubunt10
    script_user root
    enable_script_security
        notification_syslog facility local1
}

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 5
    fall 3
    rise 5
    timeout 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 80
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.152.119
    }
    track_script {
        chk_haproxy
    }
}

4.4 keepalived虛擬IP飄移測試

1，Ubuntu08主節點關閉keepalived

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# systemctl stop keepalived
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:af:4a:a4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.115/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feaf:4aa4/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

2，Ubuntu09節點接替keepalived

root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5#
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# systemctl status keepalived
● keepalived.service - Keepalive Daemon (LVS and VRRP)
     Loaded: loaded (/etc/systemd/system/keepalived.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2025-09-28 16:16:21 CST; 33s ago
    Process: 847309 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
   Main PID: 847324 (keepalived)
      Tasks: 2 (limit: 4550)
     Memory: 2.5M
     CGroup: /system.slice/keepalived.service
             ├─847324 /usr/local/keepalived/sbin/keepalived -D -S 0
             └─847325 /usr/local/keepalived/sbin/keepalived -D -S 0

Sep 28 16:16:51 ubuntu09 Keepalived_vrrp[847325]: (VI_1) Backup received priority 0 advertisement
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: (VI_1) Receive advertisement timeout
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: (VI_1) Entering MASTER STATE
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: (VI_1) setting VIPs.
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:16:52 ubuntu09 Keepalived_vrrp[847325]: Sending gratuitous ARP on ens33 for 192.168.152.119
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5#
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:4e:c2:b0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.116/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.152.119/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe4e:c2b0/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5#

3，Ubuntu08主節點啓動keepalived，搶回虛擬ip

root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# systemctl start keepalived
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:af:4a:a4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.115/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.152.119/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feaf:4aa4/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4# systemctl status keepalived
● keepalived.service - Keepalive Daemon (LVS and VRRP)
     Loaded: loaded (/etc/systemd/system/keepalived.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2025-09-28 16:19:07 CST; 18s ago
    Process: 879342 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
   Main PID: 879356 (keepalived)
      Tasks: 2 (limit: 4550)
     Memory: 1.6M
     CGroup: /system.slice/keepalived.service
             ├─879356 /usr/local/keepalived/sbin/keepalived -D -S 0
             └─879358 /usr/local/keepalived/sbin/keepalived -D -S 0

Sep 28 16:19:11 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:11 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:11 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:11 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
Sep 28 16:19:16 ubuntu08 Keepalived_vrrp[879358]: Sending gratuitous ARP on ens33 for 192.168.152.119
root@ubuntu08:/usr/local/pg_install_package/keepalived-2.3.4#

4，Ubuntu09上的虛擬IP被搶回（Ubuntu08主節點啓動keepalived，搶回虛擬ip）

oot@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:4e:c2:b0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.116/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.152.119/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe4e:c2b0/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:4e:c2:b0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.152.116/24 brd 192.168.152.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe4e:c2b0/64 scope link
       valid_lft forever preferred_lft forever
root@ubuntu09:/usr/local/pg_install_package/haproxy-3.2.5#

MSSQL123 動態日志

@wy123

標簽

數據庫 (310)

postgresql (89)

sqlserver (68)

動態

PostgreSQL patroni 高可用 4：HAProxy和Keepalived實現讀寫分離 - 動態詳情

1，環境

2，AHProxy安裝

3，HAProxy配置

3，HAProxy代理使用

3.1，PostgreSQL集羣的patroni狀態檢查

3.2，啓動HAproxy

3.3，HAproxy管理後台

3.4，讀寫分離測試

4，keepalived安裝

4.1 下載和安裝

4.2 keepalived日誌設置

4.3 keepalived綁定虛擬IP測試

4.4 keepalived虛擬IP飄移測試

4.5 通過虛擬IP連接至PostgreSQL集羣測試

1，環境

2，AHProxy安裝

3，HAProxy配置

3，HAProxy代理使用

3.1，PostgreSQL集羣的patroni狀態檢查

3.2，啓動HAproxy

3.3，HAproxy管理後台

3.4，讀寫分離測試

4，keepalived安裝

4.1 下載和安裝

4.2 keepalived日誌設置

4.3 keepalived綁定虛擬IP測試

4.4 keepalived虛擬IP飄移測試

Add a new 評論

MSSQL123 動態日志

@wy123

標簽

數據庫 (310)

postgresql (89)

sqlserver (68)

動態

PostgreSQL patroni 高可用 4：HAProxy和Keepalived實現讀寫分離 - 動態 詳情

1，環境

2，AHProxy安裝

3，HAProxy配置

3，HAProxy代理使用

3.1，PostgreSQL集羣的patroni狀態檢查

3.2，啓動HAproxy

3.3，HAproxy管理後台

3.4，讀寫分離測試

4，keepalived安裝

4.1 下載和安裝

4.2 keepalived日誌設置

4.3 keepalived綁定虛擬IP測試

4.4 keepalived虛擬IP飄移測試

4.5 通過虛擬IP連接至PostgreSQL集羣測試

1，環境

2，AHProxy安裝

3，HAProxy配置

3，HAProxy代理使用

3.1，PostgreSQL集羣的patroni狀態檢查

3.2，啓動HAproxy

3.3，HAproxy管理後台

3.4，讀寫分離測試

4，keepalived安裝

4.1 下載和安裝

4.2 keepalived日誌設置

4.3 keepalived綁定虛擬IP測試

4.4 keepalived虛擬IP飄移測試

Add a new 評論

PostgreSQL patroni 高可用 4：HAProxy和Keepalived實現讀寫分離 - 動態詳情