---
title: Use the Postgres dblink extension | Tiger Data Docs
description: Use the dblink extension and connect to other Postgres databases
---

The `dblink` [PostgreSQL extension](https://www.postgresql.org/docs/current/dblink.html) allows you to connect to other PostgreSQL databases and to run arbitrary queries.

You can use [foreign data wrappers](https://www.postgresql.org/docs/current/postgres-fdw.html) (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.

## 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 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 MST service:

1. **Connect to the database**

   Connect to the database as the `tsdbadmin` user:

   Terminal window

   ```
   psql -x "postgres://tsdbadmin:<PASSWORD>@<HOSTNAME>:<PORT>/defaultdb?sslmode=require"
   ```

2. **Create the dblink extension and test data**

   1. Create the `dblink` extension:

      ```
      CREATE EXTENSION dblink;
      ```

   2. Create a table named `inventory`:

      ```
      CREATE TABLE inventory (id int);
      ```

   3. Insert data into the `inventory` table:

      ```
      INSERT INTO inventory (id) VALUES (100), (200), (300);
      ```

## Create a foreign data wrapper using dblink\_fdw

1. **Create a user**

   Create a user `user1` who can access the `dblink`:

   ```
   CREATE USER user1 PASSWORD 'secret1'
   ```

2. **Create a remote server definition**

   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'
                );
   ```

3. **Create a user mapping**

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

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

4. **Grant usage on the remote server**

   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

To query a foreign data wrapper, you must be a database user with the necessary permissions on the remote server.

1. **Connect to the service**

   Connect to the Managed Service for TimescaleDB service as `user1` with necessary grants to the remote server.

2. **Establish the dblink connection**

   Establish the `dblink` connection to the remote target server:

   ```
   SELECT dblink_connect('my_new_conn', 'mst_remote');
   ```

3. **Query using the foreign server definition**

   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)
   ```