30 тыс. строк кода или как мы переходили с Oracle на PostgreSQL с помощью утилиты Ora2Pg 23.0

Привет, меня зовут Андрей, я работаю ведущим разработчиком в компании СИГМА и отвечаю за решения по автоматизации расчетов технических условий. Сегодня хочу поделиться своим опытом переноса в среду PostgreSQL данных из СУБД Oracle и процедур, разработанных на PL/SQL.

Перед моей командой была поставлена срочная и нетривиальная задача перенести разрозненный функционал, ранее разработанный на базе СУБД Oracle, в единую систему управления распределительными электрическим сетями (по-простому СИГМА СУС), которая работает на основе PostgreSQL и сочетает в себе подсистемы DMS, SCADA, GIS, NIS, OMS и другие.

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

Проанализировав программные обеспечения для конвертации из открытого доступа, мы вышли на заметно выделяющийся среди аналогов (к тому же и бесплатный) продукт — Ora2Pg (https://ora2pg.darold.net). Забегая вперед, скажу, что наша команда ни разу не пожалела, что выбрала данный подход и продукт. Процедура переноса достаточно прозрачная даже для тех, кто никогда не работал с PostgreSQL. Так что если и у вас запланирована миграция данных, то эта статья поможет вам подойти к процессу более системно и избежать некоторых ошибок.

Параметры проекта:

• 50 таблиц Oracle,

• 30 тыс. строк кода на PL/SQL.

Я сделал краткое описание этапов нашего проекта и постарался выделить места, которые вызвали сложности, но были решены по ходу работы:

• Конвертация комментариев на русском языке из процедур и функций.

• Невозможность автоматического переноса 2х процедур, которые используют специфические особенности Oracle.

• Изменение семантики в коде Oracle для обеспечения приемлемой производительности итогового кода PostgreSQL.

Установка и настройка необходимого ПО

Установка базового ПО

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

Ниже краткая пошаговая инструкция по установке ПО:

1. Установить Oracle database: WINDOWS.X64_193000_db_home.zip

2. Установить Perl:

   • Мы использовали сборку Strawberry Perl 5.32.1.1-64bit.

   • Установить DBD::Oracle модуль Perl (Oracle должен быть уже установлен, переменная ORACLE_HOME описана в окружении. Команда для установки: perl -MCPAN -e ‘install DBD::Oracle’).

   • Установить DBD::PG модуль Perl (команда для установки: perl -MCPAN -e ‘install DBD::PG’).

3. Установить PostgreSQL. Мы использовали postgresql-13.6-2-windows-x64.exe (дистрибутив)

4. Скачать Ora2Pg. Мы использовали версию 23.0.

   • Дистрибутив.

   • Разархивировать в любую папку (мы выбрали C:\Ora2Pg).

5. Выполнить команды Perl:

   • perl C:\Ora2Pg\Makefile.PL

   • gmake && gmake install

Настройка конфигурации Ora2Pg

После установки настраиваем конфигурацию Ora2Pg. Файл конфигурации Ora2Pg расположен в «C:\Ora2Pg\ora2pg.conf».

Основные настройки выглядят вот так:

1. Настройка Oracle_HOME:

# Set the Oracle home directory ORACLE_HOME C:\Distrib\Oracle\18\WINDOWS.X64_180000_db_home

2. Путь к базе Oracle:

# Set Oracle database connection (datasource, user, password) ORACLE_DSN   dbi:Oracle:host=localhost;sid=testdb;port=1521 ORACLE_USER   test ORACLE_PWD   test

3. Настройки кодировки.

• NLS_LANG должна соответствовать настройкам в БД Oracle.

• NLS_NCHAR – по умолчанию.

• CLIENT_ENCODING – кодировка клиента PostgreSQL.

#------------------------------------------------------------------------------ # ENCODING SECTION (Define client encoding at Oracle and PostgreSQL side) #------------------------------------------------------------------------------  # Enforce default language setting following the Oracle database encoding. This # may be used with multibyte characters like UTF8. Here are the default values # used by Ora2Pg, you may not change them unless you have problem with this # encoding. This will set $ENV{NLS_LANG} to the given value. NLS_LANG          AMERICAN_AMERICA.CL8MSWIN1251 #NLS_LANG        AMERICAN_AMERICA.AL32UTF8 # This will set $ENV{NLS_NCHAR} to the given value. NLS_NCHAR       AL16UTF16#NLS_NCHAR    AL32UTF8  # By default PostgreSQL client encoding is automatically set to UTF8 to avoid # encoding issue. If you have changed the value of NLS_LANG you might have to # change  the encoding of the PostgreSQL client. #CLIENT_ENCODING     UTF8 CLIENT_ENCODING       WIN1251

4. Настройки подключения в БД PostgreSQL

# Define the following directive to send export directly to a PostgreSQL # database, this will disable file output. Note that these directives are only # used for data export, other export need to be imported manually through the # use og psql or any other PostgreSQL client. PG_DSN    dbi:Pg:dbname=testdb;host=localhost;port=5432 PG_USER                           test PG_PWD                            test

Нам также потребовались дополнительные настройки:

1. Настройки экспорта схемы (пользователя) из Oracle (TEST – название схемы)

# Export Oracle schema to PostgreSQL schema EXPORT_SCHEMA          1 # Oracle schema/owner to use SCHEMA                 TEST

2. Выгрузка конкретной таблицы схемы:

# Set which object to export from. By default Ora2Pg export all objects. # Value must be a list of object name or regex separated by space. Note # that regex will not works with 8i database, use % placeholder instead # Ora2Pg will use the LIKE operator. There is also some extended use of # this directive, see chapter "Limiting object to export" in documentation. ALLOW                 TESTTABLE

 3. Удаление данных таблиц перед копированием данных

# Add a TRUNCATE TABLE instruction before loading data on COPY and INSERT # export. When activated, the instruction will be added only if there's no # global DELETE clause or one specific to the current table (see bellow).  TRUNCATE_TABLE         1

После установки и настройки файла конфигурации Ora2Pg можно приступать к выгрузке данных.

---

Перенос данных

Выгрузке подлежат следующие элементы БД:

# Type of export. Values can be the following keyword: # TABLE       Export tables, constraints, indexes, ... # PACKAGE     Export packages # INSERT      Export data from table as INSERT statement # COPY        Export data from table as COPY statement # VIEW        Export views # GRANT       Export grants # SEQUENCE    Export sequences # TRIGGER     Export triggers # FUNCTION    Export functions # PROCEDURE   Export procedures # TABLESPACE  Export tablespace (PostgreSQL >= 8 only) # TYPE        Export user defined Oracle types # PARTITION   Export range or list partition (PostgreSQL >= v8.4) # FDW         Export table as foreign data wrapper tables # MVIEW       Export materialized view as snapshot refresh view # QUERY       Convert Oracle SQL queries from a file. # KETTLE      Generate XML ktr template files to be used by Kettle. # DBLINK      Generate oracle foreign data wrapper server to use as dblink. # SYNONYM     Export Oracle's synonyms as views on other schema's objects. # DIRECTORY   Export Oracle's directories as external_file extension objects. # LOAD        Dispatch a list of queries over multiple PostgreSQL connections. # TEST        perform a diff between Oracle and PostgreSQL database. # TEST_VIEW   perform a count on both side of rows returned by views

 В конфигурационном файле можно указать, какой элемент выгружается:

TYPE                  PACKAGE

Но тип, указанный в командной строке, имеет приоритетное значение. Например, команда выгрузки PACKAGE из схемы TEST выглядит следующим образом:

ora2pg -p -d -t PACKAGE -o PACKAGE.sql -b C:\Ora2Pg\TEST -c C:\Ora2Pg\ora2pg.conf -l C:\Ora2Pg\TEST\PACKAGE.log

 В данном примере:

• -р – включает конвертирование PLSQL в PLPGSQL;

• -d – включает debug;

• -t – указывает, какой тип данных выгружается;

• -c – путь к конфигурационному файлу;

• -l – путь к файлу с логом.

Как я уже писал выше, процессе конвертации данных мы столкнулись с трудностями переноса кириллицы. В версии 22.1 Ora2Pg в таблицах, где присутствует кириллица, русские буквы смещаются на 1 байт, из-за этого кодировка меняется. Кодировка данных в таблицах с кириллицей исправилась после введения в конфигурационный файл настройки:

Use open ‘:encoding(iso-8859-7)’

При копировании данных процедура заканчивалась ошибкой при настройках NLS_LANG по умолчанию:

Если выставить NLS_LANG = AMERICAN_AMERICA.CL8MSWIN1251, то выходила следующая ошибка:

Успешное копирование данных происходило при следующих настройках:

NLS_LANG           AMERICAN_AMERICA.CL8MSWIN1251 CLIENT_ENCODING    WIN1251

В версии 23.0 была исправлена проблема с кодировкой.

При помощи новой директивы FORCE_PLSQL_ENCODING мы решили проблему с конвертацией пакетов с русскими символами. После этого они конвертировались корректно:

В версии 23.0, чтобы копировать данные таблиц, надо в ora2pg.conf раскомментировать строчки NLS_LANG и CLIENT_ENCODING (настройки должны соответствовать настройкам в БД Oracle и PostgreSQL соответственно).

При конвертации кода пакетов эти параметры нужно закомментировать символом «#».

Перенос кода

Весь процесс переноса исходного кода мы поделили на две последовательные задачи:

• перенос объектов базы данных, за исключением функций и процедур;

• перенос функций и процедур с последующим функциональным тестированием и тестированием производительности.

Первую задачу выполнили за 2 недели.

Для обеспечения валидности структуры конечной базы данных нужно найти и заменить все зарезервированные в PostgreSQL слова (перечень представлен по ссылке), а также проделать работу по исправлению индексов к таблицам — были переименованы индексы по одноименным таблицам.

Для достижения приемлемого уровня конвертации мы сделали соответствующие правки в части объектов и исходного кода на PL/SQL на стороне базы данных Oracle. Убрали сбор статистики отдельных объектов (там, где он был), а также все что связано с пакетами (сервисами) DBMS.

Переосмыслили COMMIT и ROLLBACK: управление транзакциями возможно только в вызовах CALL или DO в коде верхнего уровня или во вложенных CALL или DO без других промежуточных команд.

Убрали псевдонимы в UPDATE.

Убрали переменные из непосредственных запросов к таблицам.

Явно привели типы данных после манипуляций с полями.

Переосмыслили работу с глобальными переменными пакетов.

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

---

Итоги

Результаты проекта:

• Миграция успешно прошла за 3 месяца.

• 99% работ выполняли 2 специалиста, не применявших утилиты Ora2Pg ранее.

• 2 процедуры переписаны на PostgreSQL вручную. Утилита Ora2Pg для них оказалось неприменима.

• Внесли около 10 синтаксических правок в структуру кода Oracle, чтобы итоговый код соответствовал требованиям утилиты Ora2Pg.

• 5-10% строк кода в Oracle переписали вручную, чтобы изменить логику выполнения и добиться приемлемой скорости выполнения в PostgreSQL. Без выполнения оптимизации наш код в PostgreSQL, созданный с помощью Ora2Pg, выполнялся в десятки раз медленнее, чем в Oracle.

• На момент завершения проекта среднее время выполнения процедур в PostgreSQL по сравнению с Oracle увеличилось в 1.5 раза.

Выводы:

• При наличии квалифицированного эксперта, занимающегося проектом 1-2 часа в неделю, миграция может быть успешно выполнена «собственными» силами, руками специалистов, ранее не имеющих опыта применения утилиты Ora2Pg.

• В нашем случае применение утилиты Ora2Pg оказалось полностью оправданным. «Ручное» переписывание кода заняло бы значительно больше времени и ресурсов.

При выполнении проекта мы опирались на рекомендации следующих статей:

• 2020. Миграция из Oracle в Postgre

• 2017. Полезные скрипты при миграции из Oracle в PostgreSQL

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