Ce module est organisé en quatre parties :

• Méthodologie de la migration
• Recommandations et pièges à éviter
• Migration des données
• Fonctionnalités problématiques

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

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

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

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

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

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

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

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

Souvent moins important:

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

• Bugdet ?
• Open source <> gratuit
  • Coûts humains
  • Coûts matériels

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

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

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
• Ce document ne peut pas être exhaustif !

PostgreSQL et Oracle :

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

• Les transactions ne sont pas démarrées implicitement sous PostgreSQL
  • 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

• 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

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

• Pas de varchar2 dans PostgreSQL
  • le type est varchar
• Attention, sous Oracle, '' = IS NULL
  • sous PostgreSQL, ''et NULL sont distincts
• varchar peut ne pas prendre de taille sous PostgreSQL
  • 1 Go maximum dans ce cas
• Il existe aussi un type text équivalent à varchar sans taille
• Un seul encodage par base
• Collationnements
  • Par instance (avant la 8.4), par base de données (depuis la 8.4), par colonne (depuis la 9.1)

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

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

• Gestion des timestamps et intervals avec opérations arithmétiques
• Plans d'adressage IP (CIDR) et opérateurs de masquage
• Grande extensibilité des types: il est très facile d'en rajouter un nouveau
  • PERIOD
  • ip4r
  • etc.

• Date
  • sous Oracle : YYYY/MM/DD HH:MM:SS
  • sous PostgreSQL: YYYY-MM-DD (conforme SQL)
• Time
  • sous Oracle : YYYY/MM/DD HH:MM:SS
  • sous PostgreSQL : HH:MM:SS.mmmmmmm (µs)
• Gestion des fuseaux horaires
  • sous PostgreSQL, par défaut
  • timestamp sous PostgreSQL : Date+Time (+TZ)
• Format de sortie conforme SQL sous PostgreSQL :

YYYY-MM-DD HH24:MI:SS.mmmmmmm+TZ

• Type interval
  • Date1-Date2 => Interval

Oracle : nombreuses extensions incompatibles avec PostgreSQL :

• jointure (+)
  • réécrite en LEFT JOIN (standard SQL)
• CONNECT BY
  • réécrite avec WITH RECURSIVE (standard SQL)
• Nombreuses fonctions telles que NVL
  • COALESCE, CASE (standard SQL)

• 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.

• Il est très difficile de détecter tous les problèmes de langage SQL
• Le plus simple est :
  • de faire fonctionner l'application
  • de repérer tous les ordres SQL en erreur (ils sont tracés dans les journaux applicatifs)
  • de les corriger
• Attention aux mots réservés

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…

• PL/PgSQL est conçu pour être ressemblant à PL/SQL
  • Pas de package
  • Compilé à la première exécution, pas de façon globale
  • Pas de transaction autonome
  • Pas de fonctionnalités comme les directories : PL/PgSQL ne manipule pas de fichiers
  • Les autres langages PL comblent ce manque (et plus)
• Le gros du travail de portage !

Avant de pouvoir porter l'application et le PL :

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

• Veut-on migrer le schéma tel quel ?
• Utiliser les fonctionnalités de PostgreSQL ?
  • N'est plus vraiment iso-fonctionnalité
• Créer un nouveau schéma :
  • D'un coup
  • Les tables d'abord, les index et contraintes ensuite ?

• On rencontre souvent les types suivant 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

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

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

Veut-on :

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

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

• 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)

• Big-Bang
  • pas d'incrémental

• 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 -> PLpgSQL
  • au partitionnement (par héritage, dans PostgreSQL)

• 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…)

• 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

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

• Vues matérialisées
• Partitionnement
• Synonyme
• Conversion de type implicite
• Absence de hint (tag de requête)

• Sous Oracle :
  • Stocke le résultat d'une vue physiquement (table)
  • Permet la création d'index sur cette table
  • Est mise à jour au fil de l'eau ou à intervalle régulier
  • Réécriture transparente de requêtes
• Sous PostgreSQL :
  • N'existe qu'à partir de la version 9.3
  • Mise à jour uniquement sur demande
  • Pas de réécriture

Certaines choses peuvent être émulées :

• Vues matérialisées à mise à jour synchrone
  • utilisation d'un trigger
• Vues matérialisées à mise à jour asynchrone
  • tracer les changements, et appliquer à intervalle régulier (trigger + fonction)
• Vues matérialisées reconstruites à intervalle régulier
  • réexécuter un CREATE TABLE AS SELECT régulièrement
• Réécriture automatique des requêtes
  • pas de solution
  • réécriture manuelle des requêtes

Pas d'équivalent simple sous PostgreSQL :

CREATE TABLE sales (year numeric(4),
                    product varchar(10),
                   amt numeric(10,2));
CREATE TABLE sales_y1992 (
    CHECK ( year = 1992 )
) INHERITS (sales);
CREATE TABLE sales_y1993 (
    CHECK ( year = 1993 )
) INHERITS (sales);
…

• Triggers pour envoyer les enregistrements dans la bonne partition
• Pas de contrainte d'unicité globale
• Donc pas de clé primaire
• Donc pas de clé étrangère pointant sur la table partitionnée

Points essentiels :

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

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

Ce module est organisé en quatre parties :

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

Étapes :

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

• Disponible via :
  • HTTP : http://ora2pg.darold.net/
  • Git : https://github.com/darold/ora2pg
• Télécharger le fichier ora2pg-X.Y.tar.bz2
• Version au 13/01/2016 : 16.2

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

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

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

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]

É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

• 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é

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

• ORACLE_DSN
  • dbi:Oracle:host=serveur;sid=INSTANCE
• ORACLE_USER
  • system
• ORACLE_PWD
  • manager
• SCHEMA
  • NOM_SCHEMA versus SYSUSERS
• USER_GRANTS [0|1]
  • l'utilisateur Oracle a-t-il les droits DBA ?

Cette étape de validation de la configuration permet d'obtenir des informations sur la base Oracle :

• Liste des tables et colonnes
• Recherche de l'encodage de la base
• Création d'un rapport de migration
• Estimation du coût de migration

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.

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...

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

• Rapport exhaustif du contenu de la base Oracle

  ora2pg -t SHOW_REPORT
  ora2pg -t SHOW_REPORT --dump_as_html

• Estimation du coût de migration

  ora2pg -t SHOW_REPORT --estimate_cost
  ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html

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

Utilisation de fichiers de sortie dédiés

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

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

Utilisation d'un schéma sous PostgreSQL ?

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

• PostgreSQL >= 9.1
  • PG_SUPPORTS_INSTEADOF 1
  • STANDARD_CONFORMING_STRINGS 1
  • PG_SUPPORTS_IFEXISTS 1
• PostgreSQL >= 9.3
  • PG_SUPPORTS_MVIEW 1
• PostgreSQL >= 9.4
  • PG_SUPPORTS_CHECKOPTION 1
  • BITMAP_AS_GIN 1

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

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

• LONGREADLEN       8192
• LONGTRUNCOK          0
• NO_LOB_LOCATOR       0
• BLOB_LIMIT         500

É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

• 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

• 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 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

• 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 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

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 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 :

ora2pg -t PARTITION -o partitions.sql -b schema/partitions -c config/ora2pg.conf

Depuis PostgreSQL 9.3 :

• PG_SUPPORT_MVIEW 1

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

  Avant la 9.3 :

• utilisation de fonctions, table et vues dédiées

PostgreSQL ne possède pas d'objet de type SYNONYM :

• Ce sont des alias vers des objets d'autres schéma 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

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é 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

• 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

• 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)

Oracle Index Texte

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

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 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

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

Chargements différé de certains objets :

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

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

• 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);

É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

• 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

• 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

• 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 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, 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

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è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

• 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

• 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

• 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]

• 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

• Documentation officielle
• Autres sources d'informations

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

Ce module est organisé en cinq parties :

1. Outils et méthodes
2. Différences de syntaxes
3. Conversion automatique
4. Migration des procédures stockées
5. Tests et validation

• 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

• 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

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

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

• Différences au niveau du schéma
• Différences de type de données
• Différences dans le code

• Le schéma sous Oracle : USER.OBJECT
  • sous PostgreSQL, véritable espace de nommage
• Oracle convertit les noms d'objet en majuscule : NOM_TABLE
  • PostgreSQL les convertit en minuscule : nom_table
• Les types de données doivent être redéfinis (NUMBER(p,s), CLOB, etc.)
• Les synonymes n'existent pas
  • utiliser des vues si nécessaire.
• Les vues matérialisées existent depuis PosgreSQL 9.3
  • mais manquent encore de fonctionnalités

• 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 partitions n'existent pas directement
  • héritage, trigger et contraintes check
• Les colonnes virtuelles non plus
  • utilisation de vues

• 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'extension (dblink ou fdw).

Opérations sur les dates :

• DATE + NUMBER
  • => DATE + interval '1 jours'
• TIMESTAMP - TIMESTAMP = NUMBER
  • Sous PostgreSQL => interval
• NLS_DATE_FORMAT (TO_CHAR et TO_DATE)
  • => DateStyle

Pas de conversion implicite vers et depuis les types chaînes de caractères :

SELECT * FROM depts WHERE numero BETWEEN 0 AND 42;

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

• 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

• 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

• PostgreSQL n'a que des fonctions
  • une procédure retourne VOID
• 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

• Paquet de variables et de procédures stockées
• 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 variables custom
• Les définitions de fonctions à l'intérieur du code d'une fonction ne sont pas supportées

• Left outer join :

  SELECT * FROM a, b WHERE a.id = b.id (+)
  => SELECT * FROM a LEFT OUTER JOIN b ON (a.id = b.id)

• Right outer join :

  SELECT * FROM  a, c WHERE a.id (+) = c.id
  => SELECT * FROM a RIGHT OUTER JOIN c ON (a.id = c.id)

Full outer join :

SELECT * FROM a, b WHERE a.id = b.id (+)
UNION ALL SELECT * FROM a, b WHERE a.id (+) = b.id AND a.id = NULL
=> SELECT * FROM a FULL OUTER JOIN b ON (a.id = b.id)

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

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

Les noms diffèrent :

• NVL() => coalesce()
• SYSDATE => LOCALTIMESTAMP
  • équivalent de CURRENT_TIMESTAMP sans le fuseau horaire

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, ' ')

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)

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éé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 fonction de trigger

• Supprime le caractère ':' devant les nom de variable Oracle
• Remplace 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 EXCEPTION avec concaténation || par le format à la sprintf utilisé par PostgreSQL

• 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
• Ajout du mot clé STRICT aux SELECT … INTO lorsqu'il y a EXCEPTION … NO_DATA_FOUND ou TOO_MANY_ROWS
• Remplace les appels à MINUS par EXCEPT

• 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;

• 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;

• 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 dans les requêtes SELECT ... INTO ... si une exception sur NO_DATA_FOUND ou TOO_MANY_ROW est levée

• 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 fonction PostGis équivalentes.
• Remplacement des opérateurs géométriques SDO_* en opérateurs correspondants PostGis.

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 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

Étapes :

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

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

• La fonction d'origine est renommée avec le suffix _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.

Chargement des fonctions et procédures :

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

Chargement des paquets de fonctions :

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

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

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

• pgTap
  • http://www.pgtap.org/
• pgUnit
  • http://en.dklab.ru/lib/dklab_pgunit/
  • http://pgfoundry.org/projects/pgunit/
• Epic
  • http://www.epictest.org/

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

• 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.

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

• Portage des requêtes SQL
  • élément commun: SQL
  • dialecte Oracle

• Langage SQL
  • norme ISO
  • dernière version 2011
• La façon d'écrire les requêtes ne change pas
  • sauf certains détails

Table DUAL pas nécessaire

SELECT fonction();

SELECT current_timestamp;

• Conversions implicites de et vers un type text
  • supporté par Oracle
  • plus supporté par PostgreSQL depuis la version 8.3

SELECT 1 = 'a'::text;

• 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

• 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

• Pas de varchar2 dans PostgreSQL
  • le type est varchar
• Attention, sous Oracle, '' = IS NULL
  • sous PostgreSQL, '' et NULL sont distincts
• varchar peut ne pas prendre de taille sous PostgreSQL
  • 1 Go maximum dans ce cas
• Il existe aussi un type «text» équivalent à varchar sans taille
• Un seul encodage par base
• Collationnements
  • Par instance (avant la 8.4), par base de données (depuis la 8.4), par colonne (depuis la 9.1)

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

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

• Gestion des timestamps et intervals avec opérations arithmétiques
• Plans d'adressage IP (CIDR) et opérateurs de masquage
• Grande extensibilité des types: il est très facile d'en rajouter un nouveau
  • PERIOD
  • ip4r
  • etc…

• Types différents
• Fonctions équivalentes à SYSDATE
• Fonctions de manipulation différentes

• Date
  • sous Oracle: YYYY/MM/DD HH:MM:SS
  • sous PostgreSQL: YYYY-MM-DD (conforme SQL)
• Time
  • sous Oracle: YYYY/MM/DD HH:MM:SS
  • sous PostgreSQL: HH:MM:SS.mmmmmmm (µs)

• Gestion des fuseaux horaires
  • sous PostgreSQL, par défaut
  • timestamp sous PostgreSQL: Date+Time (+TZ)

• Format de sortie conforme SQL sous PostgreSQL:

  YYYY-MM-DD HH24:MI:SS.mmmmmmm+TZ

• Type interval
  • Date1-Date2 => Interval

• SYSDATE
  • retourne la date et l'heure courante, sans timezone

  • équivalent direct :

    SELECT localtimestamp;

• PostgreSQL implémente d'autres fonctions :
  • current_timestamp
  • current_date
  • current_time

• PostgreSQL ne propose pas de fonctions add_months, etc.

  SELECT current_date + interval '3 days';

  SELECT current_date + interval '1 days' * 3;

  SELECT (now() - '2014-01-01') * 2 + now()

• DECODE
• NVL

É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

• 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;

• Syntaxe quasiment identique
• Attention à la recursion
  • WITH RECURSIVE obligatoire dans PostgreSQL

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

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

• Retourne les dix premières lignes de résultats :
  • WHERE ROWNUM < 11
• PostgreSQL propose l'ordre LIMIT xx :

SELECT *
  FROM employees
 LIMIT 10;

• 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 internes
  • FROM tab1, tab2 WHERE tab1.col = tab2.col
  • FROM tab1 JOIN tab2 ON (tab1.col = tab2.col

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

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

• Oracle permet GROUP BY après HAVING
• PostgreSQL impose GROUP BY avant HAVING

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

• 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

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

• SAVEPOINT
• RELEASE SAVEPOINT
• ROLLBACK TO SAVEPOINT

• PostgreSQL pose un verrou sur les objets accédés
  • y compris en SELECT

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

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

• 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

• LEVEL donne le niveau de hiérarchie

• condition de départ

  1 AS level

• clause de récursion

  prior.level + 1

• 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

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

• Certaines fonctionnalités Oracle sont sans équivalent.
  • hints
  • accès par ROWID

• Forcer un plan d'exécution
• N'existent pas dans PostgreSQL

• localisation physique d'une ligne dans une table.
  • il existe un équivalent dans PostgreSQL
  • mais c'est à proscrire