С MySQL на PostgreSQL: подводные камни переноса

MAATRIX

Дамп снялся, данные импортировались, таблицы на месте — а приложение падает с ошибками, которых не было ни разу за годы работы на MySQL. Так выглядит типичная миграция на PostgreSQL: перенос самих данных почти всегда проходит гладко, а вот запросы и код, написанные под особенности MySQL, начинают сыпаться один за другим. Ниже — конкретные ловушки, из-за которых это происходит, и что с ними делать, а не общий план «как мигрировать базу».

Регистр идентификаторов: почему CamelCase ломает запросы

Первая и самая частая засада — как СУБД трактует регистр имён таблиц и колонок. В MySQL поведение зависит от параметра lower_case_table_names и по факту от файловой системы ОС: на Linux по умолчанию имена таблиц регистрозависимы (Users и users — разные таблицы), а на Windows и части сборок macOS файловая система нечувствительна к регистру, и MySQL по факту работает с именами без учёта регистра. Из-за этого на проектах, которые разрабатывались на Windows-машине, а крутились на Linux-сервере, годами жил код с вперемешку набранными Users, users, getUserById — и всё работало, потому что каждая среда «прощала» свою часть несоответствий.

PostgreSQL ведёт себя иначе и куда предсказуемее — но это и есть источник проблем при переносе. Если имя не взято в кавычки, PostgreSQL всегда приводит его к нижнему регистру, независимо от ОС:

CREATE TABLE Users (Id serial, UserName text);
-- реально создаётся таблица users с колонками id, username

Проблема начинается, когда схему конвертирует ORM или инструмент миграции, который заключает идентификаторы в кавычки и сохраняет исходный регистр:

CREATE TABLE "Users" ("Id" serial, "UserName" text);

Теперь таблица называется буквально Users с большой буквы, и обратиться к ней без кавычек нельзя — SELECT * FROM Users PostgreSQL развернёт в поиск таблицы users (в нижнем регистре) и выдаст relation "users" does not exist. А обратиться с кавычками надо строго тем же регистром: "users" и "Users" — это для PostgreSQL два разных идентификатора. Типичная картина после переноса: часть кода ORM генерирует запросы с кавычками и точным регистром, часть написана руками без кавычек — и половина запросов падает с ошибкой «отношение не существует», хотя таблица физически на месте.

Практический вывод: перед миграцией пройдитесь по схеме и решите один раз — либо всё в нижнем регистре без кавычек (снейк-кейс, user_name), либо везде кавычки с сохранением исходного регистра, и это должно совпадать с тем, что генерирует ORM. Смешивать нельзя. Если конвертируете схему инструментом, проверьте флаг, отвечающий за приведение регистра (у pgloader это отдельная настройка identifier_case), и явно выберите один режим для всей базы.

AUTO_INCREMENT vs SERIAL/IDENTITY: схема требует правок, а не просто переноса

В MySQL автоинкремент — это свойство самой колонки: id INT AUTO_INCREMENT PRIMARY KEY, счётчик хранится в метаданных таблицы. В PostgreSQL прямого аналога нет — используется последовательность (sequence), отдельный объект базы, из которого колонка берёт значение по умолчанию через nextval(). Раньше для этого использовали псевдотип SERIAL:

CREATE TABLE orders (
    id serial PRIMARY KEY,
    customer_id integer NOT NULL
);

SERIAL — это просто синтаксический сахар, который под капотом создаёт integer, отдельную последовательность orders_id_seq и вешает DEFAULT nextval('orders_id_seq'). Начиная с PostgreSQL 10 более правильный и стандартный SQL-способ — GENERATED ALWAYS AS IDENTITY:

CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL
);

Прямого «один в один» маппинга типов нет, и ручное преобразование схемы — первое, что должен сделать конвертер или инженер при переносе DDL.

Но главная ловушка вылезает не на этапе создания схемы, а сразу после загрузки данных. Если вы перенесли строки с уже существующими значениями id, последовательность об этом ничего не знает — она стартует с 1 и не в курсе, что в таблице уже есть строки с id до 50000. Первая же вставка новой записи через приложение попытается использовать id = 1, наткнётся на уже занятый primary key и упадёт с duplicate key value violates unique constraint. Обязательный шаг после переноса данных — синхронизировать счётчик с максимальным существующим значением:

SELECT setval(
  pg_get_serial_sequence('orders', 'id'),
  (SELECT COALESCE(MAX(id), 1) FROM orders)
);

Это нужно проделать для каждой таблицы с автоинкрементом — легко забыть хотя бы одну, и баг проявится не сразу при тестировании (там обычно вставляют мало строк), а через недели в проде, когда счётчик наконец «доедет» до первого занятого id.

NULL, пустые строки и строгая типизация — где запросы падают

MySQL в режиме, отличном от строгого (sql_mode без STRICT_TRANS_TABLES), исторически прощает много вольностей: пустая строка '', вставленная в числовую колонку, молча превращается в 0; некорректная дата вроде '0000-00-00' может попасть в колонку DATE; сравнение строки с числом происходит с неявным приведением типов почти везде. Годами написанный код мог полагаться на это поведение, даже не подозревая об этом — запрос вида WHERE user_id = '' в MySQL просто не находил совпадений (или находил строки с user_id = 0) и не считался ошибкой.

PostgreSQL заметно строже к неявным преобразованиям. Тот же запрос:

SELECT * FROM users WHERE user_id = '';

упадёт с ошибкой invalid input syntax for type integer: "" — PostgreSQL не станет молча приводить пустую строку к числу, он вернёт ошибку типа прямо на этапе разбора запроса. То же самое с датами: значение '0000-00-00', которое MySQL мог сохранить в некоторых режимах, PostgreSQL не примет вообще — date/time field value out of range. Если такие значения уже сидят в дампе (а после многолетней эксплуатации MySQL-базы они почти всегда есть), импорт данных упадёт или потребует предварительной чистки: замены '0000-00-00' на NULL, приведения пустых строк в числовых/датовых колонках к NULL до загрузки.

Отдельная тонкость — код, который в MySQL «случайно работал» из-за неявных преобразований (например, WHERE some_column = 0 неожиданно захватывал и NULL, и пустые строки из-за приведения типов), в PostgreSQL начнёт возвращать другой набор строк — не с ошибкой, а тихо, что опаснее любого падения. Это главный аргумент в пользу построчного тестирования запросов, а не только проверки, что данные успешно загрузились.

Синтаксис запросов: LIMIT/OFFSET, конкатенация строк, работа с датами

Часть SQL, которая казалась «стандартной», на деле — диалектные расширения конкретной СУБД, и после переноса они просто не парсятся.

LIMIT/OFFSET. MySQL поддерживает укороченную запись LIMIT offset, count:

-- MySQL: пропустить 20, взять 10
SELECT * FROM orders LIMIT 20, 10;

PostgreSQL такой синтаксис не понимает вообще — только явную форму LIMIT count OFFSET offset:

-- PostgreSQL: тот же результат
SELECT * FROM orders LIMIT 10 OFFSET 20;

Если в коде пагинация собиралась строкой с LIMIT $offset, $count, после переноса это будет чистая синтаксическая ошибка (syntax error at or near ",") — на каждом запросе с пагинацией.

Конкатенация строк. В MySQL для склейки строк используют функцию CONCAT(a, b, c), а оператор || по умолчанию означает логическое ИЛИ (если не включён режим PIPES_AS_CONCAT). В PostgreSQL всё наоборот: || — оператор конкатенации, а CONCAT() тоже существует, но ведёт себя иначе при NULL — CONCAT('a', NULL, 'b') вернёт 'ab' (NULL игнорируется), тогда как 'a' || NULL — это NULL целиком. Если переносите запросы с MySQL CONCAT(), где на NULL-полях ожидалась «прозрачная» склейка, а в PostgreSQL написали через || — результат начнёт превращаться в NULL везде, где хоть одно поле пустое. Аналогично GROUP_CONCAT() нужно заменять на STRING_AGG(column, ', ') — прямого аналога с тем же именем нет.

Даты. MySQL: DATE_ADD(created_at, INTERVAL 1 DAY), DATEDIFF(a, b) возвращает разницу в днях. PostgreSQL: сложение делается напрямую через интервал — created_at + INTERVAL '1 day', а функции DATEDIFF в PostgreSQL нет вообще — разницу между датами получают вычитанием (a::date - b::date, результат — целое число дней) или через EXTRACT(EPOCH FROM (a - b)) / 86400 для полных timestamp с точностью до секунд. Прямой перенос запроса с DATEDIFF даст function datediff does not exist — нужно переписывать, не подставлять по аналогии.

Практический вывод: не надейтесь, что SQL-запросы перенесутся как есть только потому, что синтаксис похож. Пройдитесь по кодовой базе grep-ом по LIMIT.*,, CONCAT, GROUP_CONCAT, DATEDIFF, DATE_ADD, DATE_SUB, NOW() внутри строковых конкатенаций — почти каждое из этих мест потребует правки.

Транзакции и уровни изоляции по умолчанию

Здесь разница тоньше, но способна вызвать баги, которые проявляются только под нагрузкой. По умолчанию InnoDB в MySQL использует уровень изоляции REPEATABLE READ — с блокировками по диапазону (gap locks), которые предотвращают часть фантомных чтений. PostgreSQL по умолчанию работает на уровне READ COMMITTED — более слабом: два одинаковых SELECT внутри одной и той же транзакции могут вернуть разные результаты, если между ними другая транзакция закоммитила изменения. Код, который неявно полагался на «стабильность» повторных чтений внутри длинной транзакции (отчёт, который несколько раз перечитывает одни и те же строки и ожидает согласованности), после переноса может начать вести себя иначе — без единой ошибки, просто с другими цифрами.

Отдельная деталь — поведение DDL. В PostgreSQL операции изменения схемы (CREATE TABLE, ALTER TABLE, DROP TABLE) транзакционны: их можно завернуть в BEGIN ... COMMIT и откатить при ошибке вместе с остальными изменениями. В MySQL большинство DDL-команд вызывают неявный commit — начатая транзакция фиксируется автоматически, откат до DDL уже невозможен. Скрипты миграции схемы, написанные с оглядкой на поведение MySQL, на PostgreSQL продолжат работать, но стоит знать: теперь откат схемы внутри транзакции — рабочий инструмент.

Если приложение использует SELECT ... FOR UPDATE или полагается на конкретный паттерн блокировок для борьбы с гонками (резервирование остатков товара), после переноса стоит отдельно прогнать нагрузочный тест на конкурентный доступ — детали блокировок (гранулярность, поведение при дедлоках) между СУБД отличаются, и полагаться, что «раз работало на MySQL — будет работать так же», не стоит.

Инструменты миграции: pgloader вместо ручного переписывания дампа

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

Наиболее распространённый вариант — pgloader: он умеет подключаться напрямую к работающей MySQL-базе (не через промежуточный дамп), сам конвертирует схему, типы данных, переносит данные и создаёт индексы и внешние ключи после загрузки (это быстрее, чем поддерживать их во время вставки). Базовый запуск:

pgloader mysql://user:password@localhost/mydb \
         postgresql://user:password@localhost/mydb

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

LOAD DATABASE
     FROM mysql://user:password@localhost/mydb
     INTO postgresql://user:password@localhost/mydb

WITH include drop, create tables, create indexes,
     reset sequences, foreign keys, downcase identifiers

SET work_mem to '256MB', maintenance_work_mem to '512MB'

CAST type tinyint to smallint,
     type datetime to timestamptz

ALTER SCHEMA 'mydb' RENAME TO 'public';

Стоит знать про нюансы самого pgloader: он по умолчанию конвертирует MySQL tinyint(1) в boolean — удобно, если колонка была флагом, но неверно, если в ней реально хранили маленькие целые числа (нужно переопределить через CAST). Также после миграции полезно свериться, что reset sequences действительно отработал (см. раздел про автоинкременты выше) — не полагайтесь на это молча, проверьте setval вручную по паре ключевых таблиц.

Из альтернатив: для более контролируемого переноса можно снять логический дамп через mysqldump --compatible=postgresql (частичная помощь, не полная конвертация) и прогнать его через отдельные конвертеры схемы, либо переносить данные через промежуточный CSV с явным маппингом типов колонка за колонкой — дольше, но даёт контроль там, где автоматика ошибается на нестандартных схемах.

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

Частые вопросы