Abilita l'accesso tra database nel database OCI con PostgreSQL utilizzando le estensioni postgres_fdw e dblink

Introduzione

Oracle Cloud Infrastructure Database con PostgreSQL (OCI Database con PostgreSQL) supporta la connettività tra database utilizzando estensioni come postgres_fdw e dblink. Queste estensioni consentono l'accesso e la condivisione dei dati senza problemi nei database PostgreSQL, utili per analytics, query federate o scenari di integrazione legacy.

Che cosa è Dblink?

DBlink è un'estensione PostgreSQL che consente l'esecuzione diretta delle istruzioni SQL su un database PostgreSQL remoto. Restituisce i set di risultati come righe o esegue comandi sul server remoto.

Che cos'è postgres_fdw?

postgres_fdw (Foreign Data Wrapper) è un'estensione PostgreSQL che consente a un database di connettersi e di eseguire query sulle tabelle in un server PostgreSQL remoto come se fossero tabelle locali. A differenza di dblink, mappa le tabelle remote come oggetti locali e offre un controllo più procedurale.

In questa esercitazione esamineremo la configurazione delle estensioni postgres_fdw e dblink in un database OCI con istanza PostgreSQL, insieme a esempi per ciascuna.

Obiettivi

Per abilitare la comunicazione di rete sicura e affidabile tra due database OCI con sistemi di database PostgreSQL situati in reti cloud virtuali (VCN) diverse e in aree diverse. Questa impostazione garantisce che le connessioni al database funzionino perfettamente attraverso i confini della VCN. Include la configurazione di Local Peering Gateways (LPG) per l'instradamento e l'impostazione della risoluzione DNS utilizzando viste private. L'obiettivo è supportare la connettività tra VCN e tra più aree per estensioni di database come postgres_fdw o dblink.

Prerequisiti

  1. Abilita l'estensione da OCI Console.

    1. Creare un file di configurazione che abiliti l'estensione dblink o postgres_fdw.

      immagine

    2. Applicare la configurazione al sistema di database.

      immagine

  2. Abilita estensione a livello di database.

    Dopo aver abilitato l'estensione in OCI Console, connettersi al database ed eseguire il comando SQL seguente.

     CREATE EXTENSION postgres_fdw;

 CREATE EXTENSION dblink;

Task 2: Configurare la connettività di rete tra i sistemi di database in VCN diversi

Per abilitare la comunicazione tra due database OCI con sistemi di database PostgreSQL posizionati in VCN diversi, effettuare le operazioni riportate di seguito.

  1. Crea Local Peering Gateway (LPG).

    Creare un GPL in ogni VCN per stabilire una connessione di peering tra di loro.

  2. Configura tabelle di instradamento.

    1. Per ogni VCN, aggiornare la tabella di instradamento associata al GPL in modo da includere una regola di instradamento.

    2. Impostare il blocco CIDR di destinazione sull'intervallo di indirizzi dell'altra VCN di destinazione e specificare l'hop successivo come LPG corrispondente.

  3. Impostare la risoluzione DNS utilizzando le viste private.

    1. Andare alla console OCI, cercare Viste private per accedere alla configurazione DNS per entrambe le reti VCN.

    2. Aprire la vista privata di ogni VCN e creare una voce di zona.

  4. Creare un record zona per ogni sistema di database.

    Nella vista privata di ciascuna VCN, aggiungere una zona che rappresenta il nome dominio completamente qualificato (FQDN) del sistema di database nell'altra VCN.

  5. Aggiungere record DNS.

    1. Fare clic sull'icona a tre punti accanto alla nuova voce di zona oppure fare clic sul collegamento FQDN.

    2. Selezionare Gestisci record e fare clic su Aggiungi record.

    3. Selezionare Tipo A (IPv4 Indirizzo), impostare TTL su 500 e immettere l'indirizzo IP dell'endpoint primario del sistema di database di destinazione nella VCN opposta.

    4. Fare clic su Aggiungi record, quindi su Pubblica modifiche.

Task 3: Configurare la connettività di rete tra i sistemi di database in VCN diversi e in aree diverse

Per abilitare la comunicazione tra due database OCI con sistemi di database PostgreSQL situati in VCN e aree diverse.

  1. Crea gateway di instradamento dinamico (DRG).

    Creare un DRG in ogni area, collegandolo alla rispettiva VCN.

  2. Configura tabelle di instradamento.

    1. Per ogni VCN, aggiornare la tabella di instradamento associata al DRG.

    2. Aggiungere una regola di instradamento con il blocco CIDR di destinazione dell'altra VCN, specificando l'hop successivo come DRG collegato.

  3. Impostare la risoluzione DNS utilizzando le viste private.

    1. Andare alla console OCI, cercare Viste private per accedere alle impostazioni DNS per ogni VCN.

    2. Aprire la vista privata per ogni VCN e creare una voce di zona.

  4. Creare un record zona per ogni sistema di database.

    Nella vista privata di ciascuna VCN, aggiungere una zona che rappresenta il nome FQDN del sistema di database nell'altra VCN.

  5. Aggiungere record DNS.

    1. Fare clic sull'icona a tre punti accanto alla nuova voce di zona oppure fare clic sul collegamento FQDN.

    2. Selezionare Gestisci record e fare clic su Aggiungi record.

    3. Selezionare Tipo A (IPv4 Indirizzo), impostare TTL su 500 e immettere l'indirizzo IP dell'endpoint primario del sistema di database opposto.

    4. Fare clic su Aggiungi record, quindi su Pubblica modifiche.

Task 4: configurare l'estensione postgres_fdw con un esempio

  1. Crea server FDW (Foreign Data Wrapper).

    Il comando CREATE SERVER definisce un server esterno denominato fdw_serv utilizzando il wrapper postgres_fdw. Specifica i dettagli di connessione quali l'host, il nome del database e la porta del database PostgreSQL di origine. Questo funge da riferimento logico per l'accesso ai dati di origine all'interno dell'istanza PostgreSQL di destinazione.

     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. Mapping utente.

    Il comando CREATE USER MAPPING associa un utente PostgreSQL di destinazione (arvindya) alle credenziali del database di origine. Ciò consente l'autenticazione della destinazione quando il server di destinazione si connette al server esterno definito. Garantisce che le query eseguite siano autorizzate correttamente sul server di origine.

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

  3. Importa origine schema.

    Il comando IMPORT FOREIGN SCHEMA importa tutte le tabelle dallo schema pubblico del server di origine nel database di destinazione come tabelle esterne. Queste tabelle vengono visualizzate in locale, ma recuperano i dati dal database di origine in tempo reale.

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

Ad esempio:

-- 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 funzione dblink_connect stabilisce una connessione dal database PostgreSQL di destinazione a un database PostgreSQL di origine utilizzando la stringa di connessione fornita. Include parametri quali nome del database, host, porta, nome utente e password. La connessione è identificata da un nome univoco (myconn), che viene utilizzato per le successive operazioni dblink. Una connessione riuscita restituisce OK, indicando la disponibilità per le query remote.

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

Ad esempio:

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

Conferme

Altre risorse di apprendimento

Esplora altri laboratori su docs.oracle.com/learn o accedi a più contenuti di formazione gratuiti sul canale YouTube di Oracle Learning. Inoltre, visitare education.oracle.com/learning-explorer per diventare Oracle Learning Explorer.

Per la documentazione del prodotto, visitare Oracle Help Center.