Логическая репликация PostgreSQL работает, но строки расходятся: какие конфликты пропускаются без остановки

Репликация не падает, потому что часть конфликтов PostgreSQL пропускает молча: update_missing и delete_missing просто отбрасывают операцию, а update_origin_differs и delete_origin_differs применяют её поверх чужих правок. Ниже: какие конфликты останавливают подписку, а какие нет, как увидеть расхождение и как его чинить.

Почему подписка зелёная, а данные на подписчике другие

Самая коварная поломка логической репликации выглядит как её отсутствие: в логах тишина, подписка в статусе работающей, мониторинг доволен. А в отчёте на подписчике не хватает записей, и бухгалтер находит это в конце месяца. Мы в АйТи-Фреш сопровождаем PostgreSQL для 1С и прикладных систем, и этот сценарий я встречал не раз: админ видит, что процесс apply живёт, и делает вывод, что данные совпадают. Это не вывод, это надежда.

Логика простая. Подписчик применяет изменения с публикатора строка за строкой. Если применить можно, он применяет. Если нельзя из-за нарушения уникального ограничения, он останавливается с ошибкой и ждёт, пока человек разберётся. А вот если нужной строки на подписчике просто нет, изменение пропускается без остановки. Так сделано намеренно: подписчик по документации вполне может иметь собственные данные, и падать на каждой отсутствующей строке было бы неудобно. Цена этого удобства в том, что тишина в логе перестаёт означать совпадение данных.

Для начала нужна классификация. В документации PostgreSQL 18 (раздел о конфликтах логической репликации) перечислено семь типов конфликтов. Я свёл их в таблицу по тому, что происходит с репликацией. | Тип конфликта | Что случилось | Репликация | |---|---|---| | insert_exists | вставка нарушает недеферрируемое уникальное ограничение | останавливается с ошибкой | | update_exists | новое значение строки нарушает недеферрируемое уникальное ограничение | останавливается с ошибкой | | multiple_unique_conflicts | вставка или обновление нарушает несколько уникальных ограничений | останавливается с ошибкой | | update_missing | строки для обновления нет | операция пропускается, репликация идёт | | delete_missing | строки для удаления нет | операция пропускается, репликация идёт | | update_origin_differs | строку до этого менял другой источник | обновление применяется, репликация идёт | | delete_origin_differs | строку до этого менял другой источник | удаление применяется, репликация идёт | Первые три строки громкие: вы про них узнаете. Четыре последние тихие, и именно они объясняют заголовок этой статьи.

Какие конфликты пропускаются без остановки и чем это опасно

Начну с двух пропусков. Конфликт update_missing означает, что строка, которую нужно обновить, на подписчике не найдена. Операция пропускается, репликация идёт дальше. Конфликт delete_missing такой же для удаления. Для удаления это ещё полбеды: вы хотели, чтобы строки не было, её и нет. Для обновления это потеря данных на подписчике: свежий статус, телефон или сумма так и не попадут в таблицу, потому что обновлять нечего. И что важно, потом строка сама не появится. Вставка была раньше, обновление пропущено, и подписчик навсегда остаётся с отставшей версией или вообще без неё.

Откуда берётся отсутствующая строка? Почти всегда из ручных правок на подписчике. Кто-то почистил «лишнее» в отчётной базе, кто-то перезалил таблицу из дампа, кто-то выполнил DELETE, потому что «это же реплика, там можно». Меня до сих пор веселит формулировка «она только для отчётов, туда никто не пишет», после которой в логе находится запись от учётной записи аналитика. Подписчик в логической репликации не read-only. Это обычный сервер, на котором можно что угодно, и PostgreSQL вас не остановит.

Теперь про вторую пару. Конфликты update_origin_differs и delete_origin_differs возникают, когда обновляется или удаляется строка, которую ранее менял другой источник (например, локальная транзакция на подписчике или другая подписка). По документации, такое изменение всегда применяется. Тихо перезаписать чужую правку, и в логе нет ошибки. Для двусторонних схем, где два сервера пишут в одни таблицы, это штатная ситуация, о которой нужно знать и которой нужно управлять. А вот в односторонней схеме «основной сервер плюс отчётный» это сигнал, что кто-то пишет туда, куда не должен.

Есть важная оговорка, которую пропускают. Эти два конфликта вообще не обнаруживаются, пока на подписчике не включён параметр track_commit_timestamp. Без него PostgreSQL не знает, кто и когда менял строку, и просто применяет изменение. То есть выключенный параметр делает класс конфликтов невидимым даже для счётчиков. Параметр по документации принимает значение только при старте сервера, значение по умолчанию off. Включать его после первого же расхождения поздно, включать надо заранее.

Отдельно отмечу уникальные конфликты, которые останавливают репликацию. Здесь всё честнее: ошибка будет повторяться, пока вы не уберёте причину, и приложение на публикаторе это не заденет, а вот подписчик начнёт отставать. WAL на публикаторе при этом копится в слоте, и если оставить ситуацию на выходные, к понедельнику получите проблему с местом на диске уже на основном сервере. Это сильно отличается от тихих конфликтов, где вред в данных, а не в диске. Если вы подозреваете, что у вас уже накопились такие пропуски, мы можем посмотреть вашу подписку и сказать, сколько строк разошлось, не трогая рабочие данные.

Как увидеть расхождение: счётчики, лог и сверка строк

Первое, что я делаю на любой подписке, это включаю track_commit_timestamp на подписчике. Параметр принимает значение только при старте, поэтому правим postgresql.conf и перезапускаем экземпляр в окно обслуживания.

# postgresql.conf на подписчике
track_commit_timestamp = on

Проверить после перезапуска можно запросом SHOW track_commit_timestamp;. Дополнительная польза по документации: с включённым параметром в запись о конфликте для insert_exists, update_exists и multiple_unique_conflicts попадают сведения об источнике, номере транзакции и времени фиксации конфликтующей строки.

Второе, это счётчики. В PostgreSQL 18 статистика конфликтов ведётся в представлении pg_stat_subscription_stats, по одной строке на подписку. Там есть apply_error_count, sync_error_count и по одному счётчику на каждый тип конфликта: confl_insert_exists, confl_update_origin_differs, confl_update_exists, confl_update_missing, confl_delete_origin_differs, confl_delete_missing и confl_multiple_unique_conflicts. Запрос для проверки:

SELECT subname, apply_error_count, sync_error_count,
       confl_update_missing, confl_delete_missing,
       confl_update_origin_differs, confl_delete_origin_differs,
       stats_reset
FROM pg_stat_subscription_stats;

Любое ненулевое значение в тихих счётчиках для односторонней схемы означает, что подписчик уже отличается от публикатора или скоро отличится. Это отличная метрика для мониторинга: берём разницу между опросами, и рост счётчика поднимает тревогу. Счётчики сбрасываются, поле stats_reset покажет когда, так что смотрите на динамику, а не на абсолютную цифру.

Третье, журнал. По документации сервер пишет сообщение вида «conflict detected on relation ... conflict=...» с пояснением в строке DETAIL: ключ, существующая локальная строка, удалённая строка или replica identity. Значения длиннее 64 байт обрезаются. В примере из документации конфликт, остановивший репликацию, идёт с уровнем ERROR, а общий шаблон сообщения описан с уровнем LOG. Поэтому журнал стоит разбирать не только по слову ERROR: grep по «conflict detected» ловит и случаи без остановки. Подробности журналирования расширены в PostgreSQL 18, проверьте формат в своей минорной версии.

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

SELECT count(*), md5(string_agg(t::text, '|' ORDER BY id))
FROM public.appointments AS t;

Если пара значений совпала, таблицы идентичны. Если нет, сверяем по диапазонам id и находим расходящиеся строки. Для больших таблиц считаем хеш порциями по диапазону ключа. Запрос нагружает сервер, поэтому ночью и по одной таблице. Одна тонкость: текстовое представление timestamptz зависит от параметра TimeZone сессии, а дат от DateStyle, поэтому на обеих сторонах запускайте сверку с одинаковыми настройками (например, SET TimeZone = 'UTC'), иначе хеши разойдутся при идентичных данных. Сверка раз в неделю стоит дешевле, чем один спор о том, чьи цифры в отчёте правильные. Согласованность чтения с реплик отдельная тема, я разбирал её в статье про согласованность чтения с реплик PostgreSQL, здесь речь о расхождении самих данных.

Репликация остановилась на конфликте: как пропустить транзакцию и не сделать хуже

Теперь про громкий случай, когда подписка встала на insert_exists, update_exists или multiple_unique_conflicts. В логе будет ERROR с типом конфликта, и повторяться он будет, пока причина не устранена. В документации PostgreSQL описано два способа выхода, и я разделяю их по тому, что вы хотите сделать с данными.

Правильный способ почти всегда исправить данные или права на подписчике так, чтобы входящее изменение больше не конфликтовало. Удалили локальную строку, которая занимает ключ, или поправили её, и на очередной попытке применение проходит. Это единственный способ, при котором вы сами решаете, какая версия строки верная. Все остальные способы решают этот вопрос за вас, причём не в пользу данных.

Второй способ пропустить конфликтующую транзакцию целиком. Для этого в сообщении об ошибке берут LSN завершения транзакции (finish LSN) и вызывают команду, как в документации:

ALTER SUBSCRIPTION mysub SKIP (lsn = '0/14C0378');

Значение выше взято из примера в документации, у вас будет своё. Вернуть всё назад можно значением NONE. После того как воркер пропустил или завершил транзакцию, поле subskiplsn в pg_subscription очищается. Эта команда пропускает все изменения удалённой транзакции, отдельные подтранзакции пропустить нельзя.

И вот тут главная ловушка. Пропуск отбрасывает не только конфликтующую строку, но и всё остальное, что было в той же транзакции. Документация прямо предупреждает: подписчик может стать несогласованным. Если транзакция на публикаторе правила пять таблиц и одна строка упёрлась в уникальность, остальные четыре правки тоже пропадут. После SKIP обязательно делайте сверку из предыдущего раздела по всем таблицам, которые затрагивала транзакция. Хорошо бы заранее найти её в логе приложения.

Есть и альтернативный путь: выключить подписку, продвинуть позицию источника функцией pg_replication_origin_advance и снова включить. Документация ставит LSN завершения плюс один, а текущие позиции показывает представление pg_replication_origin_status. Я пользуюсь им редко, потому что SKIP делает то же самое с меньшим числом способов ошибиться. Ещё одна оговорка из документации: при streaming = parallel LSN завершения может не попасть в журнал. Тогда переключаем streaming на on или off, воспроизводим конфликт и берём LSN из журнала.

Чтобы автоматические повторы не молотили впустую, при создании подписки можно задать disable_on_error. По документации CREATE SUBSCRIPTION, подписка в этом случае отключается автоматически при ошибке в воркере; значение по умолчанию false. Для небольших систем я включаю его: лучше остановленная подписка с понятным состоянием, чем бесконечный цикл ошибок и растущий слот. Но тогда мониторинг обязан замечать выключенную подписку, иначе вы получите ту же тишину, только по другой причине. Тема слотов и обслуживания схожа с тем, что я описывал в разборе репликации и failover PostgreSQL, там речь о физической репликации, а здесь о логической.

Откуда подписчик берёт расхождения: пять причин, которые я вижу чаще всего

Если собрать мои проекты в одну кучу, расхождения почти всегда сводятся к нескольким причинам. Первая и главная, запись на подписчик в обход репликации. Вторая, неверная или отсутствующая replica identity. Третья, недоделанное начальное копирование. Четвёртая, изменения схемы, которые не ушли на подписчик. Пятая, последовательности и особые объекты, которые логическая репликация не переносит.

Про replica identity коротко и по документации. Она определяет, какие сведения о старой строке пишутся в WAL при UPDATE и DELETE. Режим DEFAULT пишет старые значения первичного ключа, и у таблицы без первичного ключа он работает как NOTHING. Режим USING INDEX использует уникальный индекс без условий, без отложенной проверки и только по колонкам NOT NULL. Режим FULL пишет все колонки, платой за это служит объём WAL. Ограничение из раздела об ограничениях репликации: при FULL операции UPDATE и DELETE не применяются, если в таблице есть колонки типов вроде point или box, у которых нет класса операторов по умолчанию для B-tree или Hash. Лечится это первичным ключом или явно заданной идентичностью. Команда для таблицы без ключа:

ALTER TABLE public.clients REPLICA IDENTITY USING INDEX clients_uid_key;

Индекс clients_uid_key здесь условный, он должен быть уникальным, непартиальным и по колонкам NOT NULL.

О том, что не реплицируется, документация говорит прямо. Схема и команды DDL не реплицируются, и начальную схему переносят вручную, например через pg_dump с ключом --schema-only. Если схема на публикаторе поменялась, а данные уже идут на подписчик и в таблицу не помещаются, репликация встанет с ошибкой, пока схему не обновят. Совет из документации: аддитивные изменения применять сначала на подписчике. Данные последовательностей тоже не реплицируются: значения в serial- и identity-колонках приедут вместе с таблицей, а сама последовательность на подписчике останется на стартовом значении. Это ровно то, что ломает переключение, и подробно я разбирал это в статье про последовательности при логической репликации.

Ещё два пункта из документации, о которых вспоминают после аварии. Большие объекты (large objects) не реплицируются совсем, обойти можно только хранением данных в обычных таблицах. И реплицируются только таблицы, включая секционированные: представление, материализованное представление или внешняя таблица в публикации приведут к ошибке. Для секционированных таблиц по умолчанию изменения идут от листовых секций, поэтому те же секции должны быть на подписчике. Это меняется параметром publish_via_partition_root в CREATE PUBLICATION.

Начальное копирование тоже источник сюрпризов. По умолчанию copy_data = true, и существующие данные копируются при старте подписки. Если на подписчике таблица уже не пуста, начальная синхронизация упрётся в уникальные ключи. Если вы подписались с copy_data = false на таблицу, которая не была идентична, расхождение закладывается с первой минуты. Документация отдельно предупреждает: после изменения условия row filter ранее подписанные таблицы заново не копируются. Запомните это правило, оно спасает от «мы же пересоздали публикацию, почему данные не подтянулись».

Кейс: психологический центр «Ясный разговор», 16 рабочих мест

Условный клиент «Ясный разговор» это психологическое консультирование на 16 рабочих мест: администраторы, психологи, бухгалтерия. Основная CRM с записями на приём работала на PostgreSQL 18 на одном сервере, на втором сервере стояла подписка для отчётов и выгрузок руководителю. Схему когда-то собирал подрядчик, ушедший давно, и документации не осталось. Руководитель пожаловался на простую вещь: в отчёте за месяц сумма по оплаченным приёмам не совпадала с кассой на 37 записей.

Мониторинг показывал, что подписка работает, лаг не больше секунды, ошибок в журнале нет. Это тот самый случай, когда зелёная панель врёт по существу. Мы проверили pg_stat_subscription_stats и нашли ненулевые confl_update_missing и confl_delete_missing при нулевом apply_error_count. Причина оказалась обычной: полгода назад сотрудник отчётной группы удалил на подписчике тестовые записи и несколько «дублей» руками. Позже на основном сервере эти записи обновляли, а подписчик обновлять было нечего. Обновления молча отбрасывались, и статус «оплачено» в отчёт не попадал.

Параметр track_commit_timestamp на подписчике был выключен, поэтому конфликты origin_differs не регистрировались. Мы включили его в окно обслуживания в воскресенье вечером, перезапуск занял около двух минут. Затем сверили таблицы свёрткой count и md5: расходились две таблицы из одиннадцати, appointments и payments. Добирать строки точечно оказалось дольше, чем пересоздать данные. Для этих двух таблиц мы остановили запись, очистили их на подписчике и заново прошли начальное копирование, предварительно сделав дамп подписчика. Сам по себе REFRESH PUBLICATION тут не помогает: он запускает репликацию только для таблиц, добавленных в публикацию после прошлого обновления, а уже подписанные таблицы заново не копирует. Поэтому шли через исключение и возврат таблиц: ALTER PUBLICATION ... DROP TABLE на публикаторе и REFRESH PUBLICATION на подписчике, затем TRUNCATE двух таблиц на подписчике, ALTER PUBLICATION ... ADD TABLE и снова REFRESH PUBLICATION, где copy_data по умолчанию true. Последовательность мы сначала прогнали на тестовой копии, и вам советую сделать так же.

Итог по срокам: два рабочих дня на диагностику и исправление, одно окно в воскресенье. Результат был не только в исправленных данных. Мы отозвали у учётных записей отчётной группы права INSERT, UPDATE, DELETE и TRUNCATE на таблицы подписчика, добавили в мониторинг дельту тихих счётчиков и недельную сверку хешей по ключевым таблицам. Сумма в отчёте сошлась с кассой. Мы ничего не взломали и не переписывали, мы выключили возможность тихо портить данные.

Что настроить, чтобы тихие конфликты больше не копились

Приоритеты у меня такие. Сначала права: подписчик для чтения должен быть недоступен на запись всем, кроме процессов репликации. Пример для отчётной роли:

REVOKE INSERT, UPDATE, DELETE, TRUNCATE
  ON ALL TABLES IN SCHEMA public FROM report_user;

По документации CREATE SUBSCRIPTION при run_as_owner = false (значение по умолчанию) воркеры репликации выполняют действия от имени владельца каждой таблицы, так что отзыв прав у обычных ролей репликацию не ломает. Это самая дешёвая мера, и она закрывает главную причину расхождений.

Второе, track_commit_timestamp = on на подписчике до появления проблем, а не после. Третье, мониторинг дельты счётчиков из pg_stat_subscription_stats. В Zabbix это одна пользовательская проверка с запросом выше, а мониторинг на Zabbix с триггером на рост confl_update_missing и confl_delete_missing настраивается за вечер. Четвёртое, регулярная сверка хешей по таблицам, где цена ошибки высока, то есть по деньгам, записям и договорам. Пятое, регламент на изменение схемы: сначала подписчик, потом публикатор, и только аддитивно.

Что можно не делать. Не надо городить самописный «автоисправитель» расхождений, который сам переписывает строки на подписчике: он будет создавать конфликты origin_differs и выдавать это за порядок. Не надо включать binary = true ради скорости, не прочитав ограничения: по документации начальной синхронизации нужны функции send и receive для всех типов данных, иначе синхронизация упадёт. И не надо считать репликацию резервной копией: ошибочное удаление она тоже честно реплицирует. Если у вас в схеме обновление системы, то порядок действий без простоя описан в материале про обновление 1С без простоя, там та же логика окон и проверок.

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

Частые вопросы