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.

Pour obtenir le dernier code en développement, il faut aller sur la page du dépôt GitHub (https://github.com/darold/ora2pg) et cliquer sur le bouton Download ZIP de la branche de développement. Le fichier téléchargé sera nommé ora2pg-master.zip.

Ora2Pg est entièrement codé en Perl.

Ora2Pg se connecte à Oracle grâce à l’interface de bases de données pour Perl, appelée DBI.

Dans cette interface, Ora2Pg va utiliser le connecteur Oracle, appelé DBD::Oracle (DBD, acronyme de DataBase Driver). Tous les modules Perl, s’ils ne sont pas disponibles en paquet pour votre distribution, peuvent toujours être téléchargés à partir du site CPAN. Il suffit de saisir le nom du module (ex : DBD::Oracle) dans la case de recherche et la page de téléchargement du module vous sera proposée.

Le client lourd d’Oracle est nécessaire pour utiliser la couche OCI. Cependant, les nouvelles versions Instant Client (à partir de la version 10g) suffisent amplement à Ora2Pg. Attention toutefois, s’il est possible d’utiliser un client Oracle 12c pour se connecter à des bases Oracle de versions inférieures, l’inverse n’est pas vrai.

Ainsi il convient d’installer au minimum un client Oracle, comme instantclient-basic, instantclient-sdk ou instantclient-sqlplus. Pour que sqlplus puisse fonctionner correctement il faut au préalable installer la librairie libaio. Cette bibliothèque permet aux applications en espace utilisateur d’utiliser les appels système asynchrones d’E/S du noyau Linux.

DBD::Oracle va s’appuyer sur les variables d’environnement pour déterminer où se trouvent les bibliothèques d’Oracle.

Dans le monde Oracle, ces variables d’environnement sont très connues (ORACLE_BASE, ORACLE_HOME, NLS_LANG, etc.). Pour DBD::Oracle, le positionnement de la variable ORACLE_HOME suffit.

export ORACLE_HOME=/usr/lib/oracle/x.x/client64

Pour une installation sous Windows, l’utilisation de Strawberry Perl nécessitera les outils de compilation pour l’installation de DBD::Oracle alors que l’utilisation de la distribution libre d’ActiveState permet d’installer directement une version binaire de la bibliothèque.

Pour les anciennes versions de Perl ou certaines distributions il peut être nécessaire d’installer le module Perl, Time::HiRes.

Le connecteur PostgreSQL pour DBI, DBD::Pg est nécessaire uniquement si l’on veut migrer directement les données depuis Oracle vers PostgreSQL sans avoir à passer par des fichiers intermédiaires. DBD::Pg nécessite au minimum les bibliothèques du client PostgreSQL.

On peut se passer de ce module dans la mesure où, par défaut, Ora2Pg va écrire les objets et données à migrer dans des fichiers. Ces fichiers peuvent alors être chargés à l’aide de la commande psql ou être transférés sur une autre machine disposant de cet outil.

La bibliothèque Perl Compress::Zlib est nécessaire si vous souhaitez que les fichiers de sortie soient compressés avec gzip. C’est notamment très utile pour les fichiers de données volumineux par exemple.

Fort heureusement, on peut aussi utiliser le binaire bzip2 pour compresser le fichier de sortie. Dans ce cas, il suffit d’indiquer, dans le fichier de configuration d’Ora2Pg, l’emplacement du binaire bzip2 sur le système si celui-ci n’est pas dans le PATH.

Ora2Pg permet de migrer les bases de données MySQL depuis la version 16.0 et les bases de données SQL Server depuis la version 24.0. Tout comme pour Oracle, Ora2Pg a besoin de se connecter à l’instance distante au travers d’un pilote Perl. C’est le rôle des modules Perl DBD::MySQL ou DBD::ODBC.

Voici les lignes de commande à saisir pour compiler et installer Ora2Pg :

tar xjf ora2pg-21.0.tar.bz2
cd ora2pg-21.0/
perl Makefile.PL
make && sudo make install

Sous Windows, il faut remplacer la dernière ligne par la ligne suivante :

dmake && dmake install

dmake est l’équivalent de make pour Windows, il peut être téléchargé depuis cette URL : http://search.cpan.org/dist/dmake/. Téléchargez-le et installez dmake quelque part dans le PATH Windows.

Le fichier de configuration d’Ora2Pg par défaut est /etc/ora2pg/ora2pg.conf sous Unix et C:\ora2pg\ora2pg.conf sous Windows.

L’installation provoquera la création d’un modèle de fichier de configuration : ora2pg.conf.dist, avec toutes les variables définies par défaut. Il suffira de le renommer en ora2pg.conf et de le modifier pour obtenir le comportement souhaité.

cp /etc/ora2pg/ora2pg.conf.dist /etc/ora2pg/ora2pg.conf

Les paramètres de ce fichier sont explicités de manière exhaustive plus loin dans la formation.

Certaines options ne nécessitent pas de valeurs et ne sont introduites dans la ligne de commande que pour activer certains comportements. C’est le cas notamment de l’option -d ou --debug permettant d’activer le mode trace.

Toutes les options courtes ont une version longue, par exemple -q et --quiet.

Voici l’intégralité des options disponibles en ligne de commande pour le script Perl ora2pg et leur explication :

    -a | --allow str  : Comma separated list of objects to allow from export.
                        Can be used with SHOW_COLUMN too.
    -b | --basedir dir: Set the default output directory, where files
                        resulting from exports will be stored.
    -c | --conf file  : Set an alternate configuration file other than the
                        default /etc/ora2pg/ora2pg.conf.
    -C | --cdc_file file: File used to store/read SCN per table during export.
                        default: TABLES_SCN.log in the current directory. This
                        is the file written by the --cdc_ready option.
    -d | --debug      : Enable verbose output.
    -D | --data_type str : Allow custom type replacement at command line.
    -e | --exclude str: Comma separated list of objects to exclude from export.
                        Can be used with SHOW_COLUMN too.
    -h | --help       : Print this short help.
    -g | --grant_object type : Extract privilege from the given object type.
                        See possible values with GRANT_OBJECT configuration.
    -i | --input file : File containing Oracle PL/SQL code to convert with
                        no Oracle database connection initiated.
    -j | --jobs num   : Number of parallel process to send data to PostgreSQL.
    -J | --copies num : Number of parallel connections to extract data from Oracle.
    -l | --log file   : Set a log file. Default is stdout.
    -L | --limit num  : Number of tuples extracted from Oracle and stored in
                        memory before writing, default: 10000.
    -m | --mysql      : Export a MySQL database instead of an Oracle schema.
    -M | --mssql      : Export a Microsoft SQL Server database.
    -n | --namespace schema : Set the Oracle schema to extract from.
    -N | --pg_schema schema : Set PostgreSQL's search_path.
    -o | --out file   : Set the path to the output file where SQL will
                        be written. Default: output.sql in running directory.
    -p | --plsql      : Enable PLSQL to PLPGSQL code conversion.
    -P | --parallel num: Number of parallel tables to extract at the same time.
    -q | --quiet      : Disable progress bar.
    -r | --relative   : use \ir instead of \i in the psql scripts generated.
    -s | --source DSN : Allow to set the Oracle DBI datasource.
    -S | --scn    SCN : Allow to set the Oracle System Change Number (SCN) to
                        use to export data. It will be used in the WHERE clause
                        to get the data. It is used with action COPY or INSERT.
    -t | --type export: Set the export type. It will override the one
                        given in the configuration file (TYPE).
    -T | --temp_dir dir: Set a distinct temporary directory when two
                        or more ora2pg are run in parallel.
    -u | --user name  : Set the Oracle database connection user.
                        ORA2PG_USER environment variable can be used instead.
    -v | --version    : Show Ora2Pg Version and exit.
    -w | --password pwd : Set the password of the Oracle database user.
                        ORA2PG_PASSWD environment variable can be used instead.
    -W | --where clause : Set the WHERE clause to apply to the Oracle query to
                        retrieve data. Can be used multiple time.
    --forceowner      : Force ora2pg to set tables and sequences owner like in
                  Oracle database. If the value is set to a username this one
                  will be used as the objects owner. By default it's the user
                  used to connect to the Pg database that will be the owner.
    --nls_lang code: Set the Oracle NLS_LANG client encoding.
    --client_encoding code: Set the PostgreSQL client encoding.
    --view_as_table str: Comma separated list of views to export as table.
    --estimate_cost   : Activate the migration cost evaluation with SHOW_REPORT
    --cost_unit_value minutes: Number of minutes for a cost evaluation unit.
                  default: 5 minutes, corresponds to a migration conducted by a
                  PostgreSQL expert. Set it to 10 if this is your first migration.
   --dump_as_html     : Force ora2pg to dump report in HTML, used only with
                        SHOW_REPORT. Default is to dump report as simple text.
   --dump_as_csv      : As above but force ora2pg to dump report in CSV.
   --dump_as_json     : As above but force ora2pg to dump report in JSON.
   --dump_as_sheet    : Report migration assessment with one CSV line per database.
   --init_project name: Initialise a typical ora2pg project tree. Top directory
                        will be created under project base dir.
   --project_base dir : Define the base dir for ora2pg project trees. Default
                        is current directory.
   --print_header     : Used with --dump_as_sheet to print the CSV header
                        especially for the first run of ora2pg.
   --human_days_limit num : Set the number of human-days limit where the migration
                        assessment level switch from B to C. Default is set to
                        5 human-days.
   --audit_user list  : Comma separated list of usernames to filter queries in
                        the DBA_AUDIT_TRAIL table. Used only with SHOW_REPORT
                        and QUERY export type.
   --pg_dsn DSN       : Set the datasource to PostgreSQL for direct import.
   --pg_user name     : Set the PostgreSQL user to use.
   --pg_pwd password  : Set the PostgreSQL password to use.
   --count_rows       : Force ora2pg to perform a real row count in TEST,
                        TEST_COUNT and SHOW_TABLE actions.
   --no_header        : Do not append Ora2Pg header to output file
   --oracle_speed     : Use to know at which speed Oracle is able to send
                        data. No data will be processed or written.
   --ora2pg_speed     : Use to know at which speed Ora2Pg is able to send
                        transformed data. Nothing will be written.
   --blob_to_lo       : export BLOB as large objects, can only be used with
                        action SHOW_COLUMN, TABLE and INSERT.
   --cdc_ready        : use current SCN per table to export data and register
                        them into a file named TABLES_SCN.log per default. It
                        can be changed using -C | --cdc_file.
   --lo_import        : use psql \lo_import command to import BLOB as large
                        object. Can be use to import data with COPY and import
                        large object manually in a second pass. It is recquired
                        for BLOB > 1GB. See documentation for more explanation.
   --mview_as_table str: Comma separated list of materialized views to export
                        as regular table.
   --drop_if_exists   : Drop the object before creation if it exists.
   --delete clause    : Set the DELETE clause to apply to the Oracle query to
                        be applied before importing data. Can be used multiple
            time.

Rapport sur le contenu de la base Oracle

Ora2Pg dispose d’un mode d’analyse du contenu de la base Oracle afin de générer un rapport sur son contenu et présenter ce qui peut ou ne peut pas être exporté.

L’outil parcourt l’intégralité des objets, les dénombre, extrait les particularités de chacun d’eux et dresse un bilan exhaustif de ce qu’il a rencontré. Pour activer le mode « analyse et rapport », il faut utiliser l’export de type SHOW_REPORT par la commande suivante :

ora2pg -t SHOW_REPORT

Par défaut, le rapport se présente au format texte sur la sortie standard. Pour proposer un format plus lisible, il est recommandé d’activer l’option --dump_as_html qui crée un rapport au format HTML. D’autres formats existent, comme le CSV (--dump_as_csv) ou le JSON (--dump_as_json), si la centralisation et la consolidation de rapports sont nécessaires.

Voici un exemple de rapport obtenu avec cette commande :

-------------------------------------------------------------------------------
Ora2Pg v20.0 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
Schema  HR
Size    28.56 MB

-------------------------------------------------------------------------------
Object  Number  Invalid Comments    Details
-------------------------------------------------------------------------------
DATABASE LINK   2   0   Database links will be exported as SQL/MED PostgreSQL's
                Foreign Data Wrapper (FDW) extensions using oracle_fdw. 
GLOBAL TEMPORARY TABLE  0   0   Global temporary table are not supported by
                PostgreSQL and will not be exported. You will have to
                rewrite some application code to match the PostgreSQL
                temporary table behavior.   
INDEX   28  0   19 index(es) are concerned by the export, others are automatically
                generated and will do so on PostgreSQL. Bitmap will be
                exported as btree_gin index(es) and hash index(es) will be
                exported as b-tree index(es) if any. Domain index are exported
                as b-tree but commented to be edited to mainly use FTS.
                Cluster, bitmap join and IOT indexes will not be exported at all.
                Reverse indexes are not exported too, you may use a trigram-based
                index (see pg_trgm) or a reverse() function based index and search.
                Use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops'
                operators in your indexes to improve search with the LIKE operator
                respectively into varchar, text or char columns.
                3 function based b-tree index(es).
                13 b-tree index(es).
                3 spatial index index(es).
INDEX PARTITION 2   0   Only local indexes partition are exported, they are
                build on the column used for the partitioning.  
JOB 0   0   Job are not exported. You may set external cron job with them.  
PROCEDURE   3   1   Total size of procedure code: 1870 bytes.   
SEQUENCE    3   0   Sequences are fully supported, but all call to
                sequence_name.NEXTVAL or sequence_name.CURRVAL will be
                transformed into NEXTVAL('sequence_name') or
                CURRVAL('sequence_name').   
SYNONYM 0   0   SYNONYMs will be exported as views. SYNONYMs do not exists with
                PostgreSQL but a common workaround is to use views or set the
                PostgreSQL search_path in
                your session to access object outside the current schema.   
TABLE   22  0    2 check constraint(s). 1 unknown types. Total number of rows: 421.
                Top 10 of tables sorted by number of rows:.
                sg_infrastructure_route has 188 rows.
                employees has 107 rows. departments has 27 rows. countries has
                25 rows.
                locations has 23 rows. jobs has 19 rows. job_history has 10 rows.
                error_log_sample has 6 rows. emptyclob has 4 rows. regions has
                4 rows.
                Top 10 of largest tables:...
TABLE PARTITION 5   0   Partitions are exported using table inheritance and
                check constraint.
                Hash and Key partitions are not supported by PostgreSQL and will not
                be exported.
                5 RANGE partitions..
TRIGGER 3   1   Total size of trigger code: 736 bytes.  
VIEW    1   0   Views are fully supported but can use specific functions.   
-------------------------------------------------------------------------------
Total   69  2
-------------------------------------------------------------------------------

D’autres paramètres ne peuvent pas être analysés par Ora2Pg comme l’usage de l’application. Il existe aussi d’autres objets qui ne sont pas exportés directement par Ora2Pg comme les objets DIMENSION des fonctionnalités OLAP d’Oracle dans la mesure où ils n’ont pas d’équivalent dans PostgreSQL.

Évaluer la charge de migration d’une base Oracle

Pour déterminer le coût en jours/personne de la migration, Ora2Pg dispose d’une directive de configuration nommée ESTIMATE_COST. Celle-ci peut aussi être activée en ligne de commande : --estimate_cost. Cette fonctionnalité n’est disponible qu’avec le type d’export SHOW_REPORT.

ora2pg -t SHOW_REPORT --estimate_cost

Le rapport généré est identique à celui généré par SHOW_REPORT, mais cette fonctionnalité provoque en plus l’exploration des objets de la base de données, du code source des vues, triggers et routines stockées (fonctions, procédures et paquets de fonctions), puis donne un score à chaque objet et à chaque routine suivant le volume de code et la complexité de réécriture manuelle de ce code. En effet, la réécriture d’une routine comportant un CONNECT BY ne prend pas le même temps que la réécriture d’une routine comportant des appels à GOTO.

-------------------------------------------------------------------------------
Ora2Pg v20.0 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
Schema  HR
Size    28.56 MB

-------------------------------------------------------------------------------
Object  Number  Invalid Estimated cost  Comments    Details
-------------------------------------------------------------------------------
DATABASE LINK   2   0   6   Database links will be exported as SQL/MED PostgreSQL's
                    Foreign Data Wrapper (FDW) extensions using oracle_fdw. 
GLOBAL TEMPORARY TABLE  0   0   0   Global temporary table are not supported by
                    PostgreSQL and will not be exported. You will have to
                    rewrite some application code to match the PostgreSQL
                    temporary table behavior.   
INDEX   28  0   5.3 19 index(es) are concerned by the export, others are
                    automatically
                    generated and will do so on PostgreSQL. Bitmap will be
                    exported as btree_gin index(es) and hash index(es) will be
                    exported as b-tree index(es) if any. Domain index are exported
                    as b-tree but commented to be edited to mainly use FTS.
                    Cluster, bitmap join and IOT indexes will not be exported at all.
                    Reverse indexes are not exported too, you may use a
                    trigram-based index (see pg_trgm) or a reverse() function
                    based index and search.  Use 'varchar_pattern_ops',
                    'text_pattern_ops' or 'bpchar_pattern_ops' operators in
                    your indexes to improve search with the LIKE operator
                    respectively into varchar, text or char columns.
                    3 function based b-tree index(es). 13 b-tree index(es).
                    3 spatial index index(es).
INDEX PARTITION 2   0   0   Only local indexes partition are exported, they are
                    build on the column used for the partitioning.  
JOB 0   0   0   Job are not exported. You may set external cron job with them.  
PROCEDURE   3   1   16  Total size of procedure code: 1870 bytes.
                    add_job_history: 3. test_dupl_vazba: 7. secure_dml: 3.
SEQUENCE    3   0   1   Sequences are fully supported, but all call to
                    sequence_name.NEXTVAL or sequence_name.CURRVAL will be
                    transformed into NEXTVAL('sequence_name')
                    or CURRVAL('sequence_name').    
SYNONYM 0   0   0   SYNONYMs will be exported as views. SYNONYMs do not exists
                    with PostgreSQL but a common workaround is to use views
                    or set the PostgreSQL search_path in
                    your session to access object outside the current schema.   
TABLE   22  0   2.4  2 check constraint(s). 1 unknown types. Total number of
                    rows: 421.
                    Top 10 of tables sorted by number of rows:.
                    sg_infrastructure_route
                    has 188 rows. employees has 107 rows. departments has 27 rows.
                    countries has 25 rows. locations has 23 rows. jobs has 19 rows.
                    job_history has 10 rows. error_log_sample has 6 rows.
                    regions has 4 rows.
                    emptyclob has 4 rows. Top 10 of largest tables:.
TABLE PARTITION 5   0   1   Partitions are exported using table inheritance
                    and check constraint.
                    Hash and Key partitions are not supported by PostgreSQL
                    and will not be exported.
                    5 RANGE partitions..
TRIGGER 3   1   9.3 Total size of trigger code: 736 bytes.  cisvpolpre_bi:
                    3.3. update_job_history: 3.
VIEW    1   0   1   Views are fully supported but can use specific functions.   
-------------------------------------------------------------------------------
Total   69  2   42.0042.00 cost migration units means approximatively 1 man-day(s).
                    The migration unit was set to 5 minute(s)

-------------------------------------------------------------------------------
Migration level : B-5
-------------------------------------------------------------------------------

Migration levels:
    A - Migration that might be run automatically
    B - Migration with code rewrite and a human-days cost up to 5 days
    C - Migration with code rewrite and a human-days cost above 5 days
Technical levels:
    1 = trivial: no stored functions and no triggers
    2 = easy: no stored functions but with triggers, no manual rewriting
    3 = simple: stored functions and/or triggers, no manual rewriting
    4 = manual: no stored functions but with triggers or views with code rewriting
    5 = difficult: stored functions and/or triggers with code rewriting
-------------------------------------------------------------------------------

Details of cost assessment per function
Function test_dupl_vazba total estimated cost: 7
    CONCAT => 9 (cost: 0.1)
    TEST => 2
    SIZE => 1
    TO_CHAR => 1 (cost: 0.1)
    PRAGMA => 1 (cost: 3)
Function add_job_history total estimated cost: 3
    TEST => 2
    SIZE => 1
Function secure_dml total estimated cost: 3
    TEST => 2
    SIZE => 1
-------------------------------------------------------------------------------

Details of cost assessment per trigger
Trigger cisvpolpre_bi total estimated cost: 3.3
    CONCAT => 3 (cost: 0.1)
    TEST => 2
    SIZE => 1
Trigger update_job_history total estimated cost: 3
    TEST => 2
    SIZE => 1
-------------------------------------------------------------------------------

En fin de rapport, Ora2Pg affiche le nombre total d’objets rencontrés, les objets invalides et un nombre correspondant au nombre d’unités de coût de migration qu’il aura estimé nécessaire en fonction du code détecté. Cette unité vaut par défaut 5 minutes, cela correspond au temps moyen que mettrait un spécialiste pour porter le code. Dans l’exemple ci-dessus, on a donc une estimation par Ora2Pg d’une migration ayant un coût de 162,5 unités multipliées par 5 minutes, ce qui correspond en gros à 2 jours/personne.

L’ajustement de cette valeur est à faire en fonction de l’expérience de l’équipe en charge de la migration. Pour la première migration, il est tout à fait raisonnable de doubler ce coût dans le fichier de configuration ora2pg.conf :

COST_UNIT_VALUE       10

ou en ligne de commande :

ora2pg -t SHOW_REPORT --estimate_cost --cost_unit_value 10

Dans ce mode de rapport, Ora2Pg affiche aussi les détails du coût de migration estimé par routine.

Il est possible d’obtenir un rapport au format HTML en activant la directive DUMP_AS_HTML :

DUMP_AS_HTML       1

ou en utilisant l’option --dump_as_html en ligne de commande :

ora2pg -t SHOW_REPORT  --estimate_cost --cost_unit_value 10 --dump_as_html

Ora2Pg propose un exemple de rapport en HTML sur son site : Ora2Pg - Database Migration Report

Par défaut, Ora2Pg affiche les dix tables les plus volumineuses en termes de nombre de lignes et le top dix des tables les plus volumineuses en taille (hors partitions). Le nombre de table affichées peut être contrôlé avec la directive de configuration TOP_MAX.

ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html > report.html

L’installation est détaillée ici pour Rocky Linux 8 et 9 (similaire à Red Hat et à d’autres variantes comem Oracle Linux et Fedora), et Debian/Ubuntu.

Elle ne dure que quelques minutes.

Sur Rocky Linux 8 ou 9

Installation du dépôt communautaire  :

Les dépôts de la communauté sont sur https://yum.postgresql.org/. Les commandes qui suivent sont inspirées de celles générées par l’assistant sur https://www.postgresql.org/download/linux/redhat/, en précisant :

• la version majeure de PostgreSQL (ici la 17) ;
• la distribution (ici Rocky Linux 8) ;
• l’architecture (ici x86_64, la plus courante).

Les commandes sont à lancer sous root :

# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms\
/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# dnf -qy module disable postgresql

Installation de PostgreSQL 17 (client, serveur, librairies, extensions) : 

# dnf install -y postgresql17-server postgresql17-contrib

Les outils clients et les librairies nécessaires seront automatiquement installés.

Une fonctionnalité avancée optionnelle, le JIT (Just In Time compilation), nécessite un paquet séparé.

# dnf install postgresql17-llvmjit

Création d’une première instance :

Il est conseillé de déclarer PGSETUP_INITDB_OPTIONS, notamment pour mettre en place les sommes de contrôle et forcer les traces en anglais :

# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-17/bin/postgresql-17-setup initdb
# cat /var/lib/pgsql/17/initdb.log

Ce dernier fichier permet de vérifier que tout s’est bien passé et doit finir par :

Success. You can now start the database server using:

    /usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/17/data/ -l logfile start

Chemins :

Configuration :

Modifier postgresql.conf est facultatif pour un premier lancement.

Commandes d’administration habituelles :

Démarrage, arrêt, statut, rechargement à chaud de la configuration, redémarrage :

# systemctl start postgresql-17
# systemctl stop postgresql-17
# systemctl status postgresql-17
# systemctl reload postgresql-17
# systemctl restart postgresql-17

Test rapide de bon fonctionnement et connexion à psql :

# systemctl --all |grep postgres
# sudo -iu postgres psql

Démarrage de l’instance au lancement du système d’exploitation :

# systemctl enable postgresql-17

Ouverture du firewall pour le port 5432 :

Voir si le firewall est actif :

# systemctl status firewalld

Si c’est le cas, autoriser un accès extérieur :

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload
# firewall-cmd --list-all

(Rappelons que listen_addresses doit être également modifié dans postgresql.conf.)

Création d’autres instances :

Si des instances de versions majeures différentes doivent être installées, il faut d’abord installer les binaires pour chacune (adapter le numéro dans dnf install …) et appeler le script d’installation de chaque version. l’instance par défaut de chaque version vivra dans un sous-répertoire numéroté de /var/lib/pgsql automatiquement créé à l’installation. Il faudra juste modifier les ports dans les postgresql.conf pour que les instances puissent tourner simultanément.

Si plusieurs instances d’une même version majeure (forcément de la même version mineure) doivent cohabiter sur le même serveur, il faut les installer dans des PGDATA différents.

• Ne pas utiliser de tiret dans le nom d’une instance (problèmes potentiels avec systemd).
• Respecter les normes et conventions de l’OS : placer les instances dans un nouveau sous-répertoire de /var/lib/pgsqsl/17/ (ou l’équivalent pour d’autres versions majeures).

Pour créer une seconde instance, nommée par exemple infocentre :

• Création du fichier service de la deuxième instance :

# cp /lib/systemd/system/postgresql-17.service \
        /etc/systemd/system/postgresql-17-infocentre.service

• Modification de ce dernier fichier avec le nouveau chemin :

Environment=PGDATA=/var/lib/pgsql/17/infocentre

• Option 1 : création d’une nouvelle instance vierge :

# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-17/bin/postgresql-17-setup initdb postgresql-17-infocentre

• Option 2 : restauration d’une sauvegarde : la procédure dépend de votre outil.

• Adaptation de /var/lib/pgsql/17/infocentre/postgresql.conf (port surtout).

• Commandes de maintenance de cette instance :

# systemctl [start|stop|reload|status] postgresql-17-infocentre
# systemctl [enable|disable] postgresql-17-infocentre

• Ouvrir le nouveau port dans le firewall au besoin.

Sur Debian / Ubuntu

Sauf précision, tout est à effectuer en tant qu’utilisateur root.

Référence : https://apt.postgresql.org/

Installation du dépôt communautaire :

L’installation des dépôts du PGDG est prévue dans le paquet Debian :

# apt update
# apt install -y  gnupg2  postgresql-common 
# /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Ce dernier ordre créera le fichier du dépôt /etc/apt/sources.list.d/pgdg.list adapté à la distribution en place.

Installation de PostgreSQL 17 :

La méthode la plus propre consiste à modifier la configuration par défaut avant l’installation :

Dans /etc/postgresql-common/createcluster.conf, paramétrer au moins les sommes de contrôle et les traces en anglais :

initdb_options = '--data-checksums --lc-messages=C'

Puis installer les paquets serveur et clients et leurs dépendances :

# apt install postgresql-17 postgresql-client-17

La première instance est automatiquement créée, démarrée et déclarée comme service à lancer au démarrage du système. Elle porte un nom (par défaut main).

Elle est immédiatement accessible par l’utilisateur système postgres.

Chemins :

Configuration

Modifier postgresql.conf est facultatif pour un premier essai.

Démarrage/arrêt de l’instance, rechargement de configuration :

Debian fournit ses propres outils, qui demandent en paramètre la version et le nom de l’instance :

# pg_ctlcluster 17 main [start|stop|reload|status|restart]

Démarrage de l’instance avec le serveur :

C’est en place par défaut, et modifiable dans /etc/postgresql/17/main/start.conf.

Ouverture du firewall :

Debian et Ubuntu n’installent pas de firewall par défaut.

Statut des instances du serveur :

# pg_lsclusters

Test rapide de bon fonctionnement et connexion à psql :

# systemctl --all |grep postgres
# sudo -iu postgres psql

Destruction d’une instance :

# pg_dropcluster 17 main

Création d’autres instances :

Ce qui suit est valable pour remplacer l’instance par défaut par une autre, par exemple pour mettre les checksums en place :

• optionnellement, /etc/postgresql-common/createcluster.conf permet de mettre en place tout d’entrée les checksums, les messages en anglais, le format des traces ou un emplacement séparé pour les journaux :

initdb_options = '--data-checksums --lc-messages=C'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
waldir = '/var/lib/postgresql/wal/%v/%c/pg_wal'

• créer une instance :

# pg_createcluster 17 infocentre

Il est également possible de préciser certains paramètres du fichier postgresql.conf, voire les chemins des fichiers (il est conseillé de conserver les chemins par défaut) :

# pg_createcluster 17 infocentre \
  --port=12345 \
  --datadir=/PGDATA/17/infocentre \
  --pgoption shared_buffers='8GB' --pgoption work_mem='50MB' \
  --  --data-checksums --waldir=/ssd/postgresql/17/infocentre/journaux

• adapter au besoin /etc/postgresql/17/infocentre/postgresql.conf ;

• démarrage :

# pg_ctlcluster 17 infocentre start

Accès à l’instance depuis le serveur même (toutes distributions)

Par défaut, l’instance n’est accessible que par l’utilisateur système postgres, qui n’a pas de mot de passe. Un détour par sudo est nécessaire :

$ sudo -iu postgres psql
psql (17.0)
Type "help" for help.
postgres=#

Ce qui suit permet la connexion directement depuis un utilisateur du système :

Pour des tests (pas en production !), il suffit de passer à trust le type de la connexion en local dans le pg_hba.conf :

local   all             postgres                               trust

La connexion en tant qu’utilisateur postgres (ou tout autre) n’est alors plus sécurisée :

dalibo:~$ psql -U postgres
psql (17.0)
Type "help" for help.
postgres=#

Une authentification par mot de passe est plus sécurisée :

• dans pg_hba.conf, paramétrer une authentification par mot de passe pour les accès depuis localhost (déjà en place sous Debian) :

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

(Ne pas oublier de recharger la configuration en cas de modification.)

• ajouter un mot de passe à l’utilisateur postgres de l’instance :

dalibo:~$ sudo -iu postgres psql
psql (17.0)
Type "help" for help.
postgres=# \password
Enter new password for user "postgres":
Enter it again:
postgres=# quit

dalibo:~$ psql -h localhost -U postgres
Password for user postgres:
psql (17.0)
Type "help" for help.
postgres=#

• Pour se connecter sans taper le mot de passe à une instance, un fichier .pgpass dans le répertoire personnel doit contenir les informations sur cette connexion :

localhost:5432:*:postgres:motdepassetrèslong

Ce fichier doit être protégé des autres utilisateurs :

$ chmod 600 ~/.pgpass

• Pour n’avoir à taper que psql, on peut définir ces variables d’environnement dans la session voire dans ~/.bashrc :

export PGUSER=postgres
export PGDATABASE=postgres
export PGHOST=localhost

Rappels :

• en cas de problème, consulter les traces (dans /var/lib/pgsql/17/data/log ou /var/log/postgresql/) ;
• toute modification de pg_hba.conf ou postgresql.conf impliquant de recharger la configuration peut être réalisée par une de ces trois méthodes en fonction du système :

root:~# systemctl reload postgresql-17

root:~# pg_ctlcluster 17 main reload

postgres:~$ psql -c 'SELECT pg_reload_conf()'

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/n1_solutions.

Environnement

• Installer les paquets perl, perl-DBI requis pour le contexte d’exécution d’Ora2Pg ainsi que cpan, gcc, make, libaio-devel, perl-devel pour l’étape de compilation.

(Optionnel) Installation d’Oracle client

Requis si aucune instance Oracle Database n’est présente sur la machine.

• Installer le client Oracle à l’aide des fichiers .rpm de la dernière version en ligne sur http://www.oracle.com.

Installation du driver DBI pour Oracle

• Installer DBD::Oracle.

Installation du driver DBI pour PostgreSQL

• Installer DBD::Pg.

Téléchargement d’Ora2Pg

• Consulter le site officiel du projet et relevez la dernière version d’Ora2Pg.
• Télécharger les fichiers sources de la dernière version et les placer dans /opt/ora2pg/src.

Compilation et installation d’Ora2Pg

• Compiler et installer Ora2Pg.

Prise en main de l’instance Oracle

• Démarrer l’instance Oracle.

• Autoriser l’utilisateur hr à se connecter.

• Vérifier la connexion à la base Oracle avec sqlplus.

• IP de l’instance : demander au formateur
• Utilisateur : hr
• Mot de passe : phoenix
• Nom de service : hr

Création du rapport d’évaluation

• Définir une configuration globale avec un fichier /etc/ora2pg/ora2pg.conf.

• Créer un rapport d’évaluation rapport.html de la base HR en activant l’estimation de le charge de migration.

Les opérations d’installation de paquets (yum ou dnf, make install) sont à exécuter en tant que root. Le reste, dont les étapes de compilation, sont à effectuer avec un utilisateur normal.

Les commandes suivantes sont déstinées à être exécutées sur un serveur Rocky Linux 8.

Environnement

• Installer les paquets perl, perl-DBI requis pour le contexte d’exécution d’Ora2Pg ainsi que cpan, gcc, make, libaio-devel, perl-devel pour l’étape de compilation.

S’ils ne sont pas présents, installer les outils de compilation :

 # dnf install -y perl perl-DBI
 # dnf install -y cpan gcc make perl-devel libaio-devel

(Optionnel) Installation d’Oracle client

Requis si aucune instance Oracle Database n’est présente sur la machine.

• Installer le client Oracle à l’aide des fichiers .rpm de la dernière version en ligne sur http://www.oracle.com.

La version de l’Instant Client doit être au moins v12 pour accéder aux bases en 12c ; au plus en v10 pour accéder aux bases Oracle 8 ou 9.

Voici les archives à télécharger pour la version 19c, en architecture 64 bits (adapter le numéro de version au besoin) :

export D=https://download.oracle.com/otn_software/linux/instantclient/1917000
wget $D/oracle-instantclient19.17-basic-19.17.0.0.0-1.x86_64.rpm
wget $D/oracle-instantclient19.17-devel-19.17.0.0.0-1.x86_64.rpm
wget $D/oracle-instantclient19.17-sqlplus-19.17.0.0.0-1.x86_64.rpm

Installer ensuite les paquets téléchargés :

 # dnf install -y oracle-instantclient19.17-basic-19.17.0.0.0-1.x86_64.rpm \
                  oracle-instantclient19.17-devel-19.17.0.0.0-1.x86_64.rpm \
                  oracle-instantclient19.17-sqlplus-19.17.0.0.0-1.x86_64.rpm

Installation du driver DBI pour Oracle

• Installer DBD::Oracle.

Le driver Oracle pour Perl DBI peut être compilé à partir du module de recherche du site CPAN : http://search.cpan.org/search?query=DBD::Oracle

L’utilitaire cpan permet de réaliser automatiquement les opérations de téléchargement, de contrôle des dépendances et de compilation de l’ensemble des modules Perl proposés par la communauté. Il est à privilégier pour la gestion des mises à jour de ces modules.

Voici la procédure d’installation complète de la dernière version du driver DBD::Oracle à suivre avec l’utilisateur root :

• Avec les librairies Oracle Database (lorsqu’une instance Oracle est présente sur le serveur) :

 # export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE
 # export LD_LIBRARY_PATH=$ORACLE_HOME/lib

• Avec les librairies Instant Client (en cas d’absence des binaires Oracle sur le serveur) :

 # export ORACLE_HOME=/usr/lib/oracle/19.17/client64
 # export LD_LIBRARY_PATH=$ORACLE_HOME/lib

• Installation du driver

L’option -T de cpan permet d’ignorer la phase de tests des fichiers compilés.

 # cpan install -T DBD::Oracle

Installation du driver DBI pour PostgreSQL

• Installer DBD::Pg.

Le driver PostgreSQL pour Perl DBI peut être trouvé sur le CPAN, mais privilégier l’installation par paquet :

 # dnf install -y perl-DBD-Pg

Téléchargement d’Ora2Pg

• Consulter le site officiel du projet et relevez la dernière version d’Ora2Pg.
• Télécharger les fichiers sources de la dernière version et les placer dans /opt/ora2pg/src.

Consulter le site officiel du projet https://ora2pg.darold.net/, la page News indique la dernière version d’Ora2Pg. Le téléchargement des fichiers source de la dernière version officielle se fait depuis le dépôt Github :

 # mkdir -p /opt/ora2pg/src
 # cd /opt/ora2pg/src/
 # wget https://github.com/darold/ora2pg/archive/refs/tags/v24.1.tar.gz
 # tar xvf v24.1.tar.gz
 # cd ora2pg-24.1

La version master de Github est celle en développement et peut être éventuellement nécessaire.

Compilation et installation d’Ora2Pg

• Compiler et installer Ora2Pg.

Les instructions peuvent être exécutées avec le compte root, notamment la commande make install pour déployer les binaires dans les répertoires systèmes.

 # unset PERL_MM_OPT
 # perl Makefile.PL
 # make && make install

Prise en main de l’instance Oracle

• Démarrer l’instance Oracle.

En se connectant à l’utilisateur oracle pour la première fois, il est nécessaire de démarrer correctement l’instance de démonstration. Il s’agit d’une installation en mode container.

# changer d'utilisateur linux
sudo -iu oracle

lsnrctl start
rlwrap sqlplus / as sysdba

Dans l’invite de commandes, déclencher le démarrage de l’instance.

SQL> startup

• Autoriser l’utilisateur hr à se connecter.

Se positionner dans la base HR pour réactiver le compte.

SQL> ALTER SESSION SET container = hr;
SQL> ALTER PROFILE default LIMIT password_life_time UNLIMITED;
SQL> ALTER USER hr IDENTIFIED BY "phoenix";

• Vérifier la connexion à la base Oracle avec sqlplus.

Ajouter les variables d’environnements dans le fichier .bash_profile de l’utilisateur dalibo.

$ cat ~/.bash_profile
export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin:/usr/local/bin

Charger les variables :

source $HOME/.bash_profile

Ouvrir une session sur l’instance Oracle et se déconnecter :

sqlplus hr/phoenix@localhost:1521/hr

SQL> exit

Création du rapport d’évaluation

• Définir une configuration globale avec un fichier /etc/ora2pg/ora2pg.conf.

Lors de l’installation, il se peut que ce fichier soit absent. Il est nécessaire de copier le fichier de la distribution vers le fichier de configuration global.

sudo cp /etc/ora2pg/ora2pg.conf.dist /etc/ora2pg/ora2pg.conf

Pour la suite des exercices, renseigner les paramètres de connexions à l’instance Oracle dans la première section du fichier de configuration.

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN      dbi:Oracle://localhost:1521/hr
ORACLE_USER     hr
ORACLE_PWD      phoenix

• Créer un rapport d’évaluation rapport.html de la base HR en activant l’estimation de le charge de migration.

La commande suivante permet de créer le rapport d’évaluation :

ora2pg -t SHOW_REPORT -n hr --estimate_cost --dump_as_html > rapport.html

L’en-tête du rapport fait l’état de la version du serveur Oracle et du nom du schéma qui a été analysé. La volumétrie est calculée d’après la somme des segments (tables, index, vues matérialisées, etc.) présents dans le schéma.

Le tableau principal du rapport contient le décompte des objets identifiés dans le schéma, comme les tables ou les index. Pour chaque catégorie, le coût estimé permet de comprendre rapidement où se situe l’effort requis pour réaliser la migration. Une colonne de commentaire donne des éléments supplémentaires sur le décompte et restitue des conseils génériques sur la migration de tel ou tel composant.

Le score de complexité (de A-1 à C-5) permet de classifier la migration d’un schéma selon plusieurs niveaux de complexité. Ici, le score du schéma HR vaut B-5 :

• La migration prévoit de la réécriture de code et le coût est inférieur à 5 jour/personne ;
• La difficulté est maximale avec la présence de procédures stockées ou de triggers avec de la réécriture de code.

Enfin, la dernière partie apporte un détail plus précis sur le calcul du coût par functions, procédures et triggers. Par défaut, le coût d’une réécriture d’une méthode, même simple, vaudra 3 unités, soit l’équivalent de 15 minutes.

Ce module a pour but de montrer la configuration et l’utilisation d’Ora2Pg.

Nous allons aborder ici les différentes étapes de la configuration d’Ora2Pg :

• la configuration en elle-même ;
• comment se connecter à la base Oracle ?
• comment valider la configuration ?
• que contient la base de données et comment Ora2Pg va l’exporter ?
• comment estimer la charge de la migration ?
• comment créer un fichier de configuration générique ?

Chaque ligne non commentée doit commencer par l’une des clés de configuration. Il y en a environ 180 différentes.

La valeur de cette clé est variable. La directive de configuration et sa valeur doivent être séparées par une ou plusieurs tabulations.

Lorsque la valeur est une liste, le séparateur des éléments de la liste est généralement le caractère espace.

SKIP        fkeys pkeys ukeys indexes checks

Toutes les clés dont la valeur peut être une liste peuvent être répétées plusieurs fois, exemple :

SKIP        fkeys pkeys ukeys
SKIP        indexes checks

Pour les autres, si elles sont répétées, la dernière valeur indiquée sera la valeur prise en compte.

IMPORT

Cette variable permet d’inclure un fichier de configuration dans le fichier ora2pg.conf. Ainsi on peut définir les variables communes à toutes les configurations dans un seul fichier, qu’on inclut dans tous les autres.

Par exemple :

IMPORT common.conf

ORACLE_HOME

Cette variable très connue dans le monde Oracle permet de déterminer où se trouve le répertoire contenant toutes les bibliothèques Oracle ainsi que les autres fichiers d’un client (ou d’un serveur) Oracle.

Par exemple, pour un serveur Oracle 18c Express Edition, le ORACLE_HOME ressemble à cela :

ORACLE_HOME /opt/oracle/product/18c/dbhomeXE

Pour un client de la même version, on peut avoir :

ORACLE_HOME /usr/lib/oracle/18.5/client64

Si la variable d’environnement ORACLE_HOME était définie au moment de l’installation, ce paramètre possède alors déjà la bonne valeur.

DEBUG

Lorsque DEBUG est positionné à 1, Ora2Pg va envoyer tous les messages d’information, y compris les messages d’erreur, sur la console.

Si cette variable est positionnée à 0, alors Ora2Pg restera muet.

Il est recommandé de le désactiver par défaut et, s’il doit être activé, de rediriger la sortie standard dans un fichier ou d’utiliser un fichier de traces en donnant le chemin complet à la directive LOGFILE.

LOGFILE

La valeur de cette directive correspond à un fichier dans lequel seront ajoutés tous les messages retournés par Ora2Pg. Ceci permet notamment de garder la trace complète des messages de la migration pour s’assurer qu’il n’y a pas eu de messages d’erreur.

ORACLE_DSN

Cette variable permet de déterminer la chaîne de connexion au serveur Oracle. On y trouve en particulier :

• le connecteur DBI à utiliser : dbi:Oracle
• le nom du serveur (ou son adresse IP) : host=
• le nom de l’instance Oracle: sid=

Voici par exemple la chaîne de connexion permettant de se connecter à l’instance DB_SID sur le serveur Oracle oracle_server :

ORACLE_DSN     dbi:Oracle:host=oracle_server;sid=DB_SID

Il est possible aussi d’utiliser la notation Easy Connect ou simplement l’alias de connexion renseigné dans le fichier tnsnames.ora :

ORACLE_DSN     Oracle://serveur:1521/service # Easy connect
ORACLE_DSN     dbi:Oracle:XE             # Alias TNS

L’alias ci-dessus dispose de la définition suivante dans le fichier $ORACLE_HOME/network/admin/tnsnames.ora :

$ cat <<EOF >> $ORACLE_HOME/network/admin/tnsnames.ora
  XE = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.10) (port = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB_NAME))
  )
EOF

On peut tester cela simplement avec des outils comme tnsping ou encore sqlplus.

Pour MySQL un datasource typique sera de la forme :

ORACLE_DSN  dbi:mysql:host=192.168.1.10;database=sakila;port=3306

la partie SID propre à Oracle database est remplacée ici par database.

ORACLE_USER et ORACLE_PWD

On définit avec ces variables l’utilisateur et le mot de passe avec lesquels Ora2Pg va se connecter au serveur Oracle pour en extraire des informations (schéma, données, etc.).

Il est préférable que cet utilisateur soit déclaré comme un SYSDBA dans Oracle, c’est-à-dire un utilisateur privilégié de type DBA (un peu comme l’utilisateur postgres l’est généralement pour un serveur PostgreSQL).

SCHEMA

Cette variable permet de déterminer le schéma ou utilisateur Oracle dont les objets ou données seront exportés. Le paramètre ORACLE_USER défini précédemment dans le fichier de configuration doit avoir les droits nécessaires sur les objets de ce schéma.

Par exemple, pour exporter les objets du schéma HR de la base de données de démonstration, la valeur de la directive doit être :

SCHEMA  HR

Si aucun schéma n’est précisé, les objets ou données de tous les schémas de l’instance seront exportés hormis ceux définis dans le paramètre SYSUSERS.

SYSUSERS

Ce paramètre permet d’exclure, à l’origine, tous les utilisateurs système d’Oracle et leur schéma qui, parfois, contiennent des tables systèmes qui sont superflues pour une migration vers PostgreSQL.

À ce jour, les utilisateurs ignorés par Ora2Pg sont les suivants :

SYSTEM CTXSYS DBSNMP EXFSYS LBACSYS MDSYS MGMT_VIEW OLAPSYS ORDDATA OWBSYS
ORDPLUGINS ORDSYS OUTLN SI_INFORMTN_SCHEMA SYS SYSMAN WK_TEST WKSYS WKPROXY
WMSYS XDB APEX_PUBLIC_USER DIP FLOWS_020100 FLOWS_030000 FLOWS_040100
FLOWS_010600 FLOWS_FILES MDDATA ORACLE_OCM SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR XS$NULL PERFSTAT SQLTXPLAIN DMSYS TSMSYS WKSYS APEX_040000
APEX_040200 DVSYS OJVMSYS GSMADMIN_INTERNAL APPQOSSYS DVSYS DVF AUDSYS
APEX_030200 MGMT_VIEW ODM ODM_MTR TRACESRV MTMSYS OWBSYS_AUDIT WEBSYS WK_PROXY
OSE$HTTP$ADMIN AURORA$JIS$UTILITY$ AURORA$ORB$UNAUTHENTICATED
DBMS_PRIVILEGE_CAPTURE CSMIG MGDSYS SDE DBSFWUSER

On peut utiliser cette fonctionnalité d’une manière détournée pour ignorer les objets appartenant à d’autres utilisateurs.

Tout utilisateur spécifié dans la clause SYSUSERS sera ignoré, en plus des utilisateurs ignorés par défaut (voir liste ci-dessus).

Par exemple, si on veut ignorer les objets des utilisateurs RECETTE et DEV, il est possible d’ajouter une nouvelle ligne qui enrichira la liste des schémas à exclure :

SYSUSERS    RECETTE,DEV

USER_GRANTS

Ce paramètre est par défaut à 0 car Ora2Pg part du principe que l’on utilise un utilisateur privilégié (membre du groupe DBA, comme SYSTEM) pour, par exemple, exporter la définition des objets.

En effet, Ora2Pg utilise intensivement les vues de type DBA_.... Or, un utilisateur non privilégié n’a pas accès à ces vues, réservées aux administrateurs de la base de données. On peut alors configurer USER_GRANTS à 1 pour utiliser un utilisateur Oracle non DBA. Dans ce cas, Ora2Pg utilisera les vues de type ALL_... pour récupérer la définition des objets.

À noter, qu’alors, cela ne fonctionnera pas avec les types d’export GRANT et TABLESPACE qui doivent impérativement être réalisés par un utilisateur avec les privilèges DBA. L’analyse de requêtes applicatives dans la table DBA_AUDIT_TRAIL (export type QUERY), nécessite aussi ce privilège.

$ chown 660 /etc/ora2pg/ora2pg.conf

Pour tester que les paramètres de connexion à l’instance Oracle sont les bons, on peut utiliser les actions de rapports simples d’Ora2Pg qui ne nécessitent que la configuration des variables de connexion.

Par exemple, pour l’instance d’exemple fournie par Oracle XE et le schéma HR, la commande :

ora2pg -t SHOW_SCHEMA

permettra de lister tous les schémas de l’instance Oracle pour trouver la bonne valeur à donner à la directive SCHEMA dans le fichier de configuration.

La commande :

ora2pg -t SHOW_TABLE

donne la liste des tables qui seront exportées et le nombre d’enregistrements pour chaque table :

[1] TABLE COUNTRIES (owner: HR, 25 rows)
[2] TABLE DEPARTMENTS (owner: HR, 27 rows)
[3] TABLE EMPLOYEES (owner: HR, 107 rows)
[4] TABLE JOBS (owner: HR, 19 rows)
[5] TABLE JOB_HISTORY (owner: HR, 10 rows)
[6] TABLE LOCATIONS (owner: HR, 23 rows)
[7] TABLE REGIONS (owner: HR, 4 rows)

Si des tables sont non loguées (unlogged tables), correspondent à des tables externes ou sont partitionnées, Ora2Pg l’affichera à côté du nom de la table.

[19] UNLOGGED TABLE REGIONS (owner: HR, 4 rows)
[20] PARTITIONED TABLE SALES_PARTITIONED (owner: HR, 0 rows) - 2 partitions

L’utilisation de l’action SHOW_COLUMN :

ora2pg -t SHOW_COLUMN -a COUNTRIES

renvoie le détail des colonnes de la table COUNTRIES et notamment les correspondances des types de colonnes qui seront utilisés pour la migration :

[1] TABLE COUNTRIES (owner: HR, 25 rows)
    COUNTRY_ID : CHAR(2) => char(2)
    COUNTRY_NAME : VARCHAR2(40) => varchar(40)
    REGION_ID : NUMBER(22) => bigint

S’il s’agit d’une table contenant des objets géométriques avec une contrainte sur le type d’objet, Ora2Pg donnera son équivalent PostGIS :

[1] TABLE TRAJETS (owner: HR, 1 rows)
    MKT_ID : NUMBER(22) => bigint
    NAME : VARCHAR2(32) => varchar(32)
    START_POINT : SDO_GEOMETRY => geometry(POINT,4326)
    FINISH_POINT : SDO_GEOMETRY => geometry(GEOMETRY,4326) - POINT,LINESTRING

SHOW_ENCODING

ora2pg -t SHOW_ENCODING -c ../ora2pg.conf

Ceci retournera les valeurs NLS_LANG, NLS_NCHAR et CLIENT_ENCODING, qui seront utilisées par Ora2Pg, mais aussi l’encodage réel de la base Oracle et de l’encodage correspondant dans PostgreSQL. Par exemple :

Current encoding settings that will be used by Ora2Pg:
    Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8
    Oracle NLS_NCHAR AL32UTF8
    Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6
    Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
    PostgreSQL CLIENT_ENCODING UTF8
    Perl output encoding ''
Showing current Oracle encoding and possible PostgreSQL client encoding:
    Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8
    Oracle NLS_NCHAR AL32UTF8
    Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6
    Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
    PostgreSQL CLIENT_ENCODING UTF8

NLS_LANG et NLS_CHAR

Par défaut, Ora2Pg va utiliser l’encodage AMERICAN_AMERICA.AL32UTF8 au niveau du client Oracle. Il est toutefois possible de le changer et de forcer sa valeur avec la variable de configuration NLS_LANG. De même, la variable de session NLS_NCHAR a la valeur AL32UTF8 par défaut.

Il est fortement conseillé de conserver le comportement par défaut d’Ora2Pg pour éviter les erreurs liées à l’encodage, mais on peut le changer si l’on veut éviter le coût de l’encodage ou qu’une table Oracle ne respecte pas l’encodage lors de l’export des données. Dans ce cas, le NLS_LANG doit correspondre au paramétrage obtenu lorsqu’on ouvre une session sur Oracle avec l’utilisateur Oracle spécifié dans la configuration d’Ora2Pg. Pour cela, on se connecte à l’instance avec cet utilisateur, et on peut lire le paramétrage NLS (acronyme de National Language Support) comme suit :

$ sqlplus hr/secret@xe

SQL> set pages 80;
SQL> select * from nls_session_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   FRENCH
NLS_TERRITORY                  FRANCE
NLS_CURRENCY                   €
NLS_ISO_CURRENCY               FRANCE
NLS_NUMERIC_CHARACTERS         ,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/RR
NLS_DATE_LANGUAGE              FRENCH
NLS_SORT                       FRENCH
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              €
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 ligne(s) sélectionnée(s).

On peut aussi exécuter une requête pour récupérer le paramétrage de l’instance toute entière avec :

SELECT * FROM nls_instance_parameters ;

Ce paramétrage au niveau instance se modifie avec l’ordre ALTER SYSTEM, ainsi qu’au niveau de la base de données :

SELECT * FROM nls_database_parameters;

Ce paramétrage au niveau base de données ne se modifie pas, il est défini lors de la création de la base de données avec un SET.

CLIENT_ENCODING

Par défaut la valeur de cette directive est UTF8, c’est celle qui correspond à l’encodage unicode utilisé pour extraire les données d’Oracle.

Si le NLS_LANG a été modifié dans le fichier de configuration et pour que la conversion des données en provenance d’Oracle vers PostgreSQL soit exacte, il faut définir l’encodage à utiliser par le client PostgreSQL. Ainsi, si la variable NLS_LANG côté connexion Oracle est FRENCH_FRANCE.WE8ISO8859P1, il faudra utiliser l’encodage LATIN1 côté client PostgreSQL pour ne pas avoir de problème de conversion d’encodage des données.

Pour vous aider à trouver le jeu de caractères dans PostgreSQL correspondant à celui sous Oracle, vous pouvez consulter ce document, 22.3. Character Set Support, qui fait partie de la documentation officielle de PostgreSQL.

BINMODE

Par défaut le paramètre est positionné à utf8 si NLS_LANG utilise un encodage unicode. Il n’est donc normalement pas nécessaire de modifier cette variable de configuration. Lors de l’utilisation d’un encodage unicode, il est indispensable de le positionner à la valeur utf8 pour éviter les erreurs d’écriture Perl de type Wide character in print.

Le but est d’avoir un fichier de configuration générique qui sera utilisé pour tous les types d’export et d’utiliser la souplesse des options en ligne de commande du script ora2pg.

On commande d’abord à Ora2Pg de créer des fichiers de sortie différents pour les contraintes (FILE_PER_CONSTRAINT), les index (FILE_PER_INDEX) et les clés étrangères (FILE_PER_FKEY). Cela nous permettra de ne les importer qu’à la fin de la migration pour ne pas être gêné ou ralenti lors de l’import de données.

On peut aussi générer un fichier différent par table (FILE_PER_TABLE) lors de l’export des données et par routine (FILE_PER_FUNCTION) pour permettre un traitement individualisé.

La désactivation des triggers pour chaque table avant l’import des données est réalisée, peu importe s’ils ont été importés auparavant ou non. Cela évitera leur déclenchement s’ils ont été importés et n’aura pas d’effet si ce n’est pas le cas, DISABLE_TRIGGERS doit donc être activé. Il est toutefois préférable de ne charger les triggers qu’à la fin.

Les deux directives TRUNCATE_TABLE et DISABLE_SEQUENCE permettent de déterminer le comportement lors de l’export des données, à savoir respectivement l’ajout des ordres SQL de troncature des tables avant le chargement des données et la désactivation des ordres de réinitialisation des séquences après le chargement, ces dernières n’étant importées qu’à la fin.

COMPILE_SCHEMA permet de demander à Oracle de vérifier à nouveau le code PL/SQL et de valider ce qui doit l’être. Par exemple, un trigger a pu être ajouté et faire appel à une fonction avant qu’elle ne soit créée, et sera dans ce cas marqué invalide par Oracle. L’activation de cette variable permet de forcer Oracle à revalider le code. Par défaut ce comportement n’est pas activé, le code valide seul sera exporté.

La conversion automatique du code des routines stockées est désactivée pour pouvoir obtenir les sources du code. On utilisera l’option -p lors de l’exécution d’ora2pg afin de l’activer.

Il faut ensuite se poser la question de savoir si l’on souhaite recréer le schéma ou l’utilisateur Oracle sous lequel seront créés tous les objets dans PostgreSQL. Si la réponse est oui, il faut activer la directive EXPORT_SCHEMA et désactiver la directive CREATE_SCHEMA car la création du schéma peut se faire de manière manuelle lors de la création de la base de données et de son propriétaire.

Le schéma utilisé pour définir le search_path à la création des objets sera celui donné comme valeur de la variable SCHEMA par défaut ou celui défini par la variable PG_SCHEMA si vous souhaitez changer de nom de schéma ou que vous devez accéder à d’autres schémas lors de l’import des objets.

À ce stade, il est possible de ne plus toucher au fichier de configuration en dehors de particularités de la base Oracle obligeant à modifier certaines variables. Dans ce cas, il sera préférable de travailler sur une copie du fichier ou d’utiliser la directive INCLUDE en fin de fichier de configuration.

Ora2pg considère toujours que vous utilisez la dernière version officielle de PostgreSQL disponible à la sortie de la version d’Ora2Pg que vous utilisez. Cependant il est possible que vous ayez besoin de migrer dans une base PostgreSQL d’une version antérieure, mais toutes les fonctionnalités supportées par Ora2Pg n’y existent pas forcément encore.

Pour pouvoir contrôler cela il est nécessaire de positionner la directive PG_VERSION à la dernière version majeure de PostgreSQL. Ora2Pg adaptera l’export en fonction des fonctionnalités développées dans chaque version.

D’autres directives permettent d’activer ou de désactiver certaines fonctionnalités :

• BITMAP_AS_GIN pour autoriser l’export des index bitmap dans leur équivalent avec l’extension btree_gin ;
• STANDARD_CONFORMING_STRINGS pour l’échappement dans les chaines de caractères.

La valeur de STANDARD_CONFORMING_STRINGS doit correspondre à la valeur de la variable standard_conforming_string dans le fichier postgresql.conf.

Par défaut donc, tous ces paramètres sont activés.

Les colonnes ayant pour type Oracle spatial SDO_GEOMETRY, peuvent contenir n’importe quel type de géométrie. Le type équivalent pour PostGIS est geometry.

CREATE TABLE test_geom (
    id bigint,
    shape geometry(GEOMETRY, 4326)
);

Dans ce cas, elles pourront aussi contenir n’importe quel type de géométrie.

Il peut être intéressant d’avoir une contrainte sur le type des géométries pouvant être insérées dans la colonne si c’est toujours le même type d’objet géométrique qui doit être utilisé.

Dans ce cas, en activant la directive AUTODETECT_SPATIAL_TYPE, Ora2Pg cherchera d’abord s’il existe une contrainte géométrique sur la colonne pour déterminer le type. S’il n’y a pas d’index de contrainte alors il cherchera dans la colonne Oracle si les données sont toutes du même type. Dans ce dernier cas, Ora2Pg prend comme échantillon les 50 000 premières géométries de la colonne (ou la valeur de AUTODETECT_SPATIAL_TYPE si elle est supérieure à 1). Si les objets spatiaux de l’échantillon sont tous du même type, alors la contrainte est appliquée.

CREATE TABLE test_geom (
    id bigint,
    shape geometry(POLYGON, 4326)
);

Le système de référence spatial (SRID) utilisé va être la valeur retournée depuis la table des métadonnées spatiales Oracle (ALL_SDO_GEOM_METADATA) ou, si la valeur retournée est nulle, la valeur donnée à la directive de configuration DEFAULT_SRID. Voici à peu de chose près la requête utilisée :

SELECT COALESCE(SRID, $DEFAULT_SRID)
  FROM ALL_SDO_GEOM_METADATA
 WHERE TABLE_NAME='$table' AND COLUMN_NAME='$colname';

Si la directive CONVERT_SRID est activée alors la conversion en ESPG est demandée et dans ce cas la requête utilisée par Ora2Pg pour obtenir le SRID sera la suivante :

SELECT COALESCE(sdo_cs.map_oracle_srid_to_epsg(SRID), $DEFAULT_SRID)
  FROM ALL_SDO_GEOM_METADATA
 WHERE TABLE_NAME='$table' AND COLUMN_NAME='$colname';

Si l’extension PostGIS a été installée dans un schéma spécifique, les appels aux fonctions de l’extension devront être préfixés par le nom du schéma. Pour éviter cela, il est préférable de positionner le nom du schéma PostGIS dans la directive POSTGIS_SCHEMA et celui-ci sera ajouté au search_path lors de la création des objets.

Pour l’export des géométries, il est préférable d’utiliser le type INTERNAL pour la directive GEOMETRY_EXTRACT_TYPE. Cela évite d’utiliser les fonctions Oracle pour extraire la géométrie au format texte (WKT) ou binaire (WKB). Ces modes nécessitent l’utilisation de fonctions Oracle (SDO_UTIL.TO_WKTGEOMETRY()) et (SDO_UTIL.TO_WKBGEOMETRY()) qui sont lentes et ont la particularité de planter l’export dès que le volume est important.

Lors de l’export des LOB, si la directive NO_LOB_LOCATOR est activée, il se peut que vous rencontriez l’erreur Oracle :

ORA-24345: A Truncation or null fetch error occurred
(DBD SUCCESS_WITH_INFO: OCIStmtFetch, LongReadLen too small
 and/or LongTruncOk not set)

La solution est d’augmenter la valeur du paramètre LONGREADLEN, par défaut 1 Mo, à la taille du plus grand enregistrement de la colonne. Vous avez aussi la possibilité de tronquer les données en activant LONGTRUNCOK, ce qui ne remontera plus d’erreur mais bien évidement tronquera certaines données dont la taille dépasse la valeur de LONGREADLEN.

Il est conseillé de laisser Ora2Pg gérer l’export des LOB en utilisant des pointeurs sur les enregistrements (LOB Locator) lui permettant de récupérer les données de ces champs en plusieurs fois. Cela évite la contrainte de recherche de la bonne valeur à attribuer à LONGREADLEN.

Lors de l’export de champs LOB, il est important de diminuer très fortement la valeur de DATA_LIMIT en fonction de la vitesse maximale d’export pour éviter les dépassements de mémoire. Pour permettre à Ora2Pg d’extraire ces données avec les autres en adaptant automatiquement le DATA_LIMIT à une valeur plus faible lorsqu’il s’agit d’un LOB, la directive BLOB_LIMIT est disponible.

BLOB_LIMIT     500

La valeur de 500, voire moins, n’est pas rare avec ce type d’objet. Si cette directive n’est pas définie, par défaut, Ora2Pg est capable de détecter qu’il s’agit d’une table avec un champ BLOB et de diminuer automatiquement la valeur de DATA_LIMIT en la divisant par 10 jusqu’à ce qu’elle soit inférieure ou égale à 1000.

Une bonne pratique consiste donc à positionner une valeur à la directive BLOB_LIMIT pour forcer Ora2Pg à utiliser cette valeur pour les tables avec BLOB et continuer à utiliser la valeur de DATA_LIMIT pour les tables sans BLOB.

Nous allons aborder ici les différentes étapes à réaliser pour mettre en œuvre de façon optimale l’export du schéma :

• Comment s’y retrouver dans tous les fichiers générés et ne pas écraser le précédent export ?
• Comment utiliser la configuration générique ?
• Et enfin l’export complet du schéma Oracle en des ordres DDL PostgreSQL ?

Il est important d’organiser l’espace de travail de son projet de migration. Sans cela, on se retrouve très vite avec une multitude de fichiers dont le contenu devient très vite énigmatique.

Dans la mesure où, par défaut, Ora2Pg fait tous ses exports dans un même fichier nommé output.sql, vous pouvez aussi très facilement écraser le précédent export si vous omettez de renommer le fichier.

À minima, il est conseillé d’avoir :

• un répertoire dédié au stockage du ou des fichiers de configuration ;
• un répertoire dédié aux fichiers des données exportées ;
• un répertoire de stockage des sources du code Oracle ;
• un répertoire des objets et code convertis à la syntaxe PostgreSQL.

L’export du code source du code SQL et PL/SQL dans des fichiers dans un espace de stockage particulier est très important. Cela permet, lors de la phase de migration des routines stockées, de vérifier qu’Ora2Pg n’a pas corrompu du code et de comparer le code.

Pour créer une arborescence de travail destinée à recevoir les fichiers du projet de migration, on peut s’aider d’ora2pg en exécutant la commande suivante :

ora2pg --init_project mydb_project --project_base /opt/ora2pg

Voici l’arborescence générée par Ora2Pg :

/opt/ora2pg/mydb_project/
├── config
│   └── ora2pg.conf
├── data
├── export_schema.sh
├── import_all.sh
├── reports
├── schema
│   ├── dblinks
│   ├── directories
│   ├── functions
│   ├── grants
│   ├── mviews
│   ├── packages
│   ├── partitions
│   ├── procedures
│   ├── sequences
│   ├── synonyms
│   ├── tables
│   ├── tablespaces
│   ├── triggers
│   ├── types
│   └── views
└── sources
    ├── functions
    ├── mviews
    ├── packages
    ├── partitions
    ├── procedures
    ├── triggers
    ├── types
    └── views

La commande utilisée pour la génération automatique de l’espace de travail a permis de générer un fichier de configuration générique config/ora2pg.conf et un script shell export_schema.sh. Ce script peut être utilisé pour générer automatiquement tous les types d’export en dehors de l’export des données. Voici son contenu :

#!/bin/sh
#-------------------------------------------------------------------------------
#
# Generated by Ora2Pg, the Oracle database Schema converter, version 23.2
#
#-------------------------------------------------------------------------------
EXPORT_TYPE="SEQUENCE TABLE PACKAGE VIEW GRANT TRIGGER FUNCTION PROCEDURE
             TABLESPACE PARTITION TYPE MVIEW DBLINK SYNONYM DIRECTORY"
SOURCE_TYPE="PACKAGE VIEW TRIGGER FUNCTION PROCEDURE PARTITION TYPE MVIEW"
namespace="."
unit_cost=5

ora2pg -t SHOW_TABLE -c $namespace/config/ora2pg.conf > $namespace/reports/tables.txt
ora2pg -t SHOW_COLUMN -c $namespace/config/ora2pg.conf > $namespace/reports/columns.txt
ora2pg -t SHOW_REPORT -c $namespace/config/ora2pg.conf --dump_as_html \
  --cost_unit_value  $unit_cost --estimate_cost > $namespace/reports/report.html

for etype in $(echo $EXPORT_TYPE | tr " " "\n")
do
    ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
    ltype=`echo $ltype | sed 's/y$/ie/'`
    echo "Running: ora2pg -p -t $etype -o $ltype.sql -b $namespace/schema/${ltype}s
                          -c $namespace/config/ora2pg.conf"
    ora2pg -p -t $etype -o $ltype.sql -b $namespace/schema/${ltype}s \
            -c $namespace/config/ora2pg.conf
    ret=`grep "Nothing found" $namespace/schema/${ltype}s/$ltype.sql 2> /dev/null`
    if [ ! -z "$ret" ]; then
        rm $namespace/schema/${ltype}s/$ltype.sql
    fi
done

for etype in $(echo $SOURCE_TYPE | tr " " "\n")
do
    ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
    ltype=`echo $ltype | sed 's/y$/ie/'`
    echo "Running: ora2pg -t $etype -o $ltype.sql -b $namespace/sources/${ltype}s 
                          -c $namespace/config/ora2pg.conf"
    ora2pg -t $etype -o $ltype.sql -b $namespace/sources/${ltype}s \
            -c $namespace/config/ora2pg.conf
    ret=`grep "Nothing found" $namespace/sources/${ltype}s/$ltype.sql 2> /dev/null`
    if [ ! -z "$ret" ]; then
        rm $namespace/sources/${ltype}s/$ltype.sql
    fi
done

echo
echo
echo "To extract data use the following command:"
echo
echo "ora2pg -t COPY -o data.sql -b $namespace/data -c $namespace/config/ora2pg.conf"
echo

exit 0

Une fois la connexion à la base Oracle paramétrée dans le fichier de configuration générique, il suffit d’exécuter ce script pour que tous les exports soient réalisés. Le script réalisera même le rapport sur la base au format HTML.

Ora2Pg aura aussi créé un script import_all.sh utilisé pour l’import dans PostgreSQL des divers objets exportés et disponibles sous forme de fichiers dans l’espace de travail après exécution du script export_schema.sh. Si les données ont aussi été exportées sous forme de fichiers dans l’espace de travail, le script permet de les charger dans PostgreSQL, sinon il permettra de les charger directement depuis Oracle en utilisant les options de parallélisme d’Ora2Pg.

Pour les bases MySQL, il est nécessaire d’ajouter l’option -m ou --mysql pour indiquer à Ora2Pg qu’il s’agit d’un projet de migration de base MySQL.

Lors de la création par Ora2Pg du répertoire de travail, le fichier de configuration générique est créé à partir du fichier /etc/ora2pg/ora2pg.conf.dist et enregistré dans le répertoire mydb_project/config/. Les modifications appliquées à ce fichier sont celles exposées dans le chapitre Configuration générique. Si le fichier n’existe pas, il suffit de le copier et d’appliquer les préconisations de configuration.

On peut aussi demander à Ora2Pg d’utiliser un fichier de configuration prédéfini en le précisant avec l’option -c config_file lors de l’exécution de la commande ora2pg --init_project, c’est alors ce fichier qui sera copié dans l’espace de travail.

Les options de connexion à Oracle peuvent être données en ligne de commande avec les options d’Ora2Pg dédiées à cet effet (-s, -u et -n). Les valeurs de ces paramètres seront alors appliquées dans le fichier de configuration générique.

Ensuite, le comportement d’Ora2Pg sera déterminé par les options des lignes de commande utilisées.

Type d’export

L’option -t permet de choisir le type d’action lors de l’exécution du script plutôt que d’aller modifier le fichier de configuration. Cette option peut prendre exactement les mêmes valeurs que la variable TYPE, à savoir :

• TABLE: Extrait l’ensemble des tables, avec leurs index et leurs contraintes d’intégrité.
• VIEW: Extrait les vues et leur définition uniquement.
• GRANT: Extrait les rôles compatibles avec PostgreSQL et réaffecte les permissions aux différents objets de la base.
• SEQUENCE: Extrait l’ensemble des séquences et leur dernière valeur connue.
• TABLESPACE: Extrait les noms de tablespaces pour le stockage des tables et des index.
• TRIGGER: Extrait la définition des triggers.
• FUNCTION: Extrait les fonctions.
• PROCEDURE: Extrait les procédures.
• PACKAGE: Extrait les paquets et leur définition (package bodies).
• INSERT: Extrait les données sous forme de requêtes INSERT.
• COPY: Extrait les données sous forme d’instruction COPY.
• PARTITION: Extrait les partitions de type RANGE et LIST, ainsi que les sous-partitions.
• TYPE: Extrait les types définis par l’utilisateur.
• FDW: Exporte les tables sous forme de tables externes avec oracle_fdw.
• MVIEW: Exporte les vues matérialisées.
• QUERY: Tente de convertir automatiquement les requêtes SQL présents dans les tables d’audit d’Oracle (DBA_AUDIT_TRAIL).
• KETTLE: Génére les fichiers modèles XML utilisés par Kettle, un ETL qui dispose d’une version communautaire et dont le nom moderne est Pentaho Data Integration (PDI). Cet export nécessite que les chaînes de connexions Oracle et PostgreSQL soient définies (DSN).
• DBLINK: Génére la définition d’un objet serveur reposant sur l’extension oracle_fdw pour émuler un DBLink Oracle.
• SYNONYM: Exporte les synonymes sous la forme de vues dans un autre schéma.
• DIRECTORY: Exporte la définition des directories Oracle en s’appuyant sur l’extension external_file.
• LOAD: Distribue une liste de requêtes à travers plusieurs connexions à l’instance PostgreSQL.
• TEST: Réalise une analyse des différences entre les bases Oracle et PostgreSQL à l’issue de la migration.
• TEST_COUNT: Réalise un décompte des lignes entre les bases Oracle et PostgreSQL à l’issue de la copie des données.
• TEST_VIEW: Réalise le décompte des lignes retournées par les vues en les bases Oracle et PostgreSQL.
• TEST_DATA: Vérifie une à une les valeurs retournées par les deux systèmes.
• SEQUENCE_VALUES: Exporte les instructions de mise à jour des séquences à leur dernière value connue.
• SHOW_VERSION : Affiche la version de la base Oracle.
• SHOW_SCHEMA : Affiche la liste des schémas disponibles depuis la base Oracle.
• SHOW_TABLE : Affiche la liste des tables disponibles.
• SHOW_COLUMN : Affiche la liste des colonnes de tables disponible ainsi que les transformations qui seront réalisées par Ora2Pg lors de la conversion automatique. Remonte un avertissement si un mot réservé dans PostgreSQL est présent dans le nom des objets Oracle.
• SHOW_ENCODING : Affiche les valeurs NLS_LANG et CLIENT_ENCODING qu’Ora2Pg prévoit d’utiliser ainsi que l’encodage de la base Oracle avec les correspondances possibles avec une connexion client PostgreSQL.
• SHOW_REPORT : Retourne un rapport détaillé du contenu de la base Oracle.

L’auteur d’Ora2Pg a présenté les différents types de test lors de sa conférence « La validation de migration facilitée par Ora2Pg », à la PostgreSQL Session 14 qui s’est tenue à Paris le 17 novembre 2021.

Répertoire de stockage des fichiers

L’option -b va permettre d’utiliser l’arborescence de l’espace de travail créé auparavant pour stocker les fichiers générés dans leur espace de stockage respectif. Elle correspond à la variable OUTPUT_DIR du fichier de configuration.

Nom du fichier de sortie

Le nom des fichiers de sortie est défini à partir de l’option -o correspondant à la directive OUTPUT.

Conversion automatique du code

L’option -p est utilisée pour provoquer la conversion automatique du code SQL et PL/SQL.

Avec l’activation des directives FILE_PER_INDEX, FILE_PER_CONSTRAINT et FILE_PER_FKEYS, la commande d’extraction des définitions de tables, contraintes et index va créer quatre fichiers dans le répertoire de sortie schema/tables :

• table.sql
• CONSTRAINTS_table.sql
• INDEXES_table.sql
• FKEYS_table.sql

Le premier utilise le nom donné par l’option -o et contient les ordres CREATE TABLE .... Le second utilise aussi le nom donné dans l’option -o mais préfixé par le mot CONSTRAINT_ et, pour cause, il contient tous les ordres de création des contraintes : ALTER TABLE "..." ADD CONSTRAINT ....

Le troisième fichier contient toutes les commandes de création des index (CREATE INDEX ...) définies dans Oracle à l’exception des index implicites sur les clés primaires que PostgreSQL génère automatiquement et qui n’ont donc pas besoin d’être exportées.

Le quatrième contient les ordres de création des clés étrangères pour pouvoir être créées facilement après la migration des données.

L’option de conversion de code -p est utilisée ici uniquement pour les index ou contraintes CHECK qui peuvent utiliser des fonctions à convertir.

Les séquences sont, quant à elles, exportées dans le sous-répertoire schema/sequences et le fichier sequences.sql contenant les ordres SQL CREATE SEQUENCE .... Comme les contraintes, les séquences ne doivent être importées qu’à la fin de la migration. Les séquences seront créées avec la bonne valeur de départ après import des données.

Par défaut Ora2Pg supprime toutes les informations sur les tablespaces associés aux objets exportés de la base Oracle. Si vous souhaitez préserver ces informations, notamment pour utiliser des tablespaces différents pour les tables et les index, la directive de configuration USE_TABLESPACE doit être activée. Les tablespaces par défaut d’Oracle (TEMP, USERS et SYSTEM) ne sont pas pris en compte.

REPLACE_TABLES et REPLACE_COLS

Il peut être nécessaire de renommer une table en particulier durant la migration. La directive REPLACE_TABLES autorise de lister sur une ou plusieurs lignes les tables à transformer.

REPLACE_TABLES    ORIG_TB_NAME1:NEW_TB_NAME1
REPLACE_TABLES    ORIG_TB_NAME2:NEW_TB_NAME2

L’un des cas d’usages principaux est l’utilisation d’un mot-clé réservé avec PostgreSQL, comme windows ou array, qui sont acceptés comme noms de tables dans Oracle mais sont interdits avec PostgreSQL. La requête suivante permet de connaître les mots strictement réservés.

select word from pg_get_keywords() where catcode = 'R';

Dans le même ordre d’idée, la direction REPLACE_COLS permet de définir les renommages pour les colonnes d’une ou de plusieurs tables.

INDEXES_SUFFIX et INDEXES_RENAMING

Avec Oracle, les espaces de noms entre les tables et les index sont distincts, cela signifie qu’il est possible qu’une table et un index puissent avoir le même nom. Cette situation n’est pas supportée dans PostgreSQL et Ora2Pg propose de suffixer l’ensemble des index à migrer, à l’aide de la directive INDEXES_SUFFIX. Par défaut, ce comportement est désactivé.

INDEXES_SUFFIX      _idx

Si les noms des index importent peu dans la gestion quotidienne du schéma, il est également possible d’activer le changement automatique des noms des index, en s’inspirant du nommage proposé nativement par PostgreSQL, à savoir tablename_columns_names.

INDEXES_RENAMING    1

DATA_TYPE et MODIFY_TYPE

Par défaut, Ora2Pg transpose automatiquement les types Oracle en équivalents PostgreSQL. Cependant, certains typages peuvent provoquer des comportements anormaux dans la base de données migrées, notamment les erreurs de précisions et d’arrondis sur les types numériques, ou l’absence de fuseau horaire dans le type timestamp without timezone.

Par exemple, la table employees présente une date et des champs décimaux avec des définitions variées.

SQL> DESC employees;
 Name                  Null?    Type
 --------------------- -------- -----------------
 EMPLOYEE_ID           NOT NULL NUMBER(6)
 FIRST_NAME                     VARCHAR2(20)
 LAST_NAME             NOT NULL VARCHAR2(25)
 EMAIL                 NOT NULL VARCHAR2(25)
 PHONE_NUMBER                   VARCHAR2(20)
 HIRE_DATE             NOT NULL DATE
 JOB_ID                NOT NULL VARCHAR2(10)
 SALARY                         NUMBER(8,2)
 COMMISSION_PCT                 NUMBER(2,2)
 MANAGER_ID                     NUMBER(6)
 DEPARTMENT_ID                  NUMBER(4)

Les champs salary, commission_pct et department_id seront respectivement convertis par Ora2Pg en double precision, real et smallint, afin d’optimiser au mieux le stockage des valeurs numériques.

CREATE TABLE employees (
  employee_id integer NOT NULL,
  first_name varchar(20),
  last_name varchar(25) NOT NULL,
  email varchar(25) NOT NULL,
  phone_number varchar(20),
  hire_date timestamp NOT NULL,
  job_id varchar(10) NOT NULL,
  salary double precision,
  commission_pct real,
  manager_id integer,
  department_id smallint
);

Pour assurer le respect des données, il peut être judicieux d’enrichir la directive DATA_TYPE pour transformer les types NUMBER ou DATE dans un type équivalent.

DATA_TYPE     NUMBER(*\,2):decimal,DATE:timestamptz

CREATE TABLE employees (
  employee_id integer NOT NULL,
  first_name varchar(20),
  last_name varchar(25) NOT NULL,
  email varchar(25) NOT NULL,
  phone_number varchar(20),
  hire_date timestamptz NOT NULL,
  job_id varchar(10) NOT NULL,
  salary decimal,
  commission_pct decimal,
  manager_id integer,
  department_id smallint
);

La directive MODIFY_TYPE est similaire mais permet de préciser colonne par colonne les différentes transformations à opérer. Par exemple, pour la table employees exclusivement, la configuration sera la suivante :

MODIFY_TYPE   employees:hire_date:timestamptz
MODIFY_TYPE   employees:salary:decimal
MODIFY_TYPE   employees:commission_pct:decimal

REPLACE_AS_BOOLEAN et BOOLEAN_VALUES

Puisqu’Oracle ne dispose pas d’un type boolean, il est possible d’instruire Ora2Pg pour qu’il réalise les conversions vers une colonne de type bool en assurant la correspondance des valeurs.

REPLACE_AS_BOOLEAN      TABLE1:COL1 TABLE2:COL1

Par défaut, les traductions d’une donnée Oracle vers un booléen PostgreSQL est assurée par la directive BOOLEAN_VALUES, qu’il est possible d’étendre selon les jeux de données à migrer.

BOOLEAN_VALUES          yes:no y:n 1:0 true:false enabled:disabled

Le premier export (type GRANT) va exporter tous les rôles et leurs droits sur les objets sous forme d’ordres SQL CREATE ROLE ... et GRANT ... ON ... dans le fichier schema/users/users.sql.

Le deuxième provoque la génération des ordres de création des espaces de stockage des tables ou index, CREATE TABLESPACE ... et les ordres de déplacement des objets dans ces espaces, ALTER ... SET TABLESPACE .... Les définitions sont enregistrées dans le fichier schema/tablespaces/tablespaces.sql. Si la directive FILE_PER_INDEX est activée alors les ordres concernant les index le seront dans un fichier séparé schema/tablespaces/INDEXES_tablespaces.sql.

Le troisième type d’export va exporter tous les types définis par les utilisateurs (CREATE TYPE ...) dans le fichier schema/types/types.sql. La conversion de certains types utilisateurs Oracle nécessite une réécriture manuelle pour être compatible avec PostgreSQL. Ce sont les types définis par CREATE TYPE ... AS TABLE OF ... qui nécessitent l’écriture de fonctions définissant le comportement du type lors de la lecture et de l’écriture dans ce type. Il en va de même avec les types objets (CREATE TYPE ... AS OBJECT ... TYPE BODY). Les fonctions doivent être converties à la syntaxe PostgreSQL. Cette conversion est réalisée grâce à l’emploi de l’option -p (équivalent à l’activation de la variable PLSQL_PGSQL).

L’étape suivante de la migration du schéma consiste à exporter tous les autres types d’objets : les vues, les triggers, les fonctions et procédures stockées (les routines). Tous ces types d’export nécessitent l’emploi de l’option -p pour provoquer la conversion automatique du code SQL et PL/SQL.

L’import de ce type d’objet sera évoqué en détail dans le chapitre dédié à la migration du code PL/SQL.

Dans la mesure où la conversion du code SQL et PL/SQL n’est pas complète, voire imparfaite, il est recommandé d’extraire le code brut pour pouvoir le comparer avec le code converti par Ora2Pg en cas de problème.

L’extraction du code brut d’Oracle se fait en n’utilisant pas l’option -p lors de l’exécution du script et en désactivant l’option PLSQL_PGSQL dans le fichier de configuration.

Depuis PostgreSQL v10, il est possible de déclarer une table comme étant partitionnée et de déclarer des partitions. La spécification d’une table partitionnée consiste en une méthode de partitionnement et une liste de colonnes ou expressions à utiliser comme la clé de partitionnement.

Toutes les lignes insérées dans la table partitionnée seront alors redirigées vers une des partitions en se basant sur la valeur de la clé de partitionnement. Les méthodes de partitionnement supportées par Ora2Pg sont le partitionnement par intervalles (RANGE), par liste (LIST) et par hachage (HASH).

Les partitions peuvent elles-mêmes être définies comme des tables partitionnées, en utilisant le sous-partitionnement. Les partitions peuvent avoir leurs propres index, contraintes et valeurs par défaut, différents de ceux des autres partitions.

Le paramètre DISABLE_PARTITION permet de ne pas reprendre le partitionnement d’un table alors que le paramètre PG_SUPPORTS_PARTITION désactive la conversion en partitionnement déclaratif au profit du partitionnement par héritage, soit l’ancien comportement avant PostgreSQL v10.

Voici deux exemples de conversion avec le partitionnement déclaratif réalisées avec Ora2Pg :

• Partition par range

CREATE TABLE sales_range
(
    salesman_id  NUMBER(5),
    salesman_name VARCHAR2(30),
    sales_amount  NUMBER(10),
    sales_date    DATE
)
PARTITION BY RANGE(sales_date)
(
  PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
  PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
);

Deviendra :

CREATE TABLE sales_range
(
    salesman_id  integer,
    salesman_name varchar(30),
    sales_amount  bigint,
    sales_date    timestamp
) PARTITION BY RANGE (sales_date);

CREATE TABLE sales_jan2000 PARTITION OF sales_range
  FOR VALUES FROM ('2000-01-01') TO ('2000-01-02');

CREATE TABLE sales_feb2000 PARTITION OF sales_range
  FOR VALUES FROM ('2000-01-02') TO ('2000-01-03');

• Partition par liste

CREATE TABLE sales_list
(
    salesman_id  NUMBER(5),
    salesman_name VARCHAR2(30),
    sales_state   VARCHAR2(20),
    sales_amount  NUMBER(10),
    sales_date    DATE
)
PARTITION BY LIST(sales_state)
(
    PARTITION sales_west VALUES('California', 'Hawaii'),
    PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
    PARTITION sales_other VALUES (DEFAULT)
);

Deviendra :

CREATE TABLE sales_list
(
    salesman_id  integer,
    salesman_name varchar(30),
    sales_amount  bigint,
    sales_date    timestamp
) PARTITION BY LIST (sales_state)

CREATE TABLE sales_west PARTITION OF sales_list
    FOR VALUES IN ('California', 'Hawaii');

CREATE TABLE sales_east PARTITION OF sales_list
    FOR VALUES IN ('New York', 'Virginia', 'Florida');

CREATE TABLE sales_other PARTITION OF sales_list
    FOR VALUES DEFAULT;
);

Le but d’une vue matérialisée est de stocker physiquement le résultat de l’exécution d’une vue et d’utiliser par la suite ce stockage plutôt que le résultat de l’exécution de la requête. Il est possible de créer des index sur cette vue matérialisée. Elle est mise à jour soit à la demande soit au fil de l’eau.

Les vues matérialisées ne sont supportées qu’à partir de la version 9.3 pour PostgreSQL. Elles ne supportent pas toutes les fonctionnalités qu’offre Oracle : pas de mise à jour au fil de l’eau, pas de rafraîchissement incrémental à l’aide de journaux de vue matérialisée (MATERIALIZED VIEW LOG sous Oracle), pas de réécriture de requête (query rewrite).

CREATE MATERIALIZED VIEW emp_data_mview AS
SELECT EMPLOYEES.EMPLOYEE_ID EMPLOYEE_ID,EMPLOYEES.FIRST_NAME FIRST_NAME,
       EMPLOYEES.LAST_NAME LAST_NAME, EMPLOYEES.EMAIL
       EMAIL,EMPLOYEES.PHONE_NUMBER PHONE_NUMBER,EMPLOYEES.HIRE_DATE
       HIRE_DATE,EMPLOYEES.JOB_ID JOB_ID, EMPLOYEES.SALARY
       SALARY,EMPLOYEES.COMMISSION_PCT COMMISSION_PCT,EMPLOYEES.MANAGER_ID
       MANAGER_ID, EMPLOYEES.DEPARTMENT_ID DEPARTMENT_ID
FROM EMPLOYEES EMPLOYEES;

et pour le rafraîchissement, il suffira d’utiliser la commande SQL :

REFRESH MATERIALIZED VIEW emp_data_mview;

Il est possible de lever le verrou exclusif de l’opération de rafraîchissement à l’aide de l’option CONCURRENTLY. Pour ce faire, un index unique est requis sur l’une des colonnes de la vue matérialisée qui respecte la contrainte d’unicité.

CREATE UNIQUE INDEX ON emp_data_mview(employee_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY emp_data_mview;

Certaines fonctionnalités que propose Oracle (ex : FAST REFRESH à l’aide des MATERIALIZED VIEW LOG) ne sont pas encore présentes dans les versions actuelles de PostgreSQL. Si une mise à jour au fil de l’eau est requise, il faudra forcément passer par des triggers.

CREATE FUNCTION fct_refresh_emp_data_mview()
RETURNS trigger LANGUAGE plpgsql
AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY emp_data_mview;
  RETURN new;
END
$$;

CREATE TRIGGER trg_emp_data_mview_on_insert
AFTER INSERT ON employees FOR EACH ROW
  EXECUTE PROCEDURE fct_refresh_emp_data_mview();

Pour aller plus loin :

• Postgres 9.4 feature highlight - REFRESH CONCURRENTLY a materialized view
• Conférence pgDay Paris 2019 : Don’t forget materialized view (vidéo)

Un synonyme n’est ni plus ni moins qu’un alias vers un objet d’une base de données Oracle. Ils sont utilisés pour donner les droits d’accès à un objet dans un autre schéma ou dans une base distante auquel l’utilisateur n’aurait normalement pas accès.

Voici la syntaxe de création d’un synonyme sous Oracle :

CREATE SYNONYM synonym_name FOR object_name [@ dblink];

Les synonymes n’existent pas sous PostgreSQL, il y a deux méthodes pour les émuler.

Modification du search_path

L’objet est naturellement caché à l’utilisateur car il n’appartient pas à un schéma de son search_path par défaut et lorsqu’on veut qu’il y ait accès, on modifie le search_path. Par exemple :

SET search_path TO other_schema,...;

Cette méthode peut s’avérer assez fastidieuse à mettre en place au niveau applicatif mais évite la création de vues.

Utilisation de vues

L’autre méthode consiste donc à utiliser des vues. C’est ce que générera Ora2Pg lors de l’export des synonymes.

ora2pg -t SYNONYM -o synonyms.sql -b schema/synonyms -c config/ora2pg.conf

Par exemple, un synonyme créé sous Oracle avec l’ordre :

CREATE SYNONYM emp_table FOR hr.employees;

sera exporté par Ora2Pg de la façon suivante :

CREATE VIEW public.emp_table AS SELECT * FROM hr.employees;
ALTER VIEW public.emp_table OWNER TO hr;
GRANT ALL ON public.emp_table TO PUBLIC;

La vue public.emp_table étant la propriété de l’utilisateur HR, elle permet la consultation de la table dans le schéma HR.

Si le synonyme pointe sur une table distante par un dblink, Ora2Pg créera la vue telle que précédemment mais ajoutera un message en commentaire pour signifier que la table distante doit être créée via un Foreign Data Wrapper ou un dblink. Par exemple :

-- You need to create foreign table hr.employees using foreign server:
-- oradblink1 (see DBLINK and FDW export type)
CREATE VIEW public.emp_table AS SELECT * FROM hr.employees;
ALTER VIEW public.emp_table OWNER TO hr;
GRANT ALL ON public.emp_table TO PUBLIC;

Les DIRECTORY et tables externes n’existent pas dans PostgreSQL tels que définis dans Oracle. Il est possible d’émuler les accès à des tables externes en utilisant le Foreign Data Wrapper file_fdw mais uniquement en lecture. Ces tables doivent respecter le format CSV de COPY. Ora2Pg exporte par défaut toute table externe en une table distante basée sur l’extension file_fdw. Si vous voulez exporter ces tables comme des tables normales, il suffit de désactiver la directive de configuration EXTERNAL_TO_FDW en lui donnant la valeur 0.

Voici un exemple de table externe sous Oracle :

CREATE OR REPLACE DIRECTORY ext_directory AS '/tmp/';

CREATE TABLE ext_table (
id       NUMBER(6),
nom      VARCHAR2(20),
prenom   VARCHAR2(20),
activite CHAR(1))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY ext_directory
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (id, nom, prenom, activite))
    LOCATION ('person.dat')
  )
PARALLEL
REJECT LIMIT 0
NOMONITORING;

Ora2Pg convertit le DIRECTORY en serveur FDW en utilisant l’extension file_fdw.

CREATE EXTENSION file_fdw;
CREATE SERVER ext_directory FOREIGN DATA WRAPPER file_fdw;

Puis, il crée la table comme une table distante rattachée au serveur préalablement défini.

CREATE FOREIGN TABLE ext_table (
        id integer,
        nom varchar(20),
        prenom varchar(20),
        activite char(1)
) SERVER ext_directory OPTIONS(filename '/tmp/person.dat',
                               format 'csv',
                               delimiter ',');

Les DATABASE LINK sont des objets Oracle permettant l’accès à des objets de bases de données distantes. Ils sont créés de la manière suivante :

CREATE PUBLIC DATABASE LINK remote_service_name CONNECT TO scott
IDENTIFIED BY tiger USING 'remote_db_name';

et s’utilisent ensuite de la façon suivante :

SELECT * FROM employees@remote_service_name;

Ce type d’objet n’existe pas nativement dans PostgreSQL et nécessite l’utilisation d’une extension Foreign Data Wrapper en fonction du type du SGBD distant.

Ora2Pg exportera ces DATABASE LINK comme des bases Oracle distantes en utilisant l’extension Foreign Data Wrapper oracle_fdw par défaut. Il est tout à fait possible de changer l’extension si la base distante est une base PostgreSQL. Voici un exemple d’export par Ora2Pg :

CREATE SERVER remote_service_name FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver 'remote_db_name');

CREATE USER MAPPING FOR current_user SERVER remote_service_name
OPTIONS (user 'scott', password 'tiger');

Pour que le lien vers la base distante puisse être utilisé, il est nécessaire de créer les tables distantes dans la base locale :

ora2pg -c ora2pg.conf -t FDW -a EMPLOYEES

et le résultat de la commande ora2pg :

CREATE FOREIGN TABLE employees_fdw (...) SERVER remote_service_name
OPTIONS (schema 'HR', table 'EMPLOYEES');

Maintenant la table peut être utilisée directement au niveau SQL comme s’il s’agissait d’une table locale :

SELECT * FROM employees_fdw;

Cela fonctionne en lecture et écriture depuis PostgreSQL 9.3. Le Foreign Data Wrapper oracle_fdw peut être obtenu sur le site des extensions PostgreSQL pgxn.org

Le type BFILE permet de stocker des données non structurées dans des fichiers externes en dehors de la base de données (fichiers image, documents pdf, etc.). Le type DIRECTORY permet lui de définir des chemins sur le système de fichier qui pourront être utilisés pour le stockage de ces données externes.

Il n’existe pas de types équivalents natifs sous PostgreSQL.

Un BFILE est une colonne qui stocke un nom de fichier qui pointe vers un fichier externe contenant les données et le nom de l’identifiant du répertoire base dans lequel ce fichier est stocké : (DIRECTORY, FILENAME)

Par défaut Ora2Pg transforme le type BFILE en type bytea en chargeant le contenu du fichier directement en base sous forme d’objet binaire.

CREATE TABLE bfile_test (id bigint, bfilecol bytea);
COPY bfile_test (id,bfilecol) FROM STDIN;
1
1234,ALBERT,GRANT,21\\0121235,ALFRED,BLUEOS,26\\0121236,BERNY,JOL
YSE,34\\012
\.

Il est possible de demander à Ora2Pg de ne pas importer les données dans le champ cible, mais seulement le chemin complet (répertoire base + nom de fichier) vers le fichier. Ceci se fait en modifiant le type PostgreSQL associé au type Oracle dans la directive de configuration DATA_TYPE : ...,BFILE:TEXT,...

Il existe aussi une extension PostgreSQL nommée external_file qui permet d’émuler les DIRECTORY et BFILE d’Oracle. Si le type PostgreSQL associé au type Oracle dans la directive de configuration DATA_TYPE est positionné à EFILE (...,BFILE:EFILE,...), Ora2Pg fera les conversions nécessaires pour utiliser ce type.

Voici ce que Ora2Pg générera comme ordre SQL lorsque qu’un champ de type BFILE doit être converti en type EFILE :

INSERT INTO external_file.directories (directory_name, directory_path)
    VALUES ('EXT_DIR', '/data/ext/');
INSERT INTO external_file.directory_roles (directory_name, directory_role,
    directory_read, directory_write) VALUES ('EXT_DIR', 'hr', true, false);
INSERT INTO external_file.directories (directory_name, directory_path)
    VALUES ('SCOTT_DIR', '/usr/home/scott/');
INSERT INTO external_file.directory_roles(directory_name, directory_role,
    directory_read, directory_write) VALUES ('SCOTT_DIR', 'hr', true, true);

L’objet DIRECTORY est défini dans la table external_file.directories créée par l’extension et les privilèges d’accès à ces répertoires stockés dans une autre table, external_file.directory_roles.

Le type EFILE contient lui exactement la même chose que le type BFILE, à savoir (directory_name, file_name).

L’extension pg_trgm apporte des classes d’opérateur pour les index GiST et GIN permettant de créer un index sur une colonne texte pour les recherches rapides par similarités. Ces index permettent notamment la recherche par trigrammes pour les requêtes à base de LIKE, ILIKE, ~ et ~*.

Exemple :

CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';
SELECT * FROM test_trgm WHERE t ~ '(foo|bar)';

Ce type d’index peut correspondre aux index Oracle CTXCAT indexant des textes de petites tailles. Il faut toutefois réécrire les requêtes utilisant l’opérateur CATSEARCH en requêtes utilisant LIKE ou ILIKE.

L’indexation FTS est un des cas les plus fréquents d’utilisation non-relationnelle d’une base de données : les utilisateurs ont souvent besoin de pouvoir rechercher une information qu’ils ne connaissent pas parfaitement, d’une façon floue :

• Recherche d’un produit/article par rapport à sa description
• Recherche dans le contenu de livres/documents

PostgreSQL doit donc permettre de rechercher de façon efficace dans un champ texte. L’avantage de cette solution est d’être intégrée au SGBD. Le moteur de recherche est donc toujours parfaitement à jour avec le contenu de la base, puisqu’il est intégré avec le reste des transactions.

Voici un exemple succinct de mise en place de FTS :

• Création d’une configuration de dictionnaire dédiée (français sans accent) :

CREATE TEXT SEARCH CONFIGURATION depeches (COPY= french);
ALTER TEXT SEARCH CONFIGURATION depeches ALTER MAPPING
FOR hword, hword_part, word WITH unaccent,french_stem;

• Ajout d’une colonne vectorisée à la table depeches, afin de maximiser les performances de recherche :

ALTER TABLE depeche ADD vect_depeche tsvector;

• Création du contenu de vecteur pour les données de la table depeche :

UPDATE depeche set vect_depeche = (setweight(
    to_tsvector('depeches',coalesce(titre,'')), 'A'
    ) || setweight(
    to_tsvector('depeches',coalesce(texte,'')), 'C')
);

• Création de la fonction qui sera associée au trigger :

CREATE FUNCTION to_vectdepeche( )
 RETURNS trigger
 LANGUAGE plpgsql
 -- common options:  IMMUTABLE  STABLE  STRICT  SECURITY DEFINER
AS $function$
BEGIN
  NEW.vect_depeche := setweight(to_tsvector('depeches',coalesce(NEW.titre,''))
                        , 'A') ||
                      setweight(to_tsvector('depeches',coalesce(NEW.texte,''))
                        , 'C');
  return NEW;
END
$function$
;

Le rôle de cette fonction est d’automatiquement mettre à jour le champ vect_depeche par rapport à ce qui aura été modifié dans l’enregistrement. On donne aussi des poids différents aux zones titre et texte du document, pour qu’on puisse éventuellement utiliser cette information pour trier les enregistrements par pertinence lors des interrogations.

• Création du trigger :

CREATE TRIGGER trg_depeche before INSERT OR update ON depeche
FOR EACH ROW EXECUTE PROCEDURE to_vectdepeche();

Et ce trigger appelle la fonction définie précédemment à chaque insertion ou modification d’enregistrement dans la table.

NB : à partir de la v12, une colonne générée est préférable à l’alimentation par trigger.

• Création de l’index associé au vecteur :

 CREATE INDEX idx_gin_texte ON depeche USING gin(vect_depeche);

L’index permet bien sûr une recherche plus rapide.

• Collecte des statistiques sur la table :

ANALYZE depeche ;

• Utilisation :

SELECT titre,texte FROM depeche
    WHERE vect_depeche @@ to_tsquery('depeches','varicelle');
SELECT titre,texte FROM depeche
    WHERE vect_depeche @@ to_tsquery('depeches','varicelle & médecin');

La recherche plein texte PostgreSQL consiste en la mise en relation entre un vecteur (la représentation normalisée du texte à indexer) et d’une tsquery, c’est-à-dire une chaîne représentant la recherche à effectuer. Ici par exemple, la première requête recherche tous les articles mentionnant « varicelle », la seconde tous ceux parlant de « varicelle » et de « médecin ». Nous obtiendrons bien sûr aussi les articles parlant de médecine, « médecine » ayant le même radical que « médecin » et étant donc automatiquement classé comme faisant partie de la même famille.

La recherche propose bien sûr d’autres opérateurs que & : | pour « ou », ! pour « non ». On peut effectuer des recherches de radicaux, etc. L’ensemble des opérations possibles est détaillée ici : https://docs.postgresql.fr/current/textsearch-controls.html.

On peut trier par pertinence :

SELECT titre,texte
FROM depeche
WHERE vect_depeche @@ to_tsquery('depeches','varicelle & médecin')
ORDER BY ts_rank_cd(vect_depeche, to_tsquery('depeches','varicelle & médecin'));

Ou, écrit autrement (pour éviter d’écrire deux fois to_tsquery) :

SELECT titre,ts_rank_cd(vect_depeche,query) AS rank
FROM depeche, to_tsquery('depeches','varicelle & médecin') query
WHERE query@@vect_depeche
ORDER BY rank DESC

Ce type d’indexation plein texte correspond à la recherche de texte Oracle basée sur des index de type CONTEXT. Il sera aussi nécessaire de réécrire les requêtes Oracle utilisant l’opérateur CONTAINS avec l’operateur @@ de PostgreSQL.

La première chose à faire avant de commencer à migrer réellement la base Oracle dans une base PostgreSQL est de créer le propriétaire de la base de données, toutes les opérations se feront ensuite sous cet utilisateur. Voici comment créer le propriétaire de la base :

$ createuser --no-superuser --no-createrole --no-createdb myuser

On procède ensuite à la création de la base elle-même :

$ createdb -E UTF-8 --owner myuser mydb

Si vous avez décidé d’exporter le schéma Oracle avec la variable EXPORT_SCHEMA activée, il faut créer le schéma sous PostgreSQL :

$ psql -U myuser mydb -c "CREATE SCHEMA myschema;"

Pour faciliter ensuite l’utilisation du schéma, il est possible d’affecter un schéma par défaut à un utilisateur de sorte qu’à chaque fois qu’il se connecte à la base, ce sont les schémas donnés qui seront utilisés :

$ psql -U myuser mydb -c \
    "ALTER ROLE miguser SET search_path TO myschema,public;"

Si des tablespaces doivent être importés, les chemins doivent exister sur le système. Il faut donc s’assurer qu’ils sont présents et que PostgreSQL pourra écrire dans ces répertoires.

La base étant créée, il ne reste plus qu’à charger les différents objets en commençant par les tables, puis les partitions, s’il y en a, les vues et pour finir les tablespaces pour déplacer les objets dans leurs espaces de stockage respectif (l’export des tablespaces contient non seulement les tablespaces, mais aussi les ALTER TABLE et ALTER INDEX déplaçant les tables et index dans leur tablespace de destination).

Les objets susceptibles de gêner l’import des données, soit en provoquant des erreurs comme les contraintes, soit en ralentissant leur chargement comme les index, sont laissés de côté et ne seront importés qu’à la fin de la migration. Dans ce cas, il faudra lancer deux fois le script tablespaces.sql, une fois après le chargement des tables, une fois après le chargement des index, et ignorer les erreurs.

Lors du chargement du schéma, il y a normalement assez peu d’erreurs. Du coup, elles peuvent facilement passer inaperçues. Il est donc important de bien scruter les journaux applicatifs au fur et à mesure des commandes d’import pour détecter ces erreurs.

Les types d’erreur pouvant survenir sont souvent des problèmes d’encodage dans les valeurs des contraintes CHECK et dans les index. Dans ce cas, il faut utiliser les ordres :

SET client_encoding TO autre_encodage;

Avec aussi la possibilité, pour les contraintes et index, de trouver du code SQL utilisant des fonctions qui ne sont pas convertibles automatiquement par Ora2Pg.

CREATE INDEX idx_userage ON players ( to_number(to_char('1974', user_age)) );

ALTER TABLE "actifs" ADD CONSTRAINT CHECK (WYEAR between 0 and 42);

Ora2Pg exporte les champs Oracle de type NUMBER sans précision en bigint. Ce n’est pas forcément le bon choix notamment lorsque ce champ contient des valeurs avec décimale. Une erreur va se produire lors de l’import des données. Il sera nécessaire alors de modifier le type de la colonne à posteriori.

On trouve de temps en temps des objets comportant des accents, sans compter qu’il faudra que le nom de l’objet soit toujours placé entre guillemets doubles. Il faudra aussi utiliser le bon encodage lors de la création et des appels à l’objet. Ceci génère énormément d’erreurs et il est fortement conseillé de les supprimer.

Ora2Pg ne détecte pas les noms d’objets correspondants à des mots réservés PostgreSQL. Il vous faudra, dans ce cas, modifier manuellement le code SQL en les incluant entre guillemets doubles.

CREATE INDEX idx_userage ON user WHERE age > 16;

CREATE INDEX idx_userage ON "user" WHERE age > 16;

Oracle autorise certaines conversions implicites qui ne sont plus autorisées dans PostgreSQL depuis la version 8.3 (principalement les conversions implicites entre numériques et chaînes de caractères). Dans l’exemple, WYEAR est une colonne de type VARCHAR dans Oracle et exportée comme telle dans PostgreSQL. Il faudra donc forcer sa transformation en integer pour que la contrainte fonctionne, sinon vous obtiendrez une erreur du genre :

ERROR:  operator does not exist: character varying >= integer

Nous allons aborder ici les différentes étapes pour migrer de façon optimale les données :

• comment exporter les données ?
• comment importer les données ?
• quels problèmes peuvent être rencontrés lors de l’import des données ?
• application des ordres DDL de création des séquences, contraintes, triggers et index ?
• comment accélérer l’import des données dans PostgreSQL ?
• comment n’extraire qu’une partie des données ?

Il faut privilégier le premier type d’export à base d’instructions COPY plutôt que le second à base d’ordre INSERT. Il y a deux raisons à cela : l’import sera beaucoup plus rapide avec COPY et vous aurez potentiellement moins d’erreurs si vos données contiennent des caractères d’échappement (\).

Si l’option FILE_PER_TABLE est activée, Ora2Pg va créer un fichier de chargement de données par table exportée et le fichier tables.sql ne sera qu’un fichier de chargement global de ces fichiers à base d’instruction psql : \i nom_fichier.sql.

Si les directives de configuration DISABLE_TRIGGERS et DROP_FKEY ont été activées, le fichier global contient aussi les appels de désactivation/activation des triggers et de suppression/création des contraintes.

L’avantage d’avoir des fichiers de données à disposition est qu’ils peuvent être rechargés manuellement plusieurs fois en cas de problème jusqu’à trouver le correctif à apporter.

Dans la mesure où l’export de données dans des fichiers peut occuper un volume disque très important, Ora2Pg vous donne la possibilité de compresser vos données soit avec gzip soit avec bzip2. Pour le premier type de compression, il faut installer au préalable le module Perl Compress::Zlib et donner l’extension .gz au fichier de sortie :

ora2pg -t COPY -o datas.sql.gz -b data/ -c config/ora2pg.conf

Pour utiliser la compression avec bzip2, il suffit que le programme bzip2 soit dans le PATH et il faut donner l’extension .bz2 au fichier de sortie :

ora2pg -t COPY -o datas.sql.bz2 -b data/ -c config/ora2pg.conf

La lenteur de l’export des champs de type LOB dans des champs bytea (qui est le type correspondant sous PostgreSQL) s’explique par la taille habituellement élevée de ces données et la nécessité d’échapper l’intégralité des données.

Si la volumétrie de ce type de données est très importante, il est préférable d’exclure temporairement de l’export les tables possédant des champs de ce type en les ajoutant à la directive EXCLUDE. À partir de là, une fois le chargement des données des autres tables réalisé, il suffit de déplacer le nom de ces tables avec des champs LOB dans la directive ALLOW pour que l’export des données se fasse uniquement à partir de ces tables.

Pour accélérer l’échappement des données bytea, il faut activer l’utilisation du parallélisme. Cela permet en général d’aller deux à trois fois plus vite. Pour cela, il faut utiliser l’option -j en ligne de commande ou la variable JOBS du fichier de configuration. La valeur est le nombre de cœurs CPU que l’on veut utiliser.

Lorsque les options de parallélisation sont activées, il est important de s’assurer que la valeur de DATA_LIMIT corresponde à la vitesse moyenne maximal d’export d’un simple processus. Par exemple, si Ora2Pg exporte globalement les données à une vitesse moyenne de 5000 tuples/s, c’est très certainement la valeur à donner :

DATA_LIMIT     5000

Si c’est plutôt 20 000, alors DATA_LIMIT devra avoir cette valeur. Ceci vous permettra d’être sûr de tirer le meilleur parti de la parallélisation. Une valeur excessive par contre peut conduire à des dépassements de ressources, une valeur trop faible forcera Ora2Pg à créer des processus inutilement.

CONVERT_SRID

Oracle utilise son propre système spatial de référence SRID (Spatial Reference System Identifier), la norme de fait est maintenant l’ESPG (European Petroleum Survey Group). Oracle fournit la fonction sdo_cs.map_oracle_srid_to_epsg() permettant de le convertir dans cette norme lorsque c’est possible. Si la directive CONVERT_SRID est activée, la conversion sera effectuée.

Cette fonction retourne souvent NULL. Dans ce cas, Ora2Pg renvoit la valeur 8307 comme SRID par défaut ou, si CONVERT_SRID est activée, 4326 converti en ESPG. Il est possible de changer cette valeur par défaut en donnant la valeur du SRID à utiliser à la directive CONVERT_SRID. À noter que dans ce cas, DEFAULT_SRID ne sera pas utilisé.

DEFAULT_SRID

La directive DEFAULT_SRID permet de changer la valeur par défaut du SRID EPSG à utiliser si la valeur retournée est nulle. Elle vaut 4326 par défaut.

GEOMETRY_EXTRACT_TYPE

Cette directive permet d’informer Ora2Pg sur la méthode à utiliser pour extraire les données. Il existe trois possibilités :

• WKT
• WKB
• INTERNAL

La valeur WKT ordonne à Ora2Pg d’utiliser la fonction Oracle SDO_UTIL.TO_WKTGEOMETRY() pour extraire les données. Ora2Pg prend alors la représentation textuelle de la donnée géométrique renvoyée par Oracle sans transformation autre que l’ajout du SRID.

La valeur WKB ordonne à Ora2Pg d’utiliser la fonction Oracle SDO_UTIL.TO_WKBGEOMETRY() pour extraire les données. Ora2Pg prend alors la représentation binaire de la donnée géométrique renvoyée par Oracle la convertit en hexadécimal et ajoute le SRID.

L’utilisation de ces fonctions est intéressante pour obtenir les géométries telle que les voit Oracle ; le seul problème est qu’elles génèrent souvent des erreurs, sont incapables d’extraire des géométries en 3D et surtout provoquent des OOM (Out Of Memory) lorsque il y a un grand nombre de géométries.

Pour palier à ce problème, Ora2Pg embarque sa propre librairie Pure Perl, Ora2Pg::GEOM, permettant d’extraire les données géométriques au format WKT de manière plus rapide et surtout sans erreur. Pour utiliser cette méthode, il faut donner la valeur INTERNAL à la directive GEOMETRY_EXTRACT_TYPE.

La valeur par défaut est INTERNAL.

L’import des fichiers de données se fait simplement avec l’utilisation de la commande psql en spécifiant l’utilisateur (myuser), la base de données (mydb) et le fichier à charger (option -f).

Si le fichier de données est compressé, il est nécessaire d’utiliser le programme de décompression adéquat et de renvoyer la sortie vers la commande psql pour permettre le chargement des données au fil de la décompression.

L’import direct des données dans la base PostgreSQL n’est activé que si la variable PG_DSN est définie. Dans ce cas, le chargement se fait directement lors de l’export des données sans passer par des fichiers intermédiaires.

Une fois que les données sont chargées avec succès, il est temps de créer les contraintes, index et triggers qui avaient été laissés de côté lors de la création du schéma. Ces créations se font aussi à l’aide de la commande psql.

Il est possible que l’import de certains codes, notamment les triggers, nécessitent la présence de certaines fonctions. Dans ce cas, il faudra les intégrer en parallèle.

La création des contraintes et des index est une phase qui très souvent dure presque aussi longtemps que le chargement des données, voire plus longtemps en fonction du nombre.

Depuis la version 16.0 d’Ora2Pg, l’action LOAD permet de donner un fichier d’ordre SQL en entrée (option -i) et de distribuer sur plusieurs processeurs ces requêtes SQL à l’aide de l’option -j N d’Ora2Pg.

Il suffit dans ce cas de lui donner en entrée les fichiers relatifs à la création des contraintes et des index pour pouvoir les charger beaucoup plus rapidement.

Si le type d’export INSERT a été choisi, il arrive très souvent que cela conduise à des erreurs de caractères invalides lors de l’insertion, car le caractère backslash n’est pas échappé si STANDARD_CONFORMING_STRING est activé, ce qui correspond au standard SQL. Dans Ora2Pg, le même comportement survient. De ce fait, ils doivent être activés ou désactivés en même temps dans les deux configurations. Le meilleur moyen de corriger ce problème est d’utiliser le type d’export recommandé pour les données, c’est-à-dire COPY.

Si vous n’avez pas défini correctement les variables NLS_LANG et CLIENT_ENCODING, vous aurez aussi des erreurs de caractères invalides. Il vous faudra alors trouver les bonnes valeurs selon la méthode indiquée dans les chapitres précédents. Malgré une définition correcte de ces variables, il se peut que vous ayez encore des problèmes d’encodage, et même au sein d’une même table : certains enregistrements ne passeront pas par COPY. Il semble qu’Oracle soit très permissif sur les caractères qu’il est possible d’inclure dans un même jeu de caractères.

Pour l’essentiel, ces problèmes sont résolus en forçant toutes les communications à utiliser l’encodage UNICODE, ce qu’Ora2Pg fait par défaut.

Au besoin, chaque bloc d’import de données est précédé d’un appel à

SET client_encoding TO '...';

la valeur étant celle définie dans la variable CLIENT_ENCODING du fichier de configuration ora2pg.conf. Vous pouvez donc ajuster le jeu de caractères à utiliser au niveau de PostgreSQL au plus près des données.

Les erreurs de type numérique apparaissent en raison de la conversion du type Oracle NUMBER sans précision, converti par défaut vers le type indiqué par la variable DEFAULT_NUMERIC, c’est-à-dire bigint. Comme le type NUMBER permet d’inclure aussi bien des entiers que des décimaux, une erreur va inévitablement se produire si des décimaux se trouvent dans les données importées.

Pour résoudre ce problème, il faut évaluer la quantité de champs concernés. Si cela ne concerne que peu de champs et qu’il est possible d’avoir une valeur décimale pour ces champs, le mieux est de changer le type directement :

ALTER TABLE employees ALTER COLUMN real_age TYPE real;

Si par contre le problème se pose de manière quasi systématique, il est alors préférable de modifier le type défini dans DEFAULT_NUMERIC et de recommencer l’import complet.

Lors de l’export des LOB, si vous n’avez pas activé la directive NO_LOB_LOCATOR, il se peut que vous rencontriez l’erreur Oracle :

ORA-24345: A Truncation or null fetch error occurred (DBD SUCCESS_WITH_INFO:
    OCIStmtFetch, LongReadLen too small and/or LongTruncOk not set)

La solution est d’augmenter la valeur du paramètre LONGREADLEN, par défaut 1 Mo, à la taille du plus grand enregistrement de la colonne. Vous avez aussi la possibilité de tronquer les données en activant LONGTRUNCOK, ce qui ne remontera plus d’erreur mais bien évidement tronquera certaines données dont la taille dépasse la valeur de LONGREADLEN. Pour plus d’explication, voir le chapitre Configuration liée aux LOB.

La méthode la plus simple pour gagner en performances est d’utiliser la méthode COPY et de ne pas passer par des fichiers intermédiaires pour importer ces données. Pour envoyer directement les données extraites de la base Oracle vers la base PostgreSQL, il suffit de définir les paramètres de connexion à la base PostgreSQL dans le fichier de configuration ora2pg.conf.

COPY ou INSERT

Préférez toujours l’import des données à l’aide de l’ordre COPY plutôt qu’à base d’INSERT. Ce dernier est beaucoup trop lent pour les gros volumes de données. Lorsque l’import direct dans PostgreSQL est utilisé, Ora2Pg va utiliser une requête préparée et passer les valeurs de chaque ligne en paramètre, mais même avec cette méthode, le chargement avec l’instruction COPY reste le plus performant.

PG_DSN

Il s’agit de l’équivalent pour PostgreSQL de l’ORACLE_DNS pour Oracle dans le fichier de configuration d’Ora2Pg.

On détermine donc ici la chaîne de connexion à PostgreSQL, en particulier :

• le connecteur DBI à utiliser ;
• le nom de la base de données PostgreSQL, dbname= ;
• le nom du serveur PostgreSQL à utiliser, host= ;
• et le port sur lequel le serveur PostgreSQL écoute, port=.

Par exemple, pour la base xe se trouvant sur le serveur postgresql_server:5432 :

PG_DSN dbi:Pg:dbname=xe;host=postgresql_server;port=5432

L’utilisation de cette chaîne de connexion nécessite l’installation du module Perl DBD::Pg et donc des bibliothèques PostgreSQL.

PG_USER

Il détermine le nom de l’utilisateur PostgreSQL qui sera utilisé pour se connecter à la base PostgreSQL désignée par le paramètre PG_DSN.

Exemple, pour l’utilisateur prod :

PG_USER prod

PG_PWD

Il détermine le mot de passe de l’utilisateur PostgreSQL désigné par PG_USER pour se connecter sur la base PostgreSQL désignée par PG_DSN.

Par exemple, si le mot de passe est « secret » :

PG_PWD secret

DATA_LIMIT

Par défaut, lorsqu’on demande à Ora2Pg d’extraire les données, il récupère les données par bloc de 10 000 lignes.

Ceci permet d’écrire dans le fichier en sortie ou de transférer les données vers une base PostgreSQL toutes les 10 000 lignes et ainsi réduire les entrées/sorties. Cependant, suivant la configuration matérielle de la machine, il peut être très intéressant de faire varier cette valeur pour gagner en performance. Par exemple, sur une machine disposant de beaucoup de mémoire, travailler sur 100 000 enregistrements à chaque fois ne doit pas poser de problème et permet d’accroître les performances de manière significative.

DATA_LIMIT 100000

Si, par contre, votre machine dispose de très peu de mémoire ou que les enregistrements sont de très grosse taille, cette valeur devra être diminuée, par exemple :

DATA_LIMIT 1000

Ora2Pg de base n’utilise qu’un seul CPU ou cœur pour le chargement des données. Ceci est très limitant en termes de vitesse d’importation des données. Pour utiliser le parallélisme sur plusieurs cœurs, Ora2Pg dispose de deux directives de configuration : JOBS et ORACLE_COPIES, correspondant respectivement aux options -j et -J de la ligne de commande.

La première, -j ou JOBS, correspond au nombre de processus que l’on veut utiliser en parallèle pour écrire les données directement dans PostgreSQL. La seconde, -J ou ORACLE_COPIES, est utilisée pour définir le nombre de connexions à Oracle pour extraire les données en parallèle.

Toutefois, pour que les requêtes d’extraction des données de la base Oracle puissent être parallélisées, il faut qu’Ora2Pg ait connaissance d’une colonne de la table sur laquelle la division par processus peut être réalisée. Cette colonne doit être de type numérique et, de préférence, être une clé unique car Ora2Pg va scinder les données en fonction du nombre de processus demandés selon le principe de la requête suivante :

SELECT * FROM matable WHERE MOD(colonne, ORACLE_COPIES) = #PROCESSUS;

où colonne est la clé unique, ORACLE_COPIES est la valeur de la variable du même nom ou de l’option -J et #PROCESSUS est le numéro du processus parallélisé en commençant par 0.

Cette colonne est renseignée à l’aide de la directive de configuration DEFINED_PKEY avec pour valeur une liste de tables associées à leurs colonnes, par exemple :

DEFINED_PKEY    EMPLOYEE:ID JOBS:ID TARIF:ROUND(MONTANT_HT) ...

L’utilisation de la fonction ROUND() est impérative lorsque le champ n’est pas un entier. Il est à noter que l’option -J est sans effet si la table exportée n’a pas de colonne définie dans la directive DEFINED_PKEY.

En affinant les valeurs données à -j et -J, il est possible de multiplier par 6 à 10 la vitesse de chargement des données par rapport à un chargement n’utilisant pas la parallélisation.

Les valeurs de -j et -J se multiplient entre elles. Il faut donc faire attention à ne pas dépasser le nombre de cœurs disponible sur la machine, par exemple :

ora2pg -t COPY -c ora2pg.conf -J 8 -j 3

ouvrira 8 connexions à Oracle pour extraire les données en parallèle et, pour chacune de ces connexions, 3 processus supplémentaires seront utilisés pour enregistrer les données dans PostgreSQL, ce qui donne 24 cœurs utilisés par Ora2Pg.

Ce type de parallélisme est contraignant à mettre en œuvre et peut être mis en oeuvre par exemple pour extraire des données d’une table avec de nombreux CLOB ou BLOB pour tenter d’accélérer son export.

Pour paralléliser l’export de plusieurs tables en simultané, on peut aussi utiliser la directive PARALLEL_TABLES. Cette variable prend comme valeur le nombre de connexions à Oracle qui devront être ouvertes pour extraire les données des différentes tables en simultané. Lorsque cette directive a une valeur supérieure à 1, la variable FILE_PER_TABLE est automatiquement activée.

Par défaut, ces trois options ont la valeur 1.

JOBS             1
PARALLEL_TABLES  1
ORACLE_COPIES    1

Suivant la structure d’une table, il peut être aussi nécessaire de faire bouger la valeur de la directive DATA_LIMIT qui, par défaut, est à 10000. Pour les tables dont l’export est très rapide, une valeur à 100000 est préférable, alors que pour les tables avec LOB et potentiellement des enregistrements de très grande taille, une valeur à 100 sera probablement nécessaire. Cette valeur est aussi relative aux performances du système. Une bonne démarche est de tester la vitesse d’export sur des tables moyennes et de positionner la valeur de DATA_LIMIT à ce niveau, par exemple :

DATA_LIMIT        60000

Puis, sur les tables à très faible débit, utiliser l’option de ligne de commande -L :

ora2pg -t COPY -c ora2pg.conf -J 8 -j 3 -L 100

La plupart du temps, 90 % des tables peuvent être exportées avec la même configuration du DATA_LIMIT et du parallélisme pour les insertions dans PostgreSQL seul. Par exemple, sur un serveur avec 24 cœurs et 64 Go de RAM, la commande suivante (PostgreSQL tournant sur ce même serveur) :

ora2pg -t COPY -c ora2pg.conf -j 16 -L 60000

traitera parfaitement la très grande majorité des tables. Il est à noter que l’option -j est sans effet si le nombre de lignes de la table en cours d’export divisé par la valeur de -j (dans l’exemple au dessus : 16) est inférieur à la valeur donnée dans le DATA_LIMIT.

Pour les autres, il faut identifier les tables avec des CLOB et BLOB, les tables avec le plus grand nombre de lignes et celles avec les plus gros volumes de données. Ensuite, il faut voir s’il est possible de multiplexer les connexions à Oracle pour accélérer l’export ainsi que la valeur qui sera le mieux adaptée au DATA_LIMIT en faisant des tests d’import de données.

ALLOW

Par défaut, Ora2Pg exporte toutes les tables qu’il trouve, au moins dans le schéma désigné avec la directive SCHEMA.

On peut cependant limiter l’export à certains objets, grâce à la directive ALLOW. Il suffit ici de donner une liste de noms d’objets, séparées par un espace. Les expressions régulières sont aussi permises.

Exemple :

ALLOW         EMPLOYEES SALE_.* COUNTRIES .*_GEOM_SEQ

EXCLUDE

C’est le pendant du paramètre ALLOW ci-dessus. Cette variable de configuration permet d’exclure des objets de l’extraction. Par défaut, Ora2Pg n’exclut aucun objet. Les expressions régulières sont aussi permises.

Exemple:

EXCLUDE EMPLOYEES TMP_.* COUNTRIES EMPLOYEES_COPIE_2010.* TEST[0-9]+

Attention, les expressions régulières ne fonctionnent pas avec les versions Oracle 8i, vous devez utiliser le caractère % à la place, Ora2Pg utilise l’opérateur LIKE dans ce cas.

ALLOW/EXCLUDE : Filtres étendus

Les objets filtrés par ces directives dépendent du type d’export. Les exemples précédents montrent la manière dont sont déclarés les filtres globaux, ceux qui vont s’appliquer quel que soit le type d’export utilisé. Il est possible d’utiliser un filtre sur un type d’objet uniquement en utilisant la syntaxe : OBJECT_TYPE[FILTER]. Par exemple :

ora2pg -p -c ora2pg.conf -t TRIGGER -a 'TABLE[employees]'

limitera l’export des triggers à ceux définis sur la table EMPLOYEES. Si vous voulez exporter certains triggers mais pas ceux qui ont une clause INSTEAD OF (liés à des vues) :

ora2pg -c ora2pg.conf -t TRIGGER -e 'VIEW[trg_view_.*]'

Ou, par exemple, une forme plus complexe avec inclusion / exclusion d’éléments :

ora2pg -p -c ora2pg.conf -t TABLE -a 'TABLE[EMPLOYEES]' \
                                  -e 'INDEX[emp_.*];CKEY[emp_salary_min]'

Cette commande va exporter la définition de la table EMPLOYEES tout en excluant tous les index commençant par emp_ et la contrainte CHECK nommée emp_salary_min.

Autre exemple, lors de l’export des partitions on peut vouloir exclure certaines tables :

ora2pg -p -c ora2pg.conf -t PARTITION -e 'PARTITION[PART_199.* PART_198.*]'

Ceci va exclure de l’export les tables partitionnées concernant les années 1980 à 1999 mais pas la table principale ni les autres partitions.

Avec l’export des privilèges (GRANT) il est possible d’utiliser cette forme étendue pour exclure certains utilisateurs de l’export ou limiter l’export à certains autres :

ora2pg -p -c ora2pg.conf -t GRANT -a 'USER1 USER2'

ou bien

ora2pg -p -c ora2pg.conf -t GRANT -a 'GRANT[USER1 USER2]'

qui limitera l’export des privilèges aux utilisateurs USER1 et USER2. Mais si vous ne voulez pas exporter leurs privilèges sur certaines fonctions, alors :

ora2pg -p -c ora2pg.conf -t GRANT -a 'USER1 USER2' \
       -e 'FUNCTION[adm_.*];PROCEDURE[adm_.*]'

L’utilisation des filtres étendus en fonction de leur complexité peut nécessiter un certain temps d’apprentissage.

WHERE

Ce paramètre permet d’ajouter des filtres dans les requêtes d’extraction de données. Il n’est donc utilisé que dans le cadre d’un export de données, soit avec TYPE [INSERT|COPY].

Ora2Pg ajoutera tous les filtres déclarés dans cette variable et/ou correspondant à une table donnée, lorsque cela est possible.

Il convient de créer plusieurs fichiers ora2pg.conf si on doit ajouter des filtres sur de nombreuses tables, car la configuration de WHERE peut en effet rapidement devenir illisible si elle est complexe !

• WHERE 1=1

Cet exemple trivial est là pour illustrer le fait que si aucune table n’est mentionnée, la clause WHERE sera appliquée à toutes les requêtes d’extraction. Si le champ n’existe pas pour une table donnée, il sera ignoré. Autrement dit, Ora2Pg ne s’attend pas à ce que le(s) champ(s) mentionnés sans nom existent dans toutes les tables.

Exemple:

WHERE DATE_CREATION > '2001-01-01'

• WHERE TABLE_TEST[ID1='001']

On peut bien sûr préciser une expression pour une ou plusieurs colonnes d’une table donnée.

Par exemple, si on ne veut sélectionner que les départements dans la table DEPARTMENTS dont le champ ID est strictement inférieur à 100 :

WHERE departments[DEPARTMENT_ID<100]

Cela donne :

COPY "departments" ("department_id","department_name",[...])
FROM stdin;
10      Administration  200     1700
20      Marketing       201     1800
30      Purchasing      114     1700
40      Human Resources 203     2400
50      Shipping        121     1500
60      IT      103     1400
70      Public Relations        204     2700
80      Sales   145     2500
90      Executive       100     1700
\.

• WHERE TABLE_TEST[ID1='001' AND ID1='002] DATE_CREATE > '2001-01-01' TABLE_INFO[NAME='test']

On peut ainsi composer sur plusieurs champs d’une même table, et ainsi de suite pour plusieurs tables à la fois. Il suffit pour cela de respecter la convention NOM_DE_TABLE[COLONNE... etc.] et de séparer chaque élément par un espace.

Par exemple, si on veut restreindre les données ci-dessus aux MANAGER_ID strictement supérieurs à 200, on écrira :

WHERE DEPARTMENTS[DEPARTMENT_ID<100 AND MANAGER_ID>200]

Ce qui donne comme résultat:

COPY "departments" ("department_id","department_name",[...])
FROM stdin;
20      Marketing       201     1800
40      Human Resources 203     2400
70      Public Relations        204     2700
\.

REPLACE_QUERY

Le comportement normal d’Ora2Pg est de générer automatiquement la requête d’extraction des données de la manière suivante :

SELECT * FROM TABLENAME [CLAUSE_WHERE];

Quelques fois cela n’est pas suffisant, par exemple si l’on souhaite faire une jointure sur une table d’identifiants à migrer ou tout autre requête plus complexe que ce que ne peut produire Ora2Pg. Dans ce cas il est possible de forcer Ora2Pg à utiliser la requête SQL qui lui sera donné par la directive REPLACE_QUERY. Par exemple :

REPLACE_QUERY   EMPLOYEES[
                    SELECT e.id,e.fisrtname,lastname
                    FROM EMPLOYEES e
                    JOIN EMP_UPDT u
                        ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')
                ]

Cette requête permet de n’extraire que les enregistrements de la table employees qui ont été créés depuis le 1^er août 2014 sachant que l’information se trouve dans la table emp_updt.

Quand votre migration est terminée, et la nouvelle base en production, le travail n’est pas terminé.

Supervision & optimisation :

Il faut superviser cette nouvelle instance pour vérifier que les performances sont celles prévues. Il faut s’attendre à devoir optimiser quelques requêtes inattendues. Les outils pour relever les requêtes consommatrices sont classiques : pgBadger, pg_stat_statements…

Le gel massif des lignes :

Il existe un piège peu connu lié à toute migration logique d’une grande base : le gel massif des lignes. Pour des raisons techniques de recyclage des numéros de transaction, PostgreSQL doit « geler » les lignes anciennes et jamais modifiées, ce qui implique de réécrire le bloc. Or, toutes les lignes insérées par une migration ont le même « âge ». Si elles ne sont pas modifiées, ces lignes risquent d’être toutes gelées et réécrites en même temps : ce peut être très brutal en terme de saturation disque, de journaux générés, etc. si la base est grosse. Le délai avant le déclenchement du gel automatique dépend de la consommation des numéros de transaction sur l’instance migrée, et varie de quelques semaines à des années.

Des ordres VACUUM FREEZE sur les plus grosses tables à des moments calmes permettent d’étaler ces écritures. Si ces ordre sont interrompus, l’essentiel de qu’il a pu geler ne sera plus à re-geler plus tard.

Pour les détails, voir https://dali.bo/m4_html#le-wraparound-1 et https://dali.bo/m5_html#paramétrage-du-freeze-1.

Ora2Pg est simple d’utilisation. Sa configuration permet de réaliser facilement plusieurs fois la migration, pour les différentes étapes du projet. Son auteur est en recherche permanente d’améliorations ou de corrections, n’hésitez pas à lui envoyer un mail pour lui indiquer votre ressenti sur l’outil, vos rapports de bogues, etc.

Le temps de migration du schéma et des données est rapide. Même avec une grosse volumétrie de données, le plus long concerne généralement le code, au niveau applicatif comme au niveau des routines stockées.

Vous pouvez retrouver la documentation en ligne en anglais sur le site officiel d’Ora2Pg.

Dans l’éventualité où les temps de chargement sont un frein à la migration, Dalibo contribue à un outil spécialisé dans l’orchestration du chargement de données nommé Data2Pg. Son auteur, Philippe Beaudoin, a pu le présenter lors du PG Day France 2022 :

• Migration vers PostgreSQL : mener de gros volumes de données à bon port

Une série de documents concernant la migration Oracle vers PostgreSQL est disponible sur le wiki PostgreSQL.

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/n2_solutions.

Création de l’espace de travail

Avec l’utilisateur dalibo, créer une arborescence de travail destinée à recevoir les fichiers du projet de migration sous $HOME/tp_migration.

Création d’un super-utilisateur

Pour la suite des exercices, il est nécessaire de disposer d’une instance pour laquelle le compte dalibo est autorisé à se connecter.

sudo -iu postgres createuser --superuser dalibo

Exploration de la base

Renseigner la chaîne de connexion à la base Oracle dans la configuration du projet et lister les schémas disponibles avec ora2pg.

Lister toutes les tables disponibles avec la commande ora2pg

Migration du schéma

Configurer l’export pour que les fonctions, contraintes et index soient générés dans plusieurs fichiers séparés, à l’aide des directives FILE_PER_FUNCTION, FILE_PER_CONSTRAINT, FILE_PER_FKEYS et FILE_PER_INDEX.

S’assurer que la conversion automatique est désactivée dans la configuration du projet.

Obtenir la liste des colonnes du schéma avec les conversions proposées par Ora2PG.

Ajuster les types DATE et NUMBER(8,2) dans la configuration.

Exécuter le script permettant l’exécution chaînée de tous les types d’export du schéma et des procédures stockées.

Créer la base de données pghr dont le propriétaire est dalibo.

Importer uniquement les tables, les autres objets du schéma seront importés après l’import des données.

Migration des données

Exporter toutes les données de la base Oracle dans des fichiers.

Importer les données dans la base PostgreSQL.

Importer les contraintes, index, séquences et triggers.

Création de l’espace de travail

Avec l’utilisateur dalibo, créer une arborescence de travail destinée à recevoir les fichiers du projet de migration sous $HOME/tp_migration.

Pour créer une arborescence de travail destinée à recevoir les fichiers du projet de migration, on peut s’aider d’Ora2Pg en exécutant la commande suivante :

ora2pg --init_project tp_migration --project_base $HOME

Voici l’arborescence générée par Ora2Pg :

tp_migration/
├── config
│   └── ora2pg.conf
├── data
├── export_schema.sh
├── import_all.sh
├── reports
├── schema
│   ├── dblinks
│   ├── directories
│   ├── functions
│   ├── grants
│   ├── mviews
│   ├── packages
│   ├── partitions
│   ├── procedures
│   ├── sequences
│   ├── sequence_values
│   ├── synonyms
│   ├── tables
│   ├── tablespaces
│   ├── triggers
│   ├── types
│   └── views
└── sources
    ├── functions
    ├── mviews
    ├── packages
    ├── partitions
    ├── procedures
    ├── triggers
    ├── types
    └── views

Ora2Pg a aussi créé un script pour l’export automatique, export_schema.sh, un script pour automatiser l’import dans PostgreSQL, import_all.sh et un fichier de configuration générique config/ora2pg.conf.

Création d’un super-utilisateur

Pour la suite des exercices, il est nécessaire de disposer d’une instance pour laquelle le compte dalibo est autorisé à se connecter.

sudo -iu postgres createuser --superuser dalibo

Puisque le fichier pg_hba.conf de l’instance autorise par défaut les connexions sur le socket local avec une authentification peer, le compte dalibo peut lister les bases de données, se connecter à chacune d’entre elles et dispose des droits super-utilisateur pour en créer de nouvelles.

Dans le cadre d’une migration classique, il est recommandé de limiter les droits du propriétaire d’une base de données au strict minimum afin de réduire la surface d’attaque de l’instance en cas de compromissions d’un serveur de l’infrastructure.

Exploration de la base

Renseigner la chaîne de connexion à la base Oracle dans la configuration du projet et lister les schémas disponibles avec ora2pg.

Le fichier de configuration à modifier pour définir la chaîne de connexion à la base Oracle est tp_migration/config/ora2pg.conf.

Normalement la directive ORACLE_HOME doit déjà avoir la valeur du ORACLE_HOME de l’installation :

ORACLE_HOME     /opt/oracle/product/21c/dbhomeXE

Il reste donc à configurer les paramètres de connexion à l’instance XE d’Oracle avec l’utilisateur HR :

ORACLE_DSN      dbi:Oracle://localhost:1521/hr
ORACLE_USER     hr
ORACLE_PWD      phoenix

Dans la mesure où l’utilisateur hr n’a pas les privilèges DBA, il faut aussi activer la directive USER_GRANTS :

USER_GRANTS     1

On veut exporter le schema HR, il faut donc le spécifier dans la configuration et remplacer :

SCHEMA  CHANGE_THIS_SCHEMA_NAME

par

SCHEMA  HR

L’option -t SHOW_SCHEMA permet d’obtenir la liste des schémas disponibles, à l’exception des schémas systèmes (directive SYSUSERS) :

$ cd $HOME/tp_migration
$ ora2pg -d -c config/ora2pg.conf -t SHOW_SCHEMA
Ora2Pg version: 22.1
Trying to connect to database: dbi:Oracle://192.168.1.109:1521/hr
Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Force Oracle to compile schema HR before code extraction
Showing all schema...
SCHEMA HR

Lister toutes les tables disponibles avec la commande ora2pg

Pour lister les tables de l’instance :

$ ora2pg -c config/ora2pg.conf -t SHOW_TABLE
[1] TABLE HR.COUNTRIES (owner: HR, 25 rows)
[2] TABLE HR.DEPARTMENTS (owner: HR, 27 rows)
[3] TABLE HR.EMPLOYEES (owner: HR, 107 rows)
[4] TABLE HR.JOBS (owner: HR, 19 rows)
[5] TABLE HR.JOB_HISTORY (owner: HR, 10 rows)
[6] TABLE HR.LOCATIONS (owner: HR, 23 rows)
[7] TABLE HR.REGIONS (owner: HR, 4 rows)
----------------------------------------------------------
Total number of rows: 215

Top 10 of tables sorted by number of rows:
  [1] TABLE HR.EMPLOYEES has 107 rows
  [2] TABLE HR.DEPARTMENTS has 27 rows
  [3] TABLE HR.COUNTRIES has 25 rows
  [4] TABLE HR.LOCATIONS has 23 rows
  [5] TABLE HR.JOBS has 19 rows
  [6] TABLE HR.JOB_HISTORY has 10 rows
  [7] TABLE HR.REGIONS has 4 rows
Top 10 of largest tables:
  [1] TABLE HR.DEPARTMENTS: 0 MB (27 rows)
  [2] TABLE HR.LOCATIONS: 0 MB (23 rows)
  [3] TABLE HR.EMPLOYEES: 0 MB (107 rows)
  [4] TABLE HR.JOB_HISTORY: 0 MB (10 rows)
  [5] TABLE HR.JOBS: 0 MB (19 rows)
  [6] TABLE HR.REGIONS: 0 MB (4 rows)

Cette commande affiche aussi le top 10 des tables avec le plus d’enregistrements et, si l’utilisateur de connexion a les droits suffisants, le top 10 des tables de plus gros volume.

Migration du schéma

Configurer l’export pour que les fonctions, contraintes et index soient générés dans plusieurs fichiers séparés, à l’aide des directives FILE_PER_FUNCTION, FILE_PER_CONSTRAINT, FILE_PER_FKEYS et FILE_PER_INDEX.

Modifier le fichier de configuration du projet et positionner les valeurs suivantes :

FILE_PER_FUNCTION     1
FILE_PER_CONSTRAINT   1
FILE_PER_FKEYS        1
FILE_PER_INDEX        1

S’assurer que la conversion automatique est désactivée dans la configuration du projet.

La directive PLSQL_PGSQL doit être désactivée pour la suite des travaux pratiques. En effet, le script export_schema.sh contrôle déjà ce comportement en exportant une version originale et une version convertie du code embarquée à l’aide de l’option -p.

PLSQL_PGSQL  0

Obtenir la liste des colonnes du schéma avec les conversions proposées par Ora2PG.

Le mode d’export SHOW_COLUMN parcourt le catalogue distant et affiche les colonnes de chaque table. Il est recommandé de sauvegarder le résultat dans un fichier pour le consulter régulièrement, ou pour le comparer entre deux ajustements de configuration.

ora2pg -c config/ora2pg.conf -t SHOW_COLUMN

La revue des conversions prévues par Ora2Pg permet d’identifier quelques colonnes dont la perte de précision est possible comme le NUMBER(8,2) qui sera remplacé par un double precision. Le choix d’un timestamp est également inadapté, car les données temporelles du schéma Oracle ne sont pas horodatées.

...
[3] TABLE EMPLOYEES (owner: HR, 1 rows)
  ...  
  HIRE_DATE : DATE(7) => timestamp(0) (date?)
  ...
  SALARY : NUMBER(8,2) => double precision
...
[5] TABLE JOB_HISTORY (owner: HR, 1 rows)
  ...
  START_DATE : DATE(7) => timestamp(0) (date?)
  END_DATE : DATE(7) => timestamp(0) (date?)
  ...

Ajuster les types DATE et NUMBER(8,2) dans la configuration.

Dans le fichier de configuration du projet config/ora2pg.conf, ajouter les directives suivantes :

DATA_TYPE     DATE:date
MODIFY_TYPE   employees:salary:decimal

Exécuter le script permettant l’exécution chaînée de tous les types d’export du schéma et des procédures stockées.

$ sh export_schema.sh

Voici la liste des commandes exécutées par le script :

Running: ora2pg -p -t SEQUENCE -o sequence.sql -b ./schema/sequences
Running: ora2pg -p -t TABLE -o table.sql -b ./schema/tables
Running: ora2pg -p -t PACKAGE -o package.sql -b ./schema/packages
Running: ora2pg -p -t VIEW -o view.sql -b ./schema/views
Running: ora2pg -p -t GRANT -o grant.sql -b ./schema/grants
Running: ora2pg -p -t TRIGGER -o trigger.sql -b ./schema/triggers
Running: ora2pg -p -t FUNCTION -o function.sql -b ./schema/functions
Running: ora2pg -p -t PROCEDURE -o procedure.sql -b ./schema/procedures
Running: ora2pg -p -t TABLESPACE -o tablespace.sql -b ./schema/tablespaces
Running: ora2pg -p -t PARTITION -o partition.sql -b ./schema/partitions
Running: ora2pg -p -t TYPE -o type.sql -b ./schema/types
Running: ora2pg -p -t MVIEW -o mview.sql -b ./schema/mviews
Running: ora2pg -p -t DBLINK -o dblink.sql -b ./schema/dblinks
Running: ora2pg -p -t SYNONYM -o synonym.sql -b ./schema/synonyms
Running: ora2pg -p -t DIRECTORY -o directorie.sql -b ./schema/directories

Généralement l’extraction des GRANT et TABLESPACE génère une erreur si l’utilisateur n’a pas les droits DBA.

L’option -p implique une transformation par Ora2Pg des objets embarquant du SQL ou du code PL/SQL. Le script réalise l’export de ces mêmes objets avec le code source originel de la base Oracle pour d’éventuelles comparaisons :

Running: ora2pg -t PACKAGE -o package.sql -b ./sources/packages
Running: ora2pg -t VIEW -o view.sql -b ./sources/views
Running: ora2pg -t TRIGGER -o trigger.sql -b ./sources/triggers
Running: ora2pg -t FUNCTION -o function.sql -b ./sources/functions
Running: ora2pg -t PROCEDURE -o procedure.sql -b ./sources/procedures
Running: ora2pg -t PARTITION -o partition.sql -b ./sources/partitions
Running: ora2pg -t TYPE -o type.sql -b ./sources/types
Running: ora2pg -t MVIEW -o mview.sql -b ./sources/mviews

Voici l’arbre des fichiers générés :

tp_migration/
├── config
│   └── ora2pg.conf
├── data
├── export_schema.sh
├── import_all.sh
├── reports
│   ├── columns.txt
│   ├── report.html
│   └── tables.txt
├── schema
│   ├── dblinks
│   ├── directories
│   ├── functions
│   │   ├── EMP_SAL_RANKING_function.sql
│   │   ├── function.sql
│   │   └── LAST_FIRST_NAME_function.sql
│   ├── grants
│   ├── mviews
│   ├── packages
│   │   ├── emp_actions
│   │   │   ├── fire_employee_package.sql
│   │   │   ├── hire_employee_package.sql
│   │   │   ├── num_above_salary_package.sql
│   │   │   └── raise_salary_package.sql
│   │   ├── emp_mgmt
│   │   │   ├── create_dept_package.sql
│   │   │   ├── hire_package.sql
│   │   │   ├── increase_comm_package.sql
│   │   │   ├── increase_sal_package.sql
│   │   │   ├── remove_dept_package.sql
│   │   │   └── remove_emp_package.sql
│   │   ├── global_variables.conf
│   │   └── package.sql
│   ├── partitions
│   ├── procedures
│   │   ├── ADD_JOB_HISTORY_procedure.sql
│   │   ├── procedure.sql
│   │   └── SECURE_DML_procedure.sql
│   ├── sequences
│   │   └── sequence.sql
│   ├── synonyms
│   ├── tables
│   │   ├── CONSTRAINTS_table.sql
│   │   ├── FKEYS_table.sql
│   │   ├── INDEXES_table.sql
│   │   └── table.sql
│   ├── tablespaces
│   ├── triggers
│   │   ├── trigger.sql
│   │   └── UPDATE_JOB_HISTORY_trigger.sql
│   ├── types
│   └── views
│       ├── EMP_DETAILS_VIEW_view.sql
│       └── view.sql
└── sources
    ├── functions
    │   ├── EMP_SAL_RANKING_function.sql
    │   ├── function.sql
    │   └── LAST_FIRST_NAME_function.sql
    ├── mviews
    ├── packages
    │   ├── emp_actions_package.sql
    │   ├── emp_mgmt_package.sql
    │   └── package.sql
    ├── partitions
    ├── procedures
    │   ├── ADD_JOB_HISTORY_procedure.sql
    │   ├── procedure.sql
    │   └── SECURE_DML_procedure.sql
    ├── triggers
    │   ├── trigger.sql
    │   └── UPDATE_JOB_HISTORY_trigger.sql
    ├── types
    └── views
        ├── EMP_DETAILS_VIEW_view.sql
        └── view.sql

Créer la base de données pghr dont le propriétaire est dalibo.

Puisque l’utilisateur dalibo a été créé précédemment, il suffit de créer la base de la manière suivante :

createdb --echo --owner dalibo pghr

Puisque cette base sera essentiellement utilisée dans la suite des exercices, il est possible de positionner la variable PGDATABASE dans le fichier .bash_profile de l’utilisateur dalibo.

$ cat ~/.bash_profile
export PGDATABASE=pghr

Charger la nouvelle variable d’environnement :

source $HOME/.bash_profile

Importer uniquement les tables, les autres objets du schéma seront importés après l’import des données.

psql -f schema/tables/table.sql
psql -f schema/views/view.sql

Vérification de la bonne application du script d’import :

psql -c "\d"

               List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+--------
 public | countries        | table | dalibo
 public | departments      | table | dalibo
 public | emp_details_view | view  | dalibo
 public | employees        | table | dalibo
 public | job_history      | table | dalibo
 public | jobs             | table | dalibo
 public | locations        | table | dalibo
 public | regions          | table | dalibo

Migration des données

Exporter toutes les données de la base Oracle dans des fichiers.

ora2pg -t COPY -o data.sql -b data -c config/ora2pg.conf

[========================>] 7/7 tables (100.0%) end of scanning.
[========================>] 25/25 rows (100.0%) Table COUNTRIES (25 recs/sec)
[========================>] 27/27 rows (100.0%) Table DEPARTMENTS (27 recs/sec)
[========================>] 107/107 rows (100.0%) Table EMPLOYEES (107 recs/sec)
[========================>] 19/19 rows (100.0%) Table JOBS (19 recs/sec)
[========================>] 10/10 rows (100.0%) Table JOB_HISTORY (10 recs/sec)
[========================>] 23/23 rows (100.0%) Table LOCATIONS (23 recs/sec)
[========================>] 4/4 rows (100.0%) Table REGIONS (4 recs/sec)

Cette commande va générer un fichier par table et un fichier data.sql qui pourra être utilisé pour charger les données en une fois.

data/
├── COUNTRIES_data.sql
├── data.sql
├── DEPARTMENTS_data.sql
├── EMPLOYEES_data.sql
├── JOB_HISTORY_data.sql
├── JOBS_data.sql
├── LOCATIONS_data.sql
└── REGIONS_data.sql

Importer les données dans la base PostgreSQL.

L’import peut être réalisé fichier par fichier, mais il est plus simple d’utiliser le script de chargement global data.sql. Voici son contenu :

BEGIN;
ALTER TABLE countries DISABLE TRIGGER USER;
ALTER TABLE departments DISABLE TRIGGER USER;
ALTER TABLE employees DISABLE TRIGGER USER;
ALTER TABLE jobs DISABLE TRIGGER USER;
ALTER TABLE job_history DISABLE TRIGGER USER;
ALTER TABLE locations DISABLE TRIGGER USER;
ALTER TABLE regions DISABLE TRIGGER USER;

\i 'data/COUNTRIES_data.sql'
\i 'data/DEPARTMENTS_data.sql'
\i 'data/EMPLOYEES_data.sql'
\i 'data/JOBS_data.sql'
\i 'data/JOB_HISTORY_data.sql'
\i 'data/LOCATIONS_data.sql'
\i 'data/REGIONS_data.sql'

ALTER TABLE countries ENABLE TRIGGER USER;
ALTER TABLE departments ENABLE TRIGGER USER;
ALTER TABLE employees ENABLE TRIGGER USER;
ALTER TABLE jobs ENABLE TRIGGER USER;
ALTER TABLE job_history ENABLE TRIGGER USER;
ALTER TABLE locations ENABLE TRIGGER USER;
ALTER TABLE regions ENABLE TRIGGER USER;
COMMIT;