Re: Long running query causing XID limit breach

Поиск
Список
Период
Сортировка
От yudhi s
Тема Re: Long running query causing XID limit breach
Дата
Msg-id CAEzWdqdPnErdeg6xe=zf7aF-fGy0Z42vXEm6zE6Ok25o=f6a7Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Long running query causing XID limit breach  (Laurenz Albe <laurenz.albe@cybertec.at>)
Ответы Re: Long running query causing XID limit breach
Список pgsql-general
Дерево обсуждения
On Mon, May 27, 2024 at 2:50 PM Laurenz Albe <laurenz.albe@cybertec.at> 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/PrimaryFirst Replica/Standby for High AvailabilitySecond Replica for Reporting
hot_standby_feedback=ONhot_standby_feedback=ONhot_standby_feedback=OFF
max_standby_streaming_delay=10 secmax_standby_streaming_delay=10 secmax_standby_streaming_delay=-1 (Infinite)
statement_timeout = "2hrs"statement_timeout="2hrs"No statement_timeout i.e. infinite
idle_in_transaction_session_timeout=10minutesidle_in_transaction_session_timeout=10minutesNo idle_in_transaction_session_timeout i.e. infinite
autovacuum_freeze_max_age=100Mautovacuum_freeze_max_age=100Mautovacuum_freeze_max_age=100M
Log_autovacuum_min_duration=0Log_autovacuum_min_duration=0Log_autovacuum_min_duration=0
 

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Pgpool with high availability
Следующее
От: Sumit Kochar
Дата:
Сообщение: tds_fdw >> Install Foreign data Wrapper on EDB Postgres to connect to SQL server database