Comment migrer d'Oracle vers PostgreSQL

Ce livre blanc couvre l'expérience de migration de bases de données d'Oracle vers PostgreSQL réalisée par Intelligent Converters pour une grande entreprise de livraison de fret. La principale raison de la migration était la réduction du coût du système de gestion de base de données et le transfert vers un système open source avec des capacités d'évolutivité, de sécurité et de personnalisation suffisantes.

Voici une brève spécification de la base de données source :

• Serveur de base de données Oracle 12g

• La base de données contient 190 tables, 50 Go de données

• 120 000 lignes de PL/SQL dans les procédures stockées, les fonctions et les déclencheurs

  
  

La durée du projet était de 2 mois : un demi-mois d'évaluation et de planification, un mois pour la migration et un demi-mois pour les tests. Il y avait 3 spécialistes impliqués dans la migration : 2 développeurs et 1 administrateur de base de données.

Cette migration de base de données Oracle vers PostgreSQL comprend six phases de base :

1. Trouver toutes les méthodes spécifiques à Oracle de stockage et de traitement des données dans la base de données source et le périmètre d'utilisation (phase d'investigation et de planification)
2. Sélectionnez les outils appropriés pour la migration de schéma et implémentez-les
3. Choisissez la méthode de migration de données la plus appropriée pour réduire les temps d'arrêt du système Oracle
4. Exécutez la migration des données en gérant toutes les transformations requises par le SGBD PostgreSQL
5. Convertir tout le code PL/SQL au format PostgreSQL (à l'aide d'outils d'automatisation partielle et de post-traitement manuel)
6. Exécuter des tests de performance et fonctionnels, affiner la base de données résultante

Migration des définitions de table

Certains types de données dans Oracle n'ont pas d'équivalent direct dans PostgreSQL. L'un de ces types est DATE contenant à la fois des parties de date et d'heure.

PostgreSQL prend en charge :

• date - date pure sans partie horaire
• heure - heure pure sans partie de date avec spécification optionnelle de fuseau horaire
• horodatage - date et heure avec spécification facultative de fuseau horaire

Il existe deux options pour mapper les dates Oracle dans PostgreSQL : soit utiliser TIMESTAMP, soit configurer l'extension orafce pour utiliser le type de date de style Oracle oracle.date.

Les types spatiaux nécessitent également une attention particulière. Oracle a le type intégré SDO_GEOMETRY tandis que PostgreSQL a besoin de l'installation de PostGIS pour fonctionner avec des types de données spatiales.

Le tableau ci-dessous illustre le mappage des types sécurisés Oracle vers PostgreSQL :

Pour les types numériques, il est important de comprendre la portée de l'utilisation dans la base de données. S'il est axé sur la précision, les types numériques Oracle doivent être mappés dans PostgreSQL NUMERIC. Si la priorité absolue est la vitesse de calcul, la meilleure cartographie serait REAL ou DOUBLE PRECISION.

Nous avons utilisé le convertisseur Oracle vers PostgreSQL pour automatiser la migration des définitions de table, des index et des contraintes pour ce projet. Il mappe les types Oracle dans les équivalents PostgreSQL les plus appropriés et permet de personnaliser le mappage de type particulier.

Migration de données

Étant donné que la migration des données peut prendre beaucoup de temps pour les grandes bases de données, il est extrêmement important de choisir la bonne stratégie et les bons outils pour cette étape.

Il existe trois approches courantes pour la migration des données :

• instantané - migrer toutes les données en une seule étape
• instantané par morceaux - migrer les données par morceaux dans des threads ou des processus parallèles
• modifier la réplication des données - chargement continu des données en suivant les modifications incrémentielles

La méthode d'instantané nécessite un temps d'arrêt essentiel de la base de données source pendant toute la période de lecture des données pour éviter la perte ou la corruption des données pendant la migration. Le temps d'arrêt pour l'approche d'instantané par morceaux est beaucoup moins mais toujours nécessaire. Dans ce projet de migration, nous avons utilisé la méthode d'instantané par morceaux pour la migration de grandes tables contenant des millions de lignes.

Alors, qu'en est-il de la technique de réplication des données modifiées (CDR) ?

Il a deux implémentations majeures et chacune d'elles a sa faiblesse. Le premier est connu sous le nom de CDR basé sur des déclencheurs. Cela nécessite la création de déclencheurs lors de l'insertion, des mises à jour et de la suppression pour chaque table répliquée dans la base de données source. Ces déclencheurs suivent tous les changements en stockant des informations sur les événements changeants dans une table spéciale "historique". Sur la base de ces données, l'outil CDR réplique toutes les modifications dans la base de données cible. Le CDR basé sur les déclencheurs entraîne une surcharge supplémentaire de la base de données source en raison de l'écriture dans la table « historique » pour chaque mise à jour des données.

La deuxième méthode est appelée Transaction Log CDR. Il utilise les journaux de transactions créés par Oracle DBMS pour suivre et répliquer les modifications dans la base de données PostgreSQL cible. L'avantage de cette approche par rapport à la CDR basée sur des déclencheurs est qu'elle ne modifie pas la base de données source.

Cependant, Transaction Log CDR présente également certaines faiblesses :

• Oracle archive constamment les fichiers du journal des transactions, de sorte que l'outil CDC peut perdre certaines modifications s'il ne lit pas le journal des transactions avant son archivage.
• En cas de perte de connexion à la base de données cible lors de la réplication des modifications du journal des transactions (ou de tout autre type d'erreur), les données peuvent être perdues ou endommagées en raison d'un manque de contrôle sur le journal des transactions.

Les aspects les plus difficiles de la migration de données d'Oracle vers PostgreSQL sont : les formats de données spécifiques du SGBD source n'ayant pas d'équivalent direct dans la cible et les données externes.

Dans la section précédente de cet article, BYTEA est indiqué comme le type de données PostgreSQL le plus approprié pour les données binaires. Cependant, lors de la migration de données binaires volumineuses (la taille moyenne des champs n'est pas inférieure à 10 Mo), il n'est pas recommandé d'utiliser BYTEA. La raison est la particularité de lire les données BYTEA - elles ne peuvent être extraites que dans un fragment ; la lecture par morceaux n'est pas possible. Cela peut entraîner une surcharge de RAM essentielle. Un LARGE OBJECT de type PostgreSQL peut être utilisé comme solution de contournement pour ce problème. Toutes les valeurs de type LARGE OBJECT sont stockées dans la table système 'pg_largeobject' qui fait partie de chaque base de données. Il peut y avoir jusqu'à 4 milliards de lignes dans la table 'pg_largeobject'. La taille maximale de LARGE OBJECT est de 4 To et la lecture par morceaux est disponible.

De plus, il est important de migrer correctement le ROWID qui est une pseudo-colonne identifiant l'adresse physique de l'enregistrement dans la table. PostgreSQL a un champ de service similaire appelé ctid, mais c'est un équivalent direct de ROWID. La documentation PostgreSQL indique que ctid peut être modifié en raison de la procédure de vidage.

Il existe trois méthodes de base pour émuler la fonctionnalité ROWID dans PostgreSQL :

• Utiliser la clé primaire existante (ou en créer une nouvelle) pour identifier les lignes au lieu de ROWID
• Ajoutez une colonne serial ou bigserial avec des valeurs générées automatiquement et faites-en une clé primaire/unique pour remplacer la fonctionnalité ROWID
• Lorsqu'il n'est pas possible de définir une seule clé primaire de colonne, utilisez un index unique construit sur plusieurs colonnes (à cet effet, déterminez un ensemble minimal de champs uniques pour chaque ligne)

Oracle peut lier des données externes qui sont stockées en dehors de la base de données. Cependant, il peut être traité comme une table normale à l'aide de la fonction "table externe" du SGBD. PostgreSQL utilise la bibliothèque Foreign Data Wrapper dans le même but. Par exemple, il fournit l'extension 'file_fdw' pour travailler avec des fichiers CSV externes comme une table normale.

Défis de la migration PL/SQL

Cette section explore les problèmes qui ont été résolus lors de la migration des packages Oracle, stocke les procédures, les fonctions et les déclencheurs vers PostgreSQL.

Paquets. PostgreSQL n'a pas de packages. Cette fonctionnalité manquante peut être émulée en regroupant toutes les entrées appartenant à un package à l'intérieur du schéma PostgreSQL avec le même nom, les variables globales peuvent être stockées dans une table de service.

Chaînes vides. Contrairement à Oracle, dans PostgreSQL, les chaînes vides ne sont pas égales à NULL. Par exemple, dans Oracle :

 SQL> select NVL('','This is null') as null_test from dual; NULL_TEST ------------ This is null

La même chose dans PostgreSQL :

 testdb=# select coalesce('','This is null') as null_test; null_test ----------- (1 row)

De plus, il existe un comportement différent de concaténation de chaînes avec NULL dans Oracle et PostgreSQL. Dans Oracle :

 SQL> Select 'String' || NULL as cs from dual; CS -- String

Dans PostgreSQL :

 synctest=# Select 'String' || NULL as concat_str; concat_str ------------ (1 row)

Coulée de types. PostgreSQL requiert une conversion stricte des types lors de l'appel de fonctions, d'opérateurs ou lorsque les instructions INSERT et UPDATE placent les résultats d'expressions dans une table.

La solution consiste à utiliser le pseudo-type 'anyelement' :

 create or replace function my_concat(str1 anyelement, str2 anyelement) returns varchar language plpgsql as $$ begin return str1::varchar || str2::varchar; end; $$;

Remarque : vous pouvez soit utiliser un argument anyelement dans une procédure/fonction stockée, soit tous les paramètres du même type anyelement.

Séquences. La syntaxe de la déclaration de séquence est similaire pour Oracle et PostgreSQL. Par exemple:

 CREATE SEQUENCE SEQ1 START WITH 10 INCREMENT BY 2 MAXVALUE 1000000 CACHE 20 NO CYCLE;

Cependant, faire référence à la valeur suivante de la séquence est différent. Dans Oracle, c'est 'sequence.nextval' et dans PostgreSQL nextval('sequence').

Déclencheurs. Dans Oracle, le code source du corps du déclencheur est inclus dans l'instruction CREATE TRIGGER. PostgreSQL requiert que le code source du déclencheur soit composé en tant que fonction autonome avec la référence de l'instruction CREATE TRIGGER :

 CREATE OR REPLACE FUNCTION store_changes() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF (NEW.first_name <> OLD.first_name) OR (NEW.last_name <> OLD.last_name) OR (NEW.email <> OLD.email) THEN INSERT INTO changes_log(id,changed_on) VALUES(OLD.id,now()); END IF; RETURN NEW; END; $$ CREATE TRIGGER make_changes BEFORE UPDATE ON employees FOR EACH ROW EXECUTE PROCEDURE store_changes();

Transactions autonomes. Les transactions autonomes dans Oracle permettent à un sous-programme de valider ou d'annuler des opérations SQL sans valider ou annuler la transaction principale. Par exemple, certaines données sont insérées dans une table dans le cadre de insert-trigger.

Cette opération d'insertion doit réussir même si la transaction principale du déclencheur d'insertion a échoué. Dans ce cas, l'instruction INSERT correspondante doit être incluse dans la transaction autonome :

 CREATE OR REPLACE PROCEDURE insert_critical_data(v_data varchar2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO info_table (data) VALUES (v_data); commit; END;

Les transactions autonomes ne sont pas prises en charge directement dans PostgreSQL. Une solution de contournement possible consiste à refactoriser le code, afin qu'il n'ait pas besoin de transactions autonomes.

Sinon, vous pouvez utiliser le module 'dblink' de PostgreSQL. Avec dblink, une nouvelle connexion est établie et une requête est exécutée à l'aide de cette connexion et est immédiatement validée, quelle que soit la transaction principale. Par exemple, la fonction suivante insère une ligne dans une table et l'opération d'insertion sera validée même si la transaction appelante est annulée :

 CREATE OR REPLACE FUNCTION insert_critical_data(v_data TEXT) RETURNS void AS $BODY$ DECLARE v_sql text; BEGIN PERFORM dblink_connect('myconn', 'dbname=mydbname user=… password=… host=… port=…'); v_sql := format('INSERT INTO info_table (data) VALUES (%L)', v_data); PERFORM dblink_exec('myconn', v_sql); PERFORM dblink_disconnect('myconn'); END; $BODY$ LANGUAGE plpgsql;

Fonctions intégrées. Oracle et PostgreSQL fournissent des ensembles similaires mais pas égaux de fonctions intégrées. Le tableau ci-dessous inclut les fonctions Oracle qui nécessitent un portage dans des équivalents PostgreSQL :

* Le portage complexe de la fonction Oracle INSTR vers PostgreSQL peut être trouvé à : https://www.postgresql.org/docs/9.0/static/plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX

La condition Oracle 'where rownum < N' doit être convertie en 'limit N' dans PostgreSQL Nous avons utilisé Oracle-to-PostgreSQL Code Converter pour automatiser partiellement la conversion des déclencheurs, des procédures stockées et des fonctions. Il prend en charge la conversion des fonctions intégrées d'Oracle en équivalents PostgreSQL et traite la plupart des modèles de syntaxe de PL/SQL. Cependant, un code source complexe peut nécessiter un post-traitement manuel de la sortie.

Modules utiles pour la migration d'Oracle vers PostgreSQL

Cette section contient une brève description des modules PostgreSQL qui peuvent aider à migrer d'Oracle vers PostgreSQL et tester les résultats de la migration.

L'un des modules PostgreSQL les plus importants lors de la migration depuis Oracle est orafce . Il émule des types, des fonctions et des opérateurs spécifiques du système de gestion de base de données Oracle.

Les extensions 'pgTAP' et 'pg_prove' peuvent être utilisées pour les tests fonctionnels des fonctions PostgreSQL. Après avoir installé 'pgTAP', il crée de nombreuses fonctions stockées utilisées pour écrire des tests. La fonction de test doit être déclarée en tant que 'returns setof text'. Ensuite, il peut être exécuté à partir du client de la console psql :

 psql -h $db_host -p $db_port -U $db_user $db_name -f tests/name_of_test.sql

Des informations détaillées sur pgTAP peuvent être trouvées ici : http://pgtap.org/documentation.html

L'extension PostgreSQL plpgsql_check permet de valider le code PL/pgSQL. Par exemple, s'il manque une déclaration de variable ou un nom de colonne mal orthographié dans la fonction PostgreSQL, cela échouera définitivement lors de l'exécution. Utilisez les fonctionnalités de 'plpgsql_check' pour diagnostiquer ces problèmes avant de passer en production :

 select * from plpgsql_check_function_tb('{name of function}');

Le module plprofiler aide à analyser les performances du code PL/pgSQL dans les fonctions et les procédures stockées.

L'extension PostgreSQL oracle_fdw fournit une connexion aux bases de données Oracle via la fonctionnalité Foreign Data Wrapper du SGBD. Par exemple, si la base de données Oracle 'OCRL' est en cours d'exécution sur le serveur 'server.mydomain.com', oracle_fdw doit être configuré comme suit :

 CREATE EXTENSION oracle_fdw; CREATE SERVER oraserver FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//server.mydomain.com:1521/ORCL'); GRANT USAGE ON FOREIGN SERVER oraserver TO pguser; CREATE USER MAPPING FOR pguser SERVER oraserver OPTIONS (user 'oracle user', password 'oracle password'); CREATE FOREIGN TABLE oratable ( { column definitions } ) SERVER oraserver OPTIONS (schema 'ORAUSER', table 'ORATAB');

Ensuite, vous pouvez utiliser ' oratable ' comme une table PostgreSQL normale.