VTB 10 июн в 11:00

Как мы мигрировали из Oracle в PostgreSQL

Средний

50 мин

12K

Кейс

Привет, Хабр! Я Владимир Хаймин, эксперт по системам управления базами данных PostgreSQL в ВТБ. В последнее время одним из наиболее распространенных способов миграции стала миграция при помощи свободного инструмента ora2pg. Однако многие отмечают его крайне низкую производительность, особенно если речь идет о базах Oracle размером под 1Тб и выше. Как ускорить миграцию и сократить время простоя при переносе проектов при помощи только oracle_fdw, либо в сочетании с ora2pg я расскажу далее.

Из важного: понятие "виртуального секционирования" для внешних таблиц, суть которого - разбиение больших таблиц на батчи для ускорения миграции параллельными потоками. Также подтвержден опыт коллег из FORS, где перенос данных в целевые таблицы без индексов и последующее их создание может быть в десятки раз быстрее переноса данных в таблицы с индексами. И в целом формализован подход к миграции больших данных.

Что такое крупная БД?
Планирование и реализации миграции из Oracle в PostgreSQL
Этапы миграции на ПРОМ системы
Проблемы миграции крупных БД Ora2Pg
Типовые методы ускорения миграции Ora2Pg
Что будет если мы будем напрямую перекачивать данные из источника Oracle в PostgreSQL используя SQL/MED?
Качество трансформации таблиц по типам
Ограничения
Более детальная автоматизация переноса структуры БД
Выводы по инструментам миграции

Рекомендации по ускорению миграции данных
Отключение архивного режима
Загрузка данных без индексов и ограничений, и с индексами и ограничениями
Виртуальное секционирование
Отключение реплик + пересоздание их после загрузки данных

Практика миграции
Установка и настройка расширения oracle_fdw
Пререквизиты для расширения oracle_fdw
Компиляция oracle_fdw
Настройка oracle_fdw
Создание расширения
Создание внешнего сервера и маппинг пользователей
Импорт внешней схемы и пробные миграции данных для определения соответствия структуры таблиц

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

Итоги и результаты

Общий обзор распространенных методов и принцип выбора средств миграции

Что такое крупная БД?

Точных определений в настоящее время нет, так как со временем изменяются параметры и производительность оборудования, технологии хранения, скорости изменения и извлечения данных. И те размеры, и сложность БД которые 5, 10, 15 лет назад считались крупными в настоящее время перешли в разряд средних или малых.

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

резервного копирования и восстановления;
обслуживания БД (реиндексация, вакуум).

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

Условной границей на текущий момент для PostgreSQL можно считать размер БД от 1Тб примерно до 12Тб. Далее идут БД сверхкрупных размеров, для эксплуатации которых используются специальные методы масштабирования, шардирования по распределению данных по цепочке серверов, однако, которые с точки зрения прикладного ПО выглядят как единое целое. Шардирование может быть, как прикладным, так и на уровне БД.

Планирование и реализации миграции из Oracle в PostgreSQL

Общий подход к миграции и какие этапы необходимо предусмотреть при его реализации можно описать так:

Оценка сложности миграции. В настоящее время эта операция хорошо автоматизирована. Существует набор скриптов, который запускается на Oracle и выводит отчет. Он представляет собой опросник по тому функционалу, который используется в БД Oracle и может вызвать затруднения при переносе в PostgreSQL ввиду отсутствия либо необходимости использования дополнительных расширений и средств в целевой БД PostgreSQL. Опросник состоит из более, чем 100 пунктов. Он содержит базовые вопросы по наиболее крупным объектам БД, использованию и специфике их хранения, размерам LOB, специфичного функционала, которые есть в Oracle и отсутствует в PostgreSQL, например, глобальные временные таблицы, код Java, пакеты вроде DMBS, UTL и т.д. Здесь нам нужно понять, требуется ли это в PostgreSQL или же можно часть кода модифицировать или вовсе обойтись без него, вынеся часть кода на сторону прикладного ПО.
Разворачивание тестового стенда с исходной копией Oracle с достаточной точностью по данным их размеру, и целевой БД PostgreSQL;
Выбор средства миграции. Это могут быть как коммерческие проекты вроде Diasoft Migrator, Ispirer или свободные продукты, как Ora2pg. Небольшие БД можно мигрировать даже при помощи DBeaver;
Тестовая миграция. Оценка времени миграции, простоя системы и качества миграции. Отладка результата миграции в части DDL до того уровня, что его результат был бы для нас, приемлем. Также предварительная оптимизация БД с точки зрения производительности, так как оптимизатор Oracle работает иначе, чем оптимизатор PostgreSQL. На этом этапе у нас должна получиться структура БД пригодная для продуктивной системы, но без промышленных данных.

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

Этапы миграции на ПРОМ системы

Настройка целевой БД и средств миграции;
Миграция схемы БД, которая заранее подготовлена при тестовой миграции;
Начало простоя системы;
Остановка прикладного ПО на исходной БД Oracle;
Миграция данных. Это наиболее критичный этап миграции, занимающий более 90% времени простоя. Чем быстрее мы мигрируем данные, тем лучше;
Переключение прикладного ПО на целевую БД PostgreSQL;
Конец простоя системы.

Проблемы миграции крупных БД Ora2Pg

В рамках пилотного проекта нам удалось изучить работу многих средств миграции как свободных, так и коммерческих. И те, и другие средства имеют свои особенности относительно качества трансформации структур данных, кода функций и процедур, однако общая проблема всех средств – это низкая скорость миграции именно данных. Причем ora2pg имеет самую низкую скорость миграции данных.

Как отмечают многие, миграция именно данных имеет крайне низкую скорость, особенно если речь идет о LOB объектах, и может доходить до нескольких сотен строк в секунду. В действительности важнее не столько количество строк в секунду, сколько реальный объем данных в этой строке. Значения в полях bytea, text могут занимать до 1 GB, соответственно 1000 строк может занимать уже 1 ТВ данных. Поэтому судить о скорости миграции в единице строк в секунду не совсем верно, важнее реальный объем данных в строках.

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

Типовые методы ускорения миграции Ora2Pg

Параллелизм.

Выгрузка:
nohup ora2pg -j 2 -J 12 -t INSERT -o data.sql -b ./data -c ./config/ora2pg.conf
Oracle вычитывается в 12 потоков, запись идет в два, еще два процесса занимает ora2pg.

Загрузка:
nohup /postgres/ora2pg/client/import_all.sh -d client -o postgres -U postgres -a -y -I -P 8
Это позволит ускорить загрузку за счет параллелизма в разы, но промежуточный дамп все еще нужен.

Переливка данных из БД источника в БД назначения с использованием поддержки oracle_fdw в ora2pg:
FDW_SERVER orcl $ ora2pg -t COPY -c config/ora2pg.conf [========================>] 7/7 tables (100.0%) end of scanning. [========================>] 7/7 tables (100.0%) end of table export NOTICE: schema "ora2pg_fdw_import" does not exist, skipping [========================>] 215/215 rows (100.0%) on total estimated data (2 sec., avg: 107 recs/sec)

Здесь достигается ускорение примерно в 2,5 раза. Но пока дамп еще нужен.

Что будет если мы будем напрямую перекачивать данные из источника Oracle в PostgreSQL используя SQL/MED?

Попробуем избежать промежуточного слоя дампа, и данные будем переливаться из одной БД в другую напрямую.

Для этого хорошо подходит расширение oracle_fdw.
Установка требует компиляции и предварительной установки пакетов:
oracle-instantclient19.10-sqlplus-19.10.0.0.0-1.x86_64
oracle-instantclient19.10-basic-19.10.0.0.0-1.x86_64
oracle-instantclient19.10-devel-19.10.0.0.0-1.x86_64
oracle-instantclient19.10-tools-19.10.0.0.0-1.x86_64

Создаем расширение oracle_fdw и настраиваем параметры подключения к БД – источнику Oracle:

CREATE EXTENSION oracle_fdw;CREATE SERVER k3test_oracle FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//oracle.host:1521/k3test');CREATE USER MAPPING FOR postgres SERVER k3test_oracle OPTIONS (user 'or2pgmigration', password '*******');

Создаем схему приемник pm и схему источник pm_fdw:
create schema pm;
create schema pm_fdw;

Импортируем схему PM в базе Oracle в схему pm_fdw в базе PostgreSQL:

IMPORT FOREIGN SCHEMA "PM" FROM SERVER k3test_oracle INTO pm_fdw;

После этого схема pm_fdw содержит все таблицы и данные в исходной БД Oracle.

Миграция DDL схемы

Для одной таблицы:

create table pm.table1 as
  select * from
  pm_fdw.table1
 where false;

Для схемы целиком (с оговорками):

do $$
       declare
             schema_from varchar = 'pm_fdw';
             schema_to varchar = 'pm';
             r record;
begin
       for r in select table_name from information_schema."tables" where table_schema = schema_from
       loop
         EXECUTE 'create table '||schema_to||'.'||r.table_name||' as select * from '||schema_from||'.'||r.table_name||'where false';
       end loop;
end
$$

Миграция данных

Для одной таблицы:

insert into table pm.table1 as
  select * from
pm_fdw.table1;

Для схемы целиком:

do $$
       declare
             schema_from varchar = 'pm_fdw';
             schema_to varchar = 'pm';
             r record;
begin
       for r in select table_name from information_schema."tables" where table_schema = schema_from
       loop
         EXECUTE 'insert into '||schema_to||'.'||r.table_name||' select * from '||schema_from||'.'||r.table_name;
       end loop;
end
$$

Результат: 1Гб за 1 мин 45 сек против 44 минут в ora2pg без оптимизаций ускорения.

Качество трансформации таблиц по типам

Оно достаточно высоко и более интеллектуально в частности по типу number, где для маппинга используется по умолчании тип int8, что на этапе загрузки реальных данных типа float может приводить к ошибкам. Ниже примеры трансформации.

Oracle:

CREATE TABLE "PM"."EVENTSNAPSHOT"
   (	"ID" VARCHAR2(36 CHAR) NOT NULL ENABLE,
	"CITIZENID" NUMBER NOT NULL ENABLE,  
	"DTC" TIMESTAMP (6) DEFAULT systimestamp NOT NULL ENABLE,
	"DTM" TIMESTAMP (6) DEFAULT systimestamp NOT NULL ENABLE,
	"BODYTYPE" VARCHAR2(200 CHAR) NOT NULL ENABLE,
	"BODY" CLOB,
	"EXTENDEDDATA" BLOB,
	"PARTITION_DATE" DATE DEFAULT trunc(systimestamp) NOT NULL ENABLE
   )

PostgreSQL:

CREATE TABLE pm_fdw.eventsnapshot (
	id varchar(36) NOT NULL,
	citizenid numeric NULL,
	dtc timestamp NOT NULL,
	dtm timestamp NOT NULL,
	bodytype varchar(200) NOT NULL,
	body text NULL,
	extendeddata bytea NULL,
	partition_date timestamp(0) NOT NULL
)

Мы видим, что:

"ID" VARCHAR2(36 CHAR) -> id varchar(36) "CITIZENID" NUMBER -> citizenid numeric NULL "BODY" CLOB -> body text "EXTENDEDDATA" BLOB -> extendeddata bytea

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

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

Ограничения

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

На практике схема миграции может выглядеть так:

Вендор уже имеет готовую схему БД PostgreSQL.

Если этого нет, то:

перенос структуры таблиц и данных (при помощи oracle_fdw);
перенос индексов, ограничений, и последовательностей (при помощи ora2pg);
перенос внешних ключей (при помощи ora2pg);
перенос представлений, триггеров, функций, процедур и пакетов (при помощи ora2pg).

Более детальная автоматизация переноса структуры БД

Если oracle_fdw хорош для быстрого переноса данных, почему бы его не использовать и для переноса метаданных?

ora_migrator – это надстройка Cybertech для oracle_fdw. Разработчик — тот же автор, что и oracle_fdw.

Шаги миграции при использовании ora_migrator:

oracle_migrate_mkforeign: создание целевых схем и внешних таблиц для данных
oracle_migrate_tables: создание таблицы и перенос данных с возможностью распараллеливания
oracle_migrate_functions: миграция функций и процедур
oracle_migrate_triggers: миграция триггеров
oracle_migrate_views: миграция представлений
oracle_migrate_constraints: индексы и ограничения

Также есть функционал, позволяющий минимизировать простой до максимума путем накопления изменений в исходной БД с последующим переносом в БД назначения.

Выводы по инструментам миграции

Использование расширения oracle_fdw — это более нативный инструмент для миграции данных из Oracle. Причем это двунаправленный канал. Вы можете переливать данные как из Oracle → PostgreSQL, так и наоборот PostgreSQL → Oracle.

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

Oracle_fdw можно использовать совместно с ora2pg для того, что оно напрямую делать не может, то есть — для создания индексов, ограничений, триггеров, последовательностей и функций, но сами таблицы нужно переносить при помощи oracle_fdw.

Также для сокращения времени простоя и переноса метаданных вместо ora2pg может быть использовано расширение ora_migrator.

Ниже будут рассмотрены наиболее эффективные методы, позволяющие ускорить перенос данных.

Рекомендации по ускорению миграции данных

На текущий момент существуют общеизвестные рекомендации по ускорению загрузки данных:

https://postgrespro.ru/docs/postgresql/14/performance-tips

Уровень БД (postgresql.conf):
wal_buffers = '16MB‘ min_wal_size = '1GB' effective_cache_size = '12GB' maintenance_work_mem = '1GB' work_mem = '1GB‘ bgwriter_lru_maxpages = 0 max_wal_senders =0 wal_level=minimal fsync=off max_wal_size=16GB archive_mode=off

Таблицы:
alter table … set unlogged; сreate table test(id integer, bigtext text compression pglz/lz4) With (fillfactor=100, toast_tuple_target=2040); alter table test alter column bigtext set storage plain| external|extended|main;

Однако, на практике манипулирование всеми этими параметрами давало изменение скорости загрузки данных +/- 1–2%. Что же на самом деле влияет на ускорение загрузки данных? Экспериментально получены следующие наиболее эффективные методы.

Отключение архивного режима

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

archive_command: /bin/true
или
archive_mode: false

По окончании миграции данных архивный режим можно включить обратно.
archive_command: test ! -f /pg_walarchive/%f && cp %p /pg_walarchive/%f
или
archive_mode: true

Изменение через archive_command позволит изменить режим архивации без перезагрузки БД, в то время как через archive_mode = on/off требуется перезагрузка.

Загрузка данных без индексов и ограничений, и с индексами и ограничениями

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

Так, например, в чистую таблицу вы можете залить 1 000 000 записей за 40 секунд, но если эта таблица уже содержит около 500 000 000 записей около 10 индексов, то скорость загрузки того же 1 000 000 записей может составить от 20 до 40 мин. Пример будет продемонстрирован ниже.

Типичный пример подобной таблицы:

CREATE TABLE sbp.fp (
	id_fp bigserial NOT NULL,
	id_cfp int8 NOT NULL,
	direction varchar(16) NULL,
	status int2 NOT NULL DEFAULT 0,
	out_guid varchar(64) NULL,
	in_guid varchar(64) NULL,
	qr_code varchar(32) NULL,
	date_create timestamp(0) NOT NULL DEFAULT now(),
	date_frombank timestamp(0) NULL,
	date_tobank timestamp(0) NULL,
	date_fps timestamp NULL,
	sender_id varchar(64) NULL,
	sender_bank_id varchar(64) NULL,
	sender_fio varchar(512) NULL,
	sender_address varchar(512) NULL,
	sender_inn varchar(12) NULL,
	sender_account varchar(20) NULL,
	sender_money_kat varchar(64) NULL,
	fps_id varchar(64) NULL,
	receiver_bank_id varchar(64) NULL,
	tsp_name varchar(2000) NULL,
	tsp_full_name varchar(2000) NULL,
	tsp_id varchar(64) NULL,
	operation_id varchar(64) NULL,
	code_answer varchar(64) NULL,
	account varchar(25) NULL,
	memo varchar(2000) NULL,
	summa numeric(15,2) NULL,
	receiver_inn varchar(12) NULL,
	state_fps varchar(64) NULL,
	scheme_fps varchar(64) NULL,
	opertype_fps varchar(64) NULL,
	messagetype_fps varchar(64) NULL,
	sendertype_id varchar(64) NULL,
	receivertype_id varchar(64) NULL,
	sender_bik varchar(9) NULL,
	receiver_bik varchar(9) NULL,
	date_oper timestamp(0) NULL,
	date_operation_fps timestamp NULL,
	date_value_fps timestamp(0) NULL,
	sign varchar(2000) NULL,
	black_payment_id varchar(2000) NULL,
	category_amount varchar(64) NULL,
	id_fil int8 NULL,
	id_doc int8 NULL,
	branch_doc varchar(256) NULL,
	message text NULL,
	sender_guid varchar(64) NULL,
	receiver_fio varchar(512) NULL,
	receiver_address varchar(512) NULL,
	receiver_id varchar(64) NULL,
	receiver_doc_id varchar(64) NULL,
	receiver_type_doc varchar(64) NULL,
	receiver_ram varchar(512) NULL,
	control_value varchar(2000) NULL,
	sbp_refer_id varchar(64) NULL,
	sender_black_payment_id varchar(64) NULL,
	legal_id varchar(32) NULL,
	merchant_id varchar(32) NULL,
	kind_income varchar(32) NULL,
	visa_spm varchar(512) NULL,
	tax_amount numeric(15,2) NULL,
	CONSTRAINT pk_fp PRIMARY KEY (id_fp, date_create)
)
PARTITION BY RANGE (date_create);
CREATE INDEX client_fp_i ON ONLY sbp.fp USING btree (id_cfp);
CREATE INDEX dcst_fp ON ONLY sbp.fp USING btree (date_create, status);
CREATE INDEX fp_id_doc_i ON ONLY sbp.fp USING btree (id_doc);
CREATE INDEX fp_sbp_id ON ONLY sbp.fp USING btree (sbp_refer_id);
CREATE INDEX "i$fp$1" ON ONLY sbp.fp USING btree (operation_id, messagetype_fps, code_answer);
CREATE INDEX "i$fp$3" ON ONLY sbp.fp USING btree (messagetype_fps, date_create);
CREATE INDEX "i$fp$4" ON ONLY sbp.fp USING btree (messagetype_fps, status, code_answer, date_create);
CREATE INDEX "i$fp$6" ON ONLY sbp.fp USING btree (messagetype_fps, code_answer, status, date_create);
CREATE INDEX inguid_fp ON ONLY sbp.fp USING btree (in_guid);
CREATE INDEX operid_fp ON ONLY sbp.fp USING btree (operation_id);
CREATE INDEX outguid_fp ON ONLY sbp.fp USING btree (out_guid);
CREATE INDEX qrcode_fp ON ONLY sbp.fp USING btree (qr_code);
CREATE INDEX rb_fp ON ONLY sbp.fp USING btree (id_doc, branch_doc);
CREATE INDEX sender_guid_fp ON ONLY sbp.fp USING btree (sender_guid);

Если мы создадим пустую таблицу на основе структуры таблицы Oracle:

create table sbp.fp_tmp as select * from sbp_fdw.fp where false;

И затем выполним загрузку:

 time psql -h `hostname` sbp -c 'insert into sbp.fp_tmp select* from sbp_fdw.fp limit 1000000;'
INSERT 0 1000000

real    0m44.588s
user    0m0.031s
sys     0m0.016s

То она выполнится примерно за 40 сек.

Если же таблица уже содержит около миллиарда записей и все индексы выше, то получим загрузку того же объема данных примерно за 20 мин.

time psql -h `hostname` sbp -c 'insert into sbp.fp select* from sbp_fdw.fp limit 1000000;'
INSERT 0 1000000

real    21m14.462s
user    0m0.021s
sys     0m0.016s

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

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

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

Виртуальное секционирование

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

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

Основная проблема при извлечении секции со смещением состоит в том, что если использовать LIMIT + OFFSET при обращении к оракловой таблице через Oracle_fdw, то с каждым следующим батчем данные будут возвращаться все медленнее и медленнее. Это происходит по причине FULL SCAN на стороне Oracle.

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

Основные принципы виртуального секционирования:

В определении таблицы используется первичный ключ Oracle, даты или другие критерии, определяемые архитектурой данной таблицы. В некоторых случаях требуется создать индекс специально для миграции.
Рекомендуемый размер батча 1–10 млн строк. Главное, чтобы батч переносился максимум за 10-20 минут. Если размер батча слишком большой – может быть такая ошибка: ORA-04030: out of process memory when trying to allocate 232 bytes (session heap,lob ctl struct).
Запрос в определении секции должен выполняться на стороне Oracle со всеми возможными оптимизациями по скорости извлечения данных.
Пример для 4,5 миллиардов записей с размером батча 10 млн и числом секций 450. Также нужно проконтролировать MIN(ID) и MAX(ID), чтобы они входили в разбиение по батчам. В нашем случае MIN(ID) = 12001 MAX(ID) = 4 513 765 003. То есть номера батчей размером 10 000 000 строк от 0 до 450. Аналогично нужно сделать если разбиение по датам или времени. Ниже пример такой секции с номером 102 (для n-ной виртуальной секции 102 и в номере секции и условиях отбора WHERE нужно заменить на n):

create foreign table expch_fdw.journal_record_102 (
	id numeric(19) not null,
	journal_id numeric(19) not null,
	level_code int8 not null,
	created timestamp null,
	ref_code varchar(68) null,
	source_code varchar(64) null,
	subj_id numeric(19) null,
	description text null,
	group_id numeric(19) null,
	properties text null,
	error_code int8 null ) server ltexad_db5212xp_oracle options (table '(
select
	ID,
	JOURNAL_ID,
	LEVEL_CODE,
	CREATED,
	REF_CODE,
	SOURCE_CODE,
	SUBJ_ID,
	DESCRIPTION,
	GROUP_ID,
	TO_CLOB(PROPERTIES),
	ERROR_CODE
from
	JOURNAL_RECORD
where
	ID >= 102 * 10000000
	and ID < 102 * 10000000 + 10000000);

После создания таких виртуальных секций их можно разбить по потокам. Количество потоков определяется физическими ресурсами системы на сервере БД. И оно должно быть разумным, необходимо оставлять ресурсы и для серверных процессов. Так, например, для 64 ядер разумно разбить заливку на 55 потоков.

Все батчи распределяются между потоками и содержат такие прямые запросы:

insert into expch.journal_record select * from expch_fdw.journal_record_0 on conflict do nothing;
insert into expch.journal_record select * from expch_fdw.journal_record_1 on conflict do nothing;
…
insert into expch.journal_record select * from expch_fdw.journal_record_450 on conflict do nothing;

Опция ...on conflict do nothing нужна, если в таблице содержится хотя бы один уникальный индекс, например в составе первичного ключа для того чтобы не возникали блокировки при одновременной заливке данных разными потоками в одну и ту же таблицу.

При 55 потоках каждый поток выполнит 8 переносов по 10 млн. записей.
Время выполнения переноса 10 миллионов записей:

$ time psql -p 6432 creo -c "insert into expch_test.journal_record select * from expch_fdw.journal_record limit 10 000 000;"
INSERT 0 10000000

real 11m36.091s

Перенос произойдет примерно за 88 мин против 90 часов(!) при однопоточном переносе.

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

Отключение реплик + пересоздание их после загрузки данных

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

После миграции с точки зрения общего времени переноса данных пересоздание реплик может оказаться более выгодным.

Практика миграции

Рассмотрим, как подойти к миграции данных из Oracle в PostgreSQL, и какие нюансы и трудности могут возникнуть на каждом из шагов.

Установка и настройка расширения oracle_fdw

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

Необходимая версия расширения может зависеть от версии БД PostgreSQL. Так, например, для 14 версии PostgreSQL подходит oracle_fdw версии 2.4, а для 15 — oracle_fdw версии 2.6. Список совместимости находится на сайте разработчика.

Пререквизиты для расширения oracle_fdw

Для ASTRA linux:
Для установки расширения на указанных серверах необходимо выполнить пререквизиты:

1.1. Подключить репозитории:

Oracleclient
postgresql (нужной версии)

1.2. Устанавливаются пакеты:
Devel пакет PostgreSQL той же версии, что установлен на сервере:
postgresql-server-dev-15

Клиент Oracle:
oracle-instantclient19.3-basic_19.3.0.0.0-2_amd64.deb
oracle-instantclient19.3-tools_19.3.0.0.0-2_amd64.deb
oracle-instantclient19.3-sqlplus_19.3.0.0.0-2_amd64.deb
oracle-instantclient19.3-devel_19.3.0.0.0-2_amd64.deb

Пакеты для компиляции:
gcc
make
libpq-dev

1.3. Права на каталоги:
chown -R postgres:postgres /usr/lib/postgresql/15/lib/
chown -R postgres:postgres /usr/share/postgresql/15/extension/
mkdir /usr/share/doc/postgresql-doc-15/
chown postgres:postgres /usr/share/doc/postgresql-doc-15

Компиляция oracle_fdw

Распаковываем oracle_fdw-master.zip
Из получившийся директории выполняем компиляцию:
#cd oracle_fdw-master

Иногда требуется установить переменную окружения ORACLE_HOME. Ее значение можно получить так:
cat /etc/ld.so.conf.d/oracle-instantclient.conf
/usr/lib/oracle
/usr/lib/oracle/19.10
/usr/lib/oracle/19.10/client64
/usr/lib/oracle/19.10/client64/bin
/usr/lib/oracle/19.10/client64/bin/adrci
...

То есть, значение переменной:
export ORACLE_HOME=/usr/lib/oracle/19.10/client64/
echo $ORACLE_HOME
export ORACLE_HOME=/usr/lib/oracle/19.3/client64/

Компиляция и установка:
make
make install

Настройка oracle_fdw

1. Создание расширения

Заходим в целевую БД PostgreSQL:
postgres=# \c egg_774201

Создаем расширение oracle_fdw и проверяем версию клиента Oracle:
egg_774201=# CREATE EXTENSION oracle_fdw;
egg_774201=# \dx

Name	Version	Schema	Description
oracle_fdw	1.2	kitit	foreign data wrapper for Oracle access
plpgsql (2 rows)	1.0	pg_catalog	PL/pgSQL procedural language

egg_774201=# select kitit.oracle_diag();
oracle_diag——————————————————————————————————————
oracle_fdw 2.6.0, PostgreSQL 15.3 (Debian 15.3-1.pgdg100+1), Oracle client 19.3.0.0.0

И дополнительно можно выдать грант владельцу БД PostgreSQL:
GRANT USAGE ON foreign SERVER ifegga_or5001ln_oracle_2 TO egg_774201_admin;

Проверка строки соединения с БД Oracle

Здесь вы должны получить полную строку соединения с Oracle, пригодную для sqlplus. Если соединение с сервера PostgreSQL к Oracle проходит успешно, то продолжаем настройки внешнего сервера. Пример — строка соединения и успешный вход в систему:

sqlplus kitit/kitit@rregga-or5001lv.test.ru:1521/rregg

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 10 17:34:22 2024
Version 19.3.0.0.0

Last Successful login time: Wed Apr 10 2024 17:24:37 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL>exit

Но строка соединения может быть в другом, более широком формате, это нужно уточнить у администраторов Oracle обсуживающих данную систему. Пример:

sqlplus DMG/dia@'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = k3-mgds-app401l.ru)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = K3SDC)))'

Так или иначе нужно использовать актуальную строку соединения с БД Oracle.

2. Создание внешнего сервера и маппинг пользователей

По полученной ранее строке соединения создаем сервер:

CREATE SERVER ifegga_or5001ln_oracle_2 FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//rregga-or5001lv.test.ru:1521/rregg');

Здесь есть нюанс, связанный с тем, что по умолчании oracle_fdw устанавливает высокий уровень изоляции serializable, если у вас возникла ситуация, что нет возможности полностью отключить соединения с источником и какие-то клиенты читают данные из исходной БД Oracle, то может возникнуть ошибка сериализации. И вы не сможете даже прочесть нужные для вас данные. Чтобы этого избежать можно добавить такую опцию:

CREATE SERVER ifegga_or5001ln_oracle_2 FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//rregga-or5001lv.test.ru:1521/rregg', isolation_level 'read_committed');

Создаем маппинг пользователей:

CREATE USER MAPPING FOR postgres SERVER ifegga_or5001ln_oracle_2 OPTIONS (user 'kitit', password 'kitit');
CREATE USER MAPPING FOR egg_774201_admin SERVER ifegga_or5001ln_oracle_2 OPTIONS (user 'kitit', password 'kitit');

Финальные настройки схем и прав к ним:

create schema kitit;
ALTER SCHEMA kitit owner TO egg_774201_admin;
create schema kitit_fdw;
ALTER SCHEMA kitit_fdw owner TO egg_774201_admin;

3. Импорт внешней схемы и пробные миграции данных для определения соответствия структуры таблиц

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

IMPORT FOREIGN SCHEMA "KITIT" FROM SERVER ifegga_or5001ln_oracle_2 INTO kitit_fdw;

Это команда выполнит создание не только внешних таблиц в указанной вами схеме kitit, но и представлений (View) Oracle примерно в таком формате:

CREATE FOREIGN TABLE kitit_fdw.egg_arm_et (
	datecreate timestamp NULL,
	testcaseid varchar(50) NULL,
	processid varchar(255) NULL
)
SERVER ifegga_or5001ln_oracle_2
OPTIONS (schema 'KITIT', table 'EGG_ARM_ET');

Дополнительно:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA kitit_fdw TO egg_774201_admin;

Что мы получили?

У нас есть две схемы в PostgreSQL kitit — целевая локальная схема, куда мы будем мигрировать и исходная схема Oracle — откуда мигрируем kitit_fdw.

Теперь попробуем проверить, работает ли миграция.

Создадим пустую локальную таблицу на основе структуры внешней оракловой таблицы:

create table kitit.EGG_ARM_MESSAGEID as select * from kitit_fdw.EGG_ARM_MESSAGEID where false;

Выполним пробную миграцию 1000 строк:

insert into kitit.EGG_ARM_MESSAGEID select * from kitit_fdw.EGG_ARM_MESSAGEID limit 1000;

Очистим таблицу:

truncate kitit.EGG_ARM_MESSAGEID;

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

План миграции

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

Ниже основной лист плана миграции одной из систем.

no.	TABNAME	SIZE,MB	COUNT	BLOB	CLOB	NCLOB	BATCH
1	SALARYREESTROCROWSTATEHISTORY	120 922	796 415 919				10 000 000
2	PRODUCT_REQ_HST	120 570	348 654 327	yes			10 000 000
3	REGISTRY_ORGVISIT_CRT_ROW_FLG	61 513	230 681 315		yes		10 000 000
4	SALARYBANKCARDSHIPHISTORY	48 098	214 586 084				10 000 000
5	SALARYFILE	448 079	207 860 019	yes			1 000 000
6	SALARYREGISTRYFILE	46 094	186 692 547				10 000 000
7	REGISTRY_ORGVISIT_ROW_HST	41 436	180 497 163				10 000 000
8	REGISTRY_ORGVISIT_CRT_ROW_HST	33 365	126 122 897				10 000 000
9	SALARYBLACKLISTSCHECKLOG	25 507	99 659 075				10 000 000
10	SALARYOBJECTACTIVITY	25 471	88 773 362				10 000 000
11	SALARYACTIONREGISTRY	23 320	87 264 557				10 000 000
12	SALARYPERSONPHONE	17 045	64 905 258				10 000 000
13	SALARYPERSONDUL	15 263	31 519 351
14	SALARYREESTROCROWBANKCARD	14 926	28 628 341
15	CARD_DELIVERY_POINT_HST	14 501	24 954 214
16	SALARY_OCR_F_VALIDPERSONERROR	12 074	23 773 224
17	SALARYPERSON	8 579	21 690 065
18	RELATION_ORG_PERSON	7 119	21 658 692
19	REGISTRY_KAFKA_MESSAGE	3 439	5 038 067		yes
20	REGISTRY_OC_ROW_MDMID_REPLACE	2 230	1 939 239
21	PERSON_INFO	1 927	1 848 472
22	PERSON_INFO_DOC	1 883	1 639 772
23	PERSON_INFO_ADDRESS	1 361	1 520 347
24	PERSONIFIED_INFO	1 230	1 502 885
25	REGISTRY_ORGVISIT_ROW_PHOTO	791	1 496 631
26	PERSON_INFO_PRODUCT_PKG	668	1 079 323
27	COMMONTYPEOSPOSITION	293	317 218
28	PRODUCT_REQ_PKG	252	226 965
29	PERSON_UNLOAD_HST	235	180 702
30	DOC_ISSUE_CONFIDANT_ROW_FLG	173	130 440

Таблица отсортирована числу строк, но вы можете отсортировать и по размеру.

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

TABNAME – имя таблицы;
SIZE,MB — размер таблицы в МВ;
COUNT — число строк;
BLOB — содержит ли таблица BLOB;
CLOB — содержит ли таблица CLOB;
NCLOB — есть ли в таблице NCLOB. Данный тип не поддерживается orcale_fdw, его нужно конвертировать в определении fdw таблицы через оракловую функцию TO_LOB(имя_поля);
BATCH — размер батча, определяемый фактической скоростью миграции данного количества строк по таблице. Размер батча определяется таким образом, чтобы перенос указанного количества строк происходил не дольше 5–10 мин. Как это определить будет рассмотрено ниже.

Вариант 1. Схемы БД PostgreSQL нет, и ее нужно создать на основе той что имеется в Oracle

В простейшем случае создать схему по таблицам можно из foreign tables, которые уже конвертированы в схеме _fdw. Но так вы получите схемы без ограничений и индексов. Как их получить — будет рассмотрено ниже.

Создание схемы одной таблицы выглядит так:

create table kitit.EGG_ARM_MESSAGEID as select * from kitit_fdw.EGG_ARM_MESSAGEID where false;

Если конвертировать всю схему, то:
-- Импорт схемы без данных

do $$ declare
  schema_from varchar = 'kitit_fdw';
  schema_to varchar = 'kitit';
  r record;
begin for r in
select
	table_name
from
	information_schema."tables"
where
	table_schema = schema_from loop execute 'create table ' || schema_to || '.' || r.table_name || ' as select * from ' || schema_from || '.' || r.table_name ||' where false';
end loop;
end $$

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

Индексы и ограничения можно получить из ora2pg или создать вручную, если индексы и ограничения достаточно просты.

В ora2pg эти данные можно получить в папке проекта:
$ls projects\kitit\schema\tables
CONSTRAINTS_table.sql
FKEYS_table.sql
INDEXES_table.sql
table.sql

Скрипты CONSTRAINTS_table.sql FKEYS_table.sql INDEXES_table.sql как раз и есть нужные для нас скрипты, которые требуют модификации, но в целом пригодны на более, чем 90% для загрузки. Эти скрипты нужно использовать только после миграции данных в таблицы. Этот прием (загрузка данных + создание индексов) быстрее загрузки данных в таблицы с созданными индексами и ограничениями в десятки, а иногда в сотни раз. Но это применимо только для крупных таблиц, как это было показано ранее. Небольшие таблицы, как правило, в этом не нуждаются.

Вариант 2. Схема БД PostgreSQL есть, и ее нужно смапить с той, что имеется в Oracle

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

Возможен разный порядок столбцов в источнике Oracle и PostgreSQL и нужно выполнить сопоставление столбцов. Этих столбцов может быть несколько десятков, а то и сотен.
Разный типы столбцов в источнике Oracle и PostgreSQL. Например, столбец ID в Oracle может быть типа varchar, а в PostgreSQL int8. Соответственно нужно понять, как именно трансформировать данные.
Разные длины столбцов в источнике Oracle и PostgreSQL. Например, в Oracle varchar (1000), а в PostgreSQL varchar (32), при вставке длинного значения в 32 может быть ошибка. Поэтому нужно понять, каким образом обрезать исходные значения. Или же увеличить длину поля в PostgreSQL.
Поддерживаемые типы данных oracle_fdw. Например, тип NCLOB, который можно конвертировать функцией TO_LOB(‘nclob_value’) на стороне Oracle. Но это справедливо если не используются особенности типа NCLOB в Oracle.
Для увеличения скорости миграции данных в таблицах по необходимости нужно удалить все ограничения и индексы, а затем пересоздать. То есть, нужно из схемы создать скрипты сначала удаления всех ограничений и индексов, а затем их создания. Но это необходимо не для всех таблиц, а для самых больших. Или если в таблице очень много ключей и индексов. Так или иначе это определяется фактической скоростью миграции данной таблицы и количеством связей и проверок, необходимых для вставки каждой из строк.

Определение размера батча. Пробные миграции для определения размера батча

Как было сказано ранее, размер батча определяется таким образом, чтобы перенос указанного количества строк происходил не дольше 5–10 мин. Это можно определить только пробными миграциями каждой из таблиц.

Особое внимание стоит уделить таблицам с числом строк от 1 миллиона. Хотя могут быть проблемы с таблицами и с меньшим числом строк. Обычно пробные миграции проводят снизу таблицы начиная с таблиц с наименьшим размером. Ниже пример плана миграции на одном из тестовых контуров:

NUM	TABNAME	COUNT	COMMENT	TIME	BLOB	CLOB	NCLOB	BATCHES
1	SMEV3_REESTR	99 663 975	Перенос полностью	33 min				99
2	EGG_ARM_MESSAGEID	94 691 233	DATECREATE 01.01.2024	38 min	yes			94
3	EGG_ARM_FSSP	32 999 998	REQUEST_DATE 01.01.2024	11 min				32
4	EGG_ARM_FSSP_REQUESTS	14 540 019	INPUT_DATE 01.01.2024	8 min				14
5	EGG_ARM_ZKH	11 141 814	DATECREATE 01.01.2024	11 min		yes		11
6	EGG_ARM_FK	8 401 526	DATECREATE 01.01.2024	10 min				8
7	EGG_FSSP_AD	1 480 676	Перенос полностью	5 min
8	EGG_SMEV3_CONTEXT	497 182	LAST_UPDATE 01.01.2024	10 min
9	FK_SMEV3	2 113	Перенос полностью
10	SMEV3_DEL_DELIVERY	823	Перенос полностью
11	SGD_VERSION	120	Перенос полностью
12	ZKH_SMEV3	112	Перенос полностью
13	SMEV3_RESTRICTION_TEMP	38	LAST_UPDATE 01.01.2024
14	ARMQ_QUEUE_PAIR	22	Перенос полностью
15	ARMQ_CONNECTOR	20	Перенос полностью
16	SMEV3_INQUIRY_TEMP	16	Перенос полностью
17	EGG_ARM_FSSP_NOT_ADOPTED	8	Перенос полностью
18	IA_ROUTING	4	Перенос полностью
19	EGG_PROPERTIES	3	Перенос полностью
20	DEL_DELIVERY	1	Перенос полностью
21	EGG_ARM_FK_CERTIFICATE	1	Перенос полностью
22	EGG_ARM_COMMAND	0	Перенос полностью
23	SMEV3_RESTRICTION_SEND	0	Перенос полностью
24	EGG_TASKS	0	Перенос полностью
25	SMEV3_INQUIRY_SEND	0	Перенос полностью

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

CREATE FOREIGN TABLE kitit_fdw.egg_arm_messageid (
	processid varchar(50) NULL,
	groupid varchar(50) NULL,
	messageid varchar(36) NULL,
	logid varchar(36) NULL,
	datecreate timestamp NOT NULL,
	"type" varchar(1) NULL
)
SERVER ifegga_or5001ln_oracle_2
OPTIONS (table '(SELECT * FROM KITIT.EGG_ARM_MESSAGEID WHERE DATECREATE >=
to_date('01.01.2024 00:00:00', 'dd.mm.yyyy hh24:mi:ss'))');

Миграции таблиц с фиксацией их времени загрузки:

insert into kitit.EGG_ARM_MESSAGEID select * from kitit_fdw.EGG_ARM_MESSAGEID on conflict do nothing;
-- 38 min
insert into kitit.EGG_ARM_FSSP select * from kitit_fdw.EGG_ARM_FSSP on conflict do nothing;
-- 11 min
insert into kitit.EGG_ARM_FSSP_REQUESTS select * from kitit_fdw.EGG_ARM_FSSP_REQUESTS on conflict do nothing;
-- 8 min
insert into kitit.EGG_ARM_ZKH select * from kitit_fdw.EGG_ARM_ZKH on conflict do nothing;
-- 11 min
insert into kitit.EGG_ARM_FK select processid, supplierbildid, paymentdate, amount, bic_swift, ks, rs, inn, kpp, kbk, okato, eip, errorcode, datecreate, systemid, statuscode, gisgpm_errorcode, datechange, uip_type, member_id, additional_id, sys_paymentdate, payment_id, systemidentifier, payername, payeraccount, pack_processid, pack_response_processid, component from kitit_fdw.EGG_ARM_FK on conflict do nothing;
-- 10 min
insert into kitit.EGG_FSSP_AD select * from kitit_fdw.EGG_FSSP_AD on conflict do nothing;
-- 5 min
insert into kitit.EGG_SMEV3_CONTEXT select * from kitit_fdw.EGG_SMEV3_CONTEXT on conflict do nothing;
-- 10 min
insert into kitit.FK_SMEV3 select * from kitit_fdw.FK_SMEV3 on conflict do nothing;
-- 667 ms
insert into kitit.SMEV3_DEL_DELIVERY select * from kitit_fdw.SMEV3_DEL_DELIVERY on conflict do nothing;
-- 265 ms
insert into kitit.SGD_VERSION select * from kitit_fdw.SGD_VERSION on conflict do nothing;
-- 15 ms
insert into kitit.ZKH_SMEV3 select * from kitit_fdw.ZKH_SMEV3 on conflict do nothing;
--145 ms
insert into kitit.SMEV3_RESTRICTION_TEMP select * from kitit_fdw.SMEV3_RESTRICTION_TEMP on conflict do nothing;
--67 ms
insert into kitit.ARMQ_QUEUE_PAIR select * from kitit_fdw.ARMQ_QUEUE_PAIR on conflict do nothing;
-- 17 ms
insert into kitit.ARMQ_CONNECTOR select * from kitit_fdw.ARMQ_CONNECTOR on conflict do nothing;
--17 ms
insert into kitit.SMEV3_INQUIRY_TEMP select * from kitit_fdw.SMEV3_INQUIRY_TEMP on conflict do nothing;
-- 52 ms
insert into kitit.EGG_ARM_FSSP_NOT_ADOPTED select * from kitit_fdw.EGG_ARM_FSSP_NOT_ADOPTED on conflict do nothing;
-- 16 ms
insert into kitit.IA_ROUTING select * from kitit_fdw.IA_ROUTING on conflict do nothing;
-- 14 ms
insert into kitit.EGG_PROPERTIES select * from kitit_fdw.EGG_PROPERTIES on conflict do nothing;
-- 51 ms
insert into kitit.DEL_DELIVERY select * from kitit_fdw.DEL_DELIVERY on conflict do nothing;
-- 322 ms
insert into kitit.EGG_ARM_FK_CERTIFICATE select * from kitit_fdw.EGG_ARM_FK_CERTIFICATE on conflict do nothing;
-- 10 ms

То есть, начиная с таблицы EGG_ARM_FK, имеющей 8 млн записей, можно выполнить виртуальное секционирование.

После пробных миграций не забудьте очистить все целевые таблицы через truncate.

Параллелизм и запуск параллельной миграции. Мониторинг возникающих проблем

Каждый из скриптов переливки таблицы или батча в дальнейшем разбивается на потоки.

Пример основного многопоточного скрипта на 55 потоков:
#!/bin/sh

echo Start JORNAL RECORDS import
date
echo THREAD 1
./start_1.sh &
echo THREAD 2
./start_2.sh &
….
./start_52.sh &
echo THREAD 53
./start_53.sh &
echo THREAD 54
./start_54.sh &
echo THREAD 55
./start_55.sh &

echo .... To inspect of the import data records progress, see pg_stat_activity
date

Содержание файла одного из потоков:

date >> ./journal_record.6.log; psql -p 6432 creo -c "insert into expch.trade_details select * from expch_fdw.trade_details_225 on conflict do nothing;" >> ./journal_record.6.log 2>&1 ; date >> ./journal_record.6.log date >> ./journal_record.55.log; psql -p 6432 creo -c "insert into expch.trade_details select * from expch_fdw.trade_details_329 on conflict do nothing;" >> ./journal_record.55.log 2>&1 ; date >> ./journal_record.55.log date >> ./journal_record.15.log; psql -p 6432 creo -c "insert into expch.client_payment select * from expch_fdw.client_payment_40 on conflict do nothing;" >> ./journal_record.15.log 2>&1 ; date >> ./journal_record.15.log date >> ./journal_record.53.log; psql -p 6432 creo -c "insert into expch.trade_details select * from expch_fdw.trade_details_327 on conflict do nothing;" >> ./journal_record.53.log 2>&1 ; date >> ./journal_record.53.log date >> ./journal_record.6.log; psql -p 6432 creo -c "insert into expch.trade_details select * from expch_fdw.trade_details_390 on conflict do nothing;" >> ./journal_record.6.log 2>&1 ; date >> ./journal_record.6.log date >> ./journal_record.13.log; psql -p 6432 creo -c "insert into expch.client_payment select * from expch_fdw.client_payment_42 on conflict do nothing;" >> ./journal_record.13.log 2>&1 ; date >> ./journal_record.13.log date >> ./journal_record.55.log; psql -p 6432 creo -c "insert into expch.trade select id,subject_id,member_code,...,off_balance_amt,state_support,state_support_info,guarantee_volume_sum from expch_fdw.trade_0 on conflict do nothing;" >> ./journal_record.55.log 2>&1 ; date >> ./journal_record.55.log date >> ./journal_record.5.log; psql -p 6432 creo -c "insert into expch.TRADE_COLLATERAL_LAYERS select * from expch_fdw.TRADE_COLLATERAL_LAYERS on conflict do nothing;" >> ./journal_record.5.log 2>&1 ; date >> ./journal_record.5.log date >> ./journal_record.35.log; psql -p 6432 creo -c "insert into expch.trade_details select * from expch_fdw.trade_details_144 on conflict do nothing;" >> ./journal_record.35.log 2>&1 ; date >> ./journal_record.35.log date >> ./journal_record.25.log; psql -p 6432 creo -c "insert into expch.trade_details select * from expch_fdw.trade_details_409 on conflict do nothing;" >> ./journal_record.25.log 2>&1 ; date >> ./journal_record.25.log date >> ./journal_record.9.log; psql -p 6432 creo -c "insert into expch.trade_details select * from expch_fdw.trade_details_173 on conflict do nothing;" >> ./journal_record.9.log 2>&1 ; date >> ./journal_record.9.log date >> ./journal_record.35.log; psql -p 6432 creo -c "insert into expch.trade_details select * from expch_fdw.trade_details_34 on conflict do nothing;" >> ./journal_record.35.log 2>&1 ; date >> ./journal_record.35.log date >> ./journal_record.14.log; psql -p 6432 creo -c "insert into expch.PAYMENT_FREQ select * from expch_fdw.PAYMENT_FREQ on conflict do nothing;" >> ./journal_record.14.log 2>&1 ; date >> ./journal_record.14.log date >> ./journal_record.37.log; psql -p 6432 creo -c "insert into expch.trade_details select * from expch_fdw.trade_details_201 on conflict do nothing;" >> ./journal_record.37.log 2>&1 ; date >> ./journal_record.37.log

В логи пишется как количество вставок и сообщения об ошибках, так и время начала и завершения загрузки батча.

Запускаем start_all.sh. В pg_activity производится мониторинг процесса миграции по потокам:

PostgreSQL 15.3 - ltc-pg5001lp.test.ru - postgres@ltc-pg5001lp.test.ru:5432/postgres - Ref.: 2s
Size: 3.90T - 20.18M/s ⋅ TPS: 8 ⋅ Active connections: 59 ⋅ Duration mode: query
Mem.: 28.7% - 26.24G/1007.52G    IO Max: 13586/s
Swap: 31.7% - 603.54M/1.86G      Read:   13.16M/s - 3367/s
Load: 11.48 10.89 11.75          Write: 39.92M/s - 10219/s

RUNNING QUERIES

PID DATABASE APP USER CLIENT CPU% MEM% READ/s WRITE/s TIME+ Waiting IOW state Query 12413 creo psql postgres local 49.3 8.2 243.44K 4.58M 912:55.77 WALWrite N active insert into expch.EVENTS select * from expch_fdw.EVENTS on conflict do nothing; 17174 ltcrer-pg5002lp repluser 10.221.23.120/32 28.0 0.0 0B 0B 905:24.64 WalSenderMain N active START_REPLICATION SLOT "ltcrer_pg5002lp" 6130/BD000000 TIMELINE 6 24195 creo psql postgres local 15.5 18.6 0B 213.23K 162:24.42 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_321 on conflict do nothing; 25666 creo psql postgres local 15.1 17.9 0B 557.95K 159:39.50 WALWrite N active insert into expch.trade_details select * from expch_fdw.trade_details_318 on conflict do nothing; 29970 creo psql postgres local 16.9 19.5 31.98K 629.03K 149:17.46 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_354 on conflict do nothing; 33975 creo psql postgres local 17.8 20.9 24.88K 501.09K 139:49.81 WALInsert N active insert into expch.trade_details select * from expch_fdw.trade_details_288 on conflict do nothing; 34261 creo psql postgres local 17.3 18.2 7.11K 536.63K 138:47.16 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_320 on conflict do nothing; 35490 creo psql postgres local 16.4 20.8 0B 554.40K 136:33.11 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_289 on conflict do nothing; 39535 creo psql postgres local 17.3 15.4 31.98K 341.17K 129:17.10 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_324 on conflict do nothing; 42067 creo psql postgres local 16.0 19.7 0B 273.65K 122:51.41 WALInsert N active insert into expch.trade_details select * from expch_fdw.trade_details_336 on conflict do nothing; 46055 creo psql postgres local 15.1 10.8 0B 220.34K 114:25.79 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_322 on conflict do nothing; 46587 creo psql postgres local 17.3 17.7 10.66K 820.94K 112:23.94 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_290 on conflict do nothing; 50153 creo psql postgres local 19.1 18.8 0B 1.51M 104:45.06 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_351 on conflict do nothing; 52349 creo psql postgres local 16.4 5.0 0B 241.66K 99:52.58 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_358 on conflict do nothing; 54305 creo psql postgres local 15.5 19.8 0B 515.31K 95:28.38 WALWrite N active insert into expch.trade_details select * from expch_fdw.trade_details_294 on conflict do nothing; 54647 creo psql postgres local 17.8 16.9 14.22K 1.27M 94:08.78 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_356 on conflict do nothing; 54860 creo psql postgres local 16.4 10.3 0B 216.78K 93:32.76 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_349 on conflict do nothing; 58661 creo psql postgres local 17.3 14.4 17.77K 486.88K 84:38.07 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_307 on conflict do nothing; 60696 creo psql postgres local 16.9 17.2 0B 497.54K 79:46.31 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_302 on conflict do nothing; 62637 creo psql postgres local 16.4 11.4 0B 248.77K 75:20.59 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_337 on conflict do nothing; 62929 creo psql postgres local 17.3 16.1 10.66K 650.35K 74:16.67 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_300 on conflict do nothing; 769 creo psql postgres local 16.0 9.4 0B 462.00K 69:59.01 WALWrite N active insert into expch.trade_details select * from expch_fdw.trade_details_360 on conflict do nothing; 794 creo psql postgres local 16.8 17.9 14.22K 540.18K 69:53.14 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_306 on conflict do nothing; 2598 creo psql postgres local 15.5 3.5 0B 245.21K 66:18.63 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_339 on conflict do nothing; 6076 creo psql postgres local 16.8 3.1 0B 216.78K 57:44.43 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_340 on conflict do nothing; 6130 creo psql postgres local 17.7 14.0 3.55K 447.78K 57:38.65 WALWrite N active insert into expch.trade_details select * from expch_fdw.trade_details_357 on conflict do nothing; 7399 creo psql postgres local 29.7 15.6 0B 1.69M 55:52.47 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_368 on conflict do nothing; 7994 creo psql postgres local 17.3 15.3 3.55K 597.04K 53:43.93 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_332 on conflict do nothing; 9695 creo psql postgres local 18.2 14.2 3.55K 497.54K 49:49.45 WALInsert N active insert into expch.trade_details select * from expch_fdw.trade_details_328 on conflict do nothing; 9781 creo psql postgres local 19.0 13.6 7.11K 2.17M 49:25.45 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_333 on conflict do nothing; 12410 creo psql postgres local 18.2 11.2 3.55K 557.95K 43:44.34 WALWrite N active insert into expch.trade_details select * from expch_fdw.trade_details_303 on conflict do nothing; 13890 creo psql postgres local 18.2 13.2 10.66K 689.44K 40:47.61 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_304 on conflict do nothing; 14261 creo psql postgres local 17.7 13.6 17.77K 593.49K 39:21.06 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_301 on conflict do nothing; 14454 creo psql postgres local 21.2 13.2 3.55K 838.70K 38:48.18 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_367 on conflict do nothing; 14815 creo psql postgres local 15.5 2.1 0B 799.61K 37:16.11 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_338 on conflict do nothing; 18165 creo psql postgres local 18.1 11.8 21.32K 966.64K 30:39.23 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_350 on conflict do nothing; 18331 creo psql postgres local 19.0 9.4 10.66K 447.78K 29:49.12 WALInsert N active insert into expch.trade_details select * from expch_fdw.trade_details_364 on conflict do nothing; 19112 creo psql postgres local 50.0 12.2 0B 5.29M 27:03.16 WALInsert N active insert into expch.trade_details select * from expch_fdw.trade_details_394 on conflict do nothing; 21651 creo psql postgres local 18.6 9.8 7.11K 1.27M 21:54.82 WALInsert N active insert into expch.trade_details select * from expch_fdw.trade_details_305 on conflict do nothing; 26875 creo psql postgres local 48.2 0.8 0B 1.99M 11:42.52 WALInsert N active insert into expch.client_payment select * from expch_fdw.client_payment_109 on conflict do nothing; 27170 creo psql postgres local 24.8 5.5 31.98K 2.03M 11:38.50 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_366 on conflict do nothing; 27780 creo psql postgres local 50.8 8.0 39.09K 2.25M 10:23.48 WALWrite N active insert into expch.trade_details select * from expch_fdw.trade_details_393 on conflict do nothing; 29374 creo psql postgres local 17.7 2.9 39.09K 845.81K 06:44.00 BufferContent N active insert into expch.trade_details select * from expch_fdw.trade_details_334 on conflict do nothing; 29583 creo None local 72.9 0.2 12.56M 0B 05:58.52 VacuumDelay N active autovacuum: VACUUM expch.trade_details

Содержание логов может быть таким:

cat -n journal_record.47.log
1 Mon 20 Nov 2023 08:15:01 PM MSK
2 INSERT 0 10000000
3 Mon 20 Nov 2023 10:15:07 PM MSK
4 Mon 20 Nov 2023 10:15:07 PM MSK
5 INSERT 0 10000000
6 Mon 20 Nov 2023 11:33:51 PM MSK
7 Mon 20 Nov 2023 11:33:51 PM MSK
8 INSERT 0 10000000
9 Tue 21 Nov 2023 12:08:12 AM MSK
10 Tue 21 Nov 2023 12:08:12 AM MSK
11 INSERT 0 10000000
12 Tue 21 Nov 2023 01:02:32 AM MSK
13 Tue 21 Nov 2023 01:02:32 AM MSK
14 INSERT 0 10000000
15 Tue 21 Nov 2023 02:15:27 AM MSK
16 Tue 21 Nov 2023 02:15:27 AM MSK
17 INSERT 0 10000000
18 Tue 21 Nov 2023 03:19:27 AM MSK
19 Tue 21 Nov 2023 03:19:27 AM MSK
20 INSERT 0 9992000
21 Tue 21 Nov 2023 03:56:46 AM MSK
22 Tue 21 Nov 2023 03:56:46 AM MSK
23 ERROR: invalid byte sequence for encoding "WIN1251": 0x00
24 CONTEXT: converting column "description" for foreign table scan of "journal_record_452", row 7784003
25 Tue 21 Nov 2023 04:11:08 AM MSK

Здесь, например, возникла проблема с «битым» значением WIN1251. Последовательность байт не соответствует формату WIN1251.

Может возникнуть ситуация, связанная с лимитом максимального размера типов PostgreSQL text и bytea 1Гб. Исходное поле CLOB и BLOB могут превышать 1Гб. Такие ситуации обсуждаются отдельно. Либо их возможно уменьшить в исходной БД Oracle, либо загрузить эти строки без полей CLOB и BLOB и выгрузить их отдельно на файловое хранилище. Или разбить на части а то и вовсе пропустить если на текущий момент не понятно что с ними делать. Такие вопросы нужно решать с архитектором системы.

В случае проблем с источником Oracle может возникнуть такая ситуация:
25 Tue 21 Nov 2023 07:57:47 PM MSK
26 Tue 21 Nov 2023 07:57:47 PM MSK
27 INSERT 0 9541960
28 Tue 21 Nov 2023 08:52:37 PM MSK
29 Tue 21 Nov 2023 08:52:37 PM MSK
30 Tue 21 Nov 2023 04:33:22 PM MSK
31 INSERT 0 773060
32 Tue 21 Nov 2023 04:51:41 PM MSK
33 Tue 21 Nov 2023 04:51:41 PM MSK
34 ERROR: error fetching result: OCIStmtFetch2 failed to fetch next result rows
35 DETAIL: ORA-04030: out of process memory when trying to allocate 232 bytes (session heap,lob ctl struct)
36 Tue 21 Nov 2023 07:50:38 PM MSK
37 Tue 21 Nov 2023 07:50:38 PM MSK
38 ERROR: connection for foreign table "journal_record_detail_131" cannot be established
39 DETAIL: ORA-12518: TNS:listener could not hand off client connection
40 Tue 21 Nov 2023 07:51:21 PM MSK
41 Tue 21 Nov 2023 07:51:21 PM MSK
42 ERROR: connection for foreign table "journal_record_detail_186" cannot be established
43 DETAIL: ORA-12518: TNS:listener could not hand off client connection
44 Tue 21 Nov 2023 07:51:21 PM MSK
45 Tue 21 Nov 2023 07:51:21 PM MSK
46 ERROR: connection for foreign table "journal_record_detail_241" cannot be established
47 DETAIL: ORA-12518: TNS:listener could not hand off client connection
48 Tue 21 Nov 2023 07:51:22 PM MSK
49 Tue 21 Nov 2023 07:51:22 PM MSK
50 ERROR: connection for foreign table "journal_record_detail_296" cannot be established
51 DETAIL: ORA-12518: TNS:listener could not hand off client connection
52 Tue 21 Nov 2023 07:51:23 PM MSK
53 Tue 21 Nov 2023 07:51:23 PM MSK
54 ERROR: connection for foreign table "journal_record_detail_351" cannot be established
55 DETAIL: ORA-12518: TNS:listener could not hand off client connection
56 Tue 21 Nov 2023 07:51:23 PM MSK
57 Tue 21 Nov 2023 07:51:23 PM MSK
58 ERROR: connection for foreign table "journal_record_detail_406" cannot be established
59 DETAIL: ORA-12518: TNS:listener could not hand off client connection
60 Tue 21 Nov 2023 07:51:24 PM MSK

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

Исходная БД Oracle может быть не идеальна, и сама по себе содержать ошибки. В случае их возникновения с каждым случаем нужно разбираться отдельно: либо фиксировать ошибки на стороне данных Oracle, либо их пропускать.

Динамика вставок и роста размера БД во время миграции

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

Итоги и результаты

Описанный выше подход при миграции данных на текущий момент является наиболее точным с точки зрения оптимизации и ускорения переливки данных из Oracle в PostgreSQL. Несмотря на то, что он требует достаточно много ручной работы: подготовки, ознакомления со спецификой, пробных миграций — нам все же удалось выработать общую систему и подход к таким задачам. Коротко их можно описать следующим образом:

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

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

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

Конечно, есть реализации систем, когда функционал Oracle значительно или частично эмулируется в PostgreSQL, как это сделано, например, в orafce, решениях postgres PRO или коммерческих реализациях Diasoft. И это может значительно упростить миграцию. Но мы считаем, что ее можно считать полноценной и завершенной, если функционал целевой системы PostgreSQL подвергается минимальным модификациям. Иначе это в дальнейшем может привести к проблемам поддержки и совместимости этих решений с новыми ванильными или модифицированными версиями PostgreSQL.

Теги:

Хабы: