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
yV$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 separadasCREATE USER username WITH PASSWORD 'password'; |
|
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 separadasDROP USER username; |
|
Metadatos de usuarios | Oracle |
DBA_USERS |
PostgreSQL |
pg_catalog.pg_user |
|
Metadatos de permisos | Oracle |
DBA_SYS_PRIVS |
PostgreSQL |
pg_catalog.pg_roles |
|
String de conexión de la CLI | Oracle |
sqlplus username/password@host/tns_alias |
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áusulaSET CONSTRAINT
en transacciones posteriores hasta que se envía una declaraciónCOMMIT
.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 esENABLED
oDISABLED
.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 enNULL
cuando se borre el registro en la tabla superior. - No se admiten restricciones en
VIEWS
, a excepción deCHECK 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ónALTER 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 :
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 :
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 |
|
Hash |
|
GIN |
|
GiST |
|
SP-GiST |
|
BRIN |
|
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 |
|
PostgreSQL | pg_catalog.pg_index |
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:
|
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 |
PostgreSQL | pg_catalog.pg_class |
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
yLOCAL
que especifican si el contenido de la tabla es global o específico de la sesión. En PostgreSQL, las palabras claveGLOBAL
yLOCAL
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 esON 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.