9
05 fév 2016

Pour différentes raisons, il peut arriver que vous ayez besoin d'interroger une autre base que celle que vous êtes en train d'utiliser. Par exemple, on peut imaginer l'intégration de modifications d'une base dev vers une base prod ou comme dans mon cas la création d'une table en local à partir d'une table distante. Dans ce cas, plusieurs solutions classiques sont la création d'un script spécifique dans le langage de votre choix ou encore l'utilisation d'un outil type ETL.

Mais, dans la suite de ce tutoriel, nous verrons qu'à l'aide de l'extension dblink de Postgresql cela n'est pas forcément nécessaire. ,

Qu'est ce que dblink ?

dblink est une extension de POstgreSQL permettant d’interroger un serveur de bases de données distant à l'aide simplement de commandes SQL.

Comment utiliser dblink ?

dblink n'est pas disponible par défaut dans PostgreSQL. Il s'agit d'une extension qui s'installe très facilement de la manière suivante :

 create extension dblink; 

Et voilà à partir de maintenant vous disposez des instructions SQL nécessaires pour utiliser dblink. Dans le cas d'une connexion à un SGDB distant, deux arguments seront nécessaires. Tout d'abord les identifiants de connexion puis la requête SQL à executer.

dblink, un cas concret

Comme je le mentionnais en introduction, je souhaitais créer une table dans ma base locale à partir de données provenant d'une vue d'une base distante. Avec dblink, cela se fait à l'aide d'une simple commande SQL !

 CREATE TABLE ma_nouvelle_table AS
	SELECT * FROM
	dblink('host=remote_host
		  dbname=db_name
		  user=db_user
		  password=db_password',
		 'SELECT champ1, champ2, champGeom, champN  FROM ma_table_distante')
	AS table_distante(   num_piste integer,
		  champ1 bigint,
		  champ2 numeric(4,1),		 
		  champGeom geometry(Point,4326),
		  champN smallint
	  ); 

Et voilà, en une seule requête vous avez une nouvelle table créée contenant les données correspondant à la requête spécifiée !

A propos de l'auteur: 
Arnaud Vandecasteele

Fervent défenseur de l'Open Source, Arnaud s'est spécialisé dans le développement d'application cartographiques web. OpenLayers, PostGIS ou encore Django sont autant d'outils qu'il manipule au quotidien.
S'il n'est pas en face de son ordinateur, vous le retrouverez un GPS à la main en train de cartographier pour OpenStreetMap, de faire voler son drone ou sur un tatami !

Commentaires

Salut,
Le module dblink est désormais déconseillé et remplace par les FDW : foreign data wrappers. Cette technologie évolue assez vite est est bien plus puissante et rapide que dblink.
Et PG 9.5 apporte le magique IMPORT FOREIGN SCHEMA.
Bien sur postgis est supporté. Et il y a des fdw pour plein de sources de données : oracle, ogr, csv, db2, odbc...

Boonjour Vincent (Picavet ?),

Merci beaucoup pour cette information.

Je vais regarder ça de plus près dans ce cas !

La commande IMPORT FOREIGN SCHEMA apporte sans aucun doute une souplesse beaucoup plus grande que dblink !

Arnaud

Bonjour.

La technologie foreign data wrappers est-elle compatible avec Postgres 9.1.9 ?

Je pose la question parce qu'il est vrai que la fonction DBLink() à que j'utilise actuellement, notamment pour faire des vues, fait perdre pas mal de temps.

Les FDW ont été introduits à partir de PostgreSQL 9.1, mais le FDW spécifique vers postgres n'est disponible que depuis la 9.3 à priori.

Pour l'instant je me reposait exclusivement sur dblink car mon cas d'usage correspond exactement à ce qu’évoque Arnaud: je transfère régulièrement vers ma base de production des fichiers qui viennent d'une autre base qui sert plus à retravailler les données externes.

Cela dit je ne pense pas qu'on peut dire que les FDW remplacent dblink. Car sauf erreur de ma part, dblink permet d’exécuter n'importe quelle commande sur le serveur distant alors que les FDW table permettent "juste" de rendre accessible une table ou un schéma d'un serveur distant.

En fait c'est même carrément complémentaire puisque par ici on indique qu'on peut enregistrer les paramètres de connexion pour dblink dans une définition de FDW (CREATE SERVER + CREATE USER MAPPING).
voir doc dblink

Et comme à priori la construction est identique on doit pouvoir utiliser la même config pour dblink que pour CREATE FOREIGN TABLE.

Je vais creuser ça car ça risque de sacrément simplifier certaines de mes fonctions tout en évitant que les mots de passe se trimbalent en clair à chaque appelle de dblink ce qui semble plus propre.

Merci d'avoir remis tout ça en avant Arnaud ;)

Bonjour MarHoff,

C'est vrai qu'avec les FDW la construction des connexions distantes est beaucoup plus simple.

En plus, comme cela a été souligné, plus besoin d'afficher le mot de passe...

Par contre, les tables créées à partir de FDW ne sont pas visibles dans PGAdmin. Ce sont peut-être des objets un peu différents et du coup ce n'est pas encore disponible...

Arnaud

Mais si c'est possible ! (avec la carte kiwi?)

Il faut aller dans les préférences de pgadmin et choisirs les élément liés aux FDW qui apparaissent dans l'arborescence.
Pareil que pour les fonctions d'agrégats qui n'apparaissent pas non plus par défaut il me semble.

;)

Super info, merci MarHoff !

Bonjour.

Pour compléter un peu l'article, si l'on souhaite exécuter une action de type INSERT, UPDATE sur une base de données externe il est possible d'utiliser la fonction Postgres dblink_exec().

Par exemple :

SELECT dblink_exec('host=remote_host dbname=db_name user=db_user password=db_password','INSERT INTO matable ("nom") VALUES (''valeur'')') foo

Bonjour,

Je viens de me lancer dans l'utilisation du "FDW" de postgresql.

En suivant l'ensemble de la procédure :
CREATE SERVER ...
CREATE USER ....
CREATE FOREIGN TABLE

J'obtiens bien ma table visible dans pgadmin, mais elle reste désespérément vide. Je me demande si ce n'est pas lié au schéma. En effet, à aucun moment je n'ai fait référence au schéma de la base distante dans lequel se trouve la table que je veux "rapatrier".

J'ai du louper un truc !

Sinon FDW semble assez efficace.

Bonne journée

Antoine