Replay lag on replica even after setting max_standby_streaming_delay
1
vote
1
answer
98
views
I've a 1 master and 5 replicas postgresql cluster configured as hot standby using streaming replication in synchronous mode.
I've started to see 10' replay delays in one of the sync replicas while normally the delay is in the ms order.
The only difference I see from
pg_stat_replication
is replay_lag which I would expect its caused by a buffer pin. But I've set max_standby_streaming_delay
and max_standby_archive_delay
to 60sec. Also, hot_standby_feedback
is on. So, I'd expect any buffer pin to kill the read queries on the standby within the 1' and replay lag to maintain below the minute.
The only log lines I could see when the event happens are related to a query being cancelled:
/var/log/postgresql/postgresql-2025-01-27_170445.log:2025-01-27 17:49:42 UTC LOG: recovery restart point at 4ABC/1A7DEF98
/var/log/postgresql/postgresql-2025-01-27_174958.log:2025-01-27 17:54:41 UTC LOG: recovery restart point at 4ABC/34D14738
/var/log/postgresql/postgresql-2025-01-27_174958.log:2025-01-27 17:58:49 UTC ERROR: canceling statement due to conflict with recovery
/var/log/postgresql/postgresql-2025-01-27_174958.log:2025-01-27 17:58:49 UTC ERROR: canceling statement due to conflict with recovery
/var/log/postgresql/postgresql-2025-01-27_174958.log:2025-01-27 17:58:49 UTC ERROR: canceling statement due to conflict with recovery
/var/log/postgresql/postgresql-2025-01-27_174958.log:2025-01-27 17:58:49 UTC ERROR: canceling statement due to conflict with recovery
/var/log/postgresql/postgresql-2025-01-27_174958.log:2025-01-27 17:58:49 UTC ERROR: canceling statement due to conflict with recovery
/var/log/postgresql/postgresql-2025-01-27_174958.log:2025-01-27 17:58:49 UTC FATAL: terminating connection due to conflict with recovery
/var/log/postgresql/postgresql-2025-01-27_174958.log:2025-01-27 17:59:41 UTC LOG: recovery restart point at 4ABC/51921288
Also not sure if this is related to the fact that recoveries logs are processed every ~5'
Asked by Nico Gatti
Jan 27, 2025, 06:03 PM
Last activity: Jan 28, 2025, 05:04 AM
Last activity: Jan 28, 2025, 05:04 AM