Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p04Cu-0000n5-7J for pgsql-hackers@arkaria.postgresql.org; Tue, 29 Nov 2022 17:15:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p04Cr-0007cZ-QX for pgsql-hackers@arkaria.postgresql.org; Tue, 29 Nov 2022 17:15:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p04Cr-0007cP-A4 for pgsql-hackers@lists.postgresql.org; Tue, 29 Nov 2022 17:15:41 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p04Ck-0005bD-Nc for pgsql-hackers@lists.postgresql.org; Tue, 29 Nov 2022 17:15:40 +0000 Received: by mail-ej1-x62b.google.com with SMTP id ha10so35489132ejb.3 for ; Tue, 29 Nov 2022 09:15:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=dgzyx6jz0SL3PR+6Pz7B6m35vsKOXX3/lvlURVlUSgo=; b=Jfh6KEKJkUeFqMV1qAek+1bnix6uoHN6VttGN8qomeRVCy4tz38PnWzbzGZGw2/UIu BOpRCDllO+DEtMA3gaMh91mlrpyKyIQ5fVB4gaEzP7DVqNDfYHFi/fqI6tI7TRgtDkkn a2QAnuPNwCA0OUh4MPdGu2WCmyM9xKVVgV4C4RsqVWBBi/2LoBAuq8B3+IAhECXiFNzc xsQiKZ5RqJnCvhyuXjGdZZuym4h+OWOuUS2uC9MYJCk9Y7/t+q5sPKhMD7d9rgBq65LW Xk8RDxwlRjn6sd+v0SRSp+RCUtzVpP98AzIIjGn9D3p4UYeuwKNyL69HMSK8ORv2H6p8 Qogg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc: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=dgzyx6jz0SL3PR+6Pz7B6m35vsKOXX3/lvlURVlUSgo=; b=Gmj38bxgMJ7tqk9KgSNp4J4k/vXkXyi2HhItTjpWiCFnkCeHokv1B9+wi+OFC+yBM4 cn8spKyGjsz9J1ENMZzRPr4Oi362BRsH7k/CHnERg1FE4Kal59NGKTA6q3wXcs9Ci/1U 2Wf5lOCUHrkPW+L4q8OzOa63Y8tPJGQsdpfIWl7oPAQtcH2K9cCOU1R9qHbWRsWloxl7 QyDcNHYaFj6YQE/sv/Qts1i9zJOblXBWJSbIl0bBEBCm4yNWAUYGfLa2ViURLTezMmio UG8zf1NenT7CJr0FLysT9IsyJ0wYSNnF0vkaFfv4uEGJMEOe/D2Ma0l6/KDZm2Cbk1w9 QyoQ== X-Gm-Message-State: ANoB5pmffVvVVDW0ZovIoD+DrzH54hqCr0SerJR9l412eQTMxNZMTUYY QCBLbH04cUEQrcJabSUaz5GBeDkgQCfk+Sdci8s= X-Google-Smtp-Source: AA0mqf4VbIHQfEIK+KRagpdqRLX5CGeMoZchftnq2m1hwayBhX25bX1/3D13f0ixY7lyOdoQODR/CaNWuw7p4HvP5m4= X-Received: by 2002:a17:906:a58:b0:7ad:b45c:dbca with SMTP id x24-20020a1709060a5800b007adb45cdbcamr38007697ejf.388.1669742133305; Tue, 29 Nov 2022 09:15:33 -0800 (PST) MIME-Version: 1.0 References: <20220805.114916.994654810780821553.horikyota.ntt@gmail.com> <20220809.161236.1486509314201074910.horikyota.ntt@gmail.com> In-Reply-To: From: SATYANARAYANA NARLAPURAM Date: Tue, 29 Nov 2022 09:15:21 -0800 Message-ID: Subject: Re: An attempt to avoid locally-committed-but-not-replicated-to-standby-transactions in synchronous replication To: Bruce Momjian Cc: Andrey Borodin , Bharath Rupireddy , Kyotaro Horiguchi , Laurenz Albe , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000b8e56805ee9f2281" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b8e56805ee9f2281 Content-Type: text/plain; charset="UTF-8" On Tue, Nov 29, 2022 at 8:42 AM SATYANARAYANA NARLAPURAM < satyanarlapuram@gmail.com> wrote: > > > On Tue, Nov 29, 2022 at 8:29 AM Bruce Momjian 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. > How about allowing superusers (they can still read locally committed data) and users part of pg_monitor role? > >> >> -- >> Bruce Momjian https://momjian.us >> EDB https://enterprisedb.com >> >> Embrace your flaws. They make you human, rather than perfect, >> which you will never be. >> > --000000000000b8e56805ee9f2281 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Tue, Nov 29, 2022 at 8:42 AM SATYA= NARAYANA NARLAPURAM <satyan= arlapuram@gmail.com> wrote:


On Tue, Nov 29, 2022 at 8:29 AM Bruce Momj= ian <bruce@momjian= .us> wrote:
On Tue, Nov 29, 2022 at 08:14:10AM -0800, SATYANARAYANA NARLAPURAM wrote= :
>=C2=A0 =C2=A0 =C2=A02. Process proc die immediately when a backend is w= aiting for sync
>=C2=A0 =C2=A0 =C2=A0replication acknowledgement, as it does today, howe= ver, upon restart,
>=C2=A0 =C2=A0 =C2=A0don't open up for business (don't accept re= ady-only connections)
>=C2=A0 =C2=A0 =C2=A0unless the sync standbys have caught up.
>
>
> Are you planning to block connections or queries to the database? It w= ould 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 mo= nitoring
> hole. In cloud, I presume superusers are allowed to connect and monito= r (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 y= our
> 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= .

How about allowing supe= rusers (they can still read locally committed data) and users part of pg_mo= nitor role?




--
=C2=A0 Bruce Momjian=C2=A0 <bruce@momjian.us>=C2=A0 =C2=A0 =C2=A0 =C2=A0 https://momjian.us=
=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 https:= //enterprisedb.com

Embrace your flaws.=C2=A0 They make you human, rather than perfect,
which you will never be.
--000000000000b8e56805ee9f2281--