Re: PostgreSQL 9.2 high replication lag

[Lucas <root@xxxxxxx>]

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

On Thursday, July 15th, 2021 at 10:34 PM, Lucas <root@xxxxxxx> wrote:

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

On Wednesday, July 14th, 2021 at 5:30 PM, Laurenz Albe laurenz.albe@xxxxxxxxxxx wrote:

On Tue, 2021-07-13 at 20:14 -0700, David G. Johnston wrote:

On Tue, Jul 13, 2021 at 8:01 PM Lucas root@xxxxxxx wrote:

According to the documentation, max_standby_streaming_delay is a configuration parameter

determining 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?

The select queries...

Follow the link in the docs for max_stanbdy_steaming_delay to:

https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT

for the details.

Perhaps you might find the following article interesting, where I tried to

discuss this topic in some depth:

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

Thanks!

I have changed max_standby_streaming_delay from -1 to 120s. However, I can still see the replication being delayed for up to 3 minutes quite frequent.

I will try to see if I can find what kind of query is causing that... Maybe via pg_stat_database_conflicts?

At 10pm (red mark) yesterday I changed the max_standby_streaming_delay = -1 to max_standby_streaming_delay = 120s.

However, it hasn't changed and the replication lag is still high all the time.

I'm deploying a new slave server but it will be in the same AZ as its master. And will monitor there and replication lag.

Please, let me know if you guys have any other suggestions.

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

Attachment: signature.asc
Description: OpenPGP digital signature