Re: PostgreSQL 9.2 high replication lag

Okay.

Looks like I found the "issue". A while ago we set max_standby_streaming_delay to -1, because we were getting to many errors "ERROR: canceling statement due to conflict with recovery.".

According to the documentation, max_standby_streaming_delay is aconfiguration parameterdetermining how long a standby server should wait before canceling queries that conflict with pending WAL entries received via streaming replication.

My question then is: Which queries, if the slave can only receive SELECTs?

---
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 Wednesday, July 14th, 2021 at 1:09 PM, Lucas <root(at)sud0(dot)nz> wrote:

> Hello all,
>

> I have a cluster of PostgreSQL 9.2 servers (yes, I know... we're working on a migration to PG 13) that has one master and one slave.
> [image.png]
>

> I know that the streaming replication is expected to have delays, but this is getting worse and worse everyday. You can see the replication gap is reaching >10 minutes quite often.
>

> The servers are hosted in AWS as EC2 instances and they're both in different AZs. I also know that the streaming replication was first introduced in PG 9.0, so we're using a very old version of it.. I'm sure it has improved over the years...
> What can I look for to make this lag not so high? Is there anything I could change in my postgresql.conf files? Any tips?
>

> Thanks!
>

> ---
> 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.