Migra usuarios y esquemas de base de datos de Oracle® a Cloud SQL para PostgreSQL

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. En este documento, se analizan las diferencias básicas entre Oracle Database y Cloud SQL para PostgreSQL, ya que se relacionan con la creación de usuarios, esquemas, tablas, índices y vistas.

Además de la sección de configuración de introducción, la serie incluye las siguientes partes:

• Migra usuarios de Oracle a Cloud SQL para PostgreSQL: terminología y funcionalidad
• Migra usuarios de Oracle a Cloud SQL para PostgreSQL: tipos de datos, usuarios y tablas
• Migra usuarios de Oracle a Cloud SQL para PostgreSQL: consultas, procedimientos almacenados, funciones y activadores
• Migra usuarios de Oracle a Cloud SQL para PostgreSQL: seguridad, operaciones, supervisión y registro
• Migra usuarios y esquemas de base de datos de Oracle a Cloud SQL para PostgreSQL (este documento)

Diferencias de terminología entre Oracle y Cloud SQL para PostgreSQL

Oracle y Cloud SQL para PostgreSQL tienen diferentes arquitecturas y terminología para las instancias, bases de datos, usuarios y esquemas. Para obtener un resumen de estas diferencias, consulta la parte de terminología de esta serie.

Exporta configuraciones de Oracle

Uno de los primeros pasos a la hora de planificar una migración a Cloud SQL para PostgreSQL es revisar la configuración de parámetros existente en la base de datos de Oracle de origen. La configuración de la asignación de memoria, el grupo de caracteres y los parámetros de almacenamiento es particularmente útil, ya que ayuda a determinar la configuración inicial y el tamaño del entorno de destino de Cloud SQL para PostgreSQL. Existen varios métodos para extraer la configuración de los parámetros de Oracle. A continuación, le presentamos algunos:

• Los informes del repositorio de cargas de trabajo automático (AWR) contienen datos de la asignación de recursos (CPU, RAM), la configuración de los parámetros de instancia y el máximo de sesiones activas.
• DBA_HIST, V$OSSTAT y V$LICENSE para los detalles de uso de la CPU.
• Vista V$PARAMETER para los parámetros de configuración de la base de datos.
• Vista V$NLS_PARAMETERS para los parámetros de lenguaje de base de datos.
• Vista DBA_DATA_FILES para calcular el tamaño de almacenamiento de la base de datos.
• El SPFILE de Oracle para opciones de configuración de la instancia de base de datos.
• Herramientas del programador de trabajos (por ejemplo, crontab) para identificar copias de seguridad de rutina o períodos de mantenimiento que se deben tener en cuenta.

Importa y configura usuarios en Cloud SQL para PostgreSQL

En un nivel alto, cada esquema de Oracle debe crearse como su propio esquema en PostgreSQL. En una base de datos de Oracle, la palabra user (usuario) es sinónimo de schema (esquema). Esto significa que se crea un esquema cuando creas un usuario. Siempre hay una relación uno a uno entre los usuarios y los esquemas. En PostgreSQL, los usuarios y los esquemas se crean por separado. Se puede crear un usuario sin crear el esquema correspondiente. A fin de mantener la misma estructura de usuarios o esquemas de Oracle en PostgreSQL, puedes crear un esquema para cada usuario.

En la siguiente tabla, se ilustran ejemplos de conversiones.

Tipo de acción	Tipo de base de datos	Comparación de comandos
Crea usuarios y esquemas	Oracle	CREATE USER username IDENTIFIED BY password;
	PostgreSQL	Los conceptos de usuario y esquema son distintos en PostgreSQL; por lo tanto, se requieren dos declaraciones CREATE separadas CREATE USER username WITH PASSWORD 'password'; CREATE SCHEMA schema_name;
Asigna funciones	Oracle	GRANT CONNECT TO username;
	PostgreSQL	GRANT pg_monitor TO username;
Otorga privilegios	Oracle	GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;
	PostgreSQL	GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;
Revoca privilegios	Oracle	REVOKE UPDATE ON HR.EMPLOYEES FROM username;
	PostgreSQL	REVOKE UPDATE ON HR.EMPLOYEES FROM username;
Otorga DBA/superusuario	Oracle	GRANT DBA TO username;
	PostgreSQL	GRANT cloudsqlsuperuser TO username;
Descarta usuario	Oracle	DROP USER username CASCADE;
	PostgreSQL	Los conceptos de usuario y esquema son distintos en PostgreSQL; por lo tanto, se requieren dos declaraciones DROP separadas DROP USER username; DROP SCHEMA schema_name CASCADE;
Metadatos de usuarios	Oracle	DBA_USERS
	PostgreSQL	pg_catalog.pg_user
Metadatos de permisos	Oracle	DBA_SYS_PRIVS DBA_ROLE_PRIVS SESSION_PRIVS
	PostgreSQL	pg_catalog.pg_roles
String de conexión de la CLI	Oracle	sqlplus username/password@host/tns_alias Sqlplus username/password@host:IP/sid
	PostgreSQL	Sin mensaje de contraseña: PGPASSWORD=password psql -h hostname -U username -d database_name Con mensaje de contraseña: psql -h hostname -U username -W -d database_name

Usuarios de bases de datos de Oracle 12c

Existen dos tipos de usuarios en Oracle 12c: usuarios comunes y locales. Los usuarios comunes se crean en la base de datos de contenedores (CDB) raíz, incluidas las bases de datos conectables (PDB). Se identifican mediante el prefijo C## en su nombre de usuario. Los usuarios locales solo se crean en una PDB específica. Se pueden crear diferentes usuarios de bases de datos con nombres de usuario idénticos en varias PDB. Cuando migres de Oracle 12c a PostgreSQL, modifica los usuarios y los permisos para que se adapten a la arquitectura de PostgreSQL. A continuación, se muestran dos ejemplos comunes para ilustrar estas diferencias:

# Oracle local user
SQL> ALTER SESSION SET CONTAINER=pdb;
SQL> CREATE USER username IDENTIFIED BY password QUOTA 50M ON USERS;

# PostgreSQL user for a single database and schema
postgres=> CREATE USER username WITH PASSWORD 'password';
postgres=> GRANT CONNECT TO DATABASE database_name TO username;
postgres=> GRANT USAGE ON SCHEMA schema_name TO username;
postgres=> -- Optionally, grant object privileges in the schema
postgres=> GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL PROCEDURES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL ROUTINES IN SCHEMA schema_name TO username;

# Oracle common user
SQL> CREATE USER c##username IDENTIFIED BY password CONTAINER=ALL;

# PostgreSQL user with permissions for all database (use the local user script above and repeat it for each database and schema)

Administra usuarios a través de la consola de Google Cloud

Si deseas ver los usuarios configurados actuales de Cloud SQL para PostgreSQL, ve a la siguiente página en la consola de Google Cloud:

Google Cloud > Almacenamiento > SQL > Instancia > Usuarios

Importa definiciones de tablas y vistas

Oracle y PostgreSQL difieren en términos de distinción entre mayúsculas y minúsculas. Los nombres de Oracle no distinguen entre mayúsculas y minúsculas. Los nombres de PostgreSQL no distinguen entre mayúsculas y minúsculas, excepto cuando se encuentran entre comillas dobles. Muchas herramientas de exportación de esquemas y de generación de SQL para Oracle, como DBMS_METADATA.GET_DDL, agregan automáticamente comillas dobles a los nombres de los objetos. Estas comillas pueden generar todo tipo de problemas después de la migración. Recomendamos quitar todas las comillas en los nombres de objetos de las declaraciones de lenguaje de definición de datos (DDL) antes de crear los objetos en PostgreSQL.

Sintaxis de la creación de tablas

Cuando conviertas tablas de Oracle en tipos de datos de MySQL, el primer paso es extraer las instrucciones de creación de tablas de Oracle de la base de datos de origen. La siguiente consulta de muestra extrae el DDL para la tabla de ubicaciones del esquema de HR:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOCATIONS') FROM DUAL;

CREATE TABLE "HR"."LOCATIONS"
   (  "LOCATION_ID" NUMBER(4,0),
  "STREET_ADDRESS" VARCHAR2(40),
  "POSTAL_CODE" VARCHAR2(12),
  "CITY" VARCHAR2(30) CONSTRAINT "LOC_CITY_NN" NOT NULL ENABLE,
  "STATE_PROVINCE" VARCHAR2(25),
  "COUNTRY_ID" CHAR(2),
  CONSTRAINT "LOC_ID_PK" PRIMARY KEY ("LOCATION_ID")
...
      CONSTRAINT "LOC_C_ID_FK" FOREIGN KEY ("COUNTRY_ID")
          REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID") ENABLE

El resultado completo incluye elementos de almacenamiento, índices e información del espacio de tabla, que se omitió, ya que la declaración CREATE TABLE de PostgreSQL no admite estos elementos adicionales.

Una vez que se extrae el DDL, quita las comillas de los nombres y realiza la conversión de tablas de acuerdo con la tabla de conversión de tipos de datos de Oracle a PostgreSQL. Comprueba cada tipo de datos de la columna para ver si puede convertirse tal como está o, si no se admite, elige un tipo de datos diferente según la tabla de conversiones. Por ejemplo, a continuación se muestra el DDL convertido para la tabla de ubicaciones.

CREATE TABLE HR.LOCATIONS (
  LOCATION_ID NUMERIC(4,0),
  STREET_ADDRESS VARCHAR(40),
  POSTAL_CODE VARCHAR(12),
  CITY VARCHAR(30) CONSTRAINT LOC_CITY_NN NOT NULL,
  STATE_PROVINCE VARCHAR(25),
  COUNTRY_ID CHAR(2),
  CONSTRAINT LOC_ID_PK PRIMARY KEY (LOCATION_ID),
  CONSTRAINT LOC_C_ID_FK FOREIGN KEY (COUNTRY_ID)
REFERENCES HR.COUNTRIES (COUNTRY_ID)
)

Create table as select (CTAS)

La declaración CREATE TABLE AS SELECT (CTAS) se usa para crear una tabla nueva basada en una tabla existente. Ten en cuenta que solo se copian los nombres de columnas y los tipos de datos de la columna, mientras que las restricciones y los índices no se copian. PostgreSQL es compatible con el estándar ANSI SQL para la funcionalidad de CTAS y con la declaración CTAS de Oracle.

Columnas invisibles de Oracle 12c

PostgreSQL no admite columnas invisibles. Como solución alternativa, crea una vista que contenga solo las columnas visibles.

Restricciones de tablas

Oracle proporciona seis tipos de restricciones de tablas que se pueden definir en la creación de la tabla o después de su creación con el comando ALTER TABLE. Los tipos de restricciones de Oracle son PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL y REF. Además, Oracle le permite al usuario controlar el estado de una restricción a través de las siguientes opciones:

• INITIALLY IMMEDIATE: Comprueba la restricción al final de cada instrucción de SQL posterior (el estado predeterminado).
• DEFERRABLE/NOT DEFERRABLE: habilita el uso de la cláusula SET CONSTRAINT en transacciones posteriores hasta que se envía una declaración COMMIT.
• INITIALLY DEFERRED: Comprueba la restricción al final de las transacciones posteriores.
• VALIDATE/NO VALIDATE: comprueba (o no comprueba de forma deliberada) filas nuevas o modificadas en busca de errores. Estos parámetros dependen de si la restricción es ENABLED o DISABLED.
• ENABLED/DISABLED: Especifica si la restricción se debe aplicar de forma predeterminada después de la creación (ENABLED de forma predeterminada).

PostgreSQL también admite seis tipos de restricciones de tabla: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL y EXCLUDE. Sin embargo, hay algunas diferencias notables entre los tipos de restricciones de Oracle y PostgreSQL, que incluyen los siguientes:

• PostgreSQL no admite la restricción REF de Oracle.
• PostgreSQL no crea de forma automática un índice en las columnas referentes para una restricción de clave externa. Se necesita una declaración CREATE INDEX separada en las columnas referentes si se requiere un índice.
• PostgreSQL no admite la cláusula ON DELETE SET NULL de Oracle. Esta cláusula le indica a Oracle que establezca cualquier valor dependiente en las tablas secundarias en NULL cuando se borre el registro en la tabla superior.
• No se admiten restricciones en VIEWS, a excepción de CHECK OPTION.
• PostgreSQL no admite la inhabilitación de las restricciones. PostgreSQL admite la opción NOT VALID cuando se agrega una nueva restricción de clave externa o verificación mediante una instrucción ALTER TABLE. Esta opción le indica a PostgreSQL que omita las verificaciones de integridad referencial en los registros existentes en la tabla secundaria.

En la siguiente tabla, se resumen las diferencias clave entre los tipos de restricciones de Oracle y PostgreSQL:

Tipo de restricción de Oracle	Compatibilidad con Cloud SQL para PostgreSQL	Equivalente de Cloud SQL para PostgreSQL
PRIMARY KEY	Sí	PRIMARY KEY
FOREIGN KEY	Sí	Usa la misma sintaxis ANSI SQL como Oracle. Usa la cláusula ON DELETE para manejar los casos de eliminación de registros superiores de FOREIGN KEY. PostgreSQL proporciona tres opciones para manejar casos en los que se borran datos de la tabla superior y se hace referencia a una tabla secundaria con una restricción FOREIGN KEY: ON DELETE CASCADE ON DELETE RESTRICT ON DELETE NO ACTION PostgreSQL no admite la cláusula ON DELETE SET NULL de Oracle. Usa la cláusula ON UPDATE para controlar los casos de actualizaciones de registros superiores deFOREIGN KEY. PostgreSQL ofrece tres opciones para controlar los eventos de actualización de restricción de FOREIGN KEY: ON UPDATE CASCADE ON UPDATE RESTRICT ON UPDATE NO ACTION PostgreSQL no crea de forma automática un índice en las columnas referentes para una restricción de clave externa.
UNIQUE	Sí	Crea un índice UNIQUE de forma predeterminada.
CHECK	Sí	CHECK
NOT NULL	Sí	NOT NULL
REF	No	No compatible.
DEFERRABLE/NOT DEFERRABLE	Sí	DEFERRABLE/NOT DEFERRABLE
INITIALLY IMMEDIATE	Sí	INITIALLY IMMEDIATE
INITIALLY DEFERRED	Sí	INITIALLY DEFERRED
VALIDATE/NO VALIDATE	No	No compatible.
ENABLE/DISABLE	No	Habilitados de forma predeterminada. Usa la opción NOT VALID cuando se agregue una nueva restricción de clave externa o verificación a la tabla mediante una declaración ALTER TABLE para omitir las verificaciones de integridad referencial en los registros existentes.
Restricción en VISTAS	No	No es compatible, excepto el VIEW WITH CHECK OPTION.
Metadatos de restricciones	Oracle	DBA_CONSTRAINTS
	PostgreSQL	INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Columnas virtuales y generadas

Las columnas virtuales de Oracle se basan en los resultados del cálculo de otras columnas. Aparecen como columnas regulares, pero sus valores derivan de un cálculo simultáneo que realiza el motor de la base de datos de Oracle y no se almacena en la base de datos. Las columnas virtuales se pueden usar con restricciones, índices, partición de tablas y claves externas, pero no se pueden manipular mediante operaciones de lenguaje de manipulación de datos (DML).

Las columnas generadas de PostgreSQL son comparables a las columnas virtuales de Oracle en términos de funcionalidad. Sin embargo, a diferencia de Oracle, las columnas generadas en PostgreSQL se almacenan y debes especificar un tipo de datos para cada columna generada, lo que significa que ocupan almacenamiento como si fueran columnas normales.

Ejemplo de una columna virtual en Oracle:

SQL> CREATE TABLE PRODUCTS (
        PRODUCT_ID     INT PRIMARY KEY,
        PRODUCT_TYPE   VARCHAR2(100) NOT NULL,
        PRODUCT_PRICE  NUMBER(6,2) NOT NULL,
        PRICE_WITH_TAX AS (ROUND(PRODUCT_PRICE * 1.01, 2))
);

SQL> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE)
     VALUES(1, 'A', 99.99);

SQL> SELECT * FROM PRODUCTS;

PRODUCT_ID PRODUCT_TYPE         PRODUCT_PRICE PRICE_WITH_TAX
---------- -------------------- ------------- --------------
         1 A                            99.99         100.99

Ejemplo equivalente en PostgreSQL:

postgres=> CREATE TABLE PRODUCTS (
postgres(>         PRODUCT_ID     INT PRIMARY KEY,
postgres(>         PRODUCT_TYPE   VARCHAR(100) NOT NULL,
postgres(>         PRODUCT_PRICE  NUMERIC(6,2) NOT NULL,
postgres(>         PRICE_WITH_TAX NUMERIC GENERATED ALWAYS AS (ROUND(PRODUCT_PRICE * 1.01, 2)) STORED
postgres(> );

postgres=> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE) VALUES(1, 'A', 99.99);

postgres=> SELECT * FROM PRODUCTS;

 product_id | product_type | product_price | price_with_tax
------------+--------------+---------------+----------------
          1 | A            |         99.99 |         100.99
(1 row)

Índices de la tabla

Oracle y PostgreSQL ofrecen una variedad de algoritmos de indexación y tipos de índices que se pueden usar para diversas aplicaciones. La siguiente es una lista de los algoritmos de indexación disponibles en PostgreSQL:

Algoritmo de índice	Descripción
Árbol B	Tipo de índice predeterminado para PostgreSQL que se usa a fin de acelerar las consultas de igualdad y rango Admite todos los tipos de datos básicos y se puede usar para recuperar valores NULL. Los valores de índice se ordenan de forma ascendente, según la configuración predeterminada, pero pueden configurarse en orden descendente
Hash	Se usa para acelerar las búsquedas de igualdad. Es más eficiente que el índice de árbol B, pero se limita solo a las búsquedas de igualdad.
GIN	Índices de árbol invertido Es más eficiente que el índice de árbol B en el caos de las columnas que contienen varios valores de componentes, como array y texto
GiST	No es un tipo de índice único, sino una infraestructura para definir índices que puede admitir más operadores de comparación que un índice de árbol B normal. Es útil para los datos geométricos cuando se necesita optimizar las búsquedas de “vecino más cercano”
SP-GiST	Al igual que GiST, SP-GiST es una infraestructura para estrategias de indexación definidas por el usuario Permite una amplia variedad de estructuras de datos no balanceadas, como los árboles cuaternarios (quadtrees) No está disponible en Cloud SQL para PostgreSQL
BRIN	Índices de rango de bloques Almacena resúmenes de los rangos de bloques físicos de una tabla Para columnas con un orden de clasificación lineal Es útil para la búsqueda de rangos en tablas grandes

En la siguiente tabla, se comparan los tipos de índices entre Oracle y PostgreSQL:

Índice de Oracle	Descripción	Compatible con PostgreSQL	Equivalente de PostgreSQL
Índice de mapas de bits	Almacena un mapa de bits para cada clave de índice, ideal a fin de proporcionar recuperación de datos a las cargas de trabajo OLAP.	No	Un tipo de índice que controla la forma en que los datos se almacenan a nivel de la tabla y del índice
Índice de árbol B	El tipo de índice más común, adecuado para una variedad de cargas de trabajo y se puede configurar en la clasificación ASC|DESC.	Sí	Índice de árbol B
Índice compuesto	Se crean más de dos columnas para mejorar el rendimiento de la recuperación de datos. El orden de las columnas dentro del índice determina la ruta de acceso.	Sí	Índices de varias columnas Se pueden especificar hasta 32 columnas cuando se crea un índice de varias columnas.
Índice basado en funciones	Almacena el resultado de una función aplicada en los valores de una columna de la tabla.	Sí	Indices en expresiones
Índice único	Un índice de árbol B que aplica una restricción UNIQUE en los valores indexados por columna.	Sí	Índice único
Índice de dominio de aplicación	Se diseñaron para indexar datos no relacionales, como datos de audio o video, datos de LOB y otros tipos no textuales.	No	N/A
Índice invisible	Función de Oracle que te permite administrar, mantener y probar índices sin afectar la toma de decisiones del optimizador.	No	N/A
Tabla organizada por índices	N/A	No	PostgreSQL no admite tablas organizadas por índice. La declaración CLUSTER le indica a PostgreSQL que organice el almacenamiento de tablas según un índice especificado. Su uso es similar a la tabla organizada en índices de Oracle. Sin embargo, el agrupamiento en clústeres es una operación única y PostgreSQL no mantiene la estructura de la tabla en las actualizaciones posteriores. Se necesita un agrupamiento en clústeres periódico y manual.
Índice local y global	Se usa para indexar tablas particionadas en una base de datos de Oracle. Cada índice se define como LOCAL o GLOBAL.	No	Los índices de trabajo de partición de PostgreSQL tienen la misma funcionalidad que los índices locales de Oracle (es decir, el índice se define a nivel de la partición, no se admite el nivel global).
Índices parciales para tablas particionadas (Oracle 12c)	Crea un índice en un subconjunto de particiones de una tabla. Es compatible con LOCAL y GLOBAL.	Sí	Crea un índice en un subconjunto de particiones de una tabla. Es posible crear índices solo en un subconjunto de tablas secundarias. .
CREATE/DROP INDEX	Comando que se usa para crear y descartar índices	Sí	PostgreSQL admite el comando CREATE INDEX También admite ALTER TABLE tableName ADD INDEX indexName columnName
ALTER INDEX ... REBUILD	Vuelve a compilar el índice, lo que puede generar un bloqueo exclusivo en la tabla indexada.	Requiere una sintaxis diferente	PostgreSQL permite volver a compilar índices con la declaración REINDEX. La tabla está bloqueada para las operaciones de escritura durante esta operación y solo se permiten las operaciones de lectura.
ALTER INDEX ... REBUILD ONLINE	Vuelve a compilar un índice sin crear un bloqueo exclusivo en la tabla.	Requiere una sintaxis diferente	PostgreSQL admite volver a compilar los índices de forma simultánea con la declaración REINDEX TABLE CONCURRENTLY. En este modo, PostgreSQL intenta volver a compilar los índices con un bloqueo mínimo, aunque esta tarea posiblemente demore más tiempo y requiera más recursos.
Compresión de índices	Una característica para reducir el tamaño físico del índice.	No	N/A
Asigna el índice a un espacio de tabla	Crea un espacio de tabla de índice que se puede almacenar en un disco distinto de los datos de la tabla para reducir los cuellos de botella de E/S del disco.	No	Aunque PostgreSQL permite que se cree un índice en un espacio de tabla definido por el usuario, no puedes crear espacios de tabla en Cloud SQL para PostgreSQL y el índice debe compilarse en el espacio de tabla predeterminado.
Metadatos de índices (tablas/vistas)	Oracle	DBA_INDEXES DBA_PART_INDEXES DBA_IND_COLUMNS
	PostgreSQL	pg_catalog.pg_index pg_catalog.pg_attribute pg_catalog.pg_class

Consideraciones sobre las conversiones de los índices

En la mayoría de los casos, los índices de Oracle pueden convertirse de forma sencilla en índices de árbol B de PostgreSQL, ya que este tipo de índice es el más usado. Al igual que en una base de datos de Oracle, se crea un índice de forma automática en los campos PRIMARY KEY de una tabla. Del mismo modo, se crea un índice UNIQUE de forma automática en los campos que tienen una restricción UNIQUE. Además, los índices secundarios se crean con la declaración CREATE INDEX estándar.

En el siguiente ejemplo, se ilustra cómo una tabla de Oracle con varios campos indexados se puede convertir en PostgreSQL:

SQL> CREATE TABLE ORA_IDX_TO_PG (
        col1 INT PRIMARY KEY,
        col2 VARCHAR2(60),
        col3 DATE,
        col4 CLOB,
        col5 VARCHAR2(20)
      );

-- Single-field index
SQL> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);

-- Composite index
SQL> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);

-- Unique index
SQL> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(col3);

-- Function-based index
SQL> CREATE INDEX idx_func_col3 ON
        ora_idx_to_pg(EXTRACT(MONTH FROM col3));

-- CLOB index
SQL> CREATE INDEX idx_col4 ON
       ora_idx_to_pg(col4) INDEXTYPE IS CTXSYS.CONTEXT;

-- Invisible index
SQL> CREATE INDEX idx_col5_inv ON
        ora_idx_to_pg(col5) INVISIBLE;

-- Drop index
SQL> DROP INDEX idx_col5_inv;

postgres=> CREATE TABLE ORA_IDX_TO_PG (
postgres(> col1 INT PRIMARY KEY,
postgres(> col2 VARCHAR(60),
postgres(> col3 DATE,
postgres(> col4 TEXT,
postgres(> col5 VARCHAR(20)
postgres(> );

-- Single index (supported)
postgres=> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);

-- Composite index (supported)
postgres=> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);

-- Unique index (supported)
postgres=> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(COL3);

-- Function-based index (supported)
postgres=> CREATE INDEX idx_func_col3 ON
postgres->         ora_idx_to_pg(EXTRACT(MONTH FROM col3));

-- CLOB (Supported, but requires different syntax. See Full Text Search for details)
postgres=> CREATE INDEX idx_col4 ON ora_idx_to_pg
postgres->         USING GIN (to_tsvector('english', col4));

-- Invisible index (not supported)
-- Optional - create the index as a B-tree index
postgres=> CREATE INDEX idx_col5 ON ora_idx_to_pg(col5);

-- Drop index
postgres=> DROP INDEX idx_col2;

SQL> SELECT ui.table_name,
            ui.index_name,
            ui.index_type,
            ic.column_name
     FROM user_indexes ui JOIN user_ind_columns ic
     ON ui.index_name = ic.index_name
     WHERE ui.table_name = 'ORA_IDX_TO_PG'
     ORDER BY 4;

postgres=> select distinct
postgres->     t.relname as table_name,
postgres->     i.relname as index_name,
postgres-> pg_get_indexdef(ix.indexrelid) index_definition
postgres-> from
postgres->     pg_class t,
postgres->     pg_class i,
postgres->     pg_index ix
postgres-> where
postgres->     t.oid = ix.indrelid
postgres->     and i.oid = ix.indexrelid
postgres->     and t.relname = 'ora_idx_to_pg'
postgres-> order by
postgres->     t.relname,
postgres->     i.relname;

-- OR Use psql \d command:
postgres=> \d ora_idx_to_pg
                  Table "public.ora_idx_to_pg"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 col1   | integer               |           | not null |
 col2   | character varying(60) |           |          |
 col3   | date                  |           |          |
 col4   | text                  |           |          |
 col5   | character varying(20) |           |          |
Indexes:
    "ora_idx_to_pg_pkey" PRIMARY KEY, btree (col1)
    "idx_col2" btree (col2)
    "idx_col4" gin (to_tsvector('english'::regconfig, col4))
    "idx_col5" btree (col5)
    "idx_cols3_2" btree (col3 DESC, col2)
    "idx_func_col3" btree (date_part('month'::text, col3))

postgres=>

Partición de tablas

Oracle y PostgreSQL ofrecen funcionalidades de partición para dividir tablas grandes. Esto se logra mediante la segmentación física de una tabla en partes más pequeñas, en las que cada parte contiene un subconjunto horizontal de las filas. La tabla particionada se conoce como tabla superior y sus filas se almacenan físicamente en sus particiones. Aunque PostgreSQL no admite todos los tipos de partición de Oracle, es compatible con los más comunes.

En las siguientes secciones, se describen los tipos de particiones compatibles con PostgreSQL, con un ejemplo sobre cómo crear las particiones que corresponden a ese tipo.

Partición RANGE

Este tipo de partición asigna filas a particiones según los valores de columna que se encuentran dentro de un rango determinado. Cada partición contiene filas para las que el valor de expresión de partición se encuentra dentro de un rango determinado. Es importante tener en cuenta que los rangos no se superponen en las particiones.

Ejemplo

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY RANGE (store_id);

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES FROM (MINVALUE) TO (6);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES FROM (6) TO (11);
CREATE TABLE employees_p2 PARTITION OF employees
 FOR VALUES FROM (11) TO (16);
CREATE TABLE employees_p3 PARTITION OF employees
 FOR VALUES FROM (16) TO (21);

Partición LIST

Al igual que la partición RANGE, la partición LIST asigna filas a las particiones según los valores de columna que se encuentran dentro de un conjunto predefinido de valores. Los valores clave que aparecen en cada partición se enumeran de forma explícita para las particiones de LIST.

Ejemplo

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY LIST (store_id);

CREATE TABLE employees_pNorth PARTITION OF employees
 FOR VALUES IN (3,5,6);
CREATE TABLE employees_pEast PARTITION OF employees
 FOR VALUES IN (1,2,10);
CREATE TABLE employees_pWest PARTITION OF employees
 FOR VALUES IN (4,12,13);
CREATE TABLE employees_pCnrl PARTITION OF employees
 FOR VALUES IN (7,8,15);

Partición de HASH

La partición HASH es más adecuada cuando el objetivo es lograr una distribución uniforme de datos entre todas las particiones. Se asigna un valor de columna (o expresión en función de un valor de columna para generar un hash) y el valor de fila a la partición correspondiente a ese valor de hash. Los valores de hash deben asignarse de forma única a las particiones y todos los valores insertados deben asignarse a una sola partición.

Ejemplo

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY HASH (date_part('year', hired));

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE employees_p2 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE employees_p3 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partición de varios niveles

La partición en varios niveles es un método que permite crear una jerarquía de particiones para una sola tabla. Cada partición se divide en una cantidad de particiones diferentes. La cantidad de subparticiones puede variar de una partición a otra.

Ejemplo

CREATE TABLE sales (
 Saleid    INT,
 sale_date DATE,
 cust_code VARCHAR(15),
 income    DECIMAL(8,2))
PARTITION BY RANGE(date_part('year', sale_date));

CREATE TABLE sales_2019 PARTITION OF sales
 FOR VALUES FROM (2019) TO (2020)
 PARTITION BY RANGE(date_part('month', sale_date));

CREATE TABLE sales_2019_q1 PARTITION OF sales_2019
 FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2019_q2 PARTITION OF sales_2019
 FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2019_q3 PARTITION OF sales_2019
 FOR VALUES FROM (7) TO (10);
CREATE TABLE sales_2019_q4 PARTITION OF sales_2019
 FOR VALUES FROM (10) TO (13);

CREATE TABLE sales_2020 PARTITION OF sales
 FOR VALUES FROM (2020) TO (2021)
 PARTITION BY RANGE(date_part('month', sale_date));

CREATE TABLE sales_2020_q1 PARTITION OF sales_2020
 FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2020_q2 PARTITION OF sales_2020
 FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2020_h2 PARTITION OF sales_2020
 FOR VALUES FROM (7) TO (13);

Conecta o desconecta particiones

En PostgreSQL, las particiones se pueden agregar o quitar de la tabla superior. Una partición que se desconecta se puede volver a conectar a la misma tabla. Además, se pueden especificar nuevas condiciones de partición cuando se vuelve a conectar la partición, lo que permite que se ajusten los límites de partición.

Ejemplo

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY RANGE (date_part('year', hired));

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES FROM (2010) TO (2015);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES FROM (2015) TO (2020);

-- changing partition boundaries
BEGIN TRANSACTION;
ALTER TABLE employees DETACH PARTITION employees_p1;
ALTER TABLE employees ATTACH PARTITION employees_p1 FOR VALUES FROM (2015) TO (2022);
COMMIT TRANSACTION;

La siguiente tabla describe dónde se usan los tipos de partición de Cloud SQL para PostgreSQL y dónde se recomienda una conversión.

Tipo de partición de Oracle	Compatible con PostgreSQL	Implementación de PostgreSQL
Particiones de RANGE	Sí	PARTITION BY RANGE
Particiones de LIST	Sí	PARTITION BY LIST
Particiones de HASH	Sí	PARTITION BY HASH
SUB-PARTITIONING	Sí	Partición de varios niveles
Particiones de intervalo	No	No compatible
Asesor de particiones	No	No compatible
Partición de preferencia	No	No compatible
Partición basada en columnas virtuales	No	Como solución alternativa, considera particionar directamente con la expresión de la columna virtual: CREATE TABLE users ( id INT, username VARCHAR(20), first_letter VARCHAR(1) GENERATED ALWAYS AS ( UPPER(SUBSTR(TRIM(username), 1, 1)) ) STORED ) PARTITION BY LIST (UPPER(SUBSTR(TRIM(username), 1, 1)));
Partición automática de listas	No	No compatible
Divide las particiones	No	Como solución alternativa, considera desconectar o conectar las particiones de tabla para ajustar los límites de partición.
Particiones de intercambio	Sí	DETACH / ATTACH PARTITION
Partición de varios tipos (partición compuesta)	Sí	Partición de varios niveles
Metadatos de particiones	Oracle	DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS
	PostgreSQL	pg_catalog.pg_class pg_catalog.pg_partitioned_table

En el siguiente ejemplo, se muestra una comparación en paralelo de la creación de particiones de tablas en ambas plataformas. Ten en cuenta que PostgreSQL no admite hacer referencia a un espacio de tabla en la cláusula PARTITIONS del comando CREATE TABLE.

Implementación de Oracle

CREATE TABLE employees (
empid NUMBER,
fname VARCHAR2(30),
lname VARCHAR2(30),
hired DATE,
separated DATE,
job_code NUMBER,
store_id NUMBER)
PARTITION BY LIST (store_id) (
PARTITION employees_pNorth VALUES (3,5,6) TABLESPACE users,
PARTITION employees_pEast VALUES (1,2,10) TABLESPACE users,
PARTITION employees_pWest VALUES (4,12,13) TABLESPACE users,
PARTITION employees_pCnrl VALUES (7,8,15) TABLESPACE users
);

Implementación de PostgreSQL

CREATE TABLE employees (
empid INT,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE,
separated DATE,
job_code INT,
store_id INT)
PARTITION BY LIST (store_id);

CREATE TABLE employees_pNorth PARTITION OF employees
FOR VALUES IN (3,5,6);
CREATE TABLE employees_pEast PARTITION OF employees
FOR VALUES IN (1,2,10);
CREATE TABLE employees_pWest PARTITION OF employees
FOR VALUES IN (4,12,13);
CREATE TABLE employees_pCnrl PARTITION OF employees
FOR VALUES IN (7,8,15);

Tablas temporales

En una base de datos de Oracle, las tablas temporales se llaman GLOBAL TEMPORARY TABLES, mientras que en PostgreSQL se conocen como tablas temporales. La funcionalidad básica de una tabla temporal es idéntica en ambas plataformas. Sin embargo, existen algunas diferencias notables:

• Oracle almacena la estructura de la tabla temporal para el uso repetido, incluso después de un reinicio de la base de datos, mientras que PostgreSQL almacena la tabla temporal solo durante la sesión.
• Diferentes usuarios con los permisos adecuados pueden acceder a una tabla temporal en una base de datos de Oracle. Por el contrario, solo se puede acceder a una tabla temporal en PostgreSQL durante la sesión en la que se creó, a menos que se haga referencia a la tabla temporal con nombres calificados del esquema.
• En una base de datos de Oracle, se distingue entre las tablas temporales GLOBAL y LOCAL que especifican si el contenido de la tabla es global o específico de la sesión. En PostgreSQL, las palabras clave GLOBAL y LOCAL se admiten por razones de compatibilidad, pero no afectan la visibilidad de los datos.
• Si se omite la cláusula ON COMMIT cuando se crea una tabla temporal, el comportamiento predeterminado en la base de datos de Oracle es ON COMMIT DELETE ROWS, lo que significa que Oracle trunca la tabla temporal después de cada confirmación. Por el contrario, en PostgreSQL, el comportamiento predeterminado es conservar las filas en la tabla temporal después de cada confirmación.

En la siguiente tabla, se destacan las diferencias en las tablas temporales entre Oracle y Cloud SQL para PostgreSQL.

Función temporal de la tabla	Implementación de Oracle	Implementación de PostgreSQL
Sintaxis	CREATE GLOBAL TEMPORARY TABLE	CREATE TEMPORARY TABLE
Accesibilidad	Accesible desde varias sesiones	Accesible solo desde la sesión del creador, a menos que se haga referencia a ella con nombres calificados del esquema.
Compatibilidad de índices	Sí	Sí
Compatibilidad de claves externas	Sí	Sí
Preserva DDL	Sí	No
Acción predeterminadaON COMMIT	Se borran los registros	Se conservan los registros
ON COMMIT PRESERVE ROWS	Sí	Sí
ON COMMIT DELETE ROWS	Sí	Sí
ON COMMIT DROP	No	Sí
supportALTER TABLE	Sí	Sí
Recopila estadísticas	DBMS_STATS.GATHER_TABLE_STATS	ANALYZE
Oracle 12c GLOBAL_TEMP_ TABLE_STATS	DBMS_STATS.SET_TABLE_PREFS	ANALYZE

Columnas sin usar

La característica de Oracle de marcar columnas específicas como UNUSED a menudo se usa para quitar columnas de tablas sin quitar los datos de la columna de forma física. Esto es para evitar las posibles cargas altas que se generan cuando se descartan columnas de tablas grandes.

En PostgreSQL, descartar una columna grande no quita los datos de la columna del almacenamiento físico y, por lo tanto, es una operación rápida incluso en tablas grandes. No es necesario marcar una columna como UNUSED como en una base de datos de Oracle. El espacio ocupado por la columna eliminada se recupera mediante declaraciones DML nuevas o durante una operación VACUUM posterior.

Tablas de solo lectura

Las tablas de solo lectura son una función de Oracle que marca las tablas como de solo lectura mediante el comando ALTER TABLE. En Oracle 12c R2, esta función también está disponible para tablas con particiones y subparticiones. PostgreSQL no ofrece una característica equivalente, pero hay dos soluciones alternativas posibles:

• Otorgar el permiso SELECT en las tablas de usuarios específicos. Ten en cuenta que esto no impide que el propietario de la tabla realice operaciones DML en sus tablas.
• Crear una réplica de lectura de Cloud SQL para PostgreSQL y dirigir a los usuarios a las tablas de réplica que son tablas de solo lectura. Esta solución requiere agregar una instancia de réplica de lectura a una instancia existente de Cloud SQL para PostgreSQL.

• Crea un activador de base de datos que genere excepciones en las declaraciones DML, por ejemplo:

  -- Define trigger function
  CREATE OR REPLACE FUNCTION raise_readonly_exception() RETURNS TRIGGER AS $$
  BEGIN
    RAISE EXCEPTION 'Table is readonly!';
    RETURN NULL;
  END;
  $$ LANGUAGE 'plpgsql';
  
  -- Fire trigger when DML statements is executed on read only table
  CREATE TRIGGER myTable_readonly_trigger
  BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON myTable FOR EACH STATEMENT
  EXECUTE PROCEDURE raise_readonly_exception();
  
  -- Testing the trigger
  postgres=> INSERT INTO myTable (id) VALUES (1);
  ERROR:  Table is readonly!
  CONTEXT:  PL/pgSQL function raise_readonly_exception() line 3 at RAISE
  postgres=>
  

Grupo de caracteres

Oracle y PostgreSQL admiten una amplia variedad de grupos de caracteres, intercalaciones y Unicode, incluida la compatibilidad con lenguajes de un solo byte y varios bytes. Además, las bases de datos de PostgreSQL que residen en la misma instancia se pueden configurar con grupos de caracteres distintos. Consulta la lista de grupos de caracteres compatibles en PostgreSQL.

En una base de datos de Oracle, los grupos de caracteres se especifican a nivel de la base de datos (Oracle 12g R1 o anterior) o a nivel de la base de datos conectable (Oracle 12g R2 o posterior). En PostgreSQL, se especifica un grupo de caracteres predeterminado cuando se crea una instancia nueva de Cloud SQL para PostgreSQL. Cada base de datos creada dentro de esa instancia puede crearse con un grupo de caracteres diferente. El orden de clasificación y la clasificación de caracteres se pueden especificar por columna de tabla.

Ejemplo

-- Create a database using UTF-8 character set and ja_JP.UTF collation
postgres=> CREATE DATABASE jpdb WITH ENCODING 'UTF8' LC_COLLATE='ja_JP.UTF8' LC_CTYPE='ja_JP.UTF8' TEMPLATE=template0;

-- Query the character set and collation settings of all databases
postgres=> SELECT datname AS DATABASE_NAME, datcollate AS LC_COLLATE, datctype AS LC_CTYPE from pg_database;
 database_name | lc_collate |  lc_ctype
---------------+------------+------------
 cloudsqladmin | en_US.UTF8 | en_US.UTF8
 template0     | en_US.UTF8 | en_US.UTF8
 template1     | en_US.UTF8 | en_US.UTF8
 postgres      | en_US.UTF8 | en_US.UTF8
 jpdb          | ja_JP.UTF8 | ja_JP.UTF8
(5 rows)

-- Alternatively, use psql \l command to query the database settings
postgres=> \l
                                                List of databases
     Name      |       Owner       | Encoding |  Collate   |   Ctype    |            Access privileges
---------------+-------------------+----------+------------+------------+-----------------------------------------
 cloudsqladmin | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 |
 postgres      | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/cloudsqlsuperuser                  +
               |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser+
               |                   |          |            |            | testuser=CTc/cloudsqlsuperuser
 template0     | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqladmin                       +
               |                   |          |            |            | cloudsqladmin=CTc/cloudsqladmin
 template1     | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqlsuperuser                   +
               |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser
-- Specifying column level collation
postgres=> CREATE TABLE test1 (
postgres(>     a text COLLATE "de_DE",
postgres(>     b text COLLATE "es_ES"
postgres(> );

Vistas

PostgreSQL admite vistas simples y complejas. Para las opciones de creación de vistas, hay algunas diferencias entre Oracle y PostgreSQL. En la siguiente tabla, se destacan estas diferencias.

Función de Vista de Oracle	Descripción	Compatibilidad con Cloud SQL para PostgreSQL	Consideraciones sobre las conversiones
FORCE	Crea una vista sin verificar si existen las tablas o vistas de origen.	No	No hay una opción equivalente disponible.
CREATE OR REPLACE	Crea una vista no existente o reemplaza una vista existente.	Sí	PostgreSQL admite el comando CREATE OR REPLACE para las vistas.
WITH CHECK OPTION	Especifica el nivel de aplicación cuando se realizan operaciones DML en la vista.	Sí	El valor predeterminado es CASCADED, que también hace una evaluación de las vistas a las que se hace referencia. La palabra clave LOCAL hace que solo se evalúe la vista actual.
WITH READ-ONLY	Permite solo operaciones de lectura en la vista. No se permiten operaciones DML.	No	Una solución alternativa es otorgar privilegios de SELECT en la vista a todos los usuarios.
VISIBLE | INVISIBLE (Oracle 12c)	Especifica si una columna, según la vista, es visible o invisible para el usuario.	No	Crea el VIEW solo con las columnas obligatorias.

En el siguiente ejemplo de conversión, se demuestra la conversión de Oracle a Cloud SQL para PostgreSQL.

-- Create view to retrieve employees from department 100 using the WITH CHECK -- OPTION option
SQL> CREATE OR REPLACE FORCE VIEW vw_emp_dept100
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100
WITH CHECK OPTION;

-- Perform an UPDATE operation on the VIEW
SQL> UPDATE vw_emp_dept100
     SET salary=salary+1000;

postgres=> CREATE OR REPLACE VIEW vw_emp_dept100
postgres-> AS
postgres-> SELECT EMPLOYEE_ID,
postgres->        FIRST_NAME,
postgres->        LAST_NAME,
postgres->        SALARY,
postgres->        DEPARTMENT_ID
postgres-> FROM EMPLOYEES
postgres-> WHERE DEPARTMENT_ID=100
postgres-> WITH CHECK OPTION;

-- Perform an UPDATE operation on the VIEW
postgres=> UPDATE vw_emp_dept100
postgres-> SET salary=salary+1000;

-- Update one employee department id to 60
postgres=> UPDATE vw_emp_dept100
postgres-> SET DEPARTMENT_ID=60
postgres-> WHERE EMPLOYEE_ID=110;

ERROR:  new row violates check option for view "vw_emp_dept100"
DETAIL:  Failing row contains (110, John, Chen, JCHEN, 515.124.4269, 1997-09-28, FI_ACCOUNT, 9200.00, null, 108, 60).

Ver administración de acceso

Los propietarios de una vista deben tener privilegios en las tablas base para crear la vista. El usuario de una vista necesita los permisos SELECT adecuados en la vista. También necesitan los permisos INSERT, UPDATE, DELETE apropiados en la vista cuando realizan operaciones DML a través de la vista. En cualquier caso, los usuarios no necesitan permisos en las tablas subyacentes.

¿Qué sigue?

• Explora más acerca de las cuentas de usuario de PostgreSQL.
• Explora arquitecturas de referencia, diagramas y prácticas recomendadas sobre Google Cloud. Consulta nuestro Cloud Architecture Center.