Use the Postgres dblink extension

Tiger Cloud: Performance, Scale, Enterprise, Free

Self-hosted products

MST

The dblink Postgres extension allows you to connect to other Postgres 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 service, navigate to the Overview tab, and take a note of these parameters for the Postgres 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 Postgres MST 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 Managed Service for TimescaleDB 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 Postgres 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 Postgres 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 Managed Service for TimescaleDB 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)