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

Présentation

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 interrogations fédérées ou les scénarios d'intégration existants.

Qu'est-ce que dblink?

DBlink est une extension PostgreSQL qui permet l'exécution directe d'énoncés 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 (encapsuleur de données étranger) est une extension PostgreSQL qui permet à une base de données de se connecter aux tables d'un serveur PostgreSQL distant et de les interroger comme s'il s'agissait de tables locales. Contrairement à dblink, il mappe les tables distantes en tant qu'objets locaux et offre plus de contrôle 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 chacune.

Objectifs

Pour permettre une communication réseau sécurisée et fiable entre deux systèmes de base de données OCI Database with PostgreSQL avec PostgreSQL situés dans différents réseaux en nuage virtuels et différentes régions. Cette configuration garantit que les connexions de base de données fonctionnent de façon transparente au-delà des limites du VCN. Il comprend la configuration des passerelles d'appairage local (LPG) 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.

Conditions requises

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

    1. Créez un fichier de configuration pour l'extension dblink ou postgres_fdw.

      Image

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

      Image

  2. Activer 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 : Configurer la connectivité réseau entre les systèmes de base de données dans différents réseaux en nuage virtuels

Pour permettre la communication entre deux bases de données OCI Database with PostgreSQL et des systèmes de base de données PostgreSQL situés dans des réseaux en nuage virtuels différents, procédez comme suit :

  1. Créer des passerelles d'appairage local (LPG).

    Créez une passerelle LPG 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 LPG afin d'inclure une règle de routage.

    2. Réglez le bloc CIDR de destination à l'intervalle d'adresses de l'autre VCN cible et spécifiez le saut suivant comme passerelle LPG correspondante.

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

    1. Allez à la console OCI, recherchez Vues privées pour accéder à la configuration DNS pour les deux réseaux en nuage virtuels.

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

  4. Créer 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 complet (FQDN) du système de base de données de l'autre VCN.

  5. Ajouter des enregistrements DNS.

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

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

    3. Sélectionnez Type A (IPv4 Adresse), réglez TTL à 500 et entrez l'adresse IP du point d'extrémité principal 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 : Configurer la connectivité réseau entre les systèmes de base de données dans différents réseaux en nuage virtuels et différentes régions

Pour permettre la communication entre deux systèmes de base de données OCI Database with PostgreSQL avec PostgreSQL situés dans des réseaux en nuage virtuels et des régions différentes.

  1. Créer des passerelles de routage dynamique (DRG).

    Créez une passerelle DRG dans chaque région, en l'attachant au VCN respectif.

  2. Configurer les tables de routage.

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

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

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

    1. Allez à la console OCI, recherchez 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éer un enregistrement de zone pour chaque système de base de données.

    Dans la vue privée de chaque VCN, ajoutez une zone représentant le nom de domaine 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 à côté de la nouvelle entrée de zone ou cliquez sur le lien du nom de domaine complet.

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

    3. Sélectionnez Type A (IPv4 Adresse), réglez TTL à 500 et entrez l'adresse IP du point d'extrémité principal 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éer un serveur d'encapsuleur de données étranger.

    La commande CREATE SERVER définit un serveur étranger nommé fdw_serv à l'aide de l'encapsuleur postgres_fdw. Il spécifie 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 pour accéder aux données sources 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 données d'identification de la base de données source. Cela permet l'authentification cible lorsque le serveur cible se connecte au serveur étranger défini. Il garantit que les interrogations 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 locales 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;

Par 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 d'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 retourne OK, ce qui indique que vous êtes prêt pour les interrogations 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'>);

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

Remerciements

Ressources d'apprentissage supplémentaires

Explorez d'autres laboratoires sur le site docs.oracle.com/learn ou accédez à plus de contenu d'apprentissage gratuit sur le canal Oracle Learning YouTube. De plus, visitez education.oracle.com/learning-explorer pour devenir un explorateur Oracle Learning.

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