Foreign Tables en PostgreSQL (postgresql_fwd)

Standard
Hola a todos, recientemente nos encontramos con una situación donde tuvimos la necesidad, donde por orden, se propuso usar diferentes Bases de Datos en PostgreSQL para organizar por estrategia a los diferentes tipos de problemas que se estaban atendiendo.
Supongamos que queremos hacer un análisis de marcas de autos, y queremos dividir un volumen de datos considerable para su extracción y análisis por tipo de datos. Y al final necesitamos poder consultarlos todos dentro de una base de datos integradora.
Para resolver este tipo de problemas nos ayudó la extensión postgres_fdw de PostgreSQL.
Supongamos que tenemos los siguientes objetos:
  • marcas_db. DB Integradora.
  • marca_mercedes_db. DB Mercedes Benz. (pudiera ser más marcas: BMW, Audi, etc.)
    Esta Base de Datos puede tener varias tablas, como ejemplo vamos a crear el mapeo de una tabla.

    • atributos_auto. Tabla de atributos del auto con dos atributos: id y name.

Primero vamos e instalamos la extensión. En Amazon RDS está disponible para la versión 9.4 que estamos usando.

CREATE EXTENSION postgres_fdw;

Ya en la DB de marcas_db creamos un usuario guest para hacer la conexión, y les damos los permisos necesarios:

\c marcas_db
CREATE USER marcas_guest WITH PASSWORD ‘password';
GRANT USAGE ON SCHEMA public TO marcas_guest;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO marcas_guest;
Ya con el usuario marcas_guest listo, procedemos a crear el Servidor para el esquema ‘public’ y el Mapeo del Usuario:
Nota. Es recomendado usar un prefijo para identificar el servidor que se mapea, en este caso usaremos ‘mercedes_’
— Damos de baja el server si ya existía, la opción de cascada borra todos los objetos dependientes, *TODOS*.
DROP SERVER IF EXISTS mercedes_server CASCADE;
— Creamos el servidor de mapeo con nombre 'mercedes_server'.
CREATE SERVER mercedes_server
 FOREIGN DATA WRAPPER postgres_fdw 
  OPTIONS (host 'localhost', port '5432', dbname 'marca_mercedes_db');
— Creamos el mapeo de usuario de marcas_guest
CREATE USER MAPPING FOR public SERVER 
  mercedes_server
  OPTIONS (USER 'mercedes_server', password 'password');
(En caso de necesitar actualizar la contraseña del usuario o cambiar le usuario de servicio, pueden dar da baja el Mapeo de Usuario y darlo de alta de nuevo)
DROP USER MAPPING IF EXISTS FOR public SERVER mercedes_server;
Procedemos a crear el mapeo de la tabla atributos auto, en el cual proponemos seguir usando el prefijo, como en este caso es ‘mercedes_’:
CREATE FOREIGN TABLE public.mercedes_atributo_auto (id CHARACTER VARYING(50), name CHARACTER VARYING(100))
    SERVER tw_server  OPTIONS (schema_name 'public', TABLE_NAME 'atributo_auto');
Como podrán ver es necesario definir todos los campos de la tabla y tipos de datos.
Esto puede ser muy laborioso, por que en cada cambio que haya en alguna tabla en marca_mercedes_db es necesario borrar la tabla foránea y volver a darla de alta:
DROP FOREIGN TABLE IF EXISTS public.mercedes_atributo_auto CASCADE;
Como no es muy práctico les voy a dejar una función ‘script_foreign_tables’ para generar los scripts de todas las tablas que necesites, el cual me encontré en el siguiente link:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE OR REPLACE FUNCTION script_foreign_tables(param_server text
, param_schema_search text
, param_table_search text, param_ft_prefix text) RETURNS SETOF text
AS 
$
-- params: param_server: name of foreign data server
-- param_schema_search: wildcard search on schema use % for non-exact
-- param_ft_prefix: prefix to give new table in target database 
-- include schema name if not default schema
-- example usage: SELECT script_foreign_tables('prod_server', 'ch01', '%', 'ch01.ft_');
WITH cols AS 
( SELECT cl.relname AS TABLE_NAME, na.nspname AS table_schema, att.attname AS column_name
, format_type(ty.oid,att.atttypmod) AS column_type
, attnum AS ordinal_position
FROM pg_attribute att
JOIN pg_type ty ON ty.oid=atttypid
JOIN pg_namespace tn ON tn.oid=ty.typnamespace
JOIN pg_class cl ON cl.oid=att.attrelid
JOIN pg_namespace na ON na.oid=cl.relnamespace
LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
WHERE 
-- only consider non-materialized views and concrete tables (relations)
cl.relkind IN('v','r') 
AND na.nspname LIKE $2 AND cl.relname LIKE $3 
AND cl.relname NOT IN('spatial_ref_sys', 'geometry_columns'
, 'geography_columns', 'raster_columns')
AND att.attnum > 0
AND NOT att.attisdropped 
ORDER BY att.attnum )
SELECT 'CREATE FOREIGN TABLE ' || $4 || TABLE_NAME || ' ('
|| string_agg(quote_ident(column_name) || ' ' || column_type 
, ', ' ORDER BY ordinal_position)
|| ') 
SERVER ' || quote_ident($1) || ' OPTIONS (schema_name ''' || quote_ident(table_schema) 
|| ''', table_name ''' || quote_ident(TABLE_NAME) || '''); ' AS RESULT 
FROM cols
GROUP BY table_schema, TABLE_NAME
$ LANGUAGE 'sql';
El cuál debemos invocar desde la base de datos ‘marca_mercedes_db’ para generar el script de todas las tablas (%):
\c marca_mercedes_db
SELECT script_foreign_tables('mercedes_server', 'public', '%', 'public.mercedes_');
Esto nos va a generar todos las sentencias CREATE FOREIGN TABLE…, el cuál ya podemos correr en nuestra base de datos integradora, marcas_db.
Y listo, usando estos scripts tenemos operando diferentes bases de datos, cada una con su dominio del problema, y podemos accederlos a todos, desde nuestra base de datos integradora.
\c marcas_db
SELECT * FROM mercedes_atributo_auto;
Espero que les sirva, nos vemos en el siguiente post.

Leave a Reply

Your email address will not be published. Required fields are marked *