Skip to main content

MySQL → PostgreSQL миграция

Ниже — “боевой” runbook и чеклист миграции MySQL 5.7 → PostgreSQL для Laravel 8 на Ubuntu 22.04, PHP 7.4, один сервер (VM), без контейнеров. Я ориентируюсь на безопасный сценарий с контролем и возможностью быстрого отката.

0) Важные вводные и ограничения

⚠️ Критично важно​

  • PHP 7.4 на Ubuntu 22.04 = потенциально нестандартная установка. После миграции БД это не ломается напрямую, но любые обновления пакетов на сервере делайте осторожно.
  • Laravel 8 + PostgreSQL работает нормально, но raw SQL и некоторые пакеты часто требуют правок.
  • Миграция “в один шаг” = downtime. Мы сделаем так, чтобы downtime был предсказуемым и минимальным.

1) Цель миграции и стратегия

Цель​

Перенести данные из MySQL 5.7 в PostgreSQL, переключить Laravel на pgsql, убедиться, что:

  • данные не потерялись;
  • бизнес-функции работают;
  • интеграции не упали;
  • можно быстро откатиться.

Стратегия (рекомендуемая)​

Вариант A: “maintenance + финальный дамп + загрузка + переключение” Это самый надёжный вариант для одного сервера.

2) Подготовка (за 1–3 дня до миграции)

2.1. Инвентаризация (обязательный минимум)​

Проверить объём данных​

sudo du -sh /var/lib/mysql
mysql -e "SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,1) MB FROM information_schema.tables GROUP BY table_schema;"

Проверить самые большие таблицы​

SELECT
  table_name,
  ROUND((data_length+index_length)/1024/1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length+index_length) DESC
LIMIT 30;

2.2. Поиск потенциально проблемных SQL мест в Laravel​

Поиск raw SQL​

grep -R --line-number --ignore-case \
  -e "selectRaw" -e "whereRaw" -e "orderByRaw" \
  -e "DB::select" -e "DB::statement" -e "DB::raw" \
  -e "mysql" -e "json_extract" -e "date_format" -e "ifnull" \
  -e "find_in_set" -e "regexp" \
  ./app ./routes ./database

Что чаще всего ломается при переходе на PostgreSQL​

  • IFNULL() → COALESCE()
  • DATE_FORMAT() → TO_CHAR()
  • CONCAT() (в PG есть, но иногда проще через ||)
  • GROUP BY без полного списка колонок
  • LIKE/сортировка и регистр (MySQL часто case-insensitive)
  • JSON функции (JSON_EXTRACT, -> в MySQL) → другие операторы PG

2.3. Установка PostgreSQL на Ubuntu 22.04​

Установка​

sudo apt update
sudo apt install -y postgresql postgresql-contrib

Проверка:

sudo systemctl status postgresql
psql --version

2.4. Создание базы и пользователя PostgreSQL​

sudo -u postgres psql

Внутри psql:

CREATE USER app_user WITH PASSWORD 'STRONG_PASSWORD';
CREATE DATABASE app_db OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;
\q

2.5. Включить полезные расширения PostgreSQL​

sudo -u postgres psql -d app_db
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
\q

2.6. Установка инструмента миграции (pgloader)​

Установка​

sudo apt install -y pgloader
pgloader --version

3) Репетиция миграции (строго рекомендуется)

⚠️ Почему это обязательно​

Без репетиции вы почти гарантированно получите:

  • неожиданные ошибки типов;
  • проблемы с кодировками;
  • проблемы с unique/foreign key;
  • падение на “нулевых датах”.

3.1. Сделать копию MySQL базы в отдельную БД (локально)​

mysqldump --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 \
  -u root -p app_db > /root/app_db_dump.sql

Создать staging базу:

mysql -u root -p -e "CREATE DATABASE app_db_staging CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p app_db_staging < /root/app_db_dump.sql

3.2. Создать staging базу в PostgreSQL​

sudo -u postgres psql
CREATE DATABASE app_db_pg_staging OWNER app_user;
\q

3.3. Прогнать pgloader (репетиция)​

pgloader \
  mysql://root:MYSQL_PASSWORD@localhost/app_db_staging \
  postgresql://app_user:STRONG_PASSWORD@localhost/app_db_pg_staging

3.4. Проверить приложение на staging​

Сделайте временный .env.pg_staging и запустите приложение на другом порту/виртуальном хосте.

4) Подготовка к “окну миграции” (за 12–24 часа)

4.1. Зафиксировать релиз приложения​

  • Один commit.
  • Никаких deploy во время миграции.
  • Никаких миграций схемы.

4.2. Подготовить “режим остановки записи”​

Что нужно остановить​

  • Laravel приложение (maintenance)
  • очереди (Supervisor)
  • cron
  • любые внешние скрипты, которые пишут в MySQL

4.3. Подготовить план отката (1 минута)​

Откат = вернуть .env на MySQL​

  • включить maintenance
  • остановить воркеры
  • вернуть DB_CONNECTION=mysql
  • перезапуск php-fpm
  • снять maintenance
  • включить воркеры

Важно: MySQL во время миграции не удаляем и не меняем.

5) День миграции: пошаговый runbook

5.0. Зафиксировать время старта​

Просто запишите время в файл:

date | tee -a /root/migration_log.txt

5.1. Включить maintenance mode​

cd /var/www/app
php artisan down --render="errors::503"

Проверить:

  • сайт отдаёт maintenance страницу
  • вебхуки не создают записи (или хотя бы не проходят дальше)

5.2. Остановить очереди и фоновые процессы​

Supervisor (типовой случай)​

sudo supervisorctl status
sudo supervisorctl stop all

Если воркеры через systemd:

sudo systemctl stop laravel-worker.service

5.3. Остановить cron, который пишет в MySQL​

Если задачи в /etc/crontab или /etc/cron.d/*, временно закомментировать или остановить cron:

sudo systemctl stop cron

5.4. Убедиться, что MySQL больше не получает записи​

Быстрая проверка активных запросов​

SHOW FULL PROCESSLIST;

Проверка, что нет постоянных INSERT/UPDATE​

(смотрите по PROCESSLIST, либо по application logs)

5.5. Финальный дамп MySQL (боевой)​

mysqldump --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 \
  -u root -p app_db > /root/app_db_final.sql

Проверка, что дамп не пустой​

ls -lh /root/app_db_final.sql
tail -n 30 /root/app_db_final.sql

5.6. Создать “боевую” базу PostgreSQL​

sudo -u postgres psql
DROP DATABASE IF EXISTS app_db_pg;
CREATE DATABASE app_db_pg OWNER app_user;
\q

5.7. Перенести данные MySQL → PostgreSQL​

Вариант 1 (рекомендуется): pgloader напрямую из MySQL​

pgloader \
  mysql://root:MYSQL_PASSWORD@localhost/app_db \
  postgresql://app_user:STRONG_PASSWORD@localhost/app_db_pg

⚠️ Если pgloader падает​

Это нормально. Частые причины:

  • “нулевые даты”
  • несовместимые enum
  • проблемы с encoding
  • foreign keys

В этом случае:

  • сначала переносим таблицы без FK,
  • затем вручную добавляем FK и индексы.

5.8. Обязательные post-load команды PostgreSQL​

sudo -u postgres psql -d app_db_pg -c "ANALYZE;"

Если база крупная:

sudo -u postgres psql -d app_db_pg -c "VACUUM (ANALYZE);"

5.9. Проверка sequences (критично)​

Почему это важно​

Если sequence не выставить на max(id), новые INSERT будут падать с: duplicate key value violates unique constraint

Скрипт проверки:

SELECT
  sequence_name
FROM information_schema.sequences
WHERE sequence_schema = 'public';

Типовой фикс (пример для users.id):

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

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

6) Верификация данных (до включения сайта)

6.1. Row count по ключевым таблицам​

В MySQL​

SELECT 'users' as t, COUNT(*) FROM users
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'payments', COUNT(*) FROM payments;

В PostgreSQL​

SELECT 'users' as t, COUNT(*) FROM users
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'payments', COUNT(*) FROM payments;

6.2. Контрольные суммы (выборочно)​

Это не идеально, но быстро ловит “разъехалось”.

Пример:

  • берём таблицу
  • считаем сумму id и сумму длины строки

MySQL:

SELECT
  COUNT(*) cnt,
  SUM(id) sum_id
FROM users;

Postgres:

SELECT
  COUNT(*) cnt,
  SUM(id) sum_id
FROM users;

6.3. Проверка дат (нулевые даты)​

Если MySQL позволял 0000-00-00, то в Postgres они не попадут.

Ищем в MySQL:

SELECT COUNT(*) FROM orders WHERE created_at = '0000-00-00 00:00:00';

7) Переключение Laravel на PostgreSQL

7.1. Установить pgsql драйвер для PHP 7.4​

Проверить:

php -m | grep pgsql
php -m | grep pdo_pgsql

Если нет:

sudo apt install -y php7.4-pgsql
sudo systemctl restart php7.4-fpm

7.2. Обновить .env​

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=app_db_pg
DB_USERNAME=app_user
DB_PASSWORD=STRONG_PASSWORD

7.3. Очистить кеши Laravel​

cd /var/www/app

php artisan config:clear
php artisan cache:clear
php artisan route:clear
php artisan view:clear

php artisan config:cache
php artisan route:cache

7.4. Перезапустить php-fpm и nginx​

(зависит от вашей версии)

sudo systemctl restart php7.4-fpm
sudo systemctl restart nginx

7.5. Снять maintenance mode​

php artisan up

8) Включить очереди и cron

8.1. Supervisor​

sudo supervisorctl start all
sudo supervisorctl status

8.2. Cron​

sudo systemctl start cron
sudo systemctl status cron

9) Мониторинг после миграции (первые 2 часа)

9.1. Логи приложения​

tail -f /var/www/app/storage/logs/laravel.log

9.2. Логи nginx​

sudo tail -f /var/log/nginx/error.log
sudo tail -f /var/log/nginx/access.log

9.3. PostgreSQL: активность​

sudo -u postgres psql -d app_db_pg -c "
SELECT pid, usename, state, query_start, wait_event_type, wait_event, LEFT(query,120)
FROM pg_stat_activity
WHERE datname = 'app_db_pg'
ORDER BY query_start DESC;"

9.4. PostgreSQL: блокировки​

sudo -u postgres psql -d app_db_pg -c "
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked ON blocked.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;"

10) План отката (если что-то пошло не так)

⚠️ Когда откатываться​

  • массовые 500 ошибки
  • невозможность логина/создания ключевых сущностей
  • интеграции падают и блокируют бизнес
  • сильная деградация (например, сайт “живой”, но всё по 10 секунд)

10.1. Быстрый откат (3–5 минут)​

  1. Включить maintenance:
cd /var/www/app
php artisan down
  1. Остановить воркеры:
sudo supervisorctl stop all
sudo systemctl stop cron
  1. Вернуть .env на MySQL:
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=app_db
DB_USERNAME=...
DB_PASSWORD=...
  1. Очистить кеши:
php artisan config:clear
php artisan cache:clear
php artisan config:cache
  1. Перезапуск php-fpm:
sudo systemctl restart php7.4-fpm
sudo systemctl restart nginx
  1. Снять maintenance:
php artisan up
  1. Включить воркеры/cron:
sudo supervisorctl start all
sudo systemctl start cron

11) Что почти наверняка потребуется доработать в коде (Laravel 8 → PG)

11.1. Поиск по строкам​

Если у вас были запросы вида:

WHERE email LIKE '%test%'

В Postgres это работает, но может стать медленно. Для ускорения обычно:

  • pg_trgm + GIN индекс
  • либо нормализация (lowercase) и индексы

11.2. JSON​

Если вы используете MySQL JSON-функции — это будет самый частый источник падений.

11.3. Уникальность и регистр​

MySQL часто считает Test@x.com и test@x.com одинаковыми в уникальном индексе. Postgres — нет.

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

12) Итоговый чеклист “в одну страницу”

До миграции​

  • Репетиция миграции на staging
  • Готов PostgreSQL + пользователь + база
  • Готов pgloader
  • Проверены raw SQL места
  • Подготовлен план отката

Во время миграции​

  • php artisan down
  • stop supervisor + cron
  • финальный mysqldump
  • pgloader перенос
  • ANALYZE/VACUUM
  • setval sequences
  • row count + sanity checks
  • переключение .env
  • перезапуск php-fpm/nginx
  • php artisan up
  • старт воркеров + cron

После миграции​

  • мониторинг логов
  • мониторинг pg_stat_activity + блокировок
  • проверка интеграций
  • сверка бизнес-метрик

13) Что мне нужно от вас, чтобы сделать план ещё точнее (без воды)

Если вы дадите:

  1. примерный размер MySQL базы (например 5 GB / 50 GB / 200 GB)
  2. список самых критичных таблиц (3–10 штук)
  3. используете ли вы Laravel queues (database/redis)
  4. есть ли foreign keys реально или всё “на уровне приложения”

…я могу:

  • адаптировать runbook под ваш объём,
  • дать оптимальные параметры Postgres,
  • и написать готовые SQL-скрипты для автоматической проверки sequences и counts.