Re: canceling/terminating statement due to conflict with recovery in Replica/DR instances

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Ishan joshi <[email protected]>
To: Imran Khan <[email protected]>
Cc: pgsql-admin <[email protected]>
Subject: Re: canceling/terminating statement due to conflict with recovery in Replica/DR instances
Date: Tue, 30 Sep 2025 15:32:27 +0000
Message-ID: <EA2PR84MB378075A1B0A9E4581822E923A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CAC4eXDhX7Zw5w8Z52USrCZFT6W8y30nZaaouwTtW0COb=cvtzQ@mail.gmail.com>
References: <EA2PR84MB3780EB1126501FD490A07807A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>
	<CAC4eXDhX7Zw5w8Z52USrCZFT6W8y30nZaaouwTtW0COb=cvtzQ@mail.gmail.com>

Hi Imran,

Thanks for your reply.

We have migrated from Oracle to Postgres these 25TB database. As the storage is huge we are not in position to create new replica instance/cluster.

Yes, I also believe the tuning the parameter is not long-term solution but we will check the impact and validate the same.

Thanks & Regards,
Ishan Joshi

________________________________
From: Imran Khan <[email protected]>
Sent: 30 September 2025 18:06
To: Ishan joshi <[email protected]>
Cc: pgsql-admin <[email protected]>
Subject: Re: canceling/terminating statement due to conflict with recovery in Replica/DR instances

Hi Isha,

 I believe you have partitions and correct type of indexes created for those tables. Also, is this 25 TB size grown over many years or just few years old? Parameters tuning can help but won't be a permanent solution. Having multiple replicas I believe can make sense at this point.

Thanks,
Imran

On Tue, Sep 30, 2025, 8:59 AM Ishan joshi <[email protected]<mailto:[email protected]>> wrote:
Hi Team,

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.

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 that below parameters can help to reduce this error.

max_standby_streaming_delay
max_standby_archive_delay
hot_standby_feedback = 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 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 scenario?

Thanks & Regards,
Ishan Joshi

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: canceling/terminating statement due to conflict with recovery in Replica/DR instances
  In-Reply-To: <EA2PR84MB378075A1B0A9E4581822E923A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox