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>
In-Reply-To: <Y4YzWeRgDYOj5Rod@momjian.us>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Tue, 29 Nov 2022 08:42:01 -0800
Message-ID: 
 <CAHg+QDcw6er6maVHq5PGR2t7NePXMWD9Vih9TRE=8Z=LDxOE9w@mail.gmail.com>
Subject: Re: An attempt to avoid
 locally-committed-but-not-replicated-to-standby-transactions
 in synchronous replication
To: Bruce Momjian <bruce@momjian.us>
Cc: Andrey Borodin <amborodin86@gmail.com>,
	Bharath Rupireddy <bharath.rupireddyforpostgres@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="0000000000007dfba505ee9eab09"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDcw6er6maVHq5PGR2t7NePXMWD9Vih9TRE%3D8Z%3DLDxOE9w%40mail.gmail.com>
Precedence: bulk

--0000000000007dfba505ee9eab09
Content-Type: text/plain; charset="UTF-8"

On Tue, Nov 29, 2022 at 8:29 AM Bruce Momjian <bruce@momjian.us> wrote:

> On Tue, Nov 29, 2022 at 08:14:10AM -0800, SATYANARAYANA NARLAPURAM wrote:
> >     2. Process proc die immediately when a backend is waiting for sync
> >     replication acknowledgement, as it does today, however, upon restart,
> >     don't open up for business (don't accept ready-only connections)
> >     unless the sync standbys have caught up.
> >
> >
> > Are you planning to block connections or queries to the database? It
> would be
> > good to allow connections and let them query the monitoring views but
> block the
> > queries until sync standby have caught up. Otherwise, this leaves a
> monitoring
> > hole. In cloud, I presume superusers are allowed to connect and monitor
> (end
> > customers are not the role members and can't query the data). The same
> can't be
> > true for all the installations. Could you please add more details on your
> > approach?
>
> I think ALTER SYSTEM should be allowed, particularly so you can modify
> synchronous_standby_names, no?


Yes, Change in synchronous_standby_names is expected in this situation.
IMHO, blocking all the connections is not a recommended approach.


>
> --
>   Bruce Momjian  <bruce@momjian.us>        https://momjian.us
>   EDB                                      https://enterprisedb.com
>
> Embrace your flaws.  They make you human, rather than perfect,
> which you will never be.
>

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

<div><br></div><div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=
=3D"gmail_attr">On Tue, Nov 29, 2022 at 8:29 AM Bruce Momjian &lt;<a href=
=3D"mailto:bruce@momjian.us">bruce@momjian.us</a>&gt; wrote:<br></div><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #cc=
c solid;padding-left:1ex">On Tue, Nov 29, 2022 at 08:14:10AM -0800, SATYANA=
RAYANA NARLAPURAM wrote:<br>
&gt;=C2=A0 =C2=A0 =C2=A02. Process proc die immediately when a backend is w=
aiting for sync<br>
&gt;=C2=A0 =C2=A0 =C2=A0replication acknowledgement, as it does today, howe=
ver, upon restart,<br>
&gt;=C2=A0 =C2=A0 =C2=A0don&#39;t open up for business (don&#39;t accept re=
ady-only connections)<br>
&gt;=C2=A0 =C2=A0 =C2=A0unless the sync standbys have caught up.<br>
&gt; <br>
&gt; <br>
&gt; Are you planning to block connections or queries to the database? It w=
ould be<br>
&gt; good to allow connections and let them query the monitoring views but =
block the<br>
&gt; queries until sync standby have caught up. Otherwise, this leaves a mo=
nitoring<br>
&gt; hole. In cloud, I presume superusers are allowed to connect and monito=
r (end<br>
&gt; customers are not the role members and can&#39;t query the data). The =
same can&#39;t be<br>
&gt; true for all the installations. Could you please add more details on y=
our<br>
&gt; approach?<br>
<br>
I think ALTER SYSTEM should be allowed, particularly so you can modify<br>
synchronous_standby_names, no?</blockquote><div dir=3D"auto"><br></div><div=
 dir=3D"auto">Yes, Change in synchronous_standby_names is expected in this =
situation. IMHO, blocking all the connections is not a recommended approach=
.</div><div dir=3D"auto"><br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex" dir=3D"a=
uto"><br>
<br>
-- <br>
=C2=A0 Bruce Momjian=C2=A0 &lt;<a href=3D"mailto:bruce@momjian.us" target=
=3D"_blank">bruce@momjian.us</a>&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 <a href=3D"=
https://momjian.us" rel=3D"noreferrer" target=3D"_blank">https://momjian.us=
</a><br>
=C2=A0 EDB=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <a hr=
ef=3D"https://enterprisedb.com" rel=3D"noreferrer" target=3D"_blank">https:=
//enterprisedb.com</a><br>
<br>
Embrace your flaws.=C2=A0 They make you human, rather than perfect,<br>
which you will never be.<br>
</blockquote></div></div>

--0000000000007dfba505ee9eab09--