dblink

There are a few tools at a developers disposal to perform queries that go cross-database. In today’s post, I’ll quickly go over using dblink to establish links between Postgres databases.

Example Usage

First up, we need to make sure that the dblink extension is available to our server. CREATE EXTENSION is what we’ll use to do this:

CREATE EXTENSION dblink;

Prior to being able to query against a remote database, we need to use dblink_connect to establish a link from the local context.

-- create the crumbs link
select  dblink_connect(
    'remotedb',
    'host=127.0.0.1 port=5432 dbname=remotedb user=postgres password=password'
);

The connection string that you supply are fairly straight forward details to connect to a server with given credentials.

Using dblink, you can now invoke a query on the remote server and have the result mixed into your local code.

select  *
from    dblink('remotedb', 'SELECT "ID", "Name" FROM "People"')
as      people("ID" int4, "Name" character varying);

When you’re done with the connection, you use dblink_disconnect.

select dblink_disconnect('dbl-crumbs');  

Async Queries

dblink also gives you the opportunity to perform async queries which is really handy. You kick the query off, do something and then start fetching the results later on in your code.

/* start the query off */
select  *
from    dblink_send_query('remotedb', 'SELECT "ID", "Name" FROM "People"')
as      people;

/* Do some other work here */

/* start drawing the results */
select  *
from    dblink_get_result('remotedb')
as      people("ID" int4, "Name" character varying);

That’s a bit fancy.