Is there a way to convince PostgreSQL to execute long queries on a hot standy?

It kills the query with User query might have needed to see row versions that must be removed. I understand that, but I am doing a query on a table that I know never gets any row deleted... Is there a way to force porstgres to do it?


Thanks to Amacvar on reddit for pointing me to the answer:

max_standby_archive_delay and max_standby_streaming_delay have to be set for longer period than the longest query you want to run on the hot-standy.

From https://www.postgresql.org/docs/current/static/hot-standby.html

It is important that the administrator select appropriate settings for max_standby_archive_delay and max_standby_streaming_delay. The best choices vary depending on business priorities. For example if the server is primarily tasked as a High Availability server, then you will want low delay settings, perhaps even zero, though that is a very aggressive setting. If the standby server is tasked as an additional server for decision support queries then it might be acceptable to set the maximum delay values to many hours, or even -1 which means wait forever for queries to complete.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.