Переезд с MySQL на PostgreSQL в Laravel: как мы перенесли гео сервис и не сошли с ума

Привет! Недавно мы с проектом (а по факту я в гордом одиночестве) переезжали с MySQL на PostgreSQL. Зачем? Проект — гео‑сервис для рыбаков: карта с точками, поиск рядом, фильтры по регионам, водоёмам и рыбам. MySQL стал тормозить на гео‑запросах, а PostGIS обещал скорость и порядок. Плюс мы всё равно переезжали на новый VDS ^[1] – решили заодно сменить СУБД, пока данных не стало неприлично много.

Продом обкатано: читать будет полезно.

1. Исходные данные (чтобы понимали масштаб)

Проект не огромный, но и не игрушечный:

• ~200 объявлений, 8 рыбохозяйственных бассейнов, 85 регионов, 800 городов, 60 видов рыб, тысячи связей между ними.

• 26 миграций, накопившихся за пару лет разработки.

• Стек: Laravel 11, Next.js 15, Filament 3.3, MySQL 8.

2. Подготовка: что делать со старыми миграциями?

Проблема: 26 MySQL-миграций, которые несовместимы с PostgreSQL. Оставлять их нельзя – при следующем деплое всё упадёт. Переписывать 26 файлов – долго и больно.

Решение: мы сделали squashing (схлопывание) миграций. Проанализировали все изменения, собрали финальную структуру и написали одну миграцию для PostgreSQL. В ней:

• geometry(Point, 4326) вместо POINT,

• jsonb вместо json,

• boolean для флагов,

• string вместо ENUM (чтобы не мучиться с ENUM в PostgreSQL).

Фрагмент создания таблицы с геоданными:

php
Schema::create('product_locations', function (Blueprint $table) {
    $table->foreignId('product_id')->primary()->constrained()->onDelete('cascade');
    $table->decimal('latitude', 10, 7)->nullable();
    $table->decimal('longitude', 10, 7)->nullable();
    $table->timestampsTz();
});
// Добавляем геометрию и индексы
DB::statement('ALTER TABLE product_locations ADD COLUMN location geometry(Point, 4326) NOT NULL');
DB::statement('CREATE INDEX idx_location ON product_locations USING GIST (location)');
DB::statement('CREATE INDEX idx_lat_lng ON product_locations (latitude, longitude)');

Старые миграции мы просто удалили (архивировали). В таблице migrations осталась одна запись – о новой миграции.

Сухой остаток: не доверяйте создание таблиц pgloader'у – это ваша задача. Миграции должны быть совместимы с целевой СУБД, и лучше одна правильная, чем 26 устаревших.

3. Перенос данных: эпопея с pgloader

3.1. Почему pgloader?

Пробовали mysqldump + ручная конвертация – отстой. pgloader умеет переливать данные напрямую из MySQL в PostgreSQL с преобразованием типов. Звучит как магия, но, как выяснилось, с подводными камнями.

3.2. Версия pgloader и MySQL 8

Установили из репозитория Debian (версия 3.6.7). Запускаем – ошибка:

text
mysql: Failed to connect to mysql ... Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION

Старая версия не дружит с новым методом аутентификации MySQL 8 (caching_sha2_password). Тут два пути:

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

• найти свежую версию pgloader, которая поддерживает новый метод (информация о совместимости есть в открытых источниках, например на GitHub).

Мы пошли первым путём – создали отдельного пользователя с mysql_native_password. Но если у вас нет доступа к настройкам MySQL, придётся искать обновлённый pgloader.

Зарубка на сервере: pgloader должен уметь договариваться с MySQL 8. Если версия старая – либо меняйте метод аутентификации, либо ищите свежий билд.

3.3. Попытка скормить дамп – провал

Решили сэкономить время и не поднимать временный MySQL, а просто дать pgloader'у SQL-дамп. Фиг там. pgloader не умеет читать дампы, он работает только с живой базой по протоколу. Потратили несколько часов, пока не поняли эту очевидную (теперь) вещь.

Сухой остаток: pgloader нужна живая база, а не дамп. Не тратьте время.

3.4. Танцы со схемой

Подключились к живой MySQL, запускаем – новая ошибка:

text
pgloader failed to find schema "app_schema" in target catalog.

pgloader ожидает, что в PostgreSQL есть схема с именем исходной базы MySQL. У нас таблицы лежали в схеме app_schema (мы назвали её так, чтобы не светить реальные имена). Но pgloader проверяет наличие схемы до выполнения BEFORE LOAD. Поэтому SET search_path в BEFORE LOAD не помогало.

Перепробовали кучу вариантов:

• создавали схему app_schema и загружали туда, потом переносили данные в public – работало, но лишние телодвижения.

• пытались указать схему в URI ?search_path=app_schema – pgloader ругался на синтаксис.

В итоге родилось элегантное решение: оставить схему app_schema и перед загрузкой установить search_path для сессии через BEFORE LOAD DO. Это заставило pgloader искать таблицы именно там.

Фрагмент финального конфига:

lisp
LOAD DATABASE
     FROM mysql://pgloader:****@host:port/db_name
     INTO postgresql://laravel_user:****@postgres_db:5432/db_name

WITH create no tables,
     reset sequences,
     batch rows = 10000

BEFORE LOAD DO
     $$ SET search_path TO app_schema; $$;

EXCLUDING TABLE NAMES MATCHING 'pma__.*', 'migrations'
CAST ...;

3.5. Проблема с point-to-geometry

Ещё одна засада – преобразование типа POINT из MySQL в geometry PostgreSQL. Пробовали using point-to-geometry – получили ошибку, что такой функции нет. Оказывается, в нашей сборке pgloader этот трансформер отсутствует.

Решение: убрать using и оставить просто type point to geometry. pgloader сам преобразует бинарный WKB в целевой тип, если таблица уже создана.

Фрагмент итогового CAST:

lisp
CAST
    type tinyint to boolean drop typemod using tinyint-to-boolean,
    type datetime to timestamptz using zero-dates-to-null,
    ...
    type point to geometry   -- вот так, без using

После этого pgloader отработал без ошибок. Данные на месте.

4. Адаптация кода Laravel под PostgreSQL

4.1. Гео‑поиск: MySQL → PostGIS

В MySQL для поиска рядом мы использовали что-то вроде ST_Distance_Sphere. В PostGIS всё делается через ST_DWithin и ST_Distance. Пример скоупа:

php
public function scopeWithinRadius($query, $lat, $lng, $radius)
{
    return $query->join('product_locations', ...)
        ->whereRaw("ST_DWithin(location, ST_SetSRID(ST_MakePoint(?, ?), 4326), ?)", [$lng, $lat, $radius])
        ->select('products.*')
        ->selectRaw("ST_Distance(location, ST_SetSRID(ST_MakePoint(?, ?), 4326)) as distance", [$lng, $lat])
        ->orderBy('distance');
}

Обратите внимание: сначала долгота, потом широта. В MySQL порядок мог быть другим, и это классический «грабли» при переезде.

4.2. Двойные кавычки в ST_GeomFromText

В нескольких местах мы писали:

php
->whereRaw('NOT ST_Equals(location, ST_GeomFromText("POINT(0 0)", 4326))')

В PostgreSQL строки должны быть в одинарных кавычках. Двойные кавычки – для идентификаторов. Правильно:

php
->whereRaw('NOT ST_Equals(location, ST_GeomFromText('POINT(0 0)', 4326))')

4.3. HAVING с псевдонимом

В MySQL можно написать HAVING products_count > 0. В PostgreSQL – нельзя, потому что HAVING выполняется до SELECT. Пришлось переписывать:

php
// Было:
->having('products_count', '>', 0)

// Стало:
->havingRaw('COUNT(products.id) > 0')

4.4. RAND() → RANDOM()

Вместо orderByRaw('RAND()') используем inRandomOrder(). Laravel сам подставит RANDOM() для PostgreSQL.

4.5. Мутатор координат

При сохранении координат через мутатор мы формировали точку как POINT(lat lng). Исправили на POINT(lng lat).

5. Доведение админки Filament до ума

После переноса админка перестала открываться – те же проблемы со схемой и HAVING.

Решение:

• В .env добавили DB_SCHEMA=app_schema и убедились, что в config/database.php для pgsql есть 'schema' => env('DB_SCHEMA', 'public').

• В виджете популярных категорий заменили having на havingRaw, как в п. 4.3.

6. Что в итоге?

• База перенесена, все данные на месте.

• Гео‑запросы стали быстрее (субъективно).

• Админка работает, виджеты не падают.

• История миграций чистая – одна миграция вместо 26.

7. Самые яркие грабли (список для тех, кто захочет повторить)

• pgloader не ест дампы – нужна живая база MySQL.

• Аутентификация MySQL 8 – либо переключайте пользователя на старый метод, либо ищите свежий pgloader, который дружит с caching_sha2_password.

• Схема – если таблицы не в схеме по умолчанию, укажите search_path в BEFORE LOAD DO.

• point-to-geometry – не надо using, просто type point to geometry.

• Порядок координат – в PostGIS сначала долгота, потом широта.

• Кавычки в SQL – в ST_GeomFromText используйте одинарные.

• HAVING с псевдонимом – в PostgreSQL нельзя, повторяйте выражение.

• RAND() vs RANDOM() – используйте inRandomOrder().

• Filament и схема – не забудьте DB_SCHEMA в .env.

8. Приглашение к обсуждению

Наш опыт – лишь один из возможных путей. А как бы поступили вы?

• Структура с нуля или через pgloader? Мы предпочли создать одну финальную миграцию. А вы?

• pgloader или другой инструмент? Пробовали ли вы другие решения (mydumper/myloader, коммерческие инструменты)? Что показало себя лучше?

• geometry или geography? Мы использовали geometry с SRID 4326. Кто-то предпочитает geography для автоматического учёта кривизны Земли?

• Версия PostgreSQL – мы взяли 15-ю. На момент чтения вышли 16 и 17. Есть ли смысл сразу ставить свежую версию?

• Типы данных: мы заменили ENUM на string, а JSON на jsonb. А вы оставляете ENUM в PostgreSQL или тоже предпочитаете строки?

• Танцы с pgloader: у кого-то были похожие проблемы со схемой и search_path? Как вы их решали?

Оставляйте комментарии – обсудим! Вместе мы сделаем переезд на PostgreSQL проще для всех.

🙌 Хотите обсудить проект, нужна помощь или есть предложения? Пишите.