Activation de l'accès inter-base de données dans OCI Database with PostgreSQL à l'aide des extensions postgres_fdw et dblink

Introduction

Oracle Cloud Infrastructure Database avec PostgreSQL (OCI Database with PostgreSQL) prend en charge la connectivité inter-base de données à l'aide d'extensions telles que postgres_fdw et dblink. Ces extensions permettent un accès transparent et le partage de données entre les bases de données PostgreSQL, utiles pour les analyses, les requêtes fédérées ou les scénarios d'intégration hérités.

Qu'est-ce que dblink ?

DBlink est une extension PostgreSQL qui permet l'exécution directe d'instructions SQL sur une base de données PostgreSQL distante. Il renvoie des ensembles de résultats sous forme de lignes ou exécute des commandes sur le serveur distant.

Qu'est-ce que postgres_fdw ?

postgres_fdw (Foreign Data Wrapper) est une extension PostgreSQL qui permet à une base de données de se connecter à des tables et de les interroger dans un serveur PostgreSQL distant comme s'il s'agissait de tables locales. Contrairement à dblink, il mappe les tables distantes en tant qu'objets locaux et offre un contrôle plus procédural.

Dans ce tutoriel, nous allons explorer la configuration des extensions postgres_fdw et dblink dans une instance OCI Database with PostgreSQL, ainsi que des exemples pour chaque instance.

Objectifs

Permettre une communication réseau sécurisée et fiable entre deux bases de données OCI Database with PostgreSQL situés dans différents réseaux cloud virtuels et différentes régions. Cette configuration garantit que les connexions de base de données fonctionnent de manière transparente au-delà des frontières VCN. Elle inclut la configuration de passerelles d'appairage local pour le routage et la configuration de la résolution DNS à l'aide de vues privées. L'objectif est de prendre en charge la connectivité inter-VCN/inter-région pour les extensions de base de données telles que postgres_fdw ou dblink.

Prérequis

  1. Activez l'extension à partir de la console OCI.

    1. Créez un fichier de configuration permettant d'activer l'extension dblink ou postgres_fdw.

      image

    2. Appliquez la configuration au système de base de données.

      image

  2. Activez l'extension au niveau de la base de données.

    Après avoir activé l'extension dans la console OCI, connectez-vous à la base de données et exécutez la commande SQL suivante.

     CREATE EXTENSION postgres_fdw;

 CREATE EXTENSION dblink;

Tâche 2 : configuration de la connectivité réseau entre les systèmes de base de données de différents réseaux cloud virtuels

Pour activer la communication entre deux bases de données OCI Database with PostgreSQL situés dans des réseaux cloud virtuels différents, procédez comme suit :

  1. Création de passerelles d'appairage locales.

    Créez une passerelle d'appairage local dans chaque VCN pour établir une connexion d'appairage entre eux.

  2. Configurer les tables de routage.

    1. Pour chaque VCN, mettez à jour la table de routage associée à la passerelle d'appairage local afin d'inclure une règle de routage.

    2. Définissez le bloc CIDR de destination sur la plage d'adresses de l'autre VCN cible, puis spécifiez le saut suivant en tant que passerelle d'appairage local correspondante.

  3. Configuration de la résolution DNS à l'aide de vues privées.

    1. Accédez à la console OCI et recherchez des vues privées pour accéder à la configuration DNS pour les deux réseaux cloud virtuels.

    2. Ouvrez la vue privée de chaque VCN et créez une entrée de zone.

  4. Création d'un enregistrement de zone pour chaque système de base de données.

    Dans la vue privée de chaque VCN, ajoutez une zone qui représente le nom de domaine qualifié complet (FQDN) du système de base de données dans l'autre VCN.

  5. Ajouter des enregistrements DNS.

    1. Cliquez sur l'icône à trois points en regard de la nouvelle entrée de zone ou cliquez sur le lien de nom de domaine qualifié complet.

    2. Sélectionnez Gérer les enregistrements et cliquez sur Ajouter un enregistrement.

    3. Sélectionnez Type A (adresse IPv4), définissez TTL sur 500 et entrez l'adresse IP de l'adresse principale du système de base de données cible dans le VCN opposé.

    4. Cliquez sur Ajouter un enregistrement, puis sur Publier les modifications.

Tâche 3 : configuration de la connectivité réseau entre les systèmes de base de données de différents réseaux cloud virtuels et de différentes régions

Permettre la communication entre deux bases de données OCI Database with PostgreSQL situés dans différents réseaux cloud virtuels et différentes régions.

  1. Création de passerelles de routage dynamiques.

    Créez un DRG dans chaque région, en le rattachant au VCN respectif.

  2. Configurer les tables de routage.

    1. Pour chaque VCN, mettez à jour la table de routage associée au DRG.

    2. Ajoutez une règle de routage avec le bloc CIDR de destination de l'autre VCN, en indiquant le saut suivant comme DRG attaché.

  3. Configuration de la résolution DNS à l'aide de vues privées.

    1. Accédez à la console OCI et recherchez des vues privées pour accéder aux paramètres DNS de chaque VCN.

    2. Ouvrez la vue privée pour chaque VCN et créez une entrée de zone.

  4. Création d'un enregistrement de zone pour chaque système de base de données.

    Dans chaque vue privée de VCN, ajoutez une zone représentant le nom de domaine qualifié complet du système de base de données dans l'autre VCN.

  5. Ajouter des enregistrements DNS.

    1. Cliquez sur l'icône à trois points en regard de la nouvelle entrée de zone ou cliquez sur le lien de nom de domaine qualifié complet.

    2. Sélectionnez Gérer les enregistrements et cliquez sur Ajouter un enregistrement.

    3. Sélectionnez Type A (adresse IPv4), définissez TTL sur 500 et entrez l'adresse IP de l'adresse principale du système de base de données opposé.

    4. Cliquez sur Ajouter un enregistrement, puis sur Publier les modifications.

Tâche 4 : configurer l'extension postgres_fdw avec un exemple

  1. Création d'un serveur de wrapper de données étrangères.

    La commande CREATE SERVER définit un serveur étranger nommé fdw_serv à l'aide du wrapper postgres_fdw. Elle indique les détails de connexion tels que l'hôte, le nom de la base de données et le port de la base de données PostgreSQL source. Il s'agit d'une référence logique permettant d'accéder aux données source dans l'instance PostgreSQL cible.

     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. Mapper à l'utilisateur.

    La commande CREATE USER MAPPING associe un utilisateur PostgreSQL cible (arvindya) aux informations d'identification de base de données source. Cela permet l'authentification de la cible lorsque le serveur cible se connecte au serveur étranger défini. Elle garantit que les requêtes exécutées sur la cible sont correctement autorisées sur le serveur source.

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

  3. Importer la source du schéma.

    La commande IMPORT FOREIGN SCHEMA importe toutes les tables du schéma public du serveur source dans la base de données cible en tant que tables étrangères. Ces tables apparaissent en local, mais extraient les données de la base de données source en temps réel.

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

Exemple :

-- 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 fonction dblink_connect établit une connexion entre la base de données PostgreSQL cible et une base de données PostgreSQL source à l'aide de la chaîne de connexion fournie. Il inclut des paramètres tels que le nom de la base de données, l'hôte, le port, le nom utilisateur et le mot de passe. La connexion est identifiée par un nom unique (myconn), qui est utilisé pour les opérations dblink suivantes. Une connexion réussie renvoie OK, ce qui indique qu'elle est prête pour les requêtes distantes.

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

Exemple :

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

Accusés de réception

Ressources de formation supplémentaires

Explorez d'autres ateliers sur le site docs.oracle.com/learn ou accédez à d'autres contenus d'apprentissage gratuits sur le canal Oracle Learning YouTube. En outre, visitez le site education.oracle.com/learning-explorer pour devenir un explorateur Oracle Learning.

Pour obtenir de la documentation sur le produit, consultez Oracle Help Center.