Maîtrisez les outils et bonnes pratiques DevOps - DevOps Knowledge Hub Maîtrisez les outils et bonnes pratiques DevOps - DevOps Knowledge Hub

Optimisation des Foreign Data Wrappers PostgreSQL pour l'intégration multi-bases de données

Utilisez les FDW PostgreSQL pour interroger des bases de données distantes en toute sécurité, optimiser le pushdown et éviter les pièges de performance courants dans la fédération.

Optimisation des Wrappers de Données Externes PostgreSQL pour l'Intégration Multi-Bases de Données

Les Wrappers de Données Externes PostgreSQL aident lorsque vos données résident à plusieurs endroits et que vous devez les interroger sans tout copier au préalable. Les FDW peuvent rendre les tables distantes locales, mais les performances et le comportement des transactions dépendent fortement du wrapper, du réseau et de la forme de la requête.

Que sont les Wrappers de Données Externes ?

FDW implémente la norme SQL/MED (Management of External Data), offrant :

  • Accès transparent : Interrogez les données distantes en utilisant SQL standard
  • Tables virtuelles : Aucune duplication de données requise
  • Lectures distantes en direct : Les requêtes lisent depuis la source distante au lieu d'une copie locale
  • Capacité de jointure : Combinez des données locales et distantes
  • Opérations d'écriture : Certains wrappers prennent en charge INSERT, UPDATE et DELETE

Cas d'Utilisation Courants

  1. Migration de Base de Données : Accédez aux systèmes existants pendant la migration
  2. Architecture Multi-Locataire : Interrogez les bases de données des locataires
  3. Agrégation de Données : Combinez des données provenant de plusieurs sources
  4. Cloud Hybride : Connectez des bases de données sur site et dans le cloud
  5. Rapports : Créez une base de données de rapports centralisée
  6. Microservices : Interrogez les limites de service

Wrappers de Données Externes Disponibles

postgresql_fdw (Intégré)

  • Connectez-vous à d'autres bases de données PostgreSQL
  • Prise en charge complète des fonctionnalités
  • Meilleures performances

mysql_fdw

  • Connectez-vous à MySQL/MariaDB
  • Prise en charge de la lecture et de l'écriture

oracle_fdw

  • Connectez-vous aux bases de données Oracle
  • Couramment utilisé via l'extension open-source oracle_fdw, avec les bibliothèques client Oracle installées séparément

mongodb_fdw

  • Accédez aux collections MongoDB
  • Intégration de données JSON

file_fdw (Intégré)

  • Lisez les fichiers CSV et texte
  • Analyse de fichiers journaux

Autres

  • redis_fdw, sqlite_fdw, couchdb_fdw, etc.

Configuration de postgres_fdw

Étape 1 : Installer l'Extension

-- Sur la base de données locale
CREATE EXTENSION postgres_fdw;

Étape 2 : Créer un Serveur Externe

CREATE SERVER remote_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote-db.example.com',
        port '5432',
        dbname 'production_db',
        fetch_size '10000'
    );

Étape 3 : Créer un Mappage d'Utilisateur

-- Mapper les utilisateurs locaux aux identifiants distants
CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (
        user 'remote_user',
        password 'secure_password'
    );

-- Mapper tous les utilisateurs locaux
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (
        user 'readonly_user',
        password 'readonly_pass'
    );

Étape 4 : Créer des Tables Externes

Définition manuelle de la table :

CREATE FOREIGN TABLE remote_users (
    id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (schema_name 'public', table_name 'users');

Importer tout le schéma :

IMPORT FOREIGN SCHEMA public
    FROM SERVER remote_db
    INTO local_schema;

Importer des tables spécifiques :

IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, orders, products)
    FROM SERVER remote_db
    INTO public;

Étape 5 : Interroger les Données Externes

-- Requête simple
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '7 days';

-- Joindre des données locales et distantes
SELECT 
    l.order_id,
    l.amount,
    r.username,
    r.email
FROM local_orders l
JOIN remote_users r ON l.user_id = r.id
WHERE l.status = 'pending';

Configuration Avancée

Options d'Optimisation des Performances

CREATE SERVER fast_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'db.example.com',
        port '5432',
        dbname 'mydb',
        
        -- Taille de récupération : lignes récupérées par aller-retour
        fetch_size '50000',
        
        -- Activer les optimisations de pushdown de requête
        use_remote_estimate 'true',
        
        -- Réutiliser les connexions au serveur externe dans les sessions PostgreSQL
        keep_connections 'on',
        
        -- Exécution parallèle des requêtes
        parallel_commit 'on',
        parallel_abort 'on'
    );

Options au Niveau de la Table

CREATE FOREIGN TABLE optimized_table (
    id INTEGER,
    data TEXT
)
SERVER remote_db
OPTIONS (
    schema_name 'public',
    table_name 'large_table',
    
    -- Remplacer la taille de récupération pour cette table
    fetch_size '100000',
    
    -- Utiliser les estimations distantes pour une meilleure planification des requêtes
    use_remote_estimate 'true'
);

Options au Niveau de la Colonne

CREATE FOREIGN TABLE mapped_columns (
    local_id INTEGER OPTIONS (column_name 'remote_user_id'),
    local_name TEXT OPTIONS (column_name 'remote_username')
)
SERVER remote_db
OPTIONS (table_name 'users');

Opérations d'Écriture

Activer les Opérations d'Écriture

-- Accorder les permissions
GRANT INSERT, UPDATE, DELETE ON remote_users TO app_user;

-- Insérer des données
INSERT INTO remote_users (username, email)
VALUES ('newuser', '[email protected]');

-- Mettre à jour des données
UPDATE remote_users
SET email = '[email protected]'
WHERE username = 'newuser';

-- Supprimer des données
DELETE FROM remote_users WHERE username = 'newuser';

Transactions Entre Bases de Données

BEGIN;
    -- Insérer dans la table locale
    INSERT INTO local_orders (user_id, amount) VALUES (123, 99.99);
    
    -- Mettre à jour la table distante
    UPDATE remote_users SET last_order = NOW() WHERE id = 123;
    
    -- Les deux opérations validées ensemble
COMMIT;

PostgreSQL coordonne le travail distant via le FDW, mais vous ne devez pas supposer que chaque wrapper vous donne des garanties complètes de transaction distribuée. Pour postgres_fdw, examinez l'option de serveur two_phase_commit si vous avez besoin d'un comportement de validation en deux phases, et testez la récupération après incident avant de vous y fier pour des écritures critiques.

Fédération Multi-Bases de Données

Connexion de Plusieurs Bases de Données

-- Connectez-vous à la base de données de production
CREATE SERVER prod_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'prod.example.com', dbname 'production');

CREATE USER MAPPING FOR PUBLIC SERVER prod_db
    OPTIONS (user 'readonly', password 'pass1');

-- Connectez-vous à la base de données d'analyse
CREATE SERVER analytics_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'analytics.example.com', dbname 'warehouse');

CREATE USER MAPPING FOR PUBLIC SERVER analytics_db
    OPTIONS (user 'readonly', password 'pass2');

-- Connectez-vous à la base de données d'archives
CREATE SERVER archive_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'archive.example.com', dbname 'historical');

CREATE USER MAPPING FOR PUBLIC SERVER archive_db
    OPTIONS (user 'readonly', password 'pass3');

Vues Unifiées Entre Bases de Données

-- Importer les tables
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (current_orders) 
    FROM SERVER prod_db INTO public;

IMPORT FOREIGN SCHEMA public 
    LIMIT TO (archived_orders) 
    FROM SERVER archive_db INTO public;

-- Créer une vue unifiée
CREATE VIEW all_orders AS
SELECT *, 'current' AS source FROM current_orders
UNION ALL
SELECT *, 'archived' AS source FROM archived_orders;

-- Interroger toutes les bases de données
SELECT 
    source,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM all_orders
WHERE created_at > '2024-01-01'
GROUP BY source;

Connexion à MySQL

Installer mysql_fdw

# Les noms de paquets varient selon la version de PostgreSQL et le dépôt.
# Sur Debian/Ubuntu, recherchez d'abord :
apt search mysql-fdw

# Ensuite, installez le paquet qui correspond à votre version majeure de PostgreSQL.
sudo apt install postgresql-16-mysql-fdw

Configurer la Connexion MySQL

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (
        host 'mysql-db.example.com',
        port '3306'
    );

CREATE USER MAPPING FOR PUBLIC
    SERVER mysql_server
    OPTIONS (
        username 'mysql_user',
        password 'mysql_pass'
    );

CREATE FOREIGN TABLE mysql_products (
    id INTEGER,
    name VARCHAR(100),
    price DECIMAL(10,2)
)
SERVER mysql_server
OPTIONS (dbname 'ecommerce', table_name 'products');

-- Interroger les données MySQL depuis PostgreSQL
SELECT * FROM mysql_products WHERE price > 100;

Optimisation des Performances

1. Utiliser le Pushdown de la Clause WHERE

PostgreSQL pousse les filtres vers le serveur distant :

-- Bon : Filtre appliqué à distance
SELECT * FROM remote_users WHERE country = 'US';

-- Vérifier le plan de requête
EXPLAIN (VERBOSE) SELECT * FROM remote_users WHERE country = 'US';
-- Rechercher : "Remote SQL: SELECT ... WHERE country = 'US'"

2. Limiter le Transfert de Données

-- Mauvais : Récupère toutes les colonnes
SELECT * FROM remote_large_table;

-- Bon : Seulement les colonnes nécessaires
SELECT id, username FROM remote_large_table;

-- Bon : Utiliser LIMIT
SELECT * FROM remote_large_table LIMIT 1000;

3. Optimiser la Taille de Récupération

-- Pour les grands ensembles de résultats
ALTER SERVER remote_db OPTIONS (SET fetch_size '50000');

-- Pour les requêtes petites et fréquentes
ALTER SERVER remote_db OPTIONS (SET fetch_size '1000');

4. Utiliser les Estimations Distantes

ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- PostgreSQL interrogera EXPLAIN distant pour des estimations de coût précises

5. Indexer les Tables Distantes

-- Assurez-vous que les tables distantes ont des index appropriés
-- Connectez-vous directement à la base de données distante :

CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_orders_created_at ON orders(created_at);

6. Vues Matérialisées pour les Données Fréquemment Consultées

-- Mettre en cache les données distantes localement
CREATE MATERIALIZED VIEW cached_remote_data AS
SELECT * FROM remote_users WHERE active = true;

-- Rafraîchir périodiquement
REFRESH MATERIALIZED VIEW cached_remote_data;

-- Rafraîchissement automatique avec pg_cron si cette extension est installée
CREATE EXTENSION pg_cron;
SELECT cron.schedule('refresh-cache', '0 */6 * * *', 
    'REFRESH MATERIALIZED VIEW cached_remote_data');

Surveillance et Dépannage

Voir les Serveurs Externes

SELECT 
    srvname AS server_name,
    srvoptions AS options
FROM pg_foreign_server;

Voir les Tables Externes

SELECT 
    foreign_table_schema,
    foreign_table_name,
    foreign_server_name
FROM information_schema.foreign_tables;

Vérifier les Mappages d'Utilisateurs

SELECT 
    um.umuser::regrole AS local_user,
    fs.srvname AS foreign_server,
    um.umoptions AS options
FROM pg_user_mapping um
JOIN pg_foreign_server fs ON um.umserver = fs.oid;

Analyser les Performances des Requêtes

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '1 day';

Vérification de la Connexion

Utilisez EXPLAIN (ANALYZE, VERBOSE) pour confirmer le SQL distant et le timing. Pour les liens PostgreSQL vers PostgreSQL, vérifiez également pg_stat_activity sur le serveur distant pendant l'exécution d'une longue requête FDW.

Problèmes Courants et Solutions

Problème 1 : Délai d'Attente de Connexion

Erreur : could not connect to server

Solutions :

-- Ajouter un délai d'attente de connexion
ALTER SERVER remote_db OPTIONS (ADD connect_timeout '10');

-- Vérifier la connectivité réseau
-- Depuis le shell :
psql -h remote-db.example.com -U user -d dbname

Problème 2 : Requêtes Lentes

Solutions :

-- Activer les estimations distantes
ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- Augmenter la taille de récupération
ALTER SERVER remote_db OPTIONS (SET fetch_size '100000');

-- Vérifier si la clause WHERE est poussée
EXPLAIN VERBOSE SELECT * FROM remote_table WHERE id > 1000;

Problème 3 : Échec d'Authentification

Erreur : password authentication failed

Solutions :

-- Mettre à jour le mappage d'utilisateur
ALTER USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (SET password 'new_password');

-- Vérifier pg_hba.conf sur le serveur distant
-- Assurez-vous qu'il autorise les connexions depuis le serveur local

Problème 4 : Incompatibilité de Type de Colonne

Erreur : column type mismatch

Solution :

-- Caster explicitement les colonnes
CREATE FOREIGN TABLE remote_data (
    id INTEGER,
    data TEXT,
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (table_name 'source_table');

-- Ou importer avec les bons types
DROP FOREIGN TABLE remote_data;
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (source_table) 
    FROM SERVER remote_db INTO public;

Considérations de Sécurité

1. Utiliser des Comptes en Lecture Seule

-- Sur la base de données distante, créer un utilisateur limité
CREATE ROLE fdw_readonly;
GRANT CONNECT ON DATABASE production_db TO fdw_readonly;
GRANT USAGE ON SCHEMA public TO fdw_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fdw_readonly;

-- Utiliser dans le mappage d'utilisateur
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (user 'fdw_readonly', password 'safe_pass');

2. Stockage Sécurisé des Mots de Passe

-- Vous pouvez utiliser un fichier .pgpass pour les connexions libpq au lieu de stocker
-- le mot de passe dans le mappage d'utilisateur. Confirmez la propriété et le mode du fichier.
-- Format ~/.pgpass :
-- hostname:port:database:username:password

CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (user 'remote_user');
    -- Mot de passe lu depuis .pgpass

3. Sécurité Réseau

-- Utiliser des connexions SSL
-- Dans postgresql.conf sur le serveur distant :
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

-- Exiger SSL dans le mappage d'utilisateur
CREATE SERVER secure_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote.example.com',
        dbname 'mydb',
        sslmode 'require'
    );

Meilleures Pratiques

  1. Minimiser le transfert de données : Sélectionner uniquement les colonnes nécessaires
  2. Utiliser des index : Assurez-vous que les tables distantes sont correctement indexées
  3. Mettre en cache les données fréquemment consultées : Utiliser des vues matérialisées
  4. Surveiller les performances : EXPLAIN ANALYZE régulier
  5. Limiter les opérations d'écriture : Privilégier la lecture seule pour la plupart des utilisations FDW
  6. Utiliser le regroupement de connexions : Activer keep_connections
  7. Définir une taille de récupération appropriée : Ajuster en fonction des modèles de requête
  8. Tester les scénarios de basculement : Planifier l'indisponibilité de la base de données distante
  9. Documenter les dépendances externes : Maintenir une documentation claire
  10. Audits de sécurité réguliers : Examiner les mappages d'utilisateurs et les permissions

À Retenir

Les Wrappers de Données Externes PostgreSQL sont utiles pour la fédération de bases de données, surtout lorsque vous avez besoin de :

  • Intégration transparente : Interroger les données distantes avec SQL standard
  • Accès en temps réel : Aucune duplication de données ni délai de synchronisation
  • Prise en charge multi-bases de données : PostgreSQL, MySQL, Oracle, et plus
  • Performances : Pushdown de requête lorsque le wrapper et la requête le supportent
  • Flexibilité : Accès en lecture d'abord, avec écritures seulement après avoir vérifié le comportement du wrapper

Commencez par des requêtes en lecture seule vers des bases de données distantes, optimisez les performances avec un indexage approprié et des tailles de récupération, puis étendez-vous à des intégrations plus complexes selon les besoins.