Как сделать миграцию в PostgreSQL: перенос данных и управляемые изменения схемы

1) Выбор стратегии миграции

Есть три базовых сценария. Выбор зависит от допустимого простоя и объёма данных.

1.1. Offline-миграция (с простоем)

Порядок: остановили запись в источнике → выгрузили → загрузили в PostgreSQL → проверили → переключили приложение.

Плюсы

• проще всего обеспечить согласованность

• меньше инфраструктуры и точек отказа

• проще тестировать и повторять

Минусы

• нужен downtime (иногда часы/сутки)

• для больших БД может быть сложно уложиться в окно

1.2. Near-zero downtime через CDC (догон изменений)

Порядок: сделали первичную загрузку → параллельно применяете изменения из источника (CDC) → короткое окно на финальное выравнивание → cutover.

Плюсы

• минимальный простой

• подходит для больших и “живых” систем

Минусы

• сложнее: CDC, мониторинг, дедупликация, идемпотентность

• больше рисков “рассинхрона”, если не выстроить контроль

1.3. Поэтапная миграция на уровне приложения (expand/contract)

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

Плюсы

• хорошо контролируется в релизном процессе

• снижает риск “большого переключения”

Минусы

• дороже по разработке

• временно усложняет код (совместимость, иногда двойная запись)

2) Инвентаризация источника и критерии успеха

Перед техническими шагами нужно зафиксировать входные параметры.

2.1. Что собрать по источнику

• объёмы по таблицам (row counts, размер, темп роста)

• горячие таблицы (высокий write rate)

• типы данных и “нестандарт”: JSON, гео-типы, BLOB, money/decimal

• ограничения и зависимости: PK/FK/unique, триггеры, процедуры

• колlation/сравнение строк, чувствительность к регистру

• фактические SLA по ключевым запросам и транзакциям

2.2. Acceptance criteria (минимум)

• полнота: совпадение количества строк по ключевым таблицам

• корректность: сверка агрегатов (sum/avg/count distinct) по бизнес-метрикам

• целостность: FK/уникальность (либо доказательство, что они соблюдаются)

• производительность: измеримые цели по latency/throughput

• операционность: бэкапы, мониторинг, алерты, доступы

3) Проектирование целевой схемы PostgreSQL

3.1. Маппинг типов данных (типовые решения)

3.2. Коллации, регистр и сравнение строк

Типовая “мина” при миграции:

• в источнике сравнение может быть case-insensitive по умолчанию (особенно в MySQL/SQL Server),

• в PostgreSQL — обычно case-sensitive.

Если бизнес-логика ожидает case-insensitive уникальность (например, email), закладывайте это явно: нормализация, функциональные индексы, либо специализированные типы/расширения, где это оправдано.

3.3. Порядок включения индексов и ограничений

Для больших объёмов практичнее:

1. создать таблицы и базовые PK,

2. загрузить данные,

3. затем создавать индексы и включать FK/unique (после чистки данных).

4) Выбор инструмента миграции по источнику

Ниже — проверенные классы инструментов. Названия приведены как варианты; выбирать лучше по вашему окружению, объёму и требованию к downtime.

4.1. MySQL/MariaDB → PostgreSQL

• pgloader (часто удобен для “быстрого старта”)

• dump (mysqldump) → трансформация → COPY

• CDC: Debezium-подход, либо managed-инструменты миграции (если применимо)

Плюсы pgloader-подхода

• относительно быстро поднять “первую миграцию”

• умеет многое по конвертации типов

Минусы

• для сложных схем всё равно потребуется ручная доработка

• важно тщательно проверять типизацию и ограничения

4.2. MS SQL Server → PostgreSQL

• экспорт в CSV (bcp/SSIS) → COPY

• специализированные конвертеры схемы + пакетная загрузка

• CDC/репликация изменений (если нужен near-zero downtime)

4.3. Oracle → PostgreSQL

• ora2pg (часто используют для извлечения схемы и данных)

• Data Pump → промежуточный формат → COPY

• CDC/лог-майнинг подходы (если нужен минимальный простой)

5) Универсальный пошаговый план миграции (перенос из другой СУБД)

Шаг 1. Поднять PostgreSQL под миграцию

• отдельный кластер/инстанс для миграции (желательно максимально близкий к будущему продакшену)

• настроить диски и параметры под загрузку (IO важнее “красоты конфигов”)

• включить бэкапы и мониторинг до начала загрузки

Шаг 2. Сконвертировать схему (DDL) под PostgreSQL

Цель этапа — получить рабочую схему без бизнес-логики в триггерах/процедурах (их чаще переносят отдельно).

Практический порядок:

1. таблицы + типы + последовательности/identity

2. primary keys

3. внешние ключи и уникальности — отложить, если объёмы большие

4. индексы — часто тоже отложить до после загрузки

Шаг 3. Определить порядок загрузки таблиц

• сначала “справочники” и малые таблицы

• затем крупные “факты”

• затем зависимые таблицы (child)

• отдельно — таблицы с BLOB/крупными полями (если есть)

Шаг 4. Сделать первичную загрузку данных

Для PostgreSQL базовый скоростной механизм — COPY.

Пример шаблона загрузки CSV:

COPY app.products (id, sku, name, price, created_at)
FROM '/data/products.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',', QUOTE '"');

Важно:

• выгружайте данные в формате, который однозначно парсится (кодировки, разделители, экранирование)

• фиксируйте правила для NULL/пустых строк

• для дат/времени определите стандарт и применяйте его стабильно

Шаг 5. Включить ограничения и индексы после загрузки

После первичной загрузки:

• создайте индексы,

• включите unique и FK,

• исправьте найденные нарушения (если они есть), затем повторите включение.

Пример:

ALTER TABLE app.products
ADD CONSTRAINT products_sku_uk UNIQUE (sku);

ALTER TABLE app.order_items
ADD CONSTRAINT order_items_order_fk
FOREIGN KEY (order_id) REFERENCES app.orders(id);

Шаг 6. Синхронизировать генерацию идентификаторов

Если вы загрузили id вручную, нужно выставить sequence/identity “вперёд”.

Пример для sequence:

SELECT setval('app.products_id_seq', (SELECT max(id) FROM app.products));

Шаг 7. Обновить статистику и проверить планы запросов

После массовой загрузки:

VACUUM (ANALYZE);

Без этого PostgreSQL может выбирать неоптимальные планы, и производительность будет “плохой” даже при корректной схеме.

Шаг 8. Валидация данных (обязательный этап)

Минимальный набор проверок:

• row counts по ключевым таблицам

• контрольные агрегаты (суммы, количества, distinct)

• выборочные сверки по ключам (случайные ID, последние N записей)

• проверка ссылочной целостности (после включения FK)

Рекомендуемая таблица контроля (как артефакт миграции):

Шаг 9. Подготовить cutover и rollback

Cutover (offline):

• остановить запись в источник / перевести в read-only

• финальная догрузка “хвоста”

• финальная валидация

• переключение приложения

• усиленный мониторинг

Rollback:

• заранее определить “окно возврата”

• предусмотреть быстрый переключатель (конфиг/секрет/фичефлаг)

• определить, что делаете с данными, если успели писать в PostgreSQL

6) Практические runbook по источникам

6.1. MySQL/MariaDB → PostgreSQL (через экспорт + COPY)

Идея: выгрузить таблицы в CSV (или несколько файлов), загрузить COPY.

Плюсы

• понятный и контролируемый процесс

• легко повторять и отлаживать

• COPY обычно очень быстрый

Минусы

• вы сами отвечаете за корректность форматов дат/NULL/кодировок

• требуется аккуратная трансформация типов

Типовые нюансы MySQL:

• TINYINT(1) (boolean)

• 0000-00-00 как “дата” (в PostgreSQL недопустимо, нужно нормализовать)

• collations и сравнение строк

• ENUM (обычно переводят в справочник или CHECK constraint)

6.2. MS SQL Server → PostgreSQL (через CSV + COPY)

Ключевые нюансы:

• Unicode не проблема, но следите за выгрузкой в корректной кодировке

• DATETIME/DATETIME2 — заранее выбрать стандарт хранения времени

• BIT → boolean

• identity → sequence/identity (и синхронизация после загрузки)

• схемы и именование: зарезервированные слова, регистр, кавычки

6.3. Oracle → PostgreSQL (с акцентом на NUMBER и даты)

Ключевые нюансы:

• NUMBER требует строгого решения: bigint vs numeric(p,s)

• даты/время и часовые пояса (Oracle может хранить варианты типов времени)

• функции/процедуры/пакеты: перенос чаще отдельным проектом, не “в рамках загрузки данных”

7) CDC (если нужен минимальный downtime): как не потерять консистентность

Если вы догоняете изменения из источника:

• первичная загрузка делается по “снимку”

• далее поток изменений применяется к PostgreSQL

• важно обеспечить идемпотентность и дедупликацию событий

• на cutover вы должны “свести хвост” до нуля или приемлемого минимума

Контрольные точки:

• lag (насколько отстаёт PostgreSQL)

• количество неприменённых событий

• ошибки применения (конфликты, нарушения ограничений)

• соответствие итоговых агрегатов

Плюсы

• минимальный простой

Минусы

• требует зрелой эксплуатации (мониторинг, алерты, разбор инцидентов)

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

8) Типовые ошибки при переносе из другой СУБД

1. Миграция типов “по умолчанию”, без явных решений по времени, decimal и коллациям.

2. Создание индексов и FK до загрузки больших таблиц.

3. Отсутствие синхронизации sequence/identity.

4. Валидация только по количеству строк (без агрегатов и выборок).

5. Игнорирование case-insensitive логики источника.

6. Перенос процедур/триггеров “как есть” без переосмысления (PostgreSQL иные диалекты и практики).

9) Что нужно от вас, чтобы сделать миграцию максимально “точно”

Вы не просили уточнений, поэтому ниже — список, который можно использовать как внутренний чек-лист (без обратных вопросов):

• какая исходная СУБД (MySQL/SQL Server/Oracle и версия)

• объём данных и допустимый downtime

• есть ли требования “почти без простоя” (CDC)

• какие таблицы самые “горячие” по записи

• есть ли триггеры/процедуры, на которых держится бизнес-логика

• требования по коллациям/регистру (особенно для уникальных полей)