Use the PostgreSQL® dblink extension
dblink
is a PostgreSQL® extension that allows you to connect to other PostgreSQL databases and to run arbitrary queries.
With Foreign Data Wrappers (FDW) you can uniquely define a remote foreign server in order to access its data. The database connection details like hostnames are kept in a single place, and you only need to create once a user mapping storing remote connections credentials.
Prerequisites
To create a dblink
foreign data wrapper you need the following
information about the PostgreSQL remote server:
TARGET_PG_HOST
: The remote database hostnameTARGET_PG_PORT
: The remote database portTARGET_PG_USER
: The remote database user to connectTARGET_PG_PASSWORD
: The remote database password for theTARGET_PG_USER
TARGET_PG_DATABASE_NAME
: The remote database name
If you're using Aiven for PostgreSQL as remote server, the above
details are available in the Aiven console > the service's
Overview page or via the avn service get
command with
the Aiven CLI.
Enable dblink
extension on Aiven for PostgreSQL
To enable the dblink
extension on an Aiven for PostgreSQL service:
-
Connect to the database with the
avnadmin
user. The following shows how to do it withpsql
, the service URI can be found in the Aiven console the service's Overview page:psql "postgres://avnadmin:[AVNADMIN_PWD]@[PG_HOST]:[PG_PORT]/[PG_DB_NAME]?sslmode=require"
tipIf you're using Aiven for PostgreSQL as remote server, you can connect to a service with the
avnadmin
user with theavn service cli
command with the Aiven CLI. -
Create the
dblink
extension:CREATE EXTENSION dblink;
Create a foreign data wrapper using dblink_fdw
To create a foreign data wrapper using the dblink_fwd
:
-
Connect to the database with the
avnadmin
user. The following shows how to do it withpsql
, the service URI can be found in the Aiven console the service's Overview page:psql "postgres://avnadmin:[AVNADMIN_PWD]@[PG_HOST]:[PG_PORT]/[PG_DB_NAME]?sslmode=require"
-
Create a user
user1
that will access thedblink
:CREATE USER user1 PASSWORD 'secret1'
-
Create a remote server definition, named
pg_remote
, usingdblink_fdw
and the target PostgreSQL connection details:CREATE SERVER pg_remote
FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (
host 'TARGET_PG_HOST',
dbname 'TARGET_PG_DATABASE_NAME',
port 'TARGET_PG_PORT'
); -
Create a user mapping for the
user1
to automatically authenticate as theTARGET_PG_USER
when using thedblink
:CREATE USER MAPPING FOR user1
SERVER pg_remote
OPTIONS (
user 'TARGET_PG_USER',
password 'TARGET_PG_PASSWORD'
); -
Enable
user1
to use the remote PostgreSQL connectionpg_remote
:GRANT USAGE ON FOREIGN SERVER pg_remote TO user1;
Query data using a foreign data wrapper
To query a foreign data wrapper you must be a database user having the
necessary grants to the remote server definition. We can use user1
from the previous example. To query the remote table inventory
defined
in the target PostgreSQL database pointed by the pg_remote
server
definition:
-
Connect with the Aiven for PostgreSQL service with the database user (
user1
) having the necessary grants to the remote server definition. -
Establish the
dblink
connection to the remote target:SELECT dblink_connect('my_new_conn', 'pg_remote');
-
Execute the query passing the foreign server definition as parameter:
SELECT * FROM dblink('pg_remote','SELECT item_id FROM inventory')
AS target_inventory(target_item_id int); -
Check the results:
target_item_id
----------------
1
2
3
(3 rows)