Cómo conectar PostgreSQL a fuentes de datos externas

Una característica muy interesante de PostgreSQL y que no es común en otros Sistemas Gestores de Bases de Datos, es la capacidad de acceder a diferentes almacenes de datos de fuentes remotas mediante una gran variedad de wrappers o contenedores de datos externos disponibles. Desde nuestro servidor Postgres podemos consultar directamente otras bases de datos o archivos externos (CSV, JSON, hojas de cálculo, etc.), sin necesidad de replicar datos a través de un proceso ETL (Extract, Transform and Load), y todo ello como si lo hiciésemos localmente.

Esta propiedad tan útil llamada Foreign Data Wrapper (FDW) crea tablas foráneas dentro una base de datos PostgreSQL que actúan a modo de proxies para alguna otra fuente de datos. Cuando realizamos una consulta en una tabla foránea, el wrapper preguntará a la fuente de datos externa y devolverá los resultados como si procedieran de una tabla propia. Esto aumenta la productividad al poder, por ejemplo, consultar una base de datos SQL Server o realizar un JOIN entre una tabla MySQL y una colección MongoDB desde tu propia instancia de PostgreSQL. El uso de FDW es también muy interesante cuando hay que gestionar grandes bases de datos federadas distribuidas en varias instancias.

Cómo usar FDW para conectar dos bases de datos PostgreSQL remotas

1. Habilitamos la extensión fwd en la base de datos desde la que queremos acceder (en nuestro caso la local) y creamos opcionalmente un nuevo esquema para tenerlo todo más organizado:

CREATE EXTENSION postgres_fdw;
 CREATE SCHEMA esquema_destino;

2. Opcionalmente nos aseguramos de eliminar la configuración al servidor remoto si ya la tuviésemos creada previamente.

DROP SERVER IF EXISTS servidor_bd_remota CASCADE;

3. Configuramos el acceso al servidor remoto:

CREATE SERVER servidor_bd_remota 
 FOREIGN DATA WRAPPER postgres_fdw
 OPTIONS (host 'xx.xx.xx.xx', dbname 'foo', port '5432');

4. Para consultar la base de datos remota es necesario saber qué usuario podrá realizar esta operación. Básicamente se trata de asignar un usuario local de nuestro servidor a un usuario del servidor remoto y para ello se crea una asignación de usuarios. En este caso el usuario es el mismo desde el que estamos operando (CURRENT_USER), pero podría ser postgres o cualquier otro:

CREATE USER MAPPING FOR CURRENT_USER SERVER servidor_bd_remota 
 OPTIONS (USER 'remote_user', password 'admin');

5. Importamos el esquema remoto de tablas en el esquema de la base de datos local:

IMPORT FOREIGN SCHEMA esquema_remoto 
FROM SERVER servidor_bd_remota INTO esquema_destino;

6. Finalmente ya podemos consultar esta tabla externa como si estuviese en nuestra base de datos:

SELECT * FROM tabla_foranea;

Si quieres un ejemplo sencillo con el que poder practicar aquí tienes el código para acceder mediante FDW a la base de datos de RNAcentral, una base de datos de acceso público de solo-lectura de secuencias de ácido ribonucleico (ARN).

Todo lo que hemos visto hasta ahora esta muy bien, pero sería muy interesante si pudiésemos utilizar estos contenedores de datos externos para acceder a geodatos.

FWD desde una perspectiva geográfica

Si nos fijamos en los tipos de contenedores de datos externos publicados en la web de PostgreSQL podemos ver que existen varios geowrappers, y entre ellos llama la atención uno nativo que permite acceder a la gran mayoría de fuentes de datos geoespaciales que habitualmente usamos: GDAL/OGR

Si desconoces qué es GDAL solo cabe decir que es la navaja suiza de los GIS. Esta librería es uno de los pilares sobre el que se fundamenta gran parte del desarrollo de software geoespacial actual, tanto de servidor como de escritorio, por lo que se la considera como un proyecto prioritario de software libre para la industria.

La librería se conoce en su conjunto simplemente como GDAL pero integra dos partes bien diferenciadas: OGR para manejar datos vectoriales y GDAL para la parte de datos ráster. De ahí que también se la llame GDAL/OGR.

Para visualizar el potencial que tiene este wrapper de GDAL en PostgreSQL vamos a ver un ejemplo en el que poder consultar los datos espaciales vectoriales facilitados por un servicio WFS directamente como si fuera una tabla nativa de Postgres.

CREATE EXTENSION postgis;
 CREATE EXTENSION ogr_fdw;

 DROP SERVER IF EXISTS fdw_wfs_server;

 CREATE SERVER fdw_wfs_server
   FOREIGN DATA WRAPPER ogr_fdw 
   OPTIONS ( 
     datasource 'WFS:https://servais.enaire.es/einsignia/services/Aero_VIGOR_SRV/MapServer/WFSServer',
     format 'WFS',
     config_options 'GDAL_HTTP_UNSAFESSL=YES'); -- para que no se moleste en verificar el certificado SSL

 -- A partir de PostgreSQL 9.5 ya es posible crear automáticamente la estructura de la
 -- tabla externa en nuestro servidor sin tener que definir las columnas manualmente:
 IMPORT FOREIGN SCHEMA "Aero_VIGOR_SRV:Aerodromos" 
 FROM SERVER fdw_wfs_server INTO public;

Aquí vemos cómo se crea un objeto de servidor externo mediante CREATE SERVER y se configura el acceso al servicio WFS público facilitado por el gestor de navegación aérea ENAIRE.

A continuación se importa el esquema de la capa de aeródromos, lo que crea una tabla foránea llamada aero_vigor_srv_aerodromos en el esquema public de nuestra base de datos Postgres local.

Ya solo nos queda realizar una consulta SQL a la tabla externa para obtener y analizar los datos remotos del servicio WFS.

SELECT * 
FROM aero_vigor_srv_aerodromos 
WHERE activity = 'Aeromodelismo';

Las bases de datos relacionales como PostgreSQL han dominado durante mucho tiempo el almacenamiento y el acceso de datos, pero a veces necesitamos acceder desde nuestras aplicaciones a datos que están en un formato diferente. Como hemos visto los Foreign Data Wrappers nos proporcionan una capa de abstracción que nos facilita poder consultar esos datos de forma transparente, sin importar de donde proceden, cómo están estructurados y sin la necesidad de almacenar su contenido directamente en nuestra base de datos.

GDAL a su vez dota a PostgreSQL de una segunda capa de abstracción adicional que “traduce” los formatos geoespaciales que el Sistema Gestor de Bases de Datos no entiende, posibilitando así poder consultarlos mediante SQL, la lengua franca de las bases de datos.