Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uvf2b-00GwOQ-1a for pgsql-general@arkaria.postgresql.org; Mon, 08 Sep 2025 16:48:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uvf2a-006Dow-4w for pgsql-general@arkaria.postgresql.org; Mon, 08 Sep 2025 16:48:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uvf2Z-006Doo-PK for pgsql-general@lists.postgresql.org; Mon, 08 Sep 2025 16:48:28 +0000 Received: from mail-oa1-x36.google.com ([2001:4860:4864:20::36]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uvf2X-001Ee2-0K for pgsql-general@lists.postgresql.org; Mon, 08 Sep 2025 16:48:27 +0000 Received: by mail-oa1-x36.google.com with SMTP id 586e51a60fabf-322695f899aso1573952fac.1 for ; Mon, 08 Sep 2025 09:48:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1757350103; x=1757954903; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=bOrSrr3NDn1fKELKghMtlLROaWqHv5R7Ju0hnGCYQh8=; b=R78yzKNDPvUNaJKFIEslwX0fsBxvxVXdtrGg9zo2ZK+4EMw+AY4p2qGOSU8qYbybar Ky4TrTx/ksJ0bjaojWtnlNNMvXa+6zj+CPxAxiWe3j0uF4jZrsc2+fbOUG151L4bTbYa MEzbgz4mvdC6/wtqS0v127++DSKjGyu77bindqniAyAaHWR2NsVSQXmx3Nb+4eYQ/UD8 pSWuzDBubRZj5h0ZjSFqfEJB31MN2Hbhrx0DyWzTCQ1cCuhEVlIqkcrLc0J3o1+cZUVV XHxsmXzCyOzIL8zob463C3AR+pGBjkeTGgP+EdpMjFGTmGgtUr+NT4f6JPtnjD1PSMsI rcPA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1757350103; x=1757954903; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=bOrSrr3NDn1fKELKghMtlLROaWqHv5R7Ju0hnGCYQh8=; b=RjLPtuifblEQDrvdBBqxyncYsUkcAqp5/SeVBSYgss990UUubO/K3A7AvYgognjIqI H9M3S9NnGtkeiSUg/6O4EbSodJZ+rPmHJCuGPF3TdkimlDc3nYKDRS+HYnC/imris/q0 uHWq28kJvAD1VYC1W7pY/P1S5yy6dv2FuHYh2oAiPT/9xIDLFMVOHhwWVsczYj9DmYhe KLQY+/71Zh5h+s/oEosJZv5O2Eav+6g3w9eP29hLxJXhtBPtdq/OSmorkvPaeuJwULQK 3hX8ytm1kEyXqdWhYq/+oDnY4EkNhKTFCJPn6LQAj0fVDvTdf9xNifFlYMRTnonBqYZq 8PSg== X-Gm-Message-State: AOJu0Yxe0Wh8d1F64fV2fYjBPvb33iBEkx/0GAHrI/hWhr/a4reNddxs 0dXhn81zlDVgQT04qi0CW6WshMXpKODRbNMMNxgHBSbTQms2EFHTzcLY+VZBsPj9vlVk/B9v14o CW0KrGtyGnNBKTwfvfjI76lvdUQXvOodZnQ== X-Gm-Gg: ASbGnctbkO/Qd+zwQqJNfEq0ZMK8XCBu7+ZPx4iaM0Tk0oPI7uqOdHC7CRON7a22lp7 w9w3ew1xaDMxsF09aJQFDSd3Vqql8ag6dc7twOkGwl7dXdjEEecm+g3mCZCcKzGgnHb+z2K0a4P M1B9F7krI9mWbYJ8pGOMdpKQU1QH8A5rzXpX3DGs2xEizPoEXZDCvyd8hxSMelSUUH+iV/V4XLI aR0pNqVdiBZpZBC308= X-Google-Smtp-Source: AGHT+IHkZ9/01D16cAzOgSmMSYPoE6y+UV2JUA31I+Va0zQ5rV+8VXx7gkHNz0EwJykhdUrIbQR1pRCjWS9rwokXO1U= X-Received: by 2002:a05:6870:f116:b0:2ff:9776:1231 with SMTP id 586e51a60fabf-322626478c2mr3758905fac.1.1757350103491; Mon, 08 Sep 2025 09:48:23 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 8 Sep 2025 12:48:12 -0400 X-Gm-Features: Ac12FXyrHUbLqzZsUal74fqqYSOpKVGLxyTZtPcvB7LIgCW30tyMrZ09_dtPfmc Message-ID: Subject: Re: Fast switchover To: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000aa2c05063e4cf459" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000aa2c05063e4cf459 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Sep 8, 2025 at 12:37=E2=80=AFPM Klaus Darilion wrote: > > > *From:* Ron Johnson > *Sent:* Monday, September 8, 2025 6:10 PM > *To:* pgsql-general@lists.postgresql.org > *Subject:* Re: Fast switchover > > > > On Mon, Sep 8, 2025 at 11:03=E2=80=AFAM legrand legrand < > legrand_legrand@hotmail.com> wrote: > > Hello all the readers, > > > > 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 will always be a few seconds delay while the applications reconnect= . > > > > Do the applications connect via a VIP? That's simpler for the applicatio= n. > > > > This is what I do from the not-yet-new-primary: > > 1. psql -h $CurrentPrimary -c "ALTER SYSTEM SET > synchronous_standby_names TO '*';" > 2. Wait a few seconds. > 3. ssh $CurrentPrimary sudo ip del $VIP # cmd is more complicated, but > you get the idea > 4. ssh $CurrentPrimary pg_ctl stop -mfast # to kill connections, has > to happen, no matter the solution. > > If you remove the VIP in step 3, the TCP connections on the client side > are broken (may hang around), and will not be properly terminated if you > stop postgresql in step 4. Thay may cause delays on the client detecting > the broken TCP connection and reconnect to the server (depending on the > network/firewall configuration on the servers). Maybe faster reconnect ca= n > be achieved if you first stop postgresql, and then remove the VIP. > Interesting. Thanks. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000aa2c05063e4cf459 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Sep 8, 2025 at 12:37=E2=80=AFPM K= laus Darilion <klaus.darilion@n= ic.at> wrote:
=

=C2=A0<= /span>

From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Monday, September 8, 2025 6:10 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Fast switchover

=C2=A0

On Mon, Sep 8, 2025 at 11:03=E2=80=AFAM legrand legrand <legrand_legrand@hotmail.c= om> wrote:

Hello all the readers,

=C2=A0

For some projects we need a fast manual=C2=A0switchover to address Near Zero downtime maintenance<= /u>

(not speaking here about automated failover like those provided by H= A tools, but just planned, controlled operations)

=C2=A0

=C2=A0

Database Physical replication switchover itself:

- initial replication (before switchover) should be synchronous or r= eplication 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 u= sing proper retry delay. Pooler or specific driver may help.<= /span>


There will always be a few seconds delay while the a= pplications=C2=A0reconnect.

=C2=A0

Do the applications connect via a VIP?=C2=A0 That= 9;s simpler for the application.

=C2=A0

This is what I do from the not-yet-new-primary:

  1. psql -h=C2=A0 $CurrentPrimary -c "ALTER SYSTEM SET synchronous_standby= _names TO '*';"
  2. Wait a few seconds.
  3. ssh $CurrentPrimary sudo ip del $VIP # cmd is more complicated, but you get= the idea
  4. ssh $CurrentPrimary pg_ctl stop -mfast # to kill connections, has to happen= , no matter the solution.

If you remove the VIP= in step 3, the TCP connections on the client side are broken (may hang aro= und), and will not be properly terminated if you stop postgresql in step 4.= Thay may cause delays on the client detecting the broken TCP connection and reconnect to the server (depending= on the network/firewall configuration on the servers). Maybe faster reconn= ect can be achieved if you first stop postgresql, and then remove the VIP.<= /span>


Interesting.=C2=A0 Thanks.

--
Death to <Redacted>, and butter sauce.
Don't boi= l me, I'm still alive.
<Redacted> lobster!
--000000000000aa2c05063e4cf459--