06.06.2022 18:15
SIGMA_team 2 2700 Источник

Привет, меня зовут Андрей Наумов, я работаю ведущим разработчиком в компании СИГМА и отвечаю за решения по автоматизации расчетов технических условий. Сегодня хочу поделиться своим опытом переноса в среду 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 оказалось полностью оправданным. «Ручное» переписывание кода заняло бы значительно больше времени и ресурсов.

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

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

Комментарии (2)


  1. edo1h
    06.06.2022 20:51
    #24414242
    +5

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

    хм, а зачем?


    проверил похожий код, работает 
    create table t_from (x1 int, x2 int);
create table t_to(x1 int, x2 int, x3 int);

CREATE FUNCTION f_test(x3 int) RETURNS void
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
  begin
    insert into t_to (x1, x2, x3) select x1, x2, x3 from t_from;
  end;
$_$;

insert into t_from (x1, x2) values (1000,1001);
select f_test(777);
select * from t_to;

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

    вот это если бы расписали, было бы очень круто (медленнее выполнение запросов или plpgsql, нашли ли какие-то узкие места)


    1. anaum0v
      08.06.2022 14:09
      #24420588

      хм, а зачем?

      Согласен, сейчас, по-новому взглянув на вопрос стало ясно что необходимость убирать переменные из непосредственных запросов к таблицам как таковая действительно отсутствует. Например, при наличии предложения GROUP BY в заблуждение вводит всплывающая подсказка "Column 'x3' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" при написании кода в IDE от JetBrains - в частности резюмируя можно сказать что варианты написания (1, 2 и 3) имеют место быть и полностью валидны, причем варианты 2 и 3 по умолчанию не выдают синтаксических ошибок.

      -- 2
create or replace function f_test(x3 int) returns void
    language plpgsql
    security definer
as
$_$
begin
    insert into t_to (x1, x2, x3) select x1, x2, x3 from t_from group by x1, x2, x3;
end;
$_$;


-- 3
create or replace function f_test(x3 int) returns void
    language plpgsql
    security definer
as
$_$
begin
    insert into t_to (x1, x2, x3)
    select a.*, x3
    from (select x1, x2
          from t_from
          group by x1, x2) a;
end;
$_$;

      вот это если бы расписали, было бы очень круто (медленнее выполнение запросов или plpgsql, нашли ли какие-то узкие места)

      Что касается вопросов производительности, то сравнивать СУБД по данному критерию достаточно сложно, поэтому для себя мы объективно оцениваем производительность по времени выполнения ряда самых ресурсоемких процедур при идентичных входных условиях (одинаковое «железо», одинаковые исходные данные).

      Как уже было отмечено в статье наиболее "затратными" получились различного рода манипуляции между таблицами: когда большие массивы данных претерпевая изменения переходят из таблицы в таблицу.

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