После обновления ОС PostgreSQL ругается на collation: почему REFRESH VERSION не чинит индексы

REFRESH VERSION не исправляет индексы: он только записывает в каталог новую версию collation, и предупреждение пропадает. Порядок сортировки в уже построенных индексах он не проверяет и не меняет. Ниже: как найти зависимые индексы, проверить их через amcheck, перестроить через REINDEX и только потом обновить версию.

Что на самом деле делает ALTER COLLATION REFRESH VERSION

Начну с самого частого сценария. Сисадмин обновил ОС, перезапустил PostgreSQL, в логе появилось предупреждение о том, что у collation расходятся версии. Гуглит, находит команду ALTER COLLATION ... REFRESH VERSION, выполняет её, предупреждение исчезает, тикет закрыт. Я видел это десятки раз, когда мы подключались к серверам как обслуживающая команда PostgreSQL под 1С, и каждый раз меня удивляет одно: пропавшая строка в логе воспринимается как справка о здоровье базы. Это не справка. Это выключенная сигнализация.

Механика простая. Когда создаётся объект collation, провайдер записывает его версию в системный каталог. При каждом использовании PostgreSQL сравнивает записанную версию с текущей и при несовпадении выдаёт предупреждение. Документация PostgreSQL 18 приводит пример: WARNING о том, что collation создан версией 1.2.3.4, а операционная система даёт 2.3.4.5, плюс подсказка перестроить все зависимые объекты и выполнить ALTER COLLATION ... REFRESH VERSION. Обратите внимание на порядок в этой подсказке: сначала перестроить, потом обновлять. Команда REFRESH VERSION лишь обновляет запись в каталоге, и в документации прямо сказано, что она не проверяет, были ли зависимые объекты перестроены корректно.

Почему вообще это важно. B-tree индекс по текстовому полю хранит ключи в определённом порядке, и поиск по нему опирается на то, что порядок совпадает с правилами сортировки. Если после обновления ОС правила поменялись, индекс остаётся в старом порядке, а сравнения идут по новому. Документация называет это прямо: изменение определений collation может привести к повреждённым индексам и другим проблемам, а случается это, в частности, при обновлении ОС на новую мажорную версию или при pg_upgrade на бинарники, собранные с более новым ICU. Лечение там же: перестроить всё, что зависит от collation, например через REINDEX, и только затем обновлять версию.

Отдельный нюанс про glibc. Для libc-collation PostgreSQL использует версию самой библиотеки C как заменитель версии collation. Документация честно оговаривает, что это приближение: многие дистрибутивы меняют определения сортировки только вместе с версией библиотеки, но сопровождающие вправе перенести новые определения и в старый релиз. То есть предупреждения может не быть, а сортировка уже другая. И наоборот. Поэтому ориентироваться только на сообщение в логе нельзя.

Как найти collation и индексы, которых коснулось обновление

Первым делом выясните, чем вообще сортирует ваша база. У каждой базы есть провайдер локали, и в PostgreSQL 18 он хранится в pg_database в столбце datlocprovider: b для builtin, c для libc, i для ICU. Рядом лежит записанная версия datcollversion. Фактическую версию, которую сейчас отдаёт система, возвращает функция pg_database_collation_actual_version. Если значения различаются, то объекты, зависящие от collation базы, возможно, надо перестраивать. Столбец datlocale появился в PostgreSQL 17 вместе с провайдером builtin; в 15-й и 16-й он назывался daticulocale, так что там замените его в запросе. А на 14-й и старше нет ни datcollversion, ни этой функции: они появились в 15-й версии, и версию collation базы там штатно не отследить.

SELECT datname, datlocprovider, datcollate, datctype, datlocale,
       datcollversion,
       pg_database_collation_actual_version(oid) AS actual_version
FROM pg_database;

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

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS collation,
       pg_describe_object(classid, objid, objsubid) AS object
  FROM pg_depend d JOIN pg_collation c
       ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
  WHERE c.collversion <> pg_collation_actual_version(c.oid)
  ORDER BY 1, 2;

Чтобы увидеть индексы, чувствительные к сортировке, я пользуюсь запросом из вики PostgreSQL про изменения локали: он перечисляет индексы, у которых в определении участвует collation провайдера libc или collation по умолчанию, кроме C и POSIX. Из вики же полезно знать, что затронуты B-tree индексы по text, varchar и char, а числовые типы, bytea и GIN по tsvector не затронуты, как и столбцы с локалью C или POSIX. Это сильно сужает объём работ: в типичной базе нужен не абсолютно весь REINDEX, но ради простоты и надёжности я всё равно перестраиваю всё целиком, об этом ниже.

SELECT DISTINCT indrelid::regclass::text, indexrelid::regclass::text, collname,
       pg_get_indexdef(indexrelid)
FROM (SELECT indexrelid, indrelid, indcollation[i] coll
      FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s
  JOIN pg_collation c ON coll = c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');

Как проверить индексы через amcheck и почему тишина ничего не доказывает

Расширение amcheck из штатной поставки умеет проверять структуру B-tree индексов и прямо упоминает среди обнаруживаемых проблем несовместимости из-за смены правил сортировки в ОС, в том числе расхождение между primary и standby после крупного обновления ОС. Ставится оно командой CREATE EXTENSION amcheck в каждой базе, где нужны функции. Привилегии на выполнение не раздавайте налево: сообщения об ошибках могут раскрывать данные.

Есть две функции, и разница между ними критична для боевого сервера. bt_index_check берёт AccessShareLock на индекс и таблицу, то есть ту же блокировку, что и обычный SELECT, поэтому подходит для работающей системы и работает на hot standby. bt_index_parent_check строже, но берёт ShareLock, блокирует INSERT, UPDATE, DELETE и VACUUM на время проверки и на hot standby использовать нельзя. Параметр heapallindexed включает дополнительную фазу проверки соответствия индекса таблице; по документации она обычно занимает в несколько раз больше времени и ограничена памятью maintenance_work_mem.

CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT c.relname, bt_index_check(index => c.oid, heapallindexed => false)
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE am.amname = 'btree'
  AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND c.relpersistence <> 't'
  AND i.indisready AND i.indisvalid;

Запрос выше собран по образцу из документации amcheck. Функция при обнаружении нарушения выбрасывает ошибку, так что запрос остановится на первом проблемном индексе. Для полной картины прогоняйте проверку индекс за индексом из скрипта и складывайте результат в файл. И главное: документация формулирует жёстко, amcheck может доказать наличие повреждения, но не его отсутствие. Часть затронутых индексов может вообще не показать проблем, если реальные значения случайно сортируются одинаково. Чистый прогон означает «ничего не нашли», а не «всё исправно». Поэтому я использую amcheck как инструмент оценки масштаба и как контроль после REINDEX, но не как основание пропустить перестроение.

В каком порядке чинить: REINDEX, CONCURRENTLY и системные каталоги

Порядок у меня всегда один. Сначала резервная копия и проверка, что из неё действительно можно восстановиться: бэкап делают все, проверяет его почти никто. Затем REINDEX всех баз, зависящих от изменившейся collation. Затем повторная проверка amcheck. И только в самом конце ALTER DATABASE ... REFRESH COLLATION VERSION для collation по умолчанию, а для отдельных collation-объектов ALTER COLLATION ... REFRESH VERSION. Любая перестановка превращает процедуру в ту самую «выключенную сигнализацию».

REINDEX имеет два режима, и выбор между ними определяется окном обслуживания. Обычный REINDEX блокирует запись в таблицу, но не чтение, при этом берёт ACCESS EXCLUSIVE на перестраиваемый индекс, а по документации блокирует фактически почти любые запросы к таблице. REINDEX ... CONCURRENTLY не блокирует вставки, обновления и удаления, но делает два прохода по таблице на каждый индекс, ждёт завершения всех транзакций, которые могли бы использовать индекс, и по общим затратам существенно дольше. Внутри транзакции он не работает, одновременно по одной таблице строится только один индекс, а индексы под exclusion constraint при перестроении всей базы пропускаются. Упавший CONCURRENTLY оставляет невалидные индексы с суффиксом _ccnew, их надо удалить и повторить.

-- вся база кроме системных каталогов; нельзя внутри транзакции
REINDEX DATABASE appdb;

-- без блокировки записи, но дольше
REINDEX DATABASE CONCURRENTLY appdb;

-- системные каталоги отдельно; CONCURRENTLY тут не поддерживается
REINDEX SYSTEM;

Обратите внимание на деталь, которую пропускают чаще всего. REINDEX DATABASE по документации пересоздаёт все индексы в текущей базе, кроме системных каталогов. Каталоги идут отдельной командой REINDEX SYSTEM, и она не поддерживает CONCURRENTLY. Параметр команды должен совпадать с именем текущей базы, перестроить другую базу из той же сессии нельзя, так что подключайтесь к каждой базе по очереди. Для пакетной работы удобна утилита reindexdb: у неё есть --all, --dbname, --concurrently, --jobs, --system и --verbose. Ключ --jobs ускоряет процесс, но поднимает нагрузку и открывает по соединению на задачу, так что проверьте max_connections; с --system он несовместим.

sudo -u postgres reindexdb --dbname=appdb --jobs=4 --verbose
sudo -u postgres reindexdb --dbname=appdb --system

Если вы хотите понять, как выбирать окно и параметры в целом для 1С, у нас есть разбор полной оптимизации PostgreSQL под 1С, там же про память и автовакуум. А здесь коротко: на базе до сотни гигабайт полный REINDEX в ночное окно с остановленной 1С устраивает почти всех. CONCURRENTLY я беру, когда окна нет вообще, и честно предупреждаю, что он затянется. Если хотите, чтобы это сделали без ваших бессонных ночей, напишите мне: проведём аудит, перестроим и проверим за один вечер, а вы утром откроете базу как обычно.

После REINDEX и повторной проверки обновляем версию. Для collation базы по умолчанию:

ALTER DATABASE appdb REFRESH COLLATION VERSION;

Для отдельного collation-объекта, который показал запрос из раздела 2, используется ALTER COLLATION имя REFRESH VERSION. Для готовых системных collation в схеме pg_catalog имя пишется с указанием схемы, как в примере из документации PostgreSQL. После этого повторите запросы из раздела 2: расхождения версий быть не должно, а в логе перестанут появляться предупреждения. Если расхождение осталось, вы обновили не то или не в той базе.

Почему одинаковая версия ICU на primary и standby ничего не гарантирует

Любимая фраза при разборе инцидента: «У нас на обоих серверах одинаковая версия ICU, значит, сортировка одинаковая». Проверьте сначала, какой провайдер реально используется. Если база создана с провайдером libc, то вопрос о сортировке решает не ICU, а glibc конкретной ОС, и версия ICU на сервере вообще не при чём. Документация PostgreSQL к тому же отдельно подчёркивает, что для glibc версия библиотеки служит лишь заместителем версии collation. Поэтому сравнивайте не версию пакета, а столбцы datlocprovider и datcollversion на обеих машинах, и фактическую версию, которую возвращает функция на каждом из серверов.

С репликой ситуация хитрее, чем кажется. Физическая репликация копирует файлы индексов как есть. REINDEX на primary попадёт на standby в готовом виде, и это хорошо. Но запросы, которые идут на standby, сравнивают ключи по правилам той ОС и той библиотеки, что стоят на standby. Если вы обновили ОС на primary, а реплику оставили на старой версии, индекс перестроен по новым правилам, а читатель на реплике применяет старые. Документация amcheck прямо называет такой сценарий: ошибки сортировки появляются на standby после крупного обновления ОС. Поэтому порядок обновления у меня такой: обновляем ОС на всех узлах, сверяем фактические версии, затем перестраиваем на primary и проверяем bt_index_check на реплике, потому что именно он разрешён на hot standby.

Отдельно скажу про pg_upgrade. Если новые бинарники собраны с более новой ICU, то это тот же случай, что и обновление ОС: документация относит его к законным причинам смены collation. А вот логическая репликация и pg_dump с восстановлением от этого не страдают, потому что индексы строятся заново на целевой системе. Вики PostgreSQL по изменениям локали советует именно это, если не хочется простоя на перестроение: переезд через логическую репликацию. Физический бэкап (pg_basebackup и аналоги), восстановленный на системе с другой версией glibc или ICU, наоборот, в зоне риска: индексы в нём построены по старым правилам.

Если вы создаёте новую базу и хотите уйти от зависимости от ОС, посмотрите на провайдер builtin, появившийся в PostgreSQL 17: в каталогах у него собственный код b, и от glibc и ICU он не зависит. Какие именно гарантии стабильности он даёт и подходит ли он под ваши требования к сортировке по-русски, проверьте в разделе документации про поддержку локали своей версии, прежде чем переносить продуктивную базу. Миграция на другого провайдера локали без пересоздания базы — не та операция, которую стоит затевать в одиночку на проде.

Разбор: школа единоборств «Путь воина», 46 рабочих мест

Условный клиент для примера: школа единоборств «Путь воина», 46 рабочих мест в нескольких залах, 1С на PostgreSQL, два сервера: основной и реплика. Администратор у них по совместительству, и он сделал всё по классике: обновил ОС с Ubuntu 22.04 до 24.04, увидел предупреждение о collation в логе, прочитал подсказку и выполнил ALTER DATABASE ... REFRESH COLLATION VERSION. Предупреждение пропало, а через неделю бухгалтерия пожаловалась, что в списке контрагентов поиск иногда не находит записи, которые точно есть. Нас позвали с формулировкой «1С глючит».

Дальше я не буду рисовать драму, которой не было: чем закончится такой случай, зависит от конкретных данных. Мы поступили по процедуре из этой статьи. Первое: свежая копия базы объёмом около 38 ГБ и пробное восстановление на тестовой машине. Второе: сверка datlocprovider и datcollversion на обоих серверах. Третье: bt_index_check по всем B-tree индексам вне системных схем, результат в файл. Четвёртое: ночное окно в субботу, 1С остановлена, REINDEX DATABASE и REINDEX SYSTEM, около трёх часов вместе с запасом на то, чтобы реплика догнала primary по WAL после перестроения. Пятое: повторная проверка amcheck уже на реплике. Шестое: сверка версий функциями, о которых шла речь выше.

Отдельным шагом идёт проверка уже самой 1С. После перестроения индексов прогоните тестирование и исправление базы штатными средствами платформы: у нас есть материал про тестирование и исправление базы 1С через chdbfl, там же описано, чем отличается проверка файловой базы от клиент-серверной и чего от неё ждать. Для клиента мы также зафиксировали правило: обновление ОС на серверах баз данных теперь идёт только по чек-листу, где пункт «collation» стоит до пункта «перезагрузка», а не после него.

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

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