This is a getting started tutorial for using dblink_fdw
. dblink is a PostgreSQL contrib extension that allows users to connect to other databases and to run arbitrary queries in them.
The FDW acronym at the end of dblink_fdw
comes from the words Foreign Data Wrapper which is a unified way for PostgreSQL to define remote FOREIGN SERVER
which to access. It allows to set the DB connection details like hostnames in a single
place and to create a USER MAPPING
for remote connections with the needed passwords only once.
dblink_fdw example
First off we create the needed extension and database as the avnadmin
user. Note that the database username and password only needs to be entered once using the CREATE USER MAPPING
statement.
$ psql "postgres://avnadmin:adminpassword@postgres.demoproject.aivencloud.com:11254/defaultdb?sslmode=require"
-- 'user1' will be able to create a dblink from 'defaultdb' to a new database 'db2'
defaultdb= > CREATE USER user1 PASSWORD 'secret1';
CREATE ROLE
-- 'user2' will be the owner of the new database 'db2'
defaultdb= > CREATE USER user2 PASSWORD 'secret2';
CREATE ROLE
-- we need to grant the 'user2' to the current user (avnadmin) so that it can give user2 privileges to the new database 'db2'
GRANT user2 TO CURRENT_USER;
GRANT ROLE
-- create the database that we will access over a dblink connection
defaultdb= > CREATE DATABASE db2 OWNER user2;
CREATE DATABASE
defaultdb= > CREATE EXTENSION dblink;
CREATE EXTENSION
defaultdb= > CREATE SERVER db2remote
FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (host 'postgres.demoproject.aivencloud.com',
dbname 'db2', port '11254');
CREATE SERVER
-- 'user1' will automatically authenticate as 'user2' with the remote 'db2' database when using the dblink connections
defaultdb= > CREATE USER MAPPING FOR user1
SERVER db2remote
OPTIONS (user 'user2', password 'secret2');
CREATE USER MAPPING
-- allow user1 to user the remote db2remote connection
defaultdb= > GRANT USAGE ON FOREIGN SERVER db2remote TO user1;
GRANT
Next, we log into the db2
database as its owner user2
both of which we created above. We will create a table with some data in it:
$ psql "postgres://user2:secret2@postgres.demoproject.aivencloud.com:11254/db2?sslmode=require"
db2= > CREATE TABLE foo (id int);
CREATE TABLE
db2= > INSERT INTO foo (id) VALUES (100), (200), (300);
INSERT 0 3
Now that we have data in db2
we can access it as the user1
user from the original defaultdb
database using a dblink:
$ psql "postgres://user1:secret1@postgres.demoproject.aivencloud.com:11254/defaultdb?sslmode=require"
-- establish a dblink connection to the remote database
defaultdb= > SELECT dblink_connect('myconn', 'db2remote');
dblink_connect
────────────────
OK
(1 row)
-- execute a test query using the dblink connection
defaultdb= > SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int);
a
─────
100
200
300
(3 rows)
The contents of the foo
table in the db2
database are returned correctly from the dblink query.