Migrer les colonnes générées virtuellement d'Oracle vers PostgreSQL - Recommandations AWS
RécapitulatifConditions préalables et limitationsOutilsÉpopéesRessources connexes

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Migrer les colonnes générées virtuellement d'Oracle vers PostgreSQL

Créée par Veeranjaneyulu Grandhi (AWS), Rajesh Madiwale (AWS) et Ramesh Pathuri (AWS)

Environnement : Production

Source : base de données Oracle

Cible : compatible avec Amazon RDS for PostgreSQL ou Aurora PostgreSQL

Type R : Ré-architecte

Charge de travail : Oracle

Technologies : migration ; bases de données

Services AWS : Amazon Aurora ; Amazon RDS ; AWS DMS

Récapitulatif

Dans les versions 11 et antérieures, PostgreSQL ne fournit aucune fonctionnalité directement équivalente à une colonne virtuelle Oracle. La gestion des colonnes générées virtuelles lors de la migration d'Oracle Database vers PostgreSQL version 11 ou antérieure est difficile pour deux raisons : 

  • Les colonnes virtuelles ne sont pas visibles pendant la migration.

  • PostgreSQL ne prend pas en charge l'expression avant generate la version 12.

Cependant, il existe des solutions pour émuler des fonctionnalités similaires. Lorsque vous utilisez AWS Database Migration Service (AWS DMS) pour migrer des données d'Oracle Database vers PostgreSQL version 11 ou antérieure, vous pouvez utiliser des fonctions de déclenchement pour renseigner les valeurs dans les colonnes générées virtuellement. Ce modèle fournit des exemples de code Oracle Database et PostgreSQL que vous pouvez utiliser à cette fin. Sur AWS, vous pouvez utiliser Amazon Relational Database Service (Amazon RDS) pour PostgreSQL ou Amazon Aurora PostgreSQL Compatible Edition pour votre base de données PostgreSQL.

À partir de la version 12 de PostgreSQL, les colonnes générées sont prises en charge. Les colonnes générées peuvent être calculées à partir d'autres valeurs de colonne à la volée ou calculées et stockées. Les colonnes générées par PostgreSQL sont similaires aux colonnes virtuelles Oracle.

Conditions préalables et limitations

Prérequis

  • Un compte AWS actif

  • Une base de données Oracle source

  • Bases de données PostgreSQL cibles (sur Amazon RDS for PostgreSQL ou compatible avec Aurora PostgreSQL)

  • Expertise en codage PL/pgSQL

Limites

  • S'applique uniquement aux versions de PostgreSQL antérieures à la version 12. 

  • S'applique à la version 11g ou ultérieure d'Oracle Database.

  • Les colonnes virtuelles ne sont pas prises en charge dans les outils de migration de données.

  • S'applique uniquement aux colonnes définies dans le même tableau.

  • Si une colonne générée virtuelle fait référence à une fonction déterministe définie par l'utilisateur, elle ne peut pas être utilisée comme colonne clé de partitionnement.

  • La sortie de l'expression doit être une valeur scalaire. Il ne peut pas renvoyer un type de données fourni par Oracle, un type défini par l'utilisateur, LOB ou. LONG RAW

  • Les index définis par rapport à des colonnes virtuelles sont équivalents aux index basés sur des fonctions dans PostgreSQL.

  • Les statistiques du tableau doivent être collectées.

Outils

  • pgAdmin 4 est un outil de gestion open source pour PostgreSQL. Cet outil fournit une interface graphique qui simplifie la création, la maintenance et l'utilisation des objets de base de données.

  • Oracle SQL Developer est un environnement de développement intégré gratuit permettant de travailler avec SQL dans les bases de données Oracle dans le cadre de déploiements traditionnels et dans le cloud. 

Épopées

TâcheDescriptionCompétences requises

Créez une table de base de données Oracle source.

Dans Oracle Database, créez une table avec des colonnes générées virtuellement à l'aide de l'instruction suivante.

CREATE TABLE test.generated_column
( CODE NUMBER,
STATUS VARCHAR2(12) DEFAULT 'PreOpen',
FLAG CHAR(1) GENERATED ALWAYS AS (CASE UPPER(STATUS) WHEN 'OPEN' THEN 'N' ELSE 'Y' END) VIRTUAL VISIBLE
);

Dans ce tableau source, les données de la STATUS colonne sont migrées via AWS DMS vers la base de données cible. La FLAG colonne est toutefois remplie à l'aide de generate by fonctionnalités. Elle n'est donc pas visible par AWS DMS pendant la migration. Pour implémenter la fonctionnalité degenerated by, vous devez utiliser des déclencheurs et des fonctions dans la base de données cible pour renseigner les valeurs de la FLAG colonne, comme indiqué dans l'épopée suivante.

DBA, développeur d'applications

Créez une table PostgreSQL cible sur AWS.

Créez une table PostgreSQL sur AWS à l'aide de l'instruction suivante.

CREATE TABLE test.generated_column
(
    code integer not null,
    status character varying(12) not null ,
    flag character(1)
);

Dans ce tableau, la status colonne est une colonne standard. La flag colonne sera une colonne générée en fonction des données qu'elle status contient.

DBA, développeur d'applications
TâcheDescriptionCompétences requises

Créez un déclencheur PostgreSQL.

Dans PostgreSQL, créez un déclencheur.

CREATE TRIGGER tgr_gen_column
AFTER INSERT OR UPDATE OF status ON test.generated_column
FOR EACH ROW 
EXECUTE FUNCTION test.tgf_gen_column();
DBA, développeur d'applications

Créez une fonction de déclenchement PostgreSQL.

Dans PostgreSQL, créez une fonction pour le déclencheur. Cette fonction remplit une colonne virtuelle insérée ou mise à jour par l'application ou AWS DMS, et valide les données.

CREATE OR REPLACE FUNCTION test.tgf_gen_column() RETURNS trigger AS $VIRTUAL_COL$
BEGIN
IF (TG_OP = 'INSERT') THEN
IF (NEW.flag IS NOT NULL) THEN
RAISE EXCEPTION 'ERROR: cannot insert into column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF (TG_OP = 'UPDATE') THEN
IF (NEW.flag::VARCHAR != OLD.flag::varchar) THEN
RAISE EXCEPTION 'ERROR: cannot update column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF TG_OP IN ('INSERT','UPDATE') THEN
IF (old.flag is NULL) OR (coalesce(old.status,'') != coalesce(new.status,'')) THEN
UPDATE test.generated_column
SET flag = (CASE UPPER(status) WHEN 'OPEN' THEN 'N' ELSE 'Y' END)
WHERE code = new.code;
END IF;
END IF;
RETURN NEW;
END
$VIRTUAL_COL$ LANGUAGE plpgsql;
DBA, développeur d'applications
TâcheDescriptionCompétences requises

Créez une instance de réplication.

Pour créer une instance de réplication, suivez les instructions de la documentation AWS DMS. L'instance de réplication doit se trouver dans le même cloud privé virtuel (VPC) que vos bases de données source et cible.

DBA, développeur d'applications

Créez des points de terminaison source et cible.

Pour créer les points de terminaison, suivez les instructions de la documentation AWS DMS.

DBA, développeur d'applications

Testez les connexions des terminaux.

Vous pouvez tester les connexions du point de terminaison en spécifiant le VPC et l'instance de réplication, puis en choisissant Run test.

DBA, développeur d'applications

Créez et lancez une tâche de chargement complet.

Pour obtenir des instructions, consultez les sections Création d'une tâche et Chargement complet de la tâche dans la documentation AWS DMS.

DBA, développeur d'applications

Validez les données de la colonne virtuelle.

Comparez les données de la colonne virtuelle dans les bases de données source et cible. Vous pouvez valider les données manuellement ou écrire un script pour cette étape.

DBA, développeur d'applications

Ressources connexes