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 1v3VGL-00HE5w-4F for pgsql-admin@arkaria.postgresql.org; Tue, 30 Sep 2025 07:59:05 +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 1v3VGH-007LMe-6Z for pgsql-admin@arkaria.postgresql.org; Tue, 30 Sep 2025 07:59:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v3VGG-007LMV-NK for pgsql-admin@lists.postgresql.org; Tue, 30 Sep 2025 07:59:01 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v3VGE-000eiP-28 for pgsql-admin@postgresql.org; Tue, 30 Sep 2025 07:59:00 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-71d60501806so62115697b3.2 for ; Tue, 30 Sep 2025 00:58:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1759219138; x=1759823938; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=OmWYjKVnK1luCqFL1uBKc0VE3SNYXrOM8TE9yrsOw/o=; b=c2Pwb8trjvCEsF4J0NQZjUCrBgz9qgSkhxdMmO64hRHQFT/6ZqD5Mhnx/lxQBV9e54 2AXmB8aGl6b3yzsYgVWu7TOYLpG4cBIVqIyaEeQcBASUBwtgysH1HCIcAX8hE1xBuJCr N201D8JWxkN22kZPfRMM3GnniNVihuehTpGKF6gZNBoyE8DjF03XK7dbFshAHym/lKyG q9y+8+CgeFU+vWMAbTFqGUcq58YBO7A4iKXGNNOPU3X84PpPw3PeB1Mr5HjI/Hwldnyw cHxS832eOzm5zoxhFYf0FNRAr/hvldZT7ImgZd6WNlc5A+SqGSphTXdLdh3ZZNGxYz8h hcmw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1759219138; x=1759823938; 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=OmWYjKVnK1luCqFL1uBKc0VE3SNYXrOM8TE9yrsOw/o=; b=JfNKpwJM02nC6udW3R/v52TQEP6UYZOZT78Jl7LrYlyVBeZetpVLAmOtgxB9fA2+CH QcyEGBI4we9buGLhbSLmqbP8GV15SkXOEVOoKPNWbc5Sunr1n63G4VFvQQ6vdLh5oGRE 5m8ltA/DKyzOhpZ1tmBiZIFIZCzJ4B6L8dGhvq/M+NJzQxxLhabM7xtM+xoLIRoIIviO PQKEM+trsDKkjGSARbmMXoHjq4Li5JD59v6Z4fkvVn1ibkglxHLrzLHf23wYGUbi7ZHE CdB2iM5wDcTWTJOOt1PbJEwVjv1ED8fTRtuKW1VqnrgirBoirtU5ESffOfT+296TQ15K /y8A== X-Forwarded-Encrypted: i=1; AJvYcCWSJk0W+vf3wqbK3gVhgF/xqoeeb3IKJU86LIx+5eTeVqeclQHEt3FpaPJhYRd6VpGo3rGHL2TJzKVpZA==@postgresql.org X-Gm-Message-State: AOJu0YyUgQ0WQbxJeeTcjxZCXte5jRo/OUmqFTYOfu+ESnwPC/MPybwf ci/I29iM/ug6Kf/7Qy1CFwMw1WaMPXZxOGrnBl8Iwtls0r4ym5DJdDuZPqzMpeiM8RnLzMREnbQ solESDhZfG2ELwQpKg7o9jLJI6KvZfeM= X-Gm-Gg: ASbGnctvyD+ZVGlq0f5mWbokawdtrsQ7v5Y36Aj9fOUfaO/H4iN2zx4im6r/fLR931Y xU6Yv3Cev8gWrW90C7wRDgYEMer/CasNAmsgMgU9XX5+mzbOaPSMr97Fk/P5bgXb1JtSPim1Iin 5m/4Wiyefe3JBp7mpYBaKe2Qwkg++eZI2Hv3t36/TuNFXVZ0Mu963n3NW3REuiBw7uEz9aJmw1U h2ZGTQ8tBid154QQGtKaPrkVrRd9Saxi92XR3B0Lo0YVgipRj/A X-Google-Smtp-Source: AGHT+IEmeeeaGkFOWxNG72HnTQO+LXMdW8IuScyglDofT5oqWxOjK5IO+vTU3geLTk/kYiQ6q69xcHJsL6s6NXeUOvg= X-Received: by 2002:a53:d01a:0:b0:635:4ecd:5fca with SMTP id 956f58d0204a3-6361a887144mr17199009d50.39.1759219138268; Tue, 30 Sep 2025 00:58:58 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Peter Gram Date: Tue, 30 Sep 2025 09:58:32 +0200 X-Gm-Features: AS18NWAkaoLlprWBD2HqOqAKkFtBzxYiuEoLzeQHib4-mgoTzEV0qrlOOYvcixM Message-ID: Subject: Re: canceling/terminating statement due to conflict with recovery in Replica/DR instances To: Laurenz Albe Cc: Ishan joshi , "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary="000000000000d1953a0640001f1f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d1953a0640001f1f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Laurenz Thanks for all the answers you give on this list. Could you elaborate on why two or more standby servers would help in this case ? Med venlig hilsen Peter Gram S=C3=A6byholmsvej 18 2500 Valby Mobile: (+45) 5374 7107 Email: peter.m.gram@gmail.com On Tue, 30 Sept 2025 at 08:17, Laurenz Albe wrote: > On Tue, 2025-09-30 at 05:59 +0000, Ishan joshi wrote: > > We are using Postgresql 16.9 in production and with large database abou= t > 25TB > > of size. We have patroni setup with replica instance and DR patroni > setup with > > patroni streaming. > > > > We have high volume and frequent commit in the database. There are few > large > > tables for which we asked client to execute queries on DR/Replica > instances but > > these queries are start getting failed with "canceling statement due to > conflict > > with recovery" and "terminating statement due to conflict with recovery= " > error. > > > > As I understand the behavior is correct but we need to get rid of this > issue. > > > > I gone through the old posts and some documentation and got to know tha= t > below > > parameters can help to reduce this error. > > > > max_standby_streaming_delay > > max_standby_archive_delay > > hot_standby_feedback =3D off > > > > Our queries are running for long period that makes me to set this value > to some > > minutes/hours (lets set 900s) which is not feasible for production as i= t > will > > start impacting the replication lag. Also, the queries will fail if it > reaches > > to mentioned thresholds. > > > > If I set these parameters to "-1" (disable) then there will be direct > impact on > > replication lag which will impact further queries on replica node and D= R > cluster. > > > > Can you please guide If any other better solution present for such > scenario? > > No, there is no better solution. > > You can reduce replication conflicts by turning on "hot_standby_feedback" > and by > turning off "vacuum_truncate", but you probably won't be able to get rid > of all > replication conflicts. > > You can either have a small replay delay and canceled queries or no > canceled > queries, but the occasional replay delay. > > If you need both no delay and no canceled queries, the only clean solutio= n > is > to have two standby servers. > > Yours, > Laurenz Albe > > > --000000000000d1953a0640001f1f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Laurenz

Thanks for all the answers you give on this l= ist.
Could you elaborate on why two or more standby= servers would help in this case ?=C2=A0

=
Med v= enlig hilsen

Peter Gram
S=C3=A6byholmsvej 18=C2=A0
2500= Valby

Mobile: (= +45) 5374 7107

<= /div>


On Tue, 30 Sept 2025 at 08:17= , Laurenz Albe <laurenz.albe= @cybertec.at> wrote:
On Tue, 2025-09-30 at 05:59 +0000, Ishan joshi wrote:
> We are using Postgresql 16.9 in production and with large database abo= ut 25TB
> of size. We have patroni setup with replica instance and DR patroni se= tup with
> patroni streaming.
>
> We have high volume and frequent commit in the database. There are few= large
> tables for which we asked client to execute queries on DR/Replica inst= ances but
> these queries are start getting failed with "canceling statement = due to conflict
> with recovery" and "terminating statement due to conflict wi= th recovery" error.
>
> As I understand the behavior is correct but we need to get rid of this= issue.
>
> I gone through the old posts and some documentation and got to know th= at below
> parameters can help to reduce this error.=C2=A0
>
> max_standby_streaming_delay=C2=A0
> max_standby_archive_delay=C2=A0
> hot_standby_feedback =3D off
>
> Our queries are running for long period that makes me to set this valu= e to some
> minutes/hours (lets set 900s) which is not feasible for production as = it will
> start impacting the replication lag. Also, the queries will fail if it= reaches
> to mentioned thresholds.
>
> If I set these parameters to "-1" (disable) then there will = be direct impact on
> replication lag which will impact further queries on replica node and = DR cluster.
>
> Can you please guide If any other better solution present for such sce= nario?

No, there is no better solution.

You can reduce replication conflicts by turning on "hot_standby_feedba= ck" and by
turning off "vacuum_truncate", but you probably won't be able= to get rid of all
replication conflicts.

You can either have a small replay delay and canceled queries or no cancele= d
queries, but the occasional replay delay.

If you need both no delay and no canceled queries, the only clean solution = is
to have two standby servers.

Yours,
Laurenz Albe


--000000000000d1953a0640001f1f--