Error canceling statement due to conflict with recovery

I’m going to add some updated info and references to @max-malysh’s excellent answer above.

In short, if you do something on the master, it needs to be replicated on the slave. Postgres uses WAL records for this, which are sent after every logged action on the master to the slave. The slave then executes the action and the two are again in sync. In one of several scenarios, you can be in conflict on the slave with what’s coming in from the master in a WAL action. In most of them, there’s a transaction happening on the slave which conflicts with what the WAL action wants to change. In that case, you have two options:

1. Delay the application of the WAL action for a bit, allowing the slave to finish its conflicting transaction, then apply the action.
2. Cancel the conflicting query on the slave.

We’re concerned with #1, and two values:

• max_standby_archive_delay — this is the delay used after a long disconnection between the master and slave, when the data is being read from a WAL archive, which is not current data.
• max_standby_streaming_delay — delay used for cancelling queries when WAL entries are received via streaming replication.

Generally, if your server is meant for high availability replication, you want to keep these numbers short. The default setting of 30000 (milliseconds if no units given) is sufficient for this. If, however, you want to set up something like an archive, reporting- or read-replica that might have very long-running queries, then you’ll want to set this to something higher to avoid cancelled queries. The recommended 900s setting above seems like a good starting point. I disagree with the official docs on setting an infinite value -1 as being a good idea—that could mask some buggy code and cause lots of issues.

The one caveat about long-running queries and setting these values higher is that other queries running on the slave in parallel with the long-running one which is causing the WAL action to be delayed will see old data until the long query has completed. Developers will need to understand this and serialize queries which shouldn’t run simultaneously.

For the full explanation of how max_standby_archive_delay and max_standby_streaming_delay work and why, go here.

ERROR: canceling statement due to conflict with recovery

Detail: User query might have needed to see row versions that must be removed

5 ответов:

How do I troubleshoot the error «canceling statement due to conflict with recovery» when querying the read replica for my RDS for PostgreSQL DB instance?

Last updated: 2022-06-23

I’ve set up a read replica for my Amazon Relational Database Service (Amazon RDS) for PostgreSQL instance. I get the error «canceling statement due to conflict with recovery» when I query the read replica.

Short description

This error might occur due to the lack of visibility from the primary instance over the activity that’s happening on the read replica. The conflict with recovery occurs when WAL information can’t be applied on the read replica because the changes might obstruct an activity that’s happening on the read replica.

For example, suppose that you run a DROP statement on the primary instance when a long running SELECT statement is running on the read replica on the table that’s dropped on the primary instance. Then, the read replica has two options:

• Wait for the SELECT statement to be finished before applying the WAL record. In this case, the replication lag increases.
• Apply the WAL record, and then cancel the SELECT statement. In this case, you get the error «canceling statement due to conflict with recovery«.

The read replica resolves these replication conflicts based on the value of the parameters max_standby_streaming_delay and max_standby_archive_delay. The max_standby_streaming_delay parameter determines how long the read replica must wait before canceling standby queries that conflict with WAL entries that are about to be applied. If the conflicting statement is still running after this period, then PostgreSQL cancels the statement and issues the following error message:

You get this error typically due to long running queries on the read replica.

In the preceding example with the DROP statement, the DROP request is stored on the WAL file for applying later on the read replica for consistency. Suppose that a SELECT statement is already running on the read replica that tries to retrieve data from the dropped object with a runtime that’s more than the value in max_standby_streaming_delay. Then, the SELECT statement is canceled so that the DROP statement can be applied.

Session 1 (Read replica) Run a SELECT statement on example_table :

Session 2 (Primary) Run a DROP statement on example_table:

You get the following error:

Also, query conflict might happen when a transaction on the read replica is reading tuples that are set for deletion on the primary instance. The deletion of tuples followed by vacuuming on the primary instance causes a conflict with the SELECT query that’s still running on the replica. In this case, the SELECT query on the replica is terminated with the following error message:

Resolution

When a read replica encounters a conflict, and you get the error «canceling statement due to conflict with recovery» on the error log, you can set certain custom parameters based on the error message to reduce the impact of the conflict. Note that the custom parameters must be set on the read replica.

You get the error «canceling statement due to conflict with recovery» with «DETAIL: User was holding a relation lock for too long»

max_standby_streaming_delay/max_standby_archive_delay: You can use these parameters to allow more time before canceling standby statements that conflict with the about-to-be-applied WAL entries. These values represent the total amount of time that’s allowed for applying WAL data after the data is received from the primary instance. These parameters are specified depending on from where the WAL data is read. If WAL data is read from streaming replication, then use the max_standby_streaming_delay parameter. If WAL data is read from the archive location in Amazon Simple Storage Service (Amazon S3), then use the max_standby_archive_delay parameter.

Keep the following in mind when setting these parameters:

• If you set the values of these parameters to -1, the replica instance is allowed to wait forever for conflicting queries to complete, increasing the replication lag.
• If you set the values of these parameters to 0, then the conflicting queries are canceled, and the WAL entries are applied on the replica instance.
• The default value for these parameters is set to 30 seconds.
• If you don’t specify the unit when setting these parameters, millisecond is considered as the unit.

Tune the values of these parameters to balance query cancellation or replication lag based on your use case.

Note: If you are increasing max_standby_archive_delay to avoid canceling queries that conflict with reading WAL archive entries, then consider increasing max_standby_streaming_delay as well to avoid cancelations linked to conflict with streaming WAL entries.

You get the error «canceling statement due to conflict with recover» with «DETAIL: User query might have needed to see row versions that must be removed»

hot_standby_feedback: If you activate this parameter, feedback messages are sent to the primary instance from the read replica with information of the oldest active transaction. Therefore, the primary instance doesn’t remove records that the transaction might need.

When you activate this parameter on the read replica, long-running queries on the read replica might lead to table bloat on the primary instance. This is because vacuum operations don’t remove the dead tuples that might be required by queries running on the read replica. This parameter is turned off by default. Therefore, exercise caution when activating this parameter.

You can also inspect the pg_stat_database_conflicts view on the read replica for statistics about statements that are canceled due to conflicts with recovery on the read replica.

Источник

PostgreSQL: canceling statement due to conflict with recovery

Общая информация о проблеме

Имеется конфигурация Master-Slave на PostgreSQL 9.5 с дефолтными конфигурациями и минимум изменения. Слейв используется для выборки запросов большого объема и в результате на некоторых запросах вываливается ошибка:

ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
‘********** Error **********
ERROR: canceling statement due to conflict with recovery
SQL state: 40001
Detail: User query might have needed to see row versions that must be removed.

Основная причина возникновения в том, что в запросе выборка происходит по данным со слейва (Standby), но на мастере эти данные уже устарели и были изменены или удалены, а потому такие запросы обрываются и возникает ошибка.

Я не являюсь DBA и с PSQL имею намного меньше опыта, чем с MySQL, но всё же решил разобраться с проблемой самостоятельно. Первые ссылки поиска по данной ошибке приводят на решение установить параметр hot_standby_feedback в значение on. Причем решение является популярным и многие его описывают. Но в некоторых источниках пишут, что включение hot_standby_feedback может вызвать разбухание мастера и прочие непредвиденные проблемы, если точно не быть уверенным в том, что делаешь. Я не был уверен, а потому увеличил нижеперечисленные значения с дефолтных 30 секунд до 15 минут:

Данное решение позволяет лишь увеличить задержки для всяких долгоиграющих запросов, позволяя делать большие выборки, и не затрагивая ничего иного, что может повести себя непредсказуемо и привести к более серьёзным проблемам.

Информация из документации для ознакомления:

В режиме горячего резерва этот параметр определяет, как долго должен ждать ведомый сервер, прежде чем отменять запросы, конфликтующие с очередными изменениями в WAL, как описано в Подразделе 26.5.2. Задержка max_standby_archive_delay применяется при обработке данных WAL, считываемых из архива (не текущих данных). Значение этого параметра задаётся в миллисекундах (если явно не указаны другие единицы) и по умолчанию равно 30 секундам. При значении, равном -1, ведомый может ждать завершения конфликтующих запросов неограниченное время. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.

Заметьте, что параметр max_standby_archive_delay определяет не максимальное время, которое отводится для выполнения каждого запроса, а максимальное общее время, за которое должны быть применены изменения из одного сегмента WAL. Таким образом, если один запрос привёл к значительной задержке при обработке сегмента WAL, остальным конфликтующим запросам будет отведено гораздо меньше времени.

В режиме горячего резерва этот параметр определяет, как долго должен ждать ведомый сервер, прежде чем отменять запросы, конфликтующие с очередными изменениями в WAL, как описано в Подразделе 26.5.2. Задержка max_standby_streaming_delay применяется при обработке данных WAL, поступающих при потоковой репликации. Значение этого параметра задаётся в миллисекундах (если явно не указаны другие единицы) и по умолчанию равно 30 секундам. При значении, равном -1, ведомый может ждать завершения конфликтующих запросов неограниченное время. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.

Заметьте, что параметр max_standby_streaming_delay определяет не максимальное время, которое отводится для выполнения каждого запроса, а максимальное общее время, за которое должны быть применены изменения из WAL после получения от главного сервера. Таким образом, если один запрос привёл к значительной задержке, остальным конфликтующим запросам будет отводиться гораздо меньше времени, пока резервный сервер не догонит главный.

Внесение изменений на slave без перезагрузки

Проверка, что значения в поле context являются sighup, т.е. внесённые в postgresql.conf изменения этих параметров можно применить, не перезапуская сервер.

Проверить, что перезапуск не требуется. true, если значение изменено в файле конфигурации, но требуется перезапуск; в противном случае — false.

Источник

Ошибка PostgreSQL: отмена инструкции из-за конфликта с восстановлением

Я получаю следующую ошибку при выполнении запроса на БД PostgreSQL в режиме ожидания. Запрос, который вызывает ошибку, отлично работает в течение 1 месяца, но при запросе более 1 месяца возникает ошибка.

любые предложения о том, как решить? Спасибо

5 ответов:

выполнение запросов на горячем резервном сервере несколько сложно — это может привести к сбою, потому что во время запроса некоторые необходимые строки могут быть обновлены или удалены на основном сервере. Поскольку первичный не знает, что запрос запускается на вторичном, он думает, что может очистить (вакуумировать) старые версии своих строк. Затем вторичный должен воспроизвести эту очистку и должен принудительно отменить все запросы, которые могут использовать эти строки.

более длинные запросы будут отменены чаще.

вы можете обойти это запустив повторяемую транзакцию чтения на первичном, которая выполняет фиктивный запрос, а затем простаивает, пока реальный запрос выполняется на вторичном. Его наличие предотвратит вакуумирование старых версий строк на первичном.

более подробно на эту тему и другие обходные пути объясняются в Hot Standby — Обработка Конфликтов Запросов раздел в документации.

нет необходимости запускать неработающие транзакции на главном устройстве. В postgresql-9.1 самый прямой способ решить эту проблему-установить

Это позволит мастеру знать о длительных запросах. Из docs:

первый вариант-установить параметр hot_standby_feedback, который предотвращает Вакуум от удаления недавно мертвых строк и поэтому конфликтов очистки не происходит.

почему это не по умолчанию? Этот параметр был добавлен после начального реализация и это единственный способ, которым резерв может повлиять на мастера.

как заявил здесь о hot_standby_feedback = on :

Ну, недостатком его является то, что в режиме ожидания можно наворотить мастер, что может быть удивительно для некоторых людей, слишком

С какой настройкой max_standby_streaming_delay? Я бы предпочел по умолчанию это -1, чем по умолчанию hot_standby_feedback on. Таким образом, что вы делаете в режиме ожидания влияет только на в режиме ожидания

поэтому я добавил

и не более pg_dump ошибка для нас, ни мастер раздувать 🙂

не нужно трогать hot_standby_feedback . Как уже упоминали другие, установив его в on можно наворотить мастер. Представьте, что вы открываете транзакцию на ведомом устройстве, а не закрываете ее.

вместо этого установите max_standby_archive_delay и max_standby_streaming_delay к некоторому вменяемому значению:

таким образом, запросы на ведомых устройствах с длительностью менее 900 секунд не будут отменены. Если ваша рабочая нагрузка требует более длинных запросов, просто установите для этих параметров более высокое значение.

табличные данные на ведомом сервере горячего резервирования изменяются во время выполнения длительного запроса. Решение (PostgreSQL 9.1+), чтобы убедиться, что данные таблицы не изменяются, заключается в приостановке репликации и возобновлении после запроса:

Источник

Обсуждение: Hot Standby — ERROR: canceling statement due to conflict with recovery

Hot Standby — ERROR: canceling statement due to conflict with recovery

Re: Hot Standby — ERROR: canceling statement due to conflict with recovery

Re: Hot Standby — ERROR: canceling statement due to conflict with recovery

On Sonntag, 27. Februar 2011, Sean Laurent wrote:

> Unfortunately, most queries against the hot standby fail. Worse
> yet, pg_dump fails:

> I’m not entirely certain I understand why I’m seeing this. Nor do
> I understand how to fix or work around this. Any advice or
> suggestions would be greatly appreciated.

Long running queries on the standby are a bit tricky, because they
might need to see row versions that are already removed on the
master.
It’s well documented:
http://www.postgresql.org/docs/9.0/static/hot-standby.html

Re: Hot Standby — ERROR: canceling statement due to conflict with recovery

On Sunday, February 27, 2011 11:57:35 am Sean Laurent wrote:

> On Sun, Feb 27, 2011 at 1:04 PM, Jens Wilke <> wrote:

> > On Sonntag, 27. Februar 2011, Sean Laurent wrote:

> > > Unfortunately, most queries against the hot standby fail. Worse

> > > yet, pg_dump fails:

> > > I’m not entirely certain I understand why I’m seeing this. Nor do

> > > I understand how to fix or work around this. Any advice or

> > > suggestions would be greatly appreciated.

> > Long running queries on the standby are a bit tricky, because they

> > might need to see row versions that are already removed on the

> > It’s well documented:

> Right. I read all of that. I guess I just assumed it was possible to create

> a snapshot on the standby so that a longer running on the standby could

> complete. In particular, I was really hoping to run database dumps against

> the standby, not the master.

From the above link:

«The most common reason for conflict between standby queries and WAL replay is «early cleanup». Normally, PostgreSQL allows cleanup of old row versions when there are no transactions that need to see them to ensure correct visibility of data according to MVCC rules. However, this rule can only be applied for transactions executing on the master. So it is possible that cleanup on the master will remove row versions that are still visible to a transaction on the standby. «

Below that it goes into some possible solutions, the easiest of which to test would seem to be:

«Another option is to increase vacuum_defer_cleanup_age on the primary server, so that dead rows will not be cleaned up as quickly as they normally would be. This will allow more time for queries to execute before they are cancelled on the standby, without having to set a high max_standby_streaming_delay . However it is difficult to guarantee any specific execution-time window with this approach, since vacuum_defer_cleanup_age is measured in transactions executed on the primary server. «

Источник

Я собираюсь добавить обновленную информацию и ссылки на отличный ответ @max-malysh выше.

Короче говоря, если вы что-то делаете на ведущем, это нужно реплицировать на ведомом. Postgres использует для этого записи WAL, которые отправляются после каждого зарегистрированного действия на ведущем устройстве подчиненному. Затем ведомое устройство выполняет действие, и они снова синхронизируются. В одном из нескольких сценариев вы можете конфликтовать на ведомом устройстве с тем, что приходит от ведущего в действии WAL. В большинстве из них на ведомом устройстве происходит транзакция, которая противоречит тому, что действие WAL хочет изменить. В таком случае у вас есть два варианта:

1. Немного задержите применение действия WAL, позволив подчиненному устройству завершить свою конфликтующую транзакцию, а затем примените действие.
2. Отмените конфликтующий запрос на ведомом устройстве.

Нас интересует №1 и два значения:

• max_standby_archive_delay — это задержка, используемая после длительного разрыва связи между мастером и слейвом, когда данные считываются из архива WAL, который не является текущими данными.
• max_standby_streaming_delay — задержка для отмены запросов при получении записей WAL через потоковую репликацию.

Как правило, если ваш сервер предназначен для репликации с высокой доступностью, вы хотите, чтобы эти числа были короткими. Настройка по умолчанию 30000 (миллисекунд, если единицы измерения не указаны) достаточно для этого. Однако, если вы хотите настроить что-то вроде архива, реплики для создания отчетов или чтения, которая может иметь очень длительные запросы, вам нужно установить более высокое значение, чтобы избежать отмененных запросов. Рекомендуемый 900s настройка выше кажется хорошей отправной точкой. Я не согласен с официальными документами по установке бесконечного значения -1 как хорошая идея — это может замаскировать некоторый код с ошибками и вызвать множество проблем.

Одно предостережение относительно длительных запросов и установки этих значений выше заключается в том, что другие запросы, выполняемые на ведомом устройстве параллельно с длительным запросом, вызывающим задержку действия WAL, будут видеть старые данные до тех пор, пока длинный запрос не будет завершен. Разработчикам необходимо это понять и сериализовать запросы, которые не должны выполняться одновременно.

Для полного объяснения того, как max_standby_archive_delay и max_standby_streaming_delay работать и почему, иди сюда.

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

Ответы:

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

hot_standby_feedback = on

max_standby_streaming_delay = -1

select pg_xlog_replay_pause(); -- suspend
select * from foo; -- your query
select pg_xlog_replay_resume(); --resume