Intégration compatible avec Aurora PostgreSQL avec les bases de données PostgreSQL distantes

Cette section décrit l'intégration d'Amazon Aurora PostgreSQL Compatible Edition aux bases de données PostgreSQL distantes à l'aide de l'extension postgres_fdw (enveloppe de données étrangères) ou de la fonctionnalité. dblink Le postgres_fdw module fournit une fonctionnalité de requête fédérée pour interagir avec des bases de données distantes basées sur PostgreSQL. Les bases de données distantes peuvent être gérées ou autogérées sur Amazon EC2 ou sur site. L'postgres_fdwextension est disponible dans toutes les versions actuellement prises en charge d'Amazon Relational Database Service (Amazon RDS) pour PostgreSQL et compatible avec Aurora PostgreSQL.

Grâce à cette postgres_fdw extension, vous pouvez accéder aux données des bases de données PostgreSQL distantes et les interroger comme s'il s'agissait de tables locales. L'postgres_fdwextension prend également en charge les éléments suivants :

• Compatibilité entre versions pour accéder aux données à partir de serveurs PostgreSQL externes exécutant différentes versions.

• Gestion des transactions, qui permet de garantir la cohérence et l'intégrité des données lorsque vous effectuez des opérations sur des serveurs PostgreSQL locaux et externes.

• Les transactions distribuées, qui fournissent des garanties d'atomicité (propriété des transactions ACID) et d'isolation lorsque vous effectuez des opérations sur plusieurs serveurs PostgreSQL externes. Cela permet de garantir que toutes les opérations d'une transaction sont validées ou qu'aucune n'est validée, préservant ainsi la cohérence et l'intégrité des données.

Bien que le dblink module permette d'interagir avec des bases de données PostgreSQL distantes, il ne prend pas en charge les transactions distribuées ni les autres fonctionnalités avancées. Si vous avez besoin de fonctionnalités plus avancées, pensez plutôt à utiliser l'postgres_fdwextension. L'postgres_fdwextension fournit davantage de fonctionnalités d'intégration et d'optimisation.

Cas d'utilisation de postgres_fdw et étapes de haut niveau

L'utilisation de l'postgres_fdwextension compatible avec Aurora PostgreSQL prend en charge les cas d'utilisation et les scénarios suivants :

• Requêtes fédérées et intégration de données ‒ Interrogation et combinaison de données provenant de plusieurs bases de données PostgreSQL au sein d'une seule instance compatible avec Aurora PostgreSQL

• Déchargement des charges de travail de lecture ‒ Connexion aux répliques en lecture de serveurs PostgreSQL externes, déchargement des charges de travail lourdes en lecture et amélioration des performances des requêtes

• Opérations entre bases de données ‒ ExécutionINSERT, UPDATEDELETE, et COPY opérations sur plusieurs bases de données PostgreSQL, permettant la manipulation des données et les tâches de maintenance entre bases de données

Pour configurerpostgres_fdw, suivez les étapes de haut niveau suivantes :

1. Connectez-vous à votre cluster compatible Aurora PostgreSQL à l'aide d'un client PostgreSQL et créez l'extension : postgres_fdw

   CREATE EXTENSION postgres_fdw;

   Cette extension fournit les fonctionnalités permettant de se connecter à des bases de données PostgreSQL distantes.

2. Créez un serveur étranger nommé à my_fdw_target l'aide de la CREATE SERVER commande. Ce serveur représente la base de données PostgreSQL distante à laquelle vous souhaitez vous connecter. Spécifiez le nom de la base de données, le nom d'hôte et le mode SSL comme options pour ce serveur.

3. Assurez-vous que les groupes de sécurité et les configurations réseau nécessaires sont en place pour permettre à Aurora PostgreSQL compatible de se connecter à la base de données PostgreSQL distante.

   Si la base de données distante est hébergée sur site, vous devrez peut-être configurer un réseau privé virtuel (VPN) ou une AWS Direct Connect connexion.

   Exécutez la commande suivante :

   CREATE SERVER my_fdw_target Foreign Data Wrapper postgres_fdw OPTIONS (DBNAME 'postgres', HOST 'SOURCE_HOSTNAME', SSLMODE 'require');

4. Créez un mappage utilisateur pour l'dbuserutilisateur sur le my_fdw_target serveur. Ce mappage associe l'dbuserutilisateur et le mot de passe de l'instance locale compatible Aurora PostgreSQL à l'utilisateur correspondant sur la base de données distante.

   CREATE USER MAPPING FOR dbuser SERVER my_fdw_target OPTIONS (user 'DBUSER', password 'PASSWORD');

   Cette étape est nécessaire pour authentifier et fournir un accès à la base de données distante.

5. Créez une table étrangère nommée customer_fdw avec le mappage de my_fdw_target serveurs et d'utilisateurs que vous avez configuré précédemment :

   CREATE FOREIGN TABLE customer_fdw( id int, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');

   La customer_fdw table est mappée à la customers table de la base de données distante spécifiée par le my_fdw_target serveur. La table étrangère possède la même structure que la table distante, de sorte que vous pouvez interagir avec les données distantes comme s'il s'agissait d'une table locale.

6. Vous pouvez effectuer diverses opérations de manipulation de données sur la table customer_fdw étrangère, telles que INSERTUPDATE, et des SELECT requêtes. Le script montre comment insérer une nouvelle ligne et mettre à jour une ligne existante, supprimer un enregistrement et tronquer une table de la table distante via la customers customer_fdw table étrangère :

   INSERT INTO customer_fdw values ( 1, 'Test1', '[e-mail]', 'LMS1', '888888888');
   INSERT INTO customer_fdw values ( 2, 'Test2', '[e-mail]', 'LMS2', '999999999');
   INSERT INTO customer_fdw values ( 3, 'Test3', '[e-mail]', 'LMS3', '111111111');
   UPDATE customer_fdw set contactnumber = '123456789' where id = 2;
   DELETE FROM customer_fdw where id = 1;
   TRUNCATE TABLE customer_fdw;

7. Vous pouvez valider un plan de requête SQL en utilisant l'EXPLAINinstruction pour analyser le plan de requête pour une SELECT requête sur la customer_fdw table :

   EXPLAIN select * from customer_fdw where id =1;

   Cela peut vous aider à comprendre comment la requête est exécutée et comment l'optimiser. Pour plus d'informations sur l'utilisation de EXPLAIN cette instruction, consultez la section Optimisation des performances AWS des requêtes PostgreSQL dans Prescriptive Guidance.

8. Pour importer plusieurs tables de la base de données distante dans un schéma local, utilisez la IMPORT FOREIGN SCHEMA commande :

   CREATE SCHEMA public_fdw;
   IMPORT FOREIGN SCHEMA public LIMIT TO (employees, departments)
       FROM SERVER my_fdw_target INTO public_fdw;

   Cela crée des tables étrangères locales pour les tables spécifiées dans le public_fdw schéma. Dans cet exemple, les tables spécifiques sont les employés et les départements.

9. Pour accorder les autorisations nécessaires à un utilisateur de base de données spécifique afin qu'il puisse accéder au FDW et au serveur étranger associé et les utiliser, exécutez les commandes suivantes :

   GRANT USAGE ON FOREIGN SERVER my_fdw_target TO targetdbuser;
   GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO targetdbuser;

   Cette étape peut être utile lorsque plusieurs utilisateurs ont besoin d'accéder aux tables étrangères facilitées par le wrapper de données étranger.

Lorsque vous utilisez des tables étrangères, tenez compte des limites suivantes :

• L'accès aux données depuis une source distante peut entraîner des coûts de transfert de données et une surcharge de performance en raison de la latence du réseau. Les problèmes de performances peuvent être perceptibles pour les ensembles de données volumineux ou les requêtes qui nécessitent un transfert de données important entre l'instance compatible Aurora PostgreSQL et la source de données distante.

• Dans les requêtes complexes impliquant des fonctionnalités telles que les fonctions de fenêtre, les requêtes récursives peuvent ne pas fonctionner comme prévu ou ne pas être prises en charge.

• Le chiffrement des mots de passe n'est actuellement pas pris en charge. Mettez en œuvre des contrôles pour garantir que seuls les utilisateurs autorisés peuvent accéder aux données des bases de données distantes FDWs et les récupérer.

• Les contraintes de clé primaire ne peuvent pas être définies sur les tables étrangères, comme le montre la tentative de script de création de table suivante :

  CREATE FOREIGN TABLE customer_fdw2( id int primary key, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
  Primary keys cannot be defined on Foreign table

• La ON CONFLICT clause pour les INSERT instructions n'est pas prise en charge sur les tables étrangères, comme le montre l'exemple suivant :

  INSERT INTO customer_fdw (id, name, emailid, projectname, contactnumber) VALUES
  (1, 'test1', '[e-mail]', 'LMS', 11111111 ),
  (3, 'test3', '[e-mail]', 'LMS', 22222222 )
  ON CONFLICT (id) DO UPDATE 
  SET name = EXCLUDED.name;
  On Conflict option doesnot work.

Nettoyage

Pour nettoyer les objets créés, notamment en supprimant l'postgres_fdwextension, le my_fdw_target serveur, les mappages d'utilisateurs et les tables étrangères, exécutez les commandes suivantes :

DROP FOREIGN TABLE customer_fdw;
DROP USER MAPPING for postgres;
DROP SERVER  my_fdw_target;
DROP EXTENSION postgres_fdw cascade;

Utiliser dblink pour créer des connexions

Les fonctions du dblink module offrent un autre moyen de créer des connexions et d'exécuter des instructions SQL sur des bases de données PostgreSQL distantes. La dblink solution est un moyen plus simple et plus flexible d'exécuter des requêtes ou des opérations ponctuelles sur des bases de données distantes. Pour les scénarios plus complexes impliquant une intégration de données à grande échelle, une optimisation des performances et des exigences en matière d'intégrité des données, nous vous recommandons d'utiliserpostgres_fdw.

L'utilisation dblink implique les étapes de haut niveau suivantes :

1. Créez l'dblinkextension :

   CREATE EXTENSION dblink;

   Cette extension fournit les fonctionnalités permettant de se connecter à des bases de données PostgreSQL distantes.

2. Pour établir une connexion à une base de données PostgreSQL distante, utilisez la fonction : dblink_connect

   SELECT dblink_connect('myconn', 'dbname=postgres port=5432 host=SOURCE_HOSTNAME user=postgres password=postgres');

3. Après vous être connecté à la base de données PostgreSQL distante, exécutez des instructions SQL sur la base de données distante à l'aide des fonctions suivantes : dblink

   SELECT  FROM dblink('myconn', 'SELECT col1, col2 FROM remote_table') AS remote_data(col1 int, col2 text);

   Cette requête exécute l'SELECT * FROM remote_tableinstruction sur la base de données distante à l'aide de la myconn connexion. La requête extrait les résultats dans une table temporaire locale avec les colonnes col1 etcol2.

4. Vous pouvez également exécuter des instructions autres que des requêtes, telles queINSERT, ou UPDATEDELETE, sur la base de données distante à l'aide de la dblink_exec fonction :

   SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''value'')');