pgcat icon indicating copy to clipboard operation
pgcat copied to clipboard

Allow failover for replicas

Open magec opened this issue 1 year ago • 2 comments

Now that https://github.com/postgresml/pgcat/pull/847 is merged, when a pool is set to default_role = replica (or manually set to replica), and no replicas are available, the primary won't be touched at all. Instead, replicas will be automatically set back to available and will be checked for health again.

If you have a single replica and it is actually down, you may find convenient to send queries to the primary until you fix the replica (if the primary can handle the load). To allow this behavior, a new configuration parameter is added to the configuration that will make the primary available to receive queries if every replica in the pool is banned.

### replica_to_primary_failover_enabled
` ` `
path: pools.<pool_name>.replica_to_primary_failover_enabled
default: "false"
` ` ` 

If set to true, when the specified role is `replica` (either by setting `default_role` or manually)
and all replicas are banned, queries will be sent to the primary (until a replica is back online).

Note that when this is enabled, the replicas won't be automatically unbanned (default behavior).

magec avatar Nov 11 '24 09:11 magec

@magec Hey! Did I understand correctly that with this PR, it will be possible to switch over between the primary and replica, like in the request here: GitHub Issue #228?

WIth default_role: primary and replica_to_primary_failover_enabled: true and pg_auto_failover: When a failover happens, pgcat should send traffic to the "replica." After the "primary" recovers, pgcat will use the primary again. That would be awesome!

dantio avatar Feb 06 '25 08:02 dantio

What this allow is using the primary when all replicas are down.

magec avatar Feb 06 '25 14:02 magec