pg14+etcd+Patroni 高可用配置流程

亲爱的焦糖玛奇朵發表於2024-08-30

pg14+etcd+Patroni 高可用配置流程

pg14+etcd+Patroni 高可用配置流程
- 基礎配置
  - IP 規劃：
  - 修改超級使用者密碼：
  - 在主庫上建立流複製使用者
  - 修改 pg_hba 檔案
  - 備庫重新建立資料目錄：
- 開啟watchdog
- 建立.pgpass
- 生成備庫
- 在備庫修改同步資訊
  - 備庫檢視
  - 主庫檢視同步資訊
- 安裝etcd
  - 下載etcd安裝包：
  - 建立etcd啟動檔案
  - 新增到系統啟動命令中
- 安裝python3
  - 驗證安裝並升級pip3
  - 升級pip3
- Patroni 安裝
  - 配置檔案
  - 配置patroni服務
- 安裝Keepalived
  - 配置主備伺服器的Keepalived
    - node1
    - node2
    - node3
    - 啟動keepalived
- 安裝HAProxy
  - 修改配置檔案（主備庫共用一份配置檔案）：
  - 修改核心引數：允許監聽不存在的地址
  - 執行sysctl -p,啟動HAProxy
  - 驗證HAProxy
    - 使用psql連線到vip的5000埠，檢視是否為主庫，並且能否寫入/讀取資料：
    - 建立測試表，寫入資料正常。
    - 使用psql連線到5001埠，檢視是否為備庫，是否可以寫入/讀取資料
  - HAProxy監控頁面

基礎配置

IP 規劃：

前置條件：3 個例項下 PG 安裝配置已完成。

主庫：10.43.20.102

備庫 01：10.43.20.103

備庫 02：10.43.20.104

虛擬IP:10.43.20.105

修改超級使用者密碼：

alter user postgres with password '123456';

在主庫上建立流複製使用者

 psql -c "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secret'"

修改 pg_hba 檔案

host  replication   replicator     10.43.20.102/24     md5
host  replication   replicator     10.43.20.103/24     md5
host  replication   replicator     10.43.20.104/24     md5

備庫重新建立資料目錄：

[postgres@node2 ~]$ echo $PGDATA
/postgresql/pgdata
[postgres@node2 ~]$
[postgres@node2 ~]$ cd /postgresql/
[postgres@node2 postgresql]$ ls
pg14  pgdata  soft
[postgres@node2 postgresql]$ cd pgdata/
[postgres@node2 pgdata]$ rm -rf *
[postgres@node2 pgdata]$

開啟watchdog

modprobe softdog
chown postgres:postgres /dev/watchdog

建立.pgpass

su - postgres
echo 10.43.20.102:5432:replication:replicator:secret >> .pgpass
chmod 0600 .pgpass

生成備庫

pg_basebackup -D /postgresql/pgdata -Fp -Xs -v -P -h 10.43.20.102 -p 5432 -U replicator

在備庫修改同步資訊

cat $PGDATA/postgresql.auto.conf
echo "primary_conninfo = 'user=replicator password=secret host=10.43.20.102 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'" >> $PGDATA/postgresql.auto.conf
touch $PGDATA/standby.signal

備庫檢視

ps -ef | grep postgres

主庫檢視同步資訊

select * from pg_stat_replication；

流複製搭建成功。

安裝etcd

下載etcd安裝包：

最新版為3.5.15，下載etcd-v3.5.15-linux-amd64.tar.gz即可。

3臺機器做相同操作：

[root@node1 ~]# tar -zxvf etcd-v3.5.15-linux-amd64.tar.gz -C /postgresql/soft/
[root@node1 ~]# mv /postgresql/soft/etcd-v3.5.15-linux-amd64  mv /postgresql/soft/etcd-v3.5.15
[root@node1 ~]# cd /postgresql/soft/etcd-v3.5.15/
[root@node1 etcd-v3.5.15]# ls -rt
READMEv2-etcdctl.md  README.md  README-etcdutl.md  README-etcdctl.md  etcdutl  etcdctl  etcd  Documentation   start_etcd.log

建立etcd啟動檔案

vi start_etcd.sh
3臺機器的配置分別為：
--10.43.20.102
/postgresql/soft/etcd-v3.5.15/etcd --name etcd_01 \
  --initial-advertise-peer-urls http://10.43.20.102:2380 \
  --listen-peer-urls http://10.43.20.102:2380 \
  --listen-client-urls http://10.43.20.102:2379,http://127.0.0.1:2379 \
  --advertise-client-urls http://10.43.20.102:2379 \
  --initial-cluster-token etcd-cluster \
  --initial-cluster etcd_01=http://10.43.20.102:2380,etcd_02=http://10.43.20.103:2380,etcd_03=http://10.43.20.104:2380 \
  --initial-cluster-state new \
  --enable-v2

--10.43.20.103
/postgresql/soft/etcd-v3.5.15/etcd --name etcd_02 \
  --initial-advertise-peer-urls http://10.43.20.103:2380 \
  --listen-peer-urls http://10.43.20.103:2380 \
  --listen-client-urls http://10.43.20.103:2379,http://127.0.0.1:2379 \
  --advertise-client-urls http://10.43.20.103:2379 \
  --initial-cluster-token etcd-cluster \
  --initial-cluster etcd_01=http://10.43.20.102:2380,etcd_02=http://10.43.20.103:2380,etcd_03=http://10.43.20.104:2380 \
  --initial-cluster-state new \
  --enable-v2

--10.43.20.104
/postgresql/soft/etcd-v3.5.15/etcd --name etcd_03 \
  --initial-advertise-peer-urls http://10.43.20.104:2380 \
  --listen-peer-urls http://10.43.20.104:2380 \
  --listen-client-urls http://10.43.20.104:2379,http://127.0.0.1:2379 \
  --advertise-client-urls http://10.43.20.104:2379 \
  --initial-cluster-token etcd-cluster \
  --initial-cluster etcd_01=http://10.43.20.102:2380,etcd_02=http://10.43.20.103:2380,etcd_03=http://10.43.20.104:2380 \
  --initial-cluster-state new \
  --enable-v2

新增到系統啟動命令中

vi /usr/lib/systemd/system/etcd.service
[Unit]
Description=etcd
After=network.target remote-fs.target nss-lookup.target
 
[Service]
Type=forking
ExecStart=/bin/bash -c "/postgresql/soft/etcd-v3.5.15/start_etcd.sh > /postgresql/soft/etcd-v3.5.15/start_etcd.log 2>&1 &"
[Install]
WantedBy=multi-user.target

chmod 754 /usr/lib/systemd/system/etcd.service
systemctl daemon-reload
service etcd start
systemctl enable etcd.service
cd /postgresql/soft/etcd-v3.5.15
./etcdctl endpoint status --cluster -w table

etcd搭建完成：

安裝python3

yum install wget gcc make zlib-devel openssl openssl-devel

wget "https://www.python.org/ftp/python/3.6.5/Python-3.6.5.tar.xz"
tar -xvJf Python-3.6.5.tar.xz
cd Python-3.6.5
./configure prefix=/usr/local/python3
make && make install
ln -fs /usr/local/python3/bin/python3 /usr/bin/python3
ln -fs /usr/local/python3/bin/pip3 /usr/bin/pip3

驗證安裝並升級pip3

升級pip3

下載：pip-21.3.1-py3-none-any.whl

https://files.pythonhosted.org/packages/a4/6d/6463d49a933f547439d6b5b98b46af8742cc03ae83543e4d7688c2420f8b/pip-21.3.1-py3-none-any.whl

[root@node1 ~]# pip3 install pip-21.3.1-py3-none-any.whl
Processing ./pip-21.3.1-py3-none-any.whl
Installing collected packages: pip
  Found existing installation: pip 9.0.3
    Uninstalling pip-9.0.3:
      Successfully uninstalled pip-9.0.3
Successfully installed pip-21.3.1

Patroni 安裝

pip3 install psycopg2-binary -i https://mirrors.aliyun.com/pypi/simple/
pip3 install patroni[etcd] -i https://mirrors.aliyun.com/pypi/simple/
ln -fs /usr/local/python3/bin/patroni /usr/bin/patroni
ln -fs /usr/local/python3/bin/patronictl /usr/bin/patronictl

驗證安裝完成：

配置檔案

node1配置：

vi /postgresql/soft/patroni/patroni.yml

scope: pg_patroni
namespace: /service/
name: pg_patroni01
 
restapi:
  listen: 10.43.20.102:8008
  connect_address: 10.43.20.102:8008
 
etcd:
  #Provide host to do the initial discovery of the cluster topology:
  host: 10.43.20.102:2379
 
bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: true
    #standby_cluster:
      #host: 127.0.0.1
      #port: 1111
      #primary_slot_name: patroni
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
      wal_level: locical
      hot_standby: "on"
      wal_keep_segments: 128
      max_wal_senders: 10
      max_replication_slots: 10
      wal_log_hints: "on"
      archive_mode: "on"
      #primary_conninfo: 'host=10.43.20.102 port=31003 user=replicator'
      hot_standby: on
      archive_timeout: 1800s
 
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.43.20.102:5432
  data_dir: /postgresql/pgdata
  bin_dir: /postgresql/pg14/bin
  config_dir: /postgresql/pgdata
  pgpass: /home/postgres/.pgpass
  authentication:
    replication:
      username: replicator
      password: secret
    superuser:
      username: postgres
      password: 123456
 
 
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
nosync: false
watchdog:
    mode: automatic 
    device: /dev/watchdog
    safety_margin: 5

node2配置：

vi /postgresql/soft/patroni/patroni.yml

scope: pg_patroni
namespace: /service/
name: pg_patroni02

restapi:
  listen: 10.43.20.103:8008
  connect_address: 10.43.20.103:8008

etcd:
  #Provide host to do the initial discovery of the cluster topology:
  host: 10.43.20.103:2379

bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: true
    #standby_cluster:
      #host: 127.0.0.1
      #port: 1111
      #primary_slot_name: patroni
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
      wal_level: locical
      hot_standby: "on"
      wal_keep_segments: 128
      max_wal_senders: 10
      max_replication_slots: 10
      wal_log_hints: "on"
      archive_mode: "on"
      #primary_conninfo: 'host=10.43.20.103 port=31003 user=replicator'
      hot_standby: on
      archive_timeout: 1800s

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.43.20.103:5432
  data_dir: /postgresql/pgdata
  bin_dir: /postgresql/pg14/bin
  config_dir: /postgresql/pgdata
  pgpass: /home/postgres/.pgpass
  authentication:
    replication:
      username: replicator
      password: secret
    superuser:
      username: postgres
      password: 123456


tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
nosync: false
watchdog:
    mode: automatic
    device: /dev/watchdog
    safety_margin: 5

node3配置：

vi /postgresql/soft/patroni/patroni.yml

scope: pg_patroni
namespace: /service/
name: pg_patroni03

restapi:
  listen: 10.43.20.104:8008
  connect_address: 10.43.20.104:8008

etcd:
  #Provide host to do the initial discovery of the cluster topology:
  host: 10.43.20.104:2379

bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: true
    #standby_cluster:
      #host: 127.0.0.1
      #port: 1111
      #primary_slot_name: patroni
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
      wal_level: locical
      hot_standby: "on"
      wal_keep_segments: 128
      max_wal_senders: 10
      max_replication_slots: 10
      wal_log_hints: "on"
      archive_mode: "on"
      #primary_conninfo: 'host=10.43.20.104 port=31003 user=replicator'
      hot_standby: on
      archive_timeout: 1800s

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.43.20.104:5432
  data_dir: /postgresql/pgdata
  bin_dir: /postgresql/pg14/bin
  config_dir: /postgresql/pgdata
  pgpass: /home/postgres/.pgpass
  authentication:
    replication:
      username: replicator
      password: secret
    superuser:
      username: postgres
      password: 123456


tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
nosync: false
watchdog:
    mode: automatic
    device: /dev/watchdog
    safety_margin: 5

配置patroni服務

chown -R postgres:postgres /postgresql/soft/patroni/patroni.yml

#配置服務&開機自啟動
 
vi /usr/lib/systemd/system/patroni.service
#新增以下內容：（三臺機器都一致）
 
[Unit]
Description=patroni
After=network.target remote-fs.target nss-lookup.target
 
[Service]
Type=forking
ExecStart=/bin/bash -c "sudo -u postgres patroni  /postgresql/soft/patroni/patroni.yml> /postgresql/soft/patroni/patroni.log 2>&1 &"
[Install]
WantedBy=multi-user.target

#修改許可權，啟動服務
chmod 754 /usr/lib/systemd/system/patroni.service
systemctl daemon-reload
service patroni start
systemctl enable patroni.service

啟動成功。

安裝Keepalived

yum -y install keepalived.x86_64

配置主備伺服器的Keepalived

mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
vi /etc/keepalived/keepalived.conf

配置檔案為：

node1

global_defs {
   smtp_connect_timeout 30    
   router_id LVS_DEVEL01     
}
 
vrrp_instance VI_1 {
    state MASTER     
    interface ens33     
    virtual_router_id 51   
    priority 100          
    advert_int 1          
    authentication {
        auth_type PASS    
        auth_pass 123456    
    }
    virtual_ipaddress {
        10.43.20.105    
    }
}

node2

node3
global_defs {
   smtp_connect_timeout 30
   router_id LVS_DEVEL02  
}
 
vrrp_instance VI_1 {
    state BACKUP    
    interface ens33
    virtual_router_id 51
    priority 90    
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        10.43.20.105  
}

node3

node3
global_defs {
   smtp_connect_timeout 30
   router_id LVS_DEVEL03  
}
 
vrrp_instance VI_1 {
    state BACKUP    
    interface ens33
    virtual_router_id 51
    priority 80    
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        10.43.20.105  
}

啟動keepalived

虛擬IP已經繫結。

安裝HAProxy

 yum -y install haproxy.x86_64

修改配置檔案（主備庫共用一份配置檔案）：

vi /etc/haproxy/haproxy.cfg
global
    maxconn 100000
    stats  timeout 5s
    user   haproxy
    group  haproxy
    daemon
 
defaults
    mode               tcp
    log                global
    retries            2
    timeout queue      5s
    timeout connect    5s
    timeout client     60m
    timeout server     60m
    timeout check      15s
 
listen stats
    mode  http
    bind  10.43.20.102:7000
    stats enable
    stats uri /stats
 
listen master
    bind 10.43.20.105:5000
    mode tcp
    maxconn 2000
    option tcplog
    option httpchk OPTIONS /master
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions
    server db01 10.43.20.102:5432 check port 8008
    server db02 10.43.20.103:5432 check port 8008
    server db03 10.43.20.104:5432 check port 8008
 
listen replicas
    bind 10.43.20.105:5001
    mode tcp
    maxconn 6000
    option tcplog
    option httpchk OPTIONS /replica
    balance roundrobin
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
    server db01 10.43.20.102:5432 check port 8008
    server db02 10.43.20.103:5432 check port 8008
    server db03 10.43.20.104:5432 check port 8008

修改核心引數：允許監聽不存在的地址

vi /etc/sysctl.conf
#文末尾新增
net.ipv4.ip_nonlocal_bind = 1

執行sysctl -p,啟動HAProxy

驗證HAProxy

使用psql連線到vip的5000埠，檢視是否為主庫，並且能否寫入/讀取資料：

su - postgres 
[postgres@node1 ~]$ psql -h 10.43.20.105 -p5000
select * from pg_is_in_recovery();
create table t1(id int);
insert into t1 values(1);
select * from t1;

建立測試表，寫入資料正常。

可讀寫，驗證透過。

使用psql連線到5001埠，檢視是否為備庫，是否可以寫入/讀取資料

[postgres@node1 ~]$ psql -h 10.43.20.105 -p5001
select * from pg_is_in_recovery();
create table t2(id int);

只讀，不可寫，驗證透過。

HAProxy監控頁面

訪問：http://10.43.20.102:7000/stats

master只有一個db為up。

replicas有兩個db為up。

MMM高可用配置
2024-06-30
解密數倉高可用failover流程
2023-03-08
解密AI
ES資料庫高可用配置
2021-01-27
資料庫
Docker 高可用配置中心Etcd
2017-10-27
Docker
【SpringCloud】微服務Eureka高可用配置
2020-11-25
SpringGCCloud微服務
7. Nginx實現高可用配置
2020-11-30
Nginx
配置高可用的Hadoop平臺
2015-02-25
Hadoop
NFS-Ganasha 高可用配置
2024-08-06
NFS
MHA高可用配置及故障切換
2024-06-30
Linux 高可用仲裁裝置配置
2024-08-30
Linux
nginx內建高可用配置與第三方高可用模組nginx_ustream_check_mudule配置
2020-04-16
Nginx
HDFS High Availability（HA）高可用配置
2022-05-15
AI
Hadoop框架：HDFS高可用環境配置
2020-10-27
Hadoop框架
Kafka高可用叢集部署與配置指南
2020-10-16
Kafka
搭建高可用MongoDB叢集（一）：配置MongoDB
2014-07-02
MongoDB
RabbitMQ高階指南：從配置、使用到高可用叢集搭
2017-05-17
MQ
RabbitMQ高階指南：從配置、使用到高可用叢集搭建
2017-12-21
MQ
Redis操作及叢集搭建以及高可用配置
2020-09-01
Redis
Redis Sentinel高可用配置及C#訪問
2015-01-02
RedisC#
opengauss高可用之keepalived配置原創
2024-05-19
高可用服務之Keepalived郵件通知配置
2020-09-10
MHA+MySQL主從配置實現MySQL高可用
2018-07-10
MySql
springcloud(八)：配置中心服務化和高可用
2017-05-31
SpringGCCloud
RedHat AS 3.0下高可用性叢集配置(轉)
2007-08-11
Redhat
PostgreSQL repmgr高可用叢集+keepalived高可用
2020-09-02
SQL
EurekaServer高可用
2018-06-15
Server
什麼是高可用？高可用軟體哪家好？
2022-10-24
Kubernetes安裝之一：HA-高可用配置
2019-02-13
Nginx+Keepalived 主備高可用安裝與配置
2017-06-19
Nginx
在Linux中，如何進行高可用性配置？
2024-06-02
Linux
Redis高可用 Sentinel
2018-04-02
Redis
nt高可用部署
2024-06-15
Keepalived 高可用
2024-06-24
高可用架構
2024-09-14
架構
服務發現與配置管理高可用最佳實踐
2022-01-05
Eureka高可用叢集服務端和客戶端配置
2023-02-25
服務端客戶端
Heartbeat_2.0.3配置MySQL5.0.18為高可用叢集
2007-02-14
MySql
高可用系列文章之三 - NGINX 高可用實施方案
2022-12-19
Nginx

pg14+etcd+Patroni 高可用配置流程

pg14+etcd+Patroni 高可用配置流程

基礎配置

IP 規劃：

修改超級使用者密碼：

在主庫上建立流複製使用者

修改 pg_hba 檔案

備庫重新建立資料目錄：

開啟watchdog

建立.pgpass

生成備庫

在備庫修改同步資訊

備庫檢視

主庫檢視同步資訊

安裝etcd

下載etcd安裝包：

建立etcd啟動檔案

新增到系統啟動命令中

安裝python3

驗證安裝並升級pip3

升級pip3

Patroni 安裝

配置檔案

配置patroni服務

安裝Keepalived

配置主備伺服器的Keepalived

node1

node2

node3

啟動keepalived

安裝HAProxy

修改配置檔案（主備庫共用一份配置檔案）：

修改核心引數：允許監聽不存在的地址

執行sysctl -p,啟動HAProxy

驗證HAProxy

使用psql連線到vip的5000埠，檢視是否為主庫，並且能否寫入/讀取資料：

建立測試表，寫入資料正常。

使用psql連線到5001埠，檢視是否為備庫，是否可以寫入/讀取資料

HAProxy監控頁面

相關文章