Pull to refresh

Мультимастер репликация для firebird на python

Programming
Sandbox
Однажды появилась задача синхронизации двух баз данных, работающих под управлением СУБД Firebird. Ситуация вкратце такова.

Есть программа учета, которая работает в двух магазинах, расположенных в нескольких километрах друг от друга. Подключение к интернету — через разных провайдеров с соответствующей нашим реалиям надежностью и скоростью. Сменить провайдера в каждом из случаев можно только на более дорогого с худшим качеством связи, так что размещение базы только в одном из магазинов и удаленное подключение из другого не получается ни под каким соусом. В каждом из магазинов вносятся приходные и расходные документы, редактируются справочники и ведется учет прочей хозяйственной деятельности. Учитывая, что вводимая информация имеет какую-ни-какую, а все же коммерческую ценность, вопрос безопасности передаваемых данных также нельзя игнорировать. Получив примерно такую вводную, пошел думать. Результат раздумий представляю на суд сообщества.



Работа изнутри


Репликация 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-скрипт и питоновский клиент цельными файлами. Буду благодарен за идеи, критику и найденные баги.
Tags:репликациябазы данныхfirebirdpython
Hubs: Programming
Total votes 6: ↑4 and ↓2 +2
Views8.1K

Comments 11

Only those users with full accounts are able to leave comments. Log in, please.

Popular right now

Middle Python Engineer
from 150,000 to 210,000 ₽Spark EquationRemote job
DBA | Администратор баз данных
from 200,000 to 300,000 ₽СберМосква
Разработчик Python (Middle, Full-Stack)
from 120,000 ₽Artel of Business AutomationRemote job
Ведущий администратор баз данных
from 80,000 ₽ТатнефтьАльметьевск
Database Support Engineer
from 150,000 to 400,000 ₽AltinityНовосибирскRemote job

Top of the last 24 hours