On Mon, May 27, 2024 at 2:50 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
> > But again for HA , in case primary down we should not be in big lag for the standby
> > and thus we want the standby also with minimal lag. And as you mentioned there will
> > never be incorrect results but at amx it will be query cancellation, so I was thinking,
> > if it's fine to just keep the "hot_feedback_standby" as OFF and let the
> > max_standby_streaming_delay set as it is like 14 sec. Let me know your thoughts.
>
You cannot have it.
Let me repeat: you cannot have it.
The only way you can have no delay in replication AND no canceled queries is
if you use two different standby servers with different settings for
"max_standby_streaming_delay". One of the server is for HA, the other for
your long-running queries.
When you suggest having different max_standby_streaming_delay for first replica (say 10 sec for High availability) and second replica(say -1 for long running queries). Do you also suggest keeping "hot_feedback_standby" as "OFF" for all the three instances i.e. master and both the replicas?
Also OP has added a few other parameters as below, do you think these should be needed?
I think the master and first replica should have the same set up because in case of any disaster to master the first replica should be able to take the place of master.
| Master/Primary | First Replica/Standby for High Availability | Second Replica for Reporting |
| hot_standby_feedback=ON | hot_standby_feedback=ON | hot_standby_feedback=OFF |
| max_standby_streaming_delay=10 sec | max_standby_streaming_delay=10 sec | max_standby_streaming_delay=-1 (Infinite) |
| statement_timeout = "2hrs" | statement_timeout="2hrs" | No statement_timeout i.e. infinite |
| idle_in_transaction_session_timeout=10minutes | idle_in_transaction_session_timeout=10minutes | No idle_in_transaction_session_timeout i.e. infinite |
| autovacuum_freeze_max_age=100M | autovacuum_freeze_max_age=100M | autovacuum_freeze_max_age=100M |
| Log_autovacuum_min_duration=0 | Log_autovacuum_min_duration=0 | Log_autovacuum_min_duration=0 |
