Прошу совета у гуру.

Есть две БД одинаковой структуры и самописный репликатор. В процессе репликации нужно сверять таблицы (точнее, куски таблиц с фильтром по foreign key) на соответствие данных, и, при отличии, - реплицировать. При этом отличается обычно не более ~10% данных, т.е. большинство кусков пропускается без репликации.

Нужен быстрый алгоритм сверки. Сверять все поля во всех записях - долго. Сверять ключи - недостаточно (update'ы пропустим). Пока в каждую таблицу добавлено поле

lmts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

обновляемое триггером при каждом update'е. Есть идея брать хеш (например, CRC32) этого поля во всех записях, и считать разный хеш признаком отличия данных.

Собсно, вопросы:
1) какова вероятность ошибки, т.е. одинакового хеша при разных данных, на ~10000 записях?
2) есть ли в PG быстрая хеш-функция для целых чисел, типа CRC32? (MD5 не катит)

Или, может, кто-нибудь решал подобную задачу как-то иначе. Буду рад совету...

Сначала объясню, почему выбран именно этот путь.

В штатном режиме приложение пишет данные "зеркально" в 2 БД, а читает из них поочередно, балансируя нагрузку. Репликация же идет не в рантайме, а только в процессе восстановления, если одна из БД упала, и ее потом подняли. Т.е. фактически надо перенести из оставшейся рабочей БД в помершую, разницу - за несколько часов это не более 1-2% данных (10% - с запасом). Эдакий diff :-)

Реплицировать кусками, соответствующими данным 1 пользователя, потому что приложение определяет пользователей, чьи данные разошлись и переводит их в read-only режим, а потом "пускает" по мере синхронизации соотв. кусков.

В итоге теряем в скорости репликации (которая происходит чем реже, тем лучше :-) и выигрываем на отсутствии лога действий, который бы велся постоянно. Вариант с логом не катит, т.к. дает 3-кратный оверхед в рантайме.

_Андрей_МЯ бы, наверное, использовал sequence. При создании таблицы -
на апдейт - триггер.

Я, наверное, плохо объяснил: у нас уникальные ID и так есть. И триггер на апдейт поля lmts (Last Modification TimeStamp) тоже есть.

Засада в том, что если тупо втягивать и сверять наборы ID и lmts из обоих таблиц, то это почти гарантирует вылет по памяти - их там дофига. И долго. Нужна функция внутри PG, которая быстро посчитает хеш от всех полей lmts (скажем, их 100000). Если он разный в 2 БД, значит и данные различаются - надо реплицировать.

ИМХО, это соответствует проверке CRC для файлов, т.е. достаточно надежно. Или я что-то упустил?

_Андрей_МА зачем тупо проверять? Если нумерация инкрементная, смотрим макс. uniq_code у упавшей базы и восстанавливаем всё, что больше макс.

Примерно так и было сделано, только вместо инкремента - lmts = now().

Выяснилось, что пропускаются:
- удаление записей, т.к. оно не влияет на ID/lmts,
- старые данные.

Первая проблема решена отдельной таблицей с историей удалений и опять же триггером на DELETE. А вот вторая... Например, БД1 запущена месяц назад (боевой сервер), а БД2 - сегодня (резервный). Данные из БД1 с момента старта по вчерашний день таким образом не отловить.

Andrey Daeron[quot Oleg Zhovtanyuk]

...

Ну тогда можно:
1. Триггер логирующий в таблицу когда и где модифицирована запись.
2. Некую таблицу, в которой вести некое "отслеживание" жизнеспособности. Из серии "13:00", "полет нормальный", БД1. В которой этот полет нормальный и время отслеживались бы системой репликацией в фоновом режиме - раз в час, два - как захотите. Желательно перед бекапом.
3. При падении базы. Для простоты пусть рухнула БД2, жива БД1.
3.1 Поднимаем (из бекапа?) БД2.
3.2 В БД2 у нас есть время когда "было хорошо" ТА (п. 2)
3.3 В БД1 запрашиваются данные по всем таблицам, которые имеют дату модификации больше ТА, и модифицированы в БД 1.

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

Логично. БД2 и есть бекап для БД1 и наоборот - это классический RAIDb0 (mirroring).

1. Триггер как раз и пишет lmts = now().
2. "Отслеживание жизнеспособности" возложено на драйвер, который пингует обе БД каждые 15 сек.
3. ...
3.1 Угу...
3.2 Это максимальный lmts по всем таблицам. Выведен через вьюху.
3.3. ... и благополучно пропускаются старые данные в БД1, если lmts(БД1) = lmts(БД2). Репликатор считает базы синхронными, о чем бодро и рапортует.

Т.е. мне надо проверить не только последний ID/lmts, а соответствие всех lmts в таблице. Для чего и хочу хеш-функцию.

Andrey Daeron Oleg Zhovtanyuk
1. Триггер как раз и пишет lmts = now().
2. "Отслеживание жизнеспособности" возложено на драйвер, который пингует обе БД каждые 15 сек.
3. ...
3.1 Угу...
3.2 Это максимальный lmts по всем таблицам. Выведен через вьюху.
3.3. ... и благополучно пропускаются старые данные в БД1, если lmts(БД1) = lmts(БД2). Репликатор считает базы синхронными, о чем бодро и рапортует.

Т.е. мне надо проверить не только последний ID/lmts, а соответствие всех lmts в таблице. Для чего и хочу хеш-функцию.
3.2 - ну пусть будет так
Бррр. Не понял. А когда тогда и что навернулось?
Откуда возьмется lmts(БД1) = lmts(БД2), если БД2 рухнула после "максимального lmts"?
До этого момента гарантировано все хорошо. Если нет - то приведите пример такой модификации.
В принципе - можно ли зафксировать момент когда "все хорощо" и данные на этот момент синхронны полностью?

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

ЗЫ И что Вы будете делать если будет нужно сравнить 10^6 записей? Даже по хешу? Это слишком много сравнений.

Навернулся на взлете резервный сервер приложений вместе с репликатором, который подключили к основному, при этом БД осталась жива. Основной сервер (он нацелен на ту же пару БД) пустил пользователя и изменил одну из таблиц (не суть, какую, скажем - статистику логинов). Все, lmts одинаковы - они же считаются по последней обновленной записи. После починки упавшего сервера его снова запускают, стартует репликатор, видит, что БД синхронны и радостно говорит, что ему делать нечего. Все. На основном сервере данные за месяц. На резервном их нет.

Под хешем я имею в виду функцию от всех записей, так что для любого их числа он 1. И сравнение, соответственно, одно. Стоимость его вычисления - другой вопрос ;-)

Удаление из приложения у нас вообще асинхронное, чтобы балансировать нагрузку: ставим флаг, чтобы исключить записи из SELECT'ов, а потом в фоне потихоньку удаляем.
Двухфазный коммит - не катит, слишком тормозит в рантайме, плюс тот, кто его осуществляет (скажем, JBoss) - это лишний сервер, который тоже может упасть. Не буду поминать Оккама всуе...
Поэтому за синхронное обновление/удаление отвечает сам JDBC-драйвер.

Спасибо всем отозвавшимся за идеи и помощь.

Проблема решена.

Поковырявшись, окончательно убедился, что хеш не спасает. Выполненный из драйвера на паре БД now() дает различные данные из-за сетевого лага, так что по lmts хешировать нельзя. По ID - тоже, он не меняется при UPDATE'е.

В итоге просто взял не только время последней (lmts), но и первой (fmts) модификации данных. Примерно так:
по всем таблицам. И потом
и

БД считаю верными, если совпадают оба значения, если совпадает только lmts, то это как раз сбой на взлете, описанный в предыдущем посте. Тогда просто ругаюсь и требую очистки резервной БД и перезапуска. При этом идет полная репликация, которая переносит все данные. Работает долго, но это ведь и очень редкий случай.