CYBERTEC bietet umfassende Services und ausgezeichnete Tools für die Migration von Oracle zu PostgreSQL. Unser Ziel ist es, Ihnen die Migration zu PostgreSQL so einfach wie möglich zu machen. Dieser Migrationsguide sorgt für einen schnellen Start.
Es gibt verschiedene Aspekte die bei der Migration von Oracle zu PostgreSQL zu beachten sind. Darunter sind die folgenden:
- Migration von Datenstrukturen
- Migration der Daten
- Konvertierung bekannter SQL Statements
- Konvertierung von PL/SQL und PL/Java
- Speichermanagement in PostgreSQL und Oracle
Sehen wir uns die genannten Punkte im Detail an.
Migration der Datenstrukturen
Sie werden schnell feststellen, dass in PostgreSQL viel mehr Datentypen zur Verfügung stehen als in Oracle. Hier die richtige Wahl zu treffen, ist also äußerst wichtig und kann einen großen Einfluss auf die Performance haben.
Wir haben eine Konvertierungs-Matrix erstellt, die Ihnen Auskunft darüber gibt, welchen PostgreSQL Datentypen die Oracle Datentypen entsprechen.
Oracle type | Possible PostgreSQL type |
---|---|
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) |
Idealerweise wählen Sie einen Datentypen, der einen geringen „Overhead“ mit sich bringt. An einem Beispiel erklärt heißt das folgendes. number(10, 0)
kann innumeric(10, 0)
abgebildet werden, aber auch in int8
. int8
ist überblicherweise wesentlich effizienter: Es braucht definitiv 8 bytes, allerdings laufen Berechnungen in int8
wesentlich schneller als bei numerischen Datentypen.
varchar2
kann direkt in varchar
oder im text
Datentyp abgebildet werden. Hier halten wir den Einsatz der „citext“ Extension in PostgreSQL für sinnvoll. Die Idee hinter citext ist ein Textdatentyp bei dem die Groß-und Kleinschreibung keine Rolle spielen, was die Suche sehr viel bequemer macht. Für Webanwendungen ist einen Suche, die Groß- und Kleinschreibung nicht berücksichtigt in der Regel genau das, wonach Sie suchen. Wenn Sie sich generell für Fuzzy-Search interessieren, sollten Sie sich unseren Blog-Beitrag über dieses außerordentlich wichtige Thema ansehen. Eine Migration ist in der Regel eine gute Gelegenheit, um aufzuräumen und Dinge zu verbessern.
BLOBs sind ein weiteres Thema, zudem oft Fragen auftauchen. PostgreSQL bietet den bytea
Datentyp („byte array“). Vermittelt wird das Gefühl einer Textspalte, aber es können Binäre Daten enthalten sein. Wenn Sie keine einzelne Spalte verwenden wollen, um BLOBs zu speichern, können Sie stattdessen direkt das PostgreSQL BLOB Interface verwenden.
NOTE: Es ist wichtig zu wissen, dass Oracle Spatial in PostGIS und PostgreSQL abgebildet werden kann. Es ist also auch möglich, GIS Daten zu PostgreSQL zu migrieren.
Für fortgeschrittenes, automatisches Datentyp-Mapping werfen Sie einen Blick auf den CYBERTEC Migrator – dieser bietet einen einfachen Weg und schlägt die bestmöglichen Datentypen um große Mengen an Daten in PostgreSQL zu speichern vor.
CREATE SYNONYM
Oracle bietet den Befehl CREATE SYNONYM
welcher in PostgreSQL nicht verfügbar ist. Vor einiger Zeit haben wir bei CYBERTEC CREATE SYNONYM
für PostgreSQL entwickelt. Diese Modifikation wurde abgelehnt, weil Sie nicht ins generelle Konzept von PostgreSQL gepasst hat.
- Synonym für Tabellen: Nutzen Sie stattdessen views (
SELECT * …
) - Synonym für Views: Nutzen Sie einen view über dem view (
SELECT * …
) - Synonym für Functions / Procedures: Nutzen Sie wrapper functions
- Synonym für materialized views: Nutzen Sie views
Im Allgemeinen ist es besser, diese Synonyme bei der Migration loszuwerden. Es macht die zukünftige Entwicklung viel einfacher. Das Setzen des search_path
hat sich allgemein als nützlich erwiesen und reduziert oft die Notwendigkeit, Synonyme überhaupt zu verwenden.
Ungültige views in Oracle – gültige views in PostgreSQL
Wenn Sie Datenstrukturen von Oracle nach PostgreSQL migrieren, werden Sie feststellen, dass PostgreSQL eine strikte Abhängigkeitsverfolgung verwendet. Lassen Sie mich Ihnen ein Beispiel geben: Wenn eine Tabelle gelöscht wird, löscht PostgreSQL automatisch alle abhängigen Objekte. In Oracle ist dies nicht der Fall. Möglicherweise haben Sie es mit ungültigen Ansichten zu tun, was ein Problem ist, das Sie bei einem Migrationsprojekt beachten sollten.
Migration der Daten
Nachdem gefundene Strukturen nach PostgreSQL verschoben wurden, ist es Zeit für die Migration der Daten selbst.
Unser Migration Tool kann hier viel übernehmen, aber es gibt einige Aspekte in der Oracle Datenbank, die es zu beachten gibt:
- Infinite numbers (“minus infinite” und “plus infinite”)
- Könnnen auf „infinity“ abgebildet werden, wenn „double precision“ verwendet wird (was vielleicht nicht ist, was Sie wollen)
- Korrupte Strings in Oracle
- Ungültige byte sequence für die Kodierung „UTF8“: 0x80
- Ist wahrscheinlich, als Sie vielleicht glauben und daher extrem wichtig, einen Plan dafür zu haben
- Zero bytes in Oracle
- Ungültige byte sequence für die Kodierung „UTF8“: 0x00
- Ebenfalls sehr wahrscheinlich
Wie oben angemerkt, können die meisten dieser Probleme nicht einfach im Migrationsprozess gelöst werden, sondern es ist eine gewisse Vorbereitung notwendig. Das ist etwas, was Sie auf jeden Fall im Hinterkopf behalten sollten.
Wenn die Daten aus Oracle gelesen werden, muss dies in einer einzigen Transaktion geschehen, um die Konsistenz zu gewährleisten (oder zumindest in vielen Transaktionen, die in der Lage sind, denselben Snapshot der Daten zu sehen, wie es der CYBERTEC Migrator tut). Um dies zu erreichen, werden Sie am Ende eine lange Transaktion auf Oracle ausführen. Das kann an sich schon ein Problem sein, denn Oracle bricht eine Transaktion im Falle von „ORA-01555: snapshot to old“ ab.
Dieses Problem kann gelöst werden, indem vor der Datenmigration die Datenbank entsprechend konfiguriert wird. Eine entsprechende Konfiguration wird unter Verwendung großer UNDO-tablespaces erreicht.
Das Verschieben von Daten ist auch nicht ganz einfach. In Oracle finden Sie eine Reihe von Einschränkungen im OCI-Interface, die zu einer sehr schlechten Performance führen können, wenn große Felder nach PostgreSQL migriert werden. Diese Einschränkungen und Leistungsprobleme werden durch die Art und Weise verursacht, wie Oracle Daten liest, und in vielen Fällen kann der Client-Code nicht wirklich viel dagegen tun.
PostgreSQL und tablespaces
In Oracle ist das Konzept eines „Tablespace“ sehr wichtig, man könnte sogar sagen, dass Oracle im Grunde sein eigenes Dateisystem implementiert. Im Gegensatz dazu verlässt sich PostgreSQL stärker auf die Funktionalität des Betriebssystems (POSIX). Widerstehen Sie dem Drang, während der Migration Tablespaces anzulegen, diese haben in der PostgreSQL-Welt keinen Mehrwert.
Außerdem gehören Tablespaces in einer modernen Cloud-Umgebung meist der Vergangenheit an. Wenn Sie also eine neue Infrastruktur für PostgreSQL auf Kubernetes erstellen, wie sie z. B. von Scalefield zur Verfügung gestellt wird, besteht überhaupt keine Notwendigkeit, Tablespaces zu verwenden.
Konvertierung gängiger SQL Statements
Oracle hat einige Besonderheiten in seiner Variante von SQL, die bei der Migration von Code von Oracle nach PostgreSQL berücksichtigt werden müssen. Dieser Abschnitt zeigt einige der häufigsten Probleme, die bei Migrationen auftreten.
Joins in PostgreSQL
Oracle verwendet eine andere Syntax für Outer-Joins als andere Datenbanken Das sollten Sie bedenken, wenn Sie Code von Oracle nach PostgreSQL konvertieren. Das folgende Beispiel zeigt wie die Konvertierung funktioniert.
Oracle
SELECT b.col1, a.col2
FROM base_table b, attributes a
WHERE b.id=a.b_id(+);
PostgreSQL
SELECT b.col1, a.col2
FROM base_table b
LEFT JOIN attributes a ON b.id = a.b_id;
Es handelt sich hier um eine einfache, aber nervige Änderung.
Subselects und aliases
In Oracle benötigt ein subselect keinen Namen, es kan „anonym“ sein:
SELECT * FROM (SELECT * FROM some_table)
Sie können dies einfach in PostgreSQL umschreiben. Der folgende Code zeigt, wie das Gegenstück aussehen würde:
SELECT * FROM (SELECT * FROM some_table) AS your_fancy_name
NULL und empty strings
Oracle behandelt NULL
als empty string. Das hat einige Auswirkungen:
'hello' || NULL
ist nicht NULL
in Oracle. Um dieselbe Semantik in PostgreSQL zu erhalten, muss es daher in das folgende SQL-Fragment übersetzt werden.
Nutzen Sie
concat('hello', NULL)
oder
coalesce(strcol, '')
Die Migration von NULL-Statements kann zu Fehlern finden, die nur schwer aufzuspüren sind. Behalten Sie das bei der Migration im Hinterkopf, um später lästige Fehlersuche zu vermeiden.
Zeit-Funktionen in Oracle und PostgreSQL
Der meiste Oracle code nutzt proprietäre Funktionen wie …
SYSDATE
SYSTIMESTAMP
Es ist notwendig, diese wie folgt für PostgreSQL zu übersetzen:
clock_timestamp()
current_date
orcurrent_timestamp
Diese Änderung kann in der Regel einfach mittels Suchen & Ersetzen durchgeführt werden.
Migration von Sequenzen zu PostgreSQL
Oracle Datenbanken behandeln Squenzen ziemlich verschieden. Sie werden die folgende Methode sehen, um den nächsten Wert zu erzeugen:
asequence.NEXTVAL
Das PostgreSQL Äquivalent ist:
nextval('asequence')
Beide Datenbanken nutzen hier nicht den ANSI SQL-Weg, um Werte aus einer Sequenz abzurufen. Dieser würde wie folgt aussehen: NEXT VALUE FOR asequence
Keine Notwendigkeit für DUAL
In PostgreSQL benötigt eine Abfrage nicht nowendigerweise eine FROM-Klausel und daher besteht auch keine Notwendigkeit für DUAL.
SELECT * FROM dual;
… ist in PostgreSQL nicht notnwedig. orafce kann DUAL simulieren, aber es ist besser, es auf der PostgreSQL-Seite zu vermeiden.
DECODE
DECODE
ist ein Oracle-spezifisches Ding:
decode(expr, search, result [, search, result...] [, default])
Für PostgreSQL gibt es hier folgende Möglichkeiten:
- mit CASE / WHEN ersetzen
- mit der „decode“ Funktion von orafce ersetzen
In PostgreSQL funktioniert das ganze wie folgt:
CASE WHEN expr THEN expr [...] ELSE expr END
Generell kannDECODE
einfach ersetzt werden, da es keine komplizierte Logik enthält.
Rekursionen: CONNECT BY
In Oracle gibt es zwei Möglichkeiten, mit Rekursionen umzugehen:
WITH RECURSIVE: neue syntax
CONNECT BY: alte syntax
PostgreSQL hat eine komplette WITH RECURSIVE-Implementierung. Die alte CONNECT BY-Syntax ist sehr Oracle-spezifisch und kann in PostgreSQL (oder jeder anderen uns bekannten SQL-Datenbank-Engine) nicht verwendet werden.
Hier ein Beispiel, wie CONNECT BY in WITH RECURSIVE übersetzt werden kann:
SELECT empno, ename, level
FROM emp
START WITH empno = 7788
CONNECT BY PRIOR mgr = empno;
… versus …
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;
Konvertierung von PL/SQL und PL/Java
Der aufwendigste Teil einer jeden Migration ist die Migration des Stored-Procedure-Codes. Während PL/pgSQL und PL/SQL ziemlich ähnlich sind, gibt es dennoch Unterschiede, die beachtet werden müssen. Wenn Sie PL/Java auf der Oracle-Seite verwenden, können Sie das PostgreSQL-Pendant nutzen, aber wir würden vorschlagen, den Code lieber komplett zu ersetzen. Die Ausführung von serverseitigem Java ist in der PostgreSQL-Welt nicht allzu verbreitet.
PL/pgSQL ist ein Klon von PL/SQL, aber es ist so unterschiedlich, dass die Migration einige Aufmerksamkeit erfordert (z.B. RETURNS vs. RETURN). Der CYBERTEC Migrator verfügt über die Mittel zur automatischen Konvertierung von Code.
Autonome Transaktionen in Oracle und PostgreSQL
PostgreSQL hat keine solche Funktion wie PRAGMA AUTONOMOUS_TRANSACTION. Um diese fehlende Funktionalität muss man daher „herumarbeiten“. Im Allgemeinen ist es besser, autonome Transaktionen durch Savepoints zu ersetzen und zu versuchen, sicherzustellen, dass Transaktionen tatsächlich Commits durchführen können. In einigen seltenen Fällen gibt es keine Möglichkeit, autonome Transaktionen zu umgehen. Der Weg, dies in PostgreSQL zu modellieren, ist die Verwendung der dblink-Erweiterung.
BULK COLLECT: Use rows
BULK COLLECT ist auf der PostgreSQL-Seite nicht verfügbar. Der Weg, dies in PostgreSQL zu umgehen, ist ein zeilenbasierter Ansatz.
Seien Sie in diesem Fall vorsichtig. Die Effizienz könnte ein Problem werden. Es ist daher besser, SQL-Anweisungen anstelle von Schleifen zu verwenden.
Simulierung von Oracle Funktionen
In vielen Fällen bietet PostgreSQL dieselbe Funktionalität wie Oracle. Allerdings heißen die Funktionen oft verschieden.
Die orafce extension für PostgreSQL implementiert viele der Oracle-spezifischen Fuunktionen in PostgreSQL und sorgt so für eine erhebliche Reduktion des Codes, der händisch geändert werden muss.
Die folgende Aufzählung zeigt einige Code-Beispiele und verdeutlicht, wie diese Dinge für den Endnutzer dargestellt werden:
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;
PL/SQL packages
PostgreSQL kennt das Konzept von PL/SQL packages nicht. Es gibt aber Möglichkeiten, solche zu simulieren:
- Nutzen Sie CREATE EXTENSION – Sie können Code immer in Extensions packen
- Nutzen Sie Schemas für jedes „package“
Sie werden herausfinden, dass packages nicht wirklich ein Problem sind und dass Sie in PostgreSQL gut ohne auskommen.
Trigger-Migration
In PostgreSQL ruft ein Trigger immer eine Function auf. Der CREATE TRIGGER Befehl enthält den Code nicht direkt.
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 )
Das „Event“ kann eines der folgenden sein:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
Was diese Lösung so elegant macht, ist dass der Code einfach wiederverwendet werden kann. Zudem sind Trigger nicht notwendig, um Werte zu inkrementieren.
The beauty on the PostgreSQL side is that the code can easily be reused. What is also noteworthy here is that triggers to increment values are not needed. Sie können einfach Default-Spalten verwenden, um automatisch den nächsten Wert zu ermitteln und zu setzen.
LOGON
Trigger werden von PostgreSQL nicht unterstützt. Wenn diese diese Funktion benötigen, muss der Code in die Applikation geschoben werden.
Professionelle Hilfe
Wir bei CYBERTEC haben zahlreiche Kunden dabei unterstützt, von Oracle zu POstgreSQL zu migrieren. Weiters bieten wir den CYBERTEC Migration, um den Übergang so einfach wie möglich zu gestalten. Kontaktieren Sie uns und erhalten Sie Ihr persönliches Migrationsangebot. Wir bieten über 20 Jahre PostgreSQL Erfahrung, eine zeitnahe Lieferung und professionelle Betreuung.