Обсуждение: Queries in replica are failing

Hi,

I would probably use "hot_standby_feedback" rather than change the delay parameters, unless you want to have the read replica (Standby) actually to operate some time behind the primary, for some reason (like having a copy of the data an hour old to fix mistakes on the primary).

https://postgresqlco.nf/doc/en/param/hot_standby_feedback/

The hot_standby_feedback parameter sends feedback to the primary, so the transaction is less likely to be cancelled.  The only draw back is that is can cause some bloat on the primary database.

Regards,

Matt

Hello -

Queries in replica instance are failing with the error "cancelling statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay)  which may allow the queries to run within the time defined in those.

Is it recommended to set those? Is there any other suggestion to tackle this?

Regards

Siraj

--

On Thu, 2024-05-16 at 17:31 +0530, Siraj G wrote:
> Queries in replica instance are failing with the error "cancelling statement due
> to conflict with recovery". I was checking two parameters (max_standby_archive_delay
> and max_standby_streaming_delay)  which may allow the queries to run within the
> time defined in those.
>
> Is it recommended to set those? Is there any other suggestion to tackle this?

The only safe way to avoid this error is to set "max_standby_streaming_delay" to -1.

See https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/

Yours,
Laurenz Albe

On Wed, 2024-05-29 at 22:34 +0530, ROHIT SACHDEVA wrote:
> I received this error when i set  max_standby_streaming_delay to -1 and max_standby_archive_delay to 30 seconds.
>
> The following processes were terminated on your read replica because they were interfering with the replay of
transactionlogs: [18218]. 

Ah, then you are recovering from an archive.

In that case, set "max_standby_archive_delay" to -1 as well.

Yours,
Laurenz Albe

On Thu, 2024-05-30 at 06:59 +0530, ROHIT SACHDEVA wrote:
> On Thu, 30 May, 2024, 2:47 am Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > On Wed, 2024-05-29 at 22:34 +0530, ROHIT SACHDEVA wrote:
> > > I received this error when i set  max_standby_streaming_delay to -1 and max_standby_archive_delay to 30 seconds.
> > >
> > > The following processes were terminated on your read replica because they were interfering with the replay of
transactionlogs: [18218]. 
> >
> > Ah, then you are recovering from an archive.
> >
> > In that case, set "max_standby_archive_delay" to -1 as well.
>
> If I set both to -1 storage space is increasing rapidly.
>
> Any way out as they are okay with 1 day delay but queries should run smoothly.
>
> Can I pause or schedule the replication process replication at 12 am in Postgres RDS.

Sure, WAL will pile up on the standby if it cannot be replayed right away.

You could stop replication, but then WAL would pile up on the primary server.

You have to keep the information somewhere.

Yours,
Laurenz Albe

On Fri, 2024-05-31 at 23:37 +0530, ROHIT SACHDEVA wrote:
> On Thu, May 30, 2024 at 11:37 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Sure, WAL will pile up on the standby if it cannot be replayed right away.
> >
> > You could stop replication, but then WAL would pile up on the primary server.
> >
> > You have to keep the information somewhere.
>
> So What will be the work around for this.

Get more disk space, or perform less work on the database.

Yours,
Laurenz Albe