Como migrar usuários e esquemas do banco de dados da Oracle® para o Cloud SQL para PostgreSQL

Este documento faz parte de uma série que fornece informações importantes e orientações relacionadas ao planejamento e à realização de migrações de banco de dados Oracle® 11g/12c para o Cloud SQL para PostgreSQL versão 12. Este documento aborda as diferenças básicas entre o banco de dados Oracle® e o Cloud SQL para PostgreSQL, porque eles estão relacionados à criação de usuários, esquemas, tabelas, índices e visualizações.

Além da parte de configuração introdutória, a série inclui as seguintes partes:

Diferenças de terminologia entre o Oracle e o Cloud SQL para PostgreSQL

O Oracle e o Cloud SQL para PostgreSQL têm arquiteturas e terminologias diferentes para instâncias, bancos de dados, usuários e esquemas. Para um resumo dessas diferenças, consulte a parte de terminologia desta série.

Como exportar configurações do Oracle

Uma das primeiras etapas ao planejar uma migração para o Cloud SQL para PostgreSQL é analisar as configurações de parâmetros atuais no banco de dados Oracle de origem. As configurações de alocação de memória, conjunto de caracteres e parâmetros de armazenamento são especialmente úteis porque informam a configuração inicial e o dimensionamento do ambiente de destino do Cloud SQL para PostgreSQL. Há vários métodos para extrair configurações de parâmetros do Oracle. Veja alguns exemplos comuns:

  • Os relatórios do repositório de carga de trabalho automático (AWR, na sigla em inglês) contêm dados de alocação de recursos (CPU, RAM), configuração de parâmetros da instância e número máximo de sessões ativas.
  • DBA_HIST, V$OSSTAT e V$LICENSE para detalhes do uso da CPU.
  • Visualização V$PARAMETER para os parâmetros de configuração do banco de dados.
  • Visualização V$NLS_PARAMETERS para os parâmetros de linguagem do banco de dados.
  • Visualização DBA_DATA_FILES para calcular o tamanho do armazenamento do banco de dados.
  • O Oracle SPFILE para configurações de instância do banco de dados.
  • Ferramentas de programador de job (por exemplo, crontab) para identificar backups de rotina ou janelas de manutenção que precisam ser considerados.

Como importar e configurar usuários no Cloud SQL para PostgreSQL

De modo geral, cada esquema do Oracle precisa ser criado como seu próprio esquema no PostgreSQL. Em um banco de dados Oracle, user é sinônimo de schema. Isso significa que um esquema é criado quando você cria um usuário. Sempre há uma relação explícita entre usuários e esquemas. No PostgreSQL, usuários e esquemas são criados separadamente. É possível criar um usuário sem criar um esquema correspondente. Para manter a mesma estrutura de usuário ou de esquema do Oracle no PostgreSQL, crie um esquema para cada usuário.

A tabela a seguir ilustra exemplos de conversão:

tipo de ação Tipo de banco de dados Comparação de comandos
Criar usuário e esquema Oracle CREATE USER username IDENTIFIED BY password;
PostgreSQL Usuário e esquema são conceitos distintos no PostgreSQL. Portanto, exigem duas instruções CREATE separadas

CREATE USER username WITH PASSWORD 'password';
CREATE SCHEMA schema_name;
Atribuição de funções Oracle GRANT CONNECT TO username;
PostgreSQL GRANT pg_monitor TO username;
Concessão de privilégios Oracle GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;
PostgreSQL GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;
Como revogar privilégios Oracle REVOKE UPDATE ON HR.EMPLOYEES FROM username;
PostgreSQL REVOKE UPDATE ON HR.EMPLOYEES FROM username;
Conceder DBA/superusuário Oracle GRANT DBA TO username;
PostgreSQL GRANT cloudsqlsuperuser TO username;
Remover usuário Oracle DROP USER username CASCADE;
PostgreSQL Usuário e esquema são conceitos distintos no PostgreSQL, portanto, exigem duas instruções DROP separadas

DROP USER username;
DROP SCHEMA schema_name CASCADE;
Metadados do usuário Oracle DBA_USERS
PostgreSQL pg_catalog.pg_user
Metadados de permissões Oracle DBA_SYS_PRIVS
DBA_ROLE_PRIVS
SESSION_PRIVS
PostgreSQL pg_catalog.pg_roles
String de conexão da CLI Oracle sqlplus username/password@host/tns_alias
Sqlplus username/password@host:IP/sid
PostgreSQL Sem solicitação de senha:

PGPASSWORD=password psql -h hostname -U username -d database_name

Com solicitação de senha:

psql -h hostname -U username -W -d database_name

Usuários do banco de dados Oracle 12c:

Existem dois tipos de usuários no Oracle 12c: comuns, e os locais. Usuários comuns são criados no CDB raiz, incluindo PDBs. Elas são identificadas pelo prefixo C## no nome de usuário. Os usuários locais são criados apenas em um PDB específico. Diferentes usuários de banco de dados com nomes de usuário idênticos podem ser criados em vários PDBs. Ao migrar do Oracle 12c para o PostgreSQL, modifique usuários e permissões para atender à arquitetura do PostgreSQL. Veja dois exemplos comuns para ilustrar essas diferenças:

# 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)

Como gerenciar usuários no console

Para visualizar os usuários configurados atuais do Cloud SQL para PostgreSQL, acesse a seguinte página no console:

Google Cloud > Armazenamento > SQL > Instância > Usuários

Captura de tela da página "Usuários".

Como importar definições de tabela e visualização

O Oracle e o PostgreSQL são diferentes em termos de diferenciação de maiúsculas e minúsculas. Os nomes do Oracle não diferenciam maiúsculas de minúsculas. Os nomes do PostgreSQL não diferenciam maiúsculas de minúsculas, exceto quando estão entre aspas duplas. Muitas ferramentas de exportação de esquema e geração de SQL para Oracle, como DBMS_METADATA.GET_DDL, adicionam automaticamente aspas duplas aos nomes de objetos. Aspas podem levar a todos os tipos de problemas após a migração. Recomendamos a remoção de todas as aspas em torno dos nomes de objetos das instruções de linguagem de definição de dados (DDL, na sigla em inglês) antes de criar os objetos no PostgreSQL.

Criar sintaxe de tabela

Ao converter tabelas de Oracle para tipos de dados do PostgreSQL, a primeira etapa é extrair as instruções de tabela de criação da Oracle do banco de dados de origem. A consulta de amostra a seguir extrai a DDL da tabela de locais do esquema de RH:

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

A saída completa inclui elementos de armazenamento, índices e informações do tablespace, que foram omitidos porque esses outros elementos não são compatíveis com a instrução CREATE TABLE do PostgreSQL.

Depois de extrair o DDL, remova as aspas e execute a conversão da tabela de acordo com os tipos de dados da tabela de conversão do Oracle para PostgreSQL. Verifique cada tipo de dados da coluna para ver se eles podem ser convertidos como estão ou, se não forem compatíveis, escolha um tipo de dados diferente de acordo com a tabela de conversão. Veja a seguir o DDL convertido para a tabela de locais.

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)
)

Criar tabela como seleção (CTAS)

A instrução CREATE TABLE AS SELECT (CTAS) é usada para criar uma nova tabela com base em uma atual. Apenas nomes de coluna e tipos de dados de coluna são copiados, enquanto restrições e índices não são. O PostgreSQL é compatível com o padrão ANSI SQL para a funcionalidade CTAS e é compatível com a instrução Oracle CTAS.

Colunas invisíveis da Oracle 12c

O PostgreSQL não é compatível com colunas invisíveis. Como alternativa, crie uma visualização que contenha apenas as colunas visíveis.

Restrições da tabela

O Oracle fornece seis tipos de restrições de tabela que podem ser definidas na criação da tabela ou depois da criação da tabela usando o comando ALTER TABLE. Os tipos de restrições do Oracle são PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL e REF. Além disso, o Oracle permite que o usuário controle o estado de uma restrição por meio das seguintes opções:

  • INITIALLY IMMEDIATE: verifica a restrição no final de cada instrução SQL subsequente (o estado padrão).
  • DEFERRABLE/NOT DEFERRABLE: ativa o uso da cláusula SET CONSTRAINT em transações subsequentes até o envio de uma instrução COMMIT.
  • INITIALLY DEFERRED: verifica a restrição no final das transações subsequentes.
  • VALIDATE/NO VALIDATE: verifica (ou deliberadamente não verifica) linhas novas ou modificadas para erros. Esses parâmetros dependem de a restrição ser ENABLED ou DISABLED.
  • ENABLED/DISABLED: especifica se a restrição precisa ser aplicada após a criação (ENABLED por padrão).

O PostgreSQL também aceita seis tipos de restrições de tabela: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL e EXCLUDE. No entanto, existem algumas diferenças notáveis entre os tipos de restrição do Oracle e do PostgreSQL, incluindo:

  • O PostgreSQL não é compatível com a restrição REF da Oracle.
  • O PostgreSQL não cria automaticamente um índice nas colunas de referência para uma restrição de chave estrangeira. Uma instrução CREATE INDEX separada nas colunas de referência será necessária se for necessário um índice.
  • O PostgreSQL não é compatível com a cláusula ON DELETE SET NULL da Oracle. Esta cláusula instrui o Oracle a definir todos os valores dependentes em tabelas filhas como NULL quando o registro na tabela pai é excluído.
  • As restrições em VIEWS não são compatíveis, com exceção de CHECK OPTION.
  • O PostgreSQL não é compatível com a desativação de restrições. O PostgreSQL é compatível com a opção NOT VALID quando uma nova chave externa ou restrição de verificação é adicionada usando uma instrução ALTER TABLE. Essa opção manda o PostgreSQL pular as verificações de integridade referenciais nos registros atuais da tabela filha.

A tabela a seguir resume as principais diferenças entre os tipos de restrição do Oracle e do PostgreSQL:

Tipo de restrição da Oracle Suporte do Cloud SQL para PostgreSQL Equivalente do Cloud SQL para PostgreSQL
PRIMARY KEY Sim PRIMARY KEY
FOREIGN KEY Sim Usa a mesma sintaxe ANSI SQL que a Oracle.

Usa a cláusula ON DELETE para manipular casos de exclusões de registros pai FOREIGN KEY. O PostgreSQL fornece três opções para processar casos em que os dados são excluídos da tabela pai e uma tabela filha é referenciada por uma restrição FOREIGN KEY:

  • ON DELETE CASCADE
  • ON DELETE RESTRICT
  • ON DELETE NO ACTION

O PostgreSQL não é compatível com a cláusula ON DELETE SET NULL da Oracle.

Use a cláusula ON UPDATE para lidar com casos de atualizações de registros pai de FOREIGN KEY.
O PostgreSQL fornece três opções para lidar com eventos de atualização de restrição FOREIGN KEY:

  • ON UPDATE CASCADE
  • ON UPDATE RESTRICT
  • ON UPDATE NO ACTION

O PostgreSQL não cria automaticamente um índice nas colunas de referência para uma restrição de chave estrangeira.
UNIQUE Sim Cria um índice UNIQUE por padrão.
CHECK Sim CHECK
NOT NULL Sim NOT NULL
REF Não Incompatível.
DEFERRABLE/NOT DEFERRABLE Sim DEFERRABLE/NOT DEFERRABLE
INITIALLY IMMEDIATE Yes INITIALLY IMMEDIATE
INITIALLY DEFERRED Sim INITIALLY DEFERRED
VALIDATE/NO VALIDATE Não Incompatível.
ENABLE/DISABLE Não Ativado por padrão. Use a opção NOT VALID quando uma nova restrição de verificação ou chave estrangeira for adicionada à tabela usando uma instrução ALTER TABLE para pular as verificações de integridade referenciais nos registros existentes.
Restrição em VIEWs Não Não compatível, exceto a VIEW WITH CHECK OPTION.
Metadados de restrições Oracle DBA_CONSTRAINTS
PostgreSQL INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Colunas virtuais e geradas

As colunas virtuais da Oracle são baseadas nos resultados de cálculo de outras colunas. Elas aparecem como colunas regulares, mas os valores delas são derivados de um cálculo instantâneo pelo mecanismo de banco de dados Oracle e não armazenados no banco de dados. As colunas virtuais podem ser usadas com restrições, índices, particionamento de tabelas e chaves estrangeiras, mas não podem ser manipuladas por meio de operações de linguagem de manipulação de dados (DML).

As colunas geradas do PostgreSQL são comparáveis às colunas virtuais da Oracle em termos de funcionalidade. No entanto, diferentemente do Oracle, as colunas geradas no PostgreSQL são armazenadas e você precisa especificar um tipo de dados para cada coluna gerada, o que significa que ocupam um armazenamento como se fossem normais.

Exemplo de uma coluna virtual no 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

Exemplo equivalente no 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 tabelas

O Oracle e o PostgreSQL oferecem uma variedade de algoritmos de indexação e tipos de índices que podem ser usados para diversos aplicativos. Veja a seguir uma lista de algoritmos de indexação disponíveis no PostgreSQL:

Algoritmo de índice Descrição
B-tree
  • Tipo de índice padrão para PostgreSQL, usado para acelerar as consultas de igualdade e intervalo
  • Compatível com todos os tipos de dados primitivos e pode ser usado para recuperar valores NULL
  • Por padrão, os valores de índice são classificados em ordem crescente, mas também podem ser configurados em ordem decrescente.
Hash
  • Usado para acelerar pesquisas de igualdade
  • Mais eficiente que o índice B-tree, mas limitado apenas ao processamento de pesquisas de igualdade
GIN
  • Índices de árvore invertida
  • Mais eficiente que o índice B-tree ao lidar com colunas que contêm vários valores de componente, como matriz e texto
GiST
  • Não é um único tipo de índice, mas uma infraestrutura para definir índices compatíveis com mais operadores de comparação do que um índice B-tree normal aceitaria
  • Útil para dados geométricos ao otimizar as pesquisas de "vizinho mais próximo" necessárias
SP-GiST
  • Semelhante ao GiST, o SP-GiST é uma infraestrutura para estratégias de indexação definidas pelo usuário.
  • Permite uma ampla variedade de estruturas de dados não balanceadas, como quadtrees
  • Indisponível no Cloud SQL para PostgreSQL
BRIN
  • Índices de intervalo de blocos
  • Armazena resumos de intervalos de blocos físicos de uma tabela
  • Para colunas com uma ordem de classificação linear
  • Útil para pesquisa de intervalo em tabelas enormes

A tabela a seguir compara os tipos de índice entre o Oracle e o PostgreSQL:

Índice da Oracle Descrição Compatível com PostgreSQL Equivalente ao PostgreSQL
Índice de bitmap Armazena um bitmap para cada chave de índice, mais adequado para fornecer recuperação de dados rápida para cargas de trabalho OLAP. Não N/A
Índice B-tree O tipo de índice mais comum é adequado para diversas cargas de trabalho e pode ser configurado na classificação ASC|DESC. Sim Índice B-tree
Índice composto Criadas com duas ou mais colunas para melhorar o desempenho da recuperação de dados. A ordem das colunas dentro do índice determina o caminho de acesso. Sim Índices de várias colunas
É possível especificar até 32 colunas ao criar um índice de várias colunas.
Índice baseado em funções Armazena a saída de uma função aplicada nos valores de uma coluna da tabela. Sim Índices em expressões
Índice único Um índice B-tree que impõe uma restrição UNIQUE aos valores indexados por coluna. Sim Índice único
Índice de domínio do aplicativo Adequado para indexar dados não relacionais, como dados de áudio/vídeo, LOB e outros tipos não textuais. Não N/A
Índice invisível Recurso do Oracle que permite gerenciar, manter e testar índices sem afetar a tomada de decisão do otimizador. Não Como solução alternativa, é possível criar um índice adicional em uma réplica de leitura para fins de teste sem afetar a atividade em andamento.
Tabela organizada por índice Um tipo de índice que controla como os dados são armazenados nos níveis de tabela e índice. Não O PostgreSQL não é compatível com tabelas organizadas por índice. A instrução CLUSTER instrui o PostgreSQL a organizar o armazenamento de tabelas de acordo com um índice especificado. Ele tem uma finalidade semelhante à da tabela organizada pelo índice da Oracle. No entanto, o clustering é uma operação única, e o PostgreSQL não mantém a estrutura da tabela em atualizações posteriores. O clustering manual e periódico é necessário.
Índice local e global Usado para indexar tabelas particionadas em um banco de dados Oracle. Cada índice é definido como LOCAL ou GLOBAL. Não Os índices de trabalho das partições PostgreSQL têm a mesma funcionalidade que os índices locais do Oracle, ou seja, o índice é definido no nível da partição, o nível global não é aceito.
Índices parciais para tabelas particionadas (Oracle 12c) Cria um índice em um subconjunto das partições de uma tabela. É compatível comLOCAL e GLOBAL. Sim O particionamento no PostgreSQL funciona anexando tabelas filhas em uma tabela pai. É possível criar índices somente em um subconjunto de tabelas filhas.
CREATE/DROP INDEX Comando usado para criar e soltar o índice. Sim O PostgreSQL aceita o comando CREATE INDEX. Ele também é compatível com ALTER TABLE tableName ADD INDEX indexName columnName.
ALTER INDEX ... REBUILD Recompila o índice, o que pode causar um bloqueio exclusivo na tabela indexada. Requer sintaxe diferente O PostgreSQL é compatível com recriações de índices usando a instrução REINDEX. A tabela é bloqueada para gravações durante essa operação, e apenas as leituras são permitidas.
ALTER INDEX ... REBUILD ONLINE Recompila um índice sem criar um bloqueio exclusivo na tabela. Requer sintaxe diferente O PostgreSQL aceita recriações de índices simultâneas usando a instrução REINDEX TABLE CONCURRENTLY. Nesse modo, o PostgreSQL tenta recriar índices usando bloqueio mínimo, com a compensação de possivelmente levar mais tempo e recursos para concluir a recriação.
Compactação de índice Um recurso para reduzir o tamanho físico do índice. Não N/A
Alocar
índice para um tablespace
Cria um tablespace de índice que pode ser armazenado em um disco separado dos dados da tabela para reduzir os gargalos de E/S do disco. Não Embora o PostgreSQL permita que um índice seja criado em um tablespace definido pelo usuário, não é possível criar tablespaces no Cloud SQL para PostgreSQL, e o índice precisa ser criado no tablespace padrão.
Metadados de índice (tabelas/visualizações) Oracle DBA_INDEXES
DBA_PART_INDEXES
DBA_IND_COLUMNS
PostgreSQL pg_catalog.pg_index
pg_catalog.pg_attribute
pg_catalog.pg_class

Considerações sobre a conversão do índice

Na maioria dos casos, os índices da Oracle podem ser simplesmente convertidos em índices de árvore B do PostgreSQL, porque esse tipo de índice é o mais usado. Como em um banco de dados Oracle, um índice é criado automaticamente nos campos PRIMARY KEY de uma tabela. Da mesma forma, um índice UNIQUE é criado automaticamente em campos com uma restrição UNIQUE. Além disso, índices secundários são criados usando a instrução CREATE INDEX padrão.

O exemplo a seguir ilustra como uma tabela do Oracle com vários campos indexados pode ser convertida no 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=>

Particionamento de tabelas

O Oracle e o PostgreSQL oferecem recursos de particionamento para dividir tabelas grandes. Isso é feito com a segmentação física de uma tabela em partes menores, em que cada parte contém um subconjunto horizontal das linhas. A tabela particionada é chamada de tabela pai, e as linhas dela são armazenadas fisicamente nas partições. Embora nem todos os tipos de partição da Oracle sejam compatíveis com o PostgreSQL, o PostgreSQL aceita os mais comuns.

Nas seções a seguir, descrevemos os tipos de partição compatíveis com o PostgreSQL, ilustrando cada uma com um exemplo de como criar as partições que correspondem a esse tipo.

Particionamento por RANGE

Esse tipo de partição atribui linhas a partições com base em valores de coluna dentro de um determinado intervalo. Cada partição contém linhas para as quais o valor de expressão de particionamento está dentro de um determinado intervalo. É importante observar que os intervalos não se sobrepõem nas partições.

Exemplo

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);

Particionamento por LIST

Semelhante ao particionamento RANGE, o particionamento LIST atribui linhas a partições com base em valores de coluna dentro de um conjunto predefinido de valores. Os valores-chave que aparecem em cada partição são explicitamente listados para partições LIST.

Exemplo

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);

Particionamento por HASH

O particionamento HASH é mais adequado quando a meta é alcançar uma distribuição uniforme de dados entre todas as partições. Um valor de coluna (ou expressão com base em um valor de coluna a ser criptografado) e o valor da linha é atribuído à partição que corresponde a esse valor de hash. Os valores de hash precisam ser atribuídos exclusivamente a partições, e todos os valores inseridos precisam ser mapeados para exatamente uma partição.

Exemplo

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);

Particionamento em vários níveis

O particionamento de vários níveis é um método de criação de hierarquia em partições para uma única tabela. Cada partição é dividida em várias partições diferentes. O número de subpartições pode variar de uma partição para outra.

Exemplo

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);

Como anexar ou remover partições

No PostgreSQL, é possível adicionar ou remover partições da tabela pai. Uma partição desanexada posteriormente pode ser reanexada à mesma tabela. Além disso, novas condições de particionamento podem ser especificadas ao reanexar a partição, o que permite que os limites de partição sejam ajustados.

Exemplo

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;

A tabela a seguir descreve onde os tipos de partição Oracle e Cloud SQL para PostgreSQL são equivalentes e onde uma conversão é recomendada.

Tipo de partição da Oracle Compatível com PostgreSQL Implementação do PostgreSQL
RANGE partições Sim PARTITION BY RANGE
LIST partições Sim PARTITION BY LIST
HASH partições Sim PARTITION BY HASH
SUB-PARTITIONING Sim Particionamento em vários níveis
Partições de intervalo Não Incompatível
Consultor de partições Não Incompatível
Particionamento de preferências Não Incompatível
Particionamento baseado em coluna virtual Não Como solução alternativa, considere o particionamento com a expressão de coluna virtual diretamente:

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)));

Particionamento automático de listas Não Incompatível
Dividir
partições
Não Como solução alternativa, desanexe ou anexe partições de tabela para ajustar os limites de partição
Trocar partições Sim DETACH / ATTACH PARTITION
Particionamento de vários tipos (particionamento composto) Sim Particionamento em vários níveis
Metadados de partições Oracle DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
PostgreSQL pg_catalog.pg_class
pg_catalog.pg_partitioned_table

O exemplo a seguir é uma comparação lado a lado da criação de partições de tabela em ambas as plataformas. O PostgreSQL não é compatível com a referência a um tablespace na cláusula PARTITIONS do comando CREATE TABLE.

Implementação do 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
);

Implementação do 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);

Tabelas temporárias

Em um banco de dados Oracle, as tabelas temporárias são chamadas de GLOBAL TEMPORARY TABLES. No PostgreSQL, elas são conhecidas apenas como tabelas temporárias. A funcionalidade básica de uma tabela temporária é idêntica nas duas plataformas. No entanto, há algumas diferenças notáveis:

  • O Oracle armazena a estrutura temporária da tabela para uso repetido mesmo após uma reinicialização do banco de dados, enquanto o PostgreSQL armazena a tabela temporária somente durante a sessão.
  • Uma tabela temporária em um banco de dados Oracle pode ser acessada por diferentes usuários com as permissões apropriadas. Por outro lado, uma tabela temporária no PostgreSQL só pode ser acessada durante a sessão em que ela foi criada, a menos que a tabela temporária seja referenciada com nomes qualificados por esquema.
  • Em um banco de dados Oracle, há uma distinção entre tabelas temporárias GLOBAL e LOCAL que especificam se o conteúdo da tabela é global ou específico da sessão. No PostgreSQL, as palavras-chave GLOBAL e LOCAL são compatíveis por motivos de compatibilidade, mas não afetam a visibilidade dos dados.
  • Se a cláusula ON COMMIT for omitida na criação de uma tabela temporária, o comportamento padrão no banco de dados Oracle será ON COMMIT DELETE ROWS, o que significa que o Oracle trunca a tabela temporária após cada confirmação. Por outro lado, no PostgreSQL, o comportamento padrão é preservar linhas na tabela temporária após cada confirmação.

A tabela a seguir destaca as diferenças em tabelas temporárias entre o Oracle e o Cloud SQL para PostgreSQL.

Recurso de tabela temporária Implementação do Oracle Implementação do PostgreSQL
Sintaxe CREATE GLOBAL TEMPORARY TABLE CREATE TEMPORARY TABLE
Acessibilidade Acessível em várias sessões Acessível a partir da sessão do criador, a menos que seja referenciado com nomes qualificados por esquema
Compatibilidade de índice Sim Sim
Compatibilidade com chave estrangeira Sim Sim
Preservar DDL Sim Não
Ação padrão em ON COMMIT Registros excluídos Registros preservados
ON COMMIT PRESERVE ROWS Sim Yes
ON COMMIT DELETE ROWS Yes Sim
ON COMMIT DROP Não Sim
Suporte a ALTER TABLE Sim Sim
Como coletar estatísticas DBMS_STATS.GATHER_TABLE_STATS ANALYZE
Oracle 12c GLOBAL_TEMP_

TABLE_STATS
DBMS_STATS.SET_TABLE_PREFS ANALYZE

Colunas não utilizadas

O recurso da Oracle de marcar colunas específicas como UNUSED é frequentemente usado para remover colunas de tabelas sem remover fisicamente os dados de colunas. Isso evita os possíveis carregamentos altos que ocorrem ao descartar colunas de tabelas grandes.

No PostgreSQL, descartar uma coluna grande não remove os dados da coluna do armazenamento físico e, portanto, é uma operação rápida mesmo em tabelas grandes. Não é necessário marcar uma coluna como UNUSED como em um banco de dados Oracle. O espaço ocupado pela coluna descartada é recuperado por novas instruções DML ou durante uma operação de VACUUM subsequente.

Tabelas somente leitura

As tabelas somente leitura são um recurso do Oracle que marca tabelas como somente leitura usando o comando ALTER TABLE. No Oracle 12c R2, esse atributo também está disponível para tabelas com partições e subpartições. O PostgreSQL não oferece um recurso equivalente, mas há duas soluções alternativas possíveis:

  • Conceda a permissão SELECT em tabelas para usuários específicos. Isso não impede que o proprietário da tabela execute operações DML nas tabelas.
  • Crie uma réplica de leitura do Cloud SQL para PostgreSQL e direcione os usuários para as tabelas de réplica que são tabelas somente leitura. Essa solução requer a adição de uma instância de réplica de leitura a uma instância existente do Cloud SQL para PostgreSQL.
  • Crie um gatilho de banco de dados que gere exceções em instruções DML. Por exemplo:

    -- 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=>
    

Conjuntos de caracteres

O Oracle e o PostgreSQL são compatíveis com uma ampla variedade de conjuntos de caracteres, compilações e unicode, incluindo compatibilidade com linguagens de um único byte e de vários bytes. Além disso, os bancos de dados PostgreSQL que residem na mesma instância podem ser configurados com conjuntos de caracteres distintos. Consulte a lista de conjuntos de caracteres compatíveis com o PostgreSQL.

No Oracle Database, os conjuntos de caracteres são especificados no nível do banco de dados (Oracle 12g R1 ou anterior) ou no nível do banco de dados plugável (Oracle 12g R2 ou posterior). No PostgreSQL, um conjunto de caracteres padrão é especificado quando uma nova instância do Cloud SQL para PostgreSQL é criada. Cada banco de dados criado nessa instância pode ser criado com um conjunto de caracteres diferente. A ordem de classificação e a classificação de caracteres podem ser especificadas por coluna da tabela.

Exemplo

-- 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(> );

Visualizações

O PostgreSQL aceita visualizações simples e complexas. Para opções de criação de visualizações, há algumas diferenças entre o Oracle e o PostgreSQL. A tabela a seguir destaca essas diferenças.

Recurso de visualização Oracle Descrição Suporte do Cloud SQL para PostgreSQL Considerações sobre conversão
FORCE Crie uma visualização sem verificar se as tabelas ou visualizações de origem existem. Não Não há opções equivalentes disponíveis.
CREATE OR REPLACE Cria uma visualização inexistente ou substitui uma visualização existente. Sim O PostgreSQL é compatível com o comando CREATE OR REPLACE para visualizações.
WITH CHECK OPTION Especifica o nível de aplicação ao executar operações DML na visualização. Sim O padrão é CASCADED, o que faz com que as visualizações referenciadas também sejam avaliadas.

A palavra-chave LOCAL faz com que apenas a visualização atual seja avaliada.
WITH READ-ONLY Permite apenas operações de leitura na visualização. As operações DML são proibidas. Não Uma solução alternativa é conceder privilégios SELECT à visualização para todos os usuários.
VISIBLE | INVISIBLE (Oracle 12c) Especifique se uma coluna com base na visualização é visível ou invisível para o usuário. Não Crie o VIEW somente com as colunas necessárias.

No exemplo de conversão a seguir, veja a conversão do Oracle para o Cloud SQL PostgreSQL para visualizações.

-- 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 gerenciamento de acesso

Os proprietários de uma visualização precisam ter privilégios nas tabelas base para criar a visualização. O usuário de uma visualização precisa das permissões SELECT adequadas na visualização. Elas também precisam das permissões INSERT, UPDATE e DELETE apropriadas na visualização ao executar operações DML por meio da visualização. Em ambos os casos, os usuários não precisam de permissões nas tabelas subjacentes.

A seguir