Одного разу з'явилася задача синхронізації двох баз даних, що працюють під управлінням СУБД 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-скрипт і пітонівський клієнт цільними файлами. Буду вдячний за ідеї, критику і знайдені баги.
