Технический разбор миграции с Oracle на Postgres Pro

Миграция с Oracle Database — одного из самых мощных коммерческих решений — на Postgres Pro требует глубокого понимания архитектурных различий, различий в SQL-диалектах, трансляции логики и подходов к производительности и сопровождению. Этот процесс не ограничивается «переносом схем» — речь идет о переосмыслении логики работы с данными, пересборке слоев DWH, замене PL/SQL на PL/pgSQL и полной адаптации инфраструктуры под Postgres Pro.

Далее — практическое руководство, технические нюансы и советы по полной миграции.

1. Архитектурные различия: Oracle vs Postgres Pro

Особенность	Oracle	Postgres Pro
Тип лицензии	Проприетарная	Open source / лицензируемые расширения
Параллелизм	Adaptive, автоматический	Настраивается вручную (parallel workers)
Физическая структура	Segment / extent-based storage	Таблицы / TOAST / heap
Transaction ID wraparound	Не актуален	Требует VACUUM
Хранилище LOB	SecureFiles, BasicFiles	TOAST (in/out-of-line)
Конвейерные функции (PIPELINED)	Да	Отсутствуют (аналог — RETURNS SETOF)
Materialized views	С рефрешем по расписанию, логикой	Простые, рефреш вручную или по расписанию
Partitioning	Native, subpartitioning, interval	Declarative, inheritance, constraint-based
PL/SQL	Язык процедур Oracle	PL/pgSQL (неполная совместимость)

2. Основные этапы миграции

2.1 Анализ и инвентаризация

Перед тем как копировать хоть один байт, необходимо:

Составить список всех объектов: таблицы, представления, индексы, sequences, процедуры, пакеты, функции, типы, триггеры.
Построить карту связей (foreign key, зависимости).
Выделить бизнес-критичные процессы (например, nightly ETL, SLA-отчеты).
Проанализировать использование специфичных фич Oracle (например, CONNECT BY, PIVOT, MERGE, EXCEPTION, %ROWTYPE, REF CURSOR, PACKAGE).

Инструменты:

Oracle SQL Developer + Metadata Diff Report
ora2pg
SQLines
Скрипты из DBMS_METADATA.GET_DDL

3. Конвертация схем: типы, синтаксис, особенности

3.1 Маппинг типов данных

Oracle	Postgres Pro	Комментарии
NUMBER(p,s)	NUMERIC(p,s)	Полная совместимость
VARCHAR2(n)	VARCHAR(n)	Безопасно
CLOB	TEXT	TOAST хранение
DATE	TIMESTAMP	Oracle DATE = timestamp (без TZ)
BLOB	BYTEA	Понадобится преобразование в кодировке
RAW(16)	UUID	Часто используется для surrogate key
LONG	TEXT	Устаревший тип, переписать

3.2 SQL и DDL-конвертация

Индексы:
Oracle:

CREATE INDEX idx1 ON tab(col);

Postgres:

CREATE INDEX idx1 ON tab(col);

Идентично. Но: нет bitmap-индексов. Заменяйте BITMAP на BTREE + WHERE.

Секвенсы и автоинкремент:

Oracle:

CREATE SEQUENCE seq START WITH 100;

Postgres:

CREATE SEQUENCE seq START 100;

-- либо
id SERIAL

-- либо
GENERATED ALWAYS AS IDENTITY

Primary key + default:

id UUID PRIMARY KEY DEFAULT gen_random_uuid()

3.3 Проблемные конструкции Oracle

MERGE

MERGE INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED THEN INSERT;

Postgres:

INSERT INTO target (...)
SELECT ...
ON CONFLICT (id) DO UPDATE SET ...

CONNECT BY

Рекурсивные иерархии:

Oracle:

SELECT * FROM tree START WITH parent_id IS NULL CONNECT BY PRIOR id = parent_id;

Postgres:

WITH RECURSIVE tree(id, parent_id) AS (
  SELECT id, parent_id FROM t WHERE parent_id IS NULL
  UNION ALL
  SELECT t.id, t.parent_id FROM t JOIN tree ON t.parent_id = tree.id
)
SELECT * FROM tree;

4. Миграция PL/SQL в PL/pgSQL

4.1 Удаление пакетов (PACKAGE)

Oracle:

CREATE PACKAGE p_utils AS
  FUNCTION f1(p NUMBER) RETURN NUMBER;
END;

→ Postgres Pro:

создается SCHEMA p_utils
отдельная функция:

CREATE FUNCTION p_utils.f1(p numeric) RETURNS numeric AS $$
BEGIN
  ...
END;
$$ LANGUAGE plpgsql;

4.2 Исключения

Oracle:

BEGIN
  ...
EXCEPTION
  WHEN OTHERS THEN
    ...
END;

Postgres:

BEGIN
  ...
EXCEPTION
  WHEN others THEN
    ...
END;

Но в Postgres нет кода ошибок по названию (например, NO_DATA_FOUND). Используйте SQLSTATE.

5. Перенос данных

5.1 Инструменты

pgloader — лучший выбор для bulk migration
ora2pg — генерирует SQL + отчеты
DBMS_DATAPUMP → CSV → COPY

5.2 Стратегии

для таблиц < 50 млн строк — COPY
для таблиц > 50 млн строк — сегментная загрузка + валидация хешей

5.3 Валидация

COUNT(*)
CHECKSUM_AGG
md5(string_agg(...))

6. Производительность и настройка Postgres Pro

Параметр	Для OLTP	Для DWH
shared_buffers	25% RAM	40–50% RAM
work_mem	4–16 MB	64–256 MB
max_parallel_workers	2–4	8–12
effective_cache_size	50%	75–80%
wal_compression	on	on
jit	on (для аналитики)	on

7. Что нельзя забыть

Обновить все ETL-сценарии (PL/SQL → Python/Airflow/dbt).
Переписать BI-отчеты: заменить синтаксис (например, PIVOT → crosstab()).
Перенастроить JDBC/ODBC-драйверы в приложениях.
Добавить автоматический VACUUM и ANALYZE.
Проверить временные зоны (DATE, TIMESTAMP WITH TIME ZONE).
Включить аудит действий через pgaudit, если нужно соответствие требованиям.

Специфичные функции Oracle, не поддерживаемые напрямую в Postgres Pro

SQL-синтаксис и операторы

Oracle	Аналог / Комментарий
CONNECT BY	WITH RECURSIVE
MERGE INTO	INSERT ... ON CONFLICT ... DO UPDATE
DECODE(col, v1, r1, v2, r2, def)	CASE WHEN col = v1 THEN r1 ... ELSE def END
NVL(expr1, expr2)	COALESCE(expr1, expr2)
DUAL	Таблица dual не нужна — используйте SELECT 1
ROWNUM	LIMIT, ROW_NUMBER()
SYSDATE	CURRENT_TIMESTAMP, NOW()
TO_DATE, TO_CHAR, TO_NUMBER	Использовать ::timestamp, ::text, ::numeric
LISTAGG(...) WITHIN GROUP (...)	string_agg() + ORDER BY внутри подзапроса
PIVOT/UNPIVOT	нет аналога — использовать crosstab() из tablefunc

PL/SQL конструкции

Oracle	Postgres Pro
PACKAGE, PACKAGE BODY	Отсутствуют — заменяется схемой + функциями
%ROWTYPE	Заменяется на RECORD или конкретный TYPE
EXCEPTION WHEN NO_DATA_FOUND	Использовать GET DIAGNOSTICS и SQLSTATE
OUT/INOUT параметр	Поддерживаются, но требуют явного указания
PIPELINED TABLE FUNCTION	Заменяется на RETURNS SETOF
CURSOR FOR LOOP	Используется FOR record IN SELECT ...

Конвертация конкретных PL/SQL функций в PL/pgSQL

Пример: проверка на чётность

Oracle:

CREATE OR REPLACE FUNCTION is_even(p_num NUMBER) RETURN BOOLEAN IS
BEGIN
  RETURN MOD(p_num, 2) = 0;
END;

Postgres Pro:

CREATE OR REPLACE FUNCTION is_even(p_num NUMERIC) RETURNS BOOLEAN AS $$
BEGIN
  RETURN MOD(p_num, 2) = 0;
END;
$$ LANGUAGE plpgsql;

Пример: получение следующего значения из sequence

Oracle:

SELECT my_seq.NEXTVAL INTO v_seq FROM dual;

Postgres Pro:

v_seq := nextval('my_seq');

Пример: обработка исключений

Oracle:

BEGIN
  -- код
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;

Postgres Pro:

BEGIN
  -- код
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE '%', SQLERRM;
END;

Шаблоны миграции ETL и API

3.1. ETL на Oracle → Postgres Pro (через Airflow)

Oracle ETL-пример (PL/SQL):

BEGIN
  INSERT INTO sales_fact SELECT * FROM sales_staging;
  DELETE FROM sales_staging;
END;

Airflow DAG:

from airflow import DAG
from airflow.providers.postgres.operators.postgres import PostgresOperator
from datetime import datetime

with DAG('etl_sales', start_date=datetime(2024, 1, 1), schedule_interval='@daily') as dag:
    load_sales = PostgresOperator(
        task_id='load_sales_fact',
        sql="""
            INSERT INTO sales_fact SELECT * FROM sales_staging;
            DELETE FROM sales_staging;
        """,
        postgres_conn_id='postgres_dwh'
    )

3.2. Миграция API-запросов на Oracle → Postgres

Oracle (через APEX, ORDS):

SELECT * FROM customers WHERE id = :id;

Postgres Pro через REST-сервис:

Бэкенд на Python/Flask или FastAPI:

@app.get("/customers/{customer_id}")
def get_customer(customer_id: int):
    result = db.query("SELECT * FROM customers WHERE id = %s", (customer_id,))
    return dict(result.fetchone())

Или GraphQL/PostgREST, если нужен прямой REST-интерфейс к базе.

3.3. Загрузка данных с сохранением истории (SCD Type 2)

Postgres Pro:

UPDATE dim_customer
SET valid_to = now()
WHERE customer_id = :customer_id
  AND valid_to IS NULL;

INSERT INTO dim_customer (customer_id, name, valid_from, valid_to)
VALUES (:customer_id, :name, now(), NULL);

Автоматизировать через dbt или sqlfluff с шаблонизатором jinja2.

Миграция с Oracle требует глубокой ревизии PL/SQL, бизнес-логики, ETL и API. Тем не менее, Postgres Pro позволяет с высокой степенью совместимости реализовать даже сложные сценарии, если:

использовать SQL-аналоги;
грамотно проектировать архитектуру (параллелизм, разделение логики);
перейти к современным инструментам orchestration и API.

Практические кейсы миграции с Oracle на Postgres Pro: проблемы и решения

Кейс 1: Замена PIVOT-отчетов в BI при отсутствии PIVOT в Postgres Pro

Контекст:
В Oracle BI использовалась динамическая сводка по услугам:

SELECT * FROM (
  SELECT client_id, service_name, amount FROM sales
)

PIVOT (SUM(amount) FOR service_name IN ('Internet', 'TV', 'Mobile'));

Проблема:
Postgres Pro не поддерживает PIVOT напрямую.

Решение:

Использовать функцию crosstab() из расширения tablefunc:

SELECT * FROM crosstab(
  $$SELECT client_id, service_name, SUM(amount)
    FROM sales GROUP BY client_id, service_name$$,
  $$VALUES ('Internet'), ('TV'), ('Mobile')$$
) AS ct(client_id INT, internet NUMERIC, tv NUMERIC, mobile NUMERIC);

Для динамического набора колонок — формировать SQL в приложении или BI-уровне (например, в dbt).

Кейс 2: Нестабильная работа ETL после замены MERGE

Контекст:
ETL-скрипт обновлял dim_products из stg_products с помощью MERGE:

MERGE INTO dim_products d
USING stg_products s ON (d.id = s.id)
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...

Проблема:
MERGE не поддерживается в PostgreSQL. Простая замена INSERT ON CONFLICT работала неправильно при наличии сложных условий обновления.

Решение:
Разбить на 2 отдельных шага:

UPDATE dim_products
SET name = s.name,
    category = s.category
FROM stg_products s
WHERE dim_products.id = s.id
  AND (
    dim_products.name IS DISTINCT FROM s.name
    OR dim_products.category IS DISTINCT FROM s.category
);

INSERT INTO dim_products(id, name, category)
SELECT id, name, category
FROM stg_products s
WHERE NOT EXISTS (
  SELECT 1 FROM dim_products d WHERE d.id = s.id
);

Преимущества:

Прозрачно, читаемо, легко контролировать.
Работает с любой логикой — даже если обновление только по части условий.

Кейс 3: Проблема с CONNECT BY и иерархиями

Контекст:
Иерархическая структура регионов строилась в Oracle через:

SELECT region_id, region_name, LEVEL FROM regions
START WITH parent_id IS NULL
CONNECT BY PRIOR region_id = parent_id;

Проблема:
Postgres Pro не поддерживает CONNECT BY.

Решение:
Переписать на WITH RECURSIVE:

WITH RECURSIVE region_tree AS (
  SELECT region_id, region_name, parent_id, 1 AS level
  FROM regions
  WHERE parent_id IS NULL
  UNION ALL
  SELECT r.region_id, r.region_name, r.parent_id, t.level + 1
  FROM regions r
  JOIN region_tree t ON r.parent_id = t.region_id
)
SELECT * FROM region_tree;

Бонус: можно использовать CTE повторно в других запросах.

Кейс 4: Ошибка при работе с VARCHAR2(4000) и CLOB

Контекст:
Приложение сохраняло тексты большого объема (до 15 000 символов) в CLOB.

Проблема:
Postgres по умолчанию ограничивает VARCHAR до 10 485 и TOAST-объекты могут загружать I/O.

Решение:

Заменить CLOB на TEXT.
Проверить и убрать CHARACTER SET в приложении.
Добавить GIN-индекс по to_tsvector() для полнотекстового поиска:

CREATE INDEX idx_docs_search ON documents USING GIN(to_tsvector('russian', content));

Кейс 5: Некорректный перенос UUID из RAW(16)

Контекст:
Oracle хранил UUID в поле RAW(16), а приложения сравнивали в HEX.

Проблема:
Postgres хранил UUID в текстовом виде, что нарушало совместимость в API.

Решение:

При экспорте из Oracle использовать:

SELECT RAWTOHEX(uuid_col) FROM table;

При импорте в Postgres преобразовывать через:

SELECT decode('D420A8...', 'hex')::uuid;

В Postgres всегда хранить UUID в виде uuid, а на внешнем уровне обеспечивать сериализацию в нужный формат (hex/base64).

Кейс 6: Неудачная попытка прямой миграции BI-отчетов

Контекст:
В Oracle BI было ~200 отчетов с PL/SQL функциями (например, агрегаты по фильтру внутри SELECT).

Проблема:
После миграции BI-инструменты (Power BI) не поддерживали вызовы UDF Postgres из DirectQuery.

Решение:

Перенести логику в представления (CREATE VIEW).
Для heavy aggregation — использовать MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW monthly_orders AS
SELECT customer_id, date_trunc('month', order_date) AS month, SUM(amount)
FROM orders
GROUP BY customer_id, date_trunc('month', order_date);

BI настраивается на SELECT * FROM monthly_orders.

Подход к тестированию миграции по слоям DWH

Цель

Убедиться, что:

все данные перенесены корректно;
все вычисления и бизнес-логика работают идентично (или лучше);
производительность приемлема;
результат совпадает с ожидаемым для бизнеса и BI-систем.

Структура слоев DWH и стратегия тестирования

Слой	Тип тестов	Основные проверки
Staging	Синтаксические, контроль целостности	Кол-во строк, пустые поля, типы, null-ы
Core/Raw Vault	Сопоставление записей	Проверка ключей, ссылок, валидность источников
Business Vault	Логические тесты	Агрегации, флаги актуальности, мастер-записи
Data Marts	BI-проверка, регрессионные	Итоги по витринам, сравнение отчетов
BI (Power BI, Tableau)	Визуальные, смок-тесты	Сравнение графиков, фильтров, drill-down

Подходы к тестированию на каждом слое

1. Staging

Что тестировать:

Количество строк в каждой таблице (Oracle vs Postgres)
Наличие обязательных полей (NOT NULL)
Типы данных
Форматы дат

Методика:

-- Oracle
SELECT COUNT(*) FROM stg_customers;

-- Postgres
SELECT COUNT(*) FROM stg_customers_pg;

-- сравнение md5 хэшей
SELECT md5(string_agg(col1 || col2 || ..., '')) FROM stg_table;

2. Core / Raw Vault

Что тестировать:

Хабы, ссылки и сателлиты
Уникальность бизнес-ключей
Историчность (valid_from, valid_to)
Источники (source_system)

Примеры:

-- Дубликаты бизнес-ключей
SELECT customer_id, COUNT(*) FROM hub_customer GROUP BY customer_id HAVING COUNT(*) > 1;

-- Пустые sat по ключам
SELECT hk_customer FROM hub_customer
LEFT JOIN sat_customer ON hub_customer.hk_customer = sat_customer.hk_customer
WHERE sat_customer.hk_customer IS NULL;

3. Business Vault

Что проверять:

Флаги «актуальных» строк (is_current)
Расчётные поля (например, суммы, статусы)
Запросы с оконными функциями, ранжированием

Методика:

Сравнение бизнес-вычислений по ID между Oracle и Postgres
Проверка на дубликаты и несовпадения значений:

-- сравнение агрегаций
SELECT customer_id, SUM(order_amt) FROM bv_orders GROUP BY customer_id;

4. Data Marts / Витрины

Что тестировать:

Итоги по продажам, остаткам, показателям
Сравнение по дате/категориям/каналам
Проверка разницы на уровне процентов

Методика:

Таблица со сравнением метрик:

SELECT
  d.customer_id,
  d.sales_total_pg,
  o.sales_total_oracle,
  ABS(d.sales_total_pg - o.sales_total_oracle) AS delta
FROM dwh_pg.sales_mart d
JOIN dwh_oracle.sales_mart o ON d.customer_id = o.customer_id
WHERE ABS(d.sales_total_pg - o.sales_total_oracle) > 0.01;

5. BI / визуализация

Что тестировать:

Сводные отчеты по показателям
Работа фильтров
Drill-down и отображение текущего/исторического состояния
Производительность (время построения отчета)

Методика:

Визуальная сверка отчетов Oracle BI ↔ Power BI/Postgres
Инструментальные сравнения:
- Power BI Performance Analyzer
- JMeter/Gatling для API-запросов
- Screenshot сравнение для графиков

Категории тестов

Тип теста	Цель
Row Count Test	Совпадение количества строк
Checksum Test	Идентичность данных по полям
Column-by-Column	Точность соответствия значений
Aggregate Match	Суммы, средние, минимумы, максимумы
Business Logic	Верификация флагов, сегментов, групп
Data Freshness	Обновление за нужный период
Performance Test	Анализ времени выполнения запроса

Инструменты и автоматизация

Инструмент	Назначение
dbt tests	Тесты на уникальность, nulls
Great Expectations	Тесты данных, документация
Python + Pandas	Сравнение агрегаций и выборок
pgbench	Нагрузочное тестирование
SQL export diff	Сравнение выборок CSV

Ошибки, которые стоит ловить

Дубликаты бизнес-ключей в HUB
Несогласованная история в SAT (дубли, дырки)
NULL там, где их быть не должно
Несоответствие дат в valid_from и load_dts
Ошибки округления/типа (например, float → numeric)
Потери строк из-за неучтённого LEFT JOIN

Финальный чек-лист для запуска в прод

Элемент	Готово?
Проверены row count на всех слоях	✓
Проверена корректность бизнес-логики	✓
BI-отчеты работают и сопоставимы	✓
Производительность не хуже Oracle	✓
Аудит логов загрузки и ошибок	✓
Команда тестирования подтверждает результат	✓

Postgres Professional — это российская промышленная СУБД, созданная на базе открытого PostgreSQL, но значительно расширенная для корпоративного применения. В отличие от классического PostgreSQL, решения от Postgres Professional включают в себя поддержку российских ГОСТов и сертификацию ФСТЭК, повышенную надёжность, оптимизации под высоконагруженные системы (в том числе 1С и DWH), инструменты резервного копирования, мониторинга и отказоустойчивости. За платформой стоит команда ядра PostgreSQL в России, что гарантирует актуальность, стабильность и экспертную техническую поддержку 24/7.

Для компаний, которым важно не просто использовать PostgreSQL, а внедрить его на уровне корпоративных стандартов — с гарантией, сопровождением, документированными улучшениями и адаптацией под российское законодательство — Postgres Pro Enterprise становится логичным выбором. Это не просто бесплатная база данных, а полноценный продуктовый стек, совместимый с BI, аналитикой, ERP, 1С и другими системами, в том числе импортозамещёнными.

← Предыдущая статья

Следующая статья →

Эксплуатация Postgres Pro, Обновление Postgres Pro, поддержка