Vous trouverez en ligne les différentes versions complètes de ce document. Les solutions de TP ne figurent pas forcément dans la version imprimée, mais sont dans les versions numériques (PDF ou HTML).

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

Ce module est une introduction aux migrations de Oracle vers PostgreSQL. Nous y abordons comment gérer sa première migration (quel que soit le SGBD source et destination), puis nous réfléchirons sur le contenu de la migration et sur le choix de l’outil idoine.

À l’issue de ce module, l’outil Ora2Pg sera installé sur l’environnement Linux de formation pour permettre la génération d’un premier rapport d’évaluation.

Notre expertise est sur PostgreSQL et nous nous reposons sur notre expérience de plusieurs années de migrations Oracle vers PostgreSQL pour écrire cette formation.

Malgré tous nos efforts, certaines informations sur Oracle pourraient être erronées, ou ne plus être vraies avec les dernières versions. Nos clients n’utilisent pas forcément les mêmes fonctionnalités, avec le même niveau d’expertise, et nous n’avons pas forcément correctement restitué leur retour.

Si vous constatez des erreurs ou des manques, n’hésitez pas à nous en faire part pour que nous puissions améliorer ce support de formation.

La façon dont la première migration va se dérouler est essentielle. C’est sur cette expérience que les autres migrations seront abordées. Si l’expérience a été mauvaise, il est même probable que les migrations prévues après soient repoussées fortement, voire annulées.

Il est donc essentiel de réussir sa première migration. Réussir veut aussi dire bien la documenter, car elle servira de base pour les prochaines migrations : les méthodes employées seront réutilisées, et certainement améliorées. Réussir veut aussi dire la publiciser, au moins en interne, pour que tout le monde sache que ce type de migration est réalisable et qu’une expérience est disponible en interne.

De plus, cette migration va influencer fortement la vision des développeurs et des utilisateurs vis-à-vis de ce SGBD. Réussir la migration veut donc aussi dire réussir à faire apprécier et accepter ce moteur de bases de données. Sans cela, il y a de fortes chances que les prochaines migrations ne soient pas demandées volontairement, ce qui rendra les migrations plus difficiles.

Le premier projet de migration doit être sélectionné avec soin.

S’il est trop simple, il n’aura pas réellement de valeur. Par exemple, dans le cas d’une migration d’une base de 100 Mo, sans routines stockées, sans fonctionnalités avancées, cela ne constituera pas une base qui permettra d’aborder tranquillement une migration d’une base de plusieurs centaines de Go et utilisant des fonctionnalités avancées.

L’inverse est aussi vrai. Un projet trop gros risque d’être un souci. Prenez une base critique de plusieurs To, dotée d’un très grand nombre de routines stockées. C’est un véritable challenge, y compris pour une personne expérimentée. Il y a de fortes chances que la migration soit longue, dure, mal vécue… et possiblement annulée à cause de sa complexité. Ceci aura un retentissement fort sur les prochaines migrations.

Il est préférable de choisir un projet un peu entre les deux : une base conséquente (plusieurs dizaines de Go), avec quelques routines stockées, de la réplication, etc. Cela aura une vraie valeur, tout en étant accessible pour une première migration.

Une fois une telle migration réussie, il sera plus simple d’aborder correctement et sans crainte la migration de bases plus volumineuses ou plus complexes.

Il faut aussi ne pas oublier que la migration doit impliquer un groupe entier, pas seulement une personne. Les développeurs, les administrateurs, les équipes de support doivent tous être impliquées dans ce projet, pour qu’ils puissent intégrer les changements quant à l’utilisation de ce nouveau SGBD.

L’équipe du projet de migration doit être interne, même si une aide externe peut être sollicitée. Un chef de projet doit être nommé au sein d’une équipe hétérogène, composée de développeurs, d’administrateurs, de testeurs et d’utilisateurs. Il est à noter que les testeurs sont une partie essentielle de l’équipe.

Même si l’essentiel du projet est porté en interne, il est toujours possible de faire appel à une société externe spécialisée dans ce genre de migrations. Cela permet de gagner du temps sur certaines étapes de la migration pour éviter certains pièges, ou mettre en place l’outil de migration.

Cette migration doit être gérée comme tout autre projet :

• une réunion de lancement ;
• des réunions de suivi ;
• des rapports d’avancements.

De même, ce projet a besoin de ressources, et notamment des serveurs de tests : par exemple un serveur Oracle contenant la base à migrer (mais qui ne soit pas le serveur de production), et un serveur PostgreSQL contenant la base à migrer. Ces deux serveurs doivent avoir la volumétrie réelle de la base de production, sinon les tests de performance n’auront pas vraiment de valeur.

En fait, il faut vraiment que cette migration soit considérée comme un vrai projet, et pas comme un projet au rabais, ce qui arrive malheureusement assez fréquemment. C’est une opération essentielle, et des ressources compétentes et suffisantes doivent être offertes pour la mener à bien.

En soi, passer à PostgreSQL n’est pas une révolution. C’est un moteur de base de données comme les autres, avec un support du SQL (et quelques extensions) et ses fonctionnalités propres. Ce qui change est plutôt l’implémentation, mais, comme nous le verrons dans cette formation, si une fonctionnalité identique n’existe pas, une solution de contournement est généralement disponible.

La majorité des utilisateurs de PostgreSQL vient à PostgreSQL pour faire des économies (sur les coûts de licence). Si jamais une telle migration demandait énormément de changements, ils ne viendraient pas à PostgreSQL. Or la majorité des migrations se passe bien, et les utilisateurs restent ensuite sur PostgreSQL. Les migrations qui échouent sont généralement celles qui n’ont pas été correctement gérées dès le départ (pas de ressources pour le projet, un projet trop gros dès le départ, etc.).

Le passage à un nouveau SGBD est un peu un saut dans l’inconnu pour la majorité des personnes impliquées. Elles connaissent bien un moteur de bases de données et souvent ne comprennent pas pourquoi on veut les faire passer à un autre moteur. C’est pour cela qu’il est nécessaire de les impliquer dès le début du projet, et, le cas échéant, de les former. Il est possible d’avoir de nombreuses formations autour de PostgreSQL pour les différents acteurs : chefs de projet, administrateurs de bases de données, développeurs, etc.

De toute façon, les concepts utilisés par PostgreSQL sont très proches des concepts des moteurs SGBD propriétaires. La majorité du temps, il suffit d’adapter les compétences. Il n’est jamais nécessaire de reprendre tout à zéro. La connaissance d’un autre moteur de bases de données permet de passer très facilement à PostgreSQL, ce qui valorise l’équipe.

Contrairement à d’autres projets, le service existe déjà. Les délais sont donc généralement moins importants, ce qui permet de donner du temps aux personnes impliquées dans le projet pour fournir une migration de qualité (et surtout documenter cette opération).

Une migration aura un coût important. Ce n’est pas parce que PostgreSQL est un logiciel libre que tout est gratuit. La mise à disposition de ressources humaines et matérielles aura un coût. La formation du personnel aura un coût. Mais ce coût sera amoindri par le fait que, une fois cette migration réalisée, les prochaines migrations n’auront un coût qu’au niveau matériel principalement.

La qualité de la première migration est cruciale. Si le but est de migrer les autres bases de données de l’entreprise, il est essentiel que la première migration soit une réussite totale. Il est essentiel qu’elle soit documentée, discutée, pour que le travail effectué soit réutilisable (soit complètement, soit uniquement l’expérience et les méthodes) afin que la prochaine migration soit moins coûteuse.

Pour résumer, la première migration doit être suffisamment simple pour ne pas être un échec et suffisamment complexe pour être en confiance pour les prochaines migrations. Il est donc essentiel de bien choisir la base de sa première migration.

Il est aussi essentiel d’avoir des ressources humaines et matérielles suffisantes, tout en contrôlant les coûts.

Enfin, il est important de ne pas stresser les acteurs de cette migration avec des délais difficiles à tenir. Le service est déjà présent et fonctionnel, la première migration doit être un succès si l’on veut continuer, autant donner du temps aux équipes responsables de la migration.

Avant de pouvoir traiter le code, qu’il soit applicatif ou issu des routines stockées, il faut procéder à la migration du schéma et des données. C’est donc ce dont nous allons parler dans cette partie.

La première question à se poser concerne le schéma : veut-on le migrer tel quel ? Le changer peut permettre d’utiliser des fonctionnalités plus avancées de PostgreSQL. Cela peut être intéressant pour des raisons de performances, mais a comme inconvénient de ne plus être une migration isofonctionnelle.

Généralement, il faudra créer un nouveau schéma, et intégrer les objets par étapes : tables, index, puis contraintes.

Oracle utilise généralement number pour les types entiers. L’équivalent strict au niveau PostgreSQL est numeric mais il est préférable de passer à d’autres types de données comme int (un entier sur quatre octets) ou bigint (un entier sur huit octets) qui sont bien plus performants.

L’outil pour la migration devra être sélectionné suivant ses possibilités au niveau de la transformation de certains types en d’autres types, si jamais il est décidé de procéder ainsi.

L’outil de migration doit pouvoir aussi gérer des types particuliers, comme les types spécifiques à la recherche plein texte, ceux spécifiques aux objets binaires, ceux spécifiques à la couche spatiale, etc. Il est possible qu’un développement soit nécessaire pour faciliter la migration. Un outil libre est préférable dans ce cas.

Pour des raisons de performances, il est toujours préférable de ne déclarer les index et les contraintes qu’une fois les tables remplies. L’outil de migration doit aussi prendre cela en compte : création des tables, remplissage des tables et enfin création des index et contraintes.

Toujours dans les décisions à prendre avant la migration, il est important de savoir si l’on veut tout migrer d’un coup ou le faire en plusieurs étapes. Les deux possibilités ont leurs avantages et inconvénients.

De même, souhaite-t-on paralléliser l’import et l’export ? De ce choix dépend principalement l’outil que l’on va sélectionner pour la migration.

Enfin, souhaite-t-on modifier des données lors de l’opération de migration ? Là aussi, cela peut se concevoir, notamment si certains types de données sont modifiés. Mais c’est une décision à prendre lors des premières étapes du projet.

Certaines fonctionnalités Oracle n’ont pas d’équivalents natifs dans PostgreSQL. Il faut avoir conscience que l’outil de migration ne pourra pas convertir intégralement les objets avancés disponibles sur Oracle.

Vues matérialisées

Concernant les vues matérialisées, elles existent sous PostgreSQL depuis la version 9.3. Cependant, elles ne disposent pas de toutes les fonctionnalités accessibles sous Oracle. Il est possible de les implémenter de toutes pièces en utilisant des fonctions et triggers. En attendant leur implémentation complète au cœur du code de PostgreSQL, voici des documents expliquant de manière détaillée comment implémenter des vues matérialisées :

• PostgreSQL/Materialized Views
• Materialized Views that Really Work

Partitionnement

Les partitions telles que gérées sous Oracle existent sous PostgreSQL depuis la version 10. Avec une version inférieure, il faut utiliser l’héritage et définir des triggers et contraintes CHECK. Pour plus de détails, consultez le document 5.9. Partitionnement de tables ainsi que le document Partitionnement (module DBA2).

Les types de partitions supportées sont LIST et RANGE. Les partitions de type HASH sont supportées par PostgreSQL 11. Le partitionnement par référence n’est pas supporté.

Synonymes

Les synonymes d’Oracle n’ont pas d’équivalent sous PostgreSQL. Il doit être possible d’utiliser des vues pour tenter d’émuler cette fonctionnalité dans la mesure où il s’agit d’accéder à des objets d’autres schémas.

Absence de hint

L’optimiseur Oracle supporte des hints, qui permettent au DBA de tromper l’optimiseur pour lui faire prendre des chemins que l’optimiseur a jugés trop coûteux. Ces hints sont exprimés sous la forme de commentaires et ne seront donc pas pris en compte par PostgreSQL, qui ne gère pas ces hints.

Néanmoins, une requête comportant un hint pour contrôler l’optimiseur Oracle doit faire l’objet d’une attention particulière, et l’analyse de son plan d’exécution devra être faite minutieusement, pour s’assurer que, sous PostgreSQL, la requête n’a pas de problème particulier, et agir en conséquence le cas échéant. C’est notamment vrai lorsque l’une des tables mises en œuvre est particulièrement volumineuse. Mais, de manière générale, l’ensemble des requêtes portées devront voir leur plan d’exécution vérifié.

Le plan d’exécution de la requête sera vérifié avec l’ordre EXPLAIN ANALYZE qui fournit non seulement le plan d’exécution en précisant les estimations de sélectivité réalisées par l’optimiseur, mais va également exécuter la requête et fournir la sélectivité réelle de chaque nœud du plan d’exécution. Une forte divergence entre la sélectivité estimée et réelle permet de détecter un problème. Souvent, il s’agit d’un problème de précision des statistiques. Il est possible d’agir sur cette précision de plusieurs manières.

Tout d’abord, il est possible d’augmenter le nombre d’échantillons collectés, pour construire notamment les histogrammes. Le paramètre default_statistics_target contrôle la précision de cet échantillon. Pour une base de forte volumétrie, ce paramètre sera augmenté systématiquement dans une proportion raisonnable. Pour une base de volumétrie normale, ce paramètre sera plutôt augmenté en ciblant une colonne particulière avec l’ordre SQL ALTER TABLE … ALTER COLUMN … SET STATISTICS …;. De plus, il est possible de forcer artificiellement le nombre de valeurs distinctes d’une colonne avec l’ordre SQL ALTER TABLE … SET COLUMN … SET n_distinct = …;. Il est aussi souvent utile d’envisager une réécriture de la requête : si l’optimiseur, sous Oracle comme sous PostgreSQL, n’arrive pas à trouver un bon plan, c’est probablement qu’elle est écrite d’une façon qui empêche ce dernier de travailler correctement.

Accès par ROWID

Dans de très rares cas, des requêtes SQL utilisent la colonne ROWID d’Oracle, par exemple pour dédoublonner des enregistrements. Le ROWID est la localisation physique d’une ligne dans une table. L’équivalent dans PostgreSQL est le ctid.

Plus précisément, le ROWID Oracle représente une adresse logique d’une ligne, encodée sous la forme OOOOOO.FFF.BBBBBB.RRR où O représente le numéro d’objet, F le fichier, B le numéro de bloc et R la ligne dans le bloc. Le format est différent dans le cas d’une table stockée dans un BIG FILE TABLESPACE, mais le principe reste identique.

Quant au ctid de PostgreSQL, il ne représente qu’un couple (numéro du bloc, numéro de l’enregistrement), aucune autre information de localisation physique n’est disponible. Le ctid n’est donc unique qu’au sein d’une table. De part ce fait, une requête ramenant le ctid des lignes d’une table partitionnée peut présenter des ctid en doublons. On peut dans ce cas utiliser le champ caché tableoid (l’identifiant unique de la table dans le catalogue) de chaque table pour différencier les doublons par partition.

Cette méthode d’accès est donc à proscrire, sauf opération particulière et cadrée.

Après avoir répondu aux questions précédentes et évalué la complexité de la migration, il sera possible de sélectionner le bon outil de migration. Il en existe différents, qui répondront différemment aux besoins.

Ora2Pg est un outil libre développé par Gilles Darold. Le rythme de développement est rapide. De nouvelles fonctionnalités sont proposées rapidement, suivant les demandes des utilisateurs, les nouveautés dans PostgreSQL et les découvertes réalisées par son auteur.

Les ETL sont intéressants pour les possibilités plus importantes. Ora2Pg ne fait que de la conversion Oracle vers PostgreSQL et MySQL, alors que les ETL autorisent un plus grand nombre de sources de données et de destinations, si bien que l’expérience acquise pour la migration d’Oracle vers PostgreSQL peut être réutilisée pour réaliser la migration d’un autre moteur vers un autre moteur ou pour l’import ou l’export de données.

Il est aussi possible de développer sa propre solution si les besoins sont vraiment spécifiques au métier, voire de mixer différentes solutions. Par exemple, il était intéressant d’utiliser Ora2Pg pour la transformation du schéma et un ETL pour un export et import des données, avec des règles avancées de reprise d’étapes en erreur ou de conditions à remplir pour déclencher d’autres actions (comme la création des index ou l’activation des contraintes).

Ora2Pg est un outil écrit en Perl. Il se connecte à Oracle via le connecteur Perl pour Oracle. Après analyse des catalogues système Oracle et lecture de son fichier de configuration, il est capable de générer un fichier SQL compatible avec PostgreSQL ou de se connecter à une base PostgreSQL pour y exécuter ce script. Dans les dernières versions, il est même capable de convertir automatiquement une partie du code PL/SQL d’Oracle vers du PL/pgSQL sur PostgreSQL.

L’outil est plutôt simple de mise en œuvre et de prise en main. Il est rapide au chargement, notamment grâce à sa gestion de la commande COPY.

Pour aborder immédiatement les inconvénients de Ora2Pg, il ne propose pas de solution incrémentale : c’est tout ou partie d’une table ou rien.

Ora2Pg dispose néanmoins de nombreuses fonctionnalités. Il est capable d’exporter tout le schéma de données Oracle. Il est capable de convertir utilisateurs et droits sur les objets. Il réalise aussi automatiquement la conversion des types de données. Enfin, il s’occupe de la déclaration et du code des routines stockées (uniquement PL/SQL vers PL/pgSQL). Il propose aussi une aide au partitionnement, dont l’implémentation est vraiment différente entre Oracle et PostgreSQL.

Les ETL sont spécialisées dans la transformation et le chargement des données. Ils permettent la parallélisation pour leur traitement, ils sont très souples au niveau de la transformation de données. Tout cela leur permet d’être très rapide, quelques fois plus qu’Ora2Pg.

De plus, ils permettent de faire de la migration incrémentale.

La migration du schéma est au mieux sommaire, voire inexistante. Ce n’est clairement pas la fonctionnalité visée par les ETL.

Le paramétrage d’un ETL est souvent très long. Si vous devez migrer les données de 200 tables, vous aurez 200 jobs à créer. Dans ce cas, Ora2Pg est bien plus intéressant, vu que la migration de la totalité des tables est l’option par défaut.

Ce sont des outils riches et donc complexes. Cela demandera un apprentissage bien plus long que pour Ora2Pg. Cependant, ils sont utilisables dans bien plus de cas qu’Ora2Pg.

Nous allons aborder dans cette partie les différentes étapes à réaliser pour installer Ora2Pg à partir des sources :

• Où trouver les fichiers sources ?
• Quelle version choisir ?
• Comment préparer le serveur pour accueillir PostgreSQL ?
• Quelle procédure de compilation suivre ?
• Comment installer les fichiers compilés ?

Les fichiers sources et les instructions de compilation sont accessibles depuis le site officiel du projet.

Il est très important de toujours télécharger la dernière version, car l’ajout de fonctionnalités et les corrections de bogues sont permanentes. En effet, ce projet bénéficie d’améliorations et de corrections au fur et à mesure des retours d’expérience des utilisateurs. Il est constamment mis à jour.

La dernière distribution officielle peut être téléchargée directement depuis GitHub.com, où la dernière version est disponible aux formats zip ou tar.gz.