Message-ID: <b96e6c26f094ee6d1c7859c4180b225b565f34e6.camel@cybertec.at>
Subject: Re: canceling/terminating statement due to conflict with recovery
 in Replica/DR instances
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Peter Gram <peter.m.gram@gmail.com>
Cc: Ishan joshi <ishanjoshi@live.com>, "pgsql-admin@postgresql.org"
	 <pgsql-admin@postgresql.org>
Date: Tue, 30 Sep 2025 11:40:45 +0200
In-Reply-To: 
 <CAJ=80GWtO1YVdDAHZ9i50FM6jCaB=mHOxjVJ8+vHT6mQ-QiCrg@mail.gmail.com>
References: 
	<EA2PR84MB3780EB1126501FD490A07807A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>
	 <e453358a9a7ae334ae6eea51d108ab4bcf1da1ca.camel@cybertec.at>
	 <CAJ=80GWtO1YVdDAHZ9i50FM6jCaB=mHOxjVJ8+vHT6mQ-QiCrg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/b96e6c26f094ee6d1c7859c4180b225b565f34e6.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-09-30 at 09:58 +0200, Peter Gram wrote:
> On Tue, 30 Sept 2025 at 08:17, Laurenz Albe <laurenz.albe@cybertec.at> wr=
ote:
> > On Tue, 2025-09-30 at 05:59 +0000, Ishan joshi wrote:
> > > There are few large
> > > tables for which we asked client to execute queries on DR/Replica ins=
tances but
> > > these queries are start getting failed with "canceling statement due =
to conflict
> > > with recovery" and "terminating statement due to conflict with recove=
ry" error.
> > >=20
> > > As I understand the behavior is correct but we need to get rid of thi=
s issue.
> > >=20
> > > I gone through the old posts and some documentation and got to know t=
hat below
> > > parameters can help to reduce this error.=C2=A0
> > >=20
> > > max_standby_streaming_delay=C2=A0
> > > max_standby_archive_delay=C2=A0
> > > hot_standby_feedback =3D off
> > >=20
> > > Our queries are running for long period that makes me to set this val=
ue 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 i=
t reaches
> > > to mentioned thresholds.
> > >=20
> > > 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.
> > >=20
> > > Can you please guide If any other better solution present for such sc=
enario?
> >=20
> > No, there is no better solution.
> >=20
> > If you need both no delay and no canceled queries, the only clean solut=
ion is
> > to have two standby servers.
>
> Could you elaborate on why two or more standby servers would help in this=
 case ?=20

One of the standby servers would have "max_standby_streaming_delay =3D 0" o=
r
"hot_standby =3D off", that one would be for high availability.

The other one would have "max_standby_streaming_delay =3D -1" and would be =
used for
queries.

Yours,
Laurenz Albe