postgresql – Postgres热备和长期查询从属

max_standby_archive_delay = -1  # max delay before canceling queries
max_standby_streaming_delay = -1  # max delay before canceling queries

感谢Guillaume的回答,但幸运的是,从PostgreSQL 9.1开始,PostgreSQL有hot_standby_feedback选项(你在postgresql.conf中的备用服务器上设置它),它不会杀死长时间运行的查询,并允许WAL更新到备用服务器.这个答案归功于PostgreSQL邮件列表(Raja / Albe / Scott)上的三个人,他们在该邮件线程中帮助了我.希望这对于在stackoverflow上搜索此答案的人有所帮助.电子邮件主题可以在这里找到：

http://www.postgresql.org/message-id/D274E3C1.1113E8%awilliams@dresourcesgroup.com

http://www.postgresql.org/docs/9.1/static/hot-standby.html

摘抄：

Remedial possibilities exist if the number of standby-query cancellations is found to be unacceptable. The first option is to set the parameter hot_standby_feedback,which prevents VACUUM from removing recently-dead rows and so cleanup conflicts do not occur. If you do this,you should note that this will delay cleanup of dead rows on the primary,which may result in undesirable table bloat. However,the cleanup situation will be no worse than if the standby queries were running directly on the primary server,and you are still getting the benefit of off-loading execution onto the standby. max_standby_archive_delay must be kept large in this case,because delayed WAL files might already contain entries that conflict with the desired standby queries.

方案实施：

以下是您应该在备用服务器上配置postgresql.conf的内容：

max_standby_archive_delay = -1
max_standby_streaming_delay = -1
hot_standby_feedback = on