Exemple de migration ora2pg : Schéma Oracle HR vers PostgreSQL

Le schéma RH d'Oracle est ce qui se rapproche le plus d'une référence universelle dans le monde des bases de données.

Chaque DBA Oracle l'a vu.

Chaque consultant en migration l'utilise pour expliquer le déroulement du processus.

Ce qui le rend vraiment utile comme étude de cas, ce n'est pas sa taille — il s'agit de sept tables et de 107 employés.

Ce qui le rend utile, c'est qu'il contient, sous une forme minimale, les schémas exacts qui rendent les migrations d'Oracle vers PostgreSQL non triviales : séquences liées à des déclencheurs, procédures stockées, incompatibilités de types qui compilent proprement et échouent à l'exécution.

J'ai exécuté la migration complète en utilisant ora2pg 25.0 avec Oracle 19c comme source et PostgreSQL 18 comme cible.

Ce post couvre les cinq choses qui ont nécessité une intervention manuelle — et pourquoi chacune d'elles est beaucoup plus importante sur un schéma de production que sur une démo.

Ce que les RH contiennent

Le schéma RH est le schéma de référence standard d'Oracle, livré avec chaque installation Oracle et disponible sur GitHub.

Elle possède sept tables couvrant la hiérarchie RH standard : régions, pays, sites, départements, postes, employés et antécédents professionnels.

Il a trois séquences autonomes (employes_seq, departments_seq, locations_seq), une vue (EMP_DETAILS_VIEW), deux procédures stockées (sécuriser les DML, ajouter_historique_professionnel), et cinq déclencheurs.

Trois de ces déclencheurs existent uniquement pour appeler sequence.NEXTVAL sur INSERT — un modèle que les développeurs Oracle utilisaient auparavant DÉFAUT avec des séquences fut largement adopté.

Les deux restants implémentent la logique métier : l'un restreint les opérations DML aux heures de bureau, l'autre écrit une ligne d'historique lorsqu'un employé change de poste.

ora2pg évalue le schéma RH B-5: réécriture du code requise, environ une journée de travail pour un expert PostgreSQL.

La classification est entièrement alimentée par les procédures stockées et les déclencheurs.

Les tables, les vues et les données migrent sans intervention.

1. Mappage des types NUMÉRIQUES : le piège des colonnes monétaires

ora2pg a deux directives qui gèrent la plupart NOMBRE colonnes automatiquement.

PG_INTEGER_TYPE 1 cartes NOMBRE(p) colonnes sans échelle vers le type entier correct : la précision 1-4 devient petit entier, 5–9 devient entier, 10–18 devient grand entier.

Pour le schéma RH, cela signifie NUMÉRO_EMPLOYÉ NUMBER(6) devient ENTIER, DEPARTEMENT_ID NOMBRE(4) devient PETIT ENTIER — aucun travail manuel nécessaire.

Le piège est PG_NUMERIC_TYPE 1, qui gère les colonnes ayant une échelle déclarée.

Cette directive ne se contente pas de regarder le type déclaré — elle interroge les valeurs de données réelles.

Si un NOMBRE(8,2) la colonne contient actuellement uniquement des valeurs entières, elle associe la colonne à double précision plutôt que numérique.

EMPLOYÉS.SALAIRE est NOMBRE(8,2) dans Oracle.

Chaque salaire dans le jeu de données RH est un nombre entier : 24000, 17000, 9000. Avec PG_NUMERIC_TYPE 1 active, ora2pg maps SALAIRE à double précision.

double précision est un type à virgule flottante.

Les types à virgule flottante sont inappropriés pour les valeurs monétaires — ils ne peuvent pas représenter exactement toutes les fractions décimales, ce qui produit des erreurs d'arrondi dans les calculs financiers.

Le type correct pour le salaire est numérique.

La correction est un MODIFICATION_TYPE substituer

MODIFY_TYPE    EMPLOYEES:SALARY:numeric

Dans chaque migration de production réelle que j'évalue, les colonnes de salaire et autres colonnes monétaires nécessitent cette substitution.

Le PG_NUMERIC_TYPE directive fait son travail correctement — elle déduit les types des données.

L'inférence est tout simplement fausse pour l'argent, où la précision déclarée compte plus que ce que les données actuelles contiennent.

2. Le modèle de déclencheur de séquence

Dans Oracle, le modèle standard pour les clés primaires auto-incrémentées était :

1. Créer une séquence autonome.
2. Créer un déclencheur `BEFORE INSERT` qui s'exécute pour chaque ligne et attribue sequence.NEXTVAL à la colonne ID.

Voici comment les RH sont construites. EMPLOYÉS.EMPLOYÉ_ID n'a pas DÉFAUT clause dans Oracle — le déclencheur la remplit à chaque INSERT.

Dans PostgreSQL, l'équivalent est une colonne DEFAULT nextval('sequence_name').

Lorsqu'une ligne est insérée sans spécifier la colonne ID, PostgreSQL appelle nextval() automatiquement. Aucun déclencheur n'est nécessaire.

ora2pg convertit les déclencheurs de séquence Oracle — il les exporte en tant que fonctions de déclenchement PostgreSQL.

Mais l'exportation TABLE n'ajoute pas DEFAULT nextval(...) aux définitions de colonnes, car Oracle n'en a jamais eu. Après avoir chargé le DDL exporté, les tables n'ont pas de génération d'ID automatique. Tout INSERT sans un explicit identifiant_employé échouera avec La valeur nulle dans la colonne viole la contrainte NOT NULL.

La solution consiste à ajouter les valeurs par défaut après le chargement des déclencheurs :

ALTER TABLE hr.employees   ALTER COLUMN employee_id   SET DEFAULT nextval('hr.employees_seq');
ALTER TABLE hr.departments ALTER COLUMN department_id SET DEFAULT nextval('hr.departments_seq');
ALTER TABLE hr.locations   ALTER COLUMN location_id   SET DEFAULT nextval('hr.locations_seq');

Liez également chaque séquence à sa colonne avec PROPRIÉTÉ DE de sorte que la suppression de la table se propage automatiquement à la séquence.

Sans PROPRIÉTÉ DE, les séquences deviennent orphelines et doivent être abandonnées séparément.

3. %TYPE dans les signatures de paramètres de procédure

ajouter_historique_professionnel est exporté par ora2pg avec la syntaxe du type ancré d'Oracle dans sa liste de paramètres :

-- What ora2pg exports
CREATE OR REPLACE PROCEDURE hr.add_job_history (
    p_emp_id        job_history.employee_id%TYPE,
    p_start_date    job_history.start_date%TYPE,
    ...

PostgreSQL prend en charge %TYPE à l'intérieur du corps d'une fonction ou d'une procédure pour les déclarations de variables. Il ne prend pas en charge %TYPE dans la signature du paramètre.

Le chargement du fichier exporté échoue immédiatement avec une erreur de syntaxe.

La correction consiste à substituer les types concrets :

CREATE OR REPLACE PROCEDURE hr.add_job_history (
    p_emp_id        integer,
    p_start_date    timestamp,
    p_end_date      timestamp,
    p_job_id        varchar(10),
    p_department_id smallint
)

Les types corrects proviennent de l'analyse des colonnes effectuée avant la migration — EMPLOYEE_ID est ENTIER, DATE_DEBUT est HORODATEUR, et ainsi de suite.

C'est pourquoi l'étape d'analyse des types n'est pas facultative : vous avez besoin des types PostgreSQL finaux pour écrire cette correction.

Dans un schéma de production comportant des dizaines de procédures stockées, ce modèle apparaît fréquemment.

Chaque procédure qui utilise des paramètres de type ancré dans Oracle nécessite la même substitution manuelle avant de pouvoir être chargée dans PostgreSQL.

4. L'incompatibilité de type statement_timestamp()

Le update_job_history déclencher des appels ajouter_historique_professionnel pour écrire un historique de ligne lorsqu'un employé change de poste.

Il doit passer l'heure actuelle en tant que date_de_fin argument.

ora2pg convertit Oracle en SYSDATE à statement_timestamp(). statement_timestamp() retours horodatage avec fuseau horaire dans PostgreSQL. ajouter_historique_professionnel attend horodatage (sans fuseau horaire) pour date_de_fin.

PostgreSQL ne convertit pas implicitement timestamptz à horodatage dans les appels de procédure.

La fonction de déclenchement se charge sans erreur — CRÉER UNE FONCTION réussit.

L'incohérence de type n'apparaît qu'à l'exécution, la première fois qu'un employé change de poste ou de département.

C'est la catégorie de bug de migration la plus insidieuse : du code qui compile sans erreur et qui échoue en production.

J'utilise plpgsql_check pour intercepter exactement cette classe d'erreur.

Il effectue une analyse statique sur les corps de fonctions compilés et signale les écarts de types, les variables manquantes et les appels de procédure invalides, et ce, avant même tout test d'application.

CREATE EXTENSION plpgsql_check;

L'exécution de plpgsql_check sur la fonction de déclenchement de ce laboratoire a produit :

trigger_fct_update_job_history() | error | procedure add_job_history(integer,
timestamp without time zone, timestamp with time zone, character varying, smallint)
does not exist

La correction est un seul moulage :

CALL add_job_history(OLD.employee_id, OLD.hire_date, statement_timestamp()::TIMESTAMP,
                     OLD.job_id, OLD.department_id);

plpgsql_check n’est pas une partie intégrante du flux de travail ora2pg — il n’est pas exécuté par défaut, n’est pas mentionné dans la documentation ora2pg et n’est inclus par défaut dans aucun package PostgreSQL.

Je l'exécute à chaque migration avant de transmettre le code migré à l'équipe d'application. Il a détecté des bugs d'exécution sur chaque schéma de production que j'ai migré.

5. Les contraintes FK ne sont pas réappliquées après le chargement des données

L'exportation COPY d'ora2pg supprime toutes les contraintes de clé étrangère au début du fichier de données, charge les données, puis valide.

Il ne rétablit pas les contraintes FK après le commit.

Ceci est un bug connu — problème #1960 dans le dépôt darold/ora2pg.

Après le chargement du fichier de données, la base de données migrée ne possède aucune contrainte de clé étrangère.

Le ora2pg -t TEST La comparaison montrera que toutes les tables ont 0 FK dans PostgreSQL, même si les définitions de FK existent dans le DDL exporté.

La solution consiste à réappliquer les contraintes de clé étrangère depuis l'exportation de la table après le chargement des données :

grep 'ADD CONSTRAINT.*FOREIGN KEY' HR_tables.sql | \
  PGPASSWORD=hr psql -U hr -d hrdb -h localhost

Pour le schéma RH, cela restaure 10 contraintes de clé étrangère sur cinq tables.

Sur un schéma de production comportant des centaines de tables, le nombre de clés étrangères (FK) est un point de contrôle de validation essentiel : s'il ne correspond pas au décompte d'Oracle après la migration, le modèle de données est incomplet.

Exécution de la migration : chaque commande dans l'ordre

Environnement

Deux VM sur le même réseau. srv1 (192.168.0.180) exécute Oracle 19c avec le schéma HR dans PDB pdb1. srv2 (192.168.0.181) exécute Ubuntu avec PostgreSQL 18 et ora2pg installés. Toutes les commandes ci-dessous sont exécutées sur srv2 sauf indication contraire.

Étape 1 — Collecter les statistiques Oracle

Exécutez ceci sur srv1 Avant de générer le rapport d'évaluation, les estimations de coûts d'ora2pg sont basées sur les statistiques stockées d'Oracle — des statistiques obsolètes produisent des estimations inexactes du nombre de lignes et des unités de migration.

-- On Oracle (srv1)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS('HR');
  DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

Étape 2 — Créez le répertoire de sortie

mkdir -p /home/fernando/ora2pg-hr/output

Étape 3 — ora2pg.conf minimale

# /etc/ora2pg/ora2pg.conf
ORACLE_DSN      dbi:Oracle:host=192.168.0.180;service_name=pdb1;port=1521
ORACLE_USER     system
ORACLE_PWD      <system password>
SCHEMA          HR
EXPORT_SCHEMA   1
OUTPUT_DIR      /home/fernando/ora2pg-hr/output

Utilisation système, pas rh — l'exportation COPY se lit v$base de données qui nécessite le privilège DBA.

Étape 4 — Générer le rapport d'évaluation

ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html \
  > /home/fernando/ora2pg-hr/output/report-hr.html

Ouvrir le HTML dans un navigateur.

Scores RH B-5, environ une journée-homme.

Les moteurs de coûts sont les deux procédures stockées et les deux déclencheurs de logique métier.

Étape 5 — Analyser les types de colonnes

ora2pg -t SHOW_COLUMN > /home/fernando/ora2pg-hr/output/columns-hr.txt

Puis interroger les plages de données réelles sur Oracle pour tout NOMBRE colonne sans précision déclarée :

-- On Oracle (srv1)
SELECT 'REGIONS.REGION_ID'   AS col, MIN(region_id), MAX(region_id) FROM regions
UNION ALL
SELECT 'COUNTRIES.REGION_ID',         MIN(region_id), MAX(region_id) FROM countries;

Les deux au maximum à 50 — ENTIER est le mappage correct.

Étape 6 — Ajouter MODIFY_TYPE et réenregistrer la configuration

MODIFY_TYPE    REGIONS:REGION_ID:integer,COUNTRIES:REGION_ID:integer,EMPLOYEES:SALARY:numeric

EMPLOYÉS:SALAIRE:numérique est requis car PG_NUMERIC_TYPE 1 inspecte les données réelles, ne trouve que des valeurs de salaire en nombres entiers, et mappe NOMBRE(8,2) à double précision — inapproprié pour une colonne monétaire.

Les trois remplacements sur une seule ligne — MODIFICATION_TYPE ignore tout ce qui suit la première ligne.

Étape 7 — Exécuter les exportations

ora2pg -t TABLE           -o HR_tables.sql           2>&1 | tee output/table-export-hr.log
ora2pg -t VIEW            -o HR_views.sql            2>&1 | tee output/view-export-hr.log
ora2pg -t SEQUENCE        -o HR_sequences.sql        2>&1 | tee output/sequence-export-hr.log
ora2pg -t SEQUENCE_VALUES -o HR_sequence_values.sql  2>&1 | tee output/seqval-export-hr.log
ora2pg -t TRIGGER         -o HR_triggers.sql         2>&1 | tee output/trigger-export-hr.log
ora2pg -t PROCEDURE       -o HR_procedures.sql       2>&1 | tee output/procedure-export-hr.log
ora2pg -t COPY            -o HR_data.sql             2>&1 | tee output/copy-export-hr.log

L'exportation TABLE fait non inclure les séquences — l'exportation SEQUENCE séparée est obligatoire. Sans elle, le chargement des sequence_values à l'étape 12 échoue avec le message “ relation does not exist ”.

Étape 8 — Rédiger les fichiers de correctifs manuels

Deux fichiers qui ne peuvent pas être générés à partir de la sortie ora2pg.

HR_procedures_fixed.sql — remplace %TYPE paramètres ancrés avec des types concrets :

SET search_path = hr,public;

CREATE OR REPLACE PROCEDURE hr.add_job_history (
    p_emp_id        integer,
    p_start_date    timestamp,
    p_end_date      timestamp,
    p_job_id        varchar(10),
    p_department_id smallint
) AS $body$
BEGIN
  INSERT INTO job_history(employee_id, start_date, end_date, job_id, department_id)
    VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END;
$body$
LANGUAGE PLPGSQL SECURITY DEFINER;

CREATE OR REPLACE PROCEDURE hr.secure_dml () AS $body$
BEGIN
  IF TO_CHAR(clock_timestamp(), 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
      OR TO_CHAR(clock_timestamp(), 'DY') IN ('SAT', 'SUN') THEN
      RAISE EXCEPTION '%', 'You may only make changes during normal office hours'
        USING ERRCODE = '45205';
  END IF;
END;
$body$
LANGUAGE PLPGSQL SECURITY DEFINER;

HR_corrections_déclencheurs.sql — lance statement_timestamp() à éviter timestamptz inadéquation, et ajoute les valeurs par défaut de séquence aux colonnes d'ID :

SET search_path = hr,public;

CREATE OR REPLACE FUNCTION hr.trigger_fct_update_job_history() RETURNS trigger AS $BODY$
BEGIN
  CALL add_job_history(OLD.employee_id, OLD.hire_date,
                       statement_timestamp()::TIMESTAMP,
                       OLD.job_id, OLD.department_id);
  RETURN NEW;
END
$BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;

ALTER TABLE hr.employees   ALTER COLUMN employee_id   SET DEFAULT nextval('hr.employees_seq');
ALTER TABLE hr.departments ALTER COLUMN department_id SET DEFAULT nextval('hr.departments_seq');
ALTER TABLE hr.locations   ALTER COLUMN location_id   SET DEFAULT nextval('hr.locations_seq');

ALTER SEQUENCE hr.employees_seq   OWNED BY hr.employees.employee_id;
ALTER SEQUENCE hr.departments_seq OWNED BY hr.departments.department_id;
ALTER SEQUENCE hr.locations_seq   OWNED BY hr.locations.location_id;

Étape 9 — Créer la base de données

sudo -u postgres psql -c "CREATE USER hr WITH PASSWORD 'hr';"
sudo -u postgres psql -c "CREATE DATABASE hrdb OWNER hr;"

Étape 10 — Charger dans l'ordre des dépendances

# Tables first — everything else depends on them
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_tables.sql

# Sequences — must exist before data load and before sequence_values
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_sequences.sql

# Data — loads rows via COPY FROM stdin; FKs are dropped inside the file before load
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_data.sql

Étape 11 — Réappliquer les contraintes FK

La commande COPY d'ora2pg supprime toutes les contraintes FK avant le chargement des données et ne les ajoute jamais. Sans cette étape, la base de données n'a aucune contrainte FK après l'étape 10.

(echo "SET search_path TO hr;"; \
 grep 'ADD CONSTRAINT.*FOREIGN KEY' \
   /home/fernando/ora2pg-hr/output/HR_tables.sql) | \
  PGPASSWORD=hr psql -U hr -d hrdb -h localhost

Vérifier le compte :

PGPASSWORD=hr psql -U hr -d hrdb -h localhost -c "
SELECT COUNT(*) FROM information_schema.table_constraints
WHERE constraint_schema = 'hr' AND constraint_type = 'FOREIGN KEY';"

Attendu : 10

Étape 12 — Charger les fichiers restants

# Reset sequences to Oracle's last values — run after data, not before
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_sequence_values.sql

# Procedures — must load before triggers (triggers call these procedures)
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_procedures_fixed.sql

# Triggers — after procedures
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_triggers.sql

# Trigger fixes — replaces the timestamptz trigger function, adds sequence defaults
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_trigger_fixes.sql

# View — after all tables are in place
PGPASSWORD=hr psql -U hr -d hrdb -h localhost \
  -f /home/fernando/ora2pg-hr/output/HR_views.sql

Étape 13 — Analyse statique avec plpgsql_check

Installez l'extension, puis vérifiez tout le code stocké avant tout test d'application :

sudo apt install -y postgresql-18-plpgsql-check
sudo -u postgres psql -d hrdb -c "CREATE EXTENSION plpgsql_check;"

Vérifier les procédures et les fonctions :

PGPASSWORD=hr psql -U hr -d hrdb -h localhost -c "
SELECT pg_proc.oid::regprocedure AS function, level, message
FROM pg_proc,
     plpgsql_check_function_tb(pg_proc.oid) AS pcf
WHERE pronamespace = 'hr'::regnamespace
  AND prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql')
  AND prorettype <> 'trigger'::regtype
ORDER BY 1, 2;"

Vérifier les fonctions de déclenchement :

PGPASSWORD=hr psql -U hr -d hrdb -h localhost -c "
SELECT pg_proc.oid::regprocedure AS function, level, message
FROM pg_proc
JOIN pg_trigger ON pg_trigger.tgfoid = pg_proc.oid,
     plpgsql_check_function_tb(pg_proc.oid, pg_trigger.tgrelid) AS pcf
WHERE pg_proc.pronamespace = 'hr'::regnamespace
  AND pg_proc.prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql')
  AND pg_proc.prorettype = 'trigger'::regtype
ORDER BY 1, 2;"

Attendu après l'application de toutes les corrections : (0 lignes) sur les deux requêtes.

Étape 14 — Lancez la comparaison TEST

Ajoutez la connexion PostgreSQL à ora2pg.conf:

PG_DSN      dbi:Pg:dbname=hrdb;host=localhost;port=5432
PG_USER     hr
PG_PWD      hr

ora2pg -t TEST 2>&1 | tee /home/fernando/ora2pg-hr/output/migration_diff-hr.txt

À quoi ressemble le rapport final de TEST

Après que les cinq correctifs sont appliqués, ora2pg -t TEST compare le code source Oracle et la cible PostgreSQL côte à côte. Chaque section doit afficher OK, sauf deux faux positifs connus :

VALEUR PAR DÉFAUT DE COLONNE DIFF — Oracle a utilisé des déclencheurs pour remplir les colonnes d'ID ; PostgreSQL utilise DEFAULT nextval(...). Les trois tables auxquelles nous avons ajouté des valeurs par défaut présenteront une différence de comptage. C'est la conception correcte de PostgreSQL, pas une erreur.

FONCTION COMPTER DIFF — PostgreSQL stocke la logique des déclencheurs dans un objet de fonction distinct qui n'a pas d'équivalent dans le dictionnaire de données d'Oracle. Les deux fonctions de déclencheur (trigger_fct_secure_employees, déclencheur_fct_mise_à_jour_historique_poste) apparaissent comme des fonctions PostgreSQL supplémentaires sans équivalent Oracle. Ceci est également attendu.

Toute autre ligne DIFF dans la sortie de TEST est un réel problème qui doit être résolu avant le cutover.

En résumé

RH est un petit schéma. Corriger cinq problèmes sur un jeu de données de 107 lignes représente une demi-journée de travail.

Sur un schéma de production réel — 50 tables, 30 procédures stockées, des chaînes de déclencheurs complexes, 10 millions de lignes — les mêmes cinq catégories de problèmes existent, multipliées par 10 en volume.

Le piège de la colonne monétaire affecte tous les schémas qui stockent des prix, des salaires ou des chiffres financiers.

Le %TYPE Le problème de paramètre affecte tous les schémas avec des procédures stockées.

Le statement_timestamp() une incompatibilité de type affecte chaque déclencheur qui enregistre un horodatage actuel.

Le problème de réapplication des clés étrangères affecte tous les schémas comportant des clés étrangères.

Le modèle séquence-déclencheur-par-défaut affecte tous les schémas Oracle construits avant la version 12c.

La valeur de l'exécution de la migration sur les RH en premier n'est pas la migration elle-même, mais la création de la liste de contrôle que vous appliquez à tous les schémas suivants.

Si vous prévoyez une migration d'Oracle vers PostgreSQL et souhaitez une évaluation indépendante de la complexité, de l'effort et des risques avant de vous engager sur un calendrier, Je propose un audit de migration à prix fixe qui produit exactement cela.