Oracle vs Postgres: Diferencias de Esquema

La migración de bases de datos entre sistemas como Oracle y PostgreSQL es un proceso complejo que requiere una planificación detallada y una comprensión profunda de las diferencias arquitectónicas y de diseño entre ambas plataformas. Uno de los aspectos más cruciales a considerar es la estructura y el comportamiento de los esquemas, ya que varían significativamente entre Oracle y Postgres. Abordar estas diferencias de manera proactiva es fundamental para garantizar una transición fluida y minimizar los problemas post-migración.

Este artículo explora las distinciones fundamentales en el concepto de esquema entre Oracle y Postgres, detalla las diferencias clave en los objetos que componen un esquema y discute los desafíos comunes que surgen durante la migración de esquemas, junto con las herramientas y estrategias disponibles para superarlos.

¿Qué es un Esquema en Oracle y Postgres?

Aunque ambos sistemas utilizan el término "esquema", su implementación y relación con los usuarios difieren considerablemente.

• En Oracle, un esquema se conoce también como "usuario" y tiene el mismo nombre que este. Por defecto, cada usuario de Oracle tiene su propio esquema. Los objetos de base de datos (tablas, vistas, procedimientos, etc.) pertenecen directamente al esquema del usuario que los crea.
• En Postgres, los esquemas y los usuarios (roles) no son lo mismo. Un usuario puede poseer o tener permisos sobre múltiples esquemas. Si no se especifica explícitamente un esquema al crear un nuevo objeto, este se crea por defecto en el esquema "public". Una ventaja de la configuración en Postgres es que un usuario puede crear múltiples esquemas sin necesidad de crear usuarios separados para cada uno y otorgar permisos a otros para crear objetos dentro de esos esquemas.

Comprender esta diferencia fundamental es el primer paso para planificar una migración de esquemas efectiva, ya que afecta cómo se organizan y referencian los objetos dentro de la base de datos.

Diferencias Clave en los Objetos del Esquema

La conversión de esquemas de Oracle a Postgres implica adaptar la sintaxis y la funcionalidad de varios tipos de objetos de base de datos. A continuación, se detallan algunas de las diferencias más notables:

Columnas

Hasta la versión 12, Postgres no tenía un equivalente directo a las columnas virtuales de Oracle. En la actualidad, Postgres ofrece columnas generadas, que comparten muchas características con las columnas virtuales de Oracle. Al migrar, las columnas virtuales de Oracle deben convertirse a vistas o a las nuevas columnas generadas en Postgres, según sea apropiado.

Restricciones (Constraints)

Las restricciones como Primary Key, Foreign Key, Check, Not-Null y Unique funcionan de manera similar en ambos sistemas. Sin embargo, la gestión de restricciones aplazables (deferrable) difiere. En Oracle, puedes habilitar y deshabilitar restricciones; en Postgres, se definen como aplazables y se gestionan con SET CONSTRAINTS. Las restricciones no aplazables en Oracle que necesiten ser aplazables en Postgres a menudo deben ser eliminadas y recreadas.

Identificadores

Una diferencia importante es el manejo de mayúsculas y minúsculas en los nombres de objetos. Oracle convierte los nombres de esquemas, tablas, columnas y funciones a mayúsculas a menos que se especifiquen entre comillas dobles. Postgres, por otro lado, los convierte a minúsculas a menos que se citen. La consistencia en el uso de comillas es crucial durante la migración para evitar problemas de sensibilidad a mayúsculas/minúsculas.

Índices

Postgres soporta índices B-tree y descendentes de manera similar a Oracle. Sin embargo, ciertos tipos de índices de Oracle, como los índices reverse key, bitmap y join indexes, no son soportados actualmente en Postgres. Los índices globales tampoco son soportados. Esto requiere que se busquen alternativas o se rediseñen estos elementos durante la migración.

Particiones

Las particiones por Hash, List y Range deberían funcionar en Postgres después de la migración, ya que Postgres soporta estos métodos de particionamiento de manera nativa o a través de mecanismos como herencia y triggers.

Tablas

La sintaxis básica de CREATE TABLE es mayormente compatible. Las excepciones incluyen:

• Postgres carece de tablas temporales globales; se deben usar tablas temporales locales (LOCAL TEMP).
• Para el particionamiento, Postgres utiliza herencia, triggers y restricciones CHECK en lugar de la sintaxis de partición de Oracle.
• Parámetros de la cláusula STORAGE (INITRANS, MAXEXTENTS) no son reconocidos en Postgres y deben eliminarse.
• El parámetro PCTFREE de Oracle se reemplaza por el parámetro fillfactor de Postgres, que cumple una función similar.

Tablespaces

Aunque existen diferencias internas en la implementación, los tablespaces en Oracle y Postgres cumplen el mismo propósito de controlar la ubicación física de los datos y deberían funcionar después de la migración, aunque la sintaxis para crearlos y gestionarlos varía.

Tipos de Datos

Las diferencias en los tipos de datos son una de las áreas más críticas en la migración de esquemas. Algunos tipos de Oracle no tienen equivalentes directos en Postgres, o se comportan de manera diferente. La siguiente tabla resume algunas de las conversiones comunes y puntos a considerar:

Es vital revisar y ajustar la definición de columnas y el manejo de datos para cada tipo de dato afectado durante la migración.

El Proceso de Migración de Esquemas

La migración de esquemas es una fase clave dentro del proceso general de migración de Oracle a Postgres. Se realiza después de una fase de evaluación inicial que incluye:

1. Evaluación de la Aplicación y el Código: Analizar la dependencia del código en características específicas de Oracle.
2. Evaluación de Compatibilidad: Confirmar la compatibilidad de la aplicación con Postgres y los recursos del servidor.
3. Evaluación de Arquitectura y Limpieza: Identificar objetos obsoletos, reestructurar si es necesario (ej. separar OLTP y analítica).
4. Evaluación y Conversión del Esquema: Este paso se enfoca en identificar las diferencias de formato y estructura entre los esquemas y convertirlas.

La conversión del esquema implica adaptar definiciones de tablas, vistas, secuencias, índices, procedimientos almacenados, funciones, triggers y otros objetos para que sean compatibles con la sintaxis y la funcionalidad de Postgres. Realizar estos ajustes antes de la migración de datos es crucial.

Herramientas para la Migración de Esquemas

Existen varias herramientas que pueden ayudar a automatizar o facilitar la conversión de esquemas de Oracle a Postgres:

• Ora2pg: Una herramienta robusta de código abierto que se conecta a Oracle, extrae esquemas y tablas, y genera scripts SQL compatibles con Postgres.
• Ora_migrator: Una extensión de Postgres que usa un Foreign Data Wrapper (oracle_fdw) para extraer datos y estructuras de Oracle.
• Orafce: Una extensión de Postgres que implementa muchas funciones y paquetes comunes de Oracle en Postgres, reduciendo la necesidad de reescribir código. También soporta formatos de fecha y tipos de datos de Oracle.
• EDB Migration Portal: Un servicio web (específico para migrar a EDB Postgres Advanced Server) que ofrece evaluación, conversión de esquemas e informes de compatibilidad.
• AWS Schema Conversion Tool (AWS SCT): Una herramienta popular que automatiza la conversión de esquemas y código de base de datos, identificando y ayudando a abordar problemas de compatibilidad.
• Oracle SQL Developer: Aunque es una herramienta de Oracle, incluye capacidades de migración para transferir esquemas y datos a otras bases de datos, incluido Postgres.

Seleccionar la herramienta adecuada depende de la complejidad del esquema, el volumen de datos y si se necesita migrar a PostgreSQL puro o a una versión compatible con Oracle como EDB Postgres Advanced Server.

Desafíos Comunes y Soluciones Durante la Migración de Esquemas y Objetos

Más allá de las diferencias estructurales básicas, la migración de esquemas a menudo presenta desafíos relacionados con el comportamiento específico de ciertos objetos y sentencias SQL. Abordar estos puntos requiere una revisión cuidadosa y, a veces, reescritura del código.

Manejo de Restricciones

Como se mencionó, la gestión de restricciones aplazables difiere. Las sentencias SET CONSTRAINTS DEFERRED o IMMEDIATE deben usarse en Postgres. Las restricciones no aplazables en Oracle que deben ser aplazables en Postgres pueden requerir ser eliminadas y recreadas dentro de una transacción (BEGIN/COMMIT) para garantizar la consistencia.

Sentencia DELETE

En Oracle, la cláusula FROM es opcional en las sentencias DELETE (ej. DELETE mytable WHERE ...). En Postgres, la cláusula FROM es obligatoria (ej. DELETE FROM mytable WHERE ...). Todas las sentencias DELETE deben ser ajustadas.

Eliminación de Objetos (DROP)

En Postgres, el permiso para eliminar objetos está restringido al propietario de la tabla o a un superusuario. No es un privilegio que se pueda otorgar directamente como en Oracle. Si el código de la aplicación o los scripts dependen de otorgar este permiso, puede ser necesario reconfigurar los roles o permisos.

Tabla DUAL

Oracle requiere una cláusula FROM para cada sentencia SELECT, usando FROM DUAL cuando no se necesita una tabla real. Postgres no requiere la cláusula FROM en estos casos. Las referencias a FROM DUAL generalmente pueden ser omitidas en Postgres. Si es estrictamente necesaria, se puede crear una vista llamada DUAL.

Cadenas Vacías y NULL

En Oracle, las cadenas vacías ('') se consideran NULL. En Postgres, una cadena vacía es distinta de NULL. El operador IS NULL devolverá TRUE para una cadena vacía en Oracle, pero FALSE en Postgres. Esto afecta la lógica de la aplicación y las consultas que verifican si un valor es nulo o está vacío.

Federación vs. Foreign Data Wrappers (FDW)

La característica de Federación de Oracle permite tratar tablas de otras bases de datos como locales. Los Foreign Data Wrappers de Postgres (ej. oracle_fdw) ofrecen una funcionalidad similar pero más versátil, permitiendo conectar a una amplia gama de fuentes de datos. El código que utiliza la Federación de Oracle debe adaptarse para usar FDWs en Postgres.

Sentencia GRANT

Aunque la sentencia GRANT es similar, no todos los privilegios que se pueden otorgar en Oracle son otorgables en Postgres. Por ejemplo, el permiso para crear triggers se otorga en la tabla, pero solo el propietario puede eliminarlos en Postgres, a diferencia de Oracle.

Consultas Jerárquicas

Postgres no soporta la sintaxis START WITH ... CONNECT BY de Oracle para consultas jerárquicas. En su lugar, se utiliza la cláusula WITH RECURSIVE. Las consultas jerárquicas deben ser reescritas.

JOINs con (+)

Oracle utiliza el operador shorthand (+) para LEFT y RIGHT OUTER JOINs. Postgres no tiene este operador; se debe usar la sintaxis explícita LEFT OUTER JOIN o RIGHT OUTER JOIN.

Verificación NOT NULL

Oracle usa CHECK (columna IS NOT NULL) para definir columnas NOT NULL. Postgres utiliza una restricción NOT NULL directa definida en la columna, almacenada en el catálogo del sistema pg_attribute.

Packages

Postgres no tiene el concepto de packages de Oracle. Las funciones y procedimientos se organizan típicamente usando esquemas. La librería orafce implementa algunos packages estándar de Oracle. EDB Postgres Advanced Server ofrece compatibilidad nativa con packages de Oracle.

Conversión de PL/SQL a PL/pgSQL

PL/pgSQL de Postgres es similar a PL/SQL de Oracle (ambos son lenguajes imperativos, estructurados en bloques, con sintaxis similar para asignaciones, bucles y condicionales). Sin embargo, existen diferencias significativas en la sintaxis, el manejo de excepciones y, crucialmente, el manejo de transacciones. No se permite el control de transacciones (COMMIT/ROLLBACK) dentro de procedimientos almacenados en PL/pgSQL; debe manejarse desde la aplicación que llama al procedimiento.

Objetos Remotos

El acceso a objetos remotos en Oracle puede usar DBLINK. En Postgres, se utiliza el módulo DBLINK o, de forma más común y versátil, Foreign Data Wrappers (como oracle_fdw) para acceder a otras bases de datos.

ROWID, CTID y Columnas Identity

Postgres no tiene un equivalente exacto al pseudocolumn ROWID de Oracle, que proporciona la dirección física de una fila (aunque su valor puede cambiar). CTID en Postgres es similar pero cambia con VACUUM. La alternativa recomendada son las columnas Identity (GENERATED ALWAYS o GENERATED BY DEFAULT), que generan un valor único e inmutable para cada fila.

Secuencias

La sintaxis para obtener el siguiente valor de una secuencia difiere: Oracle usa nombre_secuencia.nextval, mientras que Postgres usa nextval('nombre_secuencia'). Esto requiere ajustes en el código que utiliza secuencias.

Función SUBSTR

El comportamiento de la función SUBSTR difiere con índices negativos. Oracle (ej. SUBSTR('ABC', -1)) devuelve 'C', mientras que Postgres devuelve 'ABC'. La extensión orafce proporciona una implementación de SUBSTR compatible con Oracle.

Sinónimos

Postgres no soporta sinónimos. Para acceder a objetos en otros esquemas, se utiliza la configuración del search_path de la sesión.

Función SYSDATE

SYSDATE de Oracle devuelve fecha y hora (en la zona horaria del servidor). Postgres no tiene una función equivalente directa. Se pueden usar funciones como statement_timestamp(), now(), transaction_timestamp() o clock_timestamp() dependiendo del momento específico que se necesite capturar.

Función TO_DATE

La función TO_DATE en Oracle devuelve un tipo de dato DATE que incluye fecha y hora. En Postgres, el tipo DATE es solo fecha. Para replicar el comportamiento de Oracle, se debe usar TO_TIMESTAMP() en Postgres. La extensión orafce también puede proporcionar una implementación compatible de TO_DATE.

Transacciones y Manejo de Errores

Oracle siempre usa transacciones (una sentencia inicia una). En Postgres, las transacciones deben iniciarse explícitamente con BEGIN (a menos que se cambie la configuración de autocommit). Ambos soportan niveles de aislamiento similares (Read Committed por defecto). El manejo de errores en tiempo de ejecución es diferente. En Postgres, un error aborta la transacción actual. Se deben usar bloques BEGIN...EXCEPTION...END para manejar excepciones y, a menudo, revertir a un savepoint o cerrar la conexión. La lógica de manejo de errores en PL/SQL debe adaptarse a PL/pgSQL.

Pruebas Después de la Migración de Esquemas

Una vez que el esquema ha sido convertido, es crucial realizar pruebas exhaustivas:

• Pruebas Funcionales: Cargar una muestra de datos y probar todas las funcionalidades de la aplicación que interactúan con la base de datos para asegurar que las consultas y operaciones DML funcionan correctamente y producen los resultados esperados.
• Pruebas de Rendimiento: Comparar el rendimiento de las consultas y cargas de trabajo en Postgres con el rendimiento original en Oracle. Ajustar la configuración de la base de datos, los índices o reescribir consultas si es necesario para optimizar el rendimiento en el nuevo entorno.

Preguntas Frecuentes

¿Cuál es la principal diferencia conceptual entre un esquema en Oracle y Postgres?

En Oracle, un esquema está ligado directamente a un usuario con el mismo nombre. En Postgres, un usuario (rol) puede poseer o interactuar con múltiples esquemas, y los objetos pueden residir en esquemas distintos al del usuario que los crea, como el esquema 'public' por defecto.

¿Qué herramientas me pueden ayudar a convertir mi esquema de Oracle a Postgres?

Existen varias herramientas como Ora2pg, AWS Schema Conversion Tool (AWS SCT), EDB Migration Portal y Oracle SQL Developer, que automatizan gran parte del proceso de conversión de objetos de esquema y código.

¿Cómo manejo las diferencias en los tipos de datos al migrar?

Es fundamental revisar el mapeo de tipos de datos entre Oracle y Postgres. Algunos tipos tienen equivalentes directos, otros requieren mapeos a tipos de datos diferentes en Postgres (ej. VARCHAR2 a VARCHAR/TEXT, NUMBER a NUMERIC/INT/BIGINT, DATE de Oracle a TIMESTAMP de Postgres) o el uso de mecanismos como Large Objects para BLOBs grandes. Herramientas como Orafce pueden ayudar con la compatibilidad de funciones y tipos de datos.

¿Qué hago con los packages de Oracle que no existen en Postgres?

En Postgres, las funcionalidades de los packages se suelen organizar utilizando esquemas y agrupando funciones y procedimientos. La extensión Orafce implementa algunos packages estándar. EDB Postgres Advanced Server ofrece compatibilidad nativa con packages.

¿Puedo controlar las transacciones dentro de un procedimiento almacenado en Postgres como en Oracle?

No. En PL/pgSQL de Postgres, no se permite el control explícito de transacciones (COMMIT/ROLLBACK) dentro de procedimientos almacenados. La lógica de transacción debe manejarse desde la aplicación que llama al procedimiento.

En conclusión, la migración de esquemas de Oracle a Postgres es una tarea que requiere una comprensión detallada de las diferencias entre ambos sistemas. Desde la definición básica de esquema hasta el comportamiento de objetos específicos y la sintaxis de SQL, cada elemento debe ser evaluado y adaptado. Con una planificación adecuada, el uso de herramientas de migración y pruebas rigurosas, es posible lograr una migración exitosa a la poderosa y flexible plataforma de Postgres.

Si quieres conocer otros artículos parecidos a Oracle vs Postgres: Diferencias de Esquema puedes visitar la categoría Bases de datos.