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

Оглавление

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

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

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

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

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

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

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

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

8. Ограничения

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

10. Выводы по инструментам миграции
    
    Рекомендации по ускорению миграции данных

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

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

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

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

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

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

17. Компиляция oracle_fdw

18. Настройка oracle_fdw
    

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

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

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

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

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

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

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

26. Динамика вставок и роста размера БД во время миграции
    
    Итоги и результаты

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

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

Точных определений в настоящее время нет, так как со временем изменяются параметры и производительность оборудования, технологии хранения, скорости изменения и извлечения данных. И те размеры, и сложность БД которые 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 пунктов и вендор, как правило, имеет скрипты для автоматизации заполнения данной оценки.

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

1. Настройка целевой БД и средств миграции;

2. Миграция схемы БД, которая заранее подготовлена при тестовой миграции;

3. Начало простоя системы;

4. Остановка прикладного ПО на исходной БД Oracle;

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

6. Переключение прикладного ПО на целевую БД PostgreSQL;

7. Конец простоя системы.

Проблемы миграции крупных БД 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, который может использовать оракловые индексы в диапазоне с тем чтобы до нужной секции данных можно было добраться быстрее.

Разбивку на виртуальные секции можно сделать по диапазону первичного ключа, датам создания модификации строки или другим критериям. Иногда определить критерии разбиения бывает вовсе невозможно без понимания математики их хранения в БД, но это все же достаточно редкие случаи. Если же это произошло, в БД Oracle требуется специально создать индекс для переноса данных.

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

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

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

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

2. Рекомендуемый размер батча 1–10 млн строк. Главное, чтобы батч переносился за 10–20 минут и не более. Если размер батча слишком большой – может быть такая ошибка: ORA-04030: out of process memory when trying to allocate 232 bytes (session heap,lob ctl struct).

3. Запрос в определении секции должен выполняться на стороне Oracle со всеми возможными оптимизациями по скорости извлечения данных.

4. Пример для 4,5 миллиардов записей с размером батча 10 млн и числом секций 450. Ниже пример такой секции с номером 102:

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;
…i
nsert into expch.journal_record select * from expch_fdw.journal_record_450 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

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

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

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 — содержит ли таблица 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 миллиона. Хотя могут быть проблемы с таблицами и с меньшим числом строк. Обычно пробные миграции проводят снизу таблицы начиная с таблиц с наименьшим размером. Ниже пример плана миграции на одном из тестовых контуров:

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

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. Несмотря на то, что он требует достаточно много ручной работы: подготовки, ознакомления со спецификой, пробных миграций — нам все же удалось выработать общую систему и подход к таким задачам. Коротко их можно описать следующим образом:

1. Составляется план миграции с учетом особенностей по каждой из таблиц.

2. Определяются таблицы, которые требуется разбить на виртуальные секции.

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

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

5. Границы размеров БД, когда этот метод нужно применять: от 100Гб и примерно до 10Тб.

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

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

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