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.

--

--

--

Software Engineer living in Southwest Western Australia

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Step Indicator in SwiftUI

Distributed caching system design

Liskov Substitution Principle in C#

The Self-Organizing Team’s Guide to Micromanagers

WeSendit Airdrop: The biggest Giveaway

WeSendit Airdrop: The biggest Giveaway

Kali Linux Desktop on Windows Subsystem for Linux

5 Simple Steps to build a Full Stack Spring Boot Web App from scratch

A Recipe for Refactoring a Legacy Spring Application Codebase

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Joel Malone

Joel Malone

Software Engineer living in Southwest Western Australia

More from Medium

Postgres replica conflicts: Part 2

IBM Message Gateway monitoring with Instana

Webpack, Azure Pipelines & Caching — build your Frontend in seconds

Configure private NPM package registry using Yarn 2 and Gitlab CI/CD

Photo by <a href=”https://unsplash.com/@markusspiske?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Markus Spiske</a> on <a href=”https://unsplash.com/s/photos/coding?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Unsplash</a>