PostgreSQL patroni高可用

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

PostgreSQL patroni 高可用 1：etcd 安裝

PostgreSQL ptroni的高可用架構圖如下所示，本文完成如下架構圖中紅色標記內的ectd分佈式存儲的安裝和配置。

圖片來源於：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

1.1 ectd下載與安裝

以下在Ubuntu08，Ubuntu09，Ubuntu10分別安裝

下載與解壓安裝
wget https://github.com/etcd-io/etcd/releases/download/v3.6.5/etcd-v3.6.5-linux-amd64.tar.gz
tar xzvf etcd-v3.6.5-linux-amd64.tar.gz -C /usr/local
ln -s /usr/local/etcd-v3.6.5-linux-amd64/ /usr/local/etcd

建立軟連接
ln -s /usr/local/etcd/etcd /usr/bin/etcd
ln -s /usr/local/etcd/etcdctl /usr/bin/etcdctl
etcd --version
/*
輸出：
etcd Version: 3.6.5
Git SHA: a061450
Go Version: go1.24.7
Go OS/Arch: linux/amd64
*/

建立數據文件路徑
mkdir -p /var/lib/etcd 
mkdir /var/lib/etcd/default.etcd

1.2 etcd 配置文件

Ubuntu08：192.168.152.115節點，路徑：/var/lib/etcd/etcd.conf

#etcd名稱，自定義
ETCD_NAME="etcd01"
#存放etcd數據的目錄，自定義
ETCD_DATA_DIR="/var/lib/etcd/default"
#監聽URL，用户客户端和SERVER進行通信
ETCD_LISTEN_CLIENT_URLS="http://192.168.152.115:2379,http://127.0.0.1:2379"
#告知客户端自身的URL，TCP 2379端口用於監聽客户端請求
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.152.115:2379"
#監聽URL，用於和其他節點通信
ETCD_LISTEN_PEER_URLS="http://192.168.152.115:2380"
#告知集羣其他節點，端口2380用於集羣通信
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.152.115:2380"
#定義了集羣內所有成員
ETCD_INITIAL_CLUSTER="etcd01=http://192.168.152.115:2380,etcd02=http://192.168.152.116:2380,etcd03=http://192.168.152.117:2380"
#集羣ID，唯一標識
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
#集羣狀態，new為新創建集羣，existing為已經存在的集羣
ETCD_INITIAL_CLUSTER_STATE="new"

Ubuntu09：192.168.152.116節點，路徑：/var/lib/etcd/etcd.conf

#etcd名稱，自定義
ETCD_NAME="etcd02"
#存放etcd數據的目錄，自定義
ETCD_DATA_DIR="/var/lib/etcd/default"
#監聽URL，用户客户端和SERVER進行通信
ETCD_LISTEN_CLIENT_URLS="http://192.168.152.116:2379,http://127.0.0.1:2379"
#告知客户端自身的URL，TCP 2379端口用於監聽客户端請求
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.152.116:2379"
#監聽URL，用於和其他節點通信
ETCD_LISTEN_PEER_URLS="http://192.168.152.116:2380"
#告知集羣其他節點，端口2380用於集羣通信
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.152.116:2380"
#定義了集羣內所有成員
ETCD_INITIAL_CLUSTER="etcd01=http://192.168.152.115:2380,etcd02=http://192.168.152.116:2380,etcd03=http://192.168.152.117:2380"
#集羣ID，唯一標識
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
#集羣狀態，new為新創建集羣，existing為已經存在的集羣
ETCD_INITIAL_CLUSTER_STATE="new"

Ubuntu10：192.168.152.117節點，路徑：/var/lib/etcd/etcd.conf

#etcd名稱，自定義
ETCD_NAME="etcd03"
#存放etcd數據的目錄，自定義
ETCD_DATA_DIR="/var/lib/etcd/default"
#監聽URL，用户客户端和SERVER進行通信
ETCD_LISTEN_CLIENT_URLS="http://192.168.152.117:2379,http://127.0.0.1:2379"
#告知客户端自身的URL，TCP 2379端口用於監聽客户端請求
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.152.117:2379"
#監聽URL，用於和其他節點通信
ETCD_LISTEN_PEER_URLS="http://192.168.152.117:2380"
#告知集羣其他節點，端口2380用於集羣通信
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.152.117:2380"
#定義了集羣內所有成員
ETCD_INITIAL_CLUSTER="etcd01=http://192.168.152.115:2380,etcd02=http://192.168.152.116:2380,etcd03=http://192.168.152.117:2380"
#集羣ID，唯一標識
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
#集羣狀態，new為新創建集羣，existing為已經存在的集羣
ETCD_INITIAL_CLUSTER_STATE="new"

1.3 etcd systemctl 服務腳本

三台服務器一樣，/etc/systemd/system/ectd.service

[Unit]
Description=Etcd Server
After=network.target
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
EnvironmentFile=/var/lib/etcd/etcd.conf
WorkingDirectory=/var/lib/etcd/
ExecStart=/usr/bin/etcd 
Restart=on-failure
RestartSec=5
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

啓動服務

systemctl daemon-reload
systemctl enable etcd
systemctl start etcd
systemctl status etcd

1.4 etcd 環境變量

--添加環境變量
--ETCDCTL_API是有v2和v3兩個版本，默認是v2版本，執行命令可能會報錯
echo 'export ETCDCTL_API=3' >> /etc/profile
source /etc/profile

其他

--新增etcd節點，systemd啓動服務出現Job for etcd.service failed because a timeout was exceeded錯誤
--解決辦法：出現該問題的原因可能時因為etcd的data-dir中的殘留信息導致的，刪除data-dir中的內容，重新啓動etcd服務
rm -rf /var/lib/etcd/default/

2，檢查etcd集羣管理

2.1 etcd狀態檢查

檢查集羣節點狀態：etcdctl endpoint health --cluster -w table

列出集羣成員：etcdctl member list -w table

檢查集羣節點的健康狀態：etcdctl endpoint health --cluster -w table

檢查集羣節點狀態
root@ubuntu08:/usr/local# etcdctl endpoint status --cluster -w table
{"level":"warn","ts":"2025-09-22T09:34:53.831953+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
+-----------------------------+------------------+---------+-----------------+---------+--------+-----------------------+-------+-----------+------------+-----------+------------+--------------------+--------+--------------------------+-------------------+
|          ENDPOINT           |        ID        | VERSION | STORAGE VERSION | DB SIZE | IN USE | PERCENTAGE NOT IN USE | QUOTA | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS | DOWNGRADE TARGET VERSION | DOWNGRADE ENABLED |
+-----------------------------+------------------+---------+-----------------+---------+--------+-----------------------+-------+-----------+------------+-----------+------------+--------------------+--------+--------------------------+-------------------+
| http://192.168.152.116:2379 | 6eb726f58bd2aa70 |   3.6.5 |           3.6.0 |   20 kB |  16 kB |                   20% |   0 B |     false |      false |         2 |          9 |                  9 |        |                          |             false |
| http://192.168.152.115:2379 | 7158c1b754a1eeba |   3.6.5 |           3.6.0 |   25 kB |  16 kB |                   34% |   0 B |      true |      false |         2 |          9 |                  9 |        |                          |             false |
| http://192.168.152.117:2379 | af96143dba13346f |   3.6.5 |           3.6.0 |   20 kB |  16 kB |                   20% |   0 B |     false |      false |         2 |          9 |                  9 |        |                          |             false |
+-----------------------------+------------------+---------+-----------------+---------+--------+-----------------------+-------+-----------+------------+-----------+------------+--------------------+--------+--------------------------+-------------------+


列出集羣成員
root@ubuntu08:/usr/local# etcdctl member list -w table
{"level":"warn","ts":"2025-09-22T09:36:22.799237+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
+------------------+---------+--------+-----------------------------+-----------------------------+------------+
|        ID        | STATUS  |  NAME  |         PEER ADDRS          |        CLIENT ADDRS         | IS LEARNER |
+------------------+---------+--------+-----------------------------+-----------------------------+------------+
| 6eb726f58bd2aa70 | started | etcd02 | http://192.168.152.116:2380 | http://192.168.152.116:2379 |      false |
| 7158c1b754a1eeba | started | etcd01 | http://192.168.152.115:2380 | http://192.168.152.115:2379 |      false |
| af96143dba13346f | started | etcd03 | http://192.168.152.117:2380 | http://192.168.152.117:2379 |      false |
+------------------+---------+--------+-----------------------------+-----------------------------+------------+

檢查集羣節點的健康狀態
root@ubuntu08:/usr/local#  etcdctl endpoint health --cluster -w table
{"level":"warn","ts":"2025-09-22T09:36:49.159370+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
+-----------------------------+--------+------------+-------+
|          ENDPOINT           | HEALTH |    TOOK    | ERROR |
+-----------------------------+--------+------------+-------+
| http://192.168.152.115:2379 |   true | 4.838419ms |       |
| http://192.168.152.117:2379 |   true | 7.374066ms |       |
| http://192.168.152.116:2379 |   true | 7.334373ms |       |
+-----------------------------+--------+------------+-------+

2.2 etcd常用命令

etcd常用命令
--截至目前位置ectd中沒有用户自定義數據，如下查詢是啓用了patroni之後才能查到，這裏僅做示例

-- 查詢所有的key
root@ubuntu08:/usr/local# etcdctl get "" --prefix --keys-only
{"level":"warn","ts":"2025-09-23T09:36:04.000716+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
/service/pg_cluster_wy_prod/config
/service/pg_cluster_wy_prod/history
/service/pg_cluster_wy_prod/initialize
/service/pg_cluster_wy_prod/leader
/service/pg_cluster_wy_prod/members/ubuntu09
/service/pg_cluster_wy_prod/status


--刪除所有的key
root@ubuntu08:/usr/local# etcdctl del /service/pg_cluster_wy_prod/ --prefix
{"level":"warn","ts":"2025-09-23T09:38:52.248350+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
6

2.3 管理etcd

--查看集羣節點：etcdctl endpoint status --cluster -w table

--轉移領導權： etcdctl move-leader 6eb726f58bd2aa70

--備份數據庫：etcdctl snapshot save etcd_bak.db，默認備份到當前目錄

管理etcd
--查看集羣節點
root@ubuntu08:/usr/local# etcdctl endpoint status --cluster -w table
{"level":"warn","ts":"2025-09-22T09:52:07.064600+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
+-----------------------------+------------------+---------+-----------------+---------+--------+-----------------------+-------+-----------+------------+-----------+------------+--------------------+--------+--------------------------+-------------------+
|          ENDPOINT           |        ID        | VERSION | STORAGE VERSION | DB SIZE | IN USE | PERCENTAGE NOT IN USE | QUOTA | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS | DOWNGRADE TARGET VERSION | DOWNGRADE ENABLED |
+-----------------------------+------------------+---------+-----------------+---------+--------+-----------------------+-------+-----------+------------+-----------+------------+--------------------+--------+--------------------------+-------------------+
| http://192.168.152.116:2379 | 6eb726f58bd2aa70 |   3.6.5 |           3.6.0 |   20 kB |  16 kB |                   20% |   0 B |     false |      false |         2 |         12 |                 12 |        |                          |             false |
| http://192.168.152.115:2379 | 7158c1b754a1eeba |   3.6.5 |           3.6.0 |   25 kB |  16 kB |                   34% |   0 B |      true |      false |         2 |         12 |                 12 |        |                          |             false |
| http://192.168.152.117:2379 | af96143dba13346f |   3.6.5 |           3.6.0 |   20 kB |  16 kB |                   20% |   0 B |     false |      false |         2 |         12 |                 12 |        |                          |             false |
+-----------------------------+------------------+---------+-----------------+---------+--------+-----------------------+-------+-----------+------------+-----------+------------+--------------------+--------+--------------------------+-------------------+


--將領導權轉移到192.168.152.116，需要在leader節點(192.168.152.115)上執行以下命令
root@ubuntu08:/usr/local#  etcdctl move-leader 6eb726f58bd2aa70
{"level":"warn","ts":"2025-09-22T09:52:52.764611+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
Leadership transferred from 7158c1b754a1eeba to 6eb726f58bd2aa70
root@ubuntu08:/usr/local#

--再次查看集羣節點
root@ubuntu08:/usr/local# etcdctl endpoint status --cluster -w table
{"level":"warn","ts":"2025-09-22T09:53:25.957168+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
+-----------------------------+------------------+---------+-----------------+---------+--------+-----------------------+-------+-----------+------------+-----------+------------+--------------------+--------+--------------------------+-------------------+
|          ENDPOINT           |        ID        | VERSION | STORAGE VERSION | DB SIZE | IN USE | PERCENTAGE NOT IN USE | QUOTA | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS | DOWNGRADE TARGET VERSION | DOWNGRADE ENABLED |
+-----------------------------+------------------+---------+-----------------+---------+--------+-----------------------+-------+-----------+------------+-----------+------------+--------------------+--------+--------------------------+-------------------+
| http://192.168.152.116:2379 | 6eb726f58bd2aa70 |   3.6.5 |           3.6.0 |   20 kB |  16 kB |                   20% |   0 B |      true |      false |         3 |         13 |                 13 |        |                          |             false |
| http://192.168.152.115:2379 | 7158c1b754a1eeba |   3.6.5 |           3.6.0 |   25 kB |  16 kB |                   34% |   0 B |     false |      false |         3 |         13 |                 13 |        |                          |             false |
| http://192.168.152.117:2379 | af96143dba13346f |   3.6.5 |           3.6.0 |   20 kB |  16 kB |                   20% |   0 B |     false |      false |         3 |         13 |                 13 |        |                          |             false |
+-----------------------------+------------------+---------+-----------------+---------+--------+-----------------------+-------+-----------+------------+-----------+------------+--------------------+--------+--------------------------+-------------------+


備份數據庫，執行etcdctl snapshot save etcd_bak.db，默認備份到當前目錄
root@ubuntu08:/usr/local#  etcdctl snapshot save etcd_bak.db
{"level":"warn","ts":"2025-09-22T09:54:51.963842+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
{"level":"info","ts":"2025-09-22T09:54:51.971594+0800","caller":"snapshot/v3_snapshot.go:83","msg":"created temporary db file","path":"etcd_bak.db.part"}
{"level":"info","ts":"2025-09-22T09:54:51.973601+0800","logger":"client","caller":"v3@v3.6.5/maintenance.go:236","msg":"opened snapshot stream; downloading"}
{"level":"info","ts":"2025-09-22T09:54:51.975135+0800","caller":"snapshot/v3_snapshot.go:96","msg":"fetching snapshot","endpoint":"127.0.0.1:2379"}
{"level":"info","ts":"2025-09-22T09:54:51.975385+0800","logger":"client","caller":"v3@v3.6.5/maintenance.go:302","msg":"completed snapshot read; closing"}
{"level":"info","ts":"2025-09-22T09:54:51.976335+0800","caller":"snapshot/v3_snapshot.go:111","msg":"fetched snapshot","endpoint":"127.0.0.1:2379","size":"25 kB","took":"4.643252ms","etcd-version":"3.6.0"}
{"level":"info","ts":"2025-09-22T09:54:51.976404+0800","caller":"snapshot/v3_snapshot.go:121","msg":"saved","path":"etcd_bak.db"}
Snapshot saved at etcd_bak.db
Server version 3.6.0

MSSQL123 Blog

@wy123

Tags

數據庫 (310)

postgresql (89)

sqlserver (68)

Stories

PostgreSQL patroni 高可用 1：ectd 安裝和配置 - Stories Detail