Общая информация о проблеме
Имеется конфигурация 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 минут:
max_standby_archive_delay = 900s
max_standby_streaming_delay = 900sДанное решение позволяет лишь увеличить задержки для всяких долгоиграющих запросов, позволяя делать большие выборки, и не затрагивая ничего иного, что может повести себя непредсказуемо и привести к более серьёзным проблемам.
Информация из документации для ознакомления:
max_standby_archive_delay (integer)
В режиме горячего резерва этот параметр определяет, как долго должен ждать ведомый сервер, прежде чем отменять запросы, конфликтующие с очередными изменениями в WAL, как описано в Подразделе 26.5.2. Задержка max_standby_archive_delay применяется при обработке данных WAL, считываемых из архива (не текущих данных). Значение этого параметра задаётся в миллисекундах (если явно не указаны другие единицы) и по умолчанию равно 30 секундам. При значении, равном -1, ведомый может ждать завершения конфликтующих запросов неограниченное время. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.
Заметьте, что параметр max_standby_archive_delay определяет не максимальное время, которое отводится для выполнения каждого запроса, а максимальное общее время, за которое должны быть применены изменения из одного сегмента WAL. Таким образом, если один запрос привёл к значительной задержке при обработке сегмента WAL, остальным конфликтующим запросам будет отведено гораздо меньше времени.
max_standby_streaming_delay (integer)
В режиме горячего резерва этот параметр определяет, как долго должен ждать ведомый сервер, прежде чем отменять запросы, конфликтующие с очередными изменениями в WAL, как описано в Подразделе 26.5.2. Задержка max_standby_streaming_delay применяется при обработке данных WAL, поступающих при потоковой репликации. Значение этого параметра задаётся в миллисекундах (если явно не указаны другие единицы) и по умолчанию равно 30 секундам. При значении, равном -1, ведомый может ждать завершения конфликтующих запросов неограниченное время. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.
Заметьте, что параметр max_standby_streaming_delay определяет не максимальное время, которое отводится для выполнения каждого запроса, а максимальное общее время, за которое должны быть применены изменения из WAL после получения от главного сервера. Таким образом, если один запрос привёл к значительной задержке, остальным конфликтующим запросам будет отводиться гораздо меньше времени, пока резервный сервер не догонит главный.
Внесение изменений на slave без перезагрузки
Проверка, что значения в поле context являются sighup, т.е. внесённые в postgresql.conf изменения этих параметров можно применить, не перезапуская сервер.
select context from pg_settings where name = 'max_standby_archive_delay';
select context from pg_settings where name = 'max_standby_streaming_delay';Изменить значения:
alter system set max_standby_archive_delay = '900s';
alter system set max_standby_streaming_delay = '900s';Проверить, что перезапуск не требуется. true, если значение изменено в файле конфигурации, но требуется перезапуск; в противном случае — false.
select pending_restart from pg_settings where name = 'max_standby_streaming_delay';
select pending_restart from pg_settings where name = 'max_standby_archive_delay';Перечитать конфиг:
select * from pg_reload_conf();Проверить новые значения:
show max_standby_archive_delay;
show max_standby_streaming_delay;