Migracja z Oracle do PostgreSQL: co się przeniesie, co trzeba przepisać, ile to kosztuje

Migracja Oracle → PostgreSQL to jeden z najczęstszych projektów redukcji kosztów licencyjnych w firmach — i jeden z najczęściej źle wycenianych. Rozkładamy go na czynniki pierwsze: co przechodzi łatwo, co boli, ile to trwa.

Przeszliśmy realną migrację: postawiliśmy bazę Oracle ze schematem, danymi i kodem PL/SQL, a następnie przenieśliśmy ją do PostgreSQL 16 trzema metodami, skonfigurowaliśmy wysoką dostępność i zmierzyliśmy wydajność przed i po tuningu. Wszystkie zrzuty kodu i wyników pochodzą z prawdziwych uruchomień na żywych bazach — nic nie jest zmyślone.

W skrócie (TL;DR)

Schemat i dane migrują się niemal automatycznie (ora2pg). Prawdziwa praca to logika w PL/SQL: pakiety, transakcje autonomiczne, kolumny wirtualne, partycjonowanie. ora2pg sam ocenił naszą migrację na poziom B-5 (trudna, do 5 dni pracy). Po stronie wydajności PostgreSQL — po nastrojeniu konfiguracji (tuningu, czyli dostrojeniu ustawień serwera) — dorównuje Oracle lub go wyprzedza na 5 z 6 mierzonych zapytań. Ale domyślne ustawienia są celowo zachowawcze, a jedno źle napisane zapytanie w ogóle się u nas nie kończyło (przerywaliśmy je po 150 sekundach) — dopóki nie przepisaliśmy go tak, by liczyło się w ćwierć sekundy.

Migracja to nie „przepięcie parametrów połączenia”

Hasło „Oracle i PostgreSQL to przecież ten sam SQL” jest prawdziwe mniej więcej w 80%. Te 80% — tabele, indeksy, widoki, większość zapytań — przenosi się niemal automatycznie. Pozostałe 20% decyduje o tym, czy projekt trwa tydzień, czy kwartał. Składają się na nie: kod PL/SQL, pakiety, transakcje autonomiczne, sekwencje, partycjonowanie i typy danych przechowujące czas (oracle’owy DATE to data wraz z godziną). Do tego dochodzi cała warstwa administracyjna: kopie zapasowe, replikacja i wysoka dostępność.

Zamiast teoretyzować, zbudowaliśmy realną bazę źródłową i przenieśliśmy ją na żywo. Każdy zrzut poniżej to prawdziwe wyjście z konsoli — łącznie z błędami, które trzeba było obejść.

Środowisko testowe: „Nordpol”

Baza źródłowa to Oracle Database Free w Dockerze (etykieta „26ai” to aktualizacja z 2026 r. na rodzinie 23ai — ciąg wersji 23.26.2.0.0 — nie odrębna generacja silnika). Schemat NORDPOL to własny schemat demonstracyjny zbudowany od zera na potrzeby tego artykułu — żaden ze standardowych przykładowych schematów Oracle (HR, SCOTT, SH) nie skupia wszystkich typowych pułapek migracyjnych w jednym miejscu. Celowo używa konstrukcji charakterystycznych dla Oracle, by wywołać wszystkie typowe pułapki migracyjne:

• tabele z kolumnami IDENTITY (styl 12c+) i osobno z sekwencją + triggerem (styl pre-12c),
• kolumny wirtualne (wyliczane line_total = quantity * unit_price * (1 - discount)),
• tabelę faktów partycjonowaną zakresowo po dacie — 100 000 zamówień, 300 000 pozycji,
• pakiet pkg_sales, transakcję autonomiczną do audytu, widok hierarchiczny (CONNECT BY),
• materialized view, global temporary table, typ obiektowy, MERGE oraz funkcje NVL/NVL2/DECODE.

Uwaga dla użytkowników 11g / 18c / 21c: cały przewodnik dotyczy każdej aktualnej wersji Oracle — różnice charakterystyczne dla starszych baz (złączenia stylem (+), ROWNUM, brak kolumn IDENTITY, CONNECT BY) omówione są osobno w sekcji Migracja ze starszych wersji Oracle.

-- Oracle: kolumna wirtualna + tabela partycjonowana zakresowo po dacie
CREATE TABLE order_items (
  order_id   NUMBER(12) NOT NULL,
  line_no    NUMBER(4)  NOT NULL,
  product_id NUMBER(8)  NOT NULL,
  quantity   NUMBER(8)  NOT NULL,
  unit_price NUMBER(10,2) NOT NULL,
  discount   NUMBER(4,3) DEFAULT 0,
  line_total NUMBER(14,2) GENERATED ALWAYS AS (quantity*unit_price*(1-discount)) VIRTUAL,
  CONSTRAINT pk_order_items PRIMARY KEY (order_id, line_no)
);

CREATE TABLE orders (
  order_id NUMBER(12) PRIMARY KEY, customer_id NUMBER(10), order_date DATE NOT NULL, ...
) PARTITION BY RANGE (order_date) (
  PARTITION p2024   VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION p2025q1 VALUES LESS THAN (DATE '2025-04-01'), ... );

Schemat testowy NORDPOL — 10 tabel, 407 538 wierszytabela faktów (czerwona) + tabele wymiarowe · strzałki = klucze obceregions5 wierszycountries13 wierszycustomers5 000 wierszyemployees1 000 wierszydepartments20 wierszyproducts500 wierszyinventory1 000 wierszyorders100 000 wierszyorder_items300 000 wierszyaudit_log— wierszy6 partycjikol. wirtualna

Schemat NORDPOL: tabela faktów order_items (300 tys. wierszy) i tabele wymiarowe, powiązane kluczami obcymi. orders jest partycjonowana, a order_items ma kolumnę wirtualną.

⇓
Pobierz schemat NORDPOL — gotowy dump Oracle SQL

Pełny dump schematu NORDPOL gotowy do zaimportowania na własnej instancji Oracle. Zawiera DDL wszystkich obiektów (tabele, sekwencje, widoki, pakiet, triggery, MV, GTT, typ obiektowy) oraz dane — 407 538 wierszy INSERTów. Użyj go, żeby wiernie powtórzyć każdy krok tego przewodnika na swojej bazie.

nordpol_oracle.sql.gz
5,9 MB (skompresowany) · 68,9 MB po rozpakowaniu
format SQL*Plus, Oracle 11g–26ai

Jak użyć:  gunzip nordpol_oracle.sql.gz, następnie:

sqlplus nordpol/nordpol123@HOST:1521/XE @nordpol_oracle.sql

Instrukcja CREATE USER z uprawnieniami jest w komentarzu na początku pliku.

⇓ Pobierz nordpol_oracle.sql.gz — 5,9 MB

Czym migrować — przegląd narzędzi

Rynek narzędzi do migracji Oracle→PostgreSQL jest dojrzały. W praktyce używa się jednego z trzech podejść (albo ich kombinacji):

Trzy drogi migracji Oracle -> PostgreSQLOraclebaza zrodlowatabele + PL/SQLora2pgschemat + daneautomat + raportoracle_fdwzywy dostepmigracja przyrostowarecznie / SQLPL/pgSQLpakiety, autonom.PostgreSQLbaza docelowa16Ocena migracji (ora2pg): poziom B-5 - wymaga przepisania pakietow, transakcji autonomicznych i kolumn wirtualnych.

Trzy drogi migracji zastosowane w tym artykule: ora2pg (schemat+dane), oracle_fdw (na żywo) i ręczne przepisanie PL/SQL.

ora2pg w praktyce

Instalacja (haczyk: klient Oracle)

Sam ora2pg to pakiet Perla. Pułapka tkwi w sterowniku DBD::Oracle, który wymaga Oracle Instant Client (sterowniki Perla do Oracle nie są w repozytoriach dystrybucji):

# 1) Instant Client (basic + sdk + sqlplus) -> /opt/oracle
# 2) sterownik Perla zbudowany pod ten klient
export ORACLE_HOME=/opt/oracle/instantclient_21_14
export LD_LIBRARY_PATH=$ORACLE_HOME
cpanm DBD::Oracle           # kompiluje sie pod Instant Client
apt-get install -y ora2pg   # sam ora2pg jest w repo

$ ora2pg --version
Ora2Pg v25.0

Raport oceny migracji — ile to naprawdę kosztuje

Zanim cokolwiek przeniesiesz, uruchom ora2pg -t SHOW_REPORT --estimate_cost. To jedyne narzędzie, które poda Ci liczbowy szacunek pracochłonności w dniach pracy programisty. W praktyce ora2pg czyta parametry połączenia z pliku ora2pg.conf. Minimalna konfiguracja, która wskazuje schemat i dane połączenia:

# ora2pg.conf
ORACLE_DSN    dbi:Oracle:host=<host>;port=1521;sid=XE
ORACLE_USER   NORDPOL
ORACLE_PWD    <hasło>
SCHEMA        NORDPOL

Jeżeli baza to PDB (Oracle 12c+), zamień sid=XE na service_name=ORCLPDB1. Raport uruchamiasz z flagą -c:

$ ora2pg -c ora2pg.conf -t SHOW_REPORT --estimate_cost

Dla naszego schematu NORDPOL raport zwraca:

ora2pg -t SHOW_REPORT --estimate_cost

-------------------------------------------------------------------------------
Ora2Pg v25.0 - Database Migration Report
-------------------------------------------------------------------------------
Version  Oracle AI Database 26ai Free Release 23.26.2.0.0
Schema   NORDPOL
Size     86.94 MB
-------------------------------------------------------------------------------
Object              Number  Invalid  Estimated cost  Comments
-------------------------------------------------------------------------------
GLOBAL TEMPORARY..  1       0        10.00   not supported in PostgreSQL
PACKAGE BODY        1       0        15.30   3 procedures/functions in package
PROCEDURE           1       0         4.60   log_event (autonomous transaction)
FUNCTION            2       0         8.50   fn_emp_fullname, fn_discount_tier
TABLE              10       0         1.30   4 IDENTITY columns, 3 CHECK constraints
TABLE PARTITION     6       0         1.00   RANGE partitioning
TRIGGER             1       0         4.00   trg_customers_bi
MATERIALIZED VIEW   1       1         0.00   only complete refresh
TYPE                1       0         1.00   object type
-------------------------------------------------------------------------------
Migration level : B-5
-------------------------------------------------------------------------------
  B = migration with code rewrite, up to 5 days of work
  5 = difficult: functions/triggers require manual rewriting

Jak czytać poziom migracji

Litera (A/B/C) mówi o pracochłonności, cyfra (1–5) o technicznej trudności. A-1 to migracja „jednym kliknięciem”. Nasze B-5 oznacza: dane i schemat pójdą automatem, ale pakiet, transakcję autonomiczną i kolumny wirtualne trzeba przepisać ręcznie.

Na co idzie budżet migracji (raport ora2pg)jednostki kosztu per typ obiektu · suma 51.8 = poziom B-5 (~1 dzień pracy)PACKAGE BODY (pakiet)15.3GLOBAL TEMP TABLE10.0FUNCTION (funkcje)8.5PROCEDURE (autonom.)4.6TRIGGER4.0INDEX (indeksy)2.7VIEW (widoki)2.4TABLE (tabele)1.3TABLE PARTITION1.0SEQUENCE1.0TYPE (typ obiekt.)1.0ręczne przepisanie (PL/SQL)automat (schemat/dane)

Ten sam raport pokazany wizualnie: ponad 80% szacowanego kosztu to kod PL/SQL (pakiet, procedura, funkcje, trigger), a nie schemat czy dane — dokładnie tam idzie budżet migracji.

Co ora2pg konwertuje sam — i robi to dobrze

Eksport DDL tabel uruchamiasz typem TABLE. Flaga -o podaje nazwę pliku wyjściowego; bez niej ora2pg pisze na stdout:

$ ora2pg -c ora2pg.conf -t TABLE -o table.sql

wyjście terminala

[ora2pg] Looking for TABLE definition...
[ora2pg] Exporting TABLE...
[ora2pg] Extracting objects (TABLE AUDIT_LOG, COUNTRIES, CUSTOMERS, DEPARTMENTS,
          EMPLOYEES, INVENTORY, ORDER_ITEMS, ORDERS, PRODUCTS, REGIONS)...
Done. 10 TABLE(s) found.
Skipping file table.sql, generating: table.sql

Wygenerowany table.sql zawiera gotowe CREATE TABLE przetłumaczone na PostgreSQL. Zwróć uwagę na trzy rzeczy: IDENTITY zachowane 1:1, kolumna wirtualna Oracle stała się GENERATED ... STORED, a oracle'owy DATE trafnie odwzorowano na timestamp(0) — zero w nawiasie oznacza brak ułamków sekundy, czyli dokładnie tę rozdzielczość, jaką ma oracle'owy DATE (data i czas do sekundy):

-- fragment table.sql wygenerowanego przez ora2pg
CREATE TABLE employees (
  employee_id bigint GENERATED BY DEFAULT AS IDENTITY,
  first_name  varchar(40) NOT NULL,
  last_name   varchar(40) NOT NULL,
  full_name   varchar(82) GENERATED ALWAYS AS (FIRST_NAME||' '||LAST_NAME) STORED,  -- VIRTUAL -> STORED
  hire_date   timestamp(0) NOT NULL DEFAULT statement_timestamp(),                  -- DATE -> timestamp
  salary      decimal(10,2), commission decimal(5,2),
  PRIMARY KEY (employee_id)
);
CREATE INDEX ix_emp_uname ON employees (upper(last_name));  -- indeks funkcyjny zachowany

Pułapka #1: kolumna wirtualna nie jest już wirtualna

Oracle przelicza kolumnę VIRTUAL dopiero przy odczycie (nie zajmuje miejsca w tabeli). W użytej tu wersji 16 PostgreSQL ma tylko GENERATED ... STORED — wartość jest fizycznie zapisywana, więc tabela urośnie na dysku. Kolumny liczone wirtualnie dodał dopiero PostgreSQL 18; jeśli migrujesz na 18 lub nowszą, odwzorujesz zachowanie Oracle bez kompromisu. (W Oracle kolumnę wirtualną można też zaindeksować — wtedy jej wartości trafiają do indeksu — oraz partycjonować po niej.)

Pułapka #2: klucz główny tabeli partycjonowanej

ora2pg wygenerował orders z PRIMARY KEY (order_id) i PARTITION BY RANGE (order_date). Na Oracle to legalne. PostgreSQL odmawia — i ma rację:

psql -f table.sql (ora2pg)

psql:table.sql:96: ERROR:  unique constraint on partitioned table must
  include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "orders" lacks column
  "order_date" which is part of the partition key.

W PostgreSQL każdy klucz unikalny tabeli partycjonowanej musi zawierać kolumnę partycjonującą. Poprawka to dopisanie order_date do PK:

-- przed:  PRIMARY KEY (order_id)
-- po:
PRIMARY KEY (order_id, order_date)

Pułapka #3: klucz obcy do tabeli partycjonowanej

Klucze obce wskazujące na tabelę partycjonowaną działają od PostgreSQL 12 — problem nie polega na braku wsparcia, tylko na tym, że FK musi odwzorować pełny klucz unikalny rodzica. Skoro PK to teraz (order_id, order_date), klucz obcy z order_items(order_id) nie ma do czego się odwołać (brakuje order_date). Albo dokładasz kolumnę partycji do tabeli-dziecka, albo rezygnujesz z deklaratywnego FK — warto o tym wiedzieć przed migracją.

Ładowanie danych — szybko i z pominięciem kolejności FK

ora2pg strumieniuje dane bezpośrednio z Oracle do PostgreSQL typem COPY. Haczyk: domyślna kolejność tabel potrafi naruszać FK (np. countries trafia do bazy przed regions). Rozwiązanie — trzy niezależne komendy:

Krok 1 — schemat bez FK (typy TABLE i SEQUENCE nie eksportują FK)

$ ora2pg -c ora2pg.conf -t SEQUENCE -o sequence.sql
$ ora2pg -c ora2pg.conf -t TABLE    -o table.sql
$ psql -h pg_host -U nordpol nordpol_pg -f sequence.sql
$ psql -h pg_host -U nordpol nordpol_pg -f table.sql

Krok 2 — dane (streaming Oracle → PostgreSQL, 407 tys. wierszy w 21 s)

$ ora2pg -c ora2pg.conf -t COPY | psql -h pg_host -U nordpol nordpol_pg

ora2pg -t COPY | psql

[ora2pg COPY] 407538/407538 rows (100.0%) on total estimated data
              (21 sec., avg: 19406 recs/sec)

-- kontrola liczebnosci po stronie PostgreSQL:
      t      | count
-------------+--------
 order_items | 300001
 orders      | 100001
 customers   |   5000
 employees   |   1000

Krok 3 — klucze obce (typ FKEY, dopiero po danych)

$ ora2pg -c ora2pg.conf -t FKEY -o fkey.sql
$ psql -h pg_host -U nordpol nordpol_pg -f fkey.sql

Pułapka #4: sekwencje i IDENTITY po załadowaniu danych

Klasyczny problem, o który potyka się wiele migracji. Po wgraniu danych z jawnymi wartościami kluczy (np. order_id = 1..100000) sekwencje nadal startują od 1. Pierwsza próba wstawienia nowego rekordu kończy się kolizją klucza. Trzeba przestawić zarówno zwykłe sekwencje, jak i sekwencje kolumn IDENTITY na najwyższą użytą wartość:

-- sekwencje jawne
SELECT setval('seq_orders',    (SELECT max(order_id)    FROM orders));
SELECT setval('seq_customers', (SELECT max(customer_id) FROM customers));
-- sekwencje kolumn IDENTITY
SELECT setval(pg_get_serial_sequence('audit_log','log_id'),
              (SELECT max(log_id) FROM audit_log));

Czego ora2pg nie zrobi za Ciebie

Tu zaczyna się prawdziwa robota — czyli te dni pracy, które ora2pg sam sobie zarezerwował w wycenie. Poniższy podział pokazuje, co przeszło automatem, a co trzeba tknąć ręcznie:

Co migruje samo, a co trzeba przepisać ręczniereguła 80/20: schemat i dane automatem, logika PL/SQL ręcznieMIGRUJE 1:1 (automat)WYMAGA PRZEPISANIA✓tabele, indeksy (też funkcyjne)✓widoki, sekwencje✓IDENTITY (12c+)✓MERGE (PG 15+)✓NVL → COALESCE✓DECODE / NVL2 → CASE✓(+) → LEFT JOIN✓DATE → timestamp⚒pakiet → schemat + funkcje⚒transakcja autonomiczna → dblink⚒kolumna VIRTUAL → STORED⚒PK tabeli partycjonowanej⚒GLOBAL TEMPORARY TABLE⚒CONNECT BY → WITH RECURSIVE⚒typ obiektowy (metody)⚒SYSDATE → LOCALTIMESTAMP

Reguła 80/20 migracji: lewa kolumna idzie automatem, prawa wymaga przepisania kodu.

Cztery najważniejsze z tych ręcznych konstrukcji omawiamy dalej z konkretnym kodem.

Pakiet → schemat + funkcje

PostgreSQL nie ma pakietów. Standardowy wzorzec: pakiet staje się schematem o tej samej nazwie, a jego procedury/funkcje — zwykłymi funkcjami w tym schemacie. Zmienna stanu pakietu (np. g_last_order_id) nie ma odpowiednika — używa się zmiennej sesyjnej (set_config/current_setting):

-- Oracle: pkg_sales.get_customer_balance(...)
-- PostgreSQL: schemat pkg_sales + funkcja
CREATE SCHEMA pkg_sales;
CREATE OR REPLACE FUNCTION pkg_sales.get_customer_balance(p_cust_id bigint)
RETURNS numeric LANGUAGE sql STABLE AS $$
  SELECT COALESCE(SUM(oi.line_total),0)            -- NVL -> COALESCE
    FROM orders o JOIN order_items oi ON oi.order_id=o.order_id
   WHERE o.customer_id=p_cust_id AND o.status <> 'CANCELLED';
$$;

Transakcja autonomiczna → dblink

To jedna z najczęstszych blokad migracji. PRAGMA AUTONOMOUS_TRANSACTION pozwala procedurze zatwierdzić własne zmiany niezależnie od transakcji nadrzędnej (typowo: audyt/logowanie). PostgreSQL nie ma tego mechanizmu wbudowanego — standardowe obejście to osobne połączenie przez dblink, które commituje samodzielnie:

CREATE EXTENSION IF NOT EXISTS dblink;
CREATE OR REPLACE FUNCTION log_event(p_table text, p_action text, p_details text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  PERFORM dblink_exec(                       -- osobne polaczenie = niezalezny commit
    'dbname=nordpol_pg host=localhost user=nordpol password=...',
    format('INSERT INTO audit_log(table_name,action,details) VALUES (%L,%L,%L)',
           p_table, p_action, p_details));
END; $$;

Dwie uwagi praktyczne. Po pierwsze, hasło wpisane wprost w treść funkcji (jak wyżej, dla czytelności) to antywzorzec — w produkcji użyj pliku .pgpass albo serwera dblink z mapowaniem użytkownika. Po drugie, dblink to nie jedyna droga: rozszerzenie pg_background realizuje to samo przez proces roboczy w tle, bez nowego połączenia i bez hasła w kodzie. dblink wybiera się często dlatego, że jest w standardowej dystrybucji.

CONNECT BY → WITH RECURSIVE

Hierarchiczne CONNECT BY (i SYS_CONNECT_BY_PATH) zamienia się na rekurencyjne wspólne wyrażenie tablicowe (CTE) — czytelniejsze i przenośne (standard SQL):

-- Oracle: START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id
CREATE VIEW v_org_chart AS
WITH RECURSIVE org AS (
  SELECT employee_id, manager_id, full_name, 1 AS depth, '/'||last_name AS path
    FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.full_name, o.depth+1, o.path||'/'||e.last_name
    FROM employees e JOIN org o ON e.manager_id = o.employee_id)
SELECT employee_id, lpad(' ',(depth-1)*2)||full_name AS org_name, depth, path FROM org;

Uwaga: rekurencyjne CTE nie ma gotowych odpowiedników wszystkich pseudokolumn CONNECT BY (CONNECT_BY_ROOT, CONNECT_BY_ISLEAF, NOCYCLE). Proste hierarchie przenoszą się 1:1, ale przy wykrywaniu cykli czy oznaczaniu liści trzeba dopisać logikę (np. nieś w CTE listę odwiedzonych węzłów, by zabezpieczyć się przed pętlą).

Dobra wiadomość: MERGE działa

Polecenie MERGE (czyli upsert — wstaw albo zaktualizuj) długo było argumentem „za Oracle”. Od PostgreSQL 15 jest natywne — naszą procedurę recalc_inventory (prosty upsert) przenieśliśmy 1:1. (Bardziej złożony oracle’owy MERGE z klauzulą DELETE lub RETURNING może wymagać korekty — RETURNING dla MERGE doszedł w PostgreSQL dopiero w wersji 17.) Mechaniczne są też NVL→COALESCE i NVL2/DECODE→CASE.

Pułapka: SYSDATE to nie CURRENT_DATE

Kusi, żeby oracle’owe SYSDATE podmienić na CURRENT_DATE — i to błąd. SYSDATE zwraca datę z godziną, a CURRENT_DATE w PostgreSQL — samą datę, więc po cichu obcina czas (dokładnie ta sama pułapka co przy DATE→date). Poprawny odpowiednik to LOCALTIMESTAMP (albo statement_timestamp()/clock_timestamp() zależnie od potrzeby).

Walidacja: te same dane, te same wyniki

Migracja jest warta tyle, ile jej walidacja. Łączymy się z docelową bazą przez psql:

bash

$ psql -h pg_host -U nordpol nordpol_pg

Uruchamiamy identyczny zestaw sprawdzeń co na Oracle — pakiet pkg_sales, funkcję fn_emp_fullname, widok v_customer_tiers i wpis w audit_log. Wyniki są co do grosza takie same — łącznie z saldem klienta liczonym przez przeniesiony pakiet, hierarchią z rekurencyjnego CTE i audytem zapisanym przez „transakcję autonomiczną” na dblink:

psql nordpol_pg

=== pakiet: place_order (CALL) + get_customer_balance ===
CALL pkg_sales.place_order(10, 5, 3, 7, NULL);   -- p_order_id = 100102
 saldo_klienta_10 
------------------
       2012249.66        <- identycznie jak na Oracle

=== fn_emp_fullname (CASE zamiast NVL2) ===
      emp4           |      emp7
--------------------------+-----------------
 Imie4 Nazwisko4 (+5.75%) | Imie7 Nazwisko7

=== audit_log (transakcja autonomiczna przez dblink) ===
 ORDERS | INSERT | order 100102 for cust 10

=== tier klientow (fn_discount_tier w widoku) ===
   tier   | klientow 
----------+----------
 PLATYNA  |     4927
 ZLOTO    |       33
 SREBRO   |       30
 STANDARD |       10

oracle_fdw: migracja na żywo i automatyczna walidacja

Dla dużych baz nie zawsze chcemy przenosin na raz (metodą „wielkiego wybuchu”, z ang. big bang). oracle_fdw montuje tabele Oracle jako tabele obce w PostgreSQL — można je odpytywać na żywo, ładować przyrostowo (INSERT ... SELECT z tabeli obcej) i — co bardzo wygodne — porównać liczbę wierszy po obu stronach jednym zapytaniem:

CREATE EXTENSION oracle_fdw;
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver '//localhost:1521/FREEPDB1');
CREATE USER MAPPING FOR nordpol SERVER oradb
  OPTIONS (user 'NORDPOL', password '...');
IMPORT FOREIGN SCHEMA "NORDPOL" LIMIT TO (orders, order_items, customers)
  FROM SERVER oradb INTO ora_src;

Po zaimportowaniu schemy Oracle jako tabele obce w schemacie ora_src możemy jednym zapytaniem porównać liczbę wierszy po obu stronach:

-- psql nordpol_pg
SELECT tabela, pg, oracle
FROM (
  SELECT 'orders'      AS tabela,
         (SELECT count(*) FROM orders)               AS pg,
         (SELECT count(*) FROM ora_src.orders)       AS oracle
  UNION ALL
  SELECT 'order_items',
         (SELECT count(*) FROM order_items),
         (SELECT count(*) FROM ora_src.order_items)
  UNION ALL
  SELECT 'customers',
         (SELECT count(*) FROM customers),
         (SELECT count(*) FROM ora_src.customers)
) t;

psql nordpol_pg (oracle_fdw)

=== Walidacja zgodnosci: PostgreSQL vs Oracle (przez FDW) ===
   tabela    |   pg   | oracle 
-------------+--------+--------
 orders      | 100001 | 100001
 order_items | 300001 | 300001
 customers   |   5000 |   5000

Kodowanie znaków — ustaw od razu

Bazę docelową twórz w UTF8 (Oracle zwykle ma AL32UTF8). Jeśli utworzysz ją jako SQL_ASCII, oracle_fdw ostrzeże, że „wszystkie znaki poza ASCII zostaną utracone” — i polskie ogonki rzeczywiście przepadną.

Ciche pułapki danych, które rozjeżdżają wyniki po cutoverze

Najgroźniejsze błędy migracji nie wywalają ładowania — one przechodzą bez słowa i ujawniają się dopiero, gdy aplikacja zaczyna zwracać inne wyniki niż na Oracle. Cztery, które widuje się najczęściej:

Pułapka #5 (najważniejsza): pusty string to nie NULL

W Oracle pusty ciąg '' jest tożsamy z NULL — to ewenement na tle standardu SQL. W PostgreSQL '' to poprawna, niepusta wartość, różna od NULL. Skutki są podstępne: zapytania WHERE col IS NULL przestają łapać rekordy, które w Oracle były „puste”; konkatenacja NULL||'x' daje w Oracle 'x', a w PostgreSQL NULL; kolumna NOT NULL przyjmie '' tam, gdzie Oracle ją odrzucał. To najczęstsza cicha przyczyna rozjazdu danych. Przed przejściem produkcyjnym znajdź takie kolumny (UPDATE ... SET col = NULL WHERE col = '') i przejrzyj logikę aplikacji oraz warunki.

Sortowanie i wielkość liter w identyfikatorach

Oracle sortuje wg NLS_SORT (np. NLS_SORT=POLISH dla poprawnej kolejności ą, ć, ż), a PostgreSQL wg collation systemu lub biblioteki ICU. Bez jawnego ustawienia (np. COLLATE "pl-PL-x-icu") ORDER BY i indeksy unikalne mogą porządkować dane inaczej niż Oracle. Druga rzecz: niecytowane identyfikatory Oracle sprowadza do WIELKICH liter, a PostgreSQL do małych — obiekt utworzony jako Orders żyje w Oracle jako ORDERS, a w PG jako orders. Kod odpytujący katalog systemowy po nazwach (albo używający cudzysłowów) trzeba ujednolicić, inaczej posypią się odwołania do tabel i kolumn.

Cache sekwencji, daty brzegowe i duże obiekty

• Cache sekwencji. Oracle domyślnie buforuje 20 wartości (CACHE 20), PostgreSQL — tylko 1. Przy intensywnych, współbieżnych wstawieniach domyślne CACHE 1 bywa wąskim gardłem — rozważ ALTER SEQUENCE ... CACHE n dla gorących sekwencji. W obu silnikach bufor i wycofania powodują dziury w numeracji — nie wolno zakładać ciągłości klucza.
• Daty brzegowe. W starych bazach trafiają się rekordy z datami spoza zakresu PostgreSQL lub technicznie nieprawidłowe (efekt luźniejszej walidacji Oracle), które przerywają COPY komunikatem date/time field value out of range. Wyłap je zapytaniem kontrolnym i oczyść lub zmapuj na NULL — inaczej jeden zły wiersz zatrzyma transfer całej tabeli.
• Duże obiekty (LOB). Konwersja CLOB→text i BLOB→bytea jest prosta, ale ładowanie bywa wąskim gardłem — ora2pg czyta LOB-y w całości do pamięci. Dwa parametry w ora2pg.conf kontrolują zużycie RAM i wielkość paczki:

  # --- ora2pg.conf (fragment dla tabel z LOB-ami) ---
  LONGREADLEN   1048576   # max 1 MB na LOB (domyślnie 0 = czyta cały obiekt do pamięci)
  DATA_LIMIT    50        # wierszy w paczce COPY (domyślnie 10 000 — za dużo przy dużych LOB-ach)

Tabele z LOB-ami eksportuj osobno, żeby móc dostosować oba parametry bez ruszania reszty migracji:

  ora2pg -c ora2pg.conf -t COPY \
    --allow DOCUMENTS,ATTACHMENTS \
    -o lob_tables.sql

  psql -h pg_host -U nordpol nordpol_pg -f lob_tables.sql

Jeśli LOB przekracza LONGREADLEN, ora2pg obcina go i loguje ostrzeżenie — zwiększ limit lub usuń zbyt duże obiekty przed migracją.

Dwie funkcje Oracle, które tracą na sile

Materialized view. Oracle potrafi odświeżać widok zmaterializowany przyrostowo (FAST REFRESH na podstawie materialized view log, opcjonalnie ON COMMIT). PostgreSQL ma tylko pełny REFRESH MATERIALIZED VIEW — przy dużych widokach to realna różnica wydajnościowa. Global temporary table. PostgreSQL ma tabele tymczasowe, ale brak mu trwałej, globalnej definicji jak oracle’owa GTT (gdzie struktura żyje stale w słowniku, a dane są prywatne dla sesji). Obejście: zwykła tabela temp w sesji albo rozszerzenie pgtt; od PG18 dochodzi częściowe wsparcie globalnych tabel tymczasowych.

Drobiazgi PL/SQL, które i tak trzeba ruszyć

• Obsługa wyjątków. Oracle’owe nazwane wyjątki, RAISE_APPLICATION_ERROR oraz SQLCODE/SQLERRM zastępuje blok EXCEPTION WHEN ... w PL/pgSQL z kodami SQLSTATE i GET STACKED DIAGNOSTICS — mapowanie błędów (np. NO_DATA_FOUND, DUP_VAL_ON_INDEX) trzeba przepisać ręcznie.
• Diagnostyka. DBMS_OUTPUT.PUT_LINE → RAISE NOTICE (lub funkcja dbms_output z orafce).
• Harmonogram zadań. DBMS_SCHEDULER/DBMS_JOB nie mają wbudowanego odpowiednika — używa się rozszerzenia pg_cron (zadania zapisane w bazie, składnia crona) albo zewnętrznego cron/pgAgent.

Migracja ze starszych wersji Oracle (11g, 18c, 21c)

W realnym świecie prawie nikt nie migruje z najnowszego 26ai — migruje się z 19c, 12c, a bardzo często z wiekowego 11g, który wciąż pracuje w setkach firm. Im starsze źródło, tym więcej „archaicznych” idiomów w kodzie — i tym ważniejsze, że ora2pg je rozumie. Postawiliśmy te wersje obok siebie (Oracle 11g, 18c i 21c) i przepuściliśmy przez nie ten sam „stary” schemat.

Co wygląda inaczej w starym kodzie

• Klucze główne na sekwencji + triggerze — 11g nie ma jeszcze kolumn IDENTITY (te weszły w 12c).
• Złączenia zewnętrzne operatorem (+) zamiast LEFT JOIN.
• Paginacja przez ROWNUM — brak FETCH FIRST n ROWS (to też 12c).
• DECODE wszędzie tam, gdzie dziś pisze się CASE.

Najlepsza wiadomość: ora2pg tłumaczy te wzorce automatycznie. Operator (+) — zmora wielu migracji — staje się czytelnym LEFT JOIN:

-- Oracle (styl 11g): zlaczenie (+), NVL i DECODE
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.sal, NVL(d.dname,'(brak)') dname,
       DECODE(SIGN(e.sal-10000), 1,'WYSOKA', 0,'SREDNIA', 'NISKA') widelki
  FROM emp e, dept d
 WHERE e.deptno = d.deptno(+);

-- ora2pg -> PostgreSQL (rzeczywiste wyjscie): (+) -> LEFT JOIN, NVL -> coalesce, DECODE -> CASE
CREATE OR REPLACE VIEW v_emp_dept (empno, ename, sal, dname, widelki) AS
SELECT e.empno, e.ename, e.sal, coalesce(d.dname,'(brak)') dname,
       CASE WHEN SIGN(e.sal-10000)=1 THEN 'WYSOKA'
            WHEN SIGN(e.sal-10000)=0 THEN 'SREDNIA' ELSE 'NISKA' END widelki
  FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno);

Najciekawsze jest to, co pokazał raport oceny. Ten sam „stary” schemat (bez pakietów i transakcji autonomicznych) na wszystkich trzech wersjach — 11g, 18c i 21c — dostał identyczny poziom A-3 (około jednego dnia pracy). Porównaj to z naszym pełnym schematem Nordpol (B-5): o trudności migracji decyduje złożoność kodu PL/SQL, a nie numer wersji źródła:

Wniosek dla starszych baz

Im starsza wersja Oracle, tym prostszy bywa kod (mniej nowoczesnych funkcji do przepisania), ale tym więcej idiomów typu (+) i sekwencja+trigger. ora2pg radzi sobie z nimi seryjnie. Realne ryzyko migracji nie zależy od numeru wersji — zależy od tego, ile logiki biznesowej siedzi w PL/SQL. Najpierw uruchom raport oceny, potem planuj.

CONNECT BY → WITH RECURSIVE — najważniejsze przepisanie dla 11g i 18c

Zapytania hierarchiczne (CONNECT BY PRIOR) są w 11g i 18c powszechne — często używane do drzew organizacyjnych i kategorii. W PostgreSQL ten sam efekt osiągasz przez WITH RECURSIVE:

Oracle

SELECT emp_id, emp_name, manager_id, LEVEL
FROM   employees
START WITH  manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

PostgreSQL

WITH RECURSIVE emp_hierarchy AS (
    -- anchor: korzenie drzewa (brak menedżera)
    SELECT emp_id, emp_name, manager_id, 1 AS lvl
    FROM   employees
    WHERE  manager_id IS NULL

    UNION ALL

    -- rekurencja: dzieci
    SELECT e.emp_id, e.emp_name, e.manager_id, h.lvl + 1
    FROM   employees e
    JOIN   emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT emp_id, emp_name, manager_id, lvl
FROM   emp_hierarchy
ORDER  BY lvl, emp_name;

ORDER SIBLINGS BY nie ma bezpośredniego odpowiednika. By zachować kolejność rodzeństwa, buduj dodatkową kolumnę ścieżki (path text[]) w części rekurencyjnej i sortuj po niej.

Konfiguracja ora2pg dla 11g / 18c / 21c

Jedyna wersja-specyficzna zmiana w ora2pg.conf to forma DSN. Starsze wersje XE używają sid; PDB (Oracle 12c+) — service_name:

# 11g XE / 18c XE / 21c XE (non-CDB lub CDB$ROOT)
ORACLE_DSN    dbi:Oracle:host=<host>;port=1521;sid=XE

# 12c+ z PDB (pluggable database)
ORACLE_DSN    dbi:Oracle:host=<host>;port=1521;service_name=ORCLPDB1

Lista kontrolna: idiomy per wersja Oracle

Mapowanie typów danych

Większość typów tłumaczy się mechanicznie, ale kilka pozycji to klasyczne źródła błędów. Najważniejsza: oracle’owy DATE zawiera godzinę — mapuj go na timestamp, nie na date, bo inaczej po cichu utniesz czas.

Dwa mapowania, które tylko wyglądają na 1:1

NUMBER bez precyzji: w PostgreSQL numeric o dowolnej precyzji liczony jest programowo i bywa kilka–kilkanaście razy wolniejszy oraz cięższy niż bigint/double precision. Jeśli kolumna mieści się w zakresie całkowitym (zwłaszcza klucze i kolumny w GROUP BY/JOIN), zmapuj ją świadomie na bigint, a nie bezrefleksyjnie na numeric. TIMESTAMP WITH TIME ZONE: Oracle zapisuje strefę w wartości, a timestamptz normalizuje wejście do UTC i pierwotnej strefy nie przechowuje — przy danych z różnych stref ta informacja przepada.

orafce — warstwa zgodności, która oszczędza przepisywania

Instalacja jest dwustopniowa: pakiet z systemu (apt-get install postgresql-16-orafce) i włączenie w bazie (CREATE EXTENSION orafce;, potem dodaj schemat oracle do search_path). Zakres jest szeroki: funkcje skalarne (nvl, nvl2, decode, instr, to_char/to_date/to_number z maskami Oracle, add_months, last_day, trunc na datach), pseudo-tabela dual, sysdate, a także emulacje pakietów: dbms_output, dbms_random, dbms_alert, dbms_pipe, utl_file oraz typy varchar2/nvarchar2 z oracle’ową semantyką długości. Dlatego warto instalować je na samym starcie.

Wysoka dostępność i replikacja

To obszar, który budzi najwięcej obaw przy odchodzeniu od Oracle („a co z RAC i Data Guard?”). W praktyce PostgreSQL ma komplet odpowiedników, często prostszych w utrzymaniu:

Jedna różnica filozoficzna: Oracle RAC to architektura wspólnego dysku (wszystkie węzły widzą tę samą pamięć masową), a typowy klaster PostgreSQL to architektura bez współdzielenia (każdy węzeł ma własną kopię danych, synchronizowaną strumieniem WAL). Dla większości zastosowań to drugie podejście jest tańsze i prostsze operacyjnie.

Wysoka dostepnosc PostgreSQL - replikacja strumieniowaPRIMARYzapis + odczytport 5432HOT STANDBYtylko odczytport 5433strumien WALopoznienie ~5 msaplikacja (W/R)raporty (R)Awaria primary -> promocja standby (pg_promote / Patroni). Odpowiednik Oracle Data Guard. Klastry produkcyjne: Patroni + etcd + HAProxy.

Replikacja strumieniowa: primary wysyła strumień WAL do gorącej repliki (standby); awaria → promocja repliki.

Postawiliśmy to na żywo: jeden klaster jako primary (port 5432), drugi jako gorąca replika (hot standby, port 5433) — replika gotowa do natychmiastowego przejęcia ruchu — zbudowana przez pg_basebackup z opcją -R. Po stronie primary widać aktywny strumień:

psql -p 5432

-- na primary: kto sie replikuje?
SELECT application_name, state, sync_state, client_addr,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn,replay_lsn)) AS opoznienie
  FROM pg_stat_replication;

 application_name |   state   | sync_state | client_addr | opoznienie
------------------+-----------+------------+-------------+-----------
 16/standby       | streaming | async      | 127.0.0.1   | 0 bytes

Test „na żywo”: zapis na primary jest natychmiast widoczny na standby, zapis na standby jest odrzucany (replika jest tylko-do-odczytu), a po awarii promujemy standby do roli primary:

failover demo

[primary 5432] INSERT INTO customers(...) RETURNING customer_id;  -- 5001
[standby 5433] SELECT ... WHERE customer_id=5001;  -- rekord juz jest (replikacja)
[standby 5433] INSERT ...;  -- ERROR: cannot execute INSERT in a read-only transaction
[primary 5432] write_lag | flush_lag | replay_lag
               00:00:00.005 | 00:00:00.013 | 00:00:00.022   -- ~5-22 ms

-- FAILOVER: promocja repliki
$ pg_ctlcluster 16 standby promote
[standby] SELECT pg_is_in_recovery();  ->  f   (to juz jest primary)

Uniwersalna promocja i produkcyjny Patroni

pg_ctlcluster to wrapper Debiana/Ubuntu; uniwersalne odpowiedniki promocji repliki to pg_ctl promote -D <katalog_danych> albo funkcja SQL SELECT pg_promote() (można ją wywołać zdalnie przez psql). Ręczna promocja jest dobra do nauki — w produkcji używa się Patroni (z etcd/Consul) do automatycznego wyboru lidera i failoveru oraz HAProxy do kierowania ruchu. To dzisiejszy standard klastrowego PostgreSQL, odpowiednik logiki Data Guard Broker.

Kopie zapasowe i odtwarzanie do punktu w czasie

Odpowiednikiem RMAN jest pgBackRest — obsługuje kopie pełne, przyrostowe i różnicowe, równoległość, kompresję, szyfrowanie, repozytoria w S3/GCS oraz PITR (odtworzenie do dowolnej sekundy) dzięki ciągłej archiwizacji WAL. Sama logika jest bliska RMAN-owi:

# pelna kopia
pgbackrest --stanza=nordpol --type=full backup
# przyrostowa (tylko zmiany od ostatniej)
pgbackrest --stanza=nordpol --type=incr backup
# odtworzenie do punktu w czasie (PITR)
pgbackrest --stanza=nordpol --type=time \
  --target='2026-06-05 12:00:00' restore

Rozszerzenia — przewaga ekosystemu

Największa różnica mentalna przy przejściu na PostgreSQL: wiele rzeczy, za które w Oracle płaci się za opcje licencyjne, tu jest rozszerzeniem — często otwartym i instalowanym jednym CREATE EXTENSION. Te najważniejsze przy migracji:

To zwykle przesądza o całkowitym koszcie posiadania (TCO)

Funkcje, które w Oracle są płatnymi opcjami (partycjonowanie, kompresja zaawansowana, diagnostyka AWR, Spatial, In-Memory), w PostgreSQL są w rdzeniu albo w darmowych rozszerzeniach. Dla wielu firm to jedyny argument, który naprawdę przekonuje zarząd — brak opłat licencyjnych przy zachowaniu funkcjonalności.

Ścieżki komercyjne, gdy chcesz mniej przepisywać

Poza narzędziami otwartymi istnieją płatne ścieżki, które redukują przepisywanie kodu — warto je znać, by świadomie wybrać kompromis między kosztem licencji a kosztem pracy:

• EDB Postgres Advanced Server (EPAS) — PostgreSQL z wbudowaną warstwą zgodności z Oracle: natywnie obsługuje pakiety, PRAGMA AUTONOMOUS_TRANSACTION, hierarchiczne CONNECT BY i typy oracle’owe, a EDB Migration Toolkit/Portal automatyzują przenosiny — kosztem płatnej subskrypcji i lekkiego uzależnienia od dostawcy.
• AWS SCT + DMS — AWS Schema Conversion Tool (ocena + konwersja schematu) razem z Database Migration Service (ciągła replikacja danych z przechwytywaniem zmian, CDC) to standard migracji do RDS/Aurora.
• Uwaga terminologiczna: Babelfish for Aurora PostgreSQL to warstwa zgodności z Microsoft SQL Server (protokół TDS, T-SQL), a NIE z Oracle — nie używaj go do tej migracji.

Strategia przejścia: na raz czy przyrostowo

Narzędzia i walidacja to jedno — o ryzyku biznesowym decyduje plan samego przełączenia produkcji. Dwa podejścia:

Plan rollbacku i prawdziwa walidacja danych

Zawsze przygotuj plan wycofania: utrzymaj Oracle jako źródło prawdy do czasu potwierdzenia stabilności. Zgodność danych weryfikuj głębiej niż count(*) — porównaj sumy kontrolne kolumn (np. sum/min/max oraz agregat md5 per tabela) i próbki rekordów brzegowych, szczególnie tam, gdzie wchodzą puste stringi, daty z czasem i liczby zmiennoprzecinkowe.

Bezpieczeństwo: role, hasła, audyt

Model uprawnień różni się fundamentalnie. W Oracle użytkownik jest jednocześnie schematem; w PostgreSQL role (z LOGIN lub bez) i schematy są rozdzielone, a uprawnienia nadaje się przez GRANT na bazę/schemat/obiekt. Trzy rzeczy do zaplanowania osobno:

• Polityki haseł. Oracle’owe PROFILE (FAILED_LOGIN_ATTEMPTS, wygaśnięcie hasła, limity sesji) nie mają bezpośredniego odpowiednika — w PostgreSQL używa się rozszerzenia passwordcheck, zewnętrznego uwierzytelniania (LDAP/PAM/SCRAM) i reguł w pg_hba.conf.
• Audyt. Oracle Unified Audit / AUDIT zastępuje rozszerzenie pgAudit.
• Bezpieczeństwo na poziomie wiersza. Oracle VPD / Label Security to w PostgreSQL Row-Level Security (RLS) — wbudowane w rdzeń.

Mapowanie ról i odtworzenie polityk haseł zaplanuj jako osobny krok przejścia — to często pomijany element, który potrafi zablokować wdrożenie tuż przed startem.

Życie po migracji: autovacuum i monitoring

Największa różnica operacyjna względem Oracle to autovacuum. PostgreSQL nie nadpisuje wierszy w miejscu — kasowanie i aktualizacja zostawiają „martwe” wersje, które autovacuum sprząta i przy okazji odświeża statystyki planera. Bez jego dostrojenia tabele z dużą rotacją puchną (tzw. bloat), a zapytania zwalniają — Oracle nie ma tego problemu, więc łatwo o nim zapomnieć.

• Po załadowaniu danych wykonaj ręczny ANALYZE (planer potrzebuje świeżych statystyk — inaczej dobiera fatalne plany).
• Monitoruj pg_stat_user_tables (n_dead_tup, last_autovacuum); dla gorących tabel zaostrz autovacuum_vacuum_scale_factor.
• Włącz pg_stat_statements (odpowiednik AWR / v$sql) — to pierwsze miejsce, gdzie szukać kandydatów do przepisania (jak nasze Q5).

Benchmark wydajnościowy

Na koniec rzecz najciekawsza: czy PostgreSQL nadąża za Oracle? Zmierzyliśmy 7 reprezentatywnych zapytań na tych samych danych (100 000 zamówień, 300 000 pozycji) i tym samym sprzęcie. Każde uruchomiliśmy po kilka razy i bierzemy medianę rozgrzanych przebiegów (pierwszy, zimny, odrzucamy), zawsze sekwencyjnie — nigdy obie bazy naraz. Szóste z tych zapytań (Q5, podzapytanie skorelowane) zachowało się tak nietypowo, że wyciągnęliśmy je z wykresu i omawiamy osobno na końcu — na głównym wykresie zostaje 6 porównywalnych zapytań.

Uczciwie o warunkach testu

To nie jest benchmark „laboratoryjny”, tylko realny pomiar na maszynie 2 vCPU / 6 GB RAM. Oracle Database Free ma narzucone limity (2 wątki CPU, ~2 GB pamięci bazy i 12 GB danych), więc PostgreSQL nastrojony do ~2 GB pamięci współdzielonej (shared_buffers) gra w tej samej lidze. Traktuj liczby jako rząd wielkości i ogólną tendencję, nie jako wyrocznię — na Twoim sprzęcie i danych wyjdą inne.

Oracle vs PostgreSQL - 6 zapytań analitycznych100k zamówień / 300k pozycji · mediana czasu · skala logarytmiczna · niżej = lepiej110100czas ms174194168Q1 agreg.160180158Q2 okno1.01.21.2Q3 hierarch.364026Q4 zakres273836Q6 top-N1299665Q7 PL/SQLOracle 26aiPostgreSQL 16 (domyślny)PostgreSQL 16 (po tuningu)

Sześć porównywalnych zapytań: PostgreSQL po nastrojeniu dorównuje Oracle lub go wyprzedza. Domyślny PostgreSQL jest wolniejszy — bo celowo nie używa pamięci serwera.

Wnioski z wykresu: po nastrojeniu PostgreSQL wygrywa lub remisuje na 5 z 6 zapytań z wykresu (Q1, Q2, Q3, Q4, Q7). Oracle jest szybszy na Q6 (top-N, czyli wybranie kilkunastu rekordów z czołówki rankingu). Największy zysk z nastrojenia widać na Q4 i Q7. Ale prawdziwie pouczające jest to jedno zapytanie (Q5), którego na wykresie nie ma — wracamy do niego za chwilę.

Co dokładnie nastroiliśmy w PostgreSQL

Domyślny postgresql.conf jest celowo zachowawczy — ma wystartować na każdym sprzęcie, więc shared_buffers to raptem 128 MB, a work_mem — 4 MB. To pierwsza rzecz do zmiany po migracji:

-- najwazniejsze parametry (maszyna 6 GB RAM, 2 vCPU, SSD)
ALTER SYSTEM SET shared_buffers           = '1536MB';  -- ~25% RAM
ALTER SYSTEM SET effective_cache_size     = '4GB';     -- ile cache zaklada planer
ALTER SYSTEM SET work_mem                 = '64MB';    -- pamiec na sort/hash (kluczowe!)
ALTER SYSTEM SET maintenance_work_mem     = '256MB';
ALTER SYSTEM SET random_page_cost         = 1.1;       -- SSD
ALTER SYSTEM SET max_parallel_workers_per_gather = 2;
ALTER SYSTEM SET jit                      = off;       -- krotkie zapytania: JIT szkodzi
-- po zmianach: restart (shared_buffers) i ANALYZE

Dwie nieoczywiste decyzje

work_mem to najważniejszy parametr dla analityki — gdy sortowanie lub budowa tablicy mieszającej (hash) nie mieszczą się w pamięci, PostgreSQL zrzuca je na dysk i zwalnia drastycznie. JIT (kompilację zapytań do kodu maszynowego) wyłączyliśmy celowo: opłaca się ona przy zapytaniach trwających sekundy, ale przy krótkich (jak nasze) sam narzut kompilacji bywa większy niż zysk — pierwszy „zimny” przebieg Q7 z włączonym JIT trwał 5 sekund. Zamiast globalnie wyłączać JIT można też podnieść próg jit_above_cost — wówczas kompilacja odpala się tylko dla naprawdę kosztownych zapytań; pełne jit=off jest uzasadnione, gdy obciążenie to głównie krótkie zapytania.

Studium przypadku Q5: gdy strojenie to za mało

Q5 to klasyczne podzapytanie skorelowane (liczone osobno dla każdego wiersza zewnętrznego): „policz klientów, których przychód przekracza średnią w ich kraju”. Na Oracle wykonuje się w ~1,8 s. Na PostgreSQL — i to zarówno przed, jak i po nastrojeniu — przekroczyło ustawiony przez nas limit czasu zapytania (statement_timeout, 150 sekund) i zostało przerwane:

psql nordpol_pg

-- PostgreSQL, podzapytanie skorelowane (oryginalna skladnia z Oracle)
SELECT count(*)
  FROM customers c
 WHERE (SELECT sum(oi.line_total)          -- przychod tego klienta
          FROM orders o
          JOIN order_items oi ON oi.order_id = o.order_id
         WHERE o.customer_id = c.customer_id)
       > (SELECT avg(kup_rev)           -- srednia krajowa (tez skorelowana po country_id!)
            FROM (SELECT sum(oi2.line_total) AS kup_rev
                    FROM customers c2
                    JOIN orders o2      ON o2.customer_id = c2.customer_id
                    JOIN order_items oi2 ON oi2.order_id  = o2.order_id
                   WHERE c2.country_id = c.country_id
                   GROUP BY c2.customer_id) kraj);

ERROR:  canceling statement due to statement timeout
Time: 150000.386 ms (02:30.000)     <- ponad 150 s, tez po nastrojeniu

Dlaczego Q5 to nie problem pamięci, tylko algorytmupodzapytanie skorelowane (raz na 5000 klientów) vs przepisanie do 2 przebiegów (CTE)PostgreSQL bez przepisaniapodzapytanie liczone OSOBNO dla każdego klienta:× 5000 razy — raz na klientaTIMEOUT > 150 000 msvsPostgreSQL po przepisaniu (CTE)cust_revprzychód klienta — 1 przebiegcountry_avgśrednia krajowa — 1 przebiegPostgreSQL 251 ms · Oracle 340 msten sam wynik (2343 klientów)

Mechanizm: po lewej PostgreSQL liczy podzapytanie osobno dla każdego z 5000 klientów (i nie kończy); po prawej — przepisane na dwa przebiegi (CTE) liczy się w ułamku sekundy.

Dlaczego? Optymalizator Oracle potrafi spłaszczyć podzapytanie skorelowane (z ang. unnest — przekształcić je w zwykłe złączenie z agregacją) i policzyć średnią raz na kraj. PostgreSQL bez przepisania wykonuje je dosłownie — raz dla każdego z 5000 klientów. (PostgreSQL spłaszcza część podzapytań — np. EXISTS i IN zamienia na półzłączenie — ale nie tego skorelowanego podzapytania skalarnego ze średnią.) Żadne zwiększanie work_mem tego nie naprawi, bo problem jest algorytmiczny, nie pamięciowy. Lekarstwem nie jest strojenie serwera, tylko przepisanie zapytania: policz przychód każdego klienta i średnią krajową raz, w dwóch przebiegach (we wspólnym wyrażeniu tablicowym, CTE), a potem porównaj:

-- to samo pytanie, ale liczone raz zamiast 5000 razy
WITH cust_rev AS (
  SELECT o.customer_id, c.country_id, sum(oi.line_total) AS rev
    FROM orders o JOIN order_items oi ON oi.order_id=o.order_id
    JOIN customers c ON c.customer_id=o.customer_id
   GROUP BY o.customer_id, c.country_id),
country_avg AS (
  SELECT country_id, avg(rev) AS avg_rev FROM cust_rev GROUP BY country_id)
SELECT count(*) FROM cust_rev cr JOIN country_avg ca USING(country_id)
 WHERE cr.rev > ca.avg_rev;

Najlepszy dowód to plany wykonania z samej bazy. Wersja oryginalna ma SubPlan liczony przy każdym wierszu i szacowany koszt 35 milionów; wersja CTE liczy wszystko w dwóch przebiegach i kończy w 263 ms:

psql nordpol_pg: EXPLAIN [ANALYZE]

-- ORYGINAL: EXPLAIN podzapytania skorelowanego (nie wykonujemy - za wolne)
 Aggregate  (cost=35172003.02..35172003.03 rows=1)        <- koszt 35 MILIONOW
   ->  Seq Scan on customers c  (cost=0.00..35171998.86)
         Filter: ((SubPlan 1) > (SubPlan 2))               <- liczone DLA KAZDEGO wiersza
         SubPlan 1  ->  Aggregate ...     (przychod klienta)
         SubPlan 2  ->  HashAggregate ... (srednia krajowa - przeliczana per klient!)

-- PRZEPISANE: EXPLAIN ANALYZE wersji CTE
 Aggregate (actual rows=1 loops=1)
   CTE cust_rev  ->  HashAggregate (actual rows=5000)      <- przychod: 1 przebieg
   ->  Hash Join (actual rows=2343)
         ->  HashAggregate (actual rows=13)                <- srednia: 1 przebieg (13 krajow)
 Execution Time: 263.680 ms                                <- 263 ms zamiast >150 s

Q5: tuning nie pomaga - pomaga przepisanie zapytaniaten sam wynik (2343 klientów), pięć sposobów · skala log · „timeout” = przerwane po 150 s110100100010000100000czas ms1842Oracle 26ai150000PostgreSQL
(domyślny)150000PostgreSQL
(po tuningu)251PostgreSQL
(przepisane)340Oracle
(przepisane)czas zapytania Q5

To samo zapytanie policzone na pięć sposobów. Przepisanie do CTE ścina czas z „ponad 150 s” do dziesiątek milisekund — i to na obu bazach.

Najważniejsza lekcja z całego benchmarku

Część „przewagi Oracle” to nie szybsza maszyna, tylko sprytniejszy optymalizator ukrywający nieefektywnie napisane zapytania. Po migracji takie miejsca trzeba znaleźć i przepisać (narzędzie: pg_stat_statements + EXPLAIN ANALYZE). Dobra wiadomość: przepisane zapytanie jest szybkie również na Oracle — czyli to po prostu lepszy SQL, nie „obejście braku”.

Kiedy migrować, a kiedy się wstrzymać

Migracja Oracle→PostgreSQL jest dziś rutynowa technicznie — ale „rutynowa” nie znaczy „darmowa w czasie”. Z naszego przejścia płyną cztery wnioski:

• Dane i schemat to nie problem. ora2pg przeniósł ponad 400 tys. wierszy (dokładnie 407 538) w 21 sekund, a typy i indeksy skonwertował sam. Budżet czasu zjada warstwa proceduralna.
• PL/SQL to sedno wyceny. Pakiety, transakcje autonomiczne, kolumny wirtualne i osobliwości partycjonowania trzeba przepisać ręcznie — raport ora2pg da Ci realną liczbę dni pracy, zanim coś obiecasz biznesowi.
• Wydajność nie jest argumentem przeciw. Po podstawowym tuningu PostgreSQL gra w tej samej lidze co Oracle. Wąskim gardłem bywają pojedyncze zapytania liczące na „magię” optymalizatora Oracle — te trzeba znaleźć i przepisać.
• Administracja ma komplet odpowiedników. Replikacja, HA, PITR, monitoring — wszystko jest, często za darmo tam, gdzie Oracle liczy sobie za opcje licencyjne. To zwykle przesądza o decyzji.

Kolejność kroków migracjizielone = automat · czerwone = ręczna robota (tu idzie budżet)1raport ora2pgocena B-52orafcewarstwa zgodności3baza UTF8kodowanie4schemat bez FKDDL5COPY danych407 538 / 21 s6FK + resyncsekwencje7przepisanie PL/SQLpakiety, CONNECT BY8walidacjaoracle_fdw + replikacja

Osiem kroków migracji w kolejności: zielone idą automatem, czerwony (przepisanie PL/SQL) to miejsce, gdzie idzie większość budżetu.

Praktyczna checklista migracji

1) Uruchom raport oceny ora2pg i policz dni pracy.   2) Zainstaluj orafce — oszczędzi przepisywania.   3) Bazę docelową twórz w UTF8.   4) Ładuj: schemat bez FK → dane → FK → resync sekwencji.   5) Przepisz pakiety, transakcje autonomiczne, CONNECT BY.   6) Zweryfikuj liczebności przez oracle_fdw.   7) Nastrój postgresql.conf i znajdź ciężkie zapytania (pg_stat_statements).   8) Zaplanuj replikację i kopie (pgBackRest).

Szkolenie powiązane

Administracja, replikacja i tuning baz danych PostgreSQL

Replikacja strumieniowa, Patroni, pgBackRest, tuning postgresql.conf, partycjonowanie i diagnostyka zapytań — wszystko, czego potrzebuje administrator po migracji z Oracle, na praktycznym szkoleniu z terminem gwarantowanym.

Zobacz szczegóły szkolenia

Szkolenie powiązane — poziom zaawansowany

Zaawansowana administracja PostgreSQL: HA, Disaster Recovery, monitoring, skalowanie

Patroni, pgBackRest PITR, HAProxy + pgBouncer, pg_stat_monitor — szkolenie dla administratorów odpowiedzialnych za produkcyjne klastry PostgreSQL z terminem gwarantowanym.

Zobacz szczegóły szkolenia