Re: Fast switchover

On Mon, 2025-09-08 at 15:03 +0000, legrand legrand wrote:
> For some projects we need a fast manual switchover to address Near Zero downtime maintenance
> (not speaking here about automated failover like those provided by HA tools, but just planned, controlled operations)
>
> Database Physical replication switchover itself:
> - initial replication (before switchover) should be synchronous or replication LAG should be controlled to prevent data loss.
> - Switchover duration seems not "compressible" under a few seconds (because of primary shutdown, promotion, new standby catch up, ...)
> - Application retry strategy (after disconnection) should be tuned using proper retry delay. Pooler or specific driver may help.

There is no need for synchronous replication; you cannot lose data with a switchover,
if you do it right:

- run a CHACKPOINT on the primary (to speed up the shutdown)
- when the checkpoint is done, perform a clean shutdown
- when the primary is down, promote the standby

The primary will transmit *all* data to the standby before it shuts down.

> May logical replication ( bi-directional, with one instance RW and the other RO) be a better solution ?

I'd say no.

> what could we expect (in term of downtime in both worlds) ?

Usually seconds, so plan for ten minutes.

Yours,
Laurenz Albe