Use the Postgres 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.

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:

   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)