From: Ishan joshi <ishanjoshi@live.com>
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: canceling/terminating statement due to conflict with recovery in
 Replica/DR instances
Thread-Topic: canceling/terminating statement due to conflict with recovery in
 Replica/DR instances
Thread-Index: AQHcMc3xjzw4H7//HE6V/BcR5HHmWw==
Date: Tue, 30 Sep 2025 05:59:39 +0000
Message-ID: 
 <EA2PR84MB3780EB1126501FD490A07807A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>
Accept-Language: en-IN, en-US
Content-Language: en-IN
msip_labels: 
x-ms-exchange-messagesentrepresentingtype: 1
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: EA2PR84MB3780:EE_|LV8PR84MB3630:EE_
x-ms-office365-filtering-correlation-id: 4cb679ff-dba0-44d9-1e98-08ddffe68ab7
x-microsoft-antispam: 
 BCL:0;ARA:14566002|15030799006|15080799012|12121999013|11091999009|8062599012|8060799015|461199028|31061999003|19110799012|51005399003|39105399003|40105399003|440099028|3412199025|102099032|18061999006;
x-microsoft-antispam-message-info: 
 =?iso-8859-1?Q?CKuM7P694mSBkxzosehYKWzJWJ5oKpjK0Xy3ZVvmDlhEpLaVeQayQx8PDF?=
 =?iso-8859-1?Q?6yrw1HNrqN7bBZAXQKEv3xbK/eTgMiGEd0vm0sp9izgw99Z9p3VWG99hAn?=
 =?iso-8859-1?Q?btiKQ58g75oEuBup6yMiySVR4Hv1cgN68XUivsr+eysf6tY8bp3BGGNAN3?=
 =?iso-8859-1?Q?IG9gX7Ikx1Ugb9oQl5RGJZ43M0xw2r4DySbFv+0laH/Wd4VHb/7C4n86jL?=
 =?iso-8859-1?Q?7oY+q0qyOv6j39Wojiu2iuIHdHTiyB/g9Eelc3JVIRtChULkRZ6LywcpTP?=
 =?iso-8859-1?Q?2n1Wou9zVcTVkiVDXsgytf62T7mX7/mePAImkOe39eBVnneRjht7MhPC2i?=
 =?iso-8859-1?Q?pJzS6bWvlZi1tPX29DsNjDFxyCteY0DoaudwGdkfp5u3Nbo58y+4rIi8yS?=
 =?iso-8859-1?Q?Z8qBHixRLgPm/X3cFVjAmKu58VrSPv6R4diC9ZBBEERqMd7XZ/u0P5xwfo?=
 =?iso-8859-1?Q?pXhQIKiAEC/MdVOMbmg+JelwgHij2OALrYgXiQWvr5+xuU1k4YxVFk7jA5?=
 =?iso-8859-1?Q?3qATXO3UxjtNyGdh6trKzVLnjxf+YUdq2byGi3oAWlSh2izwqzsSsHwq9s?=
 =?iso-8859-1?Q?rSLq2g1dXVKCUSyDyF23DZLgeMB8u2JCH9ioRXmExKp0sNPxEjAmUQVPVl?=
 =?iso-8859-1?Q?GV6sXN/NtgnkAK4J0Ldj+sSAvOrM/EFg3LnElSK96QIANOFckBivXOG7l7?=
 =?iso-8859-1?Q?Ob3QoaK6poMcVQw3DYU1pDXnH5kmyTqXSaMH2jzFTl4FqD4YGwR1N5mv7L?=
 =?iso-8859-1?Q?bRKiQ1v0yQoef78zdzLc7Zp0LwYZRcvv+5Y+GpbjPNSOA2J4UqzSFEe34a?=
 =?iso-8859-1?Q?ZZOzq9ixO+SYzvGzCTagJQmxLER1bLNSHqqjfjsbYwya8ec/QFyRubJj84?=
 =?iso-8859-1?Q?6b4ypgt2QJK46wJS8CQj0dFce4uZ4IUsXjbwFMoaD/vVNOOBj5XE9INUYQ?=
 =?iso-8859-1?Q?h4cfrl2N1Ls5qCrZ/4Igs/o/hQkKifceIb3zziQXzkYCYghhGA9wXjRRfK?=
 =?iso-8859-1?Q?FobVFylcSii9Ds4+SBnyR52A/KbWIKFrjIcrELiy4YkAkeeD8R3Jf6nIML?=
 =?iso-8859-1?Q?+NgsGEzynIQQL/JbczunQGieSXs9rNk5mlo90cnvmWYUtDJycHVbXWhH4J?=
 =?iso-8859-1?Q?OETvjgWL5qeTZHVpszorIg26HtNHczezaclMXXW5FszCMrSRpyoIoM5UIq?=
 =?iso-8859-1?Q?e+c9NCecAGl2xt9FSVury1pIM+kXQQ7R139/ANmtOB5iEAGifiujO/jTTb?=
 =?iso-8859-1?Q?NxGXEgtVj81hPcXuWNkdTHL9yiDH3JTv0tdEmFgVSV/sjqOGZPeIfPnAvE?=
 =?iso-8859-1?Q?Q+FGqy39MxkT/QxfBi67tyD1JgZzpJXu9vUEHmdzc7+yCz77vg5UEMhP1X?=
 =?iso-8859-1?Q?xkFzR8M2tFcU7afguWz/acrEIV6QDNQA=3D=3D?=
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?iso-8859-1?Q?Xg3DssJWFc0DkoNdiT0DyH0NIBnXdijzlTX+TUok0y5BMzALEyKY73RRyO?=
 =?iso-8859-1?Q?kDbhSxEFOScAD3CIg3QxRHHwJVONIakWBsiQ5AC7r+QC4nla9MZ3CAvNKl?=
 =?iso-8859-1?Q?XccrHUXYWMaFFrPsIM9O6VVtPL2aLLEwJslyCi52otOmONiubKXFIXYld5?=
 =?iso-8859-1?Q?Jia017srxyXFdqXmG6BXdmoc5DV8cnygcsbuHDGmqS8EqqiJJUIXPa9U0c?=
 =?iso-8859-1?Q?59QGXF7g0ssQP2o3jymoJA88QkgO+59cAG3OVKOkSF7/jN1CWFqUoZUHFq?=
 =?iso-8859-1?Q?wcrWmhOpaD/qa+RDMtKTegnRtrZAbqWBXGjr7BZW9mZuOU4F9Iln7rqOjO?=
 =?iso-8859-1?Q?QaHpe4ZCnctPAJ9h5tDVWVnWVahMX6ILVisxwr7QzHqcsCP+DiUKwSxi1l?=
 =?iso-8859-1?Q?zT3cscACZXkrIm3xwSas68gOnN0iqZjsWaTMHlbMMoVge4VStMz7FP5M0L?=
 =?iso-8859-1?Q?7vI5OTEjRdUAMb+3UNOH8TL5B1tzUg7iSAwAtj9P6v3cDEn4ANT0gaAPaF?=
 =?iso-8859-1?Q?m4jQ8FbbRfY+RwAqN3egIvOOX3LlPQCdSv4H5YWjokxcxoaGE3jVVl21zv?=
 =?iso-8859-1?Q?etaFL6/ObV2dLmVC/o8JugHSO/PARlmV0OK8olqOjxZmpJ59Plqbu+SspP?=
 =?iso-8859-1?Q?lU364DQBt0HhIF6CYRpVzkIUCUeyXq/Q3C+orhVwbaNS0iHD/EY2Or3T7h?=
 =?iso-8859-1?Q?3O+zTr0v4apCIkxAg+JhAdkVI/Bxv7NbwvW/e77fwGRVAmDBPfCvJ5nb+N?=
 =?iso-8859-1?Q?x0gIvDWUhY0T47apgmNYGMJO3ToB6Ss5QYlUTslWjrVNhAgwikL+IRx8DB?=
 =?iso-8859-1?Q?3DPKsGWieGYOH3/ggKIutN7xUz0tD0NV5EMkKqsjjtJq1Wt4LgH1V7zh9C?=
 =?iso-8859-1?Q?HeR+3CKseUr3SIgvubc6CuKgZ4l4AqxTYeQRgosvc7Md8iRpJTT3GskpLL?=
 =?iso-8859-1?Q?TCrEIfTTtNYfol/wimNLJfsTowTZTMy7wmy6sS6+4D7ouGu8HXa9pQ3rj/?=
 =?iso-8859-1?Q?UMLRrvTIS/j+I+J4nyMlZgb5lgQ8BhbvoCgGKGPRgQGmV6/9WnXxgvhOdc?=
 =?iso-8859-1?Q?Pc66exc9QjABVIyIFsCDAeemZCMeT6wFQejiLoLtdsyc6ik9VDRn3TLbvv?=
 =?iso-8859-1?Q?GusKEzTYfhne+Pnr9A+o/rEH9YLzrMWh5hpwSE/MhaTauKetunMI5Qvus9?=
 =?iso-8859-1?Q?lNc07DYp188VUgY2fpHhPO98veC7tVLOkCt2D1m7AhPhBDMdkqkn/7MQSf?=
 =?iso-8859-1?Q?OAaIkSZgAkMwRQ8l4jnQ=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_EA2PR84MB3780EB1126501FD490A07807A91AAEA2PR84MB3780NAMP_"
MIME-Version: 1.0
X-OriginatorOrg: sct-15-20-8534-20-msonline-outlook-774d9.templateTenant
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 4cb679ff-dba0-44d9-1e98-08ddffe68ab7
X-MS-Exchange-CrossTenant-originalarrivaltime: 30 Sep 2025 05:59:39.1035
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: LV8PR84MB3630
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/EA2PR84MB3780EB1126501FD490A07807A91AA%40EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>
Precedence: bulk

--_000_EA2PR84MB3780EB1126501FD490A07807A91AAEA2PR84MB3780NAMP_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Team,

We are using Postgresql 16.9 in production and with large database about 25=
TB of size. We have patroni setup with replica instance and DR patroni setu=
p with patroni streaming.

We have high volume and frequent commit in the database. There are few larg=
e 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 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 issu=
e.

I gone through the old posts and some documentation and got to know that be=
low 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 =
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 impac=
t 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 scenario=
?

Thanks & Regards,
Ishan Joshi

--_000_EA2PR84MB3780EB1126501FD490A07807A91AAEA2PR84MB3780NAMP_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);" class=3D"elementToProof">
Hi Team,<br>
<br>
We are using Postgresql 16.9 in production and with large database about 25=
TB of size. We have patroni setup with replica instance and DR patroni setu=
p with patroni streaming.</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);" class=3D"elementToProof">
<br>
</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);" class=3D"elementToProof">
We have high volume and frequent commit in the database. There are few larg=
e tables for which we asked client to execute queries on DR/Replica instanc=
es but these queries are start getting failed with &quot;canceling statemen=
t due to conflict with recovery&quot; and
 &quot;terminating statement due to conflict with recovery&quot; error.<br>
<br>
As I understand the behavior is correct but we need to get rid of this issu=
e.<br>
<br>
I gone through the old posts and some documentation and got to know that be=
low parameters can help to reduce this error.&nbsp;<br>
<br>
max_standby_streaming_delay&nbsp;</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);" class=3D"elementToProof">
max_standby_archive_delay&nbsp;</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);" class=3D"elementToProof">
hot_standby_feedback =3D off</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);" class=3D"elementToProof">
<br>
</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);" class=3D"elementToProof">
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 =
it will start impacting the replication lag. Also, the queries will fail if=
 it reaches to mentioned thresholds.<br>
<br>
If I set these parameters to &quot;-1&quot; (disable) then there will be di=
rect impact on replication lag which will impact further queries on replica=
 node and DR cluster.</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);" class=3D"elementToProof">
<br>
</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);" class=3D"elementToProof">
Can you please guide If any other better solution present for such scenario=
?</div>
<div id=3D"Signature">
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);">
Thanks &amp; Regards,</div>
<div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;=
 color: rgb(0, 0, 0);">
Ishan Joshi</div>
</div>
</body>
</html>

--_000_EA2PR84MB3780EB1126501FD490A07807A91AAEA2PR84MB3780NAMP_--