How to query a table in one PostgreSQL database from another PostgreSQL database using dblink

You can query a table from a different PostgreSQL database using the dblink extension. 

The steps are as follows:

1. Install the deblink extension
2. Create the server connection
3. Query the data
4. Disconnect the server

You can use SELECT  on the foreign table.

Please note the the server connection with dblink only lasts for the current session.

Install the dblink extension

The query to install the dblink extension is:

CREATE EXTENSION dblink;

Create the server connection

The query to create the server connection is:

SELECT dblink_connect('tloustore', 'host=127.0.0.1 port=5432 dbname=tloustore user=elephant password=secret'); 

 The arguments:

• The dblink name is tloustore
• host '127.0.0.1' is the ip address of the foreign server
• port '5432' is the port of the foreign server
• dbname=tloustore is the database on the foreign in the foreign server
• user=elepahnt is the username for the foreign server
• password=secret is the password for the foreign server

Query the data

Do the following select query to query the foreign data:

SELECT * FROM dblink('tloustore',

'SELECT itmcode, description

FROM inventory;')

AS tlou_inventory(itmcode integer, description character varying);

The arguments:

• tloustore is the dblink connection name
• 'SELECT itmcode, description FROM inventory;' is the query on the foreign table
• AS tlou_inventory(itmcode integer, description character varying); is the alias on the local database

Disconnect the server

The query to disconnect the dblink server is:

SELECT dblink_disconnect('tloustore');

Example