Migration Oracle -> PostgreSQL : il n’y a que le driver à changer ! Non ?

Migration Oracle -> PostgreSQL : il n’y a que le driver à changer ! Non ? Cet article n’a pas vocation à encenser PostgreSQL ni à dénigrer Oracle. Il s’agit de vous présenter les problèmes que nous avons rencontrés, comment nous les avons résolus et, pour certains, comment nous aurions pu les anticiper. Notre client a décidé de migrer ses bases de données Oracle vers PostgreSQL et nous a sollicités pour réaliser cette migration. Sur le papier, cela semble simple : il suffit de changer le driver Oracle par le driver PostgreSQL et le tour est joué. Mais le plan ne s'est pas déroulé sans accrocs. Un peu d’histoire : Pour comprendre une partie des problèmes rencontrés, il faut revenir sur l’histoire de SQL : 1970 : IBM créé SEQUEL, un langage permettant d’interroger et éditer une base de données relationnelles, il sera renommé par la suite SQL en 1975. 1979 : Relational Software Inc (devenue Oracle Corporation) s’inspire des travaux d’IBM et présente la première version commerciale du SGBD. 1986 : toutes les plus grandes entreprises du secteur se réunissent pour établir la première norme SQL. Elles sont toutes présentes sauf Oracle. 1995 : la troisième révision de la norme SQL voit le jour. PostgreSQL est créé : ils implémentent la norme. On remarque qu’Oracle a suivi son propre chemin vis-à-vis de la norme SQL alors que PostgreSQL en bon élève a mis en œuvre tout ou partie de cette norme. Après des années d'évolution de la norme, PostgreSQL demeure parmi les plus conformes. Oracle rattrape son retard petit à petit et se rapproche de plus en plus de la norme. Néanmoins, le mal est fait. Durant des années, les fonctionnalités propres à Oracle ont été disséminées dans de trop nombreux projets. C’est pourquoi il est nécessaire de migrer ces fonctionnalités vers celles équivalentes en PostgreSQL. Migration de la base de données : Quand on évoque la migration d’une base de données, il faut penser à tout ce qui la structure : tables, séquences, vues, vues matérialisées, procédures stockées, triggers, fonctions, grants, dblink, types etc … Tout cela sans compter les données ! Heureusement, cela ne se fait pas manuellement, il existe plusieurs logiciels sur le marché capables d’effectuer cette tâche. Nous allons vous présenter celui qui a été utilisé sur notre projet : ora2pg. Cet utilitaire a été créé en 2001, il est open source et est toujours maintenu par la communauté. Il fait partie des leaders du marché. L’utilisation la plus simple de cet utilitaire s’effectue en quelques étapes : Exécution des scripts d’export de la structure de la base de données Oracle ainsi que des données au format SQL. Conversion vers un format SQL compatible PostgreSQL. Exécution des scripts d’import dans la base de données PostgreSQL. ora2pg est également capable de convertir du PL/SQL en PL/pgSQL. Il propose beaucoup plus de fonctionnalités. Il permet notamment de choisir les objets que nous souhaitons migrer, convertir certains types vers d’autres ou encore, d’estimer le coût en jour-homme et la complexité de tout ce qui n’a pas pu être migré de manière automatique. La documentation est bien écrite et complète. La théorie ayant été énoncée, place au concret et à l’analyse de l’application à migrer. Étude du patrimoine applicatif : Lorsqu’un ORM (Object-Relational Mapping) tel que Spring Data JPA ou Hibernate est utilisé dans les composants, cela apporte une couche d’abstraction vis-à-vis de l’utilisation de la base de données. Bien évidemment, cette abstraction facilite grandement la migration d’un SGBD vers un autre. Malheureusement, ce n’est pas le cas sur les projets que nous avons dû migrer. Les accès à la base de données sont réalisés via des requêtes SQL natives. Les composants sont développés en Java, il s’agit d’une IHM et de quelques batchs. La particularité de l’IHM est qu’elle intègre des dépendances d’applications tierces utilisant d’autres bases de données Oracle ne faisant pas partie du projet de migration à court terme. La stratégie de notre client est de ne plus utiliser ces dépendances au profit d’appels HTTP à des API tierces. Nous avons pu retrouver toutes les fonctionnalités qu’offraient ces dépendances via les ressources exposées par les API tierces. Le périmètre de la migration semble plutôt simple : Migrer la base de données Oracle en PostgreSQL Remplacer le driver Oracle par le driver PostgreSQL dans chaque composant Remplacer les dépendances tierces par des appels HTTP à des API tierces Adapter les requêtes SQL utilisant des fonctions spécifiques à Oracle Début des travaux : Une fois la base de données migrées en utilisant ora2pg décrit précédemment, nous sommes passés sur l’adaptation des requêtes SQL. Il existe plus d’une centaine de fonctions dans Oracle et PostgreSQL. Celles qui respectent la norme sont équivalentes. Nous allons passer en revue les fonctions couramment utilisées et spécifiques à Oracle et leur équivalence Po

May 7, 2025 - 16:07

Migration Oracle -> PostgreSQL : il n’y a que le driver à changer ! Non ?

Cet article n’a pas vocation à encenser PostgreSQL ni à dénigrer Oracle. Il s’agit de vous présenter les problèmes que nous avons rencontrés, comment nous les avons résolus et, pour certains, comment nous aurions pu les anticiper.

Notre client a décidé de migrer ses bases de données Oracle vers PostgreSQL et nous a sollicités pour réaliser cette migration.
Sur le papier, cela semble simple : il suffit de changer le driver Oracle par le driver PostgreSQL et le tour est joué.
Mais le plan ne s'est pas déroulé sans accrocs.

Un peu d’histoire :

Pour comprendre une partie des problèmes rencontrés, il faut revenir sur l’histoire de SQL :

1970 : IBM créé SEQUEL, un langage permettant d’interroger et éditer une base de données relationnelles, il sera renommé par la suite SQL en 1975.
1979 : Relational Software Inc (devenue Oracle Corporation) s’inspire des travaux d’IBM et présente la première version commerciale du SGBD.
1986 : toutes les plus grandes entreprises du secteur se réunissent pour établir la première norme SQL. Elles sont toutes présentes sauf Oracle.
1995 : la troisième révision de la norme SQL voit le jour. PostgreSQL est créé : ils implémentent la norme.

On remarque qu’Oracle a suivi son propre chemin vis-à-vis de la norme SQL alors que PostgreSQL en bon élève a mis en œuvre tout ou partie de cette norme.

Après des années d'évolution de la norme, PostgreSQL demeure parmi les plus conformes. Oracle rattrape son retard petit à petit et se rapproche de plus en plus de la norme. Néanmoins, le mal est fait. Durant des années, les fonctionnalités propres à Oracle ont été disséminées dans de trop nombreux projets. C’est pourquoi il est nécessaire de migrer ces fonctionnalités vers celles équivalentes en PostgreSQL.

Migration de la base de données :

Quand on évoque la migration d’une base de données, il faut penser à tout ce qui la structure : tables, séquences, vues, vues matérialisées, procédures stockées, triggers, fonctions, grants, dblink, types etc …

Tout cela sans compter les données ! Heureusement, cela ne se fait pas manuellement, il existe plusieurs logiciels sur le marché capables d’effectuer cette tâche.

Nous allons vous présenter celui qui a été utilisé sur notre projet : ora2pg.

Cet utilitaire a été créé en 2001, il est open source et est toujours maintenu par la communauté. Il fait partie des leaders du marché.

L’utilisation la plus simple de cet utilitaire s’effectue en quelques étapes :

Exécution des scripts d’export de la structure de la base de données Oracle ainsi que des données au format SQL.
Conversion vers un format SQL compatible PostgreSQL.
Exécution des scripts d’import dans la base de données PostgreSQL.

ora2pg est également capable de convertir du PL/SQL en PL/pgSQL. Il propose beaucoup plus de fonctionnalités. Il permet notamment de choisir les objets que nous souhaitons migrer, convertir certains types vers d’autres ou encore, d’estimer le coût en jour-homme et la complexité de tout ce qui n’a pas pu être migré de manière automatique. La documentation est bien écrite et complète.

La théorie ayant été énoncée, place au concret et à l’analyse de l’application à migrer.

Étude du patrimoine applicatif :

Lorsqu’un ORM (Object-Relational Mapping) tel que Spring Data JPA ou Hibernate est utilisé dans les composants, cela apporte une couche d’abstraction vis-à-vis de l’utilisation de la base de données. Bien évidemment, cette abstraction facilite grandement la migration d’un SGBD vers un autre.

Malheureusement, ce n’est pas le cas sur les projets que nous avons dû migrer. Les accès à la base de données sont réalisés via des requêtes SQL natives. Les composants sont développés en Java, il s’agit d’une IHM et de quelques batchs.

La particularité de l’IHM est qu’elle intègre des dépendances d’applications tierces utilisant d’autres bases de données Oracle ne faisant pas partie du projet de migration à court terme. La stratégie de notre client est de ne plus utiliser ces dépendances au profit d’appels HTTP à des API tierces. Nous avons pu retrouver toutes les fonctionnalités qu’offraient ces dépendances via les ressources exposées par les API tierces.

Le périmètre de la migration semble plutôt simple :

Migrer la base de données Oracle en PostgreSQL
Remplacer le driver Oracle par le driver PostgreSQL dans chaque composant
Remplacer les dépendances tierces par des appels HTTP à des API tierces
Adapter les requêtes SQL utilisant des fonctions spécifiques à Oracle

Début des travaux :

Une fois la base de données migrées en utilisant ora2pg décrit précédemment, nous sommes passés sur l’adaptation des requêtes SQL.

Il existe plus d’une centaine de fonctions dans Oracle et PostgreSQL. Celles qui respectent la norme sont équivalentes. Nous allons passer en revue les fonctions couramment utilisées et spécifiques à Oracle et leur équivalence PostgreSQL.

Fonctionnalité Oracle	Équivalent PostgreSQL
Table DUAL	On peut créer une vue se nommant DUAL dans PostgreSQL pour faciliter la migration.
`CREATE SEQUENCE`	fonctionne aussi. Utilisation avec `nextval('seq')`.
Préfixe des colonnes lors d’un UPDATE `UPDATE t SET t.col = ...`	même syntaxe supportée mais pas nécessaire.
Sous-requête `SELECT * FROM (SELECT * FROM ma_table) MON_ALIAS;` ok	`SELECT * FROM (SELECT * FROM ma_table) AS MON_ALIAS;`
`MINUS`	`EXCEPT`.
DECODE	`CASE WHEN ... THEN ... ELSE ... END`.
NVL	`COALESCE(val, remplacement)`.
Opérateur (+)	`LEFT JOIN`, `RIGHT JOIN`, `FULL OUTER JOIN`.
Chaînes vides `''` est traité comme `NULL`	`''` ≠ `NULL`

Le traitement des dates est un vaste sujet. Voici quelques exemples :

Fonctionnalité Oracle	Équivalent PostgreSQL
`DATE` = date et heure	`DATE` = date uniquement
`CURRENT` = fuseau horaire de la session	`CURRENT` = fuseau horaire de l’OS
`date1 - date2` (en jours)	Retourne un `interval`.

Il s’agit là que d’un échantillon mais comme vous pouvez le voir, cela peut être long et fastidieux de tout adapter. Sachez qu’il existe une extension nommée orafce permettant d’émuler une partie des fonctions et packages d’Oracle au sein de PostgreSQL. Cette extension prend la forme d’un fichier SQL à exécuter avec les bons droits sur le serveur PostgreSQL.

Cette extension est supportée par AWS Aurora et Azure Database depuis respectivement 2018 et 2019. Voici quelques extraits du fichier d’installation :

-- emulation of dual table
CREATE VIEW oracle.dual AS SELECT 'X'::varchar AS dummy;
REVOKE ALL ON oracle.dual FROM PUBLIC;
GRANT SELECT, REFERENCES ON oracle.dual TO PUBLIC;

CREATE OR REPLACE FUNCTION oracle.nvl(int, int)
RETURNS int AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql IMMUTABLE;

L’installation de cette extension peut faciliter l’adaptation des requêtes SQL si votre projet est contraint par le temps ou le coût.
Néanmoins, votre projet continuera d’utiliser des spécificités d’Oracle ne faisant pas partie de la norme SQL.
Charge à vous de poursuivre ou non la migration au fil de l’eau par la suite.

Une fois la base de données migrée, les drivers changés, et requêtes adaptées, nous débutons la campagne de tests de non-régression sur l’application.

Premiers tests post-migration :

Très rapidement une erreur est remontée par l’un des composants :

org.postgresql.util.PSQLException: ERROR: relation "articles” does not exist

La table “ARTICLES” n’existe pas … Nous nous rendons compte que notre application utilise une autre base de données. Pour comprendre pourquoi nous ne l’avons pas vu, il est nécessaire de vous expliquer comment est structurée une base de données.

Dépendances Oracle tierces :

Un serveur de base de données contient une ou plusieurs instances. Chaque instance contient un ou plusieurs schémas. Chaque schéma contient une ou plusieurs tables.
Dans Oracle, il est possible d’autoriser un schéma à accéder à une table d’un autre schéma en lecture ou modification. Cela s’effectue via des GRANT. Il est également possible d’autoriser un schéma à accéder à un autre schéma d’une autre instance (voir d’une autre base de données) via le mécanisme de DBLINK.
A l’utilisation, cela revient à exécuter les requêtes SQL suivantes :

SELECT FROM SCHEMA_DIST.ARTICLES      -- grant
SELECT FROM ARTICLES@SCHEMA_DIST      -- dblink

L’écriture de ces requêtes n’est pas commune, nous aurions dû nous en rendre compte. Mais c’est sans compter l’utilisation de SYNONYM !
Les SYNONYM permettent de créer une sorte d’alias pour faciliter l’écriture de requête. Concernant les GRANT et DBLINK, il est possible de déclarer un SYNONYM de cette manière :

CREATE SYNONYM ARTICLES  FOR SCHEMA_DIST.ARTICLES
CREATE SYNONYM ARTICLES2 FOR ARTICLES@SCHEMA_DIST

Ce qui donne à l’utilisation :

SELECT FROM ARTICLES
SELECT FROM ARTICLES2

L’utilisation des GRANT et DBLINK est donc tout simplement cachée. Si l’application est mal ou peu documentée, on perd la visibilité sur ce couplage fort entre deux schémas distincts.
Il est nécessaire de bien identifier les dépendances lors de l’étude initiale. Les trois requêtes suivantes permettent respectivement de retrouver les GRANT, DBLINK et SYNONYM déclarés dans votre schéma.

SELECT GRANTOR, TABLE_NAME, TABLE_SCHEMA
FROM ALL_TAB_PRIVS WHERE GRANTEE IN ('MON_USER', 'PUBLIC');
SELECT DB_LINK, HOST
FROM ALL_DB_LINKS;
SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK
FROM ALL_SYNONYMS WHERE OWNER IN ('MON_USER', 'PUBLIC');

Poursuite des tests :

Une fois ces dépendances remplacées par des appels HTTP à des API tierces, nous avons repris les tests de non-régression sur l’ensemble de l’application.
Nous avons été confrontés à une dernière anomalie : il s’agissait d’un bug présent dans le driver PostgreSQL. N’hésitez pas à aller consulter le bug tracker de PostgreSQL si vous faites face à des comportements étranges ou insolubles à l’exécution de vos tests : https://github.com/pgjdbc/pgjdbc/issues

Conclusion :

Pour conclure, nous aimerions rappeler qu’il faut bien s’outiller pour effectuer une migration Oracle vers PostgreSQL. ora2pg semble indispensable pour la phase initiale concernant la structure et les données. N’hésitez pas à utiliser les types de données natifs dans PostgreSQL. L’installation d’extension telle que orafce dépendra des contraintes de votre projet.
L’étude initiale est importante, prenez le temps d’identifier toutes les dépendances externes à votre application. Notamment en exécutant les trois requêtes fournies précédemment qui vous permettront d’éviter les surprises.
La phase de test de non-régression est nécessaire pour déceler les derniers problèmes que pourrait engendrer une migration de ce type. Consultez le bug tracker du driver si cela est nécessaire.
Enfin, sachez qu’il n’existe pas d’outil miracle permettant de faire une migration de manière complètement automatisée. Des ajustements manuels seront nécessaires. Nous espèrons avoir levé le voile sur ce qu’était une migration et vous éviter de tomber dans les mêmes pièges que nous avons rencontrés.

Références