Migración de columnas generadas de forma virtual de Oracle a PostgreSQL

PDF

Creado por Veeranjaneyulu Grandhi (AWS), Rajesh Madiwale (AWS) y Ramesh Pathuri (AWS)

Resumen

En la versión 11 y anteriores, PostgreSQL no proporciona una característica que sea directamente equivalente a una columna virtual de Oracle. Gestionar las columnas generadas de forma virtual al migrar de Oracle Database a la versión 11 o anterior de PostgreSQL resulta difícil por dos motivos: 

• Las columnas virtuales no están visibles durante la migración.

• PostgreSQL no admite la expresión generate antes de la versión 12.

Sin embargo, existen soluciones alternativas para emular una funcionalidad similar. Cuando utilice AWS Database Migration Service (AWS DMS) para migrar datos desde Oracle Database a la versión 11 y anteriores de PostgreSQL, puede utilizar las funciones de activación para rellenar los valores de las columnas generadas de forma virtual. Este patrón proporciona ejemplos de código PostgreSQL y Oracle Database que puede utilizar para este fin. En AWS, puede utilizar Amazon Relational Database Service (Amazon RDS) para PostgreSQL o la Edición compatible con PostgreSQL de Amazon Aurora para la base de datos de PostgreSQL.

A partir de la versión 12 de PostgreSQL, se admiten las columnas generadas. Las columnas generadas pueden calcularse sobre la marcha a partir de otros valores de columna o calcularse y almacenarse. Las columnas generadas por PostgreSQL son similares a las columnas virtuales de Oracle.

Requisitos previos y limitaciones

Requisitos previos 

• Una cuenta de AWS activa

• Base de datos de origen de Oracle

• Bases de datos PostgreSQL de destino (en Amazon RDS para PostgreSQL o Aurora compatible con PostgreSQL)

• Experiencia en codificación PL/pgSQL

Limitaciones

• Solo se aplica a las versiones de PostgreSQL anteriores a la versión 12. 

• Se aplica a la versión 11g o posterior de Oracle Database.

• Las columnas virtuales no se admiten en las herramientas de migración de datos.

• Solo se aplica a las columnas definidas en la misma tabla.

• Si una columna generada de forma virtual hace referencia a una función determinista definida por el usuario, no se puede utilizar como columna clave de partición.

• El resultado de la expresión debe ser un valor escalar. No puede devolver un tipo de datos proporcionado por Oracle, un tipo definido por el usuario, LOB o LONG RAW.

• Los índices que se definen en columnas virtuales equivalen a los índices basados en funciones en PostgreSQL.

• Se deben recopilar las estadísticas de las tablas.

Herramientas

• pgAdmin 4 es una herramienta de gestión de código abierto para PostgreSQL. Esta herramienta proporciona una interfaz gráfica que simplifica la creación, el mantenimiento y el uso de los objetos de la base de datos.

• Oracle SQL Developer es un entorno de desarrollo integrado y gratuito para trabajar con SQL en bases de datos de Oracle, tanto en implementaciones tradicionales como en la nube. 

Epics

Cree tablas de bases de datos de origen y destino

Tarea	Descripción	Habilidades requeridas
Cree una tabla de base de datos de Oracle de origen.	En Oracle Database, cree una tabla con columnas generadas de forma virtual mediante la siguiente declaración. 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 ); En esta tabla de origen, los datos de la columna STATUS se migran a través de AWS DMS a la base de datos de destino. Sin embargo, la columna FLAG se rellena mediante la funcionalidad generate by, por lo que AWS DMS no podrá verla durante la migración. Para implementar la funcionalidad de generated by, debe utilizar activadores y funciones de la base de datos de destino para rellenar los valores de la columna FLAG, como se muestra en la siguiente epopeya.	Administrador de base de datos, desarrollador de aplicaciones
Cree una tabla PostgreSQL de destino en AWS.	Cree una tabla de PostgreSQL en AWS con la siguiente instrucción. CREATE TABLE test.generated_column ( code integer not null, status character varying(12) not null , flag character(1) ); En esta tabla, la columna status es una columna estándar. La columna flag será una columna generada en función de los datos de la columna status.	Administrador de base de datos, desarrollador de aplicaciones

Cree tablas de bases de datos de origen y destino

Tarea	Descripción	Habilidades requeridas
Cree una tabla de base de datos de Oracle de origen.	En Oracle Database, cree una tabla con columnas generadas de forma virtual mediante la siguiente declaración. 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 ); En esta tabla de origen, los datos de la columna STATUS se migran a través de AWS DMS a la base de datos de destino. Sin embargo, la columna FLAG se rellena mediante la funcionalidad generate by, por lo que AWS DMS no podrá verla durante la migración. Para implementar la funcionalidad de generated by, debe utilizar activadores y funciones de la base de datos de destino para rellenar los valores de la columna FLAG, como se muestra en la siguiente epopeya.	Administrador de base de datos, desarrollador de aplicaciones
Cree una tabla PostgreSQL de destino en AWS.	Cree una tabla de PostgreSQL en AWS con la siguiente instrucción. CREATE TABLE test.generated_column ( code integer not null, status character varying(12) not null , flag character(1) ); En esta tabla, la columna status es una columna estándar. La columna flag será una columna generada en función de los datos de la columna status.	Administrador de base de datos, desarrollador de aplicaciones

Cree una función de activación para gestionar la columna virtual en PostgreSQL

Tarea	Descripción	Habilidades requeridas
Cree un activador de PostgreSQL.	En PostgreSQL, cree un activador. CREATE TRIGGER tgr_gen_column AFTER INSERT OR UPDATE OF status ON test.generated_column FOR EACH ROW EXECUTE FUNCTION test.tgf_gen_column();	Administrador de base de datos, desarrollador de aplicaciones
Cree una función de activación de PostgreSQL.	En PostgreSQL, cree una función para el activador. Esta función rellena una columna virtual que la aplicación o AWS DMS insertan o actualizan y valida los datos. 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;	Administrador de base de datos, desarrollador de aplicaciones

Cree una función de activación para gestionar la columna virtual en PostgreSQL

Tarea	Descripción	Habilidades requeridas
Cree un activador de PostgreSQL.	En PostgreSQL, cree un activador. CREATE TRIGGER tgr_gen_column AFTER INSERT OR UPDATE OF status ON test.generated_column FOR EACH ROW EXECUTE FUNCTION test.tgf_gen_column();	Administrador de base de datos, desarrollador de aplicaciones
Cree una función de activación de PostgreSQL.	En PostgreSQL, cree una función para el activador. Esta función rellena una columna virtual que la aplicación o AWS DMS insertan o actualizan y valida los datos. 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;	Administrador de base de datos, desarrollador de aplicaciones

Pruebe la migración de datos mediante AWS DMS

Tarea	Descripción	Habilidades requeridas
Cree una instancia de replicación.	Para crear una instancia de replicación, siga las instrucciones de la documentación de AWS DMS. La instancia de replicación debe estar en la misma nube privada virtual (VPC) que las bases de datos de origen y destino.	Administrador de base de datos, desarrollador de aplicaciones
Cree puntos de conexión de origen y destino.	Para crear los puntos de conexión, siga las instrucciones de la documentación de AWS DMS.	Administrador de base de datos, desarrollador de aplicaciones
Probar los puntos de conexión.	Puede probar las conexiones de conexión especificando la VPC y la instancia de replicación y seleccionando Ejecutar prueba.	Administrador de base de datos, desarrollador de aplicaciones
Cree e inicie una tarea de carga completa.	Para obtener instrucciones, consulte Creación de una tarea y Configuración de tareas de carga completa en la documentación de AWS DMS.	Administrador de base de datos, desarrollador de aplicaciones
Valide los datos de la columna virtual.	Compare los datos de la columna virtual en las bases de datos de origen y destino. Puede validar los datos manualmente o escribir un script para este paso.	Administrador de base de datos, desarrollador de aplicaciones

Pruebe la migración de datos mediante AWS DMS

Tarea	Descripción	Habilidades requeridas
Cree una instancia de replicación.	Para crear una instancia de replicación, siga las instrucciones de la documentación de AWS DMS. La instancia de replicación debe estar en la misma nube privada virtual (VPC) que las bases de datos de origen y destino.	Administrador de base de datos, desarrollador de aplicaciones
Cree puntos de conexión de origen y destino.	Para crear los puntos de conexión, siga las instrucciones de la documentación de AWS DMS.	Administrador de base de datos, desarrollador de aplicaciones
Probar los puntos de conexión.	Puede probar las conexiones de conexión especificando la VPC y la instancia de replicación y seleccionando Ejecutar prueba.	Administrador de base de datos, desarrollador de aplicaciones
Cree e inicie una tarea de carga completa.	Para obtener instrucciones, consulte Creación de una tarea y Configuración de tareas de carga completa en la documentación de AWS DMS.	Administrador de base de datos, desarrollador de aplicaciones
Valide los datos de la columna virtual.	Compare los datos de la columna virtual en las bases de datos de origen y destino. Puede validar los datos manualmente o escribir un script para este paso.	Administrador de base de datos, desarrollador de aplicaciones

Recursos relacionados

• Introducción a AWS Database Migration Service (AWS DMS) (documentación de AWS DMS)

• Uso de una base de datos de Oracle como fuente para AWS DMS (documentación de AWS DMS)

• Uso de una base de datos PostgreSQL como destino para AWS DMS (documentación de AWS DMS)

• Columnas generadas en PostgreSQL (documentación de PostgreSQL)

• Funciones de activación (documentación de PostgreSQL)

• Columnas virtuales en Oracle Database (documentación de Oracle)