public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Gram <[email protected]>
To: Laurenz Albe <[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 09:58:32 +0200
Message-ID: <CAJ=80GWtO1YVdDAHZ9i50FM6jCaB=mHOxjVJ8+vHT6mQ-QiCrg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <EA2PR84MB3780EB1126501FD490A07807A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>
<[email protected]>
Hi Laurenz
Thanks for all the answers you give on this list.
Could you elaborate on why two or more standby servers would help in this
case ?
Med venlig hilsen
Peter Gram
Sæbyholmsvej 18
2500 Valby
Mobile: (+45) 5374 7107
Email: [email protected]
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:
> > 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?
>
> No, there is no better solution.
>
> You can reduce replication conflicts by turning on "hot_standby_feedback"
> and by
> turning off "vacuum_truncate", but you probably won't be able to get rid
> of all
> replication conflicts.
>
> You can either have a small replay delay and canceled queries or no
> canceled
> queries, but the occasional replay delay.
>
> If you need both no delay and no canceled queries, the only clean solution
> is
> to have two standby servers.
>
> 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: <CAJ=80GWtO1YVdDAHZ9i50FM6jCaB=mHOxjVJ8+vHT6mQ-QiCrg@mail.gmail.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