‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
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