dblink

connect to other PostgreSQL databases from within a database

PostgreSQL

CONTRIB

dblink : connect to other PostgreSQL databases from within a database

Overview

ID	Extension	Package	Version	Category	License	Language
8970	dblink	dblink	`1.2`	FDW	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-d--	No	Yes	No	Yes	no	no

Relationships
Need By	emaj mimeo omni_schema omni_test omni_vfs pg_jobmon pg_profile pgbouncer_fdw pgelog
See Also	postgres_fdw db_migrator pgspider_ext pglogical citus pgclone plproxy mimeo mysql_fdw db2_fdw

Packages

PG18	PG17	PG16	PG15	PG14
1.2	1.2	1.2	1.2	1.2

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

CREATE EXTENSION dblink;

Usage

dblink: Connect to other PostgreSQL databases from within a database

Connect to a Remote Database

CREATE EXTENSION dblink;

-- Unnamed connection (only one allowed)
SELECT dblink_connect('dbname=remotedb host=remotehost options=-csearch_path=');

-- Named connection (multiple allowed)
SELECT dblink_connect('myconn', 'dbname=remotedb host=remotehost');

Or connect via a foreign server definition:

CREATE SERVER remote_srv FOREIGN DATA WRAPPER dblink_fdw
  OPTIONS (hostaddr '192.168.1.10', dbname 'remotedb');
CREATE USER MAPPING FOR local_user SERVER remote_srv
  OPTIONS (user 'remote_user', password 'secret');

SELECT dblink_connect('myconn', 'remote_srv');

Query a Remote Database

-- Ad-hoc connection
SELECT * FROM dblink(
  'dbname=remotedb host=remotehost',
  'SELECT id, name, value FROM remote_table'
) AS t(id int, name text, value numeric);

-- Using a named connection
SELECT * FROM dblink(
  'myconn',
  'SELECT id, name FROM remote_table WHERE status = 1'
) AS t(id int, name text);

You must always specify the column definition list in the AS clause.

Execute Commands (No Result Set)

-- INSERT, UPDATE, DELETE, DDL on the remote database
SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''test'', 42)');
SELECT dblink_exec('myconn', 'UPDATE remote_table SET value = 100 WHERE id = 1');
SELECT dblink_exec('myconn', 'DELETE FROM remote_table WHERE id = 1');

Returns the command status string (e.g., INSERT 0 1).

Cursor-Based Access

SELECT dblink_open('myconn', 'mycursor', 'SELECT * FROM large_table');
SELECT * FROM dblink_fetch('myconn', 'mycursor', 100) AS t(id int, data text);
SELECT dblink_close('myconn', 'mycursor');

Connection Management

SELECT dblink_get_connections();    -- List open named connections
SELECT dblink_disconnect('myconn'); -- Close a named connection

Create a View for Convenience

CREATE VIEW remote_data AS
  SELECT * FROM dblink(
    'dbname=remotedb host=remotehost',
    'SELECT id, name, value FROM data_table'
  ) AS t(id int, name text, value numeric);

SELECT * FROM remote_data WHERE value > 100;

Key Functions

Function	Description
`dblink_connect(connstr)`	Open an unnamed persistent connection
`dblink_connect(name, connstr)`	Open a named persistent connection
`dblink_disconnect(name)`	Close a named connection
`dblink(connstr, sql)`	Execute a query, return rows
`dblink_exec(connstr, sql)`	Execute a command, return status
`dblink_open(name, cursor, sql)`	Open a cursor on a remote database
`dblink_fetch(name, cursor, count)`	Fetch rows from a remote cursor
`dblink_close(name, cursor)`	Close a remote cursor
`dblink_get_connections()`	List all open named connections