Activación del acceso entre bases de datos en OCI Database with PostgreSQL mediante las extensiones postgres_fdw y dblink

Introducción

Oracle Cloud Infrastructure Database con PostgreSQL (OCI Database with PostgreSQL) soporta la conectividad entre bases de datos mediante extensiones como postgres_fdw y dblink. Estas extensiones permiten un acceso y un uso compartido de datos uniformes en las bases de datos PostgreSQL, útiles para análisis, consultas federadas o escenarios de integración heredados.

¿Qué es el dblink?

DBlink es una extensión PostgreSQL que permite la ejecución directa de sentencias SQL en una base de datos PostgreSQL remota. Devuelve juegos de resultados como filas o ejecuta comandos en el servidor remoto.

¿Qué es postgres_fdw?

postgres_fdw (Foreign Data Wrapper) es una extensión PostgreSQL que permite a una base de datos conectarse a tablas y consultarlas en un servidor PostgreSQL remoto como si fueran tablas locales. A diferencia de dblink, asigna tablas remotas como objetos locales y ofrece más control de procedimiento.

En este tutorial, exploraremos la configuración de las extensiones postgres_fdw y dblink en una instancia de OCI Database with PostgreSQL, junto con ejemplos para cada una.

Objetivos

Para permitir una comunicación de red segura y fiable entre dos sistemas de base de datos de OCI Database with PostgreSQL ubicados en diferentes redes virtuales en la nube (VCN) y regiones diferentes. Esta configuración garantiza que las conexiones a la base de datos funcionen sin problemas a través de los límites de la VCN. Incluye la configuración de gateways de intercambio de tráfico local (LPG) para el enrutamiento y la configuración de la resolución de DNS mediante vistas privadas. El objetivo es soportar la conectividad entre VCN y entre regiones para extensiones de base de datos como postgres_fdw o dblink.

Requisitos

  1. Activar la extensión desde la consola de OCI.

    1. Cree un archivo de configuración que permita la extensión dblink o postgres_fdw.

      image

    2. Aplique la configuración al sistema de base de datos.

      image

  2. Activar la extensión en el nivel de base de datos.

    Después de activar la extensión en la consola de OCI, conéctese a la base de datos y ejecute el siguiente comando SQL.

     CREATE EXTENSION postgres_fdw;

 CREATE EXTENSION dblink;

Tarea 2: Configuración de la conectividad de red entre sistemas de base de datos en diferentes redes virtuales en la nube

Para activar la comunicación entre dos sistemas de base de datos de OCI Database with PostgreSQL ubicados en diferentes redes virtuales en la nube, siga estos pasos:

  1. Crear gateways de intercambio de tráfico local (LPG).

    Cree un LPG en cada VCN para establecer una conexión de intercambio de tráfico entre ellos.

  2. Configurar tablas de rutas.

    1. Para cada VCN, actualice la tabla de rutas asociada al LPG para incluir una regla de ruta.

    2. Defina el bloque CIDR de destino en el rango de direcciones de la otra VCN de destino y especifique el siguiente salto como el LPG correspondiente.

  3. Configuración de la resolución de DNS mediante vistas privadas.

    1. Vaya a la consola de OCI y busque Vistas privadas para acceder a la configuración de DNS para ambas redes virtuales en la nube.

    2. Abra la vista privada de cada VCN y cree una entrada de zona.

  4. Crear un registro de zona para cada sistema de base de datos.

    En cada vista privada de la VCN, agregue una zona que represente el nombre de dominio completo (FQDN) del sistema de base de datos en la otra VCN.

  5. Agregar registros de DNS.

    1. Haga clic en el icono de tres puntos junto a la nueva entrada de zona o haga clic en el enlace FQDN.

    2. Seleccione Gestionar registros y haga clic en Agregar registro.

    3. Seleccione Tipo A (dirección IPv4), defina TTL en 500 e introduzca la dirección IP de punto final principal del sistema de base de datos de destino en la VCN opuesta.

    4. Haga clic en Agregar registro y, a continuación, en Publicar cambios.

Tarea 3: Configuración de la conectividad de red entre sistemas de base de datos en diferentes redes virtuales en la nube y en diferentes regiones

Para permitir la comunicación entre dos sistemas de base de datos OCI Database with PostgreSQL ubicados en diferentes redes virtuales en la nube y regiones diferentes.

  1. Crear gateways de direccionamiento dinámico (DRG).

    Cree un DRG en cada región y asócielo a la VCN correspondiente.

  2. Configurar tablas de rutas.

    1. Para cada VCN, actualice la tabla de rutas asociada al DRG.

    2. Agregue una regla de ruta con el bloque CIDR de destino de la otra VCN y especifique el siguiente salto como el DRG asociado.

  3. Configuración de la resolución de DNS mediante vistas privadas.

    1. Vaya a la consola de OCI y busque Vistas privadas para acceder a la configuración de DNS de cada VCN.

    2. Abra la vista privada de cada VCN y cree una entrada de zona.

  4. Crear un registro de zona para cada sistema de base de datos.

    En cada vista privada de la VCN, agregue una zona que represente el FQDN del sistema de base de datos en la otra VCN.

  5. Agregar registros de DNS.

    1. Haga clic en el icono de tres puntos junto a la nueva entrada de zona o haga clic en el enlace FQDN.

    2. Seleccione Gestionar registros y haga clic en Agregar registro.

    3. Seleccione Tipo A (dirección IPv4), defina TTL en 500 e introduzca la dirección IP de punto final principal del sistema de base de datos opuesto.

    4. Haga clic en Agregar registro y, a continuación, en Publicar cambios.

Tarea 4: Configure la extensión postgres_fdw con un ejemplo

  1. Crear Servidor de Contenedor de Datos Ajenos (FDW).

    El comando CREATE SERVER define un servidor externo denominado fdw_serv mediante el envoltorio postgres_fdw. Especifica los detalles de conexión como el host, el nombre de la base de datos y el puerto de la base de datos PostgreSQL de origen. Esto actúa como una referencia lógica para acceder a los datos de origen en la instancia PostgreSQL de destino.

     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. Asignar al usuario.

    El comando CREATE USER MAPPING asocia un usuario PostgreSQL de destino (arvindya) con las credenciales de la base de datos de origen. Esto permite la autenticación de destino cuando el servidor de destino se conecta al servidor externo definido. Garantiza que las consultas ejecutadas en el destino estén debidamente autorizadas en el servidor de origen.

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

  3. Importar Origen de Esquema.

    El comando IMPORT FOREIGN SCHEMA importa todas las tablas del esquema público del servidor de origen a la base de datos de destino como tablas ajenas. Estas tablas aparecen localmente, pero recuperan datos de la base de datos origen en tiempo real.

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

Por ejemplo:

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

La función dblink_connect establece una conexión de la base de datos PostgreSQL de destino a una base de datos PostgreSQL de origen mediante la cadena de conexión proporcionada. Incluye parámetros como el nombre de base de datos, el host, el puerto, el nombre de usuario y la contraseña. La conexión se identifica mediante un nombre único (myconn), que se utiliza para las operaciones dblink posteriores. Una conexión correcta devuelve OK (Correcto), lo que indica que está preparado 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 ejemplo:

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

Acuses de recibo

Más recursos de aprendizaje

Explore otros laboratorios en docs.oracle.com/learn o acceda a más contenido de aprendizaje gratuito en el canal YouTube de Oracle Learning. Además, visite education.oracle.com/learning-explorer para convertirse en un explorador de Oracle Learning.

Para obtener documentación sobre el producto, visite Oracle Help Center.