Опубликовано: воскресенье, 14 апреля 2024 г. в 08:40

Миграция с MySQL на Postgres с помощью конструктора запросов Laravel

Источник: «Migrating from MySQL to Postgres using Laravel's query builder»

Мы недавно перевели инфраструктуру баз данных с MySQL на Postgres. Это обзор того, как мы использовали конструктор запросов Laravel для миграции данных.

Миграция с MySQL на Postgres — дело непростое; для этого существует множество пакетов и скриптов, таких как Pgloader и Nmig.

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

Зачем

Прежде всего, небольшое примечание о том, почему мы перешли с MySQL на Postgres, поскольку это, вероятно, у всех на уме, кто читает эту статью.

Основная причина заключается в том, что были проблемы с полнотекстовым поиском в MySQL для нашего случая использования. Наши тесты и последующий переход на Postgres сделали полнотекстовый поиск более надёжным и быстрым.

Мы могли бы использовать другой сервис или технологию для поиска, но в духе скучных технологий Postgres прекрасно справляется со своей задачей.

Если смотреть в будущее, то Postgres также предлагает множество интересных возможностей благодаря своей расширяемости, например, такие расширения, как Timescale.

Подготовка конфигурации базы данных

Исходная база данных — MySQL; поскольку она используется по умолчанию, можно оставить конфигурацию базы данных как есть для соединения с MySQL. Однако мы предпочли создать новое соединение с именем source.

'source' => [
    'driver' => 'mysql',
    'url' => env('SOURCE_DATABASE_URL'),
    'host' => env('SOURCE_MYSQL_DB_HOST', '127.0.0.1'),
    'port' => env('SOURCE_MYSQL_DB_PORT', '3306'),
    'database' => env('SOURCE_MYSQL_DB_DATABASE', 'forge'),
    'username' => env('SOURCE_MYSQL_DB_USERNAME', 'forge'),
    'password' => env('SOURCE_MYSQL_DB_PASSWORD', ''),
    ...
],

Убедитесь, что настроены необходимые ключи окружения, начинающиеся с SOURCE_

Следующее, что нужно сделать, это настроить destination соединение в файле config/database.php для подключения к целевой базе данных с помощью драйвера pgsql:

'destination' => [
    'driver' => 'pgsql',
    'url' => env('DATABASE_URL'),
    'host' => env('DESTINATION_DB_HOST', '127.0.0.1'),
    'port' => env('DESTINATION_DB_PORT', '5432'),
    'database' => env('DESTINATION_DB_DATABASE', 'forge'),
    'username' => env('DESTINATION_DB_USERNAME', 'forge'),
    'password' => env('DESTINATION_DB_PASSWORD', ''),
    ...
],

Создание таблиц в Postgres

Laravel делает это чрезвычайно простым, поскольку вы можете запустить миграцию в целевой базе данных и получить все таблицы, созданные с использованием правильных типов полей.

php artisan migrate --database=destination

Создание команды заполнения данных

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

Конструктор запросов Laravel позволяет извлекать данные из исходной базы данных MySQL и вставлять их в целевую базу данных Postgres, поскольку конструктор запросов будет менять язык схемы в зависимости от драйвера базы данных.

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

Команда принимает два параметра: table, которая должна мигрировать, и параметр --from, позволяющий пропустить до определённого id. Это делает команду легко перезапускаемой без необходимости перебирать тысячи строк, которые уже были обработаны.

class MigrateDatabaseCommand extends Command
{
    protected $signature = 'app:migrate-database {table} {--from=}';

    public function handle()
    {
        $table = $this->argument('table');

        $this->getOutput()->info("Importing table {$table}");

        $total = DB::connection('source')
            ->table($table)
            ->when($this->option('from'), fn ($query) => $query->where('id', '>', $this->option('from')))
            ->count();

        $this->getOutput()->progressStart($total);

        DB::connection('source')
            ->table($table)
            ->when($this->option('from'), fn ($query) => $query->where('id', '>', $this->option('from')))
            ->select('id')
            ->chunkById(1_000, function ($sourceRows) use ($table) {
                $sourceIds = Arr::pluck($sourceRows, 'id');
                $destinationRows = DB::connection('destination')->table($table)->whereIn('id', $sourceIds)->select('id')->get()->toArray();

                $missingIds = array_diff($sourceIds, Arr::pluck($destinationRows, 'id'));

                if (count($missingIds)) {
                    $missing = DB::connection('source')->table($table)->whereIn('id', $missingIds)->get();

                    DB::connection('destination')->table($table)->insert($missing);
                }

                $this->getOutput()->progressAdvance($this->option('chunk'));
            });

        $this->getOutput()->progressFinish();
    }
}

Эта команда перебирает все ID в исходной базе данных и проверяет, какие из них уже присутствуют в целевой базе данных, если есть недостающие ID, то строки будут вставлены в целевую базу данных.

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

Парочка "подводных камней"

Строки, которые обновляются или удаляются между синхронизациями

Мы столкнулись с уникальной ситуацией, когда большинство таблиц можно было удалить и импортировать в целевую базу данных за считанные секунды. В данном случае эти таблицы были синхронизированы во время миграции.

Другие таблицы с миллионами строк — это строки внутри таблицы типа "только для добавления", которая не обновляется и очищается по истечении определённого периода времени. В этом примере мы не проверяем строки, нуждающиеся в обновлении или существующие в целевой таблице, но больше не существующие в исходной, например, когда пользователь удаляет некоторые строки.

Если ваш проект нуждается в подобной функциональности, то обязательно встройте её в свою команду.

Длина поля

Убедитесь, что длина полей в Postgres определена достаточно большой для существующих (и будущих) данных. MySQL не жалуется и усекает ваши данные. Однако Postgres более строг в этом отношении и выбрасывает исключение при вставке слишком длинных для поля данных.

Обновление запросов

Если вы не используете конструктор запросов Laravel для каких-либо запросов, это означает, что вы используете какую-то функциональность, специфичную для драйвера базы данных. Обязательно обновите эти запросы!