Ativar o Acesso entre Bancos de Dados no OCI Database with PostgreSQL usando as Extensões postgres_fdw e dblink

Introdução

O Oracle Cloud Infrastructure Database com PostgreSQL (OCI Database with PostgreSQL) suporta conectividade entre bancos de dados usando extensões como postgres_fdw e dblink. Essas extensões permitem acesso contínuo e compartilhamento de dados entre bancos de dados PostgreSQL, úteis para análises, consultas federadas ou cenários de integração legados.

O que é o dblink?

DBlink é uma extensão PostgreSQL que permite a execução direta de instruções SQL em um banco de dados PostgreSQL remoto. Ele retorna conjuntos de resultados como linhas ou executa comandos no servidor remoto.

O que é o postgres_fdw?

postgres_fdw (Foreign Data Wrapper) é uma extensão PostgreSQL que permite a um banco de dados estabelecer conexão com tabelas e consultá-las em um servidor PostgreSQL remoto como se fossem tabelas locais. Ao contrário do dblink, ele mapeia tabelas remotas como objetos locais e oferece mais controle procedural.

Neste tutorial, vamos explorar a configuração das extensões postgres_fdw e dblink em uma instância do OCI Database with PostgreSQL, juntamente com exemplos para cada uma.

Objetivos

Para permitir uma comunicação de rede segura e confiável entre dois sistemas de banco de dados do OCI Database with PostgreSQL localizados em redes virtuais na nuvem (VCNs) diferentes e regiões diferentes. Essa configuração garante que as conexões de banco de dados funcionem perfeitamente entre os limites da VCN. Inclui a configuração de LPGs (Local Peering Gateways) para roteamento e a configuração da resolução de DNS usando views privadas. O objetivo é oferecer suporte à conectividade entre VCN/região para extensões de banco de dados como postgres_fdw ou dblink.

Pré-requisitos

  1. Ativar a Extensão na Console do OCI.

    1. Crie um arquivo de configuração que ative a extensão dblink ou postgres_fdw.

      image

    2. Aplique a configuração ao sistema de banco de dados.

      image

  2. Ative a Extensão no Nível do Banco de Dados.

    Após ativar a extensão na Console do OCI, conecte-se ao banco de dados e execute o comando SQL a seguir.

     CREATE EXTENSION postgres_fdw;

 CREATE EXTENSION dblink;

Tarefa 2: Configurar a Conectividade de Rede entre Sistemas de Banco de Dados em VCNs Diferentes

Para permitir a comunicação entre dois sistemas de banco de dados do OCI Database with PostgreSQL localizados em VCNs diferentes, siga as etapas:

  1. Criar LPGs (Local Peering Gateways).

    Crie um LPG em cada VCN para estabelecer uma conexão de pareamento entre eles.

  2. Configurar Tabelas de Roteamento.

    1. Para cada VCN, atualize a tabela de roteamento associada ao LPG para incluir uma regra de roteamento.

    2. Defina o bloco CIDR de destino para a faixa de endereços da outra VCN de destino e especifique o próximo salto como o LPG correspondente.

  3. Configurar a Resolução de DNS Usando Views Privadas.

    1. Vá para a Console do OCI e procure Views Privadas para acessar a configuração de DNS para ambas as VCNs.

    2. Abra a view privada de cada VCN e crie uma entrada de zona.

  4. Criar um Registro de Zona para Cada Sistema de Banco de Dados.

    Na view privada de cada VCN, adicione uma zona que represente o FQDN (Nome de Domínio Totalmente Qualificado) do sistema de banco de dados na outra VCN.

  5. Adicionar Registros de DNS.

    1. Clique no ícone de três pontos ao lado da nova entrada de zona ou clique no link FQDN.

    2. Selecione Gerenciar Registros e clique em Adicionar Registro.

    3. Selecione Tipo A (IPv4 Endereço), defina TTL como 500 e informe o Endereço IP do ponto final principal do sistema de banco de dados de destino na VCN oposta.

    4. Clique em Adicionar Registro e em Publicar Alterações.

Tarefa 3: Configurar a Conectividade de Rede entre Sistemas de Banco de Dados em VCNs Diferentes e em Região Diferente

Para permitir a comunicação entre dois sistemas de banco de dados OCI Database with PostgreSQL localizados em diferentes VCNs e regiões diferentes.

  1. Criar DRGs (Dynamic Routing Gateways).

    Crie um DRG em cada região, anexando-o à respectiva VCN.

  2. Configurar Tabelas de Roteamento.

    1. Para cada VCN, atualize a tabela de roteamento associada ao DRG.

    2. Adicione uma regra de roteamento com o bloco CIDR de destino da outra VCN, especificando o próximo salto como o DRG anexado.

  3. Configurar a Resolução de DNS Usando Views Privadas.

    1. Vá para a Console do OCI e procure Views Privadas para acessar as definições de DNS de cada VCN.

    2. Abra a view privada de cada VCN e crie uma entrada de zona.

  4. Criar um Registro de Zona para Cada Sistema de Banco de Dados.

    Na view privada de cada VCN, adicione uma zona que represente o FQDN do Sistema de banco de dados na outra VCN.

  5. Adicionar Registros de DNS.

    1. Clique no ícone de três pontos ao lado da nova entrada de zona ou clique no link FQDN.

    2. Selecione Gerenciar Registros e clique em Adicionar Registro.

    3. Selecione Tipo A (IPv4 Endereço), defina TTL como 500 e informe o Endereço IP do ponto final principal do sistema de banco de dados oposto.

    4. Clique em Adicionar Registro e em Publicar Alterações.

Tarefa 4: Configurar a extensão postgres_fdw com um exemplo

  1. Criar Servidor FDW (Foreign Data Wrapper).

    O comando CREATE SERVER define um servidor externo chamado fdw_serv usando o encapsulador postgres_fdw. Especifica os detalhes da conexão, como host, nome do banco de dados e porta do banco de dados de origem PostgreSQL. Isso atua como uma referência lógica para acessar dados de origem na instância de destino PostgreSQL.

     CREATE SERVER <fdw_servername> FOREIGN DATA WRAPPER <fdw_servername> OPTIONS (host 'primary.xxxxxx.FQDN_Remote_source_DBSystem.... oci.oraclecloud.com', dbname 'remote_source_dbname', port '5432');

  2. mapear o Usuário.

    O comando CREATE USER MAPPING associa um usuário PostgreSQL de destino (arvindya) às credenciais do banco de dados de origem. Isso permite a autenticação de destino quando o servidor de destino se conecta ao servidor externo definido. Ele garante que as consultas executadas no destino estejam devidamente autorizadas no servidor de origem.

     CREATE USER MAPPING FOR <user_name> SERVER <fdw_servername> OPTIONS (user <user_name>, password <'user_password'>);

  3. Importar Origem do Esquema.

    O comando IMPORT FOREIGN SCHEMA importa todas as tabelas do esquema público do servidor de origem para o banco de dados de destino como tabelas externas. Essas tabelas aparecem locais, mas extraem dados do banco de dados de origem em tempo real.

     IMPORT FOREIGN SCHEMA <schema_name> FROM SERVER <fdw_server_name> INTO public;

Por exemplo:

-- Create extension
CREATE EXTENSION postgres_fdw;

-- Create FDW Server
CREATE SERVER fdw_serv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    host 'primary.xxxxxx.Remote Source DBSystem FQDN.....xxxxx.oci.oraclecloud.com',
    dbname 'source',
    port '5432'
);

-- User Mapping
CREATE USER MAPPING FOR arvindya SERVER fdw_serv OPTIONS (
    user '<user_name>',
    password '<user_password>'
);

-- Import Schema from source
IMPORT FOREIGN SCHEMA public FROM SERVER fdw_serv INTO public;

-- Validate Data
SELECT * FROM emp;

A função dblink_connect estabelece uma conexão do banco de dados PostgreSQL de destino com um banco de dados PostgreSQL de origem usando a string de conexão fornecida. Ela inclui parâmetros como nome do banco de dados, host, porta, nome de usuário e senha. A conexão é identificada por um nome exclusivo (myconn), que é usado para operações dblink subsequentes. Uma conexão bem-sucedida retorna OK, indicando a prontidão para consultas remotas.

SELECT dblink_connect ('Conn_name', 'dbname=<Remote_source_DBName> port=5432 host=primary.xxxxxx..Remote_source_DbSystem FQDN..xxxx.oci.oraclecloud.com user=<user_name> password=<'user_password'>);

Por exemplo:

CREATE DATABASE target;
\c target
CREATE EXTENSION dblink;

-- Establish Connection
SELECT dblink_connect ('myconn', 'dbname=source port=5432 host=primary.xxxxxx..Remote_source_DbSystem FQDN..xxxx.oci.oraclecloud.com user=<user_name> password=<user_password>');

-- Select from Source
SELECT * FROM dblink('myconn', 'SELECT * FROM testing') AS t(a int);

-- Execute SQL remotely
SELECT * FROM dblink_exec('myconn', 'create table dblinking_table (id int)');
SELECT * FROM dblink_exec('myconn', 'insert into dblinking_table values(generate_series(1,10))');
SELECT * FROM dblink_exec('myconn', 'delete from dblinking_table where id <= 5');

-- Final Validation
SELECT * FROM dblink('myconn', 'SELECT * FROM dblinking_table') AS t(a int);

Confirmações

Mais Recursos de Aprendizado

Explore outros laboratórios em docs.oracle.com/learn ou acesse mais conteúdo de aprendizado gratuito no canal do Oracle Learning YouTube. Além disso, acesse education.oracle.com/learning-explorer para se tornar um Oracle Learning Explorer.

Para obter a documentação do produto, visite o Oracle Help Center.