Re: PostgreSQL 9.2 high replication lag

Hello guys.

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.
[image.png]

I know that max_standby_streaming_delay stands for how long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries, but this is causing some headache and I was wondering if you guys have other suggestions?

What I could do next? Do you think by upgrading PostgreSQL from 9.2 to 13 will somehow improve that?

---
Regards,

Lucas

> This message is encrypted. Both the Public Key and the GPG encrypted message are included in this email so that you can verify its origin.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Sunday, July 25th, 2021 at 8:33 PM, Lucas <root(at)sud0(dot)nz> wrote:

> > Version 9.2 is very old and has a lot of issues related to streaming replication. Additionally, it is already EOL.
>

> Agreed.
>

> > Furthermore, max_standby_streaming_delay has no relation with the streaming lag. It's associated with queries conflicting with data. This parameter will not speed up the WAL apply operation.
>

> Yes, I know. But this parameter determines how long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries. So, there could be lots of queries conflicting with about-to-be-applied WAL entries.
> Again, I was not sure about what is causing the delay so I needed to try any possibility.
>

> > Kindly consider upgrading to a supported version.
>

> Yes. We have a project in progress to upgrade.
>

> On top of that, I can say that I have restarted the slave and the replication is now normal.
> [image.png]
>

> You can see above that the replication delay is not passing 3 minutes, which is the max_standby_streaming_delay, but the actual average is 5 seconds. So, looks like rebooting the EC2 instance resolved the issue, but I do not know why.