public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Peter Gram <[email protected]>
Cc: Ishan joshi <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: canceling/terminating statement due to conflict with recovery in Replica/DR instances
Date: Tue, 30 Sep 2025 11:40:45 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAJ=80GWtO1YVdDAHZ9i50FM6jCaB=mHOxjVJ8+vHT6mQ-QiCrg@mail.gmail.com>
References: <EA2PR84MB3780EB1126501FD490A07807A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>
	<[email protected]>
	<CAJ=80GWtO1YVdDAHZ9i50FM6jCaB=mHOxjVJ8+vHT6mQ-QiCrg@mail.gmail.com>

On Tue, 2025-09-30 at 09:58 +0200, Peter Gram wrote:
> On Tue, 30 Sept 2025 at 08:17, Laurenz Albe <[email protected]> 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/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?
> > 
> > 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 this case ? 

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

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

Yours,
Laurenz Albe

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], [email protected]
  Subject: Re: canceling/terminating statement due to conflict with recovery in Replica/DR instances
  In-Reply-To: <[email protected]>

* 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