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

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


    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

    InfoWatch
    Компания

    Комментарии 6

      +1
      А зависимости оно умеет разруливать? Внешние ключи, в частности?

      И почему решили заливать в древнюю версию pg? Она уже даже не поддерживается.
        0
        Внешние ключи импортирую строчкой IWDM < schema/tables/ FKEYS_table.sql
        Насколько знаю есть ограничения — например, не поддерживает партиционированные таблицы
        По версии: эксперимент начался достаточно давно, не сразу до публикации дошло
          0
          15Gb — это правильный use case для миграции из Oracle
            0
            Спасибо за статью, познавательно.
            Скажите, а как утилита справляется с переносом пакетов, хранимых процедур? В том числе, интересно возможно ли вообще этой штукой переносить очень большие пакеты, размерами больше 100к строк?
              0
              Боюсь в любом случае необходимо вручную допиливать. В официальной доке конверсия пакетов помечена как work in progress, но по умолчанию опция включена. Для преобразований присутствуют различные параметры — замена NULL на пустые значения, вызовы SYS_GUID() в UUID, правки c outer join и др.
                0
                Да не, хранимки нереальная задача переносить. Все равно что исходники с C++ на Java конвертировать (а че, синтаксис похожий)…

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

              Самое читаемое