Oracle zu PostgreSQL: Technischer Migrationsguide

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.

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.

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;