База данных: постановка, схемы, миграции

Место в цепочке

Что предыдущее звено обязано обеспечить: правило публикации портов. База не публикуется наружу вообще — ни на все интерфейсы, ни «временно, чтобы посмотреть». Порт 5432, доступный из интернета, делает бессмысленными и шлюз, и TLS, и разграничение прав.

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

Правило BMBP «изоляция по схемам БД» здесь получает точный вид: правило вывода имени схемы, права и порядок создания.

Два способа: своя база рядом и внешняя управляемая

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

Шифрование соединения. Значения sslmode различаются не «сильнее/слабее», а тем, что проверяется:

require защищает от пассивного прослушивания, но не от подмены сервера: сертификат не проверяется. Для внешней базы целевое значение — verify-full с файлом корневого сертификата провайдера. disable допустим только там, где трафик не выходит за пределы внутренней сети одной машины.

Шаг 1. Поднять базу

Своя база в контейнере

compose.yml сервиса (по BMBP минимум для запуска — сервис и его база):

services:
  db:
    image: postgres:17-alpine
    restart: unless-stopped
    environment:
      POSTGRES_DB: appdb
      POSTGRES_USER: dbadmin
      POSTGRES_PASSWORD: ${DB_ADMIN_PASSWORD}
      POSTGRES_INITDB_ARGS: --data-checksums
    command: >
      postgres
      -c shared_buffers=1GB
      -c effective_cache_size=3GB
      -c work_mem=16MB
      -c maintenance_work_mem=256MB
      -c max_connections=100
      -c log_min_duration_statement=500ms
    volumes:
      - db_data:/var/lib/postgresql/data
    shm_size: 256m
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U dbadmin -d appdb"]
      interval: 5s
      timeout: 3s
      retries: 20
    # ports не объявлены: база доступна только по имени `db` внутри сети Compose

volumes:
  db_data:

Разбор существенных мест:

• Версия закреплена основным номером (17, а не latest). Каталог данных привязан к основной версии: сервер отказывается стартовать на каталоге, созданном другой основной версией. Смена 17 на 18 в теге — не обновление, а отказ запуска (см. отказы). Чтобы пересборка давала тот же образ, тег дополняют цифровым отпечатком: postgres:17-alpine@sha256:….
• Том именованный. Данные в томе переживают пересоздание контейнера; данные в слое контейнера исчезают вместе с ним. Путь монтирования берётся из образа, он менялся между версиями: docker image inspect postgres:17-alpine -f '{{.Config.Env}}' — переменная PGDATA.
• --data-checksums включает контрольные суммы страниц: тихая порча данных обнаруживается при чтении, а не через месяцы. Параметр действует только при инициализации кластера — на уже созданном каталоге его так не добавить.
• shm_size. По умолчанию контейнер получает 64 МБ /dev/shm; параллельные запросы используют разделяемую память и падают на нехватке.
• ports отсутствуют. Сервис обращается к базе по имени db внутри сети Compose. Если локальному инструменту нужен доступ, порт публикуется только на петлевой интерфейс и только в дев-контуре: - "127.0.0.1:5433:5432" (5433 — чтобы не конфликтовать с базой, установленной на хост).

Параметры памяти — отправная точка для сервера с 4 ГБ ОЗУ, дальше настраиваются по нагрузке:

work_mem умножается: в одном запросе может быть несколько сортировок, и каждый параллельный исполнитель берёт свою порцию. Худший случай — max_connections × work_mem × число узлов, поэтому значение держат небольшим, а параллелизм ограничивают пулом приложения, а не базой.

Запуск и проверка:

docker compose up -d db
docker compose exec db pg_isready -U dbadmin -d appdb

Ожидается: /var/run/postgresql:5432 - accepting connections.

docker compose exec db psql -U dbadmin -d appdb -c "SELECT version()"

Ожидается: строка, начинающаяся с PostgreSQL 17. — основная версия совпадает с закреплённой в теге образа.

Внешняя управляемая база

Контейнера с базой нет; вместо шага выше выполняется:

1. создать базу у провайдера, зафиксировать основную версию — она должна совпадать с версией клиента, которым будут сниматься копии;
2. в списке разрешённых адресов открыть доступ только адресам своих серверов;
3. скачать корневой сертификат провайдера и положить его рядом с секретами сервиса (.secrets/db-root.crt), смонтировав в контейнер сервиса только на чтение.

Проверка (нужен OpenSSL 1.1.1 или новее):

openssl s_client -starttls postgres -connect db.example.com:5432 \
  -CAfile .secrets/db-root.crt </dev/null 2>/dev/null | grep 'Verify return code'

Ожидается: Verify return code: 0 (ok). Другой код означает, что сертификат не соответствует файлу или имя хоста не совпадает — подключение с verify-full не заработает.

Шаг 2. Пользователи и права

Права выдаются от нуля вверх, а не «всё, потом урежем». Роли ровно три вида:

Отдельный пользователь на сервис нужен по двум причинам: сервис не может дотянуться до чужих данных, а в pg_stat_activity видно, чьи соединения заняли пул.

Пароли берутся из файла окружения и не попадают ни в SQL-файлы, ни в репозиторий. psql подставляет их через переменные, поэтому в тексте команд значений нет:

docker compose exec -T db psql -U dbadmin -d appdb -v ON_ERROR_STOP=1 \
  -v orders_pw="$ORDERS_DB_PASSWORD" -v backup_pw="$BACKUP_DB_PASSWORD" <<'SQL'
-- Подключаться к базе может не любая роль, а только названные.
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;

-- Общая схема public не используется под таблицы сервисов.
-- Начиная с PostgreSQL 15 право CREATE у PUBLIC отозвано по умолчанию.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Пользователь сервиса orders.
CREATE ROLE orders_app LOGIN PASSWORD :'orders_pw';
GRANT CONNECT ON DATABASE appdb TO orders_app;

-- Пользователь для снятия копий: чтение всех данных, ничего больше.
CREATE ROLE backup_reader LOGIN PASSWORD :'backup_pw';
GRANT CONNECT ON DATABASE appdb TO backup_reader;
GRANT pg_read_all_data TO backup_reader;
SQL

pg_read_all_data (PostgreSQL 14 и новее) даёт чтение всех таблиц и право входа во все схемы. Это именно то, что нужно pg_dump, и это избавляет от выдачи прав заново при каждой новой таблице. Право на запись у этой роли отсутствует.

docker compose exec db psql -U dbadmin -d appdb -c "\du"

Ожидается: в списке есть orders_app и backup_reader, у обоих в колонке атрибутов нет Superuser и Create DB.

Шаг 3. Схема на сервис

Правило вывода имени

Имя схемы получается из имени сервиса механически, без исключений:

1. привести имя сервиса к нижнему регистру;
2. каждый символ вне [a-z0-9_] заменить на _;
3. если первая позиция — цифра, добавить в начало s_ (идентификатор не может начинаться с цифры);
4. обрезать до 63 байт — предел длины идентификатора PostgreSQL.

Префикс pg_ зарезервирован системой: сервис с таким именем переименовывается, схема с таким именем не создаётся.

Полученное значение один раз записывается в конфигурацию сервиса явным полем (DB_SCHEMA) и дальше не вычисляется заново. Причина: переименование сервиса не должно молча увести его на пустую схему. При смене имени сервиса значение DB_SCHEMA остаётся прежним; схемы не переименовывают.

Создание и видимость

Схему создаёт администратор вместе с пользователем — тогда сервису не нужно право CREATE на базу, которого у него быть не должно (и которого управляемые базы обычно и не дают):

CREATE SCHEMA orders AUTHORIZATION orders_app;

-- Все сессии сервиса работают в своей схеме; public остаётся для расширений.
ALTER ROLE orders_app SET search_path = orders, public;

Владелец схемы — сервисная роль: миграции создают в ней таблицы без дополнительных выдач прав.

Права на чужие схемы не выдаются, и этого достаточно: без USAGE роль не может обратиться ни к одному объекту чужой схемы. Ограничение касается доступа к данным, а не к именам — системный каталог PostgreSQL читается всеми, поэтому \dn покажет и чужие схемы. Скрыть их названия средствами базы нельзя; изоляция здесь означает «не прочитать и не изменить».

docker compose exec db psql -U dbadmin -d appdb -c "\dn"

Ожидается: строка orders | orders_app.

docker compose exec db psql -U orders_app -d appdb \
  -c "SHOW search_path" -c "SELECT current_user, current_schema()"

Ожидается: orders, public, затем orders_app | orders.

docker compose exec db psql -U orders_app -d appdb -c "SELECT 1 FROM accounts.customers LIMIT 1"

Ожидается: ERROR: permission denied for schema accounts. Успешный ответ означает, что права выданы шире, чем нужно, — разбирать до перехода к следующему шагу.

Шаг 4. Миграции

Требования к инструменту

Инструмент берётся из стека сервиса (в нашем стеке это Alembic; в других — встроенный мигратор библиотеки доступа к базе). Независимо от выбора он обязан:

1. читать файлы миграций из каталога migrations/ в репозитории сервиса, рядом с кодом, но вне кода приложения;
2. задавать порядок применения явно — номером в имени файла или ссылкой на предыдущую ревизию;
3. хранить отметки о применённых миграциях в таблице внутри схемы сервиса, а не в public: иначе два сервиса в одной базе перезапишут историю друг друга;
4. применять каждый файл в транзакции и брать блокировку на время применения.

Пункт 3 в инструментах задаётся отдельно: у одних это параметр таблицы версий и её схемы, у других — достаточно search_path, выставленного при подключении. Проверяется по факту: после первой миграции таблица версий должна оказаться в orders, а не в public.

Файлы и именование

Базовая форма — нумерованные SQL-файлы:

migrations/
├── 0001_init.sql
├── 0002_api_tokens.sql
├── 0003_orders_status_index.sql
└── 0004_drop_customer_name.sql

Правила:

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

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

Первое применение

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 --single-transaction -f migrations/0001_init.sql

--single-transaction вместе с ON_ERROR_STOP=1 даёт нужное свойство: ошибка в середине файла откатывает файл целиком. В PostgreSQL изменения схемы транзакционны, поэтому «половина таблиц создалась» — не следствие природы базы, а следствие применения без транзакции.

Исключение — операторы, которые в транзакции выполняться не могут: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, VACUUM. Каждый такой оператор выносится в отдельный файл миграции, помеченный как невыполняемый в транзакции, и применяется без --single-transaction.

docker compose exec db psql -U orders_app -d appdb -c "\dt"
docker compose exec db psql -U orders_app -d appdb \
  -c "SELECT * FROM orders.schema_migrations ORDER BY 1 DESC LIMIT 3"

Ожидается: \dt перечисляет таблицы со схемой orders в колонке Schema; в таблице версий — номера применённых миграций, последний совпадает с последним файлом в migrations/. Имя таблицы версий задаётся инструментом (schema_migrations, alembic_version и т. п.) — важно, что она в схеме сервиса.

Кто применяет

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

Когда старт приложения всё же применяет миграции (например, единственная копия в дев-контуре), перед проверкой таблицы версий берётся рекомендательная блокировка — вторая копия ждёт, а не выполняет то же самое:

BEGIN;
SELECT pg_advisory_xact_lock(hashtext('orders.migrations')::bigint);
-- проверка таблицы версий и применение недостающих файлов
COMMIT;

Часть инструментов берёт такую блокировку сама — тогда добавлять её не нужно; проверяется по документации инструмента.

Шаг 5. Подключение из приложения

Строка подключения приходит одной переменной окружения; пароль в неё подставляется из файла секретов, в образ не попадает:

# своя база в той же сети Compose
postgresql://orders_app:${ORDERS_DB_PASSWORD}@db:5432/appdb?sslmode=disable

# внешняя управляемая база
postgresql://orders_app:${ORDERS_DB_PASSWORD}@db.example.com:5432/appdb?sslmode=verify-full&sslrootcert=/app/.secrets/db-root.crt

Пул. Соединение с базой — дорогой ресурс: у сервера их конечное число, а установка нового требует рукопожатия (для шифрованного соединения — заметно дороже, в наблюдавшихся случаях доли секунды). Отсюда параметры:

Сумма пулов всех копий плюс миграционный шаг плюс копирование должна помещаться в max_connections с запасом: часть слотов сервер резервирует за административными подключениями. Правило: max_connections ≥ число копий × верхний размер пула + 10.

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

Таймауты задаются в слое инфраструктуры, рядом с подключением (BMBP), и частично — на роли, чтобы действовали независимо от кода:

ALTER ROLE orders_app SET statement_timeout = '10s';
ALTER ROLE orders_app SET lock_timeout = '3s';
ALTER ROLE orders_app SET idle_in_transaction_session_timeout = '30s';

• statement_timeout — предел на один запрос; без него один тяжёлый запрос держит соединение неограниченно;
• lock_timeout — предел ожидания блокировки; без него миграция, ждущая чужую транзакцию, выстраивает за собой очередь из запросов;
• idle_in_transaction_session_timeout — обрыв сессии, открывшей транзакцию и забывшей её закрыть; такие сессии удерживают блокировки и мешают очистке;
• таймаут получения соединения из пула задаётся в приложении: запрос, которому соединение не досталось, должен закончиться ошибкой сразу, а не ждать.

Поведение при недоступности базы:

• при старте сервис не завершается с ошибкой, а повторяет подключение с растущей паузой и отвечает «не готов» на служебной ручке. Выкат при этом не переключит трафик (релиз), а перезапуск базы не потребует ручного перезапуска сервисов;
• в работе запрос завершается ошибкой в конверте { status: error }, а не зависает;
• повторяются только идемпотентные операции, с ограниченным числом попыток и растущей паузой;
• миграционный шаг при недоступной базе завершается с ошибкой и останавливает выкат — это правильное поведение: применять миграции «когда получится» нельзя.

curl -sf http://127.0.0.1:8000/ready
docker compose exec db psql -U dbadmin -d appdb -c \
  "SELECT application_name, state, count(*) FROM pg_stat_activity WHERE datname='appdb' GROUP BY 1,2"

Ожидается: служебная ручка отвечает успехом; в списке соединений видно имя сервиса, число соединений не превышает верхний размер пула, строк в состоянии idle in transaction нет.

Шаг 6. Проверка закрытости

Выполняется на сервере после запуска — это тот шаг, пропуск которого обнаруживается уже по чужим запросам в логах:

ss -ltn | grep -E ':(5432|5433)'
docker compose port db 5432

Ожидается: первая команда не выводит ничего либо выводит только 127.0.0.1:5433; вторая сообщает, что публикации нет. Строка вида 0.0.0.0:5432 означает, что база доступна из интернета — убрать публикацию и считать пароли скомпрометированными.

С другой машины:

nc -vz example.com 5432

Ожидается: отказ в соединении или истечение времени ожидания.

Изменение схемы дальше

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

Что делает изменение с таблицей:

Необратимое изменение — два выката

Пример: текстовое поле customer_name заменяется ссылкой customer_id.

Выкат 1 — только расширение, 0007_add_customer_id.sql:

ALTER TABLE orders ADD COLUMN customer_id BIGINT REFERENCES customers(id);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Код этого выката пишет оба поля, читает старое. Существующие строки заполняются отдельным шагом: на большой таблице один UPDATE на все строки удерживает блокировки и раздувает таблицу, поэтому заполнение идёт пачками по несколько тысяч строк, вне миграции.

Между выкатами проверяется, что старого кода не осталось: все копии обновлены, а поле заполнено у всех строк.

Выкат 2 — сужение, 0009_drop_customer_name.sql:

DELETE FROM orders WHERE customer_id IS NULL;   -- либо заполнить; иначе SET NOT NULL не пройдёт
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP COLUMN customer_name;

Строка с DELETE — не формальность: это те записи, до которых заполнение не дошло. К моменту второго выката с ними нужно осознанное решение, иначе SET NOT NULL остановит выкат.

Откат миграции

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

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

Типичные отказы

Откат и снятие

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

docker compose exec db psql -U orders_app -d appdb \
  -c "SELECT * FROM orders.schema_migrations ORDER BY 1 DESC LIMIT 1" -c "\dt"

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

Снять схему сервиса (дев-контур, повторное разворачивание с нуля):

DROP SCHEMA orders CASCADE;

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

docker compose down          # контейнер убран, том с данными остался
docker compose down -v       # том удалён вместе с данными — необратимо

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