5/5/2020 Postgres BD Distribuidas

Bases de datos distribuidas en PostgresSQL

Una base de datos distribuida (BDD) es un conjunto de múltiples bases de datos lógicamente relacionadas las cuales se encuentran distribuidas en diferentes espacios
lógicos (pej. un servidor corriendo 2 máquinas virtuales) e interconectados por una red de comunicaciones.

Dichas BDD tienen la capacidad de realizar procesamiento autónomo, esto permite realizar operaciones locales o distribuidas.

Un sistema de Bases de Datos Distribuida (SBDD) es un sistema en el cual múltiples sitios de bases de datos están ligados por un sistema de comunicaciones de tal forma
que, un usuario en cualquier sitio puede acceder los datos en cualquier parte de la red exactamente como si estos fueran accedidos de forma local.

Un sistema distribuido de bases de datos se almacenan en varias computadoras. Los principales factores que distinguen un SBDD de un sistema centralizado son los
siguientes:

Hay múltiples computadores, llamados sitios o nodos.

Estos sitios deben de estar comunicados por medio de algún tipo de red de comunicaciones para transmitir datos y órdenes entre los sitios.

www.v-espino.com/~chema/daw1/tutoriales/postgres/distribuidas.htm 1/6
5/5/2020 Postgres BD Distribuidas

www.datos.misiones.gov.ar

Desde cualquier BD se pueden recuperar datos de cualquier otra base de datos PostgresSQL (Naturalmente, es necesario identificarse con perfil de un usuario con acceso a
los mismos) usando dblink. DBLink son funciones preparadas por los autores de postgres que añaden la funcionalidad de acceso a datos de forma remota.

No se trata de conectarse remotamente con un servidor de bases de datos, sino acceder desde una base da datos a elementos de otra base de datos a través de una linea
de comunicaciones, combinando ambas

Instalar las funciones de dblink

Es un paso que hay que ejecutar SOLO UNA VEZ y que permite instalar las funciones necesarias para proceder a la comunicación con bases de datos remotas.

En la base de datos desde la que pretendemos conectarnos en remoto se ejecuta el SQL que creará las funciones de dblink. El script está en:

C:\Program Files\PostgreSQL\8.4\share\contrib\dblink.sql

Para las versiones 9.1 y posteriores, para generar las funciones de dblink basta ejecutar en SQL

CREATE EXTENSION dblink;

dblink1

www.v-espino.com/~chema/daw1/tutoriales/postgres/distribuidas.htm 2/6
5/5/2020 Postgres BD Distribuidas

Ejecutar comandos de SQL

Comandos del tipo SELECT:

Los campos de las tablas del nodo remoto se “traen” al nodo local con el predicado FROM añadiendo los parámetros de conexión proporcionados por dblink

SELECT lista_de_campos
FROM dblink('dbname=baseDatos port=puerto host=ordenadorRemoto user=usuario password=contraseña','SQL') AS Alias(campo1 tipo1,campo2 tipo2,…)
WHERE condiciones
ORDER BY criterios_de_ordenacion;

Es necesario indicar en el Alias qué tipo de campos vienen desde el ordenador remoto, pues se desconoce. Este extremo se define en AS …

dblink2

Comandos de mantenimiento de tablas (INSERT/DELETE/UPDATE):

En este caso se realiza mediante la ejecución de función dblink_exec(conexion,sentencia) Por ejemplo, si queremos insertar valores en la tabla gente (codigo int PK, nombre
varchar, sueldo double):
select dblink_exec('dbname=remota port=5432 host=yoquese.com user=postgres password=contrasena',
'INSERT INTO gente (codigo,nombre,sueldo) VALUES (33,\'Pepito\',1234.45));

Nota: \' sirve para incluir el carácter especial de comilla simple en la sentencia a ejecutar

Ejercicio 1.- Mostrar datos de otra base de datos residente en el mismo equipo:

SELECT p.nombre
FROM dblink('dbname=futbol user=postgres password=root','SELECT nombre FROM equipos') AS p(nombre varchar(100));

Ejercicio 2.- Mostrar los nombres de los empleados que comienzan por E en una tabla llamada templa de la base de datos empleado que
está en el servidor del alumno 'pepito' (IP=192.168.32.44):

SELECT p.nombre,p.apellido
FROM dblink('dbname=empleado
port=5432
host=192.168.32.44
user=postgres
password=root',
'SELECT nombre,apellido FROM templa')
AS p(nombre varchar(100),apellido varchar(100))
WHERE p.nombre LIKE 'E%';

Ejercicio 3.- Listado de apellidos y departamentos donde trabajan los empleados de la misma base de datos anterior. En este caso las 2
tablas son remotas.
SELECT a.b,d.f
FROM
dblink( 'dbname=ejemplo1 port=5432 host=192.168.32.44 user=postgres password=root' ,
'SELECT apellido,dept FROM templa') AS a(b varchar(100),c varchar(100)) ,
dblink( 'dbname=ejemplo1 port=5432 host=192.168.32.44 user=postgres password=root',
'SELECT numdep,nomdep FROM tdepta') AS d(e varchar(100),f varchar(100))
WHERE a.c=d.e;

Ejercicio 4.- Mezcla de datos remotos y locales. Esta vez se hará en varios pasos

a) Conexión con la base de datos remota

SELECT dblink_connect('dbname=empleado port=5432 host=192.168.32.44 user=postgres password=root');

b) Sentencia SQL (mezclo una tabla remota con una local)

SELECT r.nombre,a.texto
FROM dblink('SELECT nuempl,nombre FROM templa') AS r(nuempl varchar,nombre varchar),
tabla_local AS a
WHERE r.nuempl=a.codigo;

c) Cierre de la conexión remota

www.v-espino.com/~chema/daw1/tutoriales/postgres/distribuidas.htm 3/6
5/5/2020 Postgres BD Distribuidas
SELECT dblink_disconnect();

Ejercicio 5.- Realización de una copia de una tabla remota (o parte de ella)
SELECT dblink_connect('dbname=empleado port=5432 host=192.168.32.44 user=postgres password=root');
SELECT * into copia FROM dblink('SELECT nuempl,nombre FROM templa') AS r(nuempl varchar,nombre varchar);
SELECT dblink_disconnect();

Problemas a tener en cuenta:

Desaparece la clave primaria
Se debería alterar la tabla para crearla
Es necesario conocer los tipos de los campos que se copian

Práctica de acceso a bases de datos distribuidas:

Se va a realizar una simulación del valor de una cartera de valores. Para realizarla será necesario que 4 alumnos creen en sus equipos (192.168.32.1 a 192.168.32.4) las
siguientes bases de datos, tablas, registros y campos, es decir, la información se haya distribuida en 4 bases de datos remotas:

Equipo Base de datos Tabla Contenido

192.168.32.1 ibex35 cotizaciones

192.168.32.2 ftse quote

192.168.32.3 dax30 zitat

quote

192.168.32.4 downjones

change

En cada ordenador del aula disponemos de una base de datos llamada 'mipasata' con una tabla llamada 'cartera' con nuestra cartera de valores como la siguiente

Realizar una función llamada cuanto() a la que se le pase un tic y un mercado y devuelva el valor correspondiente.
Ejemplo cuanto(‘BBVA’,’ibex35’) devolverá 5.555
Realizar una función llamada que() a la que se pasa un tic y un mercado y devuelve la moneda en la que se cotiza.
Ejemplo que(‘BBVA’,’ibex35’) devolverá ’E’
Realizar una función llamada cambio() a la que se le pasa dos codigos de monedas y devuelve el cambio correspondiente.
Ejemplo cambio(‘E’,‘D’) devolverá 1.3344
Con las funciones anteriores, realizar un listado del valor de nuestra cartera de valores

Solución.:
Todas las funciones reciben 2 parámetros llamados a y b

cuanto(varchar,varchar) que(varchar,varchar) cambio(char,char)

declare declare declare
respuesta double precision; respuesta character; respuesta double precision;
basedatos varchar(100); basedatos varchar(100); begin
puerto varchar(4); puerto varchar(4); if(a=b) then
ordenador varchar(100); ordenador varchar(100); return 1;
usuario varchar(100); usuario varchar(100); else
contrasena varchar(100); contrasena varchar(100); select into respuesta p.r3
tabla varchar(100); tabla varchar(100); from dblink(' dbname=downjones
begin begin port=5432
if (b='ibex35') then if (b='ibex35') then host=192.168.32.4
basedatos='ibex35'; basedatos='ibex35'; user=postgres
puerto='5432'; puerto='5432'; password=root' ,
www.v-espino.com/~chema/daw1/tutoriales/postgres/distribuidas.htm 4/6
5/5/2020 Postgres BD Distribuidas
ordenador='192.168.32.1'; ordenador='192.168.32.1'; 'select * from change')
usuario='postgres'; usuario='postgres'; AS p(r1 varchar,r2 varchar,r3 double precision)
contrasena='root'; contrasena='root'; where p.r1=a
tabla='cotizaciones'; tabla='cotizaciones'; and p.r2=b;
end if; end if; if(respuesta>0) then
if (b='ftse') then if (b='ftse') then return respuesta;
basedatos='ftse'; basedatos='ftse'; else
puerto='5432'; puerto='5432'; select into respuesta p.r3
ordenador='192.168.32.2'; ordenador='192.168.32.2'; from dblink(' dbname=downjones
usuario='postgres'; usuario='postgres'; port=5432
contrasena='root'; contrasena='root'; host=192.168.32.44
tabla='quote'; tabla='quote'; user=postgres
end if; end if; password=root' ,
if (b='dax30') then if (b='dax30') then 'select * from change')
basedatos='dax30'; basedatos='dax30'; AS p(r1 varchar,r2 varchar,r3 double
puerto='5432'; puerto='5432'; precision)
ordenador='192.168.32.3'; ordenador='192.168.32.3'; where p.r1=b
usuario='postgres'; usuario='postgres'; and p.r2=a;
contrasena='root'; contrasena='root'; return (1/respuesta);
tabla='zitat'; tabla='zitat'; end if;
end if; end if; end if;
if (b='downjones') then if (b='downjones') then end;
basedatos='downjones'; basedatos='downjones';
puerto='5432'; puerto='5432';
ordenador='192.168.32.4'; ordenador='192.168.32.4';
usuario='postgres'; usuario='postgres';
contrasena='root'; contrasena='root';
tabla='quote'; tabla='quote';
end if; end if;
select into respuesta p.valor select into respuesta p.moneda
from dblink('dbname=' || basedatos || from dblink(' dbname=' || basedatos ||
' port=' || puerto || ' port=' || puerto ||
' host=' || ordenador || ' host=' || ordenador ||
' user=' || usuario || ' user=' || usuario ||
' password=' || contrasena , ' password=' || contrasena ,
'select * from ' || tabla) 'select * from ' || tabla)
AS p(tic varchar(10),valor double AS p(tic varchar(10),valor double
precision,moneda varchar) precision,moneda varchar)
where p.tic=a; where p.tic=a;
return respuesta; return respuesta;
end; end;

Para realizar un listado de la cartera de valores haremos la consulta:

select cantidad, tic, mercado, cuanto(tic,mercado), que(tic,mercado), cambio(que(tic,mercado),'E')*cantidad*cuanto(tic,mercado)

from cartera;

que dará como como resultado:

Ejercicio de bases de datos distribuidas:

El ordenador remoto lamerced.no-ip.org dispone un servidor postgreSQL con una base de datos llamada staples. Se ha creado un usuario llamado 'vendedor' con la
contraseña 'venta' que puede acceder a las siguientes vistas

entradas_partido con 3 campos:

fila (entero): fila del estadio
asiento (entero): asiento de la fila correspondiente
ocupado (bolean): si pone false indica que está disponible para la venta
precios_partido con 2 campos:
fila (entero): fila del estadio
precio (double precision): importe de una entrada de esa fila

1.- Realizar una consulta sql que permita obtener las entradas disponibles y el precio de las mismas

select *
from
dblink('dbname=staples port=5432 host=lamerced.no-ip.org user=vendedor password=venta','select * from entradas_partido') AS e(fila integer,asiento integer,ocupado boolean),
dblink('dbname=staples port=5432 host=lamerced.no-ip.org user=vendedor password=venta','select * from precios_partido') AS p(fila integer,importe double precision)
where
e.fila=p.fila
and e.ocupado=false;

2.- Crear en nuestro servidor local una tabla llamada comisiones con los siguientes campos:

Tabla: comisiones

www.v-espino.com/~chema/daw1/tutoriales/postgres/distribuidas.htm 5/6
5/5/2020 Postgres BD Distribuidas
fila (entero): fila de la que sacamos la entrada
comision (double precision): comisión que nos llevamos por la venta

En esta tabla pondremos que la comisión por vender una entrada de la fila 1 es el 20% (0.2) y para la fila 2 nos llevamos el 10% (0.1)

3.- Realizar una función llamada vendiendo en nuestro servidor con las siguientes características:

La función se llamará vendiendo.

Se le pasa como parámetro una fila y un asiento.
Si está disponible:
La debe vender (cambia el valor de ocupado a true)
Calcula el importe a cobrar (su precio incrementado en el porcentaje correspondiente)
Devuelve el mensaje: ‘VENTA EFECTUADA. Fila X. Asiento Y. Importe: Z’
Si no está disponible:
Devuelve el mensaje ‘ENTRADA NO DISPONIBLE’

declare
r boolean;
pr double precision;
sentencia character varying;
nada character varying;
begin

--Veamos si la entrada está disponible

select into r e.ocupado
from dblink('dbname=staples port=5432 host=lamerced.no-ip.org user=vendedor password=venta','select * from entradas_partido') AS e(fila integer,asiento integer,ocupado
boolean)
where e.ocupado=false
and e.fila=f
and e.asiento=a;

--Dependiendo de la instruccion anterior r puede ser false (disponible, true (ya vendida) o null (no existe esa entrada)

if(r=false) then
--Calculamos el importe de la entrada
select into pr p.importe*(1+c.comision)
from dblink('dbname=staples port=5432 host=lamerced.no-ip.org user=vendedor password=venta','select * from precios_partido') AS p(fila integer,importe double
precision),
comisiones c
where c.fila=f
and p.fila=f;

--Cambiamos ocupado de false a true

sentencia='update entradas_partido set ocupado=true where fila='||f||' and asiento='||a;
select into nada dblink_exec('dbname=staples port=5432 host=lamerced.no-ip.org user=vendedor password=venta',sentencia);

--Devolvemos el mensaje de la venta

return ('VENTA EFECTUADA. Fila: '||f||'. Asiento: '||a||'. Importe: '||pr);
else
--Esa entrada o no existe o no esta disponible
return('ENTRADA NO DSIPONIBLE');
end if;
end;

www.v-espino.com/~chema/daw1/tutoriales/postgres/distribuidas.htm 6/6