Что нужно учесть при миграции с Mysql в Postgres

5 min readJul 26, 2020

Многие сейчас работают с базами через обёртки ORM/ActiveRecord, которые нивелируют большинство различий между базами, и на первый взгляд может показаться, что достаточно поправить пару написанных ручками запросов, подключить новую базу и поправить драйвер в конфиге библиотеки. Но вряд ли вам действительно легко удастся провернуть такой финт, чтобы ничего не отвалилось. Пристальное внимание следует уделить запросам “ручками”

Кавычки

Если в mysql мы можем использовать для строковых данных как одинарные, так и двойные кавычки, то postgres для строк требует только одинарные, двойные используются для системных обозначений — названий полей, таблиц, и т.п. В mysql для полей и таблиц используются `обратные кавычки`

-- Этот комментарий поймут и Postgres и MySql
# А на такой Postgres будет ругаться!

РегистроЗависимость

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

select name, myText from my_table;
select Name, mytext from my_table;`

— вернут одинаковые данные (Но! имена полей будут такими, как вы указали в запросе!)

Но имена баз и таблиц чувствительны к регистру.

Postgres такого не допускает. Вы можете иметь 2 разных поля с названиями userId и userid. Если вы хотите использовать поле, содержащее заглавные буквы, то его обязательно нужно заключать в кавычки.

select name, myText from my_table; -- Не отработает, myText будет читаться как mytext
select name, "myText" from my_table; -- Правильно

Так же стоит учесть, что оператор LIKE в Mysql регистронезависим. В постгрес для такого поиска следует использовать оператор ILIKE, а LIKE учитывает регистр

Операторы

В mysql операторы && эквивалентен AND, а так же || эквивалентен OR, в postgres оператор || используется для конкатенации строк,
В mysql оператор ^ используется как битовый XOR, в postgres для этого используется #, а ^ для возведения в степень.
По умолчанию не умеет SOUNDS LIKE, но при необходимости можно воткнуть расширение fuzzystrmatch

Строгость к данным.

Хотя различные параметры для ограничений sql_mode в mysql присутствовали давно, полноценной “строгостью” к введенным данным, причём по умолчанию, он обзавелся только к версии 5.7 Поэтому, если вы использовали более ранние версии mysql, или новые, но с отключенными strict_mode, то переезд в postgres может преподнести вам много боли.

В первую очередь — при попытке вставить данные большего размера, чем позволяет поле базы.

И во вторую — запросы c группировкой. В mysql без параметра ‘only_full_group_by’ были допустимы запросы вида

select *, count(id) from my_table group by some_field;

В postgres все поля, которые не участвуют в группировке мы должны задать явно с указанием аггрегирующей функции.

Например для таблицы

create table users
(
 id bigserial primary key,
 name varchar(255) not null,
 age smallint default 0 null,
 department varchar(20) not null
);

Запрос может иметь вид:

select count(id), avg(age), array_agg(name), department from users group by department;

Типы данных и их размерности

Полностью можно ознакомиться в официальной документации: [MySql] [Postgres] Я отмечу здесь только некоторые нюансы

тип bool, который по факту в MySql является алиасом к tinyint(1) и умеет принимать значения 0/1. В postgres boolean — отдельный тип, который принимает значения (false|true)(‘yes’|‘no’) (‘f’|‘t’) (‘y’|‘n’) (‘0’|‘1’) — но при этом будет ругаться на 0|1 переданный без кавычек как на значение типа integer. И это запросто может стать закавыкой при миграции из mysql; Для postgres эти значения придётся либо взять в кавычки, либо явно обозначить как 0::bool, 1::bool
аналога tinyint в postgres нет. Минимальный размер — int2 (smallint) со значениями в диапазоне -32768 to +32767
нет прямого аналога unsigned, если нужна проверка на беззнаковость, то добавьте соответствующую проверку

CREATE TABLE users (
  id serial primary key, 
  name  varchar(255),
  age smallint CHECK(age > 0) 
);

Полям с датами следует уделить особое внимание. Значительные различия имеют функции работы с датой и временем. [mysql] [postgres]
Часто используемая в mysql фишка для обновления даты при изменении строки updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT TIMESTAMPв postgres не поддерживается. Для такого функционала придётся написать триггер (код легко гуглится). Или разруливать на уровне приложения. (Просто DEFAULT CURRENT_TIMESTAMP сработает)
JSON: идентичен при простых операциях вставки значений, но значительные отличия в синтаксисе доступа и совершенно разные функции. Так же следует учесть что в postgres есть более эффективный аналог jsonb (чем отличается?)
со spatial/geo -типами в mysql не работала, поэтому ничего отметить не могу, но подозреваю что различий предостаточно
В postgres отсутствует аналог “SET” . Из вариантов — ближе всего — массив с дополнительными проверками
Альтернативой Binary типу в postgres служит Bytea, но напрямую данные из одного в другой сконвертировать не получится. Решения гуглятся
Enum — postgres умеет, но создается немного по-другому: сначала создаётся тип с нужными значениями, затем при создании таблицы можно задать полю созданный тип

CREATE TYPE job_state AS ENUM ('pending', 'active', 'fail', 'success');CREATE TABLE jobs (
    id bigserial,
    name varchar(255),
    payload jsonb,
    state  job_state
);INSERT INTO jobs (name, payload, state) 
VALUES ('do it', {"foo": "bar"}, 'pending')

Создание/изменение структуры таблицы

В отличие от mysql, postgres поддерживает транзакции для создания/изменения таблиц и столбцов, а не только для данных.
postgres не умеет оперировать порядком столбцов BEFORE/AFTER конструкции не поддерживаются, только пересоздавать структуру.
есть особенности для изменения NULL/NOT NULL и значения по умолчанию. Если в mysql мы можем изменить всё одним запросом

ALTER TABLE jobs MODIFY name varchar(100) NULL DEFAULT 'cron_job' COMMENT 'job name';

То в postgres аналогичная операция будет выглядеть так

ALTER TABLE jobs ALTER COLUMN name TYPE varchar(100);
ALTER TABLE jobs ALTER COLUMN name DROP NOT NULL; 
ALTER TABLE jobs ALTER COLUMN name SET DEFAULT 'cron_jobs';
COMMENT ON COLUMN "jobs"."name" IS 'job name';

Mysql не поддерживает значения по умолчанию для типов BLOB,TEXT, JSON, GEOMETRY. В postgres c этим проблем нет.

Вычисляемые поля

Postgres младше 12 версии не умеет в вычислимые поля (generated columns) — только через триггеры делать

ALTER TABLE my_box ADD COLUMN volume integer as (width * height * len);

Для 12-й версии аналогичная операция будет с таким запросом

ALTER TABLE my_box ADD COLUMN volume integer GENERATED ALWAYS AS (width * height * len) STORED;

Autoincrement Index

В отличие от mysql, postgres может иметь несколько последовательстей (sequences) на одну таблицу, информация о которых хранится отдельно в служебной нформации

Возврат вставленного ID: можно получить сразу в запросе вставки данных

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

Функции currval и nextval для последнего вставленного и следующего id.

SELECT currval(pg_get_serial_sequence('users', 'id'));

Изменить следующее значение id

SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT MAX(id) FROM users) + 1000);

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

TRUNCATE TABLE users RESTART IDENTITY;

Полнотекстовый поиск

Если у вас используется встроенный полнотекстовый поиск, то тоже стоит подготовиться к значительным изменениям, и основательно проштудировать документацию,
https://eax.me/postgresql-full-text-search
статья на хабре
fuzzy text search

Как мигрировать данные

PgChameleon https://pgchameleon.org/documents/
PgLoader https://pgloader.io/ и другие конвертеры
сдампить в csv или json, обработать напильником самостоятельно и залить
перекачать через любимую orm -ку с зараннее подготовленной структурой новой базы

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