S

Supabase•4y ago

Prismism

Connect separate projects with dblink

Hi, I am trying to use the dblinkdblink extension to open a persistent connection between two of my projects.
Here are the two references I have used so far:
https://www.postgresql.org/docs/current/contrib-dblink-connect.html
https://medium.com/@joelmalone/remote-database-queries-in-postgres-2ec2addda7f6
and based off of those, I here are some things I have tried:

• SELECT dblink_connect('test_connection', 'hostaddr=db.xxxxxxxxxxxxxxxxxxxx.supabase.co port=5432 dbname=postgres user=postgres password=x')SELECT dblink_connect('test_connection', 'hostaddr=db.xxxxxxxxxxxxxxxxxxxx.supabase.co port=5432 dbname=postgres user=postgres password=x')
• SELECT dblink_connect('test_connection', 'host=db.xxxxxxxxxxxxxxxxxxxx.supabase.co port=5432 dbname=postgres user=postgres password=x')SELECT dblink_connect('test_connection', 'host=db.xxxxxxxxxxxxxxxxxxxx.supabase.co port=5432 dbname=postgres user=postgres password=x')
• SELECT dblink_connect('test_connection', 'hostaddr=db.xxxxxxxxxxxxxxxxxxxx.supabase.co port=5432 dbname=postgres')SELECT dblink_connect('test_connection', 'hostaddr=db.xxxxxxxxxxxxxxxxxxxx.supabase.co port=5432 dbname=postgres')
• SELECT dblink_connect('test_connection', 'host=db.xxxxxxxxxxxxxxxxxxxx.supabase.co port=5432 dbname=postgres')SELECT dblink_connect('test_connection', 'host=db.xxxxxxxxxxxxxxxxxxxx.supabase.co port=5432 dbname=postgres')
  In each case, I get Failed to run sql query: could not establish connectionFailed to run sql query: could not establish connection
  I do get a success result if I do SELECT dblink_connect('test_connection', 'dbname=postgres')SELECT dblink_connect('test_connection', 'dbname=postgres') which I presume is just connecting to itself which is obviously not what I want, but figured maybe this helps troubleshoot.

I am pulling each of the values in the connection strings from the settings/databasesettings/database in the other project, and I reset the database password to make sure it matches.

PostgreSQL Documentation

dblink_connect

dblink_connect dblink_connect — opens a persistent connection to a remote database Synopsis dblink_connect(text connstr) returns text dblink_connect(text connname, text connstr) …

Medium

Remote database queries in Postgres

Learn how to fire off SQL statements against a remote Postgres database.