Conectando Oracle e MySQL ao PostgreSQL com FDW
O FDW foi construído baseando-se no SQL/MED (SQL Management of External Data), que trata-se de uma regulamentação de um padrão de configuração, desenvolvimento e aplicação de acessos a dados externos em SGBDs e arquivos de outros fornecedores. O conceito do FDW não é apenas tornar dados visíveis de várias origens em um único ponto, mas também integrar o uso destes dados, como por exemplo, utilizando joins, ou filtros de tabela externas como parâmetro para retornar dados de outra tabela externa de outra fonte, e permitindo acima de tudo, que seja feita todas as operações DML (Data Manipulation Language) no acesso a dados externos, como select, insert, update e delete. Esta facilidade de manipulação de dados é um grande diferencial do FDW se comparado com ferramentas que tem o mesmo propósito de integração de dados, geralmente sem real-time ou permissão de alteração de objetos (acesso read only somente).
A demonstração da figura 1 mostra alguns dos objetos externos que podem se conectar ao PostgreSQL utilizando a funcionalidade de FDW, para que seja possível trabalhar com as informações destas fontes externas.O PostgreSQL disponibiliza o acesso aos dados utilizando-se do conceito de tabelas externas (foreign tables), o que torna transparente o acesso aos dados externos, e é utilizado os mesmos procedimentos de acesso a tabelas locais.
Criando extensões no PostgreSQL
A funcionalidade de FDW é disponibilizada no PostgreSQL através de extensões, que segue a idéia basicamente de arquivos que podem ser compilados dentro do PostgreSQL com determinadas funcionalidades. As extensões de FDW adicionadas ao banco de dados, permitem realizar a integração de um objeto externo (SGBD ou arquivo) ao PostgreSQL. Após a configuração do FDW, é realizado o mapeamento das tabelas do banco de dados externo com a criação de foreign tables, ou então com a criação de um schema estrangeiro.
As configurações de extensões variam de acordo com o fornecedor. Para extensões de MySQL, MongoDB, SQL Server, Web Services, a configuração é realizada através de instalação de pacotes no sistema operacional. Para outros tipos de extensões, como por exemplo do banco de dados Oracle, que será utilizado neste artigo, a extensão de FDW é configurada com um projeto no GitHub, chamado oracle_fdw.
As versões do PostgreSQL e das extensões FDW dos objetos externos funcionam da seguinte forma: O banco de dados PostgreSQL lança novas versões, releases e correções, e então os desenvolvedores que disponibilizam os FDWs dos objetos externos implementam as novas melhorias.
Extensões oracle_fdw e mysql_fdw
O oracle_fdw, é uma extensão de acesso ao Oracle, mantida no GitHub em um projeto de Laurenz Albe, e ao longo de suas versões, houveram diversas melhorias , por exemplo na versão 9.2, disponibilizado a função analyze do Oracle, na 9.3, a possibilidade de realizar inserts, updates e deletes, e na versão 9.4, foi integrado o suporte a triggers. O oracle_fdw só é compatível com versões do Oracle Database Client 10 em diante.
É importante lembrar que o GitHub é um repositório de projetos de desenvolvedores que os tornam públicos ou não, no caso do oracle_fdw, o código é aberto e é possível verificar como foi realizado o desenvolvimento, que está em constante atualizações, e que é de certa forma confiável, mas é necessário atenção antes de baixar qualquer projeto de FDW ou outro qualquer do GitHub, no caso do FDW, dar preferência aos projetos conhecidos na comunidade PostgreSQL, e que disponibilizam de forma clara o código utilizado.
Com o MySQL, o processo é um pouco mais simples, o mysql_fdw está disponível para ser instalado por meio do repositório oficial do PostgreSQL, com isso, após adicionar o repositório de pacotes do PostgreSQL no CentOS, por exemplo, basta executar a instalação do pacote mysql_fdw, e então, no banco de dados local, deve ser realizado as configurações de server, user mapping, e outras mais que será mostrado no decorrer deste artigo.
[CHECKPOINT]
Configurando o mysql_fdw
A instalação da extensão de FDW para o MySQL é realizada com os pacotes oficiais do PostgreSQL, de acordo com a versão do banco de dados instalada, para a versão 9.5 que será utilizada nos exemplos a seguir, o repositório oficial pgdg-centos95–9.5–3.noarch.rpm contém o mysql_fdw_95. Antes de realizar a instalação do mysql_fdw_95, é necessário que no servidor em questão tenha instalado o cliente de acesso ao banco de dados MySQL, isto porque, o PostgreSQL se comunica com o mysql_fdw, que por sua vez se comunica com o cliente do MySQL, que se conecta ao ao banco de dados MySQL e retorna as informações necessárias. O cliente MySQL instalado para este artigo foi o mysql-community-client, disponível no repositório mysql57-community-release-el6–9.noarch, repositório oficial do MySQL.
Abaixo no box 1 é possível verificar o modo de configuração do repositório, em seguida a instalação do cliente de MySQL 5.7, que por sua vez instala as dependências necessárias para o cliente de MySQL funcionar corretamente. Após estes passos, a configuração no servidor está pronta para o FDW, porém, ainda restará a configuração dos objetos dentro do banco de dados PostgreSQL, que será demonstrada mais adiante.
Com a instalação do cliente MySQL realizada, o próximo passo é a instalação do pacote mysql_fdw_95 no sistema operacional, e em seguida a instalação do mysql_fdw no PostgreSQL, como é ilustrado no box 2. Caso a instalação seja concluída corretamente, significa que as bibliotecas do cliente MySQL estão sendo acessadas pelo PostgreSQL através do mysql_fdw.
Como observação, pode se notar no box 2 que houveram dependências do mysql_fdw instaladas, os pacotes postgresql95-server e postgresql95. Isto ocorreu porque não havia ainda no servidor em questão o banco de dados PostgreSQL na versão 9.5 instalado, mas se houvesse, o mysql_fdw não instalaria dependências. A instalação do fdw foi realizada desta forma apenas para mostrar uma forma rápida de instalação do banco de dados e extensão na mesma tarefa.
No MySQL em que será feita a conexão via FDW, conforme exibido no Box 3, há um banco de dados chamado mysql_database, o qual possui uma tabela chamada mysql_table, que será utilizada nos próximos exemplos. Neste MySQL também foi criado um usuário chamado mysql_usuario, com a senha mysql_senha. Utilizando o cliente do PostgreSQL, será necessário criar um server, que indica ao PostgreSQL onde se conectar para obter os dados da tabela estrangeira, e para esse passo é informado como parâmetro o nome do server, o wrapper que será utilizado, neste caso, o mysql_fdw, o host em que se hospeda o banco de dados estrangeiro e a porta de conexão. Esta configuração é um pré-requisito para a configuração a seguir do user mapping, que é necessário informar o login e senha de conexão ao banco estrangeiro que tem acesso aos objetos que serão acessados. Com estas configurações de server e user mapping, todos os dados de acesso ao banco estrangeiro foram informados, servidor e porta, login e senha de conexão. Exemplo desta configuração estão no box 4.
Os objetos do banco de dados externo podem ser configurados no PostgreSQL com a criação de uma tabela estrangeira, de acordo com o que foi feito a seguir no box 5 (create foreign table) ou com a importação de esquema (import foreign schema), onde mais de uma tabela pode ser importada. É interessante entender o conceito de importação de esquema estrangeiro, onde a sintaxe import foreign schema não significa que um esquema é criado no destino, e sim que todos os objetos pertencentes ao server mencionado na sintaxe serão importados como foreign table um a um, portanto, caso seja necessário apagar estes objetos, não existe uma sintaxe drop foreign schema, mas sim os objetos devem ser removidos um a um como foreign table.
Configurando o oracle_fdw
Seguindo com a configuração de FDW, desta vez para o Oracle Database, é necessário o oracle_fdw e um cliente de banco de dados. Para o banco de dados Oracle não existe extensão oficial do PostgreSQL, portanto, será instalado o oracle_fdw, conforme foi apresentado neste artigo, um projeto existente no GitHub, mas com bastante aceitação e utilização. O GitHub pode ser “clonado” para um diretório a partir do endereço do projeto no GitHub ou o projeto pode ser baixado e transferido para o servidor manualmente. Com o GitHub já configurado no servidor, a transferência do projeto do GitHub foi transferido utilizando o comando git clone e em seguida os passos de instalação do fdw conforme o box 6 abaixo:
Antes da instalação do oracle_fdw, é necessário a instalação de um pacote chamado postgresql95-devel, que existe no repositório oficial do PostgreSQL, como este repositório já foi adicionado ao servidor na instalação do FDW do MysQL, para instalar este pacote é necessário executar a sintaxe apropriada para o sistema operacional que está sendo configurado. No caso do CentOS, a sintaxe é yum install:
Caso não seja instalado este pacote, ocorrerá um erro na instalação do oracle_fdw. Outro item necessário é que o servidor possua ou o banco de dados Oracle instalado, com uma versão a partir da 10g, para que seja utilizado as bibliotecas de acesso ao banco de dados, ou então, pode ser baixado através do site oficial da Oracle, o cliente de acesso ao banco de dados. Para este artigo, está sendo utilizado um servidor que já contém o Oracle Database instalado, mas mesmo assim, o cliente de Oracle foi baixado e durante a exportação de variáveis LD_LIBRARY_PATH e PATH, este caminho do cliente foi informado. Com estes passos finalizados, a instalação ocorrerá normalmente.
Após os procedimentos realizados, a configuração do FDW está pronta no servidor, e é necessário criar a extensão dentro do PostgreSQL, e todo o “mapeamento” dentro do banco de dados para se conectar ao Oracle Database. É importante entender neste ponto o funcionamento das bibliotecas do PostgreSQL e do Oracle Database, é comum que ocorram erros no que se refere a libs não encontradas, e na maioria dos casos se resolve com o export de PATH, LD_LIBRARY_PATH e o ORACLE_HOME.
A criação de server e o user mapping tem pequenas modificações se comparado ao MySQL, mas a ideia principal continua, criar um “link” de conexão com o banco de dados externo com as informações de conexão de servidor e usuário do objeto externo, Primeiramente, foi criado uma tabela chamada oracle_table com duas linhas para teste no PostgreSQL. Também existe um usuário oracle_usuario que já possui os grants necessários de acesso no banco de dados Oracle. No PostgreSQL, a configuração de server, user mapping e foreign table é realizado seguindo a mesma idéia que foi aplicada ao MySQL, com exceção de alguns parâmetros, como a criação do server, que difere no que diz respeito ao nome do parâmetro que indica o servidor, no MySQL era host, no Oracle chama-se dbserver. Para o oracle_fdw, também é necessário informar um usuário do PostgreSQL na criação do user mapping, para o MySQL este acesso pode ser público, o que também é possível para o Oracle, sobre tornar público ao banco de dados o acesso ao Oracle, mas tem que ser explicitamente indicado essa permissão realizando um grant aos usuários ou perfis que terão acesso.
Utilizando o FDW
O modelo de dados abaixo da figura 2 contém três tabelas, que foram criadas em banco de dados separados, simulando uma situação em que a interação entre as tabelas são necessárias com execução de select, insert, update e delete, e utilizando join entre as tabelas.
Os objetos contidos neste modelo de dados são a tabela Client, presente em um banco de dados Oracle, tabela Project está em um banco de dados MySQL, e por último a tabela Salesman PostgreSQL presente no banco de dados onde está configurado o FDW. A tabela 1 deste artigo contém os códigos que foram utilizados para a criação das tabelas em seus respectivos bancos de dados.
O primeiro teste no box 11 foi realizar join entre as tabelas, utilizando a tabela Project para fazer o join com as tabelas Client e Salesman. Primeiramente, foram listadas as 3 tabelas, para visualização do conteúdo, em seguida, um join mostrando o campo Name de cada tabela e o id do Projeto, desta forma é possível visualizar como a execução da query é transparente para o banco de dados, se os resultados são provenientes de tabela estrangeira ou não.
Por último, foi realizada uma tentativa de update da tabela Client, mas ocorre um erro informando que nesta tabela, não há chave primária, e permite visualizar a necessidade de chaves para a execução de alteração de dados na tabela estrangeira, por esse motivo, a tabela estrangeira foi removida (no PostgreSQL) e com a criação de uma nova tabela estrangeira com o parâmetro OPTIONS (key ‘true’) no campo ID, o que significa que o campo em questão agora é considerado uma chave, e desta forma, o update foi executado corretamente. Um ponto a ser lembrado é que o cliente PostgreSQL tem auto commit implícito, é muito importante ter certeza do que se está executando.
Assim como foi visualizado no box 11 que para a execução de um update é necessário uma chave, para executar um delete, também há necessidade de se ter uma chave primária, neste caso, a alteração ocorreu na tabela original no banco de dados MySQL na tabela Project. Após esta modificação da tabela na sua origem, a exclusão foi realizada com sucesso. O mesmo procedimento também vale para o insert. Essas regras são importantes para que não haja perda de performance na execução deste tipo de alteração entre banco de dados de origem e banco de dados PostgreSQL com FDW.
Para finalizar os exemplos sobre a utilização de FDW com bancos de dados Oracle, MySQL e PostgreSQL, os procedimentos abaixo estão executando delete e insert na tabela Client do banco de dados Oracle através do PostgreSQL, e após as alterações, é realizado um acesso ao banco de dados Oracle para visualizar se as alterações foram aplicadas corretamente.
Erros comuns em configuração do FDW
Mesmo não sendo um processo complexo a configuração do FDW no sistema operacional e no banco de dados PostgreSQL, há determinados erros que não estão previstos em todos os ambientes, e por isso, é importante um entendimento do que está ocorrendo para contornar o problema. Abaixo é citado alguns exemplos sobre estes erros:
- ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle, este erro ocorre no momento em que o PostgreSQL vai se conectar ao Oracle para ler a tabela, mesmo após toda a configuração ter ocorrido corretamente. Motivo do erro: falta exportar a biblioteca do cliente Oracle. Para corrigir isso, utilize o mesmo procedimento informado para o erro abaixo;
- Mesmo após exportar todas as bibliotecas, ocorrem erros de falta de arquivos de bibliotecas. Para correção inclua o caminho da biblioteca do PostgreSQL, do cliente Oracle e do cliente MySQL no arquivo /etc/alternatives/pgsql-ld-conf e após as modificações execute o comando ldconfig para o arquivo ser carregado em memória. Para esta modificação é necessário acesso com o usuário root ou ter sudo para executar esta alteração. Os caminhos incluídos para os exemplos deste artigo foram /usr/pgsql-9.5/lib/, /usr/lib/oracle/11.2/client64/lib e /usr/lib64/mysql, isto porque estão no diretório padrão de instalação. Verifique se a sua instalação está da mesma forma;
- Makefile:23: /usr/pgsql-9.5/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory, esta mensagem acontece no primeiro passo da configuração do oracle_fdw, e ocorre porque ou o pacote postgresql95-devel não foi instalado ou ocorreu algum erro durante a instalação;
- pg_config: Command not found, isto significa que é necessário exportar a biblioteca do PostgreSQL (no caso, o 9.5) que no diretório default de instalação do pacote que foi baixado do repositório do PostgreSQl, está no caminho “usr/pgsql-9.5/bin”;
- HINT: Set the option “key” on the columns that belong to the primary key, esta mensagem deixa claro que é necessário definir como chave primária a coluna em que se está tentando executar DML. Defina qual coluna é a chave primária da tabela estrangeira (definir no PostgreSQL e não na tabela original);
- ERROR: Oracle table “ORACLE_USUARIO”.”client” for foreign table “client” does not exist or does not allow read access DETAIL: ORA-00942: table or view does not exist HINT: Oracle table names are case sensitive (normally all uppercase), a mensagem de HINT outra vez já deixa claro o que pode ter ocorrido, e este erro é bem comum no início de uma configuração de foreign table, o Oracle por default cria as tabelas como maiúsculo, portanto, para pesquisá-las através do FDW, crie as tabelas estrangeiras definindo o schema e table com maiúsculo;
- ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation, este erro indica que a primeira coluna da tabela estrangeira precisa ter uma chave para que o mysql_fdw envie a solicitação de insert, update ou delete para o banco de dados remoto. Neste caso, diferente do oracle_fdw, a tabela que deve ser modificada é no próprio MySQL e não a tabela estrangeira no PostgreSQL.
Pontos positivos na utilização do FDW
Este tipo de integração do FDW entre SGBDs distintos auxilia e muito diversas atividades cotidianas de um DBA e desenvolvedores, por exemplo em migrações de banco de dados. É possível também executar procedimentos e ferramentas que só existem no PostgreSQL com dados originados de outros bancos, onde algumas ferramentas são pagas, por exemplo, ou gerar de forma simplificada e automatizada a exportação de arquivos diretamente no sistema operacional em vários formatos utilizando os diversos pacotes fdw disponíveis nos repositórios do PostgreSQL;
A consulta a dados legados de diversas aplicações poderia ser simplificada utilizando o FDW, ao invés de diversos bancos de dados e aplicações, é possível ter todos os dados centralizados em um só banco de dados, considerando que dados legados não são frequentemente acessados e a tendência é sempre diminuir, isso elimina latência podendo inclusive, estarem todos os dados concentrados em uma única interface de acesso;
Utilizar o FDW para coletar dados de webservices e twitter pode ser uma ótima alternativa para o negócio onde é necessário ter um histórico de comportamentos de clientes e opiniões diversas que estão acontecendo em tempo real na internet.
Outra alternativa de uso do FDW é integrar dois bancos distintos (Oracle e SQL Server, por exemplo) utilizando o PostgreSQL como “mediador”, para executar rotinas de migração, de troca de informações, ou até mesmo utilizar como um terceiro banco que realiza um “join” entre dados de dois SGBDs externos e disponibiliza em uma terceira aplicação(-leia também sobre os pontos negativos do FDW sobre latência).
Pontos de atenção ao utilizar FDW
As queries merecem uma atenção especial, principalmente quando a pesquisa tem muitos filtros e joins, o FDW lida muito bem com queries complexas, mas a utilização de explain para analisar como o PostgreSQL está tratando o envio da solicitação e como o destino está recebendo é importante antes da implementação em produção.
Latência pode ser um problema para o FDW, dependendo da forma como a infraestrutura está organizada, se está na nuvem e não está no mesmo datacenter, pode causar latência entre os bancos de dados, ou no caso de servidores físicos, se não estão na mesma localidade fisica, pode causar lentidão no envio e retorno de queries, portanto, latência é um ponto a ser analisado.
Atualmente, são poucos os materiais disponíveis sobre o FDW, embora seja uma comunidade muito ativa do PostgreSQL, eventualmente pode ser complicado encontrar a solução imediatamente após um determinado evento, mas o suporte da comunidade PostgreSQL e dos desenvolvedores de FDW em fóruns costumam responder rápido sobre tentativas de solução.
O FDW não atualiza alterações de tabelas que houveram na origem, por exemplo, adição de colunas ou nomes, por este motivo, caso por exemplo tenha sido criado uma nova coluna em um tabela externa que é utilizada como tabela estrangeira no PostgreSQL, o correto é atualizar a tabela estrangeira com a recriação da mesma.
Sobre o FDW
O FDW é uma excelente alternativa de conexão entre banco de dados distintos, é confiável, de fácil administração, exige pouco conhecimento do sistema operacional Linux para configuração de repositórios, instalação de pacotes e exportação de variáveis de bibliotecas, e conhecimento mínimo para a conexão no banco de dados PostgreSQL e realizar configuração do FDW, contudo, o ideal é que um profissional com conhecimento do negócio e do banco de dados realize estas atividades.
Links
Foreign data wrappers (Wiki do PostgreSQL)
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
Planeta PostgreSQL
https://planeta.postgresql.org.br
Playing with FDW in PostgreSQL
http://kartoza.com/en/blog/playing-with-foreign-data-wrappers-in-postgresql
GitHub Laurenz Albe — Projeto oracle_fdw
https://github.com/laurenz/oracle_fdw
Why SQL/MED is cool
http://rhaas.blogspot.com.br/2011/01/why-sqlmed-is-cool.html
Descrição de FDW
https://www.infoq.com/br/articles/postgresql-fts
InfoQ — Full Text Search utilizando FDW
https://www.infoq.com/br/articles/postgresql-fts
Data do artigo: Junho/2017
🇧🇷
