Z Oracle do PostgreSQL: Krótki przewodnik techniczny

CYBERTEC oferuje kompleksowe usługi przejścia z Oracle do PostgreSQL. Inteligentne narzędzia są dostępne na naszej stronie internetowej. Chcemy pomóc Państwu szybciej przejść do PostgreSQL. Ten przewodnik szybkiego startu po migracji pomoże szybko podążać we właściwym kierunku.

Podczas przechodzenia z Oracle do PostgreSQL należy wziąć pod uwagę różne aspekty, w tym:

• Migracja struktur danych
• Migracja danych
• Konwersja typowych instrukcji SQL
• Konwersja PL/SQL to PL/Java
• Zarządzanie pamięcią masową w PostgreSQL i Oracle

Przyjrzyjmy się szczegółowo tym istotnym punktom.

Migracja struktur danych

Pierwszą rzeczą do zapoznania się jest to, że PostgreSQL ma o wiele więcej typów danych niż Oracle. Dlatego ważne jest, aby dokonywać właściwych wyborów. Wybór odpowiedniego typu danych może mieć ogromny wpływ na wydajność.

Jeśli nie jesteście pewni, jak mapować typy danych Oracle do PostgreSQL, przygotowaliśmy macierz konwersji. Macierz pokazuje, które typy można zmapować do odpowiedniego odpowiednika.

Typ Oracle	Możliwy typ PostgreSQL
CHAR	char, varchar, text
NCHAR	char, varchar, text
VARCHAR	char, varchar, text
VARCHAR2	char, varchar, text, json, jsonb
NVARCHAR2	char, varchar, text
CLOB	char, varchar, text, json, jsonb
LONG	char, varchar, text
RAW	uuid, bytea
BLOB	bytea
BFILE	bytea (read-only)
LONG RAW	bytea
NUMBER	numeric, float4, float8, char, varchar, text
NUMBER (n, m) with m<=0	numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text
FLOAT	numeric, float4, float8, char, varchar, text
BINARY_FLOAT	numeric, float4, float8, char, varchar, text
BINARY_DOUBLE	numeric, float4, float8, char, varchar, text
DATE	date, timestamp, timestamptz, char, varchar, text
TIMESTAMP	date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONE	date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH LOCAL TIME ZONE	date, timestamp, timestamptz, char, varchar, text
INTERVAL YEAR TO MOTH	interval, char, varchar, text
INTERVAL DAY TO SECOND	interval, char, varchar, text
XML TYPE	xml, char, varchar, text
MDSYS.SDO_GEOMETRY	geometry (see “PostGIS support” below)

Najlepszą decyzją byłoby przejście na typ danych, który zapewnia najmniejszy narzut. Co to znaczy? Oto przykład: number(10, 0) można zmapować do numeric(10, 0) ale możecie też go zmapować do int8. Używanie int8 jest zwykle dużo bardziej wydajne. Int8 z pewnością zjada 8 bajtów – jednak obliczenia są znacznie szybsze, jeśli używacie int8 rather zamiast liczb.

varchar2 można mapować bezpośrednio do varchar lub do typu danych text. Jednak uznaliśmy za przydatne rozważenie rozszerzenia „citext” w PostgreSQL. Ideą citext jest posiadanie typu danych tekstowych niewrażliwych na wielkość liter, co sprawia, że wyszukiwanie jest o wiele wygodniejsze. W aplikacjach internetowych wyszukiwanie bez rozróżniania wielkości liter jest zwykle tym, czego szukacie. Jeśli generalnie interesuje Państwa wyszukiwanie rozmyte, zapraszamy do przeczytania naszego wpisu na blogu poświęconego temu wyjątkowo ważnemu tematowi. Migracja jest zwykle dobrą okazją do uporządkowania i poprawy sytuacji

BLOBs są również problemem, o który wiele osób pyta. Ogólnie PostgreSQL oferuje typ danych bytea (“byte array”). Wygląda jak kolumna tekstowa, ale w rzeczywistości może przechowywać dane binarne. Jeśli nie chcecie używać kolumny do przechowywania obiektów BLOB, można również bezpośrednio użyć interfejsu PostgreSQL BLOB.

UWAGA: Należy tutaj wspomnieć, że Oracle Spatial można odwzorować na PostGIS oraz PostgreSQL. Więc jeśli używasz danych GIS, istnieje również sposób na migrację do PostgreSQL. W tym przypadku również nie ma praktycznych ograniczeń.

Aby automatycznie mapować typy danych, zachęcamy rozważenie skorzystania z CYBERTEC Migrator. To narzędzie posiada zaawansowane możliwości mapowania typów danych i oferuje łatwy sposób przewidywania najlepszego możliwego typu danych do przechowywania dużych ilości danych w PostgreSQL.

CREATE SYNONYM: Brakujące piękno

Oracle zapewnia polecenie CREATE SYNONYM, które nie jest dostępne w PostgreSQL. Jakiś czas temu w CYBERTEC zaimplementowaliśmyCREATE SYNONYM dla PostgreSQL, ale ta modyfikacja została odrzucona, ponieważ nie pasuje do ogólnej koncepcji PostgreSQL.

W rezultacie trzeba zastosować obejście:

• Synonimy tabel: zamiast tego użyć widoku (SELECT * …)
• Synonimy widoków: użyć widoku u góry widoku (SELECT * …)
• Synonimy funkcji/procedur: użyć funkcji opakowujących
• Synonimy dla widoków zmaterializowanych: użyć widoków

Ogólnie rzecz biorąc, lepiej pozbyć się tych synonimów podczas migracji. To znacznie ułatwia przyszły rozwój. Ustawienie ścieżki search_path okazało się ogólnie przydatne i często ogranicza potrzebę używania synonimów w pierwszej kolejności.

Nieprawidłowe widoki w Oracle – prawidłowe widoki w PostgreSQL

Podczas migracji struktur danych z Oracle do PostgreSQL można zauważyć, że PostgreSQL używa ścisłego śledzenia zależności. Pozwólcie, że podam przykład: jeśli tabela zostanie usunięta, PostgreSQL automatycznie usunie wszystkie zależne od niej obiekty. W Oracle tak nie jest. Być może trzeba będzie poradzić sobie z nieprawidłowymi widokami, co jest problemem, o którym warto wiedzieć podczas projektu migracji.

Migracja danych do PostgreSQL

Gdy struktury danych znalezione w Oracle zostaną przeniesione do PostgreSQL, nadszedł czas, aby pomyśleć o przeniesieniu danych w ogóle.

Narzędzie do migracji może wykonać wiele rzeczy, ale są też pewne rzeczy, które wymagają uwagi po stronie bazy danych Oracle. Oto kilka najważniejszych aspektów:

• Nieskończone liczby (“minus infinite” and “plus infinite”)
  • Można zmapować do “infinity” jeśli używana jest podwójna precyzja (co w końcu może nie być pożądanym)
• Uszkodzone struny w Oracle
  • Nieprawidłowa sekwencja bajtów do kodowania “UTF8”: 0x80
  • Uszkodzone struny są bardziej powszechne niż mogłoby się wydawać.
  • Ważne jest, aby mieć plan, jak sobie z nimi poradzić
• Zero bajtów w Oracle
  • Nieprawidłowa sekwencja bajtów do kodowania “UTF8”: 0x00
  • Nieprawidłowe sekwencje są również dość powszechne

Jak wspomniano wcześniej, większości z powyższych problemów nie można łatwo rozwiązać podczas procesu migracji, ale wymagają one wstępnej obróbki po stronie źródła. Oczywiście narzędzie do migracji jest w stanie rozwiązać wiele problemów, ale nie wszystkie. Warto być tego świadomym.

Gdy dane są odczytywane z Oracle, należy to zrobić w ramach jednej transakcji, aby zapewnić spójność (lub przynajmniej w wielu transakcjach, w których można zobaczyć tę samą migawkę danych, jaką robi to CYBERTEC Migrator). W celu osiągnięcia tego, będzie przeprowadzana długa transakcja na Oracle. Może to stanowić problem sam w sobie, ponieważ Oracle kończy transakcję w przypadku “ORA-01555: snapshot too old”.

Rozwiązaniem jest skonfigurowanie go tak, aby obsługiwał bardzo długie transakcje przed rozpoczęciem migracji danych. Sposobem na to jest użycie naprawdę dużych obszarów tabel UNDO.

Przenoszenie danych jest również nieco trudne. W Oracle znajdziecie kilka ograniczeń w OCI interface co może prowadzić do bardzo słabej wydajności podczas migracji dużych pól do PostgreSQL. Te ograniczenia i problemy z wydajnością są spowodowane sposobem, w jaki Oracle odczytuje dane, aw wielu przypadkach kod klienta nie może z nimi wiele zrobić.

PostgreSQL i przestrzenie tabel

W Oracle bardzo ważna jest koncepcja „przestrzeni tabel”. Można argumentować, że Oracle w zasadzie implementuje swój własny system plików. W przeciwieństwie do tego PostgreSQL w większym stopniu opiera się na funkcjonalności systemu operacyjnego (POSIX). Oprzyjcie się pokusie tworzenia obszarów tabel podczas migracji. Przestrzeń tabel nie ma większego sensu w świecie PostgreSQL.

Ponadto w nowoczesnym środowisku chmurowym obszary tabel należą w większości do przeszłości. Jeśli więc tworzycie nową infrastrukturę dla PostgreSQL na Kubernetes, na przykład zapewnianą przez Scalefield, w ogóle nie ma potrzeby używania przestrzeni tabel.

Konwersja typowych instrukcji SQL

Oracle ma pewne specjalizacje w swoim smaku SQL, które należy wziąć pod uwagę podczas migracji kodu z Oracle do PostgreSQL. W tej sekcji przedstawiono niektóre z najczęstszych problemów napotykanych podczas migracji.

Przepisywanie złączeń dla PostgreSQL

Oracle używa innej składni dla sprzężeń zewnętrznych niż reszta świata. Należy o tym pamiętać podczas konwersji kodu z Oracle do PostgreSQL. Poniższy przykład pokazuje, jak to działa.

Oracle

SELECT b.col1, a.col2
FROM base_table b, attributes a
WHERE b.id=a.b_id(+);

musi zostać przetłumaczone na

PostgreSQL

SELECT b.col1, a.col2
FROM base_table b
LEFT JOIN attributes a ON b.id = a.b_id;

Zmiana jest właściwie łatwa, ale dość denerwująca.

Podselekcje i aliasy

W Oracle podselekcja nie potrzebuje nazwy. Może być „anonimowa”:

SELECT * FROM (SELECT * FROM some_table)

Można to łatwo przepisać w PostgreSQL. Poniższy kod pokazuje, jak faktycznie wyglądałby odpowiednik w pracy Open Source:

SELECT * FROM (SELECT * FROM some_table) AS your_fancy_name

Obsługa NULL i pustych ciągów

Oracle traktuje NULL jako pusty ciąg. Ma to kilka konsekwencji:
'hello' || NULL nie jest NULL w Oracle. Dlatego należy go przetłumaczyć na następujący fragment SQL, aby mieć taką samą semantykę w PostgreSQL:

concat('hello', NULL)
albo użyć
coalesce(strcol, '')

Migracja instrukcji null może prowadzić do subtelnych błędów, które mogą być trudne do wyśledzenia. Należy zwracać baczną uwagę na takie szczegóły i wyraźnie je sprawdzać.

Funkcje czasowe w Oracle i PostgreSQL

Większość kodu Oracle wykorzystuje zastrzeżone funkcje, takie jak…

• SYSDATE
• SYSTIMESTAMP

Konieczne jest przetłumaczenie tych wywołań, aby odpowiadały PostgreSQL:

• clock_timestamp()
• current_date or current_timestamp

Na szczęście tę zmianę można łatwo zrobić. Wyszukiwania i zamiany zwykle wystarczy.

Migracja sekwencji do PostgreSQL

Bazy danych Oracle obsługują sekwencje zupełnie inaczej. Można zobaczyć następującą metodę tworzenia następnej wartości:

asequence.NEXTVAL

Odpowiednik PostgreSQL to:

nextval('asequence')

BObie bazy danych nie używają metody ANSI SQL do pobierania wartości z sekwencji, która wyglądałaby następująco: NEXT VALUE FOR asequence

Nie ma potrzeby DUAL

W PostgreSQL zapytanie niekoniecznie wymaga klauzuli FROM. Dobrze jest jej nie używać. Dlatego nie ma potrzeby stosowania DUAL.

SELECT * FROM dual;

… Nie jest konieczne w PostgreSQL. orafce może symulować DUAL, ale lepiej unikać tego po stronie PostgreSQL. Po prostu nie ma takiej potrzeby.

DECODE: Wszechmogący

DECODE to rzecz specyficzna dla Oracle:

decode(expr, search, result [, search, result...] [, default])

Sposobem na zmianę rzeczy w PostgreSQL jest…

• Zastąpić CASE / WHEN
• Zastąp funkcją “decode” udostępnianą przez orafce

Zwykły PostgreSQL działa w następujący sposób:

CASE WHEN expr THEN expr [...] ELSE expr END

Ogólnie, DECODE można łatwo zastąpić. Nie zawiera złożonej logiki.

Obsługa rekurencji: CONNECT BY

W Oracle istnieją dwa sposoby obsługi rekursji:

WITH RECURSIVE: Nowa składnia
CONNECT BY: Stara składnia

PostgreSQL ma pełną implementację WITH RECURSIVE. Stara CONNECT BY składnia jest wysoce specyficzna dla Oracle i nie może być używana w PostgreSQL (ani w żadnym innym silniku bazy danych SQL, o którym wiemy).

Oto przykład pokazujący, jak CONNECT BY można przetłumaczyć na WITH RECURSIVE:


SELECT empno, ename, level

FROM emp

START WITH empno = 7788

CONNECT BY PRIOR mgr = empno;

… kontra…

WITH hierarchy (empno, ename, mgr, "level") AS (
SELECT empno, ename, mgr, 1 AS "level"
FROM emp
WHERE empno = 7788
UNION ALL
SELECT e.empno, e.ename, e.mgr, h."level" + 1
FROM hierarchy h
JOIN emp e ON h.mgr = e.empno
) SELECT empno, ename, "level" FROM hierarchy;

Konwersja PL/SQL oraz PL/Java

Najdroższą częścią każdej migracji jest migracja kodu procedury składowanej. Chociaż PL/pgSQL and PL/SQL są dość podobne, nadal istnieją różnice, które należy wziąć pod uwagę. Jeśli używacie PL/Java po stronie Oracle, można skorzystać z odpowiednika PostgreSQL, ale sugerujemy raczej całkowite zastąpienie kodu. Uruchamianie języka Java po stronie serwera nie jest zbyt powszechne w świecie PostgreSQL.

PL/pgSQL jest klonem PL/SQL, ale jest na tyle różny, że wymaga pewnej uwagi (np. RETURNS vs.RETURN). CYBERTEC Migrator posiada środki do automatycznej konwersji kodu.

Autonomiczna transakcja w Oracle oraz PostgreSQL

PostgreSQL nie ma czegoś takiego jak PRAGMA AUTONOMOUS_TRANSACTION. Trzeba obejść tę brakującą funkcjonalność. Ogólnie lepiej jest zastąpić transakcje autonomiczne punktami zapisu i upewnić się, że transakcje rzeczywiście mogą zostać zatwierdzone. W niektórych rzadkich przypadkach nie ma możliwości obejścia transakcji autonomicznych. Sposobem na modelowanie tego w PostgreSQL jest użycie rozszerzenia dblink.

BULK COLLECT: Użycie wierszy

BULK COLLECT nie jest dostępny po stronie PostgreSQL. Sposobem na obejście tego problemu w PostgreSQL jest użycie podejścia wiersz po wierszu.

Trzeba uważać w tym przypadku. Wydajność może stać się problemem. Dlatego lepiej jest używać instrukcji SQL zamiast pętli.

Symulowanie funkcji Oracle

W wielu przypadkach PostgreSQL oferuje taką samą funkcjonalność jak Oracle. Jednak często nazwy funkcji po prostu różnią się od siebie.

Rozszerzenie orafce dla PostgreSQL implementuje wiele z tych specyficznych dla Oracle funkcji w PostgreSQL. Korzystając z rozszerzenia, można znacznie zmniejszyć liczbę ręcznych zmian kodu.

Poniższa lista przedstawia przykłady kodu i pokazuje, jak rzeczy będą widoczne dla użytkownika końcowego:

SELECT last_day('2000-02-01 121143');
SELECT next_day ('2008-01-01 121212', 'sunAAA');
SELECT months_between ('2007-02-28 111111', '2007-04-30 112121');
select dbms_pipe.purge('bob');
select instr('Tech on the net', 'e') = 2;

orafce istnieje od wielu lat i jest dojrzały. Można na nim polegać.

PL/SQL pakiety

W PostgreSQL nie ma koncepcji pakietów PL/SQL. Istnieją jednak sposoby na symulowanie pakietów. Istnieje wiele sposobów podejścia do problemu:

• Skorzystać z CREATE EXTENSION – zawsze można spakować kod do rozszerzeń
• Użyć schematów dla każdego „pakietu”

Dowiecie się, że pakiety tak naprawdę nie stanowią problemu i że po stronie PostgreSQL można z łatwością bez nich żyć.

Migracja wyzwalaczy do PostgreSQL

W PostgreSQL wyzwalacz zawsze wywoła funkcję. Polecenie CREATE TRIGGER nie zawiera kodu bezpośrednio:

demo=# h CREATE TRIGGER
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]
}
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ]
]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

gdzie zdarzenie może być jednym z:

INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE

Piękno po stronie PostgreSQL polega na tym, że kod można łatwo ponownie wykorzystać. Warto również zauważyć, że wyzwalacze zwiększające wartości nie są potrzebne. Można po prostu użyć kolumn DEFAULT, aby automatycznie określić następną wartość i ustawić ją.

WyzwalaczeLOGON nie są obsługiwane w PostgreSQL. Jeśli jest potrzebna tego typu funkcjonalność, konieczne jest przeniesienie kodu do aplikacji lub całkowite uniknięcie tego.

Profesjonalna pomoc

W CYBERTEC pomogliśmy niezliczonym klientom w migracji z Oracle do PostgreSQL. Ponadto oferujemy CYBERTEC Migrator, aby ułatwić przejście. Skontaktuj się z nami już dziś i otrzymaj osobistą ofertę migracji. Oferujemy ponad 20 lat doświadczenia w PostgreSQL, profesjonalną obsługę i terminową dostawę.