Cómo migrar una base de datos Oracle a PostgreSQL – Parte 1

 

---

Introducción

En este artículo, mostramos cómo migrar una base de datos Oracle a PostgreSQL.
¿Por qué querría hacer eso? Una motivación habitual es hacer que su aplicación sea independiente de la base de datos, lo que le permite ejecutarse en múltiples sistemas de bases de datos y, por lo tanto, aumentar su base de clientes potenciales.

Existen varias herramientas en el mercado que afirman automatizar esta migración. KeepTool es una de ellas: fácil de usar y diseñada para automatizar las tareas de migración sin necesidad de ceder el control total a una herramienta de «caja negra».

Durante el último año, los componentes de migración integrados en Hora se han perfeccionado a través de numerosos proyectos de migración de Oracle a PostgreSQL en el mundo real. En este artículo se explica cómo utilizar estas capacidades de forma eficiente.

Tareas de migración

La migración de una base de datos Oracle a PostgreSQL implica varias tareas:

• Crear una estructura de tabla equivalente en PostgreSQL, incluyendo claves externas, claves primarias/únicas, restricciones de comprobación y comentarios.

• Recrear vistas utilizando ANSI SQL y abordar las diferencias en la sintaxis SQL.

• Copiar datos de Oracle a PostgreSQL.

• Reimplementar la lógica PL/SQL utilizando PL/pgSQL.

En las siguientes secciones se describen cada uno de estos pasos.

Creación de una estructura de tabla en PostgreSQL

Antes de empezar, revise su esquema Oracle y decida qué tablas realmente necesitan migrarse. Es posible que algunas tablas, como las tablas de copia de seguridad temporales, no sean relevantes en el entorno de destino.

La función de marcado de Hora le ayuda a marcar las tablas que desea migrar. En nuestro ejemplo, utilizamos un punto amarillo para identificar las candidatas a la migración.

Una vez marcadas las tablas, seleccione una o varias de ellas y elija DDL para PostgreSQL DB. Para comprender el proceso, comencemos con una sola tabla.

Hora muestra un cuadro de diálogo con DDL compatible con PostgreSQL. Comienza configurando PostgreSQL search_path, que desempeña una función similar, aunque no idéntica, al esquema actual de Oracle.

set search_path = hr;

A continuación, Hora genera una CREATE TABLE . Para cada columna de Oracle, Hora selecciona el tipo de datos de PostgreSQL más similar. Aunque son similares, existen diferencias importantes, que Hora gestiona automáticamente.

Ejemplo: definición de tabla de PostgreSQL

DROP TABLE IF EXISTS jobs;

CREATE TABLE jobs
(
   job_id varchar(10) NOT NULL,
   job_title varchar(35) NOT NULL,
   min_salary int4,
   max_salary int4
);

Hora también incluye comentarios sobre tablas y columnas si existen en el esquema de Oracle:

COMMENT ON TABLE jobs IS
  'Tabla de empleos con títulos de puestos y rangos salariales. Contiene 19 filas.
  Referencias con empleados y tabla job_history.';

COMMENT ON COLUMN jobs.job_id IS
  'Clave principal de la tabla de empleos.';

COMMENT ON COLUMN jobs.job_title IS
  'Columna no nula que muestra el título del puesto, por ejemplo, AD_VP, FI_ACCOUNTANT';

A continuación se generan las restricciones, cada una con su propio comentario:

ALTER TABLE jobs ADD CONSTRAINT job_id_pk PRIMARY KEY ( job_id)
NOT DEFERRABLE INITIALLY IMMEDIATE;

COMMENT ON CONSTRAINT job_id_pk ON jobs IS
'Los valores de la columna JOB_ID deben ser únicos para cada registro de datos JOBS.';

Creación de estructuras para varias tablas

Al generar DDL para varias tablas, el orden de las instrucciones cobra importancia. Por ejemplo, las claves externas que hacen referencia a una tabla requieren que la clave principal de la tabla a la que se hace referencia ya exista.

Hora determina automáticamente el orden correcto.
Basta con seleccionar varias tablas y volver a elegir DDL para la base de datos PostgreSQL.

El script generado se organiza de la siguiente manera:

1. DDL para tablas, incluyendo comentarios, claves primarias/únicas e índices

2. DDL para todas las claves externas

3. Lógica de disparadores

4. Concesiones

Esta salida estructurada le permite ejecutar todo el script de una sola vez sin preocuparse por las dependencias.

Migración de la lógica de activadores

Para cada disparador Oracle, Hora crea:

• una función PostgreSQL (que contiene la lógica) y

• un disparador PostgreSQL que llama a esta función.

Ejemplo:

create or replace function departments_bir_fnc()
returns trigger as
$body$
begin
IF coalesce( new.DEPARTMENT_ID,0) = 0 THEN
select nextval('departments_seq') INTO new.DEPARTMENT_ID ;
end IF;
return new;
end;
$body$ language plpgsql;

create or replace trigger departments_bir
before insert
on departments
for each row
execute function departments_bir_fnc();

Creación de vistas en PostgreSQL

Cambie el contexto general de las tablas a las vistas. Las vistas marcadas también se pueden procesar con la opción de menú DDL para PostgreSQL DB.

Hora genera una definición completa compatible con PostgreSQL, sustituyendo las uniones específicas de Oracle por sintaxis compatible con ANSI. En casos complejos, Hora también ajusta las llamadas a funciones integradas y otras construcciones específicas de Oracle.

Ejemplo de salida:

set search_path = hr;

-- VISTA: emp_details_view
drop view if exists emp_details_view;

create or replace view emp_details_view
(
   employee_id, job_id, manager_id, department_id, first_name, last_name,
   salary, commission_pct, location_id, department_name, country_id, city,
   state_province, job_title, country_name, region_name
)
AS
seleccionar
   E.EMPLOYEE_ID, E.JOB_ID, E.MANAGER_ID, E.DEPARTMENT_ID,
   E.FIRST_NAME, E.LAST_NAME, E.SALARY, E.COMMISSION_PCT,
   D.LOCATION_ID, D.DEPARTMENT_NAME,
   L.COUNTRY_ID, L.CITY, L.STATE_PROVINCE,
   J.JOB_TITLE,
   C.COUNTRY_NAME,
   R.REGION_NAME
from EMPLOYEES E
join DEPARTMENTS D on E.DEPARTMENT_ID = D.DEPARTMENT_ID
join LOCATIONS L on D.LOCATION_ID = L.LOCATION_ID
join COUNTRIES C on L.COUNTRY_ID = C.COUNTRY_ID
join REGIONS R on C.REGION_ID = R.REGION_ID
join JOBS J on J.JOB_ID = E.JOB_ID;

COMENTARIO SOBRE LA VISTA emp_details_view ES
  'Vista de detalles de los empleados';

-- Conceder privilegios
conceder lectura en emp_details_view a keeptool;

conceder selección en emp_details_view a keeptool;

También puede seleccionar varias vistas para generar todas las definiciones en un único script.

Copiar datos de Oracle a PostgreSQL

Hora facilita la migración de datos mediante la generación de scripts compatibles con PostgreSQL INSERT .

Comience abriendo la tabla en Tablas → Contenido de datos (o el Explorador de contenido de datos).
Haga clic con el botón derecho del ratón en la cuadrícula y seleccione Crear script INSERT.

En el cuadro de diálogo que aparece, deseleccione todas las opciones específicas de Oracle. Asegúrese de que la opción Usar literales de marca de tiempo permanece habilitada. Esta configuración produce un resultado compatible con PostgreSQL.

Ejemplo de extracto:

INSERT INTO JOBS( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES( 'AD_PRES', 'President', 20080, 40000);
INSERT INTO JOBS( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES( 'AD_VP', 'Vicepresidente de Administración', 15000, 30000);
INSERT INTO JOBS( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)...


COMMIT;

Este método se puede aplicar a cualquier tabla marcada.

Implementación de la lógica PL/SQL de Oracle utilizando PL/pgSQL

Ya hemos visto cómo Hora migra la lógica de los desencadenadores. La mayoría de las bases de datos Oracle también contienen procedimientos almacenados, funciones y paquetes. La migración de estos requiere un análisis más profundo de su código PL/SQL.

En una próxima entrada del blog se tratará cómo migrar la lógica PL/SQL a PL/pgSQL, incluyendo patrones comunes, diferencias en el manejo de excepciones y mejores prácticas.

Reescritura de consultas SQL para PostgreSQL

Hora proporciona una herramienta integrada para convertir automáticamente las consultas SQL de Oracle a PostgreSQL, incluyendo ajustes para el cumplimiento de ANSI. Esta función es útil en varias situaciones:

• Consultas dentro del código PL/SQL que no se transforman automáticamente al convertir a PL/pgSQL.
  Estas deben convertirse individualmente utilizando nuestra herramienta.

• Consultas utilizadas en aplicaciones que también se están migrando de Oracle a PostgreSQL.

En todos los casos, el flujo de trabajo es sencillo:
seleccione cualquier consulta dentro de cualquier ventana del editor SQL, haga clic con el botón derecho y elija «Reescribir SQL para PostgreSQL».

Ejemplo

Una vez seleccionada, KeepTool intenta convertir la consulta automáticamente.
Por ejemplo, se genera la siguiente consulta reescrita:

select 
   D.DEPARTMENT_NAME, D.DEPARTMENT_ID,
   E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.DEPARTMENT_ID
from DEPARTMENTS D
right join EMPLOYEES E on D.DEPARTMENT_ID = E.DEPARTMENT_ID 

El algoritmo maneja la mayoría de las consultas de forma fiable.
Sin embargo, es posible que las sentencias SQL muy complejas no se puedan convertir completamente en un solo paso.
En tales casos, intente convertir las subconsultas, los bloques UNION u otros componentes por separado para obtener los mejores resultados.

---

Resumen

La migración de una base de datos Oracle a PostgreSQL implica la conversión del esquema, la traducción de vistas, la transferencia de datos y la migración de la lógica procedimental. Hora de KeepTool simplifica todos estos pasos y le ofrece un control total sobre el SQL generado.

Esté atento a nuestro próximo artículo, en el que profundizaremos en la conversión de código PL/SQL complejo a PL/pgSQL de PostgreSQL.

---