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 typePossible PostgreSQL type
CHARchar, varchar, text
NCHARchar, varchar, text
VARCHARchar, varchar, text
VARCHAR2char, varchar, text, json, jsonb
NVARCHAR2char, varchar, text
CLOBchar, varchar, text, json, jsonb
LONGchar, varchar, text
RAWuuid, bytea
BLOBbytea
BFILEbytea (read-only)
LONG RAWbytea
NUMBERnumeric, float4, float8, char, varchar, text
NUMBER (n, m) with m<=0numeric, 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
DATEdate, timestamp, timestamptz, char, varchar, text
TIMESTAMPdate, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONEdate, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH LOCAL TIME ZONEdate, timestamp, timestamptz, char, varchar, text
INTERVAL YEAR TO MOTHinterval, char, varchar, text
INTERVAL DAY TO SECONDinterval, char, varchar, text
XML TYPExml, char, varchar, text
MDSYS.SDO_GEOMETRYgeometry (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 or current_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.

Kontaktieren Sie uns >>