PG HA Drill

You can strengthen your confidence about HA cluster by drills

Simulate several common failures in production environments to test the self-healing capabilities of Pigsty’s highly available database cluster.

Patroni Quick Start

patronictl is the patroni cli tool, aliased as pt

  1. alias pt='patronictl -c /pg/bin/patroni.yml'
  3. alias pt-up='sudo systemctl start patroni'     # launch patroni
  4. alias pt-dw='sudo systemctl stop  patroni'     # stop patroni
  5. alias pt-st='systemctl status patroni'         # report patroni status
  6. alias pt-ps='ps aux | grep patroni'            # show patroni processes
  7. alias pt-log='tail -f /pg/log/patroni.log'     # watch patroni logs

Patroni commands requires dbsu (which is postgres by default )

  1. $ pt --help
  2. Usage: patronictl [OPTIONS] COMMAND [ARGS]...
  4. Options:
  5.   -c, --config-file TEXT  Configuration file
  6.   -d, --dcs TEXT          Use this DCS
  7.   -k, --insecure          Allow connections to SSL sites without certs
  8.   --help                  Show this message and exit.
  10. Commands:
  11.   configure    Create configuration file
  12.   dsn          Generate a dsn for the provided member,...
  13.   edit-config  Edit cluster configuration
  14.   failover     Failover to a replica
  15.   flush        Discard scheduled events
  16.   history      Show the history of failovers/switchovers
  17.   list         List the Patroni members for a given Patroni
  18.   pause        Disable auto failover
  19.   query        Query a Patroni PostgreSQL member
  20.   reinit       Reinitialize cluster member
  21.   reload       Reload cluster member configuration
  22.   remove       Remove cluster from DCS
  23.   restart      Restart cluster member
  24.   resume       Resume auto failover
  25.   scaffold     Create a structure for the cluster in DCS
  26.   show-config  Show cluster configuration
  27.   switchover   Switchover to a replica
  28.   topology     Prints ASCII topology for given cluster
  29.   version      Output version of patronictl command or a...

场景一：Switchover

Switch是主动切换集群领导者

  1. $ pt switchover
  2. Master [pg-test-3]: pg-test-3
  3. Candidate ['pg-test-1', 'pg-test-2'] []: pg-test-1
  4. When should the switchover take place (e.g. 2020-10-23T17:06 )  [now]: now
  5. Current cluster topology
  6. + Cluster: pg-test (6886641621295638555) -----+----+-----------+-----------------+
  7. | Member    | Host        | Role    | State   | TL | Lag in MB | Tags            |
  8. +-----------+-------------+---------+---------+----+-----------+-----------------+
  9. | pg-test-1 | 10.10.10.11 | Replica | running |  2 |         0 | clonefrom: true |
  10. | pg-test-2 | 10.10.10.12 | Replica | running |  2 |         0 | clonefrom: true |
  11. | pg-test-3 | 10.10.10.13 | Leader  | running |  2 |           | clonefrom: true |
  12. +-----------+-------------+---------+---------+----+-----------+-----------------+
  13. Are you sure you want to switchover cluster pg-test, demoting current master pg-test-3? [y/N]: y
  14. 2020-10-23 16:06:11.76252 Successfully switched over to "pg-test-1"

场景二：Failover

  1. # run as postgres @ any member of cluster `pg-test`
  2. $ pt failover
  3. Candidate ['pg-test-2', 'pg-test-3'] []: pg-test-3
  4. Current cluster topology
  5. + Cluster: pg-test (6886641621295638555) -----+----+-----------+-----------------+
  6. | Member    | Host        | Role    | State   | TL | Lag in MB | Tags            |
  7. +-----------+-------------+---------+---------+----+-----------+-----------------+
  8. | pg-test-1 | 10.10.10.11 | Leader  | running |  1 |           | clonefrom: true |
  9. | pg-test-2 | 10.10.10.12 | Replica | running |  1 |         0 | clonefrom: true |
  10. | pg-test-3 | 10.10.10.13 | Replica | running |  1 |         0 | clonefrom: true |
  11. +-----------+-------------+---------+---------+----+-----------+-----------------+
  12. Are you sure you want to failover cluster pg-test, demoting current master pg-test-1? [y/N]: y
  13. + Cluster: pg-test (6886641621295638555) -----+----+-----------+-----------------+
  14. | Member    | Host        | Role    | State   | TL | Lag in MB | Tags            |
  15. +-----------+-------------+---------+---------+----+-----------+-----------------+
  16. | pg-test-1 | 10.10.10.11 | Replica | running |  2 |         0 | clonefrom: true |
  17. | pg-test-2 | 10.10.10.12 | Replica | running |  2 |         0 | clonefrom: true |
  18. | pg-test-3 | 10.10.10.13 | Leader  | running |  2 |           | clonefrom: true |
  19. +-----------+-------------+---------+---------+----+-----------+-----------------+

场景三：从库Patroni/Postgres宕机

场景四：主库Patroni/Postgres宕机

场景五：DCS不可用

场景六：维护模式

问题探讨

关键问题：DCS的SLA如何保障？

\==在自动切换模式下，如果DCS挂了，当前主库会在retry_timeout 后Demote成从库，导致所有集群不可写==。

作为分布式共识数据库，Consul/Etcd是相当稳健的，但仍必须确保DCS的SLA高于DB的SLA。

解决方法：配置一个足够大的retry_timeout，并通过几种以下方式从管理上解决此问题。

  1. SLA确保DCS一年的不可用时间短于该时长
  2. 运维人员能确保在retry_timeout之内解决DCS Service Down的问题。
  3. DBA能确保在retry_timeout之内将关闭集群的自动切换功能（打开维护模式）。

可以优化的点？ 添加绕开DCS的P2P检测，如果主库意识到自己所处的分区仍为Major分区，不触发操作。

关键问题：HA策略，RPO优先或RTO优先？

可用性与一致性谁优先？例如，普通库RTO优先，金融支付类RPO优先。

普通库允许紧急故障切换时丢失极少量数据（阈值可配置，例如最近1M写入）

与钱相关的库不允许丢数据，相应地在故障切换时需要更多更审慎的检查或人工介入。

关键问题：Fencing机制，是否允许关机？

在正常情况下，Patroni会在发生Leader Change时先执行Primary Fencing，通过杀掉PG进程的方式进行。

但在某些极端情况下，比如vm暂停，软件Bug，或者极高负载，有可能没法成功完成这一点。那么就需要通过重启机器的方式一了百了。是否可以接受？在极端环境下会有怎样的表现？

关键操作：选主之后

选主之后要记得存盘。手工做一次Checkpoint确保万无一失。

关键问题：流量切换怎样做，2层，4层，7层

  • 2层：VIP漂移
  • 4层：Haproxy分发
  • 7层：DNS域名解析

关键问题：一主一从的特殊场景

  • 2层：VIP漂移
  • 4层：Haproxy分发
  • 7层：DNS域名解析

HA Procedure

Failure Detection

https://patroni.readthedocs.io/en/latest/SETTINGS.html#dynamic-configuration-settings

Fencing

Configure Watchdog

https://patroni.readthedocs.io/en/latest/watchdog.html

Bad Cases

Traffic Routing

DNS

VIP

HAproxy

Pgbouncer

PG HA Drill - 图1

Last modified 2022-06-04: fill en docs (5a858d3)