Le module postgres_fdw fournit le wrapper de données distantes postgres_fdw, dont le but est de données accès à des données enregistrées dans des serveurs PostgreSQL™ externes.
Les fonctionnalités proposées par ce module sont à peu près les mêmes que celles proposées par le module dblink. Mais postgres_fdw fournit une syntaxe plus transparente et respectant les standards pour l'accès à des tables distantes. Elle peut aussi donner de meilleures performances dans beaucoup de cas.
Pour préparer un accès distant en utilisant postgres_fdw :
Installez l'extension postgres_fdw en utilisant CREATE EXTENSION(7).
Créez un objet serveur distant en utilisant CREATE SERVER(7), pour représenter chaque base distante à laquelle vous souhaitez vous connecter. Indiquez les informations de connexions, sauf user et password, comme options de l'objet serveur.
Créez une correspondance d'utilisateur avec CREATE USER MAPPING(7) pour chaque utilisateur de la base que vous voulez autoriser à accéder à un serveur distant. Indiquez le nom et le mot de passe de l'utilisateur distant avec les options user et password de la correspondance d'utilisateur.
Créez une table distante avec CREATE FOREIGN TABLE(7) ou IMPORT FOREIGN SCHEMA(7) pour chaque table distante que vous voulez utilisé. Les colonnes de la table distante doit correspondre aux colonnes de la table sur le serveur distant. Néanmoins, vous pouvez utiliser un nom de table et des noms de colonne différents de ceux de la table sur le serveur distant si vous indiquez les bons noms de colonne en options de la table distante.
Maintenant, vous avez seulement besoin de SELECT sur la table distante pour accéder aux données de la table du serveur distant. Vous pouvez aussi modifier la table sur le serveur distant en utilisant les commandes INSERT, UPDATE et DELETE. (Bien sûr, l'utilisateur utilisé pour la connexion au serveur distant doit avoir le droits de faire tout cela.)
Notez que postgres_fdw n'a pour l'instant pas de support pour les instructions INSERT avec une clause ON CONFLICT DO UPDATE. Néanmoins, la clause ON CONFLICT DO NOTHING est supporté, si la spécification de l'index unique est omise.
Il est généralement recommandé que les colonnes d'une table distante soient déclarées avec exactement les mêmes types de données et le même collationnement que celles utilisées pour les colonnes référencées dans le table du serveur distant. Bien que postgres_fdw est actuellement assez lâche sur les conversions de type de données, des anomalies sémantiques surprenantes peuvent survenir quand les types ou les collationnements ne correspondent pas dans le cas où le serveur distant interprète légèrement différemment les clauses WHERE.
Notez qu'une table distante peut être déclarée avec moins de colonnes ou avec les colonnes dans un ordre différent. La correspondance des colonnes sur la table du serveur distant se fait par nom, et non pas par position.
Un serveur distant utilisant le wrapper de données distantes postgres_fdw peut avoir les mêmes options que celles acceptées par libpq dans les chaînes de connexion comme décrit dans Section 33.1.2, « Mots clés de la chaîne de connexion ». Cependant, ces options ne sont pas autorisées :
user et password (spécifiez-les au niveau de la correspondance d'utilisateur)
client_encoding (ceci est configuré automatiquement à partir de l'encodage du serveur local)
fallback_application_name (toujours configuré à postgres_fdw)
Seuls les superutilisateurs peuvent se connecter à un serveur distant sans authentification par mot de passe. Donc spécifiez toujours l'option password pour les correspondances d'utilisateur appartenant aux utilisateurs simples.
Ces options peuvent être utilisées pour contrôler les noms utilisés dans les requêtes SQL envoyées au serveur PostgreSQL™ distant. Ces options sont nécessaires lorsqu'une table distante est créée avec des noms différents de ceux de la table du serveur distant.
Cette option, qui peut être indiquée pour une table distante, donne le nom du schéma à utiliser pour la table du serveur distant. Si cette option est omise, le nom du schéma de la table distante est utilisé.
Cette option, qui peut être indiquée pour une table distante, donne le nom de la table à utiliser pour la table du serveur distant. Si cette option est omise, le nom de la table distante est utilisé.
Cette option, qui peut être indiquée pour une colonne d'une table distante, donne le nom de la colonne à utiliser pour la colonne de la table du serveur distant. Si cette option est omise, le nom de la colonne de la table distante est utilisé.
postgres_fdw récupère des données distantes en exécutant des requêtes sur des serveurs distants. Idéalement, le coût estimé du parcours d'une table distante devrait être celui occasionné par le parcours de la table sur le serveur distant, et un supplément causé par la communication entre le serveur local et le serveur distant. Le moyen le plus fiable d'obtenir une telle estimation est de demander au serveur distant, puis d'ajouter quelque chose pour le supplément. Pour des requêtes simples, cela ne vaut pas le coût d'une requête supplémentaire vers le serveur distant. Donc postgres_fdw propose les options suivantes pour contrôler la façon dont l'estimation de coût est faite :
Cette option, qui peut être indiquée pour une table distante ou pour un serveur distant, contrôle si postgres_fdw exécute des commandes EXPLAIN distantes pour obtenir les estimations de coût. Une configuration sur la table distante surcharge celle sur le serveur, mais seulement pour cette table. La valeur par défaut est false.
Cette option, qui peut être indiquée pour un serveur distant, est une valeur numérique qui est ajoutée au coût de démarrage estimé de tout parcours de table distante sur ce serveur. Cela représente le coût supplémentaire causé par l'établissement d'une connexion, l'analyse et la planification de la requête du côté du serveur distant, etc. La valeur par défaut est 100.
Cette option, qui peut être indiquée pour un serveur distant, est une valeur numérique qui est utilisée comme coût supplémentaire par ligne pour les parcours de la table distante sur ce serveur. Cela représente le coût supplémentaire associé au transfert de données entre les serveurs. Vous pouvez augmenter ou réduire ce nombre pour refléter les latences réseau vers le serveur distant. La valeur par défaut est 0.01.
Quand use_remote_estimate est vrai, postgres_fdw obtient le nombre de lignes et les estimations de coût à partir du serveur distant. Il ajoute fdw_startup_cost et fdw_tuple_cost aux estimations de coût. Quand use_remote_estimate est faux, postgres_fdw réalise le décompte local des lignes ainsi que l'estimation de coût, puis ajoute fdw_startup_cost et fdw_tuple_cost aux estimations de coût. Cette estimation locale a peu de chances d'être précise sauf si des copies locales des statistiques de la table distante sont disponibles. Exécuter ANALYZE(7) sur la table distante permet de mettre à jour les statistiques locales ; cela exécute un parcours sur la table distante, puis calcule et enregistre les statistiques comme si la table était locale. Garder des statistiques locales peut être utile pour réduire la surcharge de planification par requête pour une table distante mais, si la table distante est fréquemment mise à jour, les statistiques locales seront rapidement obsolètes.
Par défaut, seules les clauses WHERE utilisant des opérateurs et des fonctions intégrés sont considérés pour une exécution sur le serveur distant. Les clauses impliquant des fonctions non intégrées sont vérifiées localement une fois les lignes récupérées. Si ces fonctions sont disponibles sur le serveur distant et peuvent produire les mêmes résultats que localement, les performances peuvent être améliorées en envoyant ces clauses WHERE pour une exécution distante. Ce comportement peut être contrôlé en utilisant l'option suivante :
Cette option est une liste de noms d'extensions PostgreSQL™, séparés par des virgules, installées dans des versions compatibles sur les serveurs local et distant. Les fonctions et opérateurs immutables et appartenant à une extension listée seront considérées pour une exécution sur le serveur distant. Cette option peut seulement être spécifiée sur les serveurs distants, et non pas par table.
Lors de l'utilisation de l'option extensions, il est de la responsabilité de l'utilisateur que les extensions listées existent bien et se comportent de façon identique sur les serveurs local et distant. Dans le cas contraire, les requêtes pourraient échouer ou se comporter de façon inattendue.
Cette option indique le nombre de lignes que postgres_fdw doit récupérer à chaque opération de lecture. Cette option est disponible au niveau serveur et table. Une configuration spécifiée sur une table surcharge celle du serveur. La valeur par défaut est 100.
Par défaut, toutes les tables distantes utilisant postgres_fdw sont supposées comme étant modifiables. Cela peut se surcharger en utilisant l'option suivante :
Cette option contrôle si postgres_fdw autorise les tables distantes à être modifiées en utilisant les commandes INSERT, UPDATE et DELETE. Cette option est utilisable sur une table distante ou sur un serveur distant. La configuration de cette option au niveau table surcharge celle au niveau serveur. La valeur par défaut est true.
Bien sûr, si la table distante n'est pas modifiable, une erreur surviendra malgré tout. L'utilisation de cette option permet principalement que l'erreur soit renvoyée localement, sans avoir à tenter l'exécution sur le serveur distant. Notez néanmoins que les vues information_schema indiqueront que la table distante est modifiable ou pas, suivant la configuration de cette option, et donc sans vérification du serveur distant.
postgres_fdw est capable d'importer les définitions des tables distantes en utilisant IMPORT FOREIGN SCHEMA(7). Cette commande crée les définitions des tables distantes sur le serveur local, correspondant aux tables et vues présentes sur le serveur distant. Si les tables distantes à importer ont des colonnes de type défini par des utilisateurs, le serveur local doit avoir des types compatibles de même nom.
Le comportement de l'import est personnalisable avec les options suivantes (à fournir à la commande IMPORT FOREIGN SCHEMA) :
Cette option contrôle si les options COLLATE d'une colonne sont inclus dans les définitions des tables distantes importées à partir d'un serveur distant. La valeur par défaut est true. Vous pourriez avoir besoin de la désactiver si le serveur distant possède un ensemble de noms de collation différent de celui du serveur local, ce qui risque d'être le cas s'il utilise un autre système d'exploitation.
Cette option contrôle si les expressions DEFAULT d'une colonne sont incluses dans les définitions des tables distantes importées d'un serveur distant. La valeur par défaut est false. Si vous activez cette option, faites attention au fait que les valeurs par défaut pourraient être calculées différemment sur le serveur local et sur le serveur distant ; par exemple, nextval() est une source habituelle de problèmes. La commande IMPORT échouera si une expression par défaut importée utilise une fonction ou un opérateur qui n'existe pas localement.
Cette option contrôle si les contraintes NOT NULL des colonnes sont incluses dans les définitions des tables distantes importées à partir d'un serveur distant. La valeur par défaut est true.
Notez que les contraintes autres que NOT NULL ne seront jamais importées des tables distantes. Bien que PostgreSQL™ supporte les contraintes CHECK sur les tables distantes, rien n'existe pour les importer automatiquement à cause du risque qu'une expression de contrainte puisse être évaluée différemment entre les serveurs local et distant. Toute incohérence du comportement d'une contrainte CHECK pourrait amener des erreurs difficile à détecter dans l'optimisation des requêtes. Donc, si vous souhaitez importer les contraintes CHECK, vous devez le faire manuellement et vous devez vérifier la sémantique de chaque constrainte avec attention. Pour plus de détails sur le traitement des contraintes CHECK sur les tables distantes, voir CREATE FOREIGN TABLE(7).
Les tables ou tables distantes qui sont des partitions d'autres tables sont automatiquement exclues. Les tables partitionnées sont importées, à moins qu'elles soient des partitioins d'une autre table. Puisque toutes les données à traver la table partitionnées qui est à la racine de toute la hiérarchie de partitionnement, cette approche devrait autoriser l'accès à toutes les données sans avoir besoin de créer d'objets supplémentaires.
postgres_fdw établit une connexion au serveur distant lors de la première requête qui utilise une table distante associée avec le serveur distant. La connexion est conservée et réutilisée pour les requêtes suivants de la même session. Néanmoins, si plusieurs identités d'utilisateur (correspondances d'utilisateur) sont utilisées pour accéder au serveur distant, une connexion est établie pour chaque correspondance d'utilisateur.
Lorsqu'une requête référence des tables sur un serveur distant, postgres_fdw ouvre une transaction sur le serveur distant si une transaction n'est pas déjà ouverte pour la transaction locale en cours. La transaction distante est validée ou annulée suivant que la transaction locale est validée ou annulée. Les points de sauvegardes sont gérés de la même façon en créant les points de sauvegarde correspondants.
La transaction distante utilise le niveau d'isolation SERIALIZABLE quand la transaction locale a le niveau SERIALIZABLE. Dans les cas contraires, elle utilise le niveau REPEATABLE READ. Ce choix assure que, si une requête réalise plusieurs parcours de table sur le serveur distant, elle obtiendra des résultats cohérents pour tous les parcours. Une conséquence est que les requêtes successives à l'intérieur d'une seule transaction verront les mêmes données provenant du serveur distant, même si des mises à jour sont réalisées en même temps avec l'activité standard du serveur. Ce comportement serait attendue de toute façon si la transaction locale utilise le niveau d'isolation SERIALIZABLE ou REPEATABLE READ mais elle pourrait surprendre pour une transaction locale en niveau READ COMMITTED. Une prochaine version de PostgreSQL™ pourrait modifier ce comportement.
postgres_fdw tente d'optimiser les requêtes distantes pour réduire la quantité de données transférées depuis les serveurs distants. Cela se fait en envoyant les clauses WHERE au serveur distant pour exécution, et en ne récupérant que les colonnes nécessaires pour la requête courante. Pour réduire le risque de mauvaise exécution des requêtes, les clauses WHERE ne sont pas envoyées au serveur distant sauf si elles utilisent seulement des types de données, opérateurs et fonctions intégrées ou appartenant à une extension listée dans l'option extensions du serveur distant. Les opérateurs et fonctions dans ce type de clause doivent aussi être IMMUTABLE. Pour une requête UPDATE ou DELETE, postgres_fdw tente d'optimiser l'exécution de la requête en envoyant la requête complète au serveur distant s'il n'existe pas de clauses WHERE pouvant être envoyées au serveur distant, pas de jointures locales pour la requête, pas de triggers BEFORE ou AFTER au niveau ligne sur la table cible, et pas de contraintes CHECK OPTION pour les vues parentes. Dans un UPDATE, les expressions à affecter aux colonnes cibles doivent seulement utiliser les types de données intégrées, les opérateurs ou les fonctions IMMUTABLE pour réduire le risque de mauvaise exécution de la requête.
Quand postgres_fdw rencontre une jointure entre des tables externes sur le même serveur distant, il envoie la jointure entière au serveur distant, sauf s'il pense qu'il sera plus efficace de récupérer les lignes de chaque table individuellement ou si les références de table sont sujet à des correspondances d'utilisateur différentes. Lors de l'envoi des clauses JOIN, il prend les mêmes précautions que mentionnées ci-dessus pour les clauses WHERE.
La requête envoyée au serveur distant pour exécution peut être examinée en utilisant EXPLAIN VERBOSE.
Dans les sessions distantes ouvertes par postgres_fdw, le paramètre search_path est configuré à pg_catalog, pour que seuls les objets internes soient visibles, sauf utilisant d'un nom de schéma. Ceci n'est pas un problème pour les requêtes générées par postgres_fdw lui-même car il fournit toujours ce type de qualification. Néanmoins, cela peut se révéler problématique pour les fonctions exécutées sur le serveur distant via des triggers ou des règles sur les tables distantes. Par exemple, si une table distante est en fait une vue, toute fonction utilisée dans cette vue sera exécutée avec le chemin de recherche restreint. Il est recommandé de qualifier tous les noms dans ce type de fonctions ou de leur attacher une option SET search_path (voir CREATE FUNCTION(7)) pour établier le chemin de recherche attendu.
De même, postgres_fdw établie une configuration des sessions distantes pour différents paramètres :
TimeZone est positionné à UTC
datestyle est positionné à ISO
IntervalStyle est positionné à postgres
extra_float_digits est positionné à 3 pour les serveurs distants de version 9.0 et après et est positionné à 2 pour les versions plus anciennes
Ces paramètres sont moins à même d'être problématique que search_path, mais ils peuvent être gérés avec les options de fonction SET si le besoin devait se faire sentir.
Il n'est pas recommandé de surcharger ce comportement en modifiant la configuration de la session pour ces paramètres. Cela peut être la cause d'un mauvais fonctionnement de postgres_fdw.
postgres_fdw peut être utilisé avec les serveurs distants de version 8.3 et ultérieures. En lecture seule, il est possible d'aller aussi loin que la 8.1. Néanmoins, une limitation est que postgres_fdw assume généralement que les fonctions et opérateurs internes immutables sont sûrs pour être envoyés au serveur distant pour exécution s'ils apparaissent dans une clause WHERE de la table distante. Du coup, une fonction interne ajoutée depuis la sortie du serveur distant pourrait être envoyée pour exécution, résultant en un message d'erreur indiquant que la fonction n'existe pas (« function does not exist ») ou une erreur similaire. Ce type d'échec peut être contourné en réécrivant la requête, par exemple en embarquant la table distance dans un sous-SELECT avec OFFSET 0 comme optimisation, et plaçant la fonction ou l'opérateur problématique en dehors du sous-SELECT.
Voici un exemple de création d'une table distante avec postgres_fdw. Tout d'abord, il faut installer l'extension :
CREATE EXTENSION postgres_fdw;
Ensuite, il faut créer un serveur distant avec CREATE SERVER(7). Dans cet exemple, nous souhaitons nous connecter à un serveur PostgreSQL™ sur l'hôte 192.83.123.89 écoutant sur le port 5432. La base de données sur le serveur distant sur laquelle la connexion est faite s'appelle foreign_db :
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
Une correspondance d'utilisateur, définie avec CREATE USER MAPPING(7), est également nécessaire pour identifier le rôle qui sera utilisé sur le serveur distant :
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
Il est maintenant possible de créer une table distante avec CREATE FOREIGN TABLE(7). Dans cet exemple, nous souhaitons accéder à la table nommée some_schema.some_table sur le serveur distant. Le nom local pour celle-ci sera foreign_table :
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text
)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
Il est essentiel que les types de données et autres propriétés des colonnes déclarées dans CREATE FOREIGN TABLE correspondent à la vraie table distante. Les noms des colonnes doivent également correspondre, à moins que des options column_name soient attachées aux colonnes individuelles pour montrer comment elles sont nommées sur la table distante. Dans de nombreux cas, l'utilisation de IMPORT FOREIGN SCHEMA(7) est préférable à la construction manuelle des tables distantes.
Shigeru Hanada <shigeru.hanada@gmail.com>
pg_visibility
