Re: PostgreSQL 9.2 high replication lag

[Lucas <root@xxxxxxx>]

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

On Friday, August 20th, 2021 at 5:29 PM, Laurenz Albe laurenz.albe@xxxxxxxxxxx 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

Attachment: publickey - root@sud0.nz - 0xC5E964A1.asc
Description: application/pgp-keys

Attachment: signature.asc
Description: OpenPGP digital signature