hapostgres / hapostgres/pg_auto_failover

Multi-node failure requires manual intervention

Open
#858 4 comments 4 reactions 0 assignees View on GitHub
Dominant language
C
Stars
1.4k
Forks
142
Avg merge
5h 8m
Merged PRs (30d)
1

Description

Hello, we have been testing a variety of scenarios using pg_autofailover and disaster recovery.

If we have a 6 node setup, and we destroy 3 that includes the primary, it takes manual intervention to get out of `report_lsn` state and promote a primary from the remaining healthy nodes.
This was surprising to us.

Is there a reason that we don't have a timeout for [this scenario](https://github.com/citusdata/pg_auto_failover/blob/master/src/monitor/group_state_machine.c#L1710)?

Some output from our scenario, for your perusal follows.

_Postgres version: 14.1
pg_autofailover version: 1.6.3
OS: centos7_

After cutting network access to/from node_1, node_2, and node_3, we see the following:

```bash
-bash-4.2$ pg_autoctl show state
Name | Node | Host:Port | TLI: LSN | Connection | Reported State | Assigned State
-------+-------+-----------------------------------------+----------------+--------------+---------------------+--------------------
node_1 | 1 | hadr-node-a.c.data-pcf-db.internal:5432 | 1: 0/B002508 | read-write ! | primary | draining
node_2 | 2 | hadr-node-b.c.data-pcf-db.internal:5432 | 1: 0/B002508 | read-only ! | secondary | secondary
node_3 | 3 | hadr-node-c.c.data-pcf-db.internal:5432 | 1: 0/B002508 | read-only ! | secondary | secondary
node_4 | 4 | hadr-node-d.c.data-pcf-db.internal:5432 | 1: 0/B002580 | read-only | report_lsn | report_lsn
node_5 | 5 | hadr-node-e.c.data-pcf-db.internal:5432 | 1: 0/B002580 | read-only | report_lsn | report_lsn
node_6 | 6 | hadr-node-f.c.data-pcf-db.internal:5432 | 1: 0/B002580 | read-only | report_lsn | report_lsn
```
and

```bash
-bash-4.2$ pg_autoctl show settings
Context | Name | Setting | Value
----------+---------+---------------------------+------
formation | default | number_sync_standbys | 1
primary | node_1 | synchronous_standby_names | ''
node | node_1 | candidate priority | 50
node | node_2 | candidate priority | 50
node | node_3 | candidate priority | 50
node | node_4 | candidate priority | 10
node | node_5 | candidate priority | 10
node | node_6 | candidate priority | 0
node | node_1 | replication quorum | true
node | node_2 | replication quorum | true
node | node_3 | replication quorum | true
node | node_4 | replication quorum | true
node | node_5 | replication quorum | true
node | node_6 | replication quorum | true
```

After waiting a long time (10+ min), we still see:
```bash
00:27:36 2066 INFO Failover still in progress after 3 nodes reported their LSN and we are waiting for 2 nodes to
report, activeNode is node 6 "node_6" (hadr-node-f.c.data-pcf-db.internal:5432) and reported state "report_lsn"
```

When we dropped node_2 and node_3 on the monitor, we were able to recover:

```bash
pg_autoctl drop node node_2
pg_autoctl drop node node_3
```

Final State, after a short bit of waiting (from `pg_autoctl watch` on the monitor):
```bash
Name Node Quorum Priority TLI: LSN Check Connection Report Reported State Assigned State
node_1 1 yes 50 1: 0/B002508 17s read-write ! 32m27s primary demoted
node_2 2 yes 50 1: 0/B002508 17s read-only ! 32m28s secondary dropped
node_3 3 yes 50 1: 0/B002508 17s read-only ! 32m28s secondary dropped
node_4 4 yes 10 2: 0/B002830 17s read-write 1s primary primary
node_5 5 yes 10 2: 0/B002830 6 17s read-only 1s secondary secondary
node_6 6 yes 0 2: 0/B002830 5 17s read-only 1s secondary secondary
```

Other interesting state:
node_1 properly demoted itself (for our testing purposes, we left it up, but had cut ingress/egress network traffic).

-----------

So you understand why we are working on these types of tests, we are attempting to find a good multi-site disaster recovery plan. Ideally, we can have asynchronous replication across data-centers, and then cascading replication within each DC to save on network costs and bandwidth.
Having a monitor node live outside of those two datacenters is one possibility, but cascading asynchronous replication from a secondary is currently not a pg_autofailover feature (as far we have seen).
We were also playing with the idea of having two monitors, where one does not require a 'primary' but keeps one of its node's following a primary outside its management (call it a 'secondary-leader' or 'standby-leader').
The idea being you could "promote" this entire pg_autofailover cluster in the case of disaster in the primary's site, and the second site would make sure one of its nodes is always following the primary (in case its `secondary-leader` fails).

We'd love to hear your input on this scenario, and these ideas, as well as the issue at hand.

@rheaton & @swati-nair

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.