Subject: Re: Fast switchover

From: Laurenz Albe <[email-masked]>
Date: Mon, 08 Sep 2025 21:54:14 +0200

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

---

From: Klaus Darilion <[email-masked]>
Date: Mon, 8 Sep 2025 20:31:45 +0000

> Usually seconds, so plan for ten minutes.

*lol*
So true ...

---

From: legrand legrand <[email-masked]>
Date: Tue, 9 Sep 2025 05:07:26 +0000

Hi Laurenz,
Thank you for your answer

>> For some projects we need a fast manual switchover to address Near Zero downtime maintenance

I forgot to say that application would not be stopped during maintenance…

>There is no need for synchronous replication; you cannot lose data with a switchover,

Ok

> I'd say no.

Really ?

> Usually seconds, so plan for ten minutes.

Brrr, I was thînking about a more reliable process.

Regards
PAscal

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 09 Sep 2025 07:22:02 +0200

> Brrr, I was thînking about a more reliable process.

If you want more reliable numbers, make a test run on your system.

Yours,
Laurenz Albe