Зачем переносить данные
Тема переноса (миграции) данных из 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
Приведу основные моменты установки:
- Для начала создадим пользователя и группу, добавим одно в другое:
useradd oracle grpoupadd oinstall /usr/sbin/usermod –g oinstall –G oracle - Далее качаем и устанавливаем дистрибутивы с сайта 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 и так далее - Если скачали пакеты как zip-архив, то распаковываем все и переносим в созданный каталог /u01/app/oracle/instant_client_18_3/. Не забудем прежде дать права пользователю:
сhown –R oracle:oinstall /u01/app/oracle/instant_client18_3/ - Настраиваем переменные окружения в ./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:
В новой СУБД создано 130 новых таблиц, что соответствует количеству таблиц в оригинальной БД.
Также видим загруженные последовательности и триггерные функции.
Загрузились и ограничения целостности:
Проверим наличие данных в таблице:
Заключение
В результате преобразований 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
ссылка на оригинал статьи https://habr.com/ru/company/infowatch/blog/494430/
Добавить комментарий