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 1v3cHf-000vts-HN for pgsql-admin@arkaria.postgresql.org; Tue, 30 Sep 2025 15:28:55 +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 1v3cHd-00Aly7-7T for pgsql-admin@arkaria.postgresql.org; Tue, 30 Sep 2025 15:28:53 +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 1v3cHc-00Alxu-GN for pgsql-admin@lists.postgresql.org; Tue, 30 Sep 2025 15:28:53 +0000 Received: from mail-centralusazolkn19011033.outbound.protection.outlook.com ([52.103.13.33] helo=DM5PR21CU001.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1v3cHa-000ip0-15 for pgsql-admin@postgresql.org; Tue, 30 Sep 2025 15:28:52 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=NgqtnXQKyBkp8Wy+IL6u9/iTu1GdnBExiROx1Kb6JNL8WHlbcQYvgdgYLZ9w/y3zXkStaLeewUtUEzh14a+kriU/hZVzWHstSQ4QbYl3RCLIIlh7UL8UqlPf/vUlvgR9KqwlSXwpklOuJGiQH2akbRnBh9FqcBfvcoA0DYj2dKHuF8nr7ZoSbNppz2fh5zFT2UaS9TC81bjC07rcNEIkX43Cjf7CpqkgtSxBM9F3NZ18o1r1RHUlpiykRiFVU5DQtzkR5nc9gAcMsU/F9+x72g5W1IjuiUYdV1TpQNlKenOAA1X0UYODrZ7ymTxqM7Cgv6Wz2vHogJmF84KmXIFZKw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=lmhAI1a07l4v4RKirFG87vaV910HMcxqWuJseO1jnhE=; b=n6OgGLjBTCWbq4KSdCuEh0kyDPehBHzB0+nD+RgmDQZYJpDLiZAptgk8AC3FzhMqxJI35OsaSC12z92oPPxwYS59Tf49A3+HkHMHJiaB7kWgGEn7GNrYzy9aj153m0wvKBSd1hm056u3vonmcScl9FuBEsc3709aNqvK+cuobfyt+XV1sVqRLasGiH/WuxlkZcYnPUPE4QUFdfdhkTpGb2FUoYO0jecSBjbEzN9G2L8lqbte53LpSx8ZqeIz1bQGspw/x++6J/xJCC4nbqK2ZkklRPMI9I5UxvPZMRA2yoT640vrv7lGmesPfcvf3sRwBYBhtCd+BBu6bEIgYQVvzg== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=live.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=lmhAI1a07l4v4RKirFG87vaV910HMcxqWuJseO1jnhE=; b=bMw7yLMjx8pTkWue8qOTzi8nbp5FS53JgoPJSsAICJpnHan9xKWPr0eFaIhihLcGExb6mRIldTOXbVrQOlkllnjKkdxR7huDYliGIpy6QTOYXW7cwIEd+bge9OJeOgDk/gjlnS7VVBFLL5x0F9LgMkExwlOOgpouPZ9fSweN1YRNWsZd5YxA/SK+D+bAlTQVLDnMMZdOA0BLyY5AKGYXq8z25Wlu7ZLlOGKLiV7TIAYkOUQCKrQF2/DwZgtX51YMYGEBqXEvnb1CjVdm7Ps1EIUOyMVV3roqN0CTxU/Gzn7nhlsk7Bb86Isd5b6ucDfahEX1ngKFaaBYL4zpiF2DHg== Received: from EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM (2603:10b6:303:25b::18) by SJ2PR84MB3782.NAMPRD84.PROD.OUTLOOK.COM (2603:10b6:a03:58c::16) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9160.17; Tue, 30 Sep 2025 15:28:48 +0000 Received: from EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM ([fe80::af88:ed17:72c3:3f4e]) by EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM ([fe80::af88:ed17:72c3:3f4e%5]) with mapi id 15.20.9160.017; Tue, 30 Sep 2025 15:28:48 +0000 From: Ishan joshi To: Laurenz Albe , Peter Gram CC: "pgsql-admin@postgresql.org" Subject: Re: 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/BcR5HHmW7SrQAWAgAAcZgCAAByPgIAAPlwL Date: Tue, 30 Sep 2025 15:28:48 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-IN, en-US Content-Language: en-IN X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-ms-publictraffictype: Email x-ms-traffictypediagnostic: EA2PR84MB3780:EE_|SJ2PR84MB3782:EE_ x-ms-office365-filtering-correlation-id: 0dfb7a8c-794e-4209-7698-08de00360d4d x-ms-exchange-slblob-mailprops: WaIXnCbdHrNsJbj7NxvUrpSYkT7K1zyUA0Rf4zdh1us/y/8LLtgsaqK5yaJWVS9yku8n2iUInlppO44puxNFASnBdUmHBDGYXrV+qICTKF29SCc6XxpjkK605fU+JIpoXMeyj78nvbt+HBzI6AZ259xagftgOQepBpo1AYr5fQdnR8mIj46yNt4QVdHpFcNmb7DyMGhtU8thGFC54mS2mOM3wfNdKgXyWQ2SIHdtR5SKTzD5IT13t1ZQaKNRpzQZj6HGS54ZryfyVyaxWncIznfh4XlSedljouAwhz6pF6Cqqm48LcsvVw8S925nv+OLuMHo/P/ZQSLtfoYReJMZO5a4GAsrfE6a/7uqnnOFL5AAVBVXQ3aibVg4eRV7GFmcK6rIqZBjCGwLvhwy4eIg3Z4B+0yneTpu7ezGpDfloIaWUPEkBLJ5HtM6m/mHg/PIjBDHLBvnBopTZ7D60EJy3xrGORmhMGIY015UYQcK16esnV0/IHUdIFxP6AQ3MxZim+TUJprFbBebTUCvRFzm0wTCun+vfoUu0s/r/cVwBnSuobykQTxe99S6mwg36gi0ERwg9QJ5ZQDiy2moLInOi1doeoTbA6w3C37/uWwMnmNvIVoUKw9mHrnJv0Vqhu7ijxbGlg9vn37dXgpsA5IEf0N6tKnV4Nn/G093QBaYPdl5OjYZbqKusu5H4Z112TOGNnCZXX7bensDFmHgl7mZB1hsjrvlt9kKf+8WR43F+1i+siG5BmbK3d/yoZLmvYtpZOLV6sl9AuM= x-microsoft-antispam: BCL:0;ARA:14566002|19110799012|461199028|31061999003|8060799015|8062599012|15080799012|15030799006|12121999013|11091999009|440099028|40105399003|3412199025|18061999006|102099032; x-microsoft-antispam-message-info: =?iso-8859-1?Q?GlHm99li4BYBmSAABqK4AYUuD/lNvoccvE0c45+uIUE/T+EZqx7EGb6hAU?= =?iso-8859-1?Q?E2bbTjyKTh8bBQheVH8cro8Y0Od+vDSFlHHWL02ZGvenmilyn+uRdOEtAI?= =?iso-8859-1?Q?Yq58IenyI5LPLhwgjDCgAP+OiVoO8W9yE2qjrbfhUNuhsbz+zEQn4Mt4pd?= =?iso-8859-1?Q?ErTeNw9uMZH/4TLX6+neAOYP3P41Ht1hQPqiHjIr6NaBCw85f6EypnsSAV?= =?iso-8859-1?Q?+QfJZqJEn57qM/xBvUxFNUecJ/U83DssnkalHN1hxJVTY7TzWVygBO1zgo?= =?iso-8859-1?Q?OxTx9VzQqcXLFx1DAKkKd4gKGZsLlWlLeWm79hfhgl9v7AwQsqHQomELOW?= =?iso-8859-1?Q?gb+t6qYu3jUMKA8a997DKS7mfoZelQHOgO/sDi2x7uUC9oNGJKAiywG5q+?= =?iso-8859-1?Q?WvKBOpK2Aa/hgTTEQMuXDvCNL6iFJwV/GkYDqkAfEhlA5HiXQhvY3fxyAe?= =?iso-8859-1?Q?FQFF7wDn6sTBc8LovYdsh2bJ+/taOvmbNiio1ltpAnAztxkzqr1Ku83k2A?= =?iso-8859-1?Q?6IAnsKuPmRBKQJzUU3+CLLHsFasilXsxWa3fcjJti4dMVZdADtzDdwGY12?= =?iso-8859-1?Q?s5ZT51sjp6cPkOfKYq1G/bNVz1JLXrcpm1AkY8M7lDJ7s2pyqwxXp5y4pq?= =?iso-8859-1?Q?2ALWDqigdvuoW8f9xctgvH5IWAlkP0FZE0zZ1Q+oUiyi9SzRFKiD4b+Igz?= =?iso-8859-1?Q?07krZJldVvlfOCmCB8aJWknxEBh/nws4BjHhbAnQuo1txKWZHge2TT1b0Y?= =?iso-8859-1?Q?xC0iHZsQP20VdLkPL6EhgDp6Q363ZkWBfAKpKAHjXF/yOL8fQLHjAJuu5K?= =?iso-8859-1?Q?S4YUN/s3WjAjgrd+jCxyWoTsbwwOUOeHuo7/Fvki3tX0uFLf+kX5OlBrYV?= =?iso-8859-1?Q?nXD2OFfWiRWNSpv6vktdzB9HV9/vERkHdaIyrmNTTE3fTmjs8TuMReLBC4?= =?iso-8859-1?Q?ECP1KrOY/l/Eg1upasYdPR4PQcmGWPD+Fqt62dAZthY8deQOWKOyyiz7S0?= =?iso-8859-1?Q?W9GX4Hvxe3JxGM1ESkA6+86vITzQkEFpjE4S5cqrpqeSfM0M+favexv1Ca?= =?iso-8859-1?Q?GQuQOYzKFl6dSdY3RDg9qcdYrlS+unnrsC2NgbBms3n3VsG1qheREA3pzV?= =?iso-8859-1?Q?/Inw6EWCDshha2MaidTJzKMWYyfBjVIZ3xhU61Eo0JC4KKZrANpAAgVUz6?= =?iso-8859-1?Q?oCpd+lcwK6RgKjvGGIklo7c6etZUtXjsaYTn8Btjjp0Kq9PoOpb6/mJneu?= =?iso-8859-1?Q?mOcobbyctSgd5uXYiQAg+RQgpxwMonPz/0Q/C3sKrOitgdQ19N6OrkkBWF?= =?iso-8859-1?Q?gh/h8Wury3ePa25gRBmFJP3LAg=3D=3D?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?Km5DQVGiKtIgnlbY2R9SeOoJ9tt5SmBEXClLT2BhuSlXheT5HtidQ6m7Q7?= =?iso-8859-1?Q?MNylI7zXhGJb+zg5jspviB77urWJOfLtzHY2U3YcbTjnllD54OU8WmKxHu?= =?iso-8859-1?Q?L0z8J+GXhqly/qRGZBXfPQZF6dBrJ2xx8DwjSibjcdlwXeG6UGUfWRPHxM?= =?iso-8859-1?Q?OtbgpEjj/Z+4qoFbHlKMYd+anfuglCp7YLS1OcgfuNMl7vsK+KxI1XzBry?= =?iso-8859-1?Q?ecq/LJH6CA7uBhZ1WOzTgl2NzG9VOtwM/B6j2gi85Ijn2UdiB9rxKeuCoy?= =?iso-8859-1?Q?ndgiLSlExoFPShEJjJxB+9fX9SaU1+6KBIcihe0yqjBpcA8ON7ER++fd+F?= =?iso-8859-1?Q?B15VLb8RgLouZ+i+vm5aW3/8Jg1xIUqWllPzvjKMsNS96mELzuhEwpwv1y?= =?iso-8859-1?Q?ELrrJ8KpWwhen5ffiol65LxC98UxYbxUdt76hLO53lskTjh0kztj6mGkIG?= =?iso-8859-1?Q?Hz3taVZfWRkjGTAvW47esLizEGc3uIih2ML6lWJ43lPEnKsbRn7eFJN4D9?= =?iso-8859-1?Q?pwiZjh0rR4Q+7BZyLoq0793j2YN7+2jXPpIyN5kKNWlZmrrcpCvvEiCKQc?= =?iso-8859-1?Q?xL8qAA0UlIlZVUS6Z1Q2EVxj0M1o4tkFOwvxSM/3SKVLCaJrvbShLOyGKt?= =?iso-8859-1?Q?0mjlALDptyZibLG8qqhb1Dz4q4Caad9gRXPJIQS9lD1/tUa4pZ+CxUAavI?= =?iso-8859-1?Q?a2n0bs3gb3n/t0ArRG301xPPuv/vqgKMqkwxoZEtfilsi2B8gFoS4H6QvX?= =?iso-8859-1?Q?n36Nk+B8GNQPA172CPMrpnDOnnFuxefIb4O5//Okk11i9JkVEr+rX5jjG/?= =?iso-8859-1?Q?ol/BUQj9HJMbPo+GozuIijPoCgHMzAmLTcgjWMJSgEAW2pF+eOC9TWvZJm?= =?iso-8859-1?Q?s3cEVbACPCzN5XTGChgts6dakueciHxLO/uTrUPYoBaAIbPDZ4tzgRz3cn?= =?iso-8859-1?Q?QvADwIAXLoGVNgDxNciXrdj81ebagrMAWYwjKZWi9dhfgbWMxilcwFinfA?= =?iso-8859-1?Q?yMYCZYpTvU0RG7qwlvcrbk7axLNF4+AH17afApT+Of86MEKChHSjvs6aSt?= =?iso-8859-1?Q?BEOyMTFPBzvGWoJxD1XHubhJ6bTjKI9F7y0j32lU6rVnDje7jbaTVX4n08?= =?iso-8859-1?Q?/CBg+iXSphIZkWggFB54HXVL6O1CGsb5+CudfvBFST2bQq20ykB0lRC0xh?= =?iso-8859-1?Q?4LMzKCzsbg9j48slqV5NpnfPoT1gLaYxiLKGme+S4WNJCooZ9uMfp7wOyh?= =?iso-8859-1?Q?g0oThqJ1gedQTIjrmjow=3D=3D?= Content-Type: multipart/alternative; boundary="_000_EA2PR84MB37803B77B9B17636B4560AAFA91AAEA2PR84MB3780NAMP_" 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: 0dfb7a8c-794e-4209-7698-08de00360d4d X-MS-Exchange-CrossTenant-originalarrivaltime: 30 Sep 2025 15:28:48.4455 (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: SJ2PR84MB3782 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_EA2PR84MB37803B77B9B17636B4560AAFA91AAEA2PR84MB3780NAMP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Laurenz, Thanks, for your explanations. It makes sense for having another replica i= nstance but in our case, it is not possible to have another replica instanc= e with huge database size. We will see the impact with delaying the reply lag and act accordingly. Thanks & Regards, Ishan Joshi ________________________________ From: Laurenz Albe Sent: 30 September 2025 15:10 To: Peter Gram Cc: Ishan joshi ; pgsql-admin@postgresql.org Subject: Re: canceling/terminating statement due to conflict with recovery = in Replica/DR instances On Tue, 2025-09-30 at 09:58 +0200, Peter Gram wrote: > On Tue, 30 Sept 2025 at 08:17, Laurenz Albe 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. > > > > > > As I understand the behavior is correct but we need to get rid of thi= s issue. > > > > > > I gone through the old posts and some documentation and got to know t= hat 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 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. > > > > > > 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 sc= enario? > > > > No, there is no better solution. > > > > 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 ? 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 --_000_EA2PR84MB37803B77B9B17636B4560AAFA91AAEA2PR84MB3780NAMP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi Laurenz,

Thanks,  for your explanations. It makes sense for having another repl= ica instance but in our case, it is not possible to have another replica in= stance with huge database size.

We will see the impact with delaying the reply lag and act accordingly.


Thanks & Regards,
Ishan Joshi

From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: 30 September 2025 15:10
To: Peter Gram <peter.m.gram@gmail.com>
Cc: Ishan joshi <ishanjoshi@live.com>; pgsql-admin@postgr= esql.org <pgsql-admin@postgresql.org>
Subject: Re: canceling/terminating statement due to conflict wi= th recovery in Replica/DR instances
 
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> wrote:
> > 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/Re= plica instances but
> > > these queries are start getting failed with "canceling = statement due to conflict
> > > with recovery" and "terminating statement due to c= onflict with recovery" error.
> > >
> > > As I understand the behavior is correct but we need to get r= id of this issue.
> > >
> > > I gone through the old posts and some documentation and got = to know that 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 prod= uction 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 t= here 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 fo= r such scenario?
> >
> > No, there is no better solution.
> >
> > If you need both no delay and no canceled queries, the only clean= solution is
> > to have two standby servers.
>
> Could you elaborate on why two or more standby servers would help in t= his case ?

One of the standby servers would have "max_standby_streaming_delay =3D= 0" or
"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
--_000_EA2PR84MB37803B77B9B17636B4560AAFA91AAEA2PR84MB3780NAMP_--