Como migrar do Oracle para o PostgreSQL

Este whitepaper aborda a experiência de migração de banco de dados de Oracle para PostgreSQL feita por Conversores Inteligentes para uma grande empresa de entrega de cargas. O principal motivo da migração foi a redução do custo do sistema de gerenciamento de banco de dados e a transferência para um sistema de código aberto com escalabilidade, segurança e recursos de personalização suficientes.

Aqui está uma breve especificação do banco de dados de origem:

• Servidor de banco de dados Oracle 12g
• O banco de dados contém 190 tabelas, 50 GB de dados
• 120.000 linhas de PL/SQL em procedimentos armazenados, funções e gatilhos

  
  

A duração do projeto foi de 2 meses: meio mês de avaliação e planejamento, um mês para migração e meio mês para testes. Foram 3 especialistas envolvidos na migração: 2 desenvolvedores e 1 administrador de banco de dados.

Esta migração de banco de dados Oracle para PostgreSQL inclui seis fases básicas:

1. Encontre todos os métodos específicos da Oracle de armazenamento e processamento de dados no banco de dados de origem e o escopo de uso (fase de investigação e planejamento)
2. Selecione as ferramentas apropriadas para migração de esquema e implemente-as
3. Escolha o método mais adequado de migração de dados para diminuir o tempo de inatividade do sistema Oracle
4. Execute a migração de dados lidando com todas as transformações exigidas pelo PostgreSQL DBMS
5. Converter todo o código PL/SQL no formato PostgreSQL (usando ferramentas para autômato parcial e pós-processamento manual)
6. Execute testes funcionais e de desempenho, ajuste fino do banco de dados resultante

Migração de definições de tabela

Existem alguns tipos de dados no Oracle sem equivalente direto no PostgreSQL. Um desses tipos é DATE contendo partes de data e hora.

O PostgreSQL suporta:

• data – data pura sem parte de tempo
• hora – hora pura sem parte de data com especificação opcional de fuso horário
• timestamp – data e hora com especificação opcional de fuso horário

Existem duas opções de mapeamento de datas do Oracle no PostgreSQL: use TIMESTAMP ou configure a extensão para usar o tipo de data no estilo Oracle oracle.date.

Os tipos espaciais também requerem atenção especial. O Oracle possui o tipo integrado SDO_GEOMETRY, enquanto o PostgreSQL precisa da instalação do PostGIS para trabalhar com tipos de dados espaciais.

A tabela abaixo ilustra o mapeamento de tipo seguro Oracle para PostgreSQL:

Oráculo	PostgreSQLName
BINARY_FLOAT	REAL
BINARY_INTEGER	INTEIRO
BINARY_DOUBLE	DUPLA PRECISÃO
BLOB, RAW(n), LONG RAW	BYTEA (limite de 1 GB)
CLOB, LONGO	TEXTO (limite de 1 GB)
DATA	TIMESTAMP
NÚMERO, NÚMERO(*)	DOUBLE PRECISION ou BIGINT se fizer parte da chave primária
NÚMERO(n,0), NÚMERO(n)	n<5 – SMALLINT5<=n<9 – INT9<=n<19 – BIGINTn>=19 – DECIMAL(n)
NÚMERO(p,s)	DECIMAL(p,s)
REAL	DUPLA PRECISÃO

Para tipos numéricos, é importante entender o escopo de uso no banco de dados. Se for focado em precisão, os tipos numéricos do Oracle devem ser mapeados no PostgreSQL NUMERIC. Se a prioridade máxima for a velocidade de cálculo, o melhor mapeamento seria REAL ou DUPLA PRECISÃO.

Usamos para automatizar a migração de definições de tabela, índices e restrições para este projeto. Ele mapeia os tipos Oracle nos equivalentes PostgreSQL mais apropriados e permite personalizar o mapeamento de tipo específico.

Migração de dados

Como a migração de dados pode consumir muito tempo para grandes bancos de dados, é extremamente importante escolher a estratégia e as ferramentas certas para esta etapa.

Existem três abordagens comuns para a migração de dados:

• instantâneo – migre todos os dados em uma etapa
• instantâneo por partes – migrar dados por blocos em threads ou processos paralelos
• replicação de dados alterados - carregamento contínuo de dados rastreando alterações incrementais

O método de instantâneo requer tempo de inatividade essencial do banco de dados de origem durante todo o período de leitura de dados para evitar perda ou corrupção de dados durante a migração. O tempo de inatividade para a abordagem de instantâneo por partes é muito menor, mas ainda é necessário. Neste projeto de migração, usamos o método instantâneo por partes para migração de grandes tabelas contendo milhões de linhas.

Então, e quanto à técnica de replicação de dados alterados (CDR)?

Tem duas implementações principais e cada uma delas tem sua fraqueza. O primeiro é conhecido como CDR baseado em gatilho. Requer a criação de gatilhos na inserção, atualização e exclusão de cada tabela que está sendo replicada no banco de dados de origem. Esses gatilhos rastreiam todas as alterações armazenando informações sobre eventos alterados em uma tabela especial de 'histórico'. Com base nesses dados, a ferramenta CDR replica todas as alterações no banco de dados de destino. O CDR baseado em gatilho causa sobrecarga extra do banco de dados de origem devido à gravação na tabela de 'histórico' para cada atualização de dados.

O segundo método é chamado CDR de Log de Transações. Ele usa logs de transação criados pelo Oracle DBMS para rastrear e replicar alterações no banco de dados PostgreSQL de destino. A vantagem dessa abordagem em comparação com o CDR baseado em gatilho é que ela não modifica o banco de dados de origem.

No entanto, o Transaction Log CDR também possui alguns pontos fracos:

• O Oracle arquiva constantemente os arquivos de log de transações, portanto, a ferramenta CDC pode perder algumas alterações se não ler o log de transações antes de ser arquivado.
• Em caso de perda de conexão com o banco de dados de destino durante a replicação das alterações do log de transações (ou qualquer outro tipo de erro), os dados podem ser perdidos ou danificados devido à falta de controle sobre o log de transações.

Os aspectos mais desafiadores da migração de dados do Oracle para o PostgreSQL são: formatos de dados específicos do DBMS de origem sem equivalente direto no destino e nos dados externos.

Na seção anterior deste artigo, BYTEA é indicado como o tipo de dados PostgreSQL mais adequado para dados binários. No entanto, ao migrar grandes dados binários (o tamanho médio do campo não é inferior a 10 MB), não é recomendado usar BYTEA. O motivo é a particularidade da leitura de dados BYTEA – só pode ser extraído em um fragmento; a leitura por partes não é possível. Isso pode causar sobrecarga de RAM essencial. O tipo LARGE OBJECT do PostgreSQL pode ser usado como solução alternativa para esse problema. Todos os valores do tipo LARGE OBJECT são armazenados na tabela do sistema 'pg_largeobject' que faz parte de cada banco de dados. Pode haver até 4 bilhões de linhas na tabela 'pg_largeobject'. O tamanho máximo do LARGE OBJECT é de 4 TB e a leitura por partes está disponível.

Além disso, é importante migrar corretamente o ROWID, que é uma pseudocoluna que identifica o endereço físico do registro na tabela. PostgreSQL tem um campo de serviço semelhante chamado ctid, porém é um equivalente direto de ROWID. diz que o ctid pode ser alterado devido ao procedimento de limpeza.

Existem três métodos básicos de emulação da funcionalidade ROWID no PostgreSQL:

• Use a chave primária existente (ou crie uma nova) para identificar linhas em vez de ROWID
• Adicione uma coluna serial ou bigserial com valores gerados automaticamente e torne-a uma chave primária/exclusiva para substituir a funcionalidade ROWID
• Quando não for possível definir uma única chave primária de coluna, use um índice exclusivo construído sobre várias colunas (para essa finalidade, determine um conjunto mínimo de campos exclusivos para cada linha)

A Oracle pode vincular dados externos armazenados fora do banco de dados. No entanto, ela pode ser processada como uma tabela regular usando o recurso 'tabela externa' do DBMS. O PostgreSQL usa a biblioteca Foreign Data Wrapper para o mesmo propósito. Por exemplo, ele fornece a extensão 'file_fdw' para trabalhar com arquivos CSV externos como uma tabela regular.

Desafios da migração PL/SQL

Esta seção explora problemas que foram resolvidos durante a migração de pacotes Oracle, armazena procedimentos, funções e gatilhos para PostgreSQL.

Pacotes. O PostgreSQL não possui pacotes. Esse recurso ausente pode ser emulado agrupando todas as entradas pertencentes a um pacote dentro do esquema PostgreSQL com o mesmo nome, as variáveis globais podem ser armazenadas em uma tabela de serviço.

Cordas vazias. Ao contrário do Oracle, no PostgreSQL strings vazias não são iguais a NULL. Por exemplo, no Oracle:

 SQL> select NVL('','This is null') as null_test from dual; NULL_TEST ------------ This is null

O mesmo no PostgreSQL:

 testdb=# select coalesce('','This is null') as null_test; null_test ----------- (1 row)

Além disso, há um comportamento diferente de concatenação de strings com NULL no Oracle e no PostgreSQL. No Oráculo:

 SQL> Select 'String' || NULL as cs from dual; CS -- String

No PostgreSQL:

 synctest=# Select 'String' || NULL as concat_str; concat_str ------------ (1 row)

Fundição de tipos. O PostgreSQL requer conversão de tipos estritos ao chamar funções, operadores ou quando instruções INSERT e UPDATE colocam os resultados de expressões em uma tabela.

A solução alternativa é usar o pseudotipo 'anyelement':

 create or replace function my_concat(str1 anyelement, str2 anyelement) returns varchar language plpgsql as $$ begin return str1::varchar || str2::varchar; end; $$;

Observação: você pode usar um argumento anyelement no procedimento/função armazenado ou todos os parâmetros do mesmo tipo anyelement.

Sequências. A sintaxe da declaração de sequência é semelhante para Oracle e PostgreSQL. Por exemplo:

 CREATE SEQUENCE SEQ1 START WITH 10 INCREMENT BY 2 MAXVALUE 1000000 CACHE 20 NO CYCLE;

No entanto, referenciar o próximo valor da sequência é diferente. No Oracle é 'sequence.nextval' e no PostgreSQL nextval('sequence').

Gatilhos. No Oracle, o código-fonte do corpo do gatilho está incluído na instrução CREATE TRIGGER. O PostgreSQL requer que o código-fonte do gatilho seja composto como uma função autônoma com a referência da instrução CREATE TRIGGER:

 CREATE OR REPLACE FUNCTION store_changes() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF (NEW.first_name <> OLD.first_name) OR (NEW.last_name <> OLD.last_name) OR (NEW.email <> OLD.email) THEN INSERT INTO changes_log(id,changed_on) VALUES(OLD.id,now()); END IF; RETURN NEW; END; $$ CREATE TRIGGER make_changes BEFORE UPDATE ON employees FOR EACH ROW EXECUTE PROCEDURE store_changes();

Transações Autônomas. As transações autônomas no Oracle permitem que um subprograma confirme ou reverta operações SQL sem confirmar ou reverter a transação principal. Por exemplo, alguns dados são inseridos em uma tabela como parte do disparador de inserção.

Esta operação de inserção deve ser bem-sucedida mesmo se a transação principal do disparador de inserção falhar. Neste caso, a instrução INSERT correspondente deve ser incluída na transação autônoma:

 CREATE OR REPLACE PROCEDURE insert_critical_data(v_data varchar2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO info_table (data) VALUES (v_data); commit; END;

Transações autônomas não são suportadas diretamente no PostgreSQL. Uma solução possível é refatorar o código, para que não precise de transações autônomas.

Caso contrário, você pode usar o módulo 'dblink' do PostgreSQL. Com o dblink, uma nova conexão é estabelecida e uma consulta é executada usando essa conexão e é imediatamente confirmada, independentemente da transação principal. Por exemplo, a função a seguir insere uma linha em uma tabela e a operação de inserção será confirmada mesmo se a transação de chamada for revertida:

 CREATE OR REPLACE FUNCTION insert_critical_data(v_data TEXT) RETURNS void AS $BODY$ DECLARE v_sql text; BEGIN PERFORM dblink_connect('myconn', 'dbname=mydbname user=… password=… host=… port=…'); v_sql := format('INSERT INTO info_table (data) VALUES (%L)', v_data); PERFORM dblink_exec('myconn', v_sql); PERFORM dblink_disconnect('myconn'); END; $BODY$ LANGUAGE plpgsql;

Funções incorporadas. O Oracle e o PostgreSQL fornecem conjuntos de funções integradas semelhantes, mas não iguais. A tabela abaixo inclui funções do Oracle que requerem portabilidade para equivalentes do PostgreSQL:

Oráculo	PostgreSQLName
ADD_MONTH($data,$n_mês)	$data + $n_mês * intervalo '1 mês'
DECODE($exp, $quando, $então, ...)	CASE $exp WHEN $quando ENTÃO $então ... END
INSTR($str1, $str2)	*POSIÇÃO($str2 em $str1)
ROWNUM	**número_linha() sobre ()
SYSDATE	DATA ATUAL
SYS_GUID()	uuid_generate_v1()

*A portabilidade complexa da função Oracle INSTR para PostgreSQL pode ser encontrada em:

A condição do Oracle 'where rownum < N' deve ser convertida em 'limit N' no PostgreSQL Usamos o Oracle-to-PostgreSQL Code Converter para automatizar parcialmente a conversão de gatilhos, procedimentos armazenados e funções. Ele oferece suporte à conversão de funções integradas do Oracle em equivalentes do PostgreSQL e processa a maioria dos padrões de sintaxe de PL/SQL. No entanto, o código-fonte complexo pode exigir pós-processamento manual da saída.

Módulos Úteis para Migração de Oracle para PostgreSQL

Esta seção contém uma breve descrição dos módulos do PostgreSQL que podem ajudar a migrar do Oracle para o PostgreSQL e testar os resultados da migração.

Um dos módulos PostgreSQL mais importantes ao migrar do Oracle é . Ele emula tipos, funções e operadores específicos do sistema de gerenciamento de banco de dados Oracle.

As extensões 'pgTAP' e 'pg_prove' podem ser usadas para testes funcionais de funções do PostgreSQL. Depois de instalar o 'pgTAP', ele cria muitas funções armazenadas usadas para escrever testes. A função de teste deve ser declarada como 'retorna conjunto de texto'. Em seguida, ele pode ser executado a partir do cliente do console psql:

 psql -h $db_host -p $db_port -U $db_user $db_name -f tests/name_of_test.sql

Informações detalhadas sobre o pgTAP podem ser encontradas aqui:

A extensão PostgreSQL ajuda a validar o código PL/pgSQL. Por exemplo, se houver declaração de variável ausente ou nome de coluna incorreto dentro da função PostgreSQL, isso definitivamente falhará durante a execução. Use os recursos de 'plpgsql_check' para diagnosticar esses problemas antes de ir para a produção:

 select * from plpgsql_check_function_tb('{name of function}');

O módulo ajuda a analisar o desempenho do código PL/pgSQL em funções e procedimentos armazenados.

A extensão PostgreSQL fornece conexão com bancos de dados Oracle por meio do recurso Foreign Data Wrapper do DBMS. Por exemplo, se o banco de dados Oracle 'OCRL' estiver sendo executado no servidor 'server.mydomain.com', o oracle_fdw deverá ser configurado da seguinte forma:

 CREATE EXTENSION oracle_fdw; CREATE SERVER oraserver FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//server.mydomain.com:1521/ORCL'); GRANT USAGE ON FOREIGN SERVER oraserver TO pguser; CREATE USER MAPPING FOR pguser SERVER oraserver OPTIONS (user 'oracle user', password 'oracle password'); CREATE FOREIGN TABLE oratable ( { column definitions } ) SERVER oraserver OPTIONS (schema 'ORAUSER', table 'ORATAB');

Então, você pode usar ' oratable ' como uma tabela PostgreSQL regular.