Поскольку тема «переезда» c СУБД Oracle на СУБД Postgres не теряет актуальности, продолжаем наш цикл о миграции. Это вторая статья о переносе данных из Oracle в Postgres (первая доступна по ссылке). На этот раз мы подробнее остановимся на секционировании и временных таблицах, а такжe рассмотрим существующий инструментарий для конвертации данных и сокращения времени простоя.

IOT-таблицы в Oracle

В СУБД Oracle есть так называемые IOT-таблицы, Index-Organized Tables. В обычных таблицах данные хранятся в произвольном порядке. IOT-таблицы хранят данные в листьях индекса типа B-Tree и при его обходе и извлечении данных последние будут упорядочены. Стоит отметить, что этот порядок сохраняется. После добавления, обновления или удаления записей данные всё равно будут упорядочены.

Также важно, что ключи поиска и искомые данные находятся в одной структуре – это обеспечивает быстрый доступ. Для физического хранения данных индекса не требуются отдельные файлы, а это значит, что уменьшаются требования к объёму дисковой подсистемы.

Часть полей может быть вынесена в область переполнения, что позволит уменьшить размер главного сегмента и увеличить производительность запросов, запрашивающих хранящиеся в нём данные.

Команда CLUSTER в PostgreSQL

Сравним это с тем, что происходит в Postgres. Там существует команда CLUSTER, во время выполнения которой данные таблицы физически упорядочиваются согласно заданному индексу.

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

Можно попытаться воспользоваться модулем pg_repack для упорядочивания таблицы, однако, наибольшее время уйдёт на применение изменений, накопленных в результате работы триггера. При работе модуля pg_repack явно создаётся триггер, перехватывающий изменения. Они сохраняются в таблице-журнале и, в дальнейшем, применяются на основную таблицу.

Может потребоваться периодическое выполнение команды CLUSTER, в противном случае, упорядочивания данных не будет. Также требуется объём дискового пространства не меньше двухкратного размера таблицы и её индексов, поскольку:

• Создаётся копия таблицы с упорядоченными данными.

• Создаются индексы для копии таблицы.

• Удаляется старая версия.

INCLUDE-индексы в PostgreSQL

В ряде ситуаций IOT-таблицу можно заменить INCLUDE-индексом. В СУБД Postgres Pro 9.5 и PostgreSQL 11 появилась возможность добавления столбцов в качестве не ключевых полей индекса. Их значения будут храниться только в листовых узлах индекса. Для их добавления используется следующая конструкция: CREATE INDEX ON TABLE (col_list1) INCLUDE (col_list2). Для части запросов индекс может стать покрывающим, то есть содержать все необходимые для работы запроса столбцы.

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

Секционирование (partitioning)

В СУБД Oracle используется секционирование для:

1. Улучшения доступности данных.

2. Упрощения администрирования объектов с большим количеством строк.

3. Улучшения производительности запросов.

4. Уменьшения очереди на получение блокировок путём обращения запросов к разным секциям.

Что поддерживается в PostgreSQL?

1. Декларативное секционирование типа HASH, LIST, RANGE. Однако, в отличие от Oracle здесь не предусмотрено автоматического создания секций в схемах RANGE и LIST при добавлении данных, ключи секционирования которых отсутствуют в имеющихся секциях. В таких случаях секции придётся создавать вручную.

2. Многоуровневое локальное секционирование. В отличие от Oracle глобальный индекс здесь не поддерживается вне зависимости от уровня вложенности.

   Это связано с особенностями модели многоверсионности в Postgres. В существующей реализации не совсем понятно, как перестраивать такие индексы и как эффективно удалять старые версии строчек с учётом того, что другие процессы будут обращаться к этому индексу, искать данные.

   Скорее всего, решение этой проблемы состоит в усовершенствовании существующего механизма многоверсионности и реализации аналога REDO и UNDO логам в Oracle.

3. Отсечение секций во время планирования и выполнения запроса (partition pruning). Например, при соединении двух таблиц одна из них может содержать секции. При анализе плана выполнения такой операции можно увидеть, что СУБД не обращалась к определённым секциям. Это хорошо, потому что, если обращаться ко всем секциям и искать, есть там данные или нет, это приводит к снижению производительности запроса.

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

Дополнительные ограничения секционирования PostgreSQL

Помимо указанных выше ограничений в СУБД PostgreSQL нет шаблонов подсекций (subpartition template). Что это означает? Допустим, в СУБД Oracle есть некоторая секционированная по списку таблица. В свою очередь, её секции разбиты на подсекции – например, по дате. Предположим, добавляется значение, которого не было в списке, и автоматически создаётся новая секция. Для этой новой секции автоматически создаются подсекции при добавлении новых диапазонов. К сожалению, в PostgreSQL пока такого нет. Поэтому придётся предварительно создавать секции, в частности, по расписанию.

Ниже приведён пример использования этого механизма в СУБД Oracle:

CREATE TABLE subpart_test (
    id NUMBER,
    dt DATE
)
PARTITION BY RANGE(id)
INTERVAL (1)
SUBPARTITION BY RANGE(dt)
SUBPARTITION TEMPLATE (
    SUBPARTITION SUBPART_BEFORE_2016 VALUES LESS THAN (TO_DATE('2016-01-01', 'YYYY-MM-DD')),
    SUBPARTITION SUBPART_2016 VALUES LESS THAN (TO_DATE('2017-01-01', 'YYYY-MM-DD')),
    SUBPARTITION SUBPART_2017 VALUES LESS THAN (TO_DATE('2018-01-01', 'YYYY-MM-DD')),
    SUBPARTITION SUBPART_AFTER_2017 VALUES LESS THAN (MAXVALUE)
)
(
    PARTITION PART_1 VALUES LESS THAN (2)
);

SELECT table_name
     , partition_name
     , subpartition_name
  FROM USER_TAB_SUBPARTITIONS
 WHERE table_name = 'SUBPART_TEST';
 
TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME
SUBPART_TEST    PART_1          PART_1_SUBPART_BEFORE_2016
SUBPART_TEST    PART_1          PART_1_SUBPART_2016
SUBPART_TEST    PART_1          PART_1_SUBPART_2017
SUBPART_TEST    PART_1          PART_1_SUBPART_AFTER_2017

INSERT INTO SUBPART_TEST
VALUES (1, TO_DATE('2016-10-01', 'YYYY-MM-DD'));

INSERT INTO SUBPART_TEST
VALUES (1, SYSDATE);

INSERT INTO SUBPART_TEST
VALUES (3, SYSDATE);

SELECT u.*
  FROM USER_TAB_SUBPARTITIONS u
 WHERE u.table_name = 'SUBPART_TEST';

TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME
SUBPART_TEST    PART_1          PART_1_SUBPART_BEFORE_2016
SUBPART_TEST    PART_1          PART_1_SUBPART_2016
SUBPART_TEST    PART_1          PART_1_SUBPART_2017
SUBPART_TEST    PART_1          PART_1_SUBPART_AFTER_2017
SUBPART_TEST    SYS_P45068      SYS_SUBP45064
SUBPART_TEST    SYS_P45068      SYS_SUBP45065
SUBPART_TEST    SYS_P45068      SYS_SUBP45066
SUBPART_TEST    SYS_P45068      SYS_SUBP45067

Из вывода видно, что была создана новая секция и её подсекция.

Не поддерживается и секционирование по виртуальному столбцу. В Oracle 11 такая возможность появилась, но в PostgreSQL её по-прежнему нет.

Ключ секционирования в PostgreSQL должен быть частью первичного ключа или уникального индекса, об этом тоже стоит помнить.

Рекомендуем ознакомиться со следующей статьёй в блоге компании Postgres Professional, посвящённой секционированию.

Расширение pg_pathman

В компании Postgres Professional был разработан модуль pg_pathman: Он появился задолго до внедрения декларативного секционирования в основную ветку PostgreSQL. Что он умел и умеет? Многое – в частности, автоматически создавать секции по диапазону. Стоит отметить, что он работает на «ванильной» СУБД PostgreSQL до версии 13 включительно. Для версии 14 этот модуль не поддерживается. Однако в дальнейших планах компании есть перенос особенностей pg_pathman в PostgreSQL, чтобы улучшить производительность и поддержку секционирования. Хочется, чтобы этот механизм для решения бизнес-задач могли использовать все.

Временные таблицы в СУБД PostgreSQL

В Oracle есть глобальные временные таблицы, но, начиная с 18-й версии Oracle, также появились и сессионные.

В СУБД PostgreSQL временные таблицы являются сессионными, здесь можно использовать один из трёх режимов:

1. on commit drop. При завершении транзакции таблица удаляется, что может привести к многочисленным вопросам, связанным с производительностью. Дело в том, что при удалении таблицы возникают сообщения об инвалидации кешей. У каждого процесса, обслуживающего клиентские запросы, есть свой локальный кеш. Он применяется для хранения различных данных, в частности, статистик для планировщика, копий определённых объектов словаря данных. Это было сделано для того, чтобы словарь данных не стал узким местом.

   Тем не менее, при таком подходе именно словарь данных и превращается в «бутылочное горлышко», потому что перед обращением в свой кеш процесс проверяет, нет ли сообщений инвалидации. Если такое сообщение есть, процесс будет перечитывать данные из системного каталога. А поскольку системный каталог является достаточно популярным местом, большинство процессов будут просто ждать, когда же им разрешат взять блокировку и прочитать необходимые данные. Поэтому режим on commit drop лучше не использовать.

2. on commit delete rows. Предлагается использовать следующую конструкцию: CREATE TABLE IF NOT EXISTS ON COMMIT DELETE ROWS. Таким образом, если таблица уже существует, для каждой транзакции она заново создаваться не будет. Когда транзакция завершается, строки удаляются. Получается таблица, куда можно добавлять данные, изменять их, сохранять с их помощью промежуточный результат вычислений. Этот способ наиболее предпочтительный.

3. on commit preserve rows. При завершении транзакции строки сохраняются. Если их нужно сохранить между транзакциями в рамках одной сессии, то такой режим тоже возможен.

В чём польза временных таблиц?

По ним можно собрать статистику для планировщика. Если у него будет неактуальная статистика, то с большой долей вероятности выберется некорректный план выполнения. Это может привести к деградации производительности запросов.

Для автоматического сбора статистики по временным таблицам существует модуль online_analyze.

Расширение pg_variables

При выполнении прикладного кода в СУБД Oracle часто возникает необходимость обращения к данным, срок хранения которых совпадает с временем работы сессии

В СУБД PostgreSQL для этих целей подойдёт модуль pg_variables, разработанный компанией Postgres Pro

В память серверного процесса помещаются данные, срок хранения которых совпадает с временем работы сессии. Поскольку при этом данные объектов БД не меняются, то данный модуль возможно использовать на ведомом сервере (реплике). Создание и удаление таблиц – изменения в системном каталоге, такие, как pg_class, pg_attribute, pg_index и другие. Поэтому на ведомом сервере нельзя создавать временные таблицы.

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

Использование глобального словаря данных

Также для хранения промежуточной информации можно использовать глобальный словарь данных языков программирования PL/Perl или PL/Python. С этим тоже нужно быть аккуратными, потому что на каждый процесс будет запущен свой отдельный интерпретатор Perl или Python. Нужно понимать, что интерпретатор тоже требует ресурсов, в частности, памяти.

Чем заменить Oracle RAC и Data Guard?

Коснёмся кратко Real Application Cluster и решений для обеспечения высокой отказоустойчивости. В СУБД PostgreSQL Real Application Cluster не реализован, и решение о его замене принимается исходя из бизнес-требований.

Data Guard – это решение, обеспечивающее отказоустойчивость в Oracle. Его аналогом в СУБД PostgreSQL является потоковая репликация, при которой ведомый сервер (реплика) используется в качестве горячего резерва (hot standby)

Для автоматического переключения с ведущего на ведомый сервер используются следующие инструменты:

1. Corosync

2. Pacemaker

3. Patroni

4. Stolon

Варианты переноса схемы данных

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

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

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

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

Оценка стоимости миграции

Когда становится понятно, как миграция будет выглядеть на стороне конечной СУБД («приёмника»), можно провести оценку стоимости. Сюда нужно закладывать следующее:

1. Создание демонстрационного стенда.

2. Стоимость непосредственной разработки.

3. Стоимость тестирования, отладки и сопровождения.

4. Лицензии на альтернативную СУБД и её техническая поддержка.

Этапы конвертации данных

1. Создание стенда с установленной и настроенной целевой СУБД.

2. Выбор и настройка средств конвертации данных.

3. Выполнение конвертации схемы и данных.

4. Проверка корректности выполнения пункта 3: все таблицы перенесены, никакие поля не потеряны, данные тоже перенесены.

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

6. Выбор стратегии переноса для обновляемых данных в исходной СУБД.

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

Инструменты конвертации данных

Поговорим об инструментах для конвертации данных.  Открытая бесплатная утилита ora2pg позволяет выполнить необходимые действия. Она может создать проект миграции. Также она умеет сканировать исходную СУБД и извлекать оттуда описания структур данных и сами данные (при необходимости их можно сохранять в промежуточные файлы). Кроме того, ora2pg может генерировать команды для создания структур данных в целевой базе.

Достоинства ora2pg

Главное достоинство данной утилиты – настройка списков объектов для переноса. Можно указать схемы и таблицы, подлежащие переносу. Можно даже перенести некоторую таблицу, но не переносить часть индексов. Это достигается за счёт регулярных выражений. Конечно, эта гибкость не совсем бесплатная, потому что ora2pg использует не расширенный механизм регулярных выражений, а базовый. Это может затормозить извлечение структур данных, но, тем не менее, такая возможность есть.

Ещё с помощью ora2pg можно сопоставлять типы данных исходной и конечной СУБД («источника» и «приёмника»). Разумеется, ora2pg даёт возможность перенести данные напрямую в целевую СУБД, либо в какие-то промежуточные файлы, если это нужно для решения определённой задачи.

Недостатки ora2pg

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

Стоит помнить, что обычно ora2pg преобразует тип NUMBER в NUMERIC, однако, часто в NUMBER хранятся целые числа. При анализе значений вполне может оказаться, что там либо 0, либо 1. Тогда это логическое значение, и в СУБД Postgres оно будет соответствовать типу bool. Кроме того, значения полей могут не превысить максимальных значений типов smallint, int. В результате, в конфигурационном файле ora2pg приходится прописывать исключения для ряда столбцов таблиц.

Ниже показана структура проекта в ora2pg.

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

Инструмент Pentaho Kettle

Название этой утилиты – Pentaho Kettle. Это ETL-инструмент, позволяющий разрабатывать различные сценарии извлечения, обработки и сохранения данных. Также с помощью Pentaho kettle можно внедрять логику, реализованную на языке Java, в ETL-процесс. Кроме того, возможны запуски сценариев по расписанию и взаимодействие с различными СУБД.

Совместимость с Apache Kafka

Pentaho kettle может взаимодействовать с Apache Kafka. Был такой случай, когда разработчики реализовали перехват изменений на основе анализа данных LogMiner, вся информация записывалась в Apache Kafka. Дальше использовался компонент Pentaho kettle для извлечения данных, выполнялся их анализ, в зависимости от результата запускалось то или иное преобразование. Таким путём данные попадали в конечную СУБД («приёмник»), PostgreSQL.

Преимущества Pentaho Kettle

Стоит сказать несколько слов о преимуществах Pentaho Kettle. В нём можно оформлять действия в виде подзадач, там реализовано своеобразное процедурное программирование. Есть возможность разрабатывать и отлаживать сценарии с использованием графического интерфейса.

Инструмент позволяет сохранять часть данных в таблицы-журналы при возникновении ошибки во время переноса, а потом можно посмотреть, что именно не получилось. При этом большая часть данных всё равно будет перенесена в конечную СУБД («приёмник»). К сожалению, эта возможность доступна не для всех компонентов, но для большинства она работает.

Ещё в Pentaho Kettle можно количественно проверять корректность переноса данных, и это возможно благодаря метрикам. При использовании ora2pg придётся обратиться к СУБД Oracle и СУБД Postgres, проверить и сравнить количество данных. Pentaho Kettle изначально фиксирует, сколько данных было извлечено из СУБД Oracle и сколько данных было записано в СУБД Postgres. Таким образом, становится ясно, полностью выполнена запись или всё же нет.

Помимо этого, в Pentaho Kettle есть компонент для расчёта контрольных сумм, и в нём можно указать поля, на основе которых вычисляется контрольная сумма. Таким образом, можно делать ещё и качественную проверку при миграции данных.

Пример задания Pentaho Kettle

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

• Start – cmaep1

• Start – cmaep2

• Start – cmaep3

Здесь идёт речь о секционированной таблице. В ней 16 секций, которые обрабатываются в три потока, причём независимо друг от друга. Из этого примера видно, что Pentaho kettle может обрабатывать несколько таблиц одновременно, при этом каждая таблица тоже может обрабатываться несколькими потоками.

В примере выше 7 потоков читают данные из СУБД Oracle и 5 потоков записывают данные в СУБД Postgres. Компонент ora_cm_account_entry читает данные из СУБД Oracle указанным запросом и количеством потоков для одновременного чтения. pg_cm_account_entry записывает данные в СУБД PostgreSQL командой COPY, что значительно уменьшает время переноса.

Сравнение ora2pg vs Pentaho kettle

В таблице ниже представлен итог сравнения вышеописанных средств конвертации данных.

Интеграция со смежными системами: инструменты

Стоит упомянуть и такой аспект, как интеграция со смежными системами. Иногда возникает базовая потребность из Oracle обратиться в Postgres. Это можно сделать через dblink, который можно создать благодаря технологии postgres-odbc. В доступной по ссылке статье описано, как поставить postgres-odbс на стороне Oracle и как использовать его для подключения к Postgres.

Иногда возникает необходимость обращения в обратном направлении, из СУБД Postgres к СУБД Oracle. Для этой цели можно использовать обёртку сторонних данных oracle_fdw.

Стоит понимать, что этот модуль использует «оракловый» клиент, поставляемый в виде уже собранных под конкретную архитектуру библиотек. Есть rpm-пакеты, архивы, cозданные на основе стандартной архитектуры. Не факт, что этот клиент сработает для архитектуры, отличной от стандартной. Стоит отметить, что на российских ОС, в частности, Astra Linux Смоленск и Орёл этот модуль работает.

Cокращениe времени простоя

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

Первый подход подразумевает использование журналов материализованных представлений. В Oracle есть возможность обновлять материализованные представления не полностью, а частично, как раз благодаря этим журналам. Существует решение, которое извлекает данные из этих журналов и передаёт в очередь сообщений, например, Apache Kafka.

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

Третий подход наиболее популярен. Это применение Log Miner для анализа redo и archive журналов. Есть специальный коннектор, его можно найти в составе Debezium.

Ранее приводился пример, в котором разработчики посмотрели реализацию работы с Log Miner в Debezium и реализовали то же самое в своём приложении. Потом шла запись данных в Apache Kafka, откуда они извлекались с помощью Pentaho Kettle и сохранялись в конечной СУБД («приёмнике»).

О коде приложений - в другой раз...

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