Migra usuarios de Oracle® a Cloud SQL para PostgreSQL: tipos de datos, usuarios y tablas

Este documento es parte de una serie que proporciona información clave y orientación relacionada con la planificación y realización de migraciones de bases de datos de Oracle® 11g/12c a Cloud SQL para PostgreSQL versión 12. Además de la sección de configuración de introducción, la serie incluye las siguientes partes:

Tipos de datos

Cloud SQL para PostgreSQL proporciona varios tipos de datos que son totalmente equivalentes o similares a los tipos de datos proporcionados por Oracle. En la siguiente tabla, se enumeran los tipos de datos de PostgreSQL más comunes, seguidos de una comparación entre los tipos de datos primitivos de Oracle y los tipos de datos correspondientes de Cloud SQL para PostgreSQL. Si un tipo de datos no es compatible, se muestra un tipo alternativo.

Los 12 tipos de datos básicos de Cloud SQL para PostgreSQL

Familia de tipos de datos de Cloud SQL para PostgreSQL Nombre del tipo de datos de Cloud SQL para PostgreSQL Especificación del tipo de datos
String/carácter CHAR(n) Almacena n caracteres con exactitud. Alias de CHARACTER(n).
CHARACTER(n) Almacena n caracteres con exactitud.
VARCHAR(n) Almacena una cantidad variable de caracteres, hasta un máximo de n caracteres. Alias de CHARACTER VARYING(n).
CHARACTER VARYING(n) Almacena una cantidad variable de caracteres, hasta un máximo de n caracteres.
TEXT Es una variante específica de VARCHAR que no requiere que especifiques un límite superior en la cantidad de caracteres. La cadena de caracteres más larga posible que se puede almacenar es de 1 GB (igual para todos los tipos de datos de caracteres de cadena).
Numérico SMALLINT El valor mínimo es −32768 | El valor máximo es 32767.
INTEGER El valor mínimo es −2147483648 | El valor máximo es 2147483647.
BIGINT El valor mínimo es 2^63 | El valor máximo es 2^63−1.
REAL Número de punto flotante de precisión simple (4 bytes).
DOUBLE PRECISION Número de punto flotante de precisión doble (8 bytes)
DECIMAL (p,s) Es capaz de almacenar cualquier valor con p dígitos y s decimales.
NUMERIC(p,s) Es capaz de almacenar cualquier valor con p dígitos y s decimales.
SMALLSERIAL Número entero que aumenta automáticamente. El valor mínimo es 1 | El valor máximo es 32767.
SERIAL Número entero que aumenta automáticamente. El valor mínimo es 1 | El valor máximo es 2147483647.
BIGSERIAL Número entero que aumenta automáticamente. El valor mínimo es 1 | El valor máximo es 2^63-1.
Monetario MONEY Importe de la moneda con una precisión fraccionaria fija. El valor mínimo es -92233720368547758.08. | El valor máximo es +92233720368547758.07.
Fecha y hora DATE - Son valores con una parte de fecha, pero sin parte de tiempo.
El rango admitido es de 4713 a.C. a 5874897 d.C., con una resolución de 1 día.
TIMESTAMP (p) - Son valores que contienen partes de fecha y hora.
- El rango admitido es 4713 a.C. a 294276 d.C.
- La resolución es de p dígitos fraccionarios en segundos (o 1 microsegundo si no se especifica).
TIMESTAMP (p) with time zone - Son valores que contienen la fecha, la hora y la zona horaria.
- El rango admitido es 4713 a.C. a 294276 d.C.
- La resolución es de p dígitos fraccionarios en segundos (o 1 microsegundo si no se especifica).
TIME (p) - Valores con una parte de tiempo, pero sin una parte de fecha.
- El rango admitido es de 00:00:00 a 24:00:00.
- La resolución es de p dígitos fraccionarios en segundos (o 1 microsegundo si no se especifica).
TIME (p) with time zone - Valores con una hora y una zona horaria, pero sin una parte de fecha.
- El rango admitido es de 00:00:00 a 24:00:00.
- La resolución es de p dígitos fraccionarios en segundos (o 1 microsegundo si no se especifica).
INTERVAL (p) - Intervalo de tiempo
- El intervalo admitido es de -178000000 a 178000000 años.
: - La resolución es de p dígitos fraccionarios en segundos (o 1 microsegundo si no se especifica).
JSON JSON Son datos JSON textuales.
JSONB Son datos JSON binarios.
XML XML Datos XML.

Geométrico		 GEOMETRY Es el tipo de columna que se debe especificar cuando se usan los siguientes modelos de datos.
POINT Es un valor (x,y).
LINE Una tupla (A, B, C) en la que Ax + By + C = 0 y A y B no son 0.

O

Punto 1 y punto 2 en los siguientes formatos:

[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
LSEG Un segmento de líneas ((x1,y1),(x2,y2)).
BOX Cuadro rectangular ((x1,y1),(x2,y2)).
PATH Una secuencia de puntos ((x1,y1),...).
POLYGON Es una secuencia de puntos, en efecto una ruta de acceso cerrada.
CIRCLE (x,y),r) (punto central y radio)
Lógicos BOOLEAN - Contiene un valor verdadero, falso o nulo.
- Acepta valores como yes, true, t, on y 1 como verdaderos.
Bit/datos binarios BIT (n) Es una string de bits de longitud fija.
BYTEA Datos binarios/array de bytes.
Tipos de direcciones de red CIDER Redes IPv4 e IPv6.
INET Hosts y redes IPv4 e IPv6.
MACADDR Direcciones MAC.
MACADDR8 Direcciones MAC (formato EUI-64).
Otros PG_LSN Número de secuencia de registro de Cloud SQL para PostgreSQL.
TSQUERY Es una consulta de búsqueda de texto.
TSVECTOR Es un documento de búsqueda de texto.
TXID_SNAPSHOT Es una instantánea del ID de transacción a nivel de usuario.
UUID Es un identificador único universal.

Conversión de tipos de datos de Oracle a Cloud SQL para PostgreSQL

Familia de tipos de datos de Oracle Nombre del tipo de datos de Oracle Especificación del tipo de datos de Oracle Equivalente de Cloud SQL para PostgreSQL a Oracle Valor correspondiente/alternativo de Cloud SQL para PostgreSQL
String/carácter CHAR(n) El tamaño máximo es de 2,000 bytes. Sí CHAR(n)
CHARACTER(n) El tamaño máximo es de 2,000 bytes. Sí CHARACTER(n)
NCHAR(n) El tamaño máximo es de 2,000 bytes. No CHAR(n)
VARCHAR(n) El tamaño máximo es de 2,000 bytes. Sí VARCHAR(n)
NCHAR VARYING(n) String UTF-8 de extensión variable
El tamaño máximo es de 4,000 bytes.		 No CHARACTER VARYING(n)
VARCHAR2(n) 11g El tamaño máximo es de 4,000 bytes. El tamaño máximo es de 32 KB en PL/SQL. Sí* VARCHAR(n)
VARCHAR2(n)
12g		 El tamaño máximo es de 32,767 bytes MAX_STRING_SIZE= EXTENDED. Sí* VARCHAR(n)
NVARCHAR2(n) El tamaño máximo es de 4,000 bytes. No VARCHAR(n)
LONG El tamaño máximo es de 2 GB. Sí* TEXT
RAW(n) El tamaño máximo es de 2,000 bytes. Sí* BYTEA
LONG RAW El tamaño máximo es de 2 GB. Sí* BYTEA
Numérico NUMBER Número de punto flotante. Sí* NUMERIC(p,s)
NUMBER(*) Número de punto flotante. Sí* DOUBLE PRECISION
NUMERIC(p,s) La precisión puede variar de 1 a 38. Sí NUMERIC(p,s)
FLOAT(p,s) Número de punto flotante. Sí* DOUBLE PRECISION
DEC(p,s) Número de punto fijo. Sí DEC(p,s)
DECIMAL(p,s) Número de punto fijo. Sí DECIMAL(p,s)
INT Número entero de 38 dígitos. Sí INT
INTEGER Número entero de 38 dígitos. Sí INTEGER
SMALLINT Número entero de 38 dígitos. Sí SMALLINT
REAL Número de punto flotante. Sí* DOUBLE PRECISION
DOUBLE PRECISION Número de punto flotante. Sí DOUBLE PRECISION
Fecha y hora DATE Almacena datos de fecha y hora (año, mes, día, hora, minuto y segundo). Sí* TIMESTAMP(0)
TIMESTAMP(p) Fecha y hora con fracción. Sí TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE Fecha y hora con fracción y zona horaria. Sí TIMESTAMP (p) WITH TIME ZONE
INTERVAL YEAR(p) TO MONTH Intervalo de fecha. Sí* INTERVAL YEAR TO MONTH
INTERVAL DAY(p) TO SECOND(s) Intervalo de tiempo y día. Sí* INTERVAL DAY TO SECOND(s)
Lógicos BOOLEAN Valores TRUE, FALSE y NULL. No se puede asignar a una columna de tabla de base de datos. Sí BOOLEAN
XML XMLTYPE Datos XML. No XML
LOB BFILE El puntero al archivo binario, con un tamaño máximo de 4 GB. No VARCHAR(255)
CLOB Objeto grande de caracteres con un tamaño de archivo máximo de 4 GB. No TEXT
BLOB Objeto binario grande con un tamaño máximo de 4 GB. No BYTEA
NCLOB String de Unicode de extensión variable con un tamaño de archivo máximo de 4 GB. No TEXT
ROWID ROWID Dirección de fila física. No CTID
UROWID(n) ID de fila universal de las direcciones de fila lógicas. No CTID
Espaciales SDO_ GEOMETRY La descripción geométrica de un objeto espacial. Sí Postgres tiene varios tipos de geometría, entre ellos point, line, path, polygon, circle, ....
SDO_TOPO_ GEOMETRY Describe una geometría de topología. Sí PostGIS, una extensión de Postgres muy usada, proporciona varios tipos de topología.
SDO_GEORASTER Una cuadrícula de trama o un objeto de imagen se almacena en una sola fila. Sí PostGIS, una extensión de Postgres muy usada, proporciona una herramienta para cargar archivos de trama en la base de datos.
Tipos de medios ORDDicom Admite el almacenamiento y la administración archivos con formato Digital Imaging and Communications in Medicine (DICOM). Sí, con solución alternativa Postgres proporciona un tipo binario, bytea, para almacenar objetos grandes.
ORDDoc Admite el almacenamiento y la administración de cualquier tipo de datos multimedia. Sí, con solución alternativa Postgres proporciona un tipo binario, bytea, para almacenar objetos grandes.
ORDImage Admite el almacenamiento y la administración de datos de imágenes. Sí, con solución alternativa Postgres proporciona un tipo binario, bytea, para almacenar objetos grandes.
ORDVideo Admite el almacenamiento y la administración de datos de video. Solución alternativa Postgres proporciona un tipo binario, bytea, para almacenar objetos grandes.

* El tipo de datos correspondiente en Cloud SQL para PostgreSQL es, en su mayoría, compatible con Oracle, pero lleva un nombre diferente.

Tipos definidos por el usuario

Oracle se refiere a los tipos definidos por el usuario (UDT) como OBJECT TYPES, que se administran mediante PL/SQL. Los tipos definidos por el usuario le permiten a este crear tipos de datos complejos y dedicados a las aplicaciones que se basan en la lista de tipos de datos de Oracle incorporada y se extienden desde allí.

Tipos definidos por el usuario de Oracle Implementación o descripción general de Oracle Compatibilidad con Cloud SQL para PostgreSQL Solución correspondiente o alternativa para Cloud SQL para PostgreSQL
Tipo de datos abstracto (ADT) CREATE TYPE ADT_DEMO AS OBJECT (
ID NUMBER(6),
NAME VARCHAR2(20)
);		 De forma parcial con una sintaxis diferente Los tipos compuestos de Cloud SQL para PostgreSQL se especifican mediante una lista de nombres de atributos y tipos de datos:

CREATE TYPE ADT_DEMO AS
(
ID NUMERIC(6),
NAME VARCHAR(20)
);
Tipo VARRAY CREATE TYPE VARRAY_DEMO AS VARRAY (5) OF VARCHAR2(25); No Como solución alternativa, Cloud SQL para PostgreSQL permite que las columnas se definan como arrays:

CREATE TABLE VARRAY_TABLE (
NAME VARCHAR(25)[]
);
Tipo de tabla anidada CREATE TYPE NTT_DEMO AS OBJECT (
ID NUMBER(6),
NAME VARCHAR2(20)
) ;
CREATE TYPE NTT_TABLE AS TABLE OF NTT_DEMO;		 No Como solución alternativa, usa una combinación de tipos compuestos y definiciones de columnas de arrays en Cloud SQL para PostgreSQL a fin de lograr una funcionalidad similar al tipo de tabla anidada de Oracle.

CREATE TYPE NTT_DEMO AS
(
ID NUMERIC(6),
NAME VARCHAR(20)
);
CREATE TABLE NTT_TABLE (
RECORD NTT_DEMO[]
);
Tipo incompleto Los tipos incompletos son tipos sin atributos y métodos. Otros tipos pueden hacer referencia a ellos. Sin embargo, debes proporcionar la definición del tipo antes de usarlo.

CREATE TYPE INCOMPLETE_DEMO;

 No N/A

Usuarios

En esta sección, se analiza cómo crear usuarios y asignarles permisos, y la necesidad de convertir tablas de Oracle en tablas de Cloud SQL para PostgreSQL.

Creación y permisos de usuarios

Las cuentas de usuario de la base de datos de Oracle (el "usuario" y el "esquema" de Oracle son idénticos) se pueden usar para autenticar y conectarse a las sesiones de base de datos, mientras que el acceso a la autorización se establece a nivel individual del usuario para permisos y objetos específicos de la base de datos.

En general, hay dos tipos de usuarios de base de datos:

  • Administradores: administran la instancia de la base de datos, los usuarios y los recursos.
  • Cuentas de usuario: entregan operaciones lógicas, como aplicaciones.

Los administradores otorgan privilegios a las cuentas de usuario de la aplicación y del usuario para acceder a los objetos de base de datos. Los permisos de base de datos de Oracle se otorgan a un usuario para operaciones específicas (por ejemplo, con el fin de crear una sesión o conectarse) o a objetos de base de datos específicos (por ejemplo, SELECT en una tabla específica o EXECUTE en un procedimiento almacenado específico).

En Oracle, existe el concepto de usuarios y funciones. Los usuarios se utilizan para autenticarse con la base de datos, y las funciones proporcionan grupos de permisos que pueden otorgarse en conjunto.

En Cloud SQL para PostgreSQL, los usuarios y los roles son sinónimos. Un usuario de Cloud SQL para PostgreSQL es un rol con permiso connect. Puedes usar la declaración CREATE USER o CREATE ROLE para crear un usuario de la base de datos. A diferencia de Oracle, en Cloud SQL para PostgreSQL, los esquemas y los usuarios se crean por separado. Un esquema es una colección de objetos (por ejemplo, tablas, tipos de datos, funciones, etc.) y es propiedad de un usuario.

Comando "create user" de Oracle
CREATE USER user_name IDENTIFIED BY password;
Comando "create user" de Cloud SQL para PostgreSQL
CREATE USER user WITH PASSWORD 'password';

O

CREATE ROLE user WITH LOGIN PASSWORD 'password';

Consideraciones sobre las conversiones

  • Los usuarios se definen a nivel de raíz y son válidos para todas las bases de datos contenidas en el mismo clúster de Cloud SQL para PostgreSQL. Esto es similar a los “usuarios comunes” en Oracle 12c.
  • La sintaxis CREATE USER de Cloud SQL para PostgreSQL es diferente a la de Oracle y no se puede migrar tal como está. CREATE USER en Cloud SQL para PostgreSQL es un alias para CREATE ROLE, excepto que la opción LOGIN esté habilitada de forma predeterminada.

Tablas

Las tablas de Oracle se construyen a partir de muchos elementos, como tipos de datos de columnas, restricciones de tablas, índices, particiones y funciones de propiedad de tablas de Oracle, entre otros. Si deseas migrar correctamente a las tablas de base de datos de Cloud SQL para PostgreSQL, todos los elementos de la tabla de Oracle deben convertirse en tablas de Cloud SQL para PostgreSQL. Algunos elementos se admiten con modificaciones menores o sin ninguna modificación, mientras que otros deben modificarse por completo.

Desde la perspectiva de la migración, es probable que la conversión de PL/SQL en Cloud SQL para PostgreSQL requiera más esfuerzo, ya que convertir las tablas de Oracle en tablas de Cloud SQL para PostgreSQL es una etapa fundamental con importancia significativa y más implicaciones en el rendimiento y el tamaño de los datos.

A continuación, se mencionan las diferencias principales entre las tablas de Oracle y Cloud SQL para PostgreSQL y las características relacionadas. Estas diferencias se analizan en las otras partes de la serie.

  • Sintaxis de la creación de tablas
  • Metadatos de índices y tablas
  • Compatibilidad con restricciones
  • Compatibilidad y limitaciones de los tipos de datos
  • Índices
  • Particiones y administración de particiones
  • Mantenimiento de índices y tablas
  • Tablas temporales
  • Vistas
  • Columnas invisibles y visibles (Oracle 12c)
  • Grupos de caracteres de tablas y columnas

¿Qué sigue?