Conexión y Consulta de Datos en Bases de Datos Remotas con Postgres
Resumen
¿Cómo conectar a bases de datos remotas usando dblink en PostgreSQL?
Conectar bases de datos remotas puede parecer una tarea complicada, pero PostgreSQL simplifica este proceso con su extensión dblink. Esta herramienta permite ejecutar consultas en bases de datos remotas directamente desde tu servidor local, otorgándote flexibilidad en la gestión y análisis de tus datos. Vamos a explorar paso a paso cómo puedes hacer uso efectivo de dblink para interactuar de manera eficiente con bases de datos en distintos servidores.
¿Cómo crear una base de datos remota simulada?
Para empezar, será necesario crear una base de datos que se comporte como si estuviera en un servidor remoto. Ejecuta los siguientes pasos:
Crea la base de datos: Inserta un ejemplo de base de datos llamada remota en tu servidor actual. El nombre es arbitrario, lo importante es la estructura.
Define la tabla:
La tabla usuarios_vip tendrá dos columnas importantes:
id: un entero que identificará al usuario.
fecha: la fecha desde la que el usuario es VIP.
Inserta datos de prueba, por ejemplo, el usuario con ID 50 ha sido VIP desde 2010. Esto permitirá simular cómo cruzar datos con información local.
¿Cómo verificar la instalación y uso de dblink?
Antes de correr consultas, verifica si la extensión dblink está instalada. Si no:
CREATE EXTENSION dblink;
Comprobada su existencia, ejecuta una consulta sencilla a la base de datos remota. La consulta debe incluir los siguientes parámetros:
Base de datos: remota
Puerto: 5432 (por defecto)
Host: 127.0.0.1 (IP del servidor, puede variar)
Usuario: Debe ser aquel que tiene permisos en la base de datos remota, por ejemplo, postgres.
Clave: Asegúrate de encapsular la contraseñas. Utiliza vistas para no revelar credenciales en consultas.
La consulta dblink podría lucir así:
SELECT*FROM dblink('dbname=remota hostaddr=127.0.0.1 user=postgres password=tu_contraseña','SELECT id, fecha FROM usuarios_vip')AS datos_remotos(id INTEGER, fecha DATE);
¿Cómo unir datos de bases de datos locales y remotas?
Una vez obtenida la información de la base de datos remota, puedes unirla con tus tablas locales:
Join entre tablas: Supongamos que quieres cruzar tus datos de pasajeros locales con los usuarios VIP de remota. La consulta SQL sería:
SELECT p.*FROM pasajeros p
JOINdblink('dbname=remota hostaddr=127.0.0.1 user=postgres password=tu_contraseña','SELECT id, fecha FROM usuarios_vip')AS datos_remotos(id INTEGER, fecha DATE)ON p.id = datos_remotos.id;
Usa USING para simplificar: Cuando los nombres de columna coinciden en ambas tablas, el uso de USING simplifica el JOIN.
SELECT p.*FROM pasajeros p
JOINdblink('dbname=remota hostaddr=127.0.0.1 user=postgres password=tu_contraseña','SELECT id, fecha FROM usuarios_vip')AS datos_remotos(id INTEGER, fecha DATE)USING(id);
Buenas prácticas al trabajar con dblink
Seguridad: No compartas contraseñas ni credenciales directas en tus consultas, utiliza vistas si es necesario para ocultar información sensible.
Permisos adecuados: Asegúrate de que el usuario cuenta con suficientes permisos para realizar las operaciones necesarias en las tablas remotas.
Optimización: Considera siempre el impacto en el rendimiento al realizar conexiones a bases remotas y cómo se pueden optimizar estas operaciones.
Aplicar estas estrategias fomentará un manejo de datos más seguro y eficiente. ¡Anímate a realizar consultas en direcciones contrarias, desde la base de datos remota hacia la local! Experimentar con estos conceptos mejorará tus habilidades en la gestión de datos con PostgreSQL.
Utilizando un right join trayendo solo aquellos usuarios que no son vip :D
Interesante, muy interesante.
Gracias por compartirlo. :D
excelente
OMG! Lo tiene todo
Es super completa esa BD
Simulando una conexión a Bases de Datos remotas
Obtener datos de hosts remotos
dblink
Primero debe verificarse que dblink esta instalado ya que es una extension de postgres y no viene por defecto
Crear la extension dblink que postgres incluye pero no instala por defecto, esta extension debe crearse por cada base de datos individual
CREATE EXTENSION dblink;
-- se deberian dejar espacio despues de cada instruccion ya que son comandos diferentesSELECT*FROMdblink('dbname=remota
port=5432
host=127.0.0.1
user=xxxx
password=xxxx','SELECT id, fecha FROM vip;')AS datos_remotos(id integer,"date"date);
A la query se le debe dar el formato de como llegaran los datos
Haciendo cruce de datos con una tabla local
SELECT*FROM passenger
JOINdblink('dbname=remota
port=5432
host=127.0.0.1
user=xxxx
password=xxxxxxx','SELECT id, date FROM vip;')AS datos_remotos(id integer,"date"date)ON(passenger.id = datos_remotos.id);
Ya que ambas tablas se cruzan por la columna id, el JOIN se puede hacer con USING(id)
SELECT*FROM passenger
JOINdblink('dbname=remota
port=5432
host=127.0.0.1
user=xxxx
password=xxxxxxx','SELECT id, date FROM vip;')AS datos_remotos(id integer,"date"date)USING(id);
Al usar usign id el query retorna solo una columna de id en lugar de ambas columnas lo cual haria si se usara un ON para realizar el JOIN
Reto
query a la tabla de pasageros desde la tabla remota
No porque exista se deba usar. Hay que pensar el en performance antes de quere usar dblink. Nunca lo eh usado pero lo primero que se me viene a la mente es lo malo que podria hacer un JOIN across servers. Esto podria degradar el rendimiento tremendamente.
De que otra manera puede hacerse? 0:
Odilia nació en 2018 pero empezó a ser vip desde 2010. Esas subscripciones vip se heredan de generación en generación.
Si le sale el error de:
ERROR: could not establish connection
DETAIL: falló la conexión al servidor en «127.0.0.1», puerto 5432:FATAL: password authentication failed for user "usuario_consulta"falló la conexión al servidor en «127.0.0.1», puerto 5432:FATAL: password authentication failed for user "usuario_consulta"SQLstate:08001
Lo que deben hacer es confirmar que la contraseña este correcta, o sino pues cambiarla y con eso deberia funcionar, igual ver que el usuario esta bien escrito.
Buen dato, suele pasar. Es el error más común.
Hola, y como congfirmo usuario y contraseña?
Saludos
Aquí dejo el reto haciendolo al revés
SELECT*FROM vip
JOINdblink('dbname=transporte
port=5432 host=127.0.0.1 user=usuario_consulta
password=jose123','SELECT id,nombre,direccion_residencia,fecha_nacimiento FROM pasajero')ASdatos_remotos(id integer,nombre character varying,direccion_residencia character varying,fecha_nacimiento date)USING(id)
Simulando una conexión a Bases de Datos remotas
dblink es una extensión de PostgreSQL que permite conectarse y acceder a otras bases de datos PostgreSQL en el mismo servidor o en un servidor remoto. Aquí hay un ejemplo de cómo usar dblink para conectarse a una base de datos remota en localhost:
Instale la extensión dblink:
CREATEEXTENSION dblink;
Conectarse a la base de datos remota:
SELECT*FROMdblink('host=localhost port=5432 dbname=nombre_base_de_datos','SELECT nombre, direccion FROM tabla')ASt1(nombre character varying, direccion character varying);
Nota: Asegúrese de reemplazar "nombre_base_de_datos" y "tabla" con los nombres reales de su base de datos y tabla respectivamente.
Información resumida de esta clase
#EstudiantesDePlatzi
dblink es una función que me permite conectarme a servidores remotos para generar consultas
Al utilizar dblink debo especificar los parámetros de conexión a esta base de datos
Puedo usar USING si los datos que vamos a comparar son los mismos
Allí creé una tabla llamada devices y guardé un par de datos.
Y finalmente realicé la configuración para la conexión desde mi cliente local usando pgadmin:
SELECT*FROM pasajero AS pa
JOINdblink('dbname=postgresdb
port=5433 host=127.0.0.1 user=postgres
password= password','SELECT id, local_date_time, status FROM devices')ASdatos_remotos(id integer, fecha timestamp without time zone, status character varying(255))USING(id);
La conexión desde el contenedor docker de postgres hasta mi maquina local seria:
Se debe poner el nombre del servicio en vez de localhost o 127.0.0.1
SELECT*FROM devices AS de
JOINdblink('dbname=transporte
port=5432 host=host.docker.internal user=postgres
password=postgres','SELECT id, nombre, direccion_residencia, fecha_nacimiento FROM pasajero')ASdatos_remotos(id integer, nombre character varying(100), direccion_residencia character varying, fecha_nacimiento date)USING(id);
Yo estoy usando docker para levantar postgres, ambas bases de datos estan en este contenedor, al momento de crear la extensión me dice que no encuentra el archivo, entré a la ruta y en efecto no esta el archivo
dblink.control
alguna forma que me recomienden para solucionar este problema
ERROR: could not open extension control file "/usr/share/postgresql/14/extension/dblikn.control":No such file or directory
Mi versión de postgres es
PostgreSQL14.1(Debian14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc(Debian10.2.1-6)10.2.120210110,64-bit
Mi aporte, datos basicos para usar dblink
Este debe estar activado en postgres, si no lo esta debe hacerse con
CREATEEXTENSION dblink;
Una simple consulta en la tabla remota
select *fromdblink('dbname=RemoteDB port=5432<-- puerto remoto
host=X.x.x.x<--IP remota
user= postgres <-- usuario
password= postgres',<-- password, aguas, debe estar encapsulado
'SELECT id FROM usuarios_vip')<-- la consulta
ASdatos_remotos(id integer);<-- los datos deben ser expresados como otra tabla y de que tipo son
El código del reto:
CREATEEXTENSION dblink;SELECT*FROM vip
JOINdblink('dbname=transport
port=5432 host=127.0.0.1 user=usuario_consulta
password=etc123','SELECT id, name, address, age, FROM passengers')ASremote_data(id integer, name character varying, address character varying, age date)USING(id);
definitivamente es imposible seguirle el paso al profesor, en cada paso se equivoca o le sale un error.
Son errores intencionales.
Me surgió el siguiente error al tratar de conectarme a la base de datos remota, alguna idea de cómo solucionarlo:
El usuario existe?, la clave corresponde al usuario? el usuario si está asignado a la base de datos a la que intentas conectarte?
tengo el mismo error pero en detalle dice: al rol usuario_consulta no se le permite conectarse
authentication failed
¿Es posible guardar un dblink como un objeto?
Por ejemplo, en Oracle, con la sentencia CREATE DBLINK puedo generar un objeto con los parámetros de conexión, lo que me permite llamar a una tabla remota de la siguiente forma:
SELECT a.campo1, b.campo2FROM tabla_local AS a
JOIN tabla_remota@NOMBRE_DBLINKas t2
WHERE a.id=b.id
De esta forma ahorramos mucha sintaxis y evitamos que la pass del usuario esté en la query... gracias!
Tengo una pregunta sobre este punto. Actualmente yo necesito hacer conexiones de este tipo en mi trabajo, pero me lo impiden porque me dijeron que podría ocasionar problemas de seguridad de la información. El uso de dblinks puede originar problemas en la seguridad de la información?
al hacer exactamente igual me aparece error de sintasys pero ya e echo todo y he revisado casa cosa y no me han dejado
<code>
SELECT * FROM
dblink('dbname=remota
port=5432
host=127.0.0.1
user=usuario_consulta
password=Rionegro1*,
'SELECT id, fecha FROM vip;')
AS datos_remotos(id integer, "date" date);
Hola Tienes un error en: => vip;') <= el ; va afuera del paréntesis para indicar que cerro la lineal, lo ideal seria
=> vip');
Al ejecutar el siguiente comando
SELECT*FROMdblink('dbname=remota
port=5432 host=127.0.0.1 user=usuario_consulta
password=etc123,SELECT id, fecha FROM vip')ASDATOS_REMOTOS(id INTEGER, fecha DATE);
Me arroja el siguiente error:
ERROR: connection not available
SQL state: 08003
A ustedes les ha ocurrido?
Me ocurrió exactamente el mismo error, pero se me "solucionó" al corregir la contraseña del usuario, abrir y cerrar el PgAdmin. Y no sé si también fué que dejé abierto el servidor principal.
a mi me aparecio por que no instale dblink en la DB 'transporte', la instale tambien alli y se resolvio. otro detalles es asegurarse de dar los permisos al usuario consulta,
