Для чего написана статья?

Чтобы протестировать идею об аудиторию, собрать полезный фидбэк, и/или дополнительные идеи (возможно для кого-то такая же проблема близка) :-)

Для кого будет интересно?

Если ваша система не использует ORM (Hibernate/Entity framework/Django ORM), в котором функциональность миграций встроена.
Если промышленное окружение вашей Системы находится не у Вас под контролем, если, например вы поставщик продукта, разрабатываемого на заказ, и поставляемые вами скрипты DDL/DML скрипты вручную накатывает администратор базы данных после ревью кода и, к сожалению, возможных изменений :-)
Если нет возможности внедрить инструмент делающий “магию для миграций” - Liquibase / Flyway по любым рациональным (или не очень) причинам.
И тем не менее, очень, очень и очень :-) хочется контролировать как изменения в коде попадают в базу данных сначала в тестовом окружении, а потом в промышленном.
Крайне желательно, чтобы изменения вносились в производственную среду точно так же, как и в тестовую. Хотя достичь этого на 100% невозможно, это цель, к которой нужно стремиться.
По крайней мере, когда возникает вопрос: “Вот тут у нас зачем-то было поле удалено (или добавлено) и у нас теперь ничего не работает”, должно быть что-то, какая-то информация, о том кто, что, и когда на базу данных “накатывал” :-)

[ВАЖНО] Все примеры кода, для PostgreSQL, но сама концепция вполне “RDBMS agnostic”, то есть независима от производителя, модели и версии СУБД (как и от фазы луны) :-) Единственное существенное требование это то, что она должна быть “реляционной”, как в смысле типов хранимых в БД сущностей, так и в смысле поддержки ACID.

Итак, базовые требования

Доставка изменений в исходном коде (DDL/DML скриптов) в тестовое окружение;
Доставка изменений в исходном коде в промышленное окружение;
Возможность сохранять данные в существующих экземплярах БД, не пересоздавая их с нуля каждый раз при развертывании из DDL/DML скриптов; (Поверьте, это важно даже в тестовом окружении)
Поддержка следующих типов изменений:
- Изменения схемы (таблиц, представлений, хранимых процедур, вью, триггеров)
- Изменения данных:
  - В имеющихся lookup таблицах, условно неизменных на стороне Заказчика, могут быть изменения на стороне исходного кода;
  - Иногда требуется выполнить модификацию/миграцию существующих данных в целевой БД из-за изменений функциональности системы;
Возможность заранее проверить какие изменения будут применены на данном конкретном экземпляре база данных;
Возможность сгенерировать DDL/DML скрипт для проведения предварительного код ревью и последующего применения на конкретной БД;

Как обычно решается подобная задача?

В базе данных заводится табличка (или несколько), в которой хранятся примененные к данному экземпляру изменения.

Каждый DDL/DML скрипт, отправляемый клиенту, самостоятельно проверят применялся ли он уже на данном экземпляре БД, применяет транзакционно (или нет) содержащиеся в нем изменения и логирует (или нет) в БД статус о том, что его применили.

В чем проблема с таким подходом?

Каждый скрипт содержит boilerplate код, который каждый раз нужно добавлять в каждый из скриптов. Этот код сам по себе может содержать ошибки и, даже разные в разных скриптах :-) И если вы найдете ее в одном, то возможно таки придется проверить и все остальные :-) так, на всякий случай.
Если вы поставляете пакет скриптов, то какой их них будет применяться, а какой нет в точности можно узнать только открыв каждый из них и пристально проверив что там внутри.

То есть инструмент, который накатывает скрипты, управляет транзакциями и логирует изменения в БД все же нужен.

Вопросы, которые хочется пока отставить в стороне, не обсуждать и просто принять как precondition :-)

Применение подхода Fix forward * для отката неудачных изменений, вместо создания Rollback скриптов;
Применение подхода с “не ломающими изменениями” и “горячим внедрением” *, чтобы были:
1. возможность выполнять скрипты обновления без остановки системы;
2. и даже (при определенной дисциплине) возможность параллельной работы двух версий Системы, новой и старой на одном общем экземпляре базы данных.
Трекинг изменений на уровне каждой схемы, не на уровне всей базы данных;

Предлагаемый подход

Структура Git репозитория (отчасти навеяно flyway)

Скрипты делятся на четыре типа и применяются в определенном порядке:
1. Environment initialization -> (только однажды для создания пустой базы данных и пустых схем)
2. Baseline -> (только однажды на пустой схеме, но уже с табличками контроля версий)
3. Versioned -> (только однажды для каждой версии схемы)
4. Repeatable -> (каждый раз при изменении скрипта).
Инициализация окружения: Создание пользователей, ролей, БД (Environment initialization)
- Применяются для создания пользователей, ролей и пустой БД на сервере. Зависят от окружения и применяются только однажды для каждой создаваемой БД. Отличаются в промышленном и тестовых окружениях
- Типы объектов:
  - Пользователи (users), роли (roles), БД (databases), Схемы (schema)
- Применяются только однажды для каждой БД с помощью стандартной утилиты psql *
Базовые (Baseline)
- Применяются только на пустую БД для создания начальной стартовой версии схемы, наследованной из промышленного окружения прошлой версии, а также таблиц для контроля версии БД;
- Предполагается что БД, схема и пользователи уже созданы
- Типы объектов
  - Схема, таблицы, данные (tables, data), представления (view), процедуры (procedures), триггеры (triggers)
  - Предполагается что БД, схема и пользователи уже созданы
Версионные (Versioned)
- Применяются на каждую схему строго последовательно, при отсутствии записи о том, что скрипты данной версии применялись
- Версия БД монотонно возрастающее целое число. Каждый скрипт переводит систему из одного согласованного состояния в другое. Применяется только в рамках транзакции.
- При применении в БД записывается время, и пользователь, от имени которого применялись скрипты
- Типы объектов
  - Схема и данные (tables, data)
Повторяемые (Repeatable)
- Применяются только после успешного применения всех версионированных скриптов, на целевой версии системы, и применяются при изменении контрольной суммы, хранящейся в БД или при отсутствии записи в таблице с контрольной суммой. Используются для объектов, которые не содержат изменяемых данных.
- Типы объектов:
  - Представления (view), процедуры (procedures), триггеры (triggers)

Структура в база данных

Для хранения состояния в каждой схеме есть свои таблички с версиями, например такие:
- dbmigration_versions (version_id, is_baseline, created_at, created_by, created_from)
  - Хранит историю применения baseline + versioned scripts
- dbmigration_repeatable (sha256sum, relative_path, created_at, created_by, created_from)
  - Хранят историю применения repeatable scripts, один тот же скрипт может применятся несколько раз если он изменился

Конкретные кейсы применения:

[ВАЖНО] Ниже подразумевается использование инструмента dbmigration.py но это строго необязательно.

Обновление БД в окружении разработки:

В Gitlab добавить pipeline который получает из ветки код, содержащий DDL/DML скрипты и применяет утилиту dbmigration (или другую) для каждой целевой схемы БД:

$ git clone <url содержащий изменения в схемах БД>

$ USER_PASSWORD=topsecret123 dbmigration.py update --host <host> --port <port> --dbname <db> --user <user_name> schema1 ./db/schema1

$ USER_PASSWORD=topsecret123 dbmigration.py update --host <host> --port <port> --dbname <db> --user <user_name> schema2 ./db/schema2

Обновление БД в промышленном окружении:

Подразумевается возможность применения подхода с горячим внедрением *
Так же подразумевается, что предварительно все шаги описанные ниже проведены в UAT окружении, а после применения скриптов проведен успешный смоук тест системы там же;

Получив архив, содержащий скрипты обновления БД выполнить следующие команды:

 $ tar xzvf system-x.y.z.tar.gz

 $ cd system-x.y.z/schema1

 $ USER_PASSWORD=topsecret123 dbmigration.py verify --host <host> --port <port> --dbname <db> --user <user_name> --build-update-script ./schema1_migrate.sql schema1 ./db/schema1

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

Применить файл в окружении с помощью утилиты psql *:

PGPASSWORD=topsecret123 psql -h <host> -p <port> -U <user> -d <database> -v schema_name=<schema1> -f "schema1_migrate.sql"

Повторить для каждой схемы :-)

Порядок выпуска и применение хотфиксов для БД с предыдущими версиями схем:

Например, у вас (точнее у Заказчика) есть ПРОМ окружение, в котором развернут релиз 3, UAT окружение в котором уже есть релиз 4, и вы сейчас в своем окружении активно работаете над версий 5.
И тут в UAT-е находят баг который, разумеется, есть и релизе 5 (который пока только у вас), и для того чтобы было более интересно, есть еще и на ПРОМ-е и для его исправления нужно выпустить хотфикс причем хотфикс подразумевает миграцию в базе данных.
… Друзья уже и так много букв, продолжение последует, если будет запрос аудитории :-)

Сноски

Fix Forward в миграциях БД — это стратегия исправления ошибок, при которой вместо отката (rollback) проблемной миграции создается новая, исправляющая миграция (forward), применяемая поверх текущей. Это обеспечивает непрерывность, сохраняет целостность данных и устраняет риск потери данных при откате, что критично для production.
“не ломающие” изменения в миграциях БД - это подход внесения изменений, при котором изменения в схему вносятся таким образом чтобы не сломать существующий функционал прошлой версии системы. Можно: добавлять таблицы, добавлять поля в существующие таблицы. Нельзя: удалять поля в существующих таблицах, удалять таблицы. Точнее удалять можно, но только через релиз, после внедрения на ПРОМ выпускаем update который зачищает те таблички и поля которые уже никакой версией вашего ПО нигде и никак не используются.

Полезные ссылки

Миграция базы данных в Legacy системах