Однажды появилась задача синхронизации двух баз данных, работающих под управлением СУБД Firebird. Ситуация вкратце такова.
Есть программа учета, которая работает в двух магазинах, расположенных в нескольких километрах друг от друга. Подключение к интернету — через разных провайдеров с соответствующей нашим реалиям надежностью и скоростью. Сменить провайдера в каждом из случаев можно только на более дорогого с худшим качеством связи, так что размещение базы только в одном из магазинов и удаленное подключение из другого не получается ни под каким соусом. В каждом из магазинов вносятся приходные и расходные документы, редактируются справочники и ведется учет прочей хозяйственной деятельности. Учитывая, что вводимая информация имеет какую-ни-какую, а все же коммерческую ценность, вопрос безопасности передаваемых данных также нельзя игнорировать. Получив примерно такую вводную, пошел думать. Результат раздумий представляю на суд сообщества.
Для начала — реплицироваться будут не все данные. Есть служебные таблицы, есть таблицы логов, есть реестры и журналы, которые формируются автоматически из таблиц первичной документации — их надо исключать. Так что сперва определяемся, что мы будем синхронизировать. Для этого в таблице RPL_TABLES содержатся названия синхронизируемых таблиц и служебная информация — нужно ли реплицировать все поля, и является ли эта таблица шапкой документа (это влияет на логику работы базы данных). В таблице RPL_FIELDS, соответственно, хранятся названия полей тех таблиц, которые нужно синхронизировать не полностью.
Далее были написаны три процедуры, которые на основании этой информации генерируют необходимые тригеры для логирования всех изменений в указанных таблицах. Две из них — RPL_ALLFIELDS и RPL_SOMEFIELDS — генерируют тригеры, которые в свою очередь геренерируют sql-запросы для выполнения другими участниками репликации. Самое трудное при их написании было не ошибиться в количестве кавычек :) Третья — RPL_INSTALL — анализирует вышеупомянутые таблицы настроек и запускает для из них нужную процедуру. При написании этих процедур использовалась связка execute statement / execute block, которая, кроме всего прочего, позволяет изнутри процедуры изменять метаданные базы.
В результате в таблице RPL_LOG сохраняются все изменения, сделанные пользователями программы независимо от того, где, когда и как они (пользователи) их (изменения) внесли. Теперь переходим к взаимодействию между несколькими базами данных.
Синхронизация проводится по схеме «звезда»: каждая база данных подключается к серверу синхронизации, заливает ему свои изменения с момента последнего сеанса синхронизации, получает наборы изменений от других баз-клинтов, применяет их себе и отключается. Сервер работает сам с собой по точно такой же схеме, являясь одновременно и клиентом.
В деталях это происходит так. В каждой базе-клиенте есть таблица RPL_SESSIONS. В начале сеанса репликации в ней делается отметка он начале новой сессии, идентификатор которой хранится в таблице RPL_LOG для каждого сгенерированного запроса. После этого из таблицы RPL_LOG выбираются все накопившиеся запросы из предыдущей сессии, записываются в файл (для дальнейшего анализа в случае проблем и просто на всякий случай) и заливаются на сервер в таблицу RPL_BLOB. Вместе с блобом пишется идентификатор базы данных и номер сессии. Дальше из сервера выкачиваются аналогичные блобы от других баз и применяются к своей базе данных. После этого в таблицу RPL_DATABASES клиента записывается информация о номере последней успешно синхронизированной сессии для каждой из баз-соседей и (опять же, на случай анализа проблем) в таблицу RPL_RECEIVED записываются идентификаторы базы, блоба, сессии и время синхронизации.
Межбазовое взаимодействие сделано с помощью простого скрипта на питоне. Фактически это мой первый работающий скрипт на питоне, так что за подход, синтаксис и кривизну решений прошу сильно не ругать. Скрипт условно можно разделить на две части — класс для упрощения работы с базой и последовательное выполнение вышеописанных запросов.
Данный скрипт выполняется системным планировщиком каждые 15 минут и, как показала практика, удачно обрабатывает невозможность соединения и обрывы связи во время синхронизации. В первом случае он просто вылетает по таймауту, а во втором при попытке вставить уже имеющиеся данные база отбрасывает их по ограничению первичного ключа.
Для обеспечения безопасности между серверами, на которых работают базы данных, поднят VPN и вся работа проводится только через него. Кроме того, для репликации создан отдельный пользователь в базе данных, у которого есть права на чтения только таблиц RPL_*.
На данный момент репликация не умеет коррекно разрешить ситуацию, когда в разных базах одновременно изменяются одни и те же данные. При существующем подходе две базы данных просто «обменяются информацией»: в первой базе после репликации сохранятся данные из второй, во второй, соответственно, те, которые были внесены в первой. Сейчас такое ограничение для нас несущественно, поскольку одинаковые данные в разных базах редактируются крайне редко и, как правило, одним человеком. Так что пока кто-то доберется из магазина в магазин, скорее всего уже отработает очередной сеанс репликации. Но, тем не менее, проблема есть, и она будет решатся.
Создано решение, которое обеспечивает приемлемую для нас скорость и надежность при необходимости синхронизировать данные между удаленными серверами, соединенными ненадежными каналами связи. При этом решение не привязано к конкретной базе* и может быть легко использовано для других баз данных практически без изменений. Более того, решение самодостаточно в том смысле, что сервер синхронизации может быть совершенно отдельной базой данных, в которой будут только таблицы RPL_*. Такое может пригодится, если все сервера баз данных, которые надо синхронизировать, находятся за NAT-ами и нет возможности их оттуда вывести (пример — мобильный интернет в Украине). Кроме того, решение не требует изменений в программах, работающих с базой данных, и не зависит от ОС, установленной на сервере БД — если только там смогут запустится firebird, python и (опционально) vpn.
* Фактически, единственная привязка — опция is_docheader в таблице RPL_TABLES, которая подразумевает наличие в соответствующей таблице поля commited и генерирования запроса при изменении только этого поля, игнорируя любые другие изменения в таблице.
Желающие опробовать решение у себя могут скачать полный SQL-скрипт и питоновский клиент цельными файлами. Буду благодарен за идеи, критику и найденные баги.
Есть программа учета, которая работает в двух магазинах, расположенных в нескольких километрах друг от друга. Подключение к интернету — через разных провайдеров с соответствующей нашим реалиям надежностью и скоростью. Сменить провайдера в каждом из случаев можно только на более дорогого с худшим качеством связи, так что размещение базы только в одном из магазинов и удаленное подключение из другого не получается ни под каким соусом. В каждом из магазинов вносятся приходные и расходные документы, редактируются справочники и ведется учет прочей хозяйственной деятельности. Учитывая, что вводимая информация имеет какую-ни-какую, а все же коммерческую ценность, вопрос безопасности передаваемых данных также нельзя игнорировать. Получив примерно такую вводную, пошел думать. Результат раздумий представляю на суд сообщества.
Работа изнутри
Для начала — реплицироваться будут не все данные. Есть служебные таблицы, есть таблицы логов, есть реестры и журналы, которые формируются автоматически из таблиц первичной документации — их надо исключать. Так что сперва определяемся, что мы будем синхронизировать. Для этого в таблице RPL_TABLES содержатся названия синхронизируемых таблиц и служебная информация — нужно ли реплицировать все поля, и является ли эта таблица шапкой документа (это влияет на логику работы базы данных). В таблице RPL_FIELDS, соответственно, хранятся названия полей тех таблиц, которые нужно синхронизировать не полностью.
Далее были написаны три процедуры, которые на основании этой информации генерируют необходимые тригеры для логирования всех изменений в указанных таблицах. Две из них — RPL_ALLFIELDS и RPL_SOMEFIELDS — генерируют тригеры, которые в свою очередь геренерируют sql-запросы для выполнения другими участниками репликации. Самое трудное при их написании было не ошибиться в количестве кавычек :) Третья — RPL_INSTALL — анализирует вышеупомянутые таблицы настроек и запускает для из них нужную процедуру. При написании этих процедур использовалась связка execute statement / execute block, которая, кроме всего прочего, позволяет изнутри процедуры изменять метаданные базы.
В результате в таблице RPL_LOG сохраняются все изменения, сделанные пользователями программы независимо от того, где, когда и как они (пользователи) их (изменения) внесли. Теперь переходим к взаимодействию между несколькими базами данных.
Синхронизация проводится по схеме «звезда»: каждая база данных подключается к серверу синхронизации, заливает ему свои изменения с момента последнего сеанса синхронизации, получает наборы изменений от других баз-клинтов, применяет их себе и отключается. Сервер работает сам с собой по точно такой же схеме, являясь одновременно и клиентом.
В деталях это происходит так. В каждой базе-клиенте есть таблица RPL_SESSIONS. В начале сеанса репликации в ней делается отметка он начале новой сессии, идентификатор которой хранится в таблице RPL_LOG для каждого сгенерированного запроса. После этого из таблицы RPL_LOG выбираются все накопившиеся запросы из предыдущей сессии, записываются в файл (для дальнейшего анализа в случае проблем и просто на всякий случай) и заливаются на сервер в таблицу RPL_BLOB. Вместе с блобом пишется идентификатор базы данных и номер сессии. Дальше из сервера выкачиваются аналогичные блобы от других баз и применяются к своей базе данных. После этого в таблицу RPL_DATABASES клиента записывается информация о номере последней успешно синхронизированной сессии для каждой из баз-соседей и (опять же, на случай анализа проблем) в таблицу RPL_RECEIVED записываются идентификаторы базы, блоба, сессии и время синхронизации.
Работа снаружи
Межбазовое взаимодействие сделано с помощью простого скрипта на питоне. Фактически это мой первый работающий скрипт на питоне, так что за подход, синтаксис и кривизну решений прошу сильно не ругать. Скрипт условно можно разделить на две части — класс для упрощения работы с базой и последовательное выполнение вышеописанных запросов.
Данный скрипт выполняется системным планировщиком каждые 15 минут и, как показала практика, удачно обрабатывает невозможность соединения и обрывы связи во время синхронизации. В первом случае он просто вылетает по таймауту, а во втором при попытке вставить уже имеющиеся данные база отбрасывает их по ограничению первичного ключа.
Для обеспечения безопасности между серверами, на которых работают базы данных, поднят VPN и вся работа проводится только через него. Кроме того, для репликации создан отдельный пользователь в базе данных, у которого есть права на чтения только таблиц RPL_*.
Совершенствование
На данный момент репликация не умеет коррекно разрешить ситуацию, когда в разных базах одновременно изменяются одни и те же данные. При существующем подходе две базы данных просто «обменяются информацией»: в первой базе после репликации сохранятся данные из второй, во второй, соответственно, те, которые были внесены в первой. Сейчас такое ограничение для нас несущественно, поскольку одинаковые данные в разных базах редактируются крайне редко и, как правило, одним человеком. Так что пока кто-то доберется из магазина в магазин, скорее всего уже отработает очередной сеанс репликации. Но, тем не менее, проблема есть, и она будет решатся.
Итоги
Создано решение, которое обеспечивает приемлемую для нас скорость и надежность при необходимости синхронизировать данные между удаленными серверами, соединенными ненадежными каналами связи. При этом решение не привязано к конкретной базе* и может быть легко использовано для других баз данных практически без изменений. Более того, решение самодостаточно в том смысле, что сервер синхронизации может быть совершенно отдельной базой данных, в которой будут только таблицы RPL_*. Такое может пригодится, если все сервера баз данных, которые надо синхронизировать, находятся за NAT-ами и нет возможности их оттуда вывести (пример — мобильный интернет в Украине). Кроме того, решение не требует изменений в программах, работающих с базой данных, и не зависит от ОС, установленной на сервере БД — если только там смогут запустится firebird, python и (опционально) vpn.
* Фактически, единственная привязка — опция is_docheader в таблице RPL_TABLES, которая подразумевает наличие в соответствующей таблице поля commited и генерирования запроса при изменении только этого поля, игнорируя любые другие изменения в таблице.
Желающие опробовать решение у себя могут скачать полный SQL-скрипт и питоновский клиент цельными файлами. Буду благодарен за идеи, критику и найденные баги.