Message-ID: <aa9c4428c2066cb683952950ce07df85f50b962c.camel@cybertec.at>
Subject: Re: Fast switchover
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: legrand legrand <legrand_legrand@hotmail.com>, 
 "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Mon, 08 Sep 2025 21:54:14 +0200
In-Reply-To: <AS1P190MB17014CF63D3EDDB0F4F7B66B900CA@AS1P190MB1701.EURP190.PROD.OUTLOOK.COM>
References: 
	<AS1P190MB17014CF63D3EDDB0F4F7B66B900CA@AS1P190MB1701.EURP190.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/aa9c4428c2066cb683952950ce07df85f50b962c.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-09-08 at 15:03 +0000, legrand legrand wrote:
> For some projects we need a fast manual=C2=A0switchover to address Near Z=
ero downtime maintenance
> (not speaking here about automated failover like those provided by HA too=
ls, but just planned, controlled operations)
>=20
> Database Physical replication switchover itself:
> - initial replication (before switchover) should be synchronous or replic=
ation LAG should be controlled to prevent data loss.
> - Switchover duration seems not "compressible" under a few seconds (becau=
se 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 s=
witchover,
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 ot=
her 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