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

    «Ландшафт» СУБД в проектах нашей компании до недавнего времени выглядел так: большую часть составляла 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 выполним следующие шаги.

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

    Все дальнейшие действия будут производится из-под учетной записи root. Установим репозиторий:
    #yum install 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. В обеих хорошо соотносятся типы данных, и там, и там есть такое понятие, как схема. Воспользуемся этим и будем переносить данные «посхемно». Процесс миграции будет состоять из следующих шагов.

    1. Создание проекта миграции с помощью Оra2pg.
    2. Правка файла конфигурации ora2pg.conf.
    3. Выгрузка DDL таблиц, индексов, constraints из Oracle.
    4. Создание БД в Postgres.
    5. Импорт DDL таблиц, подготовленный на 3-м шаге.
    6. Копирование данных.
    7. Импорт 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. Звучит многообещающе.

    Удачных миграций!
    CUSTIS
    Company

    Comments 16

      0
      Я думаю стоит отметить, что эта тулза в состоянии также и конвертнуть PLSQL в PGPLSQL, но делает это не совсем хорошо. Но, все таки, может быть проще поправить уже конвертнутое решение, чем писать все самому.
        +2
        Не совсем хорошо это мягко сказано. На самом деле без сильной боли можно мигрировать только в простых случаях, когда субд как записная книжка используется (кстати, а Оракл то вам зачем нужен был тогда?).
        Кроме проблем конвертации кода (не, разобрать PLSQL, построить дерево, трансформировать и кодогенерировать я и сам могу). есть проблема совместимости пакетов, работы с датами, работы с лобами. Для трехзвенок приходится в отсутствии такого понятия как context в PostgreSQL весьма изощрятся. Временные таблицы в оракле временные данные, в постгресе — существуют во время сессии только.
        Так что конвертнуть данные это только первый шаг по дороге миграции.
          0
          Согласен, что данные — это только начало. Но оговорюсь, что проект был простой, т.е. не содержал логику в PL/SQL.
          Почему для такого простого проекта изначально был выбран Oracle? Тут всё просто — у клиента это своего рода корпоративный стандарт.
          0
          На данный момент на соседнем проекте, где очень много развесистой бизнес-логики находится в PL/SQL, мы проводим такие эксперименты. Планируем по результатам написать статью, но пока могу сказать, что результаты более чем печальные.
          0
          В связи с этим мы были вынуждены, проанализировав и поправив логику приложения, в ущерб гибкости сконвертировать его в «подходящие» типы, например, в varchar2(100)

          В Postgres вроде бы нет varchar2. Я так понимаю, перешли на VARCHAR обычный?
            +1
            Конечно, имелось в виду, что в исходной БД Oracle до миграции некоторые anydata пришлось перевести в varchar2. При конвертации они получили тип varchar в PostgreSQL
            0
            А как решили проблему с отсутствием в PostgreSQL такого понятия как пакеты?
              0
              Этот проект был простой и логики на PL/SQL не было. На соседнем проекте проводим эксперименты с конвертацией PL/SQL, но я пока не готов аргументированно показать «как надо». Думаю, по результатам, напишем ещё одну статью.
                0
                было бы здорово :)
                а job тоже не было в проекте?
                Я как раз сегодня столкнулся с этим…
              0
              А пробовали просто создать dblink в Оракле на Постгре и залить данные на Постгре?
                0
                А разве есть connector?
                  0
                  В Оракле для любой БД можно создать коннектор. Например мы создали для mysql и писали туда данные из Оракла. Единственный нюанс нужно было писать 2 раза commit
                    0
                    Для MySQL есть родной как раз.
                    А вот для постгреса — насколько я знаю нет. Написать можно все что угодно конечно, хоть процедуру на C и ее в триггере вызывать. Сложность миграции при этом правда не снижается.
                      0
                      Мы по-моему не родной использовали, а через ODBC
                        0
                        Есть еще один способ переливки данных — настроить Foreign Data Wrapper для Oracle на стороне Postgres. Но опять же, структура уже должна существовать
                  0
                  Если у вас в PostreSQL уже есть структура таблиц с индексами, то можно сделать и так. Но нам же нужно было создать структуру с нуля. DDL, который есть у вас в Oracle, один в один не выполнится в PostgreSQL, нужна конвертация. Вот тут Ora2Pg и пригодился.

                Only users with full accounts can post comments. Log in, please.