Cómo migrar de Oracle a PostgreSQL

Este documento técnico cubre la experiencia de la migración de bases de datos de Oracle a PostgreSQL realizada por Intelligent Converters para una gran empresa de entrega de carga. La razón clave de la migración fue la reducción del costo del sistema de gestión de la base de datos y la transferencia a un sistema de código abierto con suficientes capacidades de escalabilidad, seguridad y personalización.

Aquí hay una breve especificación de la base de datos de origen:

• Servidor de base de datos Oracle 12g
• La base de datos contiene 190 tablas, 50 GB de datos
• 120000 líneas de PL/SQL en procedimientos almacenados, funciones y disparadores

  
  

La duración del proyecto fue de 2 meses: medio mes de evaluación y planificación, un mes de migración y medio mes de pruebas. Hubo 3 especialistas involucrados en la migración: 2 desarrolladores y 1 administrador de base de datos.

Esta migración de base de datos de Oracle a PostgreSQL incluye seis fases básicas:

1. Encuentre todos los métodos específicos de Oracle para almacenar y procesar datos en la base de datos de origen y el ámbito de uso (fase de investigación y planificación)
2. Seleccione las herramientas apropiadas para la migración de esquemas e impleméntelas
3. Elija el método más adecuado de migración de datos para reducir el tiempo de inactividad del sistema Oracle
4. Ejecute la migración de datos manejando todas las transformaciones requeridas por PostgreSQL DBMS
5. Convierta todo el código PL/SQL a formato PostgreSQL (utilizando herramientas para autómatas parciales y posprocesamiento manual)
6. Ejecutar pruebas funcionales y de rendimiento, ajuste fino de la base de datos resultante

Migración de definiciones de tabla

Hay algunos tipos de datos en Oracle que no tienen un equivalente directo en PostgreSQL. Uno de estos tipos es DATE que contiene partes de fecha y hora.

PostgreSQL admite:

• fecha - fecha pura sin parte de tiempo
• hora: hora pura sin parte de fecha con zona horaria especificación opcional
• marca de tiempo: fecha y hora con especificación opcional de zona horaria

Hay dos opciones para mapear fechas de Oracle en PostgreSQL: usar TIMESTAMP o configurar la extensión para usar el tipo de fecha al estilo de Oracle oracle.date.

Los tipos espaciales también requieren una atención especial. Oracle tiene un tipo incorporado SDO_GEOMETRY, mientras que PostgreSQL necesita la instalación de PostGIS para trabajar con tipos de datos espaciales.

La siguiente tabla ilustra la asignación de tipos seguros de Oracle a PostgreSQL:

Oráculo	postgresql
BINARIO_FLOAT	REAL
BINARIO_INTEGER	ENTERO
BINARIO_DOBLE	PRECISIÓN DOBLE
BLOB, RAW(n), LARGO RAW	BYTEA (límite de 1GB)
CLOB, LARGO	TEXTO (límite de 1GB)
FECHA	MARCA DE TIEMPO
NÚMERO, NÚMERO(*)	DOUBLE PRECISION o BIGINT si es parte de Primary Key
NÚMERO(n,0), NÚMERO(n)	n<5 – SMALLINT5<=n<9 – INT9<=n<19 – BIGINTn>=19 – DECIMAL(n)
NÚMERO (p, s)	DECIMAL(p,s)
REAL	PRECISIÓN DOBLE

Para los tipos numéricos, es importante comprender el alcance del uso en la base de datos. Si se centra en la precisión, los tipos numéricos de Oracle deben mapearse en PostgreSQL NUMERIC. Si la máxima prioridad es la velocidad de cálculo, el mejor mapeo sería REAL o DOBLE PRECISIÓN.

Usamos para automatizar la migración de definiciones de tablas, índices y restricciones para este proyecto. Mapea los tipos de Oracle en los equivalentes de PostgreSQL más apropiados y permite personalizar el mapeo de tipos particulares.

Migración de datos

Dado que la migración de datos puede consumir mucho tiempo para bases de datos grandes, es extremadamente importante elegir la estrategia y las herramientas adecuadas para este paso.

Hay tres enfoques comunes para la migración de datos:

• instantánea: migre todos los datos en un solo paso
• Instantánea por partes: migre datos por fragmentos en subprocesos o procesos paralelos
• cambiar la replicación de datos: carga continua de datos mediante el seguimiento de cambios incrementales

El método de instantánea requiere un tiempo de inactividad esencial de la base de datos de origen durante todo el período de lectura de datos para evitar la pérdida o corrupción de datos durante la migración. El tiempo de inactividad para el enfoque de instantáneas por partes es mucho menor, pero sigue siendo necesario. En este proyecto de migración, utilizamos el método de instantáneas por partes para la migración de tablas grandes que contienen millones de filas.

Entonces, ¿qué pasa con la técnica de replicación de datos modificados (CDR)?

Tiene dos implementaciones principales y cada una de ellas tiene su debilidad. El primero se conoce como CDR basado en disparadores. Requiere la creación de activadores de inserción, actualización y eliminación para cada tabla que se replica en la base de datos de origen. Esos disparadores rastrean todos los cambios almacenando información sobre cambios de eventos en una tabla especial de 'historial'. En función de estos datos, la herramienta CDR replica todos los cambios en la base de datos de destino. El CDR basado en disparadores provoca una sobrecarga adicional de la base de datos de origen debido a la escritura en la tabla de "historial" para cada actualización de datos.

El segundo método se llama Transaction Log CDR. Utiliza registros de transacciones creados por Oracle DBMS para rastrear y replicar cambios en la base de datos PostgreSQL de destino. La ventaja de este enfoque en comparación con CDR basado en disparadores es que no modifica la base de datos de origen.

Sin embargo, Transaction Log CDR también tiene algunas debilidades:

• Oracle archiva constantemente los archivos de registro de transacciones, por lo que la herramienta CDC puede perder algunos cambios si no lee el registro de transacciones antes de archivarlo.
• En caso de perder la conexión con la base de datos de destino mientras se replican los cambios del registro de transacciones (o cualquier otro tipo de error), los datos pueden perderse o dañarse debido a la falta de control sobre el registro de transacciones.

Los aspectos más desafiantes de la migración de datos de Oracle a PostgreSQL son: formatos de datos específicos del DBMS de origen que no tienen un equivalente directo en los datos de destino y externos.

En la sección anterior de este artículo, se afirma que BYTEA es el tipo de datos de PostgreSQL más adecuado para datos binarios. Sin embargo, al migrar datos binarios grandes (el tamaño de campo promedio no es inferior a 10 MB), no se recomienda utilizar BYTEA. La razón es la particularidad de leer datos BYTEA: solo se puede extraer en un fragmento; la lectura por partes no es posible. Esto puede causar una sobrecarga de RAM esencial. El tipo LARGE OBJECT de PostgreSQL se puede utilizar como solución para este problema. Todos los valores de tipo LARGE OBJECT se almacenan en la tabla del sistema 'pg_largeobject' que forma parte de cada base de datos. Puede haber hasta 4 mil millones de filas en la tabla 'pg_largeobject'. El tamaño máximo de OBJETO GRANDE es de 4 TB y la lectura por partes está disponible.

Además, es importante migrar correctamente el ROWID, que es una pseudocolumna que identifica la dirección física del registro en la tabla. PostgreSQL tiene un campo de servicio similar llamado ctid, sin embargo, es un equivalente directo de ROWID. dice que ctid podría cambiarse debido al procedimiento de limpieza.

Hay tres métodos básicos para emular la funcionalidad ROWID en PostgreSQL:

• Use la clave principal existente (o cree una nueva) para identificar filas en lugar de ROWID
• Agregue una columna serial o bigserial con valores generados automáticamente y conviértala en una clave principal/única para reemplazar la funcionalidad ROWID
• Cuando no sea posible definir una clave principal de una sola columna, use un índice único creado en varias columnas (para este propósito, determine un conjunto único mínimo de campos para cada fila)

Oracle puede vincular datos externos que se almacenan fuera de la base de datos. Sin embargo, se puede procesar como una tabla normal utilizando la función de "tabla externa" del DBMS. PostgreSQL utiliza la biblioteca Contenedor de datos externos para el mismo propósito. Por ejemplo, proporciona la extensión 'file_fdw' para trabajar con archivos CSV externos como una tabla normal.

Desafíos de la migración PL/SQL

Esta sección explora los problemas que se resolvieron durante la migración de paquetes, procedimientos de almacenamiento, funciones y disparadores de Oracle a PostgreSQL.

paquetes PostgreSQL no tiene paquetes. Esta característica que falta se puede emular agrupando todas las entradas que pertenecen a un paquete dentro del esquema de PostgreSQL con el mismo nombre, las variables globales se pueden almacenar en una tabla de servicio.

Cuerdas vacías. A diferencia de Oracle, en PostgreSQL las cadenas vacías no son iguales a NULL. Por ejemplo, en Oracle:

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

Lo mismo en PostgreSQL:

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

Además, existe un comportamiento diferente de concatenación de cadenas con NULL en Oracle y PostgreSQL. En oráculo:

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

En PostgreSQL:

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

Fundición de tipos. PostgreSQL requiere una conversión de tipos estricta al llamar a funciones, operadores o cuando las declaraciones INSERT y UPDATE colocan los resultados de las expresiones en una tabla.

La solución es usar el pseudo-tipo 'cualquier elemento':

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

Nota: puede usar un argumento anyelement en el procedimiento/función almacenado o todos los parámetros del mismo tipo anyelement.

Secuencias. La sintaxis de la declaración de secuencia es similar para Oracle y PostgreSQL. Por ejemplo:

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

Sin embargo, hacer referencia al siguiente valor de la secuencia es diferente. En Oracle es 'secuencia.nextval' y en PostgreSQL nextval('secuencia').

Desencadenadores. En Oracle, el código fuente del cuerpo del activador se incluye en la instrucción CREATE TRIGGER. PostgreSQL requiere que el código fuente del activador se componga como una función independiente con la referencia de la declaración 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();

Transacciones Autónomas. Las transacciones autónomas en Oracle permiten que un subprograma confirme o revierta operaciones de SQL sin comprometer o revertir la transacción principal. Por ejemplo, algunos datos se insertan en una tabla como parte de insert-trigger.

Esta operación de inserción debe tener éxito incluso si falla la transacción principal del activador de inserción. En este caso, la instrucción INSERT correspondiente debe incluirse en la transacción autónoma:

 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;

Las transacciones autónomas no se admiten directamente en PostgreSQL. Una posible solución es refactorizar el código para que no necesite transacciones autónomas.

De lo contrario, puede usar el módulo 'dblink' de PostgreSQL. Con dblink, se establece una nueva conexión y se ejecuta una consulta utilizando esta conexión y se confirma de inmediato, independientemente de la transacción principal. Por ejemplo, la siguiente función inserta una fila en una tabla y la operación de inserción se confirmará incluso si la transacción de llamada se revierte:

 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;

Funciones integradas. Oracle y PostgreSQL proporcionan conjuntos de funciones integradas similares pero no iguales. La siguiente tabla incluye funciones de Oracle que requieren transferencia a equivalentes de PostgreSQL:

Oráculo	postgresql
ADD_MONTH($fecha,$n_mes)	$fecha + $n_mes * intervalo '1 mes'
DECODE($exp, $cuando, $entonces, ...)	CASO $exp CUANDO $cuándo ENTONCES $entonces ... FIN
INSTR($str1, $str2)	*POSICIÓN($str2 en $str1)
ROWNUM	**número_fila() sobre ()
FECHA DEL SISTEMA	FECHA ACTUAL
SYS_GUID()	uuid_generate_v1()

*La migración compleja de la función INSTR de Oracle a PostgreSQL se puede encontrar en:

La condición de Oracle 'where rownum < N' debe convertirse en 'límite N' en PostgreSQL Usamos Oracle-to-PostgreSQL Code Converter para automatizar parcialmente la conversión de disparadores, procedimientos almacenados y funciones. Admite la conversión de funciones integradas de Oracle en equivalentes de PostgreSQL y procesa la mayoría de los patrones de sintaxis de PL/SQL. Sin embargo, el código fuente complejo puede requerir un procesamiento posterior manual de la salida.

Módulos útiles para la migración de Oracle a PostgreSQL

Esta sección contiene una breve descripción de los módulos de PostgreSQL que pueden ayudar a migrar de Oracle a PostgreSQL y probar los resultados de la migración.

Uno de los módulos de PostgreSQL más importantes a la hora de migrar desde Oracle es . Emula tipos, funciones y operadores específicos del sistema de gestión de bases de datos Oracle.

Las extensiones 'pgTAP' y 'pg_prove' se pueden usar para pruebas funcionales de las funciones de PostgreSQL. Después de instalar 'pgTAP', crea muchas funciones almacenadas que se utilizan para escribir pruebas. La función de prueba debe declararse como 'devuelve un conjunto de texto'. Luego se puede ejecutar desde el cliente de la consola psql:

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

Puede encontrar información detallada sobre pgTAP aquí:

La extensión de PostgreSQL ayuda a validar el código PL/pgSQL. Por ejemplo, si falta una declaración de variable o un nombre de columna mal escrito dentro de la función PostgreSQL, definitivamente fallará durante la ejecución. Use las capacidades de 'plpgsql_check' para diagnosticar tales problemas antes de pasar a producción:

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

El módulo ayuda a analizar el rendimiento del código PL/pgSQL en funciones y procedimientos almacenados.

La extensión de PostgreSQL, proporciona conexión a bases de datos de Oracle a través de la función Contenedor de datos externos de DBMS. Por ejemplo, si la base de datos Oracle 'OCRL' se ejecuta en el servidor 'server.mydomain.com', oracle_fdw debe configurarse de la siguiente manera:

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

Luego, puede usar ' oratable ' como una tabla normal de PostgreSQL.