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 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.

To enable the dblink extension on an MST PostgreSQL service:

  1. Connect to the database as the tsdbadmin user:

    psql -x "postgres://tsdbadmin:<PASSWORD>@<HOSTNAME>:<PORT>/defaultdb?sslmode=require"
  2. Create the dblink extension

    CREATE EXTENSION dblink;
  3. Create a table named inventory:

    CREATE TABLE inventory (id int);
  4. Insert data into the inventory table:

    INSERT INTO inventory (id) VALUES (100), (200), (300);
  1. Create a user user1 who can access the dblink

    CREATE USER user1 PASSWORD 'secret1'
  2. 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'
    );
  3. 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'
    );
  4. Enable user1 to use the remote PostgreSQL connection mst_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.

  1. Connect to the MST service as user1 with necessary grants to the remote server.

  2. Establish the dblink connection to the remote target server:

    SELECT dblink_connect('my_new_conn', 'mst_remote');
  3. 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)

Found an issue on this page?

Report an issue!