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 <laurenz(dot)albe(at)cybertec(dot)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/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