The dblink
PostgreSQL extension
allows you to connect to other PostgreSQL databases and to run arbitrary queries.
You can use foreign data wrappers (FDWs)
to define a remote foreign server
to access its data. The
database connection details such as hostnames are kept in a single place, and you
only need to create a user mapping
to store remote connections credentials.
To create a dblink
foreign data wrapper you need this information
about the PostgreSQL remote server:
HOSTNAME
: The remote database hostnamePORT
: The remote database portUSER
: The remote database user to connect. The default user istsdbadmin
.PASSWORD
: The remote database password for theUSER
DATABASE_NAME
: The remote database name. The default database name isdefaultdb
.
note
The details you need are available in the MST portal service Overview
tab. Alternatively, you can use the avn service get
command in the Aiven client.
To enable the dblink
extension on an MST PostgreSQL service:
Connect to the database as the
tsdbadmin
user:psql -x "postgres://tsdbadmin:<PASSWORD>@<HOSTNAME>:<PORT>/defaultdb?sslmode=require"Create the
dblink
extensionCREATE EXTENSION dblink;Create a table named
inventory
:CREATE TABLE inventory (id int);Insert data into the
inventory
table:INSERT INTO inventory (id) VALUES (100), (200), (300);
Create a user
user1
who can access thedblink
CREATE USER user1 PASSWORD 'secret1'Create a remote server definition named
mst_remote
, usingdblink_fdw
and the connection details of the MST service.CREATE SERVER mst_remoteFOREIGN DATA WRAPPER dblink_fdwOPTIONS (host 'HOST',dbname 'DATABASE_NAME',port 'PORT');Create a user mapping for the
user1
to automatically authenticate as thetsdbadmin
when using thedblink
:CREATE USER MAPPING FOR user1SERVER mst_remoteOPTIONS (user 'tsdbadmin',password 'PASSWORD');Enable
user1
to use the remote PostgreSQL connectionmst_remote
:GRANT USAGE ON FOREIGN SERVER mst_remote TO user1;
In this example in the user1
user queries the remote table inventory
defined in the target
PostgreSQL database from the mst_remote
server definition:
To query a foreign data wrapper, you must be a database user with the necessary permissions on the remote server.
Connect to the MST service as
user1
with necessary grants to the remote server.Establish the
dblink
connection to the remote target server:SELECT dblink_connect('my_new_conn', 'mst_remote');Query using the foreign server definition as parameter:
SELECT * FROM dblink('my_new_conn','SELECT * FROM inventory') AS t(a int);
Output is similar to:
a-----100200300(3 rows)
Found an issue on this page?
Report an issue!