Message-ID: <e453358a9a7ae334ae6eea51d108ab4bcf1da1ca.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: Ishan joshi <ishanjoshi@live.com>, "pgsql-admin@postgresql.org"
	 <pgsql-admin@postgresql.org>
Date: Tue, 30 Sep 2025 08:16:53 +0200
In-Reply-To: 
 <EA2PR84MB3780EB1126501FD490A07807A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>
References: 
	<EA2PR84MB3780EB1126501FD490A07807A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.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/e453358a9a7ae334ae6eea51d108ab4bcf1da1ca.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-09-30 at 05:59 +0000, Ishan joshi wrote:
> We are using Postgresql 16.9 in production and with large database about =
25TB
> of size. We have patroni setup with replica instance and DR patroni setup=
 with
> patroni streaming.
>=20
> We have high volume and frequent commit in the database. There are few la=
rge
> tables for which we asked client to execute queries on DR/Replica instanc=
es but
> these queries are start getting failed with "canceling statement due to c=
onflict
> with recovery" and "terminating statement due to conflict with recovery" =
error.
>=20
> As I understand the behavior is correct but we need to get rid of this is=
sue.
>=20
> I gone through the old posts and some documentation and got to know that =
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 value t=
o 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 re=
aches
> to mentioned thresholds.
>=20
> If I set these parameters to "-1" (disable) then there will be direct imp=
act 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 scenar=
io?

No, there is no better solution.

You can reduce replication conflicts by turning on "hot_standby_feedback" a=
nd 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