PostgreSQL错误：由于与恢复冲突而取消语句 [英] PostgreSQL ERROR: canceling statement due to conflict with recovery

问题描述

在待机模式下在PostgreSQL数据库上运行查询时出现以下错误。导致错误的查询可以在1个月内正常工作，但是当您查询1个月以上时，会导致错误。

I'm getting the following error when running a query on a PostgreSQL db in standby mode. The query that causes the error works fine for 1 month but when you query for more than 1 month an error results.

ERROR: canceling statement due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed

关于如何解决的任何建议？谢谢

Any suggestions on how to resolve? Thanks

推荐答案

在热备份服务器上运行查询有些棘手-可能会失败，因为在查询过程中可能会更新一些所需的行或主要删除。由于主数据库不知道查询是在辅助数据库上启动的，因此它认为它可以清除（清理）其行的旧版本。然后，secondary必须重播此清理操作，并且必须强制取消所有可以使用这些行的查询。

Running queries on hot-standby server is somewhat tricky — it can fail, because during querying some needed rows might be updated or deleted on primary. As a primary does not know that a query is started on secondary it thinks it can clean up (vacuum) old versions of its rows. Then secondary has to replay this cleanup, and has to forcibly cancel all queries which can use these rows.

更长的查询将被更频繁地取消。

Longer queries will be canceled more often.

您可以通过以下方法解决此问题：在主数据库上启动一个可重复的读取事务，该事务将进行虚拟查询，然后在次要数据库上运行真正的查询时处于空闲状态。

You can work around this by starting a repeatable read transaction on primary which does a dummy query and then sits idle while a real query is run on secondary. Its presence will prevent vacuuming of old row versions on primary.

有关此主题的更多信息和其他变通方法，请参见热备援-处理文档中的冲突部分。

More on this subject and other workarounds are explained in Hot Standby — Handling Query Conflicts section in documentation.

这篇关于PostgreSQL错误：由于与恢复冲突而取消语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！