Home » Как мигрировать базу данных MySQL в Postgres с помощью pgloader

Как мигрировать базу данных MySQL в Postgres с помощью pgloader

Если работаете с legacy-системами, то рано или поздно придётся столкнуться с миграцией данных. Особенно болезненный вопрос — переход с MySQL на PostgreSQL. Причины разные: от требований новых проектов до банальной необходимости использовать более продвинутые возможности Postgres. В любом случае, pgloader — это тот инструмент, который может серьёзно упростить жизнь и сэкономить кучу времени. Сегодня разберём, как правильно мигрировать базу данных MySQL в PostgreSQL с помощью этой утилиты, избежав типичных граблей и получив максимум от процесса.

Что такое pgloader и как он работает

pgloader — это специализированная утилита, написанная на Common Lisp, которая предназначена для миграции данных из различных СУБД в PostgreSQL. Поддерживает MySQL, SQLite, MS SQL Server, CSV и другие форматы. Основная фишка — не просто копирование данных, а их трансформация с учётом особенностей PostgreSQL.

Принцип работы довольно простой:

Читает схему исходной базы данных
Автоматически создаёт соответствующую схему в PostgreSQL
Переносит данные с преобразованием типов
Создаёт индексы и ограничения
Генерирует отчёт о миграции

Особенность pgloader в том, что он понимает специфику разных СУБД и может корректно обработать MySQL-специфичные конструкции, такие как ENUM, SET, TIMESTAMP с нулевыми значениями и прочие радости.

Установка и первоначальная настройка

Для начала работы понадобится сервер с установленным PostgreSQL. Если ещё не определились с хостингом, можно взять VPS или выделенный сервер под свои нужды.

Установка pgloader на Ubuntu/Debian:

apt update
apt install pgloader postgresql-client-common

Для CentOS/RHEL:

yum install epel-release
yum install pgloader postgresql

Если в репозиториях версия старая, можно собрать из исходников:

git clone https://github.com/dimitri/pgloader.git
cd pgloader
make pgloader
cp build/bin/pgloader /usr/local/bin/

Подготовка к миграции

Перед началом миграции нужно убедиться, что всё готово:

MySQL база доступна и работает
PostgreSQL установлен и настроен
Есть права на создание базы данных в PostgreSQL
Достаточно места на диске (PostgreSQL может занимать больше места)

Создаём целевую базу данных в PostgreSQL:

psql -U postgres -c "CREATE DATABASE target_db;"
psql -U postgres -c "CREATE USER migration_user WITH PASSWORD 'your_password';"
psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE target_db TO migration_user;"

Базовая миграция: простой случай

Самый простой способ — использовать команду напрямую:

pgloader mysql://user:password@localhost/source_db postgresql://user:password@localhost/target_db

Но это работает только для идеальных случаев. В реальности понадобится больше контроля над процессом.

Создание конфигурационного файла

Для серьёзной миграции лучше создать конфигурационный файл. Пример `migration.conf`:

LOAD DATABASE
    FROM mysql://mysql_user:mysql_password@mysql_host/mysql_db
    INTO postgresql://pg_user:pg_password@pg_host/pg_db

WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1,
     multiple readers per thread, rows per range = 50000

SET MySQL PARAMETERS
    net_read_timeout  = '120',
    net_write_timeout = '120'

SET PostgreSQL PARAMETERS
    maintenance_work_mem to '1GB',
    checkpoint_segments to '64'

CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
     type date drop not null drop default using zero-dates-to-null

BEFORE LOAD DO
    $$ DROP SCHEMA IF EXISTS mysql CASCADE; $$,
    $$ CREATE SCHEMA mysql; $$

AFTER LOAD DO
    $$ UPDATE mysql.users SET created_at = NOW() WHERE created_at IS NULL; $$;

Запуск с конфигурационным файлом:

pgloader migration.conf

Обработка проблемных случаев

Реальная миграция редко проходит гладко. Рассмотрим типичные проблемы и их решения:

Проблема	Причина	Решение
Нулевые даты (0000-00-00)	MySQL позволяет, PostgreSQL — нет	Использовать zero-dates-to-null
ENUM типы	Разная реализация	Преобразовать в VARCHAR или создать TYPE
Кодировка	Различия в обработке UTF-8	Указать SET client_encoding = ‘UTF8’
Большие таблицы	Таймауты и память	Настроить workers и rows per range

Оптимизация процесса миграции

Для больших баз данных важно правильно настроить параметры:

WITH include drop, create tables, create indexes, reset sequences,
     workers = 16,
     concurrency = 2,
     multiple readers per thread,
     rows per range = 100000,
     batch rows = 10000

SET PostgreSQL PARAMETERS
    work_mem to '256MB',
    maintenance_work_mem to '2GB',
    checkpoint_segments to '128',
    wal_buffers to '64MB',
    shared_buffers to '4GB'

workers — количество параллельных потоков
rows per range — размер порции данных
batch rows — размер batch для INSERT
concurrency — количество одновременных соединений

Специальные случаи и трансформации

Иногда нужно преобразовать данные “на лету”. Например, изменить структуру таблицы:

LOAD DATABASE
    FROM mysql://user:pass@host/db
    INTO postgresql://user:pass@host/db

INCLUDING ONLY TABLE NAMES MATCHING 'users', 'orders', 'products'

EXCLUDING TABLE NAMES MATCHING 'temp_', 'cache_'

ALTER TABLE users 
    ADD COLUMN full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

CAST column users.status from "enum('active','inactive')" to text;

Мониторинг и отладка

pgloader предоставляет подробную статистику выполнения:

pgloader --verbose --debug migration.conf

Вывод покажет:

Количество обработанных строк
Скорость миграции
Ошибки и предупреждения
Время выполнения для каждой таблицы

Для логирования в файл:

pgloader --logfile migration.log migration.conf

Сравнение с альтернативными решениями

Инструмент	Плюсы	Минусы	Скорость
pgloader	Автоматическое преобразование типов, параллельность	Специфичен для PostgreSQL	Высокая
mysqldump + psql	Простота, доступность	Ручная обработка типов	Средняя
ETL-системы (Pentaho, Talend)	Гибкость, GUI	Сложность настройки	Средняя
Собственные скрипты	Полный контроль	Много времени на разработку	Зависит от реализации

Автоматизация и интеграция в CI/CD

pgloader отлично подходит для автоматизации. Пример скрипта для регулярной синхронизации:

#!/bin/bash

# Создаём временную базу для миграции
psql -U postgres -c "DROP DATABASE IF EXISTS temp_migration;"
psql -U postgres -c "CREATE DATABASE temp_migration;"

# Запускаем миграцию
pgloader --verbose migration.conf

# Если всё прошло успешно, переключаем базы
if [ $? -eq 0 ]; then
    psql -U postgres -c "DROP DATABASE IF EXISTS old_production;"
    psql -U postgres -c "ALTER DATABASE production RENAME TO old_production;"
    psql -U postgres -c "ALTER DATABASE temp_migration RENAME TO production;"
    echo "Migration completed successfully"
else
    echo "Migration failed"
    exit 1
fi

Интересные возможности и хитрости

Несколько нестандартных способов использования pgloader:

Миграция с фильтрацией: можно мигрировать только определённые записи с помощью WHERE
Преобразование на лету: изменение структуры данных во время миграции
Множественные источники: объединение данных из разных MySQL баз
Инкрементальная миграция: перенос только изменённых данных

Пример фильтрации данных:

LOAD DATABASE
    FROM mysql://user:pass@host/db
    INTO postgresql://user:pass@host/db

INCLUDING ONLY TABLE NAMES MATCHING 'users'
WHERE users.created_at > '2023-01-01'

Производительность и статистика

По опыту, pgloader показывает впечатляющие результаты:

Скорость миграции: 50,000-200,000 строк/сек (зависит от железа)
Потребление памяти: 100-500MB (настраивается)
Эффективность сжатия: PostgreSQL обычно занимает на 10-30% меньше места
Время простоя: минимальное при правильной настройке

Для базы в 100GB миграция обычно занимает 2-6 часов на современном железе.

Заключение и рекомендации

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

Когда использовать pgloader:

Одноразовая миграция с MySQL на PostgreSQL
Нужна высокая скорость и минимальное время простоя
Сложная схема базы данных с множественными типами
Автоматизация процесса миграции

Когда стоит рассмотреть альтернативы:

Нужна миграция между разными СУБД (не в PostgreSQL)
Требуется сложная трансформация данных
Необходим графический интерфейс

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

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

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