blocks|key|170594|text|在热备份服务器上运行查询有点棘手-它可能会失败，因为在查询过程中，可能会在主服务器上更新或删除一些需要的行。因为主节点不知道查询是在次节点上启动的，所以它认为可以清理(真空)其行的旧版本。然后，次要必须重放此清理，并必须强制取消所有可以使用这些行的查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|170595|更长的查询将更频繁地被取消。|170596|您可以通过在主服务器上启动一个可重复的读事务来解决此问题，该事务将执行一个虚拟查询，然后在次要服务器上运行真正的查询时处于空闲状态。它的存在将阻止对主服务器上的旧行版本进行清理。|170597|有关此主题和其他解决方法的更多信息，请参阅文档中的Hot+Standby+—+Handling+Query+Conflicts部分。|offset|length|170598|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/hot-standby.html#HOT-STANDBY-CONFLICT^0|0|0|0|P|12|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@$H|V|I|W|1|X]]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

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 <a href="http://www.postgresql.org/docs/current/static/hot-standby.html#HOT-STANDBY-CONFLICT" rel="noreferrer">Hot Standby — Handling Query Conflicts</a> section in documentation.

blocks|key|170631|text|热备从机上的表数据在长时间运行查询时被修改。确保表数据不被修改的一种解决方案(PostgreSQL+9.1%2B)是暂停复制，并在查询后恢复：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|170632|select+pg_xlog_replay_pause();+--+suspend
select+*+from+foo;+--+your+query
select+pg_xlog_replay_resume();+--resume|code-block|syntax|javascript|170633|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The table data on the hot standby slave server is modified while a long running query is running. A solution (PostgreSQL 9.1+) to make sure the table data is not modified is to suspend the replication and resume after the query:

<pre><code>select pg_xlog_replay_pause(); -- suspend
select * from foo; -- your query
select pg_xlog_replay_resume(); --resume
</code></pre>

blocks|key|388724|text|不需要碰hot_standby_feedback。正如其他人所提到的，将其设置为on可能会使master膨胀。想象一下，在从服务器上打开事务，而不是关闭它。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|388725|相反，将max_standby_archive_delay和max_standby_streaming_delay设置为某个合理的值：|388726|#+/etc/postgresql/10/main/postgresql.conf+on+a+slave
max_standby_archive_delay+=+900s
max_standby_streaming_delay+=+900s|code-block|syntax|javascript|388727|这样持续时间小于900秒的slaves查询不会被取消。如果您的工作负载需要更长的查询，只需将这些选项设置为更高的值。|388728|entityMap^0|4|K|14|2|0|4|P|U|R|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|10|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|11|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|P|$]]

No need to touch <code>hot_standby_feedback</code>. As others have mentioned, setting it to <code>on</code> can bloat master. Imagine opening transaction on a slave and not closing it.

Instead, set <code>max_standby_archive_delay</code> and <code>max_standby_streaming_delay</code> to some sane value:

<pre><code># /etc/postgresql/10/main/postgresql.conf on a slave
max_standby_archive_delay = 900s
max_standby_streaming_delay = 900s
</code></pre>

This way queries on slaves with a duration less than 900 seconds won't be cancelled. If your workload requires longer queries, just set these options to a higher value.

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.

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

Any suggestions on how to resolve? Thanks

PostgreSQL ERROR: canceling statement due to conflict with recovery

SQL数据库

PostgreSQL

数据库

服务器

在待机模式下对PostgreSQL数据库运行查询时，我收到以下错误。导致错误的查询在1个月内工作正常，但当查询超过1个月时，会出现错误。ERROR: canceling statement due to conflict with recoveryDetail: User query might have needed...

问PostgreSQL错误:由于与恢复冲突，正在取消语句
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PostgreSQL错误:由于与恢复冲突，正在取消语句EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PostgreSQL错误:由于与恢复冲突，正在取消语句
EN