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

Что же умеет pgloader и в чем состоит его польза? Перечислим несколько основных достоинств:

• Мультиплатформенный импорт: утилита поддерживает миграцию «на лету» из MySQL, MS SQL, SQLite, а также загрузку из файлов формата CSV (и многих других)
• Автоматическое преобразование типов: утилита сама сопоставляет типы данных (например, datetime из MySQL преобразует в timestamp для Postgres)
• Умная обработка ошибок: вместо падения при первой же битой строке, pgloader отправляет некорректные данные в отдельный reject-лог, продолжая загрузку остальных записей
• Высокая скорость: использует встроенный протокол PostgreSQL COPY для потоковой передачи больших объемов данных без накладных расходов на парсинг каждого SQL-запроса
• Возможность настраивать правила трансформации (например, замену пустых строк на NULL или изменение кодировки).

Кроме всего перечисленного, как и у любого инструмента, у pgloader есть не только плюсы, но и свои недостатки. Так, при обработке огромных баз данных или сложных связей он может быть крайне требователен к оперативной памяти, а стэк-трейс на Common Lisp, на котором написана утилита, бывает трудно прочитать разработчику без специфического опыта. Кроме того, при всех возможностях кастомизации работа с DDL ограничена, поэтому если нужна тонкая настройка внешних ключей или специфических индексов, схему лучше подготовить в Postgres заранее, а pgloader использовать только для данных (data only).

Сама же команда pgloader запускается с помощью файла конфигурации (обычно с расширением .load). Перед примером классического файла для миграции перечислим некоторые важные флаги (добавляются при вызове команды) и параметры конфигурации в самом файле:

• --version: показать установленную версию
• -v, --verbose: выводить подробный лог работы (полезно для отслеживания прогресса)
• -d, --debug: включить режим отладки
• workers = X: количество одновременных потоков для чтения данных из источника
• concurrency = X:  количество потоков для одновременной записи в PostgreSQL
• include drop:  удалять существующие таблицы в Postgres перед началом импорта
• data only:  импорта только содержимого (схема должна быть уже создана)
• schema only: перенос только структуры таблиц без самих данных

Запуск скрипта осуществляется через простой вызов pgloader migrate.load​​​​​​​. Приведем пример файла migrate.load с комментариями:

/* 
  Указываем тип миграции. В данном случае мы переносим всю базу данных (DATABASE),
  включая схемы, таблицы, индексы и данные.
*/
LOAD DATABASE
     -- Источник: строка подключения к исходной базе данных MySQL
     FROM mysql://root:password@localhost/my_old_db
     
     -- Цель: строка подключения к целевой базе данных PostgreSQL
     INTO postgresql://postgres:password@localhost/my_new_db

 /* 
   Секция WITH задает глобальные параметры поведения pgloader 
 */
 WITH include drop,      -- Удалять таблицы в Postgres, если они там уже существуют
      create tables,     -- Автоматически создавать структуры таблиц в Postgres
      create indexes,    -- Автоматически переносить и создавать индексы
      reset sequences,   -- Сбросить и выставить автоинкременты (последовательности) в актуальные значения
      workers = 4,       -- Использовать 4 параллельных потока для чтения данных из MySQL
      concurrency = 2    -- Использовать 2 параллельных потока для записи данных в PostgreSQL

  /* 
    Секция SET позволяет задать специфические настройки для сессии СУБД.
    В данном случае увеличиваем таймауты MySQL, чтобы соединение не оборвалось на больших таблицах.
  */
  SET MySQL parameters
      net_read_timeout  = '120',
      net_write_timeout = '120'

 /* 
   Секция CAST - это правила трансформации типов данных (из MySQL в Postgres)
 */
 CAST type datetime to timestamp drop default drop not null,
      -- Превращаем datetime в timestamp, сбрасывая дефолтные значения и ограничение NOT NULL
      
      type tinyint to boolean using tinyint-to-boolean
      -- Конвертируем тип tinyint в логический тип (boolean) с помощью встроенной функции

 /* 
   Секция BEFORE LOAD DO позволяет выполнить любые SQL-команды 
   в целевой базе PostgreSQL строго ДО начала миграции.
 */
 BEFORE LOAD DO
   $$ drop schema if exists mysql cascade; $$; 
   -- Удаляем старую схему mysql (если она осталась от прошлых тестов), чтобы избежать конфликтов

В результате этих манипуляций после запуска pgloader командой выше мы получим вывод в стиле:

                                 table name       read   imported     errors      total time
-------------------------------------------  ---------  ---------  ---------  --------------
                            before load do...        1          1          0          0.015s
                             fetch meta data        42         42          0          0.218s
-------------------------------------------  ---------  ---------  ---------  --------------
                             mysql.users          5000       5000          0          0.452s
                             mysql.orders        12500      12498          2          1.120s
                        index build & phrs          --         --          0          0.340s
-------------------------------------------  ---------  ---------  ---------  --------------
                    reset sequences (auto)           2          2          0          0.042s
-------------------------------------------  ---------  ---------  ---------  --------------
                          Total import time      17542      17540          2          2.187s

По сводной таблице сразу видно, как прошла миграция. Колонка table name показывает этапы работы и имена перенесенных таблиц (с указанием схемы), read - сколько строк pgloader смог прочитать из MySQL, imported - сколько строк успешно записалось в PostgreSQL, errors - количество «битых» строк, которые Postgres отказался принимать (например, из-за нарушения уникальности или невалидных данных), total time - время, затраченное на каждую таблицу и операцию. Если количество ошибок больше нуля, то стоит изучить файлы *.reject и *.log, которые содержат сами строки, которые не удалось вставить, и описание конкретной ошибки соответственно. Найденные ошибки можно исправить самостоятельно и догрузить только пропущенные данные.

После того, как утилита отработает, в MySQL (источник) ничего не изменится, т.к. pgloader использует режим Read-Only для источника. Никакие данные не удалятся и не модифицируются. В PostgreSQL (Цель) сначала выполнится блок BEFORE LOAD DO: если в Postgres уже была схема с именем mysql, она полностью удалится со всеми таблицами (CASCADE). Затем последовательно создадутся - новые таблицы со структурой, аналогичной MySQL (но с адаптированными типами данных: например, tinyint превратится в boolean), все строки из MySQL, индексы и внешние ключи. Значения автоинкремента (sequences) также обновятся до актуального максимума, чтобы новые записи в Postgres начинались с правильного ID. Подобным образом, мы получим полностью готовую смигрированную новую базу с преобразованными старыми данными.