Версионирование базы данных на лету

Здравствуйте, меня зовут Евгений, и я веб разработчик. Несколько лет назад мне перепала функция DBA (Database Administrator), я получил по этому поводу несколько сертификатов и решал соответствующие задачи. Я давно хотел описать задачу версионирования базы данных, но мне казалось, что для этого должны быть какие-то беспроигрышные варианты, которые хорошо знают умелые дяди, а я просто чего-то недопонимаю. Вчерашнее собеседование и последующий поиск по тематическим ресурсам показал, что это не так, и задача действительно сложна, актуальна и не решается однозначно. Разберём её по пунктам.

Что мы версионируем


Мы используем контроль версий только для DDL (Data Definition Language) запросов. Сами данные нас не интересуют. Почему? Рассмотрим два крайних случая.

  1. Данных мало (скажем, менее 50 мегабайт). В этом случае, мы можем просто периодически делать полный дамп базы и смело складывать его в репозиторий.
  2. Данных много (больше гигабайта). В этом случае версионирование нам мало поможет, всё равно разобраться в этом будет довольно проблематично. Целесообразно в данном случае использовать стандартную схему с бекапами и архив логом, которая позволяет нам получить целостную версию базы на любой момент во времени.


Зачем нам версионировать DDL?


Если вы работаете со сложной базой данных, то таблицы, как ни странно, в ней наименее интересны (хотя тоже должны быть под контролем версий). Гораздо сложнее приходится с бизнес логикой, которая содержится в триггерах, представлениях, пакетах и процедурах, и тому подобных объектах. К примеру, в одной из баз, с которыми я работал, были пакеты размером до полутора мегабайт. В эти пакеты постоянно вносятся правки, и жизненно необходимо знать, кто внёс правки, когда, желательно знать, зачем, и как бы нам это откатить до любого нужного состояния.

Идеальный мир


Представим себе идеальный мир, в котором у нас есть чёткое ТЗ, которое не меняется до завершения проекта. Релиз, после которого мы забываем о том, что делали, и получаем регулярную зарплату за красивые глаза. Идеальный код, который мы писали сразу с учётом всех нюансов, который работает без ошибок и не требует сопровождения. Отсутствие доработок, срочных багфиксов, механизмов интеграции, возможность работать на тестовой базе и тестовых выборках, наличие повсеместных юнит тестов, которые говорят, что всё безупречно.

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

Реальный мир


Краткое содержание этой части

Теперь открываем глаза и смотрим вокруг. В большинстве случаев, проект реализуется по схеме, которую я бы назвал СВВ (сделал, выкатил, выкинул). Огромный процент завершённых проектов не может продаться и закрывается без всяких перспектив на будущее. Оставшиеся счастливчики переживают сотни итераций, после которых от изначального ТЗ остаётся в лучшем случае название. В этой реальности нас в первую очередь волнует не скорость работы продукта, его требования и качество. Нас волнует скорость разработки, поскольку, кроме очевидных причин, от неё зависит самая большая часть бюджета проекта — стоимость работы в часах разработки.

Да, это неправильно. Мир жесток, несправедлив, динамичен, и требует мгновенной реакции, даже если при этом страдает качество. Все разработчики стремятся в душе к идеальному коду, но большинство принимает условия сделки с дьяволом, и ищет допустимый компромисс качества и скорости. Мы стараемся делать, как лучше, но учимся не краснеть, если вместо срока в полгода и идеального продукта мы за две недели сделали нестабильное и местами некрасивое решение. Более того, в какой-то момент приходит понимание, что “последний баг” никогда не будет найден, и всё, что мы можем — просто в какой-то момент прекратить его искать и сделать релиз. Доводить решение до идеала — удел простейших приложений и консольных скриптов — да и то часто не удаётся учесть какие-то нетривиальные моменты. Когда же мы говорим о крупных проектах, то пример Oracle, Microsoft и Apple нам показывает, что идеального кода не бывает. Как пример — классический ответ DBA на вопрос о том, что в новом релизе Oracle Database — “убрали 30% старых багов, добавили 40% новых”.

Кто виноват и что делать?


Во что же это выливается, если мы говорим о БД? Обычно это так:

  1. Доступ к базе есть у большого количества разработчиков
  2. Часто есть необходимость откатить тот или иной объект
  3. Никто и никогда не признается, что это именно он сломал объект
  4. Модификации часто носят непонятный характер

Далее, если к DBA приходит разработчик и просит вернуть предыдущую версию его объекта, то DBA может это сделать в трёх случаях (на примере Oracle):

  1. Если предыдущая версия всё ещё сохранилась в UNDO
  2. Если объект был просто удалён и сохранился в мусорной корзине (RECYCLEBIN)
  3. Если он может развернуть полный бекап базы на требуемую дату

Самый реальный вариант — третий. Но он осложняется тем, что зачастую неизвестно, на какую дату нужно выполнить восстановление, и восстановление базы размером, скажем, в 10 террабайт — это довольно долгая и ресурсоёмкая операция. Так что обычно DBA просто разводит руками, разработчик хмуро выпивает кофе и идёт писать своё объект с нуля.

Что мы можем сделать, чтобы упростить жизнь разработчикам? Я вижу единственный вариант — версионировать базу по факту уже совершённых изменений. Естественно, это не даёт никакой возможности предупредить возможные ошибки — зато даст способ в большом проценте случаев вернуть к жизни нужный объект и всю систему.

Реализация на примере Oracle


Первое простое решение “в лоб” — это просто периодически выгружать всю базу. Но выгрузка базы занимает длительное время, и тогда мы не будем знать, кто, когда и что менял. Так что явно требуется что-то сложнее. А именно — нам нужно выгружать только изменённые DDL объекты. Для этого можно использовать два подхода — использовать Audit, или создать системный триггер. Я воспользовался вторым способом. Тогда последовательность получается такая:

  1. Создаём таблицу, в которой будут хранятся данные о DDL запросах
  2. Создаём системный триггер, который будет писать в эту таблицу

При этом для каждого действия мы можем получить довольно подробную информацию, включая полный текст запроса, схему, имя и тип объекта, IP адрес пользователя, сетевое имя его машины, имя пользователя, тип и дату изменений. Как правило, этого хватает, чтобы потом найти разработчика и выдать медаль.

Далее нам хочется иметь репозиторий, в котором в интуитивном виде будет представлена структура базы, чтобы иметь возможность сравнивать различные версии объекта. Для этого, при каждом изменении базы требуется выгрузить изменённые объекты и закоммитить в базу. Ничего сложного! Создаём Git репозиторий, сначала делаем туда полную выгрузку, затем создаём сервис, который мониторит нашу таблицу изменений, и в случае появления новых записей выгружает изменившиеся объекты.

Как это выглядит


Обычное сравнение

Side by side сравнение
Список объектов в схеме
История изменений конкретного объекта
То же самое на github

То есть, у нас есть рабочий инструмент, при помощи которого можно найти источник любых изменений в базе и при необходимости их откатить. В моём случае, наиболее крупный Git репозиторий в Gitlab (свой инстанс на отдельной машине) занимает несколько сотен мегабайт, в нём около сотни тысяч коммитов, и при этом он достаточно шустро работает. До переезда на Gitlab этот же репозиторий отлично жил на github, а потом на bitbucket.

Данные о каких объектах мы после этого имеем:

  1. Таблицы
  2. представления
  3. материализованные представления
  4. триггеры
  5. последовательности
  6. пользователи (с хешами паролей, которые можно использовать для восстановления старого пароля)
  7. пакеты, функции, процедуры
  8. database links (тоже с хешами паролей)
  9. гранты
  10. констрейны с их состоянием
  11. синонимы

Так же можно модифицировать программу под задачу обновления устаревшей базы данных — выгружаем старую версию, поверх неё выгружаем новую, исправляем разницу в полуавтоматическом режиме.

Минусы


  1. Некоторые изменения могут произойти слишком быстро, и сервис не успеет выгрузить промежуточные результаты — но вряд ли они нам актуальны, да и можно их найти в таблице изменений.
  2. Некоторые изменения могут затрагивать сразу несколько объектов — например, удаление схемы или DROP CASCADE — но это тоже можно корректно отработать при желании, вопрос только в реализации.
  3. Из-за того, что в репозитории хранятся хеши паролей, его нельзя выдавать напрямую разработчикам.

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

Ссылка на мой алгоритм для PHP и руководство по установке есть в конце статьи, но я искренне рекомендую вам пользоваться им только для справки — он был написан давно и очень криво левой рукой во время выполнения других задач. Единственный плюс — в том, что, как ни странно, он работает.

Заключение


Искренне желаю вам, чтобы вам не пришлось работать с таким workflow. И надеюсь, что эта публикация вам поможет, если ваш мир всё же далёк от идеального.
А вы версионируете свою базу данных? Правильным образом, или по факту? Может быть, есть реализации под другие СУБД — MySQL, Postgres? Или есть какой-то фундаментально другой хороший подход, который я проглядел?

Ссылки


  1. Большое обсуждение того, как версионировать базу на stackoverflow
  2. Реализация правильного подхода от Liquibase
  3. Аналогичная моей старая реализация на Java + SVN
  4. Веб сайт моего инструмента с инструкциями по установке
  5. Репозиторий кода моего инструмента на github
  6. Взять меня на работу можно тут
Ads
AdBlock has stolen the banner, but banners are not teeth — they will be back

More

Comments 23

    +1
    Для мелкомягких, в SQL Server есть DDL триггеры:

    CREATE TRIGGER DB_SCHEMA_AUDIT 
    ON  database 
    FOR     CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE,
            CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
            CREATE_VIEW, DROP_VIEW, ALTER_VIEW,
            CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
            CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
            CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER
    AS
    ....

    А для контроля версий можно использовать "Redgate SQL Compare". Софтик умеет сравнивать базу данных с например TFS.
      0
      Не только триггерами этом можно делать. Для SQL Server 2008+ использование XEvent более удачным решением было бы. А по поводу контроля версия постоянно использовать компаратор не комильфо. Ужа давно существуют сорс контролы, которые это все делают автоматически.

      Для тех кто в поисках, вдруг будут полезны эти плагины для SSMS: dbForge Source Control for SQL Server и SQL Source Control.
      +1
      А не лучше ли у всех отобрать права на запись к базе у всех кроме сервера CI. И раздать всем доступ к репозиторию с определенной структурой. Сервер CI будет переодически проверять наличие коммитов и применять новые запросы к базе.
        0
        Кажется, именно про это и написано под заголовком "идеальный мир". А в ссылках есть пример такой реализации. Конечно лучше!
        +2
        Решал подобную задачу для Oracle и DB2 LUW
        Утилиты для выгрузки DDL:
        https://github.com/qwazer/scheme2ddl
        https://github.com/qwazer/db2-scheme2ddl

        scheme2ddl расползлась по узкому кругу заинтересованных лиц за 3 года, может и вам пригодится.
          +1
          Чтобы только не выдумать, лишь бы не использовать нормальные миграции и не заниматься настройкой workflow в команде...

          А вы версионируете свою базу данных?


          Разумеется. Любой проект начинается с этого. Более того, флоу построен так, что невозможно начать проект без нулевой миграции.

          по факту?


          Да упаси господи. То, что вы описали, это не версионирование БД, это карго-культ. Вы делаете вид, что у вас миграции. И ждете, что с неба спустятся белые бородатые люди и принесут вкусную огненную воду.

          Может быть, есть реализации под другие СУБД — MySQL, Postgres?


          А какая, собственно говоря, разница? Не понимаю.

          Или есть какой-то фундаментально другой хороший подход, который я проглядел?


          Разумеется есть.
            +2
            А хоть слово конкретики?
              +2
              А что конкретно вас интересует?
              Слово "миграции" я упомянул два раза. Это паттерн такой, архитектурный.

              1. На каждое изменение делаете два файлика. NNN.up.sql и NNN.down.sql, где NNN — последовательный номер миграции. Можете для удобства взять timestamp, например.
              2. В файлик *.up.sql записываете DDL, которые приведут к требуемым изменениям в вашей БД
              3. В файл же *.down.sql пишете команды, которые изменения откатят (внимание — это не всегда возможно!)
              4. Пишете систему, которая:
                • умеет определять, какая миграция была применена последней
                • применять все последующие, по одной или до конца
                • откатывать миграции
              5. Лучше всего пункт 4. взять готовым. Например на PHP в каждый фреймворк обязательно входит система миграций

              А дальше радуетесь жизни
              Коммит — пуш — сервер сборки запускает команду migrate up — вы получаете новое состояние БД. Желательно сначала на тестовом стенде. Виза QA — сборка на боевом.
                0
                +1 автор ищет проблемы на своё мягкое место. За изменение БД должен отвечать программист который делал это изменение и миграции должны хранится вместе с кодом проекта.
                Я в PHP использую DoctrineMigrations
                  –1
                  Конкретно меня интересует реализация. Использование архитектурного паттерна — это недалёкий шаг от того, что просто решить, что надо использовать контроль версий на базе.

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

                  Из требований навскидку могу выделить следующее:

                  1. Возможность централизованно мигрировать различные инстансы
                  2. Уметь определять ошибки миграции и править их в полуавтоматическом режиме
                  3. Иметь систему прав доступа и аппрува изменений
                  4. Желательно уметь связывать миграции с версией кода
                  5. Желательно уметь автоматически генерировать миграции на основании уже внесённых изменений (написать корректный и работающий DDL в блокноте и потом пытаться его накатить это сложная задача с малыми шансами на успех)
                  6. Комментировать и привязывать к задачам каждую миграцию
                  7. Сравнивать итоговый DDL произвольных моментов в жизненном цикле. Не в виде огромной пачки изменений, которые скорее всего будут один и тот же объект, а в виде конечной разницы.

                  Как пример готовых сервисов, которые установил и ими пользуешься — есть таск менеджеры, багтрекеры, есть тот же Gitlab. Просто берёшь и используешь. Так что да, я жду белых бородатых людей, которые хотят мне продать работающее решение.
                  Разницы в том, какую СУБД использовать, действительно нет — вопрос в наличии готовых реализаций.

                  К слову о миграциях в PHP фреймворках — видел реализацию в Yii. Для больших проектов это мало подходит, поскольку там обычно разработчики базы данных и самого приложения — разные люди. Заставлять тех, кто хорошо умеет работать с СУБД писать код миграции на PHP — некорректная практика. Получать у этих людей SQL миграции и отдавать разработчикам — тоже. Можно, конечно, написать простенький скрипт, который генерирует класс миграции на основании скормленного ему SQL — но это уже опять создание велосипедов. Да и миграции в Yii не решают большей части описанных выше задач.
                    0
                    Конкретно меня интересует реализация.

                    Реализация описанного мной паттерна требует написания ровно одного небольшого bash-скрипта. Всё.

                    А всё что вы написали — это от непонимания того, что такое "миграция". Что доказывается, например, следующей фразой:

                    Заставлять тех, кто хорошо умеет работать с СУБД писать код миграции на PHP
                      –1
                      Вы зачем-то вырвали предложение из контекста, надеюсь что по невнимательности. Оно про реализацию миграции на Yii.
                      Ну и полностью проигнорировали список требований. Вам может быть достаточно одного bash скрипта. Мне — нет.
                        +1
                        Ваши требования не нужны. Вы не хотите никак этого понять...

                        Вот например:

                        Уметь определять ошибки миграции и править их в полуавтоматическом режиме

                        Полная ерунда. Что за ошибки? Как их править? Миграция либо накатилась, либо откатилась. Одно из двух. Ошибка? Откат транзакции. Запись о миграции не создается. Сборка целиком откатывается. В логе сборки мы видим подробности.

                        Иметь систему прав доступа и аппрува изменений

                        Это вопрос к вам, как к тимлиду и к тому, как вы строите свой wokflow.

                        Желательно уметь связывать миграции с версией кода

                        Миграции — это просто файлы. Они лежат в гите. Если вы используете какие-то другие "версии кода" — это вопрос к вам.

                        Комментировать и привязывать к задачам каждую миграцию

                        К задачам привязываются коммиты, а не миграции.

                        Желательно уметь автоматически генерировать миграции на основании уже внесённых изменений

                        Не может быть никаких изменений без миграций. Поэтому требование абсурдно.

                        Ну и так далее. Вы очень многое недопонимаете.
                          0
                          1. Возможность централизованно мигрировать различные инстансы

                          миграции применяются при деплое автоматически или вручную. Сначала на тестовом сервере с использованием CI, а потом на боевом.

                          2. Уметь определять ошибки миграции и править их в полуавтоматическом режиме

                          запихнул миграцию в транзакцию и если она не выполнилась, транзакцию откатываем. Это делается через систему мигрирования.
                          Править миграцию в автоматическом режиме невозможно и не нужно.

                          3. Иметь систему прав доступа и аппрува изменений

                          Проект имеет доступ к всей базе и менять может всю базу. На уровне миграции разделения прав доступа быть не может. Это задача другого порядка.

                          4. Желательно уметь связывать миграции с версией кода

                          миграции нужно хранить в репозитории с кодом

                          5. Желательно уметь автоматически генерировать миграции на основании уже внесённых изменений

                          Многие системы миграций умеют генерить код на основе БД и миграции на основе кода. Doctrine Migrations по крайней мере точно умеет

                          6. Комментировать и привязывать к задачам каждую миграцию

                          добавляя к коммиту с миграцией номер задачи мы автоматически связываем их. Так работает GitHub и GitLab

                          7. Сравнивать итоговый DDL произвольных моментов в жизненном цикле.

                          Для того что бы увидеть конечный результат нужно применить миграции.
                          Обычно деплой мастер применяет все миграции на локальной базе и проверяет их на конфликты и потом при желании собирает их в одну большую миграцию.

                          видел реализацию в Yii

                          Я лично считаю не правильным использовать PHP конструкции для описания миграции. Во всех проектах которые я видел миграции описывались как SQL. Где-то это был *.sql файл, где-то PHP класс в котором описывались изменения как SQL. Для Yii можно вызвать execute(), для Doctrine Migrations addSql()

                          Получать у этих людей SQL миграции и отдавать разработчикам

                          Если миграции пишет разработчик БД, а не разработчик приложения, он все равно должен сохранить миграцию в проекте. Для упрощения можно описывать миграции, как говорил AlexLeonov, в отдельный *.sql файлах. Любой нормальный редактор будет поддерживать подсветку SQL синтаксиса в таких файлах.
                  0
                  Да, я много раз указал, что не считаю способ версионирования по факту корректным, и слёзы стыда наворачиваются на мои глаза каждый раз, когда я думаю об этом. Однако в реальных условиях мне (и многим другим) приходится работать в качестве DBA во внешних компаниях, где я никоим образом не могу повлиять на workflow. И проблемы, которые теоретически должны быть на совести разработчиков, как тут уже указали, в одночасье становятся моими при возникновении каких-либо ошибок. И версионировать по факту — это в данном случае единственный способ иметь возможность что-то исправить.
                  0
                  В случае с ораклом почти каждый проект пилит свои уникальные велосипедные тулы по дампу, дифу, накатке патчей и т.п.

                  В общем случае, если логики на уровне базы не много, то отлично используют liquibase, dbmaintain
                    0
                    Про dbmaintain ещё не слышал, спасибо. Только их страничка не обновлялась с 2011 года — оно стабильно и не требует обновления, или тихо умерло?

                    Вот меня очень сильно смущает писать велосипеды. А впереди ещё большой проект на постгресе...
                      0
                      doctrine migrations для многих sql — для postgres точно есть. Базовые возможности — миграция схемы данных на основе маппинга и текущей схемы в БД
                        0
                        Doctrine поддерживает Oracle и многие другие
                          0
                          Спасибо! Конечно, не полное решение, но как платформа выглядит очень симпатично. А есть возможность посмотреть итоговый DDL на момент выбранной миграции и смотреть diff между итоговыми же DDL от различных миграций?
                            0
                            Я полагаю, что под DDL вы подразумеваете схему БД и маппинг внутри приложения (какие поля в коде если отображаются и как на колонки в таблицах БД)

                            Если так, то этот diff — это две части

                            1 — файлик с sql патчем
                            2 — diff самого кода

                            Как было указано чуть выше, Doctrine migrations — это на PHP. Вполне может быть, что есть реализации на других языках.
                          0
                          dbmaintain похоже что мертв, но видел его в одном проекте года 3 назад. Успешно использовали, но там схема была достаточно простая.
                          liquibase видел на более сложных проектах, но и он сложнее.
                        0
                        СУБД InterSystems Caché. Из коробки есть контроль версий в файлы (а там уже прикручивайте любую систему котроля версий) и открыт код плагинов для удобной работы с Git и SVN. Из CI есть Jenkins и интеграция с GitHub. Удобно.

                        Only users with full accounts can post comments. Log in, please.