Patroni

PG高可用

PostgreSQL的开源HA工具有很多种，下面几种算是比较常用的

• PAF(PostgreSQL Automatic Failomianver)
• repmgr
• Patroni

它们的比较可以参考: https://scalegrid.io/blog/managing-high-availability-in-postgresql-part-1/

Patroni

Patroni 提供了用于配置高可用性 PostgreSQL 集群的模板。

Introduction

参数顺序

Patroni configuration

patronictl

patronictl --help

patronictl -c postgres-ha-bootstrap.yaml --help
Usage: patronictl [OPTIONS] COMMAND [ARGS]...

Options:
  -c, --config-file TEXT  Configuration file
  -d, --dcs TEXT          Use this DCS
  -k, --insecure          Allow connections to SSL sites without certs
  --help                  Show this message and exit.

Commands:
  configure    Create configuration file
  dsn          Generate a dsn for the provided member, defaults to a dsn...
  edit-config  Edit cluster configuration
  failover     Failover to a replica
  flush        Discard scheduled events
  history      Show the history of failovers/switchovers
  list         List the Patroni members for a given Patroni
  pause        Disable auto failover
  query        Query a Patroni PostgreSQL member
  reinit       Reinitialize cluster member
  reload       Reload cluster member configuration
  remove       Remove cluster from DCS
  restart      Restart cluster member
  resume       Resume auto failover
  scaffold     Create a structure for the cluster in DCS
  show-config  Show cluster configuration
  switchover   Switchover to a replica
  topology     Prints ASCII topology for given cluster
  version      Output version of patronictl command or a running Patroni...

patronictl edit-config & show-config

patronictl edit-config --force -s/-p 参数

patronictl show-config

patronictl list

patronictl -c postgres-ha-bootstrap.yaml list
+ Cluster: postgres-11c0db98 (7198370692495388831) -+---------+----+-----------+
| Member                | Host       | Role         | State   | TL | Lag in MB |
+-----------------------+------------+--------------+---------+----+-----------+
| postgres-11c0db98-0-0 | 245.0.0.26 | Leader       | running |  2 |           |
| postgres-11c0db98-1-0 | 245.0.0.37 | Sync Standby | running |  2 |         0 |
+-----------------------+------------+--------------+---------+----+-----------+

patronictl reinit

Leader 不能 reinit

patronictl -c postgres-ha-bootstrap.yaml reinit postgres-11c0db98
+ Cluster: postgres-11c0db98 (7198370692495388831) -+---------+----+-----------+
| Member                | Host       | Role         | State   | TL | Lag in MB |
+-----------------------+------------+--------------+---------+----+-----------+
| postgres-11c0db98-0-0 | 245.0.0.26 | Leader       | running |  2 |           |
| postgres-11c0db98-1-0 | 245.0.0.37 | Sync Standby | running |  2 |         0 |
+-----------------------+------------+--------------+---------+----+-----------+
Which member do you want to reinitialize [postgres-11c0db98-0-0, postgres-11c0db98-1-0]? []: postgres-11c0db98-0-0 
Are you sure you want to reinitialize members postgres-11c0db98-0-0? [y/N]: y
Failed: reinitialize for member postgres-11c0db98-0-0, status code=503, (I am the leader, can not reinitialize)

patronictl -c postgres-ha-bootstrap.yaml reinit postgres-11c0db98 postgres-11c0db98-1-0
+ Cluster: postgres-11c0db98 (7198370692495388831) -+---------+----+-----------+
| Member                | Host       | Role         | State   | TL | Lag in MB |
+-----------------------+------------+--------------+---------+----+-----------+
| postgres-11c0db98-0-0 | 245.0.0.26 | Leader       | running |  2 |           |
| postgres-11c0db98-1-0 | 245.0.0.37 | Sync Standby | running |  2 |         0 |
+-----------------------+------------+--------------+---------+----+-----------+
Are you sure you want to reinitialize members postgres-11c0db98-1-0? [y/N]: y
Success: reinitialize for member postgres-11c0db98-1-0

patronictl -c switchover

patronictl -c postgres-ha-bootstrap.yaml switchover 
Master [postgres-11c0db98-0-0]: 
Candidate ['postgres-11c0db98-1-0'] []: 
When should the switchover take place (e.g. 2023-02-25T22:37 )  [now]: 
Current cluster topology
+ Cluster: postgres-11c0db98 (7198370692495388831) -+---------+----+-----------+
| Member                | Host       | Role         | State   | TL | Lag in MB |
+-----------------------+------------+--------------+---------+----+-----------+
| postgres-11c0db98-0-0 | 245.0.0.26 | Leader       | running |  2 |           |
| postgres-11c0db98-1-0 | 245.0.0.37 | Sync Standby | running |  2 |         0 |
+-----------------------+------------+--------------+---------+----+-----------+
Are you sure you want to switchover cluster postgres-11c0db98, demoting current master postgres-11c0db98-0-0? [y/N]: y
2023-02-25 21:37:44.30145 Successfully switched over to "postgres-11c0db98-1-0"
+ Cluster: postgres-11c0db98 (7198370692495388831) -------+----+-----------+
| Member                | Host       | Role    | State    | TL | Lag in MB |
+-----------------------+------------+---------+----------+----+-----------+
| postgres-11c0db98-0-0 | 245.0.0.26 | Replica | stopping |    |   unknown |
| postgres-11c0db98-1-0 | 245.0.0.37 | Leader  | running  |  2 |           |
+-----------------------+------------+---------+----------+----+-----------+

patronictl -c postgres-ha-bootstrap.yaml list
+ Cluster: postgres-11c0db98 (7198370692495388831) -------+----+-----------+
| Member                | Host       | Role    | State    | TL | Lag in MB |
+-----------------------+------------+---------+----------+----+-----------+
| postgres-11c0db98-0-0 | 245.0.0.26 | Replica | stopping |    |   unknown |
| postgres-11c0db98-1-0 | 245.0.0.37 | Leader  | running  |  3 |           |
+-----------------------+------------+---------+----------+----+-----------+

patronictl -c postgres-ha-bootstrap.yaml list
+ Cluster: postgres-11c0db98 (7198370692495388831) -+---------+----+-----------+
| Member                | Host       | Role         | State   | TL | Lag in MB |
+-----------------------+------------+--------------+---------+----+-----------+
| postgres-11c0db98-0-0 | 245.0.0.26 | Sync Standby | running |  3 |         0 |
| postgres-11c0db98-1-0 | 245.0.0.37 | Leader       | running |  3 |           |
+-----------------------+------------+--------------+---------+----+-----------+

patronictl failover

patronictl -c postgres-ha-bootstrap.yaml failover
Candidate ['postgres-11c0db98-0-0'] []: 
Error: Failover could be performed only to a specific candidat

patronictl failover <clusterName> --candidate <memberName> --force

部署

基于Patroni的PostgreSQL高可用环境部署

failsafe可以解决此问题

etcd性能差的情况下可以适当调整该参数。