pgloader. Миграция данных в БД PostgreSQL

Версия: 3.6.9.

В исходном дистрибутиве установлено по умолчанию: да.

Связанные компоненты: freetds, sbcl.

Схема размещения: не используется.

Проект pgloader является программным обеспечением с открытым исходным кодом. C ходом разработки можно ознакомиться по ссылке https://github.com/dimitri/pgloader.

Утилита представляет собой инструмент для переноса/миграции данных из MS SQL Server, MySQL, SQLite в базу данных PostgreSQL. Инструмент использует PostgreSQL-команду COPY для копирования данных из исходной базы данных или файла. Пример процесса миграция с MS SQL на Pangolin с помощью pgloader представлен в одноименном подразделе документа «Руководство прикладного разработчика» раздела «Миграция на текущую версию».

Если в процессе эксплуатации автоматизированной системы принято решение о смене SQL-сервера, необходимо учитывать, что разные реализации SQL-серверов отличаются:

• диалектом;
• поддерживаемыми типами данных;
• процедурными языками;
• особенностями ввода в эксплуатацию.

Укрупненно процесс смены SQL-сервера (миграция) состоит из нескольких стадий, которые представлены в таблице:

Номер	Стадия	Ожидаемый результат
1.	Архитектурное планирование	Получены требования к сервису, DataFlow, метрикам SLA
2.	Планирование схемы данных	Получены DDL-данных, с учетом диалекта целевого SQL-сервера (чаще в виде скрипта liquibase/flyway)
3.	Определение схемы данных: - подготовка DDL-таблиц; - индексирование и связывание таблиц.	На целевом SQL-сервере будет подготовлена схема данных, а также добавлены необходимые УЗ
4.	Перенос данных	На целевой SQL-сервер будут перенесены данные с исходного SQL-сервера
5.	Перенос кода (опционально)	На целевом SQL-сервере будут созданы: - хранимые процедуры; - триггеры; - регламентные процедуры, необходимые для функционирования АС
6.	Опциональная валидация данных (контроль полноты перенесенных данных)	Получено подтверждение о полноте перенесенных данных

Примечание:

В этой статье подробно рассматриваются 3 и 4 шаги. Особый акцент уделяется переносу данных.

Перенос данных в неиндексированную структуру и последующая индексация занимает меньше времени, чем перенос данных в индексированную структуру. Поэтому для уменьшения затрат на миграцию следует разделить шаг «Подготовка схемы данных» на два этапа:

• подготовка DDL-таблиц;
• индексирование и связывание таблиц.

Подготовка

Подготовка схемы в целевой БД

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

Миграция исходного кода T-SQL

Автоматизированного сценария переноса исходного кода БД функциональностью утилиты не предусмотрено. Для этого существуют сторонние инструменты, но в данной статье они не рассматриваются.

Доступ к базам данных

В процессе работы pgloader создает объекты в целевой базе данных Pangolin и переносит в нее данные. Для работы инструмента необходимы права на создание и наполнение объектов. Рекомендуется выделить отдельную роль и наделить ее необходимыми правами, так как для базы данных источника необходим пользователь, который имеет доступ ко всем переносимым объектам.

Доработка

Выполнены следующие доработки:

• добавлен тип данных datetime2;
• реализовано выполнение скрытого запроса пароля для MS SQL Server;
• исправлена ошибка с типом данных msdatetimeoffset путем доработки соответствия типа datetimeoffset источника типу timestamptz приемника;
• исправлен дефект утилиты, при котором порядок и состав колонок таблиц приемника должны были точно соответствовать источнику;
• исправлен перенос uuid.

Ограничения

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

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

Установка

Исполняемый файл pgloader утилиты размещен в архиве party-distrib.zip дистрибутива продукта в каталоге migration_tools.

Для установки pgloader:

sudo yum install freetds sbcl

Скопируйте на хост дистрибутив, распакуйте и скопируйте исполняемый файл migration_tools/pgloader (выполнять в папке с распакованным дистрибутивом):

sudo cp ./migration_tools/pgloader /usr/local/bin/

Проверьте, что все установлено корректно и версия отображается:

pgloader --version

#Output
pgloader version "3.6.0"
compiled with SBCL 1.4.0-1.el7

Для настройки данного компонента необходимо воспользоваться инструкцией из официальной документации.

Настройка

Конфигурационный файл

Для запуска процедуры миграции необходимо подготовить конфигурационный файл <имя файла>.load.

Пример содержания конфигурационного файла приведен ниже.

В большинстве случаев это полный и готовый файл конфигурации для переноса данных из MS SQL Server.

load database
        from mssql://pgloader_tuz:password@tktest-example00000.vm.ru:1433/TEST                     -- для пользователя SQL Server
--      from mssql://domain\username:password@URI_MSSQL:PORT_MSSQL/DB_FOR_CONNECTION               -- для пользователя AD
        into postgresql://etl_tuz:password@tkte...st-example00001.vm.ru:5432/test                     -- для подключения по паролю
--      into postgresql://etl_tuz@tktest-example00001.vm.ru:5432/test?sslmode=require              -- для подключения по сертификату

-- перенос только объектов схемы dbo (важно, чтобы все участвующие схемы в Pangolin принадлежали тому пользователю, под которым идет миграция)
-- сюда же при необходимости можно добавить исключения (excluding) или including-правила.
including only table names like '%' in schema 'dbo'

-- задать опции
with create schemas, include drop, truncate, disable triggers, create tables, create indexes, drop indexes, reset sequences

-- настроить параметры работы по нагрузке при переносе данных
set work_mem to '16MB', maintenance_work_mem to '512 MB', role to 'as_admin'

-- задать правила преобразования типов
cast type bigint to bigint, type geometry to bytea, type geography to bytea, type smallmoney to money, type tinyint to smallint, type smallint to smallint, type date to date

-- если схема dbo существует, то удалить ее
before load do $$ drop schema if exists dbo cascade; $$;

Подключение по сертификату

1. В домашнем каталоге пользователя, под которым запускается pgloader, создайте скрытую папку ~/.postgresql.

2. Скопируйте в ~/.postgresql файлы client.crt и client.key под именами:

   • client.crt под именем postgresql.crt;
   • client.key под именем postgresql.key.

3. В строке подключения укажите параметр ?sslmode=require.

В случае необходимости использования файлов доверенных сертификатов (например, trusted.crt) pgloader не предусматривает для них соответствующих имен файлов в каталоге ~/.postgresql.

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

Подробное описание: https://github.com/dimitri/pgloader/issues/308?ysclid=lnuc7mbune557377359.

Использование модуля

Запуск утилиты

Пример конфигурационного файла migr1.load:

LOAD DATABASE
FROM mssql://SA@$IP-адрес:$Порт/TestDB
INTO postgresql://postgres@$IP-адрес:$Порт/first_db?sslmode=require
including only table names like 'test2' in schema 'dbo'
with create no schemas, create no indexes, no foreign keys, disable triggers, truncate, create no tables, quote identifiers, reset sequences, data only,  workers = 4;
-- cast type bigint to bigint, type float to float;

Запуск утилиты:

/usr/pangolin-5.5.0/migration_tools/pgloader/pgloader migr1.load

Пример вывода результата:

2022-11-10T08:55:17.039000+03:00 LOG pgloader version "3.6.9"
2022-11-10T08:55:17.042000+03:00 LOG Enter MSSQL password if necessary:
2022-11-10T08:55:26.888000+03:00 LOG Enter MSSQL password if necessary:
2022-11-10T08:55:34.006000+03:00 LOG Migrating from #<MSSQL-CONNECTION mssql://SA@$IP-адрес:$Порт/TestDB {1006874393}>
2022-11-10T08:55:34.007000+03:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@$IP-адрес:$Порт/first_db {1006875803}>
Max connections reached, increase value of TDS_MAX_CONN
Max connections reached, increase value of TDS_MAX_CONN
2022-11-10T08:55:34.276000+03:00 LOG report summary reset
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
        fetch meta data          0          1                     0.049s
               Truncate          0          1                     0.006s
-----------------------  ---------  ---------  ---------  --------------
          "dbo"."test2"          0         23     0.2 kB          0.036s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.037s
        Reset Sequences          0          0                     0.028s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          ✓         23     0.2 kB          0.065s

После окончания работы утилиты необходимо:

• проанализировать журнал на предмет ошибок в колонке errors;
• проверить корректность данных в целевой базе данных.

Возможные ошибки

1. Ошибка загрузки библиотек libsybdb.so, libsybdb.so.5:

   KABOOM!
   LOAD-FOREIGN-LIBRARY-ERROR: Unable to load any of the alternatives:
      ("libsybdb.so" "libsybdb.so.5")
   An unhandled error condition has been signalled:
      Unable to load any of the alternatives:
      ("libsybdb.so" "libsybdb.so.5")

   Решение:

   • установить недостающие пакеты: sudo yum install freetds sbcl;
   • проверить, что переменная LD_LIBRARY_PATH корректна, а ее значения доступны;
   • в случае с HA-кластерной конфигурацией, где есть оркестратор Pangolin Manager, поместить запуск переменной в service-файл процесса: /etc/systemd/system/pangolin-manager.service.

2. Ошибка FATAL при подключении по сертификату:

   In context DSN-OPTION-SSL:
   While parsing DSN-OPTION-SSL-REQUIRE. Expected: the string "require"

   Решение:

   Убрать из директивы set лишние настройки sslmode.

3. Ошибка Failed to connect to mssql:

   ERROR mssql: Failed to connect to mssql at "test0001.test.ru" (port 1102) as user "user": Connection to the database failed for an unknown reason.

   Решение:

   Поскольку утилита по умолчанию обращается на порт 1433 СУБД MSSQL, необходимо объявить переменную TDSPORT = <используемый_порт>, например:

   TDSPORT = 1102

Ссылки на документацию разработчика

Утилита pgloader: https://pgloader.readthedocs.io/en/latest/index.html.