Use the PostgreSQL dblink extension

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.

Prerequisites

Before you begin, sign in to your Managed Service for TimescaleDB portal, navigate to the Overview tab, and take a note of these parameters for the PostgreSQL remote server. Alternatively, you can use the avn service get command in the Aiven client:

HOSTNAME: The remote database hostname
PORT: The remote database port
USER: The remote database user to connect. The default user is tsdbadmin.
PASSWORD: The remote database password for the USER
DATABASE_NAME: The remote database name. The default database name is defaultdb.

Enable the dblink extension

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 extension
```
CREATE 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 foreign data wrapper using dblink_fdw

Create a user user1 who can access the dblink
```
CREATE USER user1 PASSWORD 'secret1'
```

Create a remote server definition named mst_remote, using dblink_fdw and the connection details of the MST service.


CREATE SERVER mst_remote
    FOREIGN DATA WRAPPER dblink_fdw
    OPTIONS (
             host 'HOST',
             dbname 'DATABASE_NAME',
             port 'PORT'
             );

Create a user mapping for the user1 to automatically authenticate as the tsdbadmin when using the dblink:


CREATE USER MAPPING FOR user1
       SERVER mst_remote
       OPTIONS (
        user 'tsdbadmin',
        password 'PASSWORD'
        );

Enable user1 to use the remote PostgreSQL connection mst_remote:
```
GRANT USAGE ON FOREIGN SERVER mst_remote TO user1;
```

Query data using a foreign data wrapper

In this example in the user1 user queries the remote table inventory defined in the target PostgreSQL database from the mst_remote server definition:

Quering data using a foreign data wrapper

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
  -----
   100
   200
   300
 (3 rows)