Как мигрировать с MySQL на PostgreSQL: типы, данные, проверка и типичные ошибки

Веб-разработчики с опытом MySQL сталкиваются с миграцией на PostgreSQL, когда проект перерастает простой CRUD: нужны JSON-поля с индексами, аналитика, сложные выборки, отчёты. При переносе данных и схемы легко наступить на грабли — неправильные типы, отсутствие индексов, попытки писать под Postgres «как под MySQL». Ниже — пошаговое решение: отличия типов, миграция данных, проверка и типичные ошибки.

В чём проблема

При миграции с MySQL на PostgreSQL типичные симптомы: падения запросов из-за несовместимого синтаксиса, потери или искажения данных при конвертации типов, медленные выборки из-за отсутствия индексов на больших таблицах. Ошибки часто возникают уже на этапе переноса схемы и данных. В логах — ошибки вида column "id" does not exist, syntax error at or near, или долгий Seq Scan в EXPLAIN. Причина: отличия типов (AUTO_INCREMENT vs SERIAL, TINYINT(1) vs BOOLEAN, JSON vs JSONB), другая семантика строк и дат, а также незнание инструментов миграции и проверки.

Сравнение для выбора:

PostgreSQL vs MySQL (когда что выбирать)

Критерий	PostgreSQL	MySQL
Типы данных	Богатые (JSONB, ARRAY, UUID)	Базовые
JSON	JSONB + индексы	JSON без индексов
Аналитика	Window Functions, CTE	Ограниченно
Расширяемость	EXTENSION	Почти нет
Лицензия	PostgreSQL License	GPL / коммерция
Full-text	Встроенный	Слабее
Когда выбирать	Сложные запросы, аналитика	Простой CRUD

Коротко: CRUD-блог или простой сайт — часто достаточно MySQL; API, аналитика, JSON, отчёты — лучше PostgreSQL, чтобы не упираться в ограничения позже.

Рабочее решение

Установка PostgreSQL

Ubuntu / Debian

sudo apt update
sudo apt install postgresql postgresql-contrib

CentOS / Rocky Linux

sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

macOS (Homebrew)

brew install postgresql@16
brew services start postgresql@16

Windows (WSL2)

Установи Ubuntu в WSL2
Далее — как для Ubuntu
Работай только внутри WSL, не из-под Windows.

Первое подключение

sudo -u postgres psql

Типичная ошибка:

psql: FATAL: role "user" does not exist

Решение:

sudo -u postgres createuser --interactive

Типы данных: отличия от MySQL

INTEGER, SERIAL vs AUTO_INCREMENT

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL
);

SERIAL — это sugar над sequence. В PostgreSQL это нормально.

VARCHAR vs TEXT

email TEXT

В PostgreSQL нет разницы по производительности между ними. TEXT — твой друг.

BOOLEAN vs TINYINT(1)

is_active BOOLEAN DEFAULT true

Без 0/1. Человечно.

JSONB

CREATE TABLE products (
  id SERIAL,
  attrs JSONB
);

SELECT *
FROM products
WHERE attrs @> '{"color": "red"}';

JSONB индексируется и реально работает. Подробно: снипет «JSONB и GIN-индекс».

ARRAY

tags TEXT[];

SELECT *
FROM articles
WHERE 'postgres' = ANY(tags);

Массивы прямо в БД — иногда очень удобно.

UUID

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE events (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT
);

TIMESTAMPTZ vs TIMESTAMP

created_at TIMESTAMPTZ DEFAULT now()

Всегда используй TIMESTAMPTZ. Внутри хранится UTC, локаль выставляется на уровне приложения.

Фичи PostgreSQL, которых нет в MySQL

Window Functions

SELECT
  product_id,
  category_id,
  RANK() OVER (PARTITION BY category_id ORDER BY sales DESC)
FROM products;

Без подзапросов и боли.

CTE (WITH)

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT COUNT(*) FROM paid_orders;

Читаемо и удобно.

Full-Text Search

SELECT *
FROM articles
WHERE to_tsvector(title || ' ' || body)
      @@ plainto_tsquery('postgres');

LISTEN / NOTIFY

LISTEN order_created;
NOTIFY order_created, '{"id":123}';

Простейшая очередь без Kafka.

Практические примеры

Пример 1: e-commerce

SELECT
  o.id,
  SUM(oi.price * oi.qty) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

Пример 2: поиск + фильтр

SELECT *
FROM products
WHERE to_tsvector(name)
      @@ plainto_tsquery('iphone')
AND price < 50000;

Пример 3: JSON

SELECT *
FROM products
WHERE attrs->>'brand' = 'Apple';

Пример 4: Window Functions

SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
) t
WHERE rn <= 3;

Пример 5: отчёт по продажам за период (CTE)

WITH daily_totals AS (
  SELECT
    date_trunc('day', created_at) AS day,
    SUM(total) AS revenue
  FROM orders
  WHERE status = 'paid'
    AND created_at >= now() - interval '30 days'
  GROUP BY date_trunc('day', created_at)
)
SELECT
  day::date,
  revenue,
  SUM(revenue) OVER (ORDER BY day) AS revenue_cumulative
FROM daily_totals
ORDER BY day;

Пример 6: выборка с JSONB и агрегацией

-- Товары с атрибутом «цвет» и средняя цена по бренду
SELECT
  attrs->>'brand' AS brand,
  COUNT(*) AS cnt,
  ROUND(AVG((attrs->>'price')::numeric), 2) AS avg_price
FROM products
WHERE attrs ? 'color'
GROUP BY attrs->>'brand'
HAVING COUNT(*) > 5;

Миграция с MySQL на PostgreSQL

Подготовка

mysqldump db > dump.sql

Основные правки схемы

AUTO_INCREMENT → SERIAL
VARCHAR(255) → TEXT
TINYINT(1) → BOOLEAN

Способ А: pgloader (рекомендую)

pgloader mysql://user:pass@localhost/db postgresql://user:pass@localhost/db

Подходит для больших баз.

Способ Б: SQL-конвертер

Работает, но требует ручной доработки.

Проверка данных

SELECT COUNT(*) FROM users;

Миграция схемы в Laravel (PostgreSQL)

После переноса данных приложение должно создавать таблицы под Postgres. Пример миграции:

// database/migrations/xxxx_create_products_table.php
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->jsonb('attrs')->nullable();  // не json(), а jsonb()
    $table->boolean('is_active')->default(true);
    $table->timestamps();
});
// Индекс по JSONB для поиска
DB::statement('CREATE INDEX idx_products_attrs_gin ON products USING GIN (attrs)');

Откат

Всегда держи MySQL read-only, пока не проверил всё.

ORM и клиенты

Node.js (pg — нативный клиент)

Готовый снипет с Pool и параметризованными запросами: PostgreSQL: Node.js (pg) — Pool и запросы.

const { Pool } = require('pg');
const pool = new Pool({
  host: 'localhost',
  database: 'myapp',
  user: 'app_user',
  password: process.env.DB_PASSWORD,
});

// Параметризованный запрос (защита от SQL-инъекций)
const res = await pool.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]
);

// JSONB-поле
const products = await pool.query(
  `SELECT * FROM products WHERE attrs @> $1`,
  [JSON.stringify({ color: 'red' })]
);

Prisma (схема под PostgreSQL):

generator client {
  provider = "prisma-client-js"
}
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
model User {
  id    Int    @id @default(autoincrement())
  email String
}

TypeORM — аналогично, в ormconfig укажи type: 'postgres'.

Python (psycopg2)

import psycopg2
from psycopg2.extras import RealDictCursor

conn = psycopg2.connect(
    host="localhost", dbname="myapp",
    user="app_user", password="secret"
)

with conn.cursor(cursor_factory=RealDictCursor) as cur:
    cur.execute(
        "SELECT * FROM orders WHERE created_at > %s",
        (datetime.now() - timedelta(days=7),)
    )
    rows = cur.fetchall()

PHP

Doctrine — driver: pdo_pgsql, типы как в документации.
Laravel Eloquent — в .env ставишь DB_CONNECTION=pgsql, миграции те же, для JSONB используй $table->jsonb('attrs').

ORM vs raw SQL

CRUD → ORM
отчёты, аналитика → сырой SQL

EXPLAIN ANALYZE

Перед тем как оптимизировать — замерь. Пошаговый снипет: EXPLAIN ANALYZE — как найти медленный запрос. Запрос:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';

Пример вывода без индекса (плохо):

Seq Scan on orders  (cost=0.00..1234.00 rows=5000 width=80) (actual time=0.05..89.22 rows=4823 loops=1)
  Filter: (created_at > (now() - '7 days'::interval))
  Rows Removed by Filter: 95177
Planning Time: 0.12 ms
Execution Time: 91.45 ms

Seq Scan по всей таблице, отфильтровано 95k строк — дорого.

После создания индекса CREATE INDEX idx_orders_created_at ON orders(created_at);:

Index Scan using idx_orders_created_at on orders  (cost=0.42..312.18 rows=5000 width=80) (actual time=0.03..8.11 rows=4823 loops=1)
  Index Cond: (created_at > (now() - '7 days'::interval))
Planning Time: 0.08 ms
Execution Time: 9.02 ms

Index Scan — в разы быстрее. Ориентируйся на:

Seq Scan на больших таблицах — кандидат на индекс.
Index Scan / Index Only Scan — хорошо.
Если rows в плане сильно отличается от actual rows — обнови статистику: ANALYZE orders;.

Резервные копии

Готовые команды и варианты форматов: снипет «pg_dump и pg_restore».

pg_dump

pg_dump db > backup.sql
pg_dump -s db > schema.sql

pg_restore

pg_restore -d db backup.dump

Cron

0 3 * * * pg_dump db | gzip > /backup/db.sql.gz

S3 (пример)

aws s3 cp backup.sql.gz s3://my-backups/

Мониторинг и оптимизация

Логи медленных запросов

В postgresql.conf или через ALTER SYSTEM:

ALTER SYSTEM SET log_min_duration_statement = 500;  -- логировать запросы дольше 500 ms

Индексы

CREATE INDEX idx_orders_created_at ON orders(created_at);

Нет индекса на created_at в большой таблице = боль.

VACUUM

PostgreSQL не чистит всё за тебя идеально. Важен автовакуум и периодический ANALYZE.

Размеры

SELECT pg_database_size('db');
SELECT pg_table_size('orders');

Проверка результата

После миграции данных — убедись, что строки на месте и типы корректны:

SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;

Сравни с количеством строк в MySQL. Выборочно проверь даты и JSON-поля: SELECT id, created_at, attrs FROM products LIMIT 5;.

Медленные запросы — используй EXPLAIN (ANALYZE, BUFFERS) (см. раздел выше). Ориентируйся на: Seq Scan на больших таблицах — кандидат на индекс; Index Scan / Index Only Scan — норма. Если план сильно расходится с фактом по rows — выполни ANALYZE table_name;.

Резервная копия перед продакшен-переключением — убедись, что pg_dump и pg_restore проходят без ошибок на тестовой копии БД. Держи MySQL в read-only до полной проверки приложения на Postgres.

Типичные ошибки

Забыли COMMIT или долго держим транзакцию открытой

Симптом: блокировки, другие сессии ждут, при падении приложения откат большого объёма. Причина: в PostgreSQL транзакция живёт до явного COMMIT/ROLLBACK. Как исправить: делай короткие транзакции, не держи соединение открытым на время логики приложения, в коде явно коммить или откатывать.

Сортировка и фильтрация по полям без индекса

Симптом: в EXPLAIN — Seq Scan, запрос выполняется сотни миллисекунд на больших таблицах. Причина: нет индекса по колонкам в WHERE, ORDER BY, GROUP BY. Как исправить: добавь индекс под частые запросы, например CREATE INDEX idx_orders_created_at ON orders(created_at);, затем ANALYZE orders;.

TRUNCATE … CASCADE без понимания последствий

Симптом: удалились данные из связанных таблиц, на которые не рассчитывал. Причина: TRUNCATE ... CASCADE сносит данные в дочерних таблицах по FK. Как исправить: перед запуском проверь зависимости, используй TRUNCATE ... RESTART IDENTITY CASCADE только когда уверен в списке таблиц; для выборочной очистки — DELETE с условием.

Дедлоки при конкурентных транзакциях

Симптом: ошибка deadlock detected. Причина: две и более транзакции блокируют ресурсы в разном порядке. Как исправить: повторяй запрос при получении ошибки дедлока (retry в коде), упорядочивай блокировки (например, всегда обновляй строки в одном порядке по id).

JSON вместо нормальной таблицы для структурированных данных

Симптом: сложные запросы по полям внутри JSON, медленные выборки. Причина: данные, которые по смыслу сущности (поля, связи), запихнуты в одно JSON-поле. Как исправить: вынеси стабильные поля в отдельные колонки с индексами; JSONB оставь для действительно гибких атрибутов.

Где применять

Миграция с MySQL на PostgreSQL — когда проект перерастает CRUD, нужны JSON с индексами, аналитика, сложные запросы.
Новый веб-проект (API, отчёты, очереди) — выбор Postgres с самого начала избавляет от миграции потом.
Production — после переноса данных проверь счётчики, выполни ANALYZE, настрой бэкапы и мониторинг медленных запросов.

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

Требования