Привет, меня зовут Андрей, я работаю ведущим разработчиком в компании СИГМА и отвечаю за решения по автоматизации расчетов технических условий. Сегодня хочу поделиться своим опытом переноса в среду 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, в ней уже не было таких проблем. Так что рекомендуем использовать самую свежую версию.
Ниже краткая пошаговая инструкция по установке ПО:
Установить Oracle database: WINDOWS.X64_193000_db_home.zip
Установить 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').
Установить PostgreSQL. Мы использовали postgresql-13.6-2-windows-x64.exe (дистрибутив)
Скачать Ora2Pg. Мы использовали версию 23.0.
Разархивировать в любую папку (мы выбрали C:\Ora2Pg).
Выполнить команды 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_home2. Путь к базе Oracle:
# Set Oracle database connection (datasource, user, password)
ORACLE_DSN dbi:Oracle:host=localhost;sid=testdb;port=1521
ORACLE_USER test
ORACLE_PWD test3. Настройки кодировки.
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 WIN12514. Настройки подключения в БД 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 TEST2. Выгрузка конкретной таблицы схемы:
# 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 TESTTABLE3. Удаление данных таблиц перед копированием данных
# 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 оказалось полностью оправданным. «Ручное» переписывание кода заняло бы значительно больше времени и ресурсов.
При выполнении проекта мы опирались на рекомендации следующих статей:
В итоге основную задачу мы выполнили — безболезненно перешли из одной среды в другую — и параллельно с этим выработали ряд подходов для работы с большими объемами данных, а также исправили конвертацию комментариев на русском языке из процедур и функций. Если вы тоже решите выполнить миграцию данных без ручного переписывания кода — утилита Ora2Pg 23.0 станет отличным помощником.