Как стать автором
Обновить
48.21

Миграция из Oracle в Postgres

Время на прочтение 9 мин
Количество просмотров 19K

Зачем переносить данные


image


Тема переноса (миграции) данных из Oracle в Postgres набирает обороты. В РФ количество запросов по переходу на Postgres на волне импортозамещения, сейчас больше, чем когда либо. Oracle — очень качественный, мощный и отлично документированный инструмент. Но вместе с тем очень дорогой в лицензировании и поддержке. Зачастую охватить все его возможности не получается в силу специфики использования. Ключевыми параметрами эксплуатации в нашем случае являются производительность системы, процент использования (утилизации) железа, простая тех. поддержка, а также дешевое и быстрое масштабирование — легкое развертывание новых баз.


Я решил изучить процесс миграции данных, изучая то, как реализовать пожелания конкретного заказчика, а также из практического любопытства, теперь я пробую перенести небольшую схему около 15 Гбайт с Oracle 11.2.0.4 на Linux Redhat 6.8 в Postgres 9.4 на Windows. Немного погрузившись в тему, я решил написать эту статью и рассмотреть процесс подробно. В отрыве от нашей специфики пост будет полезен интеграторам и DBA при планировании переноса данных/CUT.


Как перенести данные


Как выяснилось, существуют инструменты и под задачи переноса данных. Наиболее рациональным способом, на мой взгляд, является утилита ORA2PG Жиля Дарольда. Ora2PG соединяется с БД Oracle с помощью Perl-модуля и выгружает все объекты схем, перечисленных в конфигурационном файле, в SQL-файлы, параллельно формируя структуру каталогов. Сами файлы представляют собой DDL-команды. Далее выгружаются данные: в виде INSERT команд для таблиц. Кроме того данные можно вставить и непосредственно из базы в базу, т.е. в свежесозданную схему Postgres. Импорт осуществляется модулем PERL:PG.


Начнем с того, что у нас уже есть сервера c развернутыми СУБД Oracle и Postgres в одной сети. Помимо них, для осуществления плана по миграции данных, желательно иметь промежуточную машину с настроенным окружением.


Далее я ставлю виртуальную машину на CentOS 7 в минимальной конфигурации плюс development tools и вывожу ее в Интернет. В нашем случае база Postgres расположена по адресу 10.70.85.11, база Oracle — 10.70.85.14, а виртуальная машина — по адресу 10.70.85.15.
Сама установка ORA2PG потребует подготовки.


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


  • Выход в Интернет — в моем случае через прокси-сервер: ставим и настраиваем авторизующий прокси CNTLM. Создаем пользователей (пользователь ‘postgres’ должен создаться самостоятельно при установке агента).
  • Ставим репозиторий Postgres с сайта Postgres для версии 9.4.
  • Ставим клиент Postgres 9.4 и средства разработки, иначе не соберем Perl-модуль
  • Ставим instant-клиент Oracle. Он ставится не так просто, как хотелось бы, но готовых инструкций достаточно много.
  • Настраиваем подключение для обоих клиентов.
  • Доустанавливаем необходимые пакеты.
  • Собираем модули Perl.
  • Ставим сам ORA2 PG.

Установка клиента Oracle


Приведу основные моменты установки:


  1. Для начала создадим пользователя и группу, добавим одно в другое:

    useradd oracle
    grpoupadd oinstall
    /usr/sbin/usermod –g oinstall –G oracle
  2. Далее качаем и устанавливаем дистрибутивы с сайта Oracle (потребуется регистрация):

    oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm
    oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm
    oracle-instantclient18.3-jdbc-18.3.0.0.0-1.x86_64.rpm
    oracle-instantclient18.3-devel-18.3.0.0.0-1.x86_64.rpm
    rpm –Uvh oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm и так далее
  3. Если скачали пакеты как zip-архив, то распаковываем все и переносим в созданный каталог /u01/app/oracle/instant_client_18_3/. Не забудем прежде дать права пользователю:

    сhown –R oracle:oinstall /u01/app/oracle/instant_client18_3/
  4. Настраиваем переменные окружения в ./bash_profile для Oracle. Для полноценной работы необходимо дописать переменную клиентских библиотек LD_LIBRARY_PATH:

    export SQLPATH=/u01/app/oracle/instantclient_18_3
    export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    export TNS_ADMIN=${SQLPATH}
    export LD_LIBRARY_PATH=${SQLPATH}
    export PATH=${SQLPATH}:${PATH}

Если после установки при попытке запуска вас преследует ошибка: “./sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory”, понадобится установить libaio1.


Yum install libaio1 libaio-devel

Поставим readline:


yum install readline-devel.x86_64

После установки клиента Oracle настраиваем доступ к БД: создаем файл TNSNAMES.ORA и кладем его в подкаталог /network/admin в каталоге клиента.


Предполагаем, что Listener на стороне Oracle настроен по умолчанию.


Известно, что даже опытные администраторы БД не помнят на память синтаксис tnsnames.ora, поэтому привожу пример:


iwtm =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = 10.70.85.14)(Port = 1521 ))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = iwtm )
 )
)

Проверим доступ через tnsping:


tnsping iwtm
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.70.8
.14)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = iwtm)))
OK (40 msec)

На практике я столкнулся с тем, что sqlplus не подключается, ссылаясь на ошибку прокси:


Error 46 initializing SQL*Plus
HTTP proxy setting has incorrect value
SP2-1502: The HTTP proxy server specified by http_proxy is not accessible

Отключим, указав еще пару строчек в ./bash_profile Oracle:


unset https_proxy
unset http_proxy

Ставим клиент PostgreSQL


Укажем репозиторий, найденный на домашней странице:


yum install https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm

И ставим клиент:


yum install postgresql94

Опционально можно поставить и серверные компоненты:


yum install postgresql94-server  

Проверяем доступ к базе postgres


Прописываем доступ для нашей машины на стороне сервера Postgres в файле pg_hba.conf, а именно запись следующего вида:


host all all 10.70.85.15./32 md5

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


Ставим Perl


Perl нужен версии не ниже 5.10. Для установки модулей ставим CPAN:


yum install gcc cpan

При первом запуске CPAN попробует сам себя настроить. Из важных вопросов спросит учетную запись для прокси и попросит указать зеркало для пакетов.


Укажем зеркало в РФ и еще одно:


 http://mirror.truenetwork.ru/CPAN/
http://mirror.ps.kz/CPAN/

Пробуем собрать модули Perl для подключения


Начнем с DBD::Oracle для подключения к Oracle. Собирать модуль будем из-под Oracle-записи (проверьте, что вы уже поставили ora-instant-client, SDK, SQL plus и библиотеку libaio1), где у нас есть все переменные окружения. На всякий случай подтянем их еще раз:


source  ~/.bash_profile

        perl -MCPAN -e 'install DBD::Oracle'

Первый вариант может не сработать, тогда ставим по документации в Ora2PG.


perl -MCPAN -e shell
        cpan> get DBD::Oracle
        cpan> quit
        cd ~/.cpan/build/DBD-Oracle*
        export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
        export ORACLE_HOME=/usr/lib/oracle/11.2/client64/lib
        perl Makefile.PL
        make
        make install

Далее DBI::PG для postgres. Требуется версия не ниже 1.614, иначе получите интересную ошибку:


Can't set DBI::db=HASH(0x9036450)->{AutoInactiveDestroy}: 
    unrecognised attribute name or invalid value 
    at /rad/perl/lib/cpan/DBI.pm line 708.
Can't get DBI::db=HASH(0x9036450)->{AutoInactiveDestroy}: 
    unrecognised attribute name

Можно обновить все модули из CPAN командой:


upgrade /(.*)/

Ставим дополнительные библиотеки


yum install libdbd-pg-perl postgresql-plperl
yum install postgresql94-devel

и сам модуль


yum install perl-DBD-Pg

        perl -MCPAN -e 'install DBD::PG'

Если модуль автоматически не собрался, скопируем исходники, дадим права пользователю postgres и соберем пакет под ним:


cd ~/.cpan/build/DBD-PG*
        perl Makefile.PL
        make && make install

После настройки модулей


В БД Postgres открываем pgadmin, создаем пустую базу данных, назначаем права учетной записи — мы заходим под postgres.


Итак, мы все поставили.


Приступаем к сборке самой утилиты


tar -xf ora2pg-x.x.tar.bz2
        cd ora2pg-x.x/
        perl Makefile.PL
        make && make install

Экспорт данных


Далее работаем под oracle. Запускаем и инициируем наш проект:


ora2pg --project_base /home/oracle/export/ --init_project test_project

Команда создаст дерево каталогов и конфигурационный файл. Переходим к этому файлу. В нем настраиваем подключение к обеим БД. Это секция DBI::oracle — указываем хост инстанс и логин/пароль:


# Set Oracle database connection (datasource, user, password)
ORACLE_DSN  dbi:Oracle:host=10.70.85.14;sid=iwtm;port=1521
ORACLE_USER system
ORACLE_PWD  password

DBI::PG — указываем хост базу данных и логин/пароль:


# Define the following directive to send export directly to a PostgreSQL
# database. This will disable file output.
PG_DSN      dbi:Pg:dbname=IWDM;host=10.70.85.11;port=5432
PG_USER postgres
PG_PWD      password

Указываем схему Oracle для экспорта:


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

Указываем, что схему нужно пересоздать (по умолчанию):


# Enable/disable the CREATE SCHEMA SQL order at starting of the output file.
# It is enable by default and concern on TABLE export type.
CREATE_SCHEMA   1

Указываем схему Postrges, в которую будет осуществляться импорт:


PG_SCHEMA   IWDM

Указываем output-файл для экспорта:


OUTPUT      output.sql

Выбираем данные для экспорта:


# EXPORT SECTION (Export type and filters)
# Type of export. Values can be the following keyword:
TYPE        TABLE

В наш экспорт войдут таблицы, индексы, последовательности, триггеры и ограничения целостности данных (далее constraints):


Я также включаю отладочный режим для наглядности:


# Trace all to stderr
DEBUG       1

Начинаем экспорт схемы Oracle. Поскольку это занимает время, запускаем его в фоне для надежности и идем пить чай:


cd /home/oracle/export/test_project/
nohup  ./export_schema.sh -d  >/dev/null 2>&1

Дампим данные в файлы:


ora2pg -t COPY -o output.sql -b ./data -c ./config/ora2pg.conf

Пробуем импортировать схему


./import_all.sh -h 10.70.85.11 -d IWDM -o IWDM -U postgres./import_all.sh -h 10.70.85.11 -d IWDM -o postgres -U postgres –y -I

Обратите внимание на следующие опции:


–y — перед загрузкой скрипт предлагает затереть ранее загруженную схему, потом ввести пароль, создать схему и т.д. по кругу.


Опция -y крайне удобна, когда импорт не проходит в десятый раз, и ваши правки просто повторяются автоматически.


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


-I — это значит, что мы не грузим индексы, ограничения на первичные внешние ключи и триггеры. Иначе загрузка данных будет проблематична.


Стоит сказать, что импорт редко проходит гладко. В беспроблемных на первый взгляд таблицах кроются ошибки синтаксиса. В моем случае импорт схемы спотыкался на таблице User и на всех последующих со схожими названиями, далее на индексах и далее на внешних ключах таблиц, где присутствовало слово ‘user’. По умолчанию стоит директива STOP_ON_ERROR.


Обратите внимание на зарезервированные слова – user, offset, from, to. Такие записи нужно заключать в двойные кавычки вручную, иначе импорт «споткнется».


Для подробной справки посмотрим:


https://postgrespro.ru/docs/postgresql/9.4/sql-keywords-appendix


Заменим их все! Немного магии редактора sed:


sed -i 's/user/”user”/' /schema/tables/g’ data/tables.sql

Вполне возможно, что придется закомментировать какой-нибудь триггер или индекс и разобраться с ним позднее.


Зная о проблеме выше, открываем файл /schema/output.sql и правим имена столбцов и таблиц аналогичным образом.


Данные c учетом наших правок следует импортировать вручную:


psql -h 10.70.85.11 -U postgres -p 5432 –d IWDM < data/output.sql

Далее индексы:


psql -h 10.70.85.11 -U postgres -d IWDM < schema/tables/ INDEXES_table.sql

Ограничения на первичные и внешние ключи:


psql -h 10.70.85.11 -U postgres -d IWDM < schema/tables/ CONSTRAINTS_table.sql
psql -h 10.70.85.11 -U postgres -d IWDM < schema/tables/ FKEYS_table.sql

Далее триггеры:


psql -h 10.70.85.11 -U postgres -d IWDM < schema/triggers/trigger.sql

Посмотрим, что у нас было и что получилось


Для этого используем графические средства администрирования: Oracle SQL Developer для Oracle и PGadmin для postgres:


image


В новой СУБД создано 130 новых таблиц, что соответствует количеству таблиц в оригинальной БД.


image


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


Загрузились и ограничения целостности:


image


Проверим наличие данных в таблице:


image


image


Заключение


В результате преобразований Oracle-Postgres, благодаря утилите Ora2Pg, нам удалось сконвертировать таблицы и преобразовать основные типы, которые используются только в Oracle: varchar2 в varchar, Number в Numeric и т.д. Полагаю, что адекватной альтернативы такому способу нет. Конечно, можно вручную создавать таблицы с другими типами данных в новой БД и переписывать код целиком для функций, но это слишком трудоемко, очень непрактично и скорее мешает, чем помогает решать задачи конкретных заказчиков Вот пример преобразования таблиц.


Oracle:


CREATE TABLE "IWDM"."Statistics1" 
   (    "Id" NUMBER(20,0) NOT NULL ENABLE, 
    "CreateDate" DATE NOT NULL ENABLE, 
    "User" NVARCHAR2(128), 
    "Workstation" NVARCHAR2(255), 
    "Operation" NUMBER(*,0) NOT NULL ENABLE, 
    "UnicParam" NUMBER(20,0) NOT NULL ENABLE, 
    "UnicString" NVARCHAR2(255) NOT NULL ENABLE, 
    "Description" NVARCHAR2(2000), 
          ) 
  TABLESPACE "IWDM" ;

Postgres:


CREATE TABLE statistics1 (
    id numeric(20) NOT NULL,
    createdate timestamp NOT NULL,
    "user" varchar(128),
    workstation varchar(255),
    operation numeric(38) NOT NULL,
    unicparam numeric(20) NOT NULL,
    unicstring varchar(255) NOT NULL,
    description varchar(2000)
) ;

Автоматически сконвертированы последовательности.


Oracle:


CREATE SEQUENCE  "IWDM"."SQ_Statistics1"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;

Postgres:


СREATE SEQUENCE iwdm.sq_statistics1
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE iwdm.sq_statistics1
  OWNER TO postgres;

Напоследок, поговорим о других возможных проблемах с экспортом.


Хотя ORA2PG и умеет конвертировать язык PL/SQL в PL/PGSQL, специфический код Oracle в индексах или ограничениях целостности будет сконвертирован с ошибками. Пример от Жиля Дарольда:


CREATE INDEX idx_userage ON user ( to_number(to_char('YYYY', user_age)) ); 
CREATE INDEX idx_userage ON «user» ( date_part('year', user_age) );

Вполне возможны проблемы с кодировками при вставке записей в Postgres. С проблемой зарезервированных слов я уже столкнулся. К счастью, больших объектов типа BLOB в моих таблицах не было. Конвертация типа BLOB в bytea была бы весьма долгой. В документации рекомендуется либо исключить такие таблицы опцией EXCLUDE в конфигурационном файле, либо использовать многопоточность с опцией THREAD_COUNT.


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


Остается пожелать всем положительного опыта.


Автор статьи: Тимур Галиулин GTRch

Теги:
Хабы:
+7
Комментарии 6
Комментарии Комментарии 6

Публикации

Информация

Сайт
www.infowatch.ru
Дата регистрации
Дата основания
Численность
201–500 человек
Местоположение
Россия