Re: PostgreSQL 9.2 high replication lag

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Friday, August 20th, 2021 at 5:29 PM, Laurenz Albe laurenz(dot)albe(at)cybertec(dot)at wrote:

> On Fri, 2021-08-20 at 01:33 +0000, Lucas wrote:
>

> > After setting max_standby_streaming_delay to 120s it got a lot better.
> >

> > But the replication delay is still happening quite often, except this time goes up to 120s only.
>

> That's exactly what this parameter should do.
>

> If you don't want the delays, either reduce the value (and get more canceled queries)
>

> or try to reduce the number of conflicts, for example by setting "hot_standby_feedback = on".

Yes, I already have the hot_standby_feedback = on set to on on all slaves.

> Note that you will never be able to completely get rid of replication colflicts;
>

> for example, there are buffer pin conflicts or lock conflicts caused by autovacuum
>

> truncation.
>

> See this article for more:
>

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

> If you want a standby that has no apply delays and no canceled queries is usually
>

> not possible. Consider using two standby servers for these two purposes.

Thanks for sharing this. I feel relief a bit to know that replication conflicts will always "be there". Since I started this email thread, we have deployed a couple of extra slaves to share the load between them. This has helped a lot with the replication delay, but it is still there... 

I think I'll end up lowering max_standby_streaming_delay and dealing with conflits when they happen. Let me ask you; Is there a way to know what kind of conflicts are being responsible for the replication delay? How could I check this?

Thanks
Lucas