Миграция данных из Oracle в Postgres

«Ландшафт» СУБД в проектах нашей компании до недавнего времени выглядел так: большую часть составляла Oracle, существенно меньшие — MS SQL и MySQL.

Но, как известно, нет ничего вечного, и недавно к нам поступил запрос о применимости Postgres в одном из наших проектов. К этой СУБД мы присматривались в последние пару лет очень пристально — посещали конференции, meetup’ы, но вот попробовать ее в «боевых» условиях до недавнего времени не доводилось.

Итак, задача

Дано: сервер Oracle (single instance) 11.2.0.3 и набор не связанных друг с другом схем общим объемом ~ 50GB.
Необходимо: перенести данные, индексы, первичные и ссылочные ключи из Oracle в Postgres.

Выбор инструмента миграции

Обзор инструментария для миграции показал наличие как коммерческих инструментов, таких как Enterprise DB Migration Toolkit и Oracle Golden Gate, так и свободного ПО.

Перевод был запланирован однократный, поэтому требовалось зрелое средство, вместе с тем понятное и простое. Кроме того, конечно, учитывался и вопрос стоимости. Из свободного ПО наиболее зрелым на сегодняшний день является проект Ora2Pg Жиля Дарольда (Darold Gill), он же во многом превзошел по функционалу и коммерческие варианты.

Преимущества, склонившие чашу весов в его сторону:

богатый функционал;
активное развитие проекта (15 лет разработки, 15 мажорных релизов).

Принцип работы утилиты командной строки Ora2Pg довольно прост: она соединяется с БД Oracle, сканирует указанную в файле конфигурации схему и выгружает объекты схемы в виде DDL-инструкций в sql-файлы. Сами данные можно как выгрузить в виде INSERT’ов в sql-файл, так и вставить напрямую в созданные таблицы СУБД Postgres.

Установка и настройка окружения

В компании мы используем подход DevOps для создания виртуальных машин, установки необходимого софта, конфигурирования и развертывания ПО. Наш рабочий инструмент — Ansible. Но для того, чтобы облегчить восприятие и не вводить в статью новые сущности, к делу не относящиеся, далее мы будем показывать ручные действия из командной строки. Для тех, кому интересно, мы выкладываем Ansible playbook для всех шагов здесь.

Итак, на виртуальной машине с OS Centos 6.6 выполним следующие шаги.

Установим репозиторий Postgres.
Установим Postgres 9.4 сервер.
Создадим БД и настроим доступ.
Установим Postgres как сервис и запустим его.
Установим instant клиент Oracle.
Установим утилиту Ora2Pg.

Все дальнейшие действия буду производится из-под учетной записи root.
Установим репозиторий:
#yum install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
Установим Postgres 9.4:
#yum install postgresql94-server
Создадим кластер Postgres:
#service postgresql-9.4 initdb

Настройка доступа сводится к тому, что мы специально понижаем безопасность соединения Postgres для удобства тестирования.
Конечно, в продакшн-среде мы не рекомендуем так делать.

В файле /var/lib/pgsql/9.4/data/postgresql.conf необходимо раскомментировать строчку listen_addresses = '*'
В файле /var/lib/pgsql/9.4/data/pg_hba.conf для локальных и удаленных соединений необходимо поставить метод trust.
Секция после редактирования выглядит так:

# TYPE DATABASE USER ADDRESS METHOD  # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all all trust

Зарегистрируем Postgres как сервис и запустим его:

#chkconfig postgresql-9.4 on #service postgresql-9.4 restart

Для установки Oracle instant client необходимо загрузить с OTN следующие пакеты:

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm

Установим их:

#yum install /tmp/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm #yum install /tmp/oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm #yum install /tmp/oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm #yum install /tmp/oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm

Создадим папку для tnsnames.ora:

#mkdir -p /usr/lib/oracle/11.2/client64/network/admin #chmod 755 /usr/lib/oracle/11.2/client64/network/admin

Установим следующие переменные окружения (в .bash_profile пользователя):

export ORACLE_HOME=/usr/lib/oracle/11.2/client64 export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib export TNS_ADMIN=$ORACLE_HOME/network/admin

И проверим работоспособность.

sqlplus system/<you_password_here>@host.domain.ru/SERVICE

Если все ок — то получим примерно такой вывод:

 Connected to:      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production      With the Partitioning, OLAP, Data Mining and Real Application Testing options       SQL>

Остался последний шаг настройки — установка Оra2pg.
Скачиваем с сайта последнюю версию Ora2Pg. На момент написания статьи была версия 15.2.

Установим необходимые пакеты:

#yum install gcc cpan postgresql94-plperl postgresql94-devel

Установим модуль CPan:

#cpan

Установим дополнительные модули для Perl:

#cpan Test::Simple DBI Time::HiRes DBD::Oracle DBD::Pg

Распакуем Ora2pg в, скажем, /install:

#cd /install #tar -xvf ora2pg-15.2.tar.gz

Соберем Ora2pg:

#perl Makefile.PL #make #make install

Миграция

СУБД Postgres по «духу» наиболее близка к Oracle. В обеих хорошо соотносятся типы данных, и там, и там есть такое понятие, как схема. Воспользуемся этим и будем переносить данные «посхемно».

Процесс миграции будет состоять из следующих шагов:

создание проекта миграции с помощью Оra2pg;
правка файла конфигурации ora2pg.conf;
выгрузка DDL таблиц, индексов, constraints из Oracle;
создание БД в Postgres;
импорт DDL таблиц, подготовленный на 3-м шаге;
копирование данных;
импорт DDL индексов и constraints.

Все последующие действия будем выполнять от пользователя postgres.

#su -l postgres

Создадим проект миграции. Проект состоит из набора папок tables/functions/views/packages, в которых будут находится sql-файлы с DDL соответствующих объектов, конфигурационного файла ora2pg.conf и скрипта запуска — export_schema.sh.

$ora2pg --init_project my_project_name $cd my_project_home $vi config/ora2pg.conf

Конфигурирование

Файл конфигурации Ora2pg довольно объемен, и я остановлюсь только на тех параметрах, которые являются корневыми или потребовались во время миграции наших данных. Про остальные я рекомендую узнать из этой статьи.
Секция, описывающая параметры соединения c БД Oracle:

ORACLE_HOME /usr/lib/oracle/11.2/client64 ORACLE_DSN dbi:Oracle:host=oracle_host.domain.ru;sid=<SID> ORACLE_USER SYSTEM ORACLE_PWD MANAGER

Секция, описывающая, какую схему выгружаем:

EXPORT_SCHEMA 1 SCHEMA TST_OWNER

И указание, в какую схему загружаем:

PG_SCHEMA tst_owner

Указываем тип экспорта. Параметр COPY говорит о том, что мы будем копировать данные напрямую из Oracle в Postgres, минуя текстовый файл.

TYPE TABLE,COPY

Секция, описывающая параметры соединения c БД Postgres:

PG_DSN dbi:Pg:dbname=qqq;host=localhost;port=5432 PG_USER tst_owner PG_PWD tst_onwer

Секция конвертации типов данных. Для того, чтобы тип number() без указания точности не конвертировался в bigint, укажем:

DEFAULT_NUMERIC numeric

На этом конфигурационные шаги закончены, и мы готовы приступить к переносу. Выгрузим описания схемы в виде набора sql-файлов c DDL объектов:

$./export_schema.sh

Создадим базу данных qqq, пользователя test_owner и выдадим необходимые права.

$psql  postgres=#create database qqq; CREATE DATABASE postgres=#create user test_owner password ‘test_owner’; CREATE ROLE postgres=#grant all on database qqq to test_owner; GRANT postgres=#\q

Выполним импорт sql файла c DDL таблиц

$psql -d qqq -U test_owner < schema/tables/table.sql

Теперь все готово к копированию данных.
Запускаем:

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

Несмотря на тот факт, что в командной строке мы указываем параметр -о с именем файла, в который следует сохранять выгрузку, вставка данных происходит напрямую из Oracle в Postgres. В нашем случае скорость вставки была около 6 тыс. строк в секунду, но это, конечно же, зависит от типов копируемых данных и окружающей инфраструктуры.
Остался последний шаг — создать индексы и constraints.

$psql -d qqq -U test_owner < schema/tables/INDEXES_table.sql $psql -d qqq -U test_owner < schema/tables/CONSTRAINTS_table.sql

Если в процессе выполнения предыдущих команд вы не получили ошибок — поздравляю, миграция прошла успешно!
Но, как известно из закона Мёрфи: ‘’Anything that can go wrong will go wrong’’.

Наши подводные камни

Первый подводный камень уже был упомянут выше: тип number() без указания точности конвертируется в bigint, но это легко исправить правильной конфигурацией.
Следующей сложностью оказалось то, что в Postgres нет типа, аналогичного Oracle anydata. В связи с этим мы были вынуждены, проанализировав и поправив логику приложения, в ущерб гибкости сконвертировать его в «подходящие» типы. Например, в varchar2(100).
Кроме того, если у вас есть какие-то кастомные типы, то все придется переделывать, поскольку они не транслируются, но это тема как минимум для отдельной статьи.

Подведем итоги

Утилита Ora2Pg, несмотря на сложность настройки, проста и надежна в использовании. Ее смело можно рекомендовать для миграции небольших и средних БД. Кстати, ее автор на PGConf Russia объявил о том, что начинает проект MS2Pg.
Звучит многообещающе.
Удачных миграций!

ссылка на оригинал статьи http://habrahabr.ru/post/262605/