Remote database queries in Postgres

Photo by Migle Siauciulyte on Unsplash

In Postgres, you can fire off SQL statements against a remote database. This can be handy to copy data between servers, or perform cross-database queries.

First, you’ll need to enable the dblink extension:

create extension if not exists dblink;

Next, we can open a persistent named connection to the remote database by calling dblink_connect() (ref) and specifying the connection’s parameters, like so:

select dblink_connect(‘dev1’, ‘host=dev1 user=myUser password=myPassword dbname=myDb’);

Which returns a typically boring result, but let’s us know everything is OK:

Before we can fire off a query against the remote table, be aware that we need to tell our local connection the schema of the data being returned. We do this using the alias part of the from clause, in a format similar to the columns of a create statement.

With this in mind, a complete query to a remote table might look like this:

select *
from dblink(‘dev1’, ‘select * from asset_type’)
as remote_asset_type (
 id numeric(10,0),
 code character varying(50),
 parent_id numeric(10,0),
 description character varying(50),
 image_id character(36),
);

Let’s break that down.

select *

From the overall results, we are selecting all columns. Nothing special here.

from dblink(‘dev1’, ‘select * from asset_type’)

Here, we are calling dblink(), specifying the persistent connection’s name as the first parameter, and the SQL statement to execute on the remote server as the second parameter. We use the result as the row source of our from clause.

as remote_asset_type (
 id numeric(10,0),
 code character varying(50),
 parent_id numeric(10,0),
 description character varying(50),
 image_id character(36),
);

Finally, we define the schema of the rows received from the remote database.

Once we’re done, we can disconnect the persistent connection like this:

select dblink_disconnect(‘dev1’);

If you’ve forgotten which connections you have open, or to help you clean up after yourself, you can query the database for a list of connections by calling dblink_get_connections() (ref):

select dblink_get_connections();

Which returns something like this:

The persistent connections returned by dblink_get_connections()

For more details on dblink, see the docs here.