MIME-Version: 1.0
References: <20220805.114916.994654810780821553.horikyota.ntt@gmail.com>
 <CALj2ACWPMYoPSC3t-9uW+0gqDUcJf1mLww6hHzo2V2AvE-Tu+w@mail.gmail.com>
 <20220809.161236.1486509314201074910.horikyota.ntt@gmail.com>
 <CALj2ACXmMWtpmuT-=v8F+Lk4QCbdkeN+yHKXeRGKFfjG96YbKA@mail.gmail.com>
 <CALj2ACUO6oz-43ryqfMOVZ_Q-N10C5tkzKku12+QV02NnXsDrw@mail.gmail.com>
 <YzYh3NpCQAFkA6lF@momjian.us>
 <CAAhFRxjFGSk-hVTjnpFwm1XBUcHL8Obugt=P+ixV5AD9H+Kkrw@mail.gmail.com>
 <CAAhFRxgcBy-UCvyJ1ZZ1UKf4Owrx4J2X1F4tN_FD=fh5wZgdkw@mail.gmail.com>
 <CALj2ACVG5KCoPD_5AF2_u07HuZe4ajaLWKycB6OBYsGuj67OhA@mail.gmail.com>
 <CAHg+QDf9sMJ-r9JqFQTALRy8dX8Mr6SoFEvXx8V-Tto10VcFPA@mail.gmail.com>
 <Y4YzWeRgDYOj5Rod@momjian.us>
 <CAHg+QDcw6er6maVHq5PGR2t7NePXMWD9Vih9TRE=8Z=LDxOE9w@mail.gmail.com>
 <CAHg+QDdz+7H8wuNk9A6nBFei46BWhBq1A37_O4kNmJpuSq+dUg@mail.gmail.com>
 <CALj2ACVW1b7ue2qskO-Mef6975Mf3QZJs+47sHAgk8QB-bmDMA@mail.gmail.com>
In-Reply-To: 
 <CALj2ACVW1b7ue2qskO-Mef6975Mf3QZJs+47sHAgk8QB-bmDMA@mail.gmail.com>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Wed, 18 Mar 2026 00:28:44 -0700
Message-ID: 
 <CAHg+QDdd7BXB9HD9ddevk_D5TtweEBantcvJ5up5hznryZ33_w@mail.gmail.com>
Subject: Re: An attempt to avoid
 locally-committed-but-not-replicated-to-standby-transactions
 in synchronous replication
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>, Andrey Borodin <amborodin86@gmail.com>,
	Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 Laurenz Albe <laurenz.albe@cybertec.at>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009927dc064d476732"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDdd7BXB9HD9ddevk_D5TtweEBantcvJ5up5hznryZ33_w%40mail.gmail.com>
Precedence: bulk

--0000000000009927dc064d476732
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Reviving this thread.

On Sun, Jan 29, 2023 at 9:55=E2=80=AFPM Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com> wrote:

> For proc die, it looks like the suggestion was to process it
> immediately and upon next restart, don't allow user connections unless
> all sync standbys were caught up. However, we need to be able to allow
> replication connections from standbys so that they'll be able to
> stream the needed WAL and catch up with primary, allow superuser or
> users with pg_monitor role to connect to perform ALTER SYSTEM to
> remove the unresponsive sync standbys if any from the list or disable
> sync replication altogether or monitor for flush lsn/catch up status.
> And block all other connections. Note that replication, superuser and
> users with pg_monitor role connections are allowed only after the
> server reaches a consistent state not before that to not read any
> inconsistent data.
>

Allowing replication, superuser and pg_monitor seems reasonable to me.


>
> The trickiest part of doing the above is how we detect upon restart
> that the server received proc die while waiting for sync replication
> ACK. One idea might be to set a flag in the control file before the
> crash. Second idea might be to write a marker file (although I don't
> favor this idea); presence indicates that the server was waiting for
> sync replication ACK before the crash. However, we may not detect all
> sorts of crashes in a backend when it is waiting for sync replication
> ACK to do any of these two ideas. Therefore, this may not be a
> complete solution.
>

You cannot control the crash, it can be a simple power failure too and none
of them could have reached the disk.
Additionally, this is in a critical transaction commit path.


>
> Third idea might be to just let the primary wait for sync standbys to
> catch up upon restart irrespective of whether it was crashed or not
> while waiting for sync replication ACK. While this idea works well
> without having to detect all sorts of crashes, the primary may not
> come up if any unresponsive standbys are present (currently, the
> primary continues to be operational for read-only queries at least
> irrespective of whether sync standbys have caught up or not).
>

I prefer this approach because depending on the quorum policy defined in
the synchrnous_standby_names, the primary will open connections for
read/writes.
If there is no progress from sync standbys then Postgres admin has to jump
in regardless.

Thanks,
Satya

--0000000000009927dc064d476732
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Reviving this thread.</div><br><div class=3D"gmail_qu=
ote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Ja=
n 29, 2023 at 9:55=E2=80=AFPM Bharath Rupireddy &lt;<a href=3D"mailto:bhara=
th.rupireddyforpostgres@gmail.com">bharath.rupireddyforpostgres@gmail.com</=
a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Fo=
r proc die, it looks like the suggestion was to process it<br>
immediately and upon next restart, don&#39;t allow user connections unless<=
br>
all sync standbys were caught up. However, we need to be able to allow<br>
replication connections from standbys so that they&#39;ll be able to<br>
stream the needed WAL and catch up with primary, allow superuser or<br>
users with pg_monitor role to connect to perform ALTER SYSTEM to<br>
remove the unresponsive sync standbys if any from the list or disable<br>
sync replication altogether or monitor for flush lsn/catch up status.<br>
And block all other connections. Note that replication, superuser and<br>
users with pg_monitor role connections are allowed only after the<br>
server reaches a consistent state not before that to not read any<br>
inconsistent data.<br></blockquote><div><br></div><div>Allowing replication=
, superuser and pg_monitor seems reasonable to me.</div><div>=C2=A0</div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex">
<br>
The trickiest part of doing the above is how we detect upon restart<br>
that the server received proc die while waiting for sync replication<br>
ACK. One idea might be to set a flag in the control file before the<br>
crash. Second idea might be to write a marker file (although I don&#39;t<br=
>
favor this idea); presence indicates that the server was waiting for<br>
sync replication ACK before the crash. However, we may not detect all<br>
sorts of crashes in a backend when it is waiting for sync replication<br>
ACK to do any of these two ideas. Therefore, this may not be a<br>
complete solution.<br></blockquote><div><br></div><div>You cannot control t=
he crash, it can be a simple power failure too and none of=C2=A0them could =
have reached the disk.</div><div>Additionally, this is in a critical transa=
ction commit path.</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex">
<br>
Third idea might be to just let the primary wait for sync standbys to<br>
catch up upon restart irrespective of whether it was crashed or not<br>
while waiting for sync replication ACK. While this idea works well<br>
without having to detect all sorts of crashes, the primary may not<br>
come up if any unresponsive standbys are present (currently, the<br>
primary continues to be operational for read-only queries at least<br>
irrespective of whether sync standbys have caught up or not).<br></blockquo=
te><div><br></div><div>I prefer this approach because depending on the quor=
um policy defined in the synchrnous_standby_names, the primary will open co=
nnections for read/writes.</div><div>If there is no progress from sync stan=
dbys then Postgres admin has to jump in regardless.</div><div>=C2=A0</div><=
div>Thanks,</div><div>Satya</div></div></div>

--0000000000009927dc064d476732--