Migrer d’Oracle à PostgreSQL

Formation MIGORPG

Dalibo SCOP

24.04

17 avril 2024

Sur ce document

Formation Formation MIGORPG
Titre Migrer d’Oracle à PostgreSQL
Révision 24.04
ISBN N/A
PDF https://dali.bo/migorpg_pdf
EPUB https://dali.bo/migorpg_epub
HTML https://dali.bo/migorpg_html
Slides https://dali.bo/migorpg_slides

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.

Plan de migration

PostgreSQL

Introduction

Ce module est organisé en quatre parties :

  • Méthodologie de la migration
  • Choix de l’outil de migration
  • Installation d’Ora2Pg
  • Rapport d’évaluation

Avertissement

  • Ceci est écrit par des spécialistes de PostgreSQL
    • pas d’Oracle

Méthodologie de migration

La première migration est importante :

  • Les méthodes employées seront réutilisées, améliorées…
  • Un nouveau SGBD doit être supporté pendant de nombreuses années
  • Elle influence la vision des utilisateurs vis-à-vis du SGBD
  • Une migration ratée ou peu représentative est un argument pour les détracteurs du projet

Projet de migration

Le projet doit être choisi avec soin :

  • Ni trop gros (trop de risque)
  • Ni trop petit (sans valeur)
  • Transversal :
    • Implication maximale
    • Projet de groupe, pas individuel

Équipe du projet de migration

  • Chef de projet
  • Équipe hétérogène (pas que des profils techniques)
  • Recetteurs et utilisateurs nombreux (validation du projet la plus continue possible)

Expertise extérieure

  • Société de service
  • Contrat de support
  • Expert PostgreSQL

Gestion de projet

  • Réunions de lancement, de suivi
  • Reporting
  • Serveurs de projet
  • Pas un projet au rabais, ou un travail de stagiaire

Passer à PostgreSQL

  • Ce n’est pas une révolution
  • Le but est de faire des économies
    • … sans chamboulement

Motiver

  • Formations indispensables
  • Divers cursus
    • du chef de projet au développeur
  • Adoption grandissante de PostgreSQL
    • pérennité

Valoriser

  • Concepts PostgreSQL très proches des SGBD propriétaires
    • Adapter les compétences
    • Ne pas tout reprendre à zéro

Gestion des délais

Souvent moins important :

  • Le service existe déjà
  • Donner du temps aux acteurs

Coûts

  • Budget ?
  • Open source <> gratuit
    • coûts humains
    • coûts matériels

Qualité

  • Cruciale
    • la réussite est obligatoire
  • Le travail effectué doit être réutilisable
  • Ou tout du moins l’expérience et les méthodologies

But de la première migration

  • Privilégier la qualité
  • Contrôler les coûts
  • N’est souvent pas contrainte par des délais stricts

Choix de l’outil de migration

Avant de pouvoir porter l’application et le PL :

  • Migrer le schéma
  • Migrer les données

Besoins de la migration : schéma

  • Veut-on migrer le schéma tel quel ?
  • Utiliser les fonctionnalités de PostgreSQL ?
    • n’est plus vraiment à isofonctionnalité
  • Créer un nouveau schéma :
    • d’un coup
    • les tables d’abord, les index et contraintes ensuite ?

Besoins de la migration : types

  • On rencontre souvent les types suivants sous Oracle :
    • number(18,0), number(4,0)
    • int : -2147483648 à +2147483647 (4 octets, number(9,0))
    • bigint : -9223372036854775808 à 9223372036854775807 (8 octets, number(18,0))
  • Type natifs bien plus performants (gérés par le processeur, taille fixe)
  • Certains outils migrent en numeric(x,0), d’autres en int/bigint
    • peut être un critère de choix

Besoins de la migration : autres types

  • Types plein texte ?
  • Blob ?
  • GIS ?
  • Un développement peut être nécessaire pour des types spéciaux

Besoins de la migration

  • Déclarer les tables
  • Les remplir
  • Puis seulement déclarer les index, PK, FK, contraintes…
  • Performances…

Migration des données

Veut-on :

  • Migrer en une seule fois les données ? (« Big Bang »)
  • Pouvoir réaliser des incréments ?
  • Paralléliser sur plusieurs sessions/threads ?
  • Modifier des données « au passage » ?

Fonctionnalités problématiques

Lors de la migration, certaines fonctionnalités d’Oracle auront peu ou pas d’équivalent :

  • Vues matérialisées (mise à jour)
  • Partitionnement (différences)
  • Synonymes
  • Conversion de type implicite
  • Absence de hint (tag de requête)
  • Accès par ROWID

Choix de l’outil

Suivant les réponses aux questions précédentes, vous choisirez :

  • Ora2Pg
  • Un ETL :
    • Kettle (Pentaho Data Integrator)
    • Talend
  • De développer votre propre programme
  • De mixer les solutions

Ora2Pg - introduction

  • En Perl
  • Se connecte à Oracle
  • Génère un fichier SQL compatible avec PostgreSQL, en optimisant les types
  • Conversion automatique d’une partie du code PL/SQL en PL/pgSQL
  • Simple de mise en œuvre
  • Rapide au chargement (utilise COPY)

Ora2Pg - défauts

  • Big-Bang
    • pas d’incrémental

Ora2Pg - fonctionnalités

  • Exporte tout le schéma Oracle :
    • tables, vues, séquences, contraintes d’intégrité, trigger, etc.
    • utilisateurs et droits
  • Gère la conversion des types
    • blob et clob -> bytea et text
    • number -> int, bigint, real, double, decimal
  • Réécrit les entêtes de fonction correspondant aux fonctions Oracle
  • Aide à :
    • la conversion PL/SQL -> PL/pgSQL
    • au partitionnement (par héritage, ou déclaratif)

Les ETL - avantages

  • Spécialisés dans la transformation et le chargement de données
  • Rapides (cœur de métier)
  • Parallélisables
  • Très souples sur la transformation
  • Migration incrémentale possible (fusion, slow changing dimensions, etc.)

Les ETL - inconvénients

  • Migration sommaire du schéma
    • quand c’est supporté
  • Beaucoup de travail de paramétrage
    • peut-être 200 jobs à créer si 200 tables…
  • Apprentissage long
    • outil complexe et riche fonctionnellement

Installation d’Ora2Pg

Étapes :

  • Téléchargement
  • Pré-requis
  • Compilation
  • Installation
  • Utilisation

Téléchargement

Dépendances requises

  • Oracle >= 8i client ou serveur
  • Environnement Oracle correct (ORACLE_HOME et PATH comprenant sqlplus)
  • libaio (Redhat) ou libaio1 (Debian)
  • Unix : Perl 5.10+
  • Windows : Strawberry Perl 5.10+ ou ActiveStep Perl 5.10+
  • Modules Perl
    • Time::HiRes
    • Perl DBI > v1.614 et DBD::Oracle

Dépendances optionnelles

En option :

  • PostgreSQL >= 8.4 client ou serveur
  • DBD::Pg pour l’import direct dans PostgreSQL
  • Compress::Zlib : compression des fichiers en sortie
  • DBD::MySQL pour migrer les bases MySQL
  • DBD::ODBC pour migrer les bases Microsoft SQL Server

Compilation et installation

  • Décompresser l’archive téléchargée
  • Générer les fichiers de compilation
  • Compiler et installer
  • Ora2Pg est prêt à être configuré !

Évaler une migration

Le script ora2pg s’utilise de la façon suivante :

ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]

Utilisation basique :

ora2pg -t ACTION [-c fichier_de_configuration]

Rapport d’évaluation - 1

  • Rapport exhaustif du contenu de la base Oracle
ora2pg -t SHOW_REPORT
ora2pg -t SHOW_REPORT --dump_as_html

Rapport d’évaluation - 2

  • Estimation du coût de migration
ora2pg -t SHOW_REPORT --estimate_cost
ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html

Conclusion

Points essentiels :

  • Grande importance de la première migration
  • Même si Oracle et PostgreSQL sont assez similaires, il y a de nombreuses différences
  • Choix des outils de migration
  • La majorité du temps de migration est imputable à la conversion du PL/SQL
  • Évaluation de la migration, ce qui doit ou pas être migré et comment

Questions

N’hésitez pas, c’est le moment !

Quiz

Installation de PostgreSQL depuis les paquets communautaires

Travaux pratiques

Travaux pratiques (solutions)

Schéma et données

Introduction

Ce module est organisé en trois parties :

  • Configuration d’Ora2Pg
  • Migration du schéma
  • Migration des données

Configuration d’Ora2Pg

Étapes de la configuration :

  • Syntaxe du fichier de configuration
  • Connexion et schéma Oracle
  • Validation de la configuration
  • La base Oracle vue par Ora2Pg
  • Estimation de la charge de migration
  • Création d’une configuration générique

Structure du fichier

Structure

  • Fichier de configuration simple
  • Les lignes en commentaires débutent par un dièse (#)
  • Les variables sont en majuscules
  • Plusieurs paramètres sont du type binaire : 0 pour désactivé et 1 pour activé

Configuration locale

  • IMPORT fichier.conf
  • ORACLE_HOME /path/.../
  • DEBUG [0|1]
  • LOGFILE /path/.../migration.log

Connexion à Oracle

  • ORACLE_DSN
    • dbi:Oracle:host=serveur;sid=INSTANCE
    • dbi:Oracle:TNSNAME
    • dbi:Oracle://serveur:1521/service
  • ORACLE_USER (system par défaut)
  • ORACLE_PWD (manager par défaut)
  • SCHEMA
    • NOM_SCHEMA versus SYSUSERS
  • USER_GRANTS [0|1]
    • l’utilisateur Oracle a-t-il les droits DBA ?

Exploration de la base distante

Cette étape d’exploration permet d’obtenir des informations sur la base Oracle :

  • Liste des tables et colonnes
  • Recherche de l’encodage de la base

Découverte de la base

Certaines informations sont disponibles immédiatement, sans plus de configuration :

  • SHOW_VERSION affiche la version de l’instance Oracle.
  • SHOW_SCHEMA liste les schémas définis sous Oracle.
  • SHOW_TABLE affiche la liste des tables de la base Oracle.
  • SHOW_COLUMN affiche la liste des colonnes par table d’une base Oracle.

Gestion de l’encodage - 1

Recherche de l’encodage utilisé par l’instance Oracle :

  • SHOW_ENCODING : affiche les valeurs utilisées par Ora2Pg pour
    • NLS_LANG
    • CLIENT_ENCODING
  • NLS_LANG
    • AMERICAN_AMERICA.AL32UTF8
    • French_France.WE8ISO8895P1
  • NLS_NCHAR
    • AL32UTF8

Gestion de l’encodage - 2

  • CLIENT_ENCODING
    • utf8, latin1, latin9
  • BINMODE
    • utf8, raw

Configuration générique

Le but du fichier de configuration générique est multiple :

  • éviter de faire des allers/retours en édition sur ce fichier
  • éviter d’avoir une multitude de fichiers de configuration dédiés à chaque opération
  • utiliser la souplesse des options de ligne de commande

Fichiers de sortie

Utilisation de fichiers de sortie dédiés

  • FILE_PER_CONSTRAINT 1
  • FILE_PER_INDEX 1
  • FILE_PER_FKEYS 1
  • FILE_PER_TABLE 1
  • FILE_PER_FUNCTION 1

Ordres SQL additionnels

  • Ajout d’ordres SQL :
    • DISABLE_TRIGGERS 1
    • TRUNCATE_TABLE 1
    • DISABLE_SEQUENCE 1
    • COMPILE_SCHEMA [0|1]
  • Désactivation de la conversion automatique du PL/SQL :
    • PLSQL_PGSQL 0

Comportement côté PostgreSQL

Utilisation d’un schéma sous PostgreSQL ?

  • EXPORT_SCHEMA [0|1]
  • PG_SCHEMA nom_du_shema
  • CREATE_SCHEMA 0

Versions de PostgreSQL

  • Indiquer la version majeure cible de PostgreSQL
    • PG_VERSION 9.6
    • PG_VERSION 11
  • Par défaut : 11
  • Autres contrôles lié à la version :
    • BITMAP_AS_GIN : export des index bitmap en btree_gin
    • STANDARD_CONFORMING_STRINGS échappement dans les chaines de caractères

Bases spatiales

La base contient des champs de type SDO_GEOMETRY.

  • Faut-il utiliser les contraintes sur les géométries ?
    • AUTODETECT_SPATIAL_TYPE [0|1]
  • Quel système de référence spatial par défaut ?
    • DEFAULT_SRID 4326
    • CONVERT_SRID [0|1|N]
  • PostGIS est-il installé dans un schéma spécifique ?
    • POSTGIS_SCHEMA schema_name
  • Format d’export des géométries :
    • GEOMETRY_EXTRACT_TYPE [INTERNAL|WKT|WKB]

Configuration liée aux LOB

L’export des champs CLOB et BLOB sont contrôlés par :

  • LONGREADLEN 1047552
  • LONGTRUNCOK 0
  • NO_LOB_LOCATOR 0
  • BLOB_LIMIT 500

Migration du schéma

Étapes :

  • Organisation de l’espace de travail
  • Utilisation de la configuration générique
  • Export du schéma Oracle
  • Import du schéma dans PostgreSQL

Organisation de l’espace de travail

  • Arborescence d’un projet de migration
    • dossier de la configuration
    • dossier du schéma source Oracle
    • dossier du schéma converti à PostgreSQL
    • dossier des fichiers de données exportées
ora2pg --init_project dirname --project_base dirname

Utilisation de la configuration générique

  • Fichier ora2pg.conf générique
    • création du fichier ora2pg.conf générique dans le dossier de configuration
  • Utilisation des options de ligne de commande du script ora2pg
    • -t type d’export
    • -b répertoire de stockage des fichiers
    • -o nom du fichier de sortie
    • -p conversion automatique du code

Export de la structure de la base

  • Export des tables, contraintes et index
ora2pg -p -t TABLE -o table.sql -b schema/tables -c config/ora2pg.conf
  • Export des séquences
ora2pg -t SEQUENCE -o sequences.sql -b schema/sequences -c config/ora2pg.conf
  • Export des vues
ora2pg -p -t VIEW -o views.sql -b schema/views -c config/ora2pg.conf
  • Préservation des tablespaces Oracle : USE_TABLESPACE

Modification de la structure des objets

  • Renommer les objets
    • REPLACE_TABLES ORIG_TABLE1:DEST_TABLE1
    • REPLACE_COLS TABLE1(ORIG_COL1:DEST_COL1, [..])
    • INDEXES_SUFFIX _idx
    • INDEXES_RENAMING
  • Changer les types de données
    • DATA_TYPE NUMBER(*\,0):bigint
    • MODIFY_TYPE TABLE1:COL1:integer TABLE1:COL2:timestamptz
    • REPLACE_AS_BOOLEAN TABLE1:COL1 [..]

Export des objets globaux

  • Les rôles et droits
ora2pg -t GRANT -o users.sql -b schema/users -c config/ora2pg.conf
  • Les tablespaces
ora2pg -t TABLESPACE -o tablespaces.sql -b schema/tablespaces \
-c config/ora2pg.conf
  • Les types composites
ora2pg -p -t TYPE -o types.sql -b schema/types -c config/ora2pg.conf

Export des routines stockées

  • Export des objets avec conversion de code
ora2pg -p -t TRIGGER -o triggers.sql -b schema/triggers -c config/ora2pg.conf
ora2pg -p -t FUNCTION -o functions.sql -b schema/functions -c config/ora2pg.conf
ora2pg -p -t PROCEDURE -o procedures.sql -bschema/procedures -c config/ora2pg.conf
ora2pg -p -t PACKAGE -o packages.sql -b schema/packages -c config/ora2pg.conf

Export des sources PL/SQL

  • Extraction du code brut d’Oracle
ora2pg -t TYPE -o types.sql -b sources/types -c config/ora2pg.conf
ora2pg -t VIEW -o views.sql -b sources/views -c config/ora2pg.conf
ora2pg -t MVIEW -o mviews.sql -b sources/mviews -c config/ora2pg.conf
ora2pg -t TRIGGER -o triggers.sql -b sources/triggers -c config/ora2pg.conf
ora2pg -t FUNCTION -o functions.sql -b sources/functions -c config/ora2pg.conf
ora2pg -t PROCEDURE -o procedures.sql -b sources/procedures -c config/ora2pg.conf
ora2pg -t PACKAGE -o packages.sql -b sources/packages -c config/ora2pg.conf

Export des partitions

  • Partitions par range, list et hash
  • Partitions et sous-partitions
ora2pg -t PARTITION -o partitions.sql -b schema/partitions -c config/ora2pg.conf
  • Paramètrage : DISABLE_PARTITION et PG_SUPPORTS_PARTITION

Export des vues matérialisées

ora2pg -t MVIEW -o mviews.sql -b schema/mviews -c config/ora2pg.conf

Export des synonymes

PostgreSQL ne possède pas d’objet de type SYNONYM

  • Ce sont des alias vers des objets d’autres schémas ou bases de données
  • Il existe deux méthodes pour les émuler sous PostgreSQL :
    • modification du search_path
    • utilisation de vues
  • Ora2Pg utilise la deuxième méthode :
ora2pg -t SYNONYM -o synonyms.sql -b schema/synonyms -c config/ora2pg.conf

Export des tables externes

PostgreSQL ne possède pas d’objets de type DIRECTORY ni de tables EXTERNAL

  • Ce sont des répertoires et fichiers de données utilisés comme des tables
  • Sous PostgreSQL, il faut utiliser le Foreign Data Wrapper file_fdw
    • ne fonctionne qu’en lecture
    • ces tables doivent respecter le format CSV de COPY
ora2pg -t DIRECTORY -o directories.sql -b schema/directories -c config/ora2pg.conf

PostgreSQL ne possède pas d’objets de type DATABASE LINK

  • Ce sont des objets permettant l’accès à des bases distantes
  • Sous PostgreSQL il faut utiliser le Foreign Data Wrapper oracle_fdw
    • fonctionne en lecture / écriture
    • les tables distantes sont vues comme des tables locales
ora2pg -t DBLINK -o dblinks.sql -b schema/dblinks -c config/ora2pg.conf

Export des BFILE et DIRECTORY - 1

  • Sous PostgreSQL il n’y a pas d’équivalent aux types DIRECTORY et BFILE
    • Ora2Pg exporte les BFILE en donnée bytea par défaut
    • Si le type BFILE est redéfini en TEXT, stockage du chemin du fichier externe

Export des BFILE et DIRECTORY - 2

  • Pour avoir la même fonctionnalité : extension external_file
    • type EFILE correspondant au type BFILE : (directory_name, filename)
    • les fichiers sont stockés sur le système de fichier
    • fichier accessible en lecture / écriture
    • activé lorsque BFILE est redéfini en EFILE (directive DATA_TYPE)

Recherche Plein Texte

Oracle Index Texte

  • CONTEXT
    • indexation de documents volumineux
    • opérateur CONTAINS
  • CTXCAT
    • indexation de petits documents
    • opérateur CATSEARCH

Recherche Plein Texte

PostgreSQL : Full Text Search/Recherche Plein Texte

  • correspond à CONTEXT
  • opérateur @@ équivalent à CONTAINS
    SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
  • S’appuie sur GIN ou GiST
  • Extension pg_trgm pour les recherches LIKE '%mot%mot%’, équivalent de CTXCAT

Préparation de l’import

  • Préparation de l’import du schéma
    • création du propriétaire de la base
    • création de la base
  • Si EXPORT_SCHEMA est activé
    • création du schéma
    • utilisation d’un schéma par défaut
  • Création des tablespaces

Import du schéma

Création des objets du schéma :

psql -U myuser -f schema/tables/tables.sql mydb >> create_mydb.log 2>&1
psql -U myuser -f schema/partitions/partitions.sql mydb >> create_mydb.log 2>&1
psql -U myuser -f schema/views/views.sql mydb >> create_mydb.log 2>&1
psql -U myuser -f schema/tablespaces/tablespaces.sql mydb >> create_mydb.log 2>&1

Import différé

Chargement différé de certains objets :

  • Séquences
  • Contraintes
  • Déclencheurs
  • Index

Bilan de l’export/import

Bilan de l’export/import du schéma :

  • Lecture des logs et étude des problèmes
  • Sensibilité à la casse
  • Encodage des valeurs de contraintes CHECK et conditions des index
  • Possibilité de code spécifique à Oracle dans les contraintes et les index
  • Champs numériques

Exemple d’erreurs

  • Accents dans les noms d’objets
  • Mots réservés
  • Certaines conversions implicites
    • CHECK (WYEAR between 0 and 9);
    • CHECK (wyear::integer between 0 and 9);

Migration des données

Étapes :

  • Export / import des données
  • Problèmes rencontrés
  • Restauration des séquences, contraintes, triggers et index
  • Performances de l’import des données
  • Utilisation du parallélisme
  • Limitation des données à importer

Exporter les données

  • Création des fichiers de données :
ora2pg -t COPY -o datas.sql -b data/ -c config/ora2pg.conf
ora2pg -t INSERT -o datas.sql -b data/ -c config/ora2pg.conf
  • Un fichier de données par table :
    • FILE_PER_TABLE 1
  • Compression des fichiers de données

Cas des données CLOB/BLOB

  • Les champs bytea
    • export des champs BLOB et CLOB en bytea très lent
    • exclusion temporaire des tables avec LOB
    • utilisation de la parallélisation pour ces tables

Cas des données spatiales

  • Le SRID, système spatial de référence
    • CONVERT_SRID converti la valeur Oracle dans la norme EPSG
    • DEFAULT_SRID force la valeur du SRID par défaut
  • Mode d’extraction des données GEOMETRY_EXTRACT_TYPE [WKT|WKB|INTERNAL]

Import des données

  • Import des fichiers de données :
psql -U myuser -f data/datas.sql mydb >> data_mydb.log 2>&1
gunzip -c data/datas.sql.gz | psql  -U myuser  mydb >> data_mydb.log 2>&1
bunzip2 -c data/datas.sql.bz2 | psql  -U myuser  mydb >> data_mydb.log 2>&1
  • Chargement direct dans PostgreSQL lors de l’export

Restauration des contraintes

Restauration des contraintes, triggers, séquences et index

psql -U myuser -f schema/tables/CONSTRAINTS_tables.sql mygdb >> create_mydb.log 2>&1
psql -U myuser -f schema/tables/INDEXES_tables.sql mygdb >> create_mydb.log 2>&1
psql -U myuser -f schema/sequences/sequences.sql mygdb >> create_mydb.log 2>&1
psql -U myuser -f schema/triggers/triggers.sql mygdb >> create_mydb.log 2>&1

Restauration parallélisée des contraintes

Action :

  • LOAD permet de paralléliser des ordres SQL sur N processus
ora2pg -c config/ora2pg -t LOAD -j 4 -i schema/tables/INDEXES_tables.sql
ora2pg -c config/ora2pg -t LOAD -j 4 -i schema/tables/CONSTRAINTS_tables.sql

Problèmes d’import des données

  • Problème d’échappement de caractères : utiliser COPY
  • Encodage des données : CLIENT_ENCODING
  • Erreur de type numérique : DEFAULT_NUMERIC ou ALTER TABLE
  • CLOB, BLOB et XML : LONGREADLEN

Performances de l’import des données

  • Type d’export COPY
  • Import direct dans PostgreSQL
    PG_DSN  dbi:Pg:dbname=test_db;host=localhost;port=5432
    PG_USER [nom_utilisateur]
    PG_PWD  [mot_de_passe]
  • Nombre d’enregistrements traités en mémoire : DATA_LIMIT

Utiliser le parallélisme

  • Parallélisme pour le traitement et l’import des données dans PostgreSQL
    • JOBS Ncores
  • Parallélisme pour l’extraction des données d’Oracle
    • ORACLE_COPIES Ncores
    • DEFINED_PKEY EMPLOYEE:ID
  • Parallélisme par tables exportées
    • PARALLEL_TABLES Ncores
  • Nombre de processus utilisés
    • JOBS x ORACLE_COPIES | PARALLEL_TABLES = Total Nombre cœurs

Limitation des données exportées

  • Contrôle des tables à exporter
    • ALLOW TABLE1 TABLE2 [..] TABLEN
    • EXCLUDE TABLE1 TABLE2 [..] TABLEN
  • Contrôle des données à exporter
    • WHERE TABLE[condition valide] GLOBAL_CONDITION
    • WHERE TABLE_TEST[ID1='001']
    • WHERE DATE_CREATION > '2001-01-01
    • REPLACE_QUERY TABLENAME[SQL_QUERY]

Conclusion

  • Le temps de migration du schéma et des données est très rapide…
  • …il est souvent marginal par rapport au temps de la migration du code
  • Préférer toujours la dernière version d’Ora2Pg
  • Faites un retour d’expérience de votre migration à l’auteur

Pour aller plus loin

Questions

N’hésitez pas, c’est le moment !

Quiz

Travaux pratiques

Travaux pratiques (solutions)

Requêtes SQL

Introduction

Ce module est organisé en quatre parties :

  • Compatibilité avec Oracle
  • Types de données
  • Différences de syntaxes
  • Transactions

Compatibilité avec Oracle

Oracle et PostgreSQL sont assez proches :

  • Tous deux des SGBDR
  • Le langage d’accès aux données est SQL
  • Les deux ont des connecteurs pour la majorité des langages (Java, C, .Net…)
  • Les langages embarqués sont différents
  • C’est dans les détails que se trouvent les problèmes

Points communs

PostgreSQL et Oracle :

  • Ont le même langage d’accès aux données (SQL)
    • mais des « variantes » différentes (extensions au standard)
  • Nombreux concepts en commun:
    • transactions et savepoints
    • MVCC et verrouillage
  • Conservation
    • des logiques applicative et algorithmique
    • de l’architecture applicative

Différences de schéma - 1

  • Le schéma sous Oracle : USER.OBJECT
    • sous PostgreSQL, véritable espace de nommage
  • La création des tables est entièrement compatible mais :
    • les tables temporaires globales n’existent pas sous PostgreSQL
    • INITTRANS, MAXEXTENTS sont inutiles (et n’existent pas)
    • PCTFREE correspond au paramètre fillfactor
    • PCTUSED est inutile (et n’existe pas)
  • Les colonnes générées sont disponibles depuis PostgreSQL 12
    • uniquement pour les colonnes stockées
    • utilisation de vues pour simuler les colonnes virtuelles

Différences de schéma - 2

  • Casse par défaut du nom des objets différente entre Oracle et PostgreSQL
  • Si casse non spécifiée :
    • majuscule sous Oracle
    • minuscule sous PostgreSQL
  • Forcer la casse
    • " " autour des identifiants

Différences de schéma - 3

  • Les contraintes sont identiques (clés primaires, étrangères et uniques, …)
  • Les index : btree uniquement, les autres n’existent pas (bitmap principalement)
  • Les tablespaces : la même chose dans sa fonctionnalité principale
  • Les types utilisateurs (CREATE TYPE) nécessitent une réécriture
  • Les liens inter-bases (DBLINK) n’existent pas sauf sous forme d’extensions (dblink ou fdw)

Autres différences anecdotiques

  • HAVING et GROUP BY
    • Oracle permet GROUP BY après HAVING
    • PostgreSQL impose GROUP BY avant HAVING
  • Table DUAL n’est pas nécessaire
  • Conversions implicites de et vers un type text
    • supporté par Oracle
    • plus supporté par PostgreSQL depuis la version 8.3
    • convertir explicitement :
         SELECT 1 = 'a'::text;

Types de données

  • Plusieurs incompatibilités
    • Oracle ne supporte pas bien la norme SQL
    • types numériques, chaînes, binaires, dates
  • PostgreSQL fournit également des types spécialisés

Différences sur les types numériques

  • Oracle ne gère pas les types numériques « natifs » SQL :
    • smallint, integer, bigint
  • Le type numeric du standard SQL est appelé number sous Oracle

Différences sur les types chaînes

  • Pas de varchar2 dans PostgreSQL
    • mais varchar
    • varchar (n) : taille en nombre de caractères
  • Le type text équivalent à varchar sans taille (1 Go maximum)
  • Attention, sous Oracle, '' équivaut à NULL
    • sous PostgreSQL, '' et NULL sont distincts
  • Un seul encodage par base
  • Collationnement par colonne

Différences sur le type booléen

  • Oracle n’a pas de type boolean
    • émulé de diverses manières
  • Attention aux ORM (Hibernate) suite à la migration de données
    • ils chercheront un boolean sous PostgreSQL alors que vous aurez migré un int

Différences sur les types binaires

  • 2 implémentations différentes sous PostgreSQL
    • large objects et fonctions lo_*
    • bytea

Différences sur les types dates

  • PostgreSQL :
    • date : jour
    • time : heure seule
    • timestamp : date + heure (alias timestamptz)
  • Fuseaux horaires
    • YYYY-MM-DD HH24:MI:SS.mmmmmmm+TZ (conforme SQL)
  • Type interval sous PostgreSQL

Différences sur les fonctions temporelles

  • SYSDATE
    • retourne la date et l’heure courante, sans timezone
    • équivalent avec PostgreSQL : localtimestamp
  • PostgreSQL ne propose pas de fonctions add_months, etc.
  • NLS_DATE_FORMAT (TO_CHAR et TO_DATE)
    • configuration DateStyle

Différences sur les types spécialisés

PostgreSQL fournit de nombreux types de données spécialisés :

  • timestamps et intervalles, avec opérations arithmétiques
  • Adressage IP (CIDR), avec opérateurs de masquage
  • Grande extensibilité des types : il est très facile d’en rajouter un nouveau
    • PERIOD
    • ip4r
  • Nombreuses extensions
    • PostGIS

Différences de syntaxes

  • Expressions conditionnelles DECODE et NVL
  • Concaténation de chaînes avec NULL
  • Pseudo-colonne ROWNUM
  • Jointures
  • Opérateurs ensemblistes
  • Hiérarchies

DECODE

Équivalent de la clause CASE du standard

CASE expr
  WHEN valeur1 THEN valeur_retour1
  WHEN valeur2 THEN valeur_retour2
  ELSE valeur_retour3
END
CASE
  WHEN expr1 THEN valeur_retour1
  WHEN expr2 THEN valeur_retour2
  ELSE valeur_retour3
END

NVL

  • Retourne le premier argument non NULL
SELECT NVL(description, description_courte, '(aucune)') FROM articles;
  • Équivalent de la norme SQL : COALESCE
SELECT COALESCE(description, description_courte, '(aucune)') FROM articles;

Concaténation avec NULL

Changement de comportement de l’opérateur ||

  • Rappel : pour Oracle, NULL équivaut à une chaîne vide ''
  • Pour PostgreSQL : SELECT 'chaîne' || NULL équivaut à NULL
  • Réécritures possibles :
SELECT 'chaîne' || COALESCE(null, '');
SELECT CONCAT('chaîne', null);

ROWNUM

  • Pseudo-colonne Oracle
  • Numérote les lignes du résultat
    • parfois utiliser pour limiter le résultat

Numéroter les lignes

  • ROWNUM n’existe pas dans PostgreSQL
    • row_number() OVER ()
    • attention si ORDER BY

Limiter le résultat

  • Retourne les dix premières lignes de résultats :
    • WHERE ROWNUM < 11
  • PostgreSQL propose l’ordre LIMIT xx :
SELECT *
  FROM employees
 LIMIT 10;

ROWNUM et ORDER BY

  • Oracle effectue le tri après l’ajout de ROWNUM
  • PostgreSQL applique le tri avant de limiter le résultat
  • Résultats différents

Jointures

  • Jointures internes
    • FROM tab1, tab2 WHERE tab1.col = tab2.col
    • FROM tab1 JOIN tab2 ON (tab1.col = tab2.col)

Jointures externes

  • Syntaxe (+) d’Oracle historique
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN

Produit cartésien

  • FROM t1, t2;
  • FROM t1 CROSS JOIN t2

Opérateurs ensemblistes

  • UNION / UNION ALL
  • INTERSECT
  • EXCEPT
    • équivalent de MINUS

Hiérarchies

  • Explorer un arbre hiérarchique
    • CONNECT BY Oracle
    • WITH RECURSIVE PostgreSQL

Syntaxe CONNECT BY

  • START WITH
    • condition de départ
  • CONNECT BY PRIOR
    • lien hiérarchique
SELECT empno, ename, job, mgr
  FROM emp
  START WITH mgr IS NULL
  CONNECT BY PRIOR empno = mgr

WITH RECURSIVE

WITH RECURSIVE hierarchie AS (
condition de départ
UNION ALL
clause de récursion
)
SELECT * FROM hierarchie

Niveau de hiérarchie

  • LEVEL donne le niveau de hiérarchie
  • Condition de départ
   1 AS level
  • Clause de récursion
   prior.level + 1

Chemin de hiérarchie

  • niveau 1/niveau 2/niveau 3
  • Condition de départ
    • niveau initial AS path
  • Clause de récursion
    • concatène le niveau précédent avec le path
    • prior.path || niveau courant

Détection des cycles

  • Équivalent de NOCYCLE
  • Tableau contenant les éléments
    • pseudo-colonne cycle
    • element = ANY (tableau) AS cycle
    • WHERE cycle = false

Common Table Expressions

  • Syntaxe quasiment identique
  • Attention à la récursion
    • WITH RECURSIVE obligatoire dans PostgreSQL

Transactions

  • Les transactions ne sont pas démarrées automatiquement
    • BEGIN
    • sauf avec JDBC (BEGIN caché)
  • Toute erreur non gérée dans une transaction entraîne son annulation
    • Oracle revient à l’état précédent de l’ordre en échec
    • PostgreSQL plus strict de ce point de vue
  • DDL transactionnels

Niveaux d’isolation

  • BEGIN TRANSACTION ISOLATION LEVEL xxxx
    • READ COMMITTED
    • REPEATABLE READ
    • SERIALIZABLE

SAVEPOINT

  • SAVEPOINT
  • RELEASE SAVEPOINT
  • ROLLBACK TO SAVEPOINT

Verrous explicites

  • SELECT FOR SHARE/UPDATE
    • quelques subtilités
  • LOCK TABLE

Conclusion

  • Oracle et PostgreSQL répondent à la même norme ISO SQL
    • … mais la conversion des requêtes SQL sera nécessaire
    • le typage des données est bien plus fourni avec PostgreSQL
  • Pour détecter les erreurs de syntaxe :
    • faire fonctionner l’application
    • repérer tous les ordres SQL en erreur (traces applicatives)
    • les corriger
  • Attention aux mots réservés

Questions

N’hésitez pas, c’est le moment !

Quiz

Travaux pratiques

Travaux pratiques (solutions)

Procédures stockées

Introduction

Oracle et PostgreSQL n’ont pas le même langage PL :

  • Oracle : PL/SQL et Java
  • PostgreSQL : PL/pgSQL, PL/Java, PL/Perl, PL/Python, PL/R…

Sommaire

Ce module est organisé en cinq parties :

  • Outils et méthodes
  • Différences dans le code
  • Conversion automatique
  • Migration des procédures stockées
  • Tests et validation

Outils et méthodes

  • Outils d’émulation de fonctionnalités Oracle
  • Outils de conversion de code PL/SQL vers PL/pgSQL
  • Outils de débogage du code PL/pgSQL

Les outils d’émulation

  • Orafce :
    • nombreuses fonctions de compatibilité Oracle
    • to_char(1 param), add_month(), decode()
    • DBMS_ALERT, DBMS_PIPE, DBMS_OUTPUT, DBMS_RANDOM et UTL_FILE
  • Migration Tool Kit :
    • réservé à EDB PostgreSQL Plus Advanced Server Migration
    • ne convertit pas le code PL/SQL

Les outils de conversion

  • Ora2pg
    • convertisseur de code PL/SQL en PL/pgSQL sous licence GPL
    • seul outil libre

Les outils de débogage

  • pldebugger (ex edb-debugger)
  • plpgsql_check
  • SQLMaestro

Différences dans le code

  • Généralité
  • Triggers
  • Routines
  • Packages

Généralité - 1

  • nom_sequence.nextval => nextval('nom_sequence')
  • Pas de transaction autonome à moins de passer par dblink ou pg_background
  • RETURN => RETURNS
  • EXECUTE IMMEDIATE => EXECUTE
  • SELECT sans INTO => PERFORM

Généralité - 2

  • REVERSE LOOP => inversion des bornes
  • Une fonction doit avoir un langage
  • CONNECT BY n’existe pas, utiliser WITH RECURSIVE
  • REF CURSOR doit être remplacé par REFCURSOR
  • nom_curseur%ROWTYPE doit être remplacé par RECORD
  • BULK COLLECT => Array
  • Les chaînes vides sont équivalentes à NULL sous Oracle

Triggers

  • Ils doivent être séparés en fonction et trigger
  • :NEW et :OLD => NEW et OLD
  • UPDATING, INSERTING, DELETING => TG_OP (UPDATE, INSERT, DELETE)
  • RETURN NEW impératif dans les triggers BEFORE, retour implicite sous Oracle

Routines

  • PostgreSQL supporte les fonctions et procédures stockées
    • uniquement les fonctions pour les versions 10 ou inférieures
  • Il doit toujours y voir des parenthèses pour la liste des paramètres, même si elle est vide
  • Les valeurs par défaut sont aussi autorisées
  • PostgreSQL peut retourner un pseudo type RECORD, correspondant à un enregistrement
    • sous Oracle, il faut soit utiliser une référence de curseur soit définir une TABLE FUNCTION

Packages

  • Paquet de variables et de procédures stockées
    • pas d’équivalent sous PostgreSQL
  • Utilisation d’un schéma pour émuler les appels aux fonctions
    • nom_paquet.nom_fonction
  • Variables globales non supportées
    • utiliser des tables ou des paramètres de configuration (GUC)
  • Les définitions de fonctions à l’intérieur du code d’une fonction ne sont pas supportées

Conversion automatique du code

  • Paquets de procédure stockées
  • En-têtes et paramètres des triggers, fonctions etc.
  • Types des données
  • Fonctions
  • Modification de syntaxe

Conversions globales

  • Les PACKAGES ou paquets de procédures stockées
  • Les déclarations de triggers et routines
  • Les paramètres des routines
  • La conversion des types de variable

Correspondance des fonctions - 1

Les noms diffèrent :

  • NVL() => coalesce()
  • SYSDATE => LOCALTIMESTAMP
    • équivalent de CURRENT_TIMESTAMP sans le fuseau horaire
  • NLSSORT(colname, 'nls_sort=GERMAN') => colname COLLATE "de_DE"

Correspondance des fonctions - 2

Les paramètres changent :

  • to_number(num)
    • => to_number(num, '99...99D99...99')
  • to_date( string1, format_mask, nls_language)
    • => to_date(text, text)
  • replace(a, b)
    • => replace(a, b, ' ')

Correspondance des fonctions - 3

Les noms et les paramètres changent :

trunc(.*date.*)
 => date_trunc('day', ...date...)
substr( string, start_position, length )
 => substring(string from start_position for length)

Correspondance des fonctions - 4

La réécriture est complète :

add_months
 =>+ 'N months'::interval
add_years
 =>+ 'N year'::interval
TO_NUMBER(TO_CHAR(…))
 => to_char(…)::integer
decode("user_status",'active',"username",null)
 => (CASE WHEN user_status='active' THEN username ELSE NULL END)

Réécriture de parties de code - 1

  • Réécrit les appels aux séquences
    • nom.nextval => nextval('nom')
    • nom.currval => currval('nom')
  • Remplace les appels :new. en NEW. et :old. en OLD. dans les triggers
  • Remplace INSERTING|DELETING|UPDATING en TG_OP='INSERT|DELETE|UPDATE' dans les fonctions de trigger

Réécriture de parties de code - 2

  • Supprime le caractère : devant les noms de variable Oracle
  • Convertit les sorties Oracle DBMS_OUTPUT.(put_line|put|new_line)(...) en RAISE NOTICE '...'
  • Inversement des bornes min et max dans les boucles FOR … IN … REVERSE min .. max
  • Réécrit les RAISE EXCEPTIONavec concaténation || par le format à la sprintf utilisé par PostgreSQL

Réécriture de parties de code - 3

  • Remplacement des ROWNUM dans la clause where par des clauses LIMIT et/ou OFFSET
  • Réécrit la clause HAVING … GROUP BY (variante acceptée par Oracle mais pas PostgreSQL) en GROUP BY … HAVING
  • Remplace les appels à MINUS par EXCEPT

Réécriture de parties de code - 4

  • Supprime les appels à FROM DUAL
  • Supprime les DEFAULT NULL qui est la valeur par défaut sous PostgreSQL lorsqu’aucune valeur par défaut n’est précisée
  • Suppression des noms d’objets répétés après les END, exemple : END fct_name; est réécrit en END;

Réécriture de parties de code - 5

  • Déplacement des commentaires dans les CASE entre le WHEN et le THEN, non supporté par PostgreSQL
  • Remplacement des conditions IS NULL et IS NOT NULL par des instructions à base de coalesce (pour Oracle, une chaîne vide est équivalente à NULL)
  • Inverse les déclarations de curseur CURSOR moncurseur; pour les rendre compatibles avec PostgreSQL : moncurseur CURSOR;

Réécriture de parties de code - 6

  • Supprime le mot clé IN de la déclaration des curseurs.
  • Remplacement des sorties de curseur EXIT WHEN ...%NOTFOUND par IF NOT FOUND THEN EXIT; END IF;
  • Ajout du mot clé STRICT aux SELECT … INTO lorsqu’il y a EXCEPTION … NO_DATA_FOUND ou TOO_MANY_ROWS

Réécriture de parties de code - 7

  • Remplacement des REGEX_LIKE( string, pattern ) en syntaxe avec l’opérateur PostgreSQL de recherche regex string ~ pattern.
  • Remplacement des appels aux variables d’environnement SYS_CONTECT('USERENV', ...) en équivalent PostgreSQL.
  • Remplacement des fonctions spatiales SDO_GEOM.* en appels aux fonctions PostGis équivalentes.
  • Remplacement des opérateurs géométriques SDO_* en opérateurs correspondants PostGis.

Remplacement concernant les exceptions

Remplacement de :

  • STORAGE_ERROR par OUT_OF_MEMORY
  • ZERO_DIVIDE par DIVISION_BY_ZERO
  • INVALID_CURSOR par INVALID_CURSOR_STATE
  • SQLCODE par le presque équivalent SQLSTATE sous PostgreSQL
  • raise_application_error en RAISE EXCEPTION

Remplacement d’autres mots clés

Remplacement de :

  • SYS_REFCURSOR par REFCURSOR
  • SQL%NOTFOUND par NOT FOUND
  • SYS_EXTRACT_UTC par AT TIME ZONE 'UTC
  • dup_val_on_index en unique_violation

Migration des procédures stockées

Étapes :

  • Cas des procédures avec transactions autonomes
  • Import des fonctions et paquets de fonctions
  • Absence de fonctions ou paquets

Cas des transactions autonomes

Non supportées nativement par PostgreSQL, Ora2Pg utilise une fonction de substitution :

  • La fonction d’origine est renommée avec le suffixe _atx
  • La fonction de substitution prend le nom originel de la fonction
  • La fonction de substitution appelle la fonction _atx au travers d’un dblink
  • Utilisation possible de l’extension pg_background à partir de PostgreSQL 9.5 et Ora2Pg 17.5

Import des procédures et paquets avec Ora2Pg

Chargement des fonctions et procédures :

psql --single-transaction -U myuser -f schema/functions/functions.sql mydb
psql --single-transaction -U myuser -f schema/procedures/procedures.sql mydb

Chargement des paquets de procédures stockées :

psql --single-transaction -U myuser -f schema/packages/packages.sql mydb

Code non exporté

Absence de certaines fonctions ou paquets de fonctions dans l’export

  • Le code a été invalidé par Oracle
  • Activer COMPILE_SCHEMA
  • Activer EXPORT_INVALID

Certains commentaires des paquets de fonctions ne sont pas importés

Tests et validation

Valider le portage du code :

  • Fonctionnement à l’identique
  • Possibilité de résultats différents
  • Déboguer le code PL/pgSQL et comparer avec le code source
  • Ne pas oublier le test des scripts ou jobs externes

Ora2Pg : tests intégrés

Deux actions permettent de tester à minima :

  • TEST : compare le nombre d’objets et de lignes des deux bases.
    • ora2pg -c config/ora2pg.conf -t TEST
  • TEST_VIEW : compare le nombre de lignes retounées par les vues.
    • ora2pg -c config/ora2pg.conf -t TEST_VIEW
  • Dans les deux cas PG_DSN doit être positionné.

Outils de tests unitaires pour PostgreSQL

Plans de tests complets

  • Tests sur la base de données
  • Tests sur l’application
  • Tests sur les performances
  • Stress test
  • Tests des scripts de maintenance et job

Conclusion

  • La conversion automatique fait gagner du temps
  • Mais les réécritures manuelles peuvent s’avérer nombreuses
  • La phase de tests est la plus importante de la migration

Questions

N’hésitez pas, c’est le moment !

Quiz

Travaux pratiques

Travaux pratiques (solutions)