Migration einer Oracle Datenbank nach PostgreSQL

 

---

Einleitung

In diesem Artikel zeigen wir, wie Sie eine Oracle-Datenbank nach PostgreSQL migrieren.
Warum sollte man das tun? Ein häufiger Grund besteht darin, eine Anwendung plattformunabhängig zu machen, sodass sie auf mehreren Datenbanksystemen lauffähig ist – und damit den potenziellen Kundenkreis zu erweitern.

Es gibt verschiedene Tools auf dem Markt, die eine solche Migration versprechen. KeepTool ist eines davon – einfach zu bedienen und so konzipiert, Migrationsaufgaben zu automatisieren, ohne dass man die vollständige Kontrolle an ein „Black-Box“-Tool abgeben muss.

Im Laufe des letzten Jahres wurden Horas integrierte Migrationsfunktionen in zahlreichen Oracle-zu-PostgreSQL-Projekten verfeinert. In diesem Artikel erklären wir, wie Sie diese Funktionen effizient nutzen.

Migrationstätigkeiten

Die Migration einer Oracle-Datenbank nach PostgreSQL umfasst mehrere Schritte:

• Erstellen einer äquivalenten Tabellenstruktur in PostgreSQL, inklusive Fremdschlüsselbeziehungen, Primär-/Unique-Keys, Check-Constraints und Kommentaren

• Erneutes Erstellen von Views auf Basis von ANSI-SQL sowie kleineren Syntaxanpassungen

• Übertragen der Daten von Oracle nach PostgreSQL

• Neuumsetzung von PL/SQL-Logik in PL/pgSQL

Diese Schritte werden in den folgenden Kapiteln erläutert.

Erstellen einer Tabellenstruktur in PostgreSQL

Überprüfen Sie zunächst Ihr Oracle-Schema und entscheiden Sie, welche Tabellen wirklich migriert werden sollen. Manche Tabellen – etwa solche zur temporären Datensicherung – werden im Zielsystem möglicherweise nicht benötigt.

Horas Flagging-Funktion hilft Ihnen dabei, die zu migrierenden Tabellen zu markieren. In unserem Beispiel verwenden wir einen gelben Punkt als Kennzeichnung.

Wählen Sie anschließend eine oder mehrere Tabellen aus und verwenden Sie den Menüpunkt DDL for PostgreSQL DB. Zur Veranschaulichung beginnen wir mit einer einzelnen Tabelle.

Hora zeigt nun einen Dialog mit PostgreSQL-kompatiblem DDL. Zunächst wird das PostgreSQL-search_path gesetzt – vergleichbar mit dem aktuellen Schema in Oracle, aber nicht völlig identisch.

set search_path = hr;

Danach erzeugt Hora eine CREATE TABLE-Anweisung. Für jede Oracle-Spalte bestimmt Hora den passenden PostgreSQL-Datentyp. Obwohl beide Systeme ähnliche Datentypen besitzen, gibt es relevante Unterschiede, die Hora automatisch berücksichtigt.

Beispiel: PostgreSQL-Tabellendefinition

DROP TABLE IF EXISTS jobs;

CREATE TABLE jobs
(
   job_id varchar(10) NOT NULL,
   job_title varchar(35) NOT NULL,
   min_salary int4,
   max_salary int4
);

Falls im Oracle-Schema Kommentare vorhanden sind, werden diese in PostgreSQL übertragen:

COMMENT ON TABLE jobs IS
  'jobs table with job titles and salary ranges. Contains 19 rows.
  References with employees and job_history table.';

COMMENT ON COLUMN jobs.job_id IS
  'Primary key of jobs table.';

COMMENT ON COLUMN jobs.job_title IS
  'A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT';

Anschließend folgen die Constraints – mit automatisch erzeugten Kommentaren:

ALTER TABLE jobs ADD CONSTRAINT job_id_pk PRIMARY KEY ( job_id)
NOT DEFERRABLE INITIALLY IMMEDIATE;

COMMENT ON CONSTRAINT job_id_pk ON jobs IS
'The values in the column JOB_ID should be unique for each JOBS data record.';

Erstellen der Struktur für mehrere Tabellen

Bei mehreren Tabellen muss die Reihenfolge der DDL-Anweisungen berücksichtigt werden. Eine Fremdschlüsselbeziehung setzt z. B. voraus, dass der referenzierte Primär- oder Unique-Key bereits existiert.

Hora übernimmt diese Reihenfolge automatisch.
Markieren Sie dazu einfach mehrere Tabellen und wählen Sie erneut DDL for PostgreSQL DB.

Das generierte Skript folgt dieser Struktur:

1. Tabellen-DDL, inklusive Kommentare, Primär-/Unique-Keys und Indexe

2. Alle Fremdschlüssel

3. Triggerlogik

4. Berechtigungsvergabe

So kann das komplette Skript am Stück ausgeführt werden.

Migration der Triggerlogik

Für jeden Oracle-Trigger erzeugt Hora:

• eine PostgreSQL-Funktion (mit der Triggerlogik) und

• einen PostgreSQL-Trigger, der diese Funktion aufruft.

Beispiel:

create or replace function departments_bir_fnc()
returns trigger as
$body$
begin
IF coalesce( new.DEPARTMENT_ID,0) = 0 THEN
select nextval('departments_seq') INTO new.DEPARTMENT_ID ;
end IF;
return new;
end;
$body$ language plpgsql;

create or replace trigger departments_bir
before insert
on departments
for each row
execute function departments_bir_fnc();

Views in PostgreSQL erzeugen

Wechseln Sie nun die Ansicht von Tabellen zu Views. Bereits markierte Views können ebenfalls über DDL for PostgreSQL DB verarbeitet werden.

Hora generiert eine vollständige, PostgreSQL-kompatible Definition. Oracle-spezifische Joins werden automatisch in ANSI-konforme Syntax umgewandelt. Bei komplexeren Views berücksichtigt Hora zudem Funktionsaufrufe und weitere Oracle-spezifische Elemente.

Beispiel:

set search_path = hr;

-- VIEW: emp_details_view
drop view if exists emp_details_view;

create or replace view emp_details_view
(
   employee_id, job_id, manager_id, department_id, first_name, last_name,
   salary, commission_pct, location_id, department_name, country_id, city,
   state_province, job_title, country_name, region_name
)
AS
select
   E.EMPLOYEE_ID, E.JOB_ID, E.MANAGER_ID, E.DEPARTMENT_ID,
   E.FIRST_NAME, E.LAST_NAME, E.SALARY, E.COMMISSION_PCT,
   D.LOCATION_ID, D.DEPARTMENT_NAME,
   L.COUNTRY_ID, L.CITY, L.STATE_PROVINCE,
   J.JOB_TITLE,
   C.COUNTRY_NAME,
   R.REGION_NAME
from EMPLOYEES E
join DEPARTMENTS D on E.DEPARTMENT_ID = D.DEPARTMENT_ID
join LOCATIONS L on D.LOCATION_ID = L.LOCATION_ID
join COUNTRIES C on L.COUNTRY_ID = C.COUNTRY_ID
join REGIONS R on C.REGION_ID = R.REGION_ID
join JOBS J on J.JOB_ID = E.JOB_ID;

COMMENT ON VIEW emp_details_view IS
  'Employees details view';

-- Grant privileges
grant read on emp_details_view to keeptool;

grant select on emp_details_view to keeptool;

Auch mehrere Views können gemeinsam verarbeitet werden.

Übertragen der Daten von Oracle nach PostgreSQL

Hora erleichtert die Datenmigration, indem es PostgreSQL-kompatible INSERT-Skripte erzeugt.

Öffnen Sie die Tabelle im Bereich Tables → Data Content oder im Data Content Browser.
Rechtsklicken Sie auf das Datenraster und wählen Sie Create INSERT Script.

Im folgenden Dialog deaktivieren Sie alle Oracle-spezifischen Optionen. Die Option Use timestamp literals sollte jedoch aktiviert bleiben.

Beispiel:

INSERT INTO JOBS( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES( 'AD_PRES', 'President', 20080, 40000);
INSERT INTO JOBS( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES( 'AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO JOBS( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)

...
COMMIT;

Dieses Verfahren können Sie auf jede markierte Tabelle anwenden.

Umsetzung von Oracle-PL/SQL in PL/pgSQL

Wir haben bereits gezeigt, wie Hora Triggerlogik nach PL/pgSQL überträgt.
In vielen Oracle-Datenbanken gibt es jedoch deutlich mehr PL/SQL-Code – etwa Packages, Prozeduren und Funktionen.

Ein Folgeartikel wird detailliert erklären, wie Sie PL/SQL-Logik nach PL/pgSQL migrieren, welche Unterschiede zu beachten sind und welche Best Practices sich bewährt haben.

SQL-Abfragen für PostgreSQL umschreiben

Hora bietet ein integriertes Werkzeug, das Oracle-SQL-Abfragen automatisch in PostgreSQL-SQL konvertiert – einschließlich Anpassungen für die ANSI-Kompatibilität. Diese Funktion ist in mehreren Situationen hilfreich:

• Abfragen innerhalb von PL/SQL-Code, die beim Konvertieren nach PL/pgSQL nicht automatisch umgewandelt werden.
  Diese müssen einzeln mithilfe unseres Tools konvertiert werden.

• Abfragen innerhalb einer Anwendung, die ebenfalls von Oracle nach PostgreSQL migriert wird.

In allen Fällen ist der Workflow einfach:
Markieren Sie eine beliebige SQL-Abfrage im SQL-Editor, klicken Sie mit der rechten Maustaste und wählen Sie „Rewrite SQL for PostgreSQL“.

„Rewrite SQL for PostgreSQL“ menu item

Beispiel

Nach der Auswahl versucht KeepTool, die Abfrage automatisch umzuschreiben.
Das erzeugte Ergebnis sieht beispielsweise so aus:

select 
   D.DEPARTMENT_NAME, D.DEPARTMENT_ID, 
   E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.DEPARTMENT_ID 
from DEPARTMENTS D 
right join EMPLOYEES E on D.DEPARTMENT_ID = E.DEPARTMENT_ID 

Der Algorithmus funktioniert bei den meisten Abfragen sehr zuverlässig.

Bei sehr komplexen SQL-Statements kann jedoch eine vollständige automatische Konvertierung nicht immer gewährleistet werden.
In solchen Fällen empfiehlt es sich, Subselects, UNION-Teile oder andere Komponenten der Abfrage separat umzuwandeln.

---

Zusammenfassung

Die Migration einer Oracle-Datenbank nach PostgreSQL umfasst das Konvertieren der Tabellen- und View-Struktur, das Übertragen der Daten sowie die Neuerstellung der PL/SQL-Logik. KeepTools Hora erleichtert diese Schritte erheblich und bietet dennoch volle Kontrolle über den Migrationsprozess.

 

---