В настоящее время тема миграции с СУБД Oracle на СУБД PostgreSQL (и разработанную на её основе СУБД Postgres Pro) является очень актуальной. В этой области у команды Postgres Professional накоплен многолетний опыт, которым мы решили поделиться. На основе наших материалов для внутреннего обучения мы подготовили серию статей для Хабра о миграции данных в PostgreSQL из «оракловой» базы.

Список полезных ссылок на близкие темы можно найти в конце статьи. Например, рекомендуется почитать:

Наша сегодняшняя статья охватывает основные этапы переноса данных, а также различия типов и форматов данных в целевой СУБД и СУБД-источнике.

Процесс переноса данных из СУБД Oracle в СУБД Postgres состоит из следующих этапов:

Определение использования специфичных для СУБД-источника возможностей хранения и обработки данных.
Выбор средств конвертации данных.
Конвертация данных.
Подключение и извлечение данных из СУБД-источника и наоборот.
Выбор подхода к уменьшению времени простоя.

Во-первых, у Oracle и PostgreSQL разные механизмы хранения и обработки данных. Требуется определить используемые в СУБД-источнике возможности, которых нет в целевой СУБД. Часть из них может быть получена путём установки дополнительных модулей.

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

Далее нужно выбрать средства конвертации данных, подходящие для ваших задач. В отдельной статье мы расскажем о достоинствах и недостатках наиболее популярных инструментов.

Затем с использованием выбранных инструментов данные переносятся в таблицы целевой СУБД. При этом, помимо полного переноса, возможны также отслеживание и перенос изменений СУБД-источника для уменьшения времени простоя.

После того, как инструменты выбраны и данные перенесены в целевую базу, следует проверить успешность конвертации – не только с точки зрения схемы, но и с точки зрения самих данных. Нужно провести количественное сравнение и оценить качество переноса данных, их целостность.

Также часто возникает необходимость взаимодействия с данными СУБД Oracle из СУБД PostgreSQL и наоборот. Далеко не все объекты исходной СУБД могут быть перенесены в новую по разным причинам. Но при этом во время работы в СУБД PostgreSQL могут потребоваться данные из СУБД Oracle. Следовательно, необходима возможность подключения из целевой СУБД к СУБД-источнику с извлечением данных оттуда, и наоборот.

После переноса данных нужно выбрать стратегию отслеживания изменений в СУБД-источнике и их применения на целевой СУБД для уменьшения времени простоя приложения при переключении на новую СУБД

Особенности, затрудняющие перенос данных при миграции

Вернёмся к особенностям, которые затрудняют перенос данных. Перечислим основные:

Несовместимые типы данных.
Специфичные форматы хранения данных.
Вычисляемые столбцы.
Внешние структуры данных.

Второй блок особенностей включает в себя ещё несколько пунктов:

Cекционирование.
Временные таблицы.
Использование RAC и Data Guard.

Несовпадающие типы данных в СУБД Oracle и Postgres

Начнём с особенностей типов данных. В СУБД Oracle есть типы данных без прямого аналога в СУБД PostgreSQL.

Яркий пример – тип DATE, содержащий дату и время.

В PostgreSQL есть:

time - время дня без даты, предусматривается указание часового пояса;
date - дата без времени суток;
timestamp – дата и время, предусматривается указание часового пояса;
interval – временной интервал.

Чтобы появилась дата со временем, необходимо использовать тип oracle.date из модуля Orafce. Его потребуется установить, тогда «оракловый» тип DATE будет доступен. Либо стоит рассматривать TIMESTAMP как ближайший аналог DATE в Oracle

Второй пример несовпадения типов: в СУБД Oracle есть тип sdo_geometry позволяющий нам работать с геометрией, в том числе и с кадастровыми данными. Конвертация данных типа sdo_geometry в СУБД PostgreSQL возможна только с установкой сторонних модулей, таких, как PostGIS

Различия в форматах хранения данных

Разберёмся со спецификой форматов хранения данных. Если мы говорим про большие символьные объекты CLOB и тип LONG, существовавший в Oracle до них, то их преобразовывают в «постгрессовый» тип TEXT. Но следует учитывать, что в TEXT максимальная величина хранимого значения не должна превышать 1 ГБ.

Если говорить про большие двоичные данные BLOB, RAW или LONG RAW, то здесь нужно использовать bytea, но и для этого типа хранимое значение не может быть больше 1 ГБ. Либо можно использовать тип large object, о котором будет рассказано чуть позже.

Типы NUMBER(38,0) и INTEGER в СУБД Oracle имеют свои особенности. В данном случае значение может содержать до 38 цифр до запятой и не содержит цифр в дробной части. Тем не менее, при анализе значений вполне может оказаться, что там либо 0, либо 1. Тогда это логическое значение, и в СУБД Postgres оно будет соответствовать типу bool. Соответствиями в разных случаях могут быть smallint, integer, bigint, особенно если речь идёт о первичных ключах – их значения берутся из последовательностей. Поэтому, если мы видим тип INTEGER, это не означает автоматически, что там 38 цифр. Вполне возможно, что этот тип в PostgreSQL можно заменить на более компактный, и это хорошо. Если использовать более компактные типы, то требования к дисковому пространству сократятся. А если ещё и расположить правильно с точки зрения выравнивания, то данные будут храниться в более компактном виде, требуя меньше места на диске.

Форматы чисел с плавающей точкой

Когда у «ораклового» типа NUMBER обозначено некоторое количество знаков после запятой, нужно использовать тип NUMERIC в СУБД Postgres. Если мы говорим про BINARY_FLOAT, BINARY_DOUBLE и BINARY_INTEGER в Oracle, при переходе на PostgreSQL они переводятся в типы данных REAL, DOUBLE PRECISION и INTEGER соответственно.

В типе NUMBER СУБД Oracle можно указать отрицательное количество знаков после запятой, это так называемое «округление слева». В PostgreSQL для типа NUMERIC это стало возможно только с версии 15. Поясним сокращения p и s для этих типов: precision – общее количество цифр числа, а scale – количество знаков после запятой.

Если не указать точность у NUMERIC, то количество знаков после запятой может достигать 16383, что в большинстве случаев не требуется. По умолчанию, утилиты переноса данных в скриптах создания схемы данных указывают тип NUMERIC без обозначения точности. Поэтому в конфигурационных файлах требуется чётко прописать правила конвертации типов с указанием желаемого количества знаков в дробной части.

Точность или скорость?

Также нужно учитывать, чего мы хотим добиться. Если для нас в приоритете точность (её, например, требуют денежные величины), то нужно использовать тип NUMERIC. Иногда понятно, что важна производительность, а значения точными не будут (например, приблизительные значения в методах расчёта вычислительной математики). В этом случае можно воспользоваться так называемыми «неточными типами», REAL и DOUBLE PRECISION, и они будут выгоднее в плане скорости обработки. С точки зрения алгоритмов вычислительной математики, это как раз то, что нам нужно – быстрота вычислений

Пустая строка ≠ NULL

Всегда стоит помнить, что в СУБД PostgreSQL пустая строка и NULL не одно и то же. Если в Oracle взять строку «Здравствуй» и объединить её со значением NULL, результатом будет «Здравствуй». В PostgreSQL результатом того же объединения будет NULL. Таким образом, это может стать проблемой, приводящей к неожиданным результатам. Такие ошибки крайне сложно отловить, особенно если запрос достаточно большой. Например, это может быть запрос на несколько тысяч строк кода. Важно помнить, что пустая строчка и NULL – совершенно не одно и то же. Поэтому правило, состоящее в том, что некая строка IS NOT NULL изначально будет ложным высказыванием, потому что пустая строка не является NULL

Особенности «постгрессовых» типов: TEXT и VARCHAR

Значения TEXT хранятся в отдельном хранилище TOAST и извлекаются по требованию тем же способом, что и обычные данные. Максимальный объём одной записи – 1 ГБ. Стоит отметить, что PostgreSQL позволяет не писать максимальную длину строки при указании типа VARCHAR. В результате он не будет отличаться от типа TEXT, что по бизнес-логике может быть недопустимым

Особенности «постгрессовых» типов: BYTEA

Значения BYTEA также хранятся в отдельном хранилище TOASTи извлекаются по требованию тем же способом, что и обычные данные. Максимальный объём одной записи – 1 ГБ.

Значение типа BYTEA нельзя читать кусочками, его можно извлечь только целиком. Таким образом, даже если количество строк будет небольшим, для их чтения всё равно может потребоваться значительный объём оперативной памяти. Предположим, что мы хотим извлечь данные некоторых видео, и известно, что каждое из них занимает 300-400 МБ. Поэтому, если мы хотим извлечь 10 видео, то у нас будет минимум 300 МБ х 10 = 3 ГБ.

Особенности «постгрессовых» типов: LARGE OBJECT

Значения large object хранятся в таблице pg_largeobject, для каждой БД она своя. Максимальное число записей на базу данных – 4 млрд (loid в таблице pg_largeobject занимает 32 бита). Максимальный объём одной записи – 4 ТБ. Может показаться, что pg_largeobject – это аналог больших двоичных объектов в Oracle. Значения можно читать кусочками – таким образом, требования к памяти заметно уменьшаются.

Тем не менее, у больших объектов PostgreSQL есть свои нюансы. Во-первых, максимальный размер одной таблицы не может превышать 32 ТБ . Поскольку таблица pg_largeobject одна на всю базу, её объём нужно тщательно отслеживать, т.к. при приближении её размера к максимально допустимому происходит значительное замедление операций вставки. Это связано с тем, что Postgres делает несколько попыток найти свободное место и не находит его. Это первый момент

Второй момент: largeobject является отдельным объектом СУБД, то есть на него выдаются отдельные права. Без них некоторые пользователи не смогут читать и(или) изменять его.

Третий момент заключается в том, что данные pg_largeobject не могут быть переданы с помощью логической репликации. Что это значит? Известно, что в PostgreSQL есть два типа репликации – физическая и логическая. При физической репликации данные передаются и применяются на уровне байтов. При логической репликации анализируются записи журнала предзаписи (WAL), а затем из них извлекаются команды изменения данных, которые можно применить на каком-то другом сервере. С pg_largeobject этот способ не работает, при работе с ним невозможно извлечь эти команды.

Также стоит отметить, что из-за особенностей многоверсионного механизма в Postgres таблицу pg_largeobject нужно очищать от старых версий строк. То есть периодически придётся запускать процедуру очистки для больших объектов vacuumlo. Этот исполняемый файл содержится в стандартном дистрибутиве PostgreSQL. После запуска данная утилита будет проверять, что на тот или иной большой объект ссылается хотя бы одна строка в таблице в базе данных, тип поля которой oid или lo. Если нет, то такой объект будет удалён. Также для таблицы с largeobject можно создать триггер, срабатывающий после обновления и удаления данных. При этом следует проверить его влияние на общую производительность системы в рамках нагрузочного тестирования.

Отметим, что в основной таблице тип этого поля должен быть oid или lo, то есть фактически это номер записи в pg_largeobject. Если посмотреть на содержимое pg_largeobject, то для Postgres тип каждого кусочка BYTEA. Эти кусочки между собой можно склеить, если принято решение прочитать вообще всё. Или же, как уже было сказано, можно читать по частям.

Необходимо помнить, что нельзя обрабатывать pg_largeobject несколькими потоками одновременно при работе утилит pg_dump/pg_restore. Дело в том, что при использовании нескольких потоков каждый поток обрабатывает свою таблицу. А таблица pg_largeobject всего одна на базу данных, и её однопоточная обработка может занять достаточно длительное время. При pg_restore сначала идёт восстановление данных pg_largeobject одним потоком, а потом уже начинается восстановление всех остальных таблиц. Почему? Потому что остальные таблицы могут ссылаться (и часто ссылаются) на значения pg_largeobject. Кроме того, pg_largeobject не секционируется.

Работа с JBPM

Существует ещё один нюанс, связанный с большими объектами в PostgreSQL. Он касается работы JBPM. JBPM работает с двоичными данными с использованием pg_largeobject. Иногда могут возникать ошибки контроля доступа к данным, поэтому следует очень чётко прописать права доступа. Нужно раздать их и проверить в pg_largeobject_metadata и pg_roles, что все права были переданы так, как требуется для работы приложения.

Для JBPM значение поля типа TEXT – идентификатор для обращения к pg_largeobject. Он считает, что это число, и пытается строку привести к числу. Понятно, что если мы попытаемся преобразовать строку «здравствуй, мир» в число, то возникнет ошибка конвертации на уровне JDBC-драйвера. Поэтому для изменения поведения с полем типа TEXT требовалось поменять Java-аннотацию в исходном коде JBPM. Нужно быть внимательным и тщательно следить за ошибками Java, если таковые будут. Сообщения об ошибках от Java не стоит игнорировать.

Вычисляемые столбцы

Вычисляемые столбцы появились в СУБД PostgreSQL начиная с версии 12. До этого приходилось создавать физические поля, что могло увеличить требования к месту на диске для хранения данных. Вычисляемые столбцы можно индексировать, что позволяет ускорить выполнение запросов при работе с ними. Однако, в отличие от СУБД Oracle, вычисляемые столбцы не могут быть ключами секционирования в СУБД PostgreSQL

Внешние структуры данных

Иногда в СУБД Oracle часть данных находится вне базы, но с помощью “внешних таблиц” с ними можно работать так же, как и с данными обычной таблицы. В СУБД PostgreSQL для этого используется механизм Foreign Data Wrapper. В частности, для работы с внешними CSV-файлами используется модуль file_fdw, позволяющий работать с ними как с данными обычной таблицы базы данных

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

Ниже приведены ссылки на различные статьи и мастер-классы конференций pgconf, посвящённых миграции:

Перенос данных с Oracle на PostgreSQL: основные этапы, несовпадающие типы и форматы данных