Skip to content
Search ⌘K
Get started

Query external data sources with FDW

Query other Tiger Cloud services or external PostgreSQL databases using foreign data wrappers

You use PostgreSQL foreign data wrappers (FDWs) to query external data sources from a Tiger Cloud service. These external data sources can be one of the following:

  • Other Tiger Cloud services
  • PostgreSQL databases outside of Tiger Cloud

If you are using VPC peering, you can create FDWs in your Customer VPC to query a service in your Tiger Cloud project. However, you can’t create FDWs in your Tiger Cloud services to query a data source in your Customer VPC. This is because Tiger Cloud VPC peering uses AWS PrivateLink for increased security. See VPC peering documentation for additional details.

PostgreSQL FDWs are particularly useful if you manage multiple Tiger Cloud services with different capabilities, and need to seamlessly access and merge regular and time-series data.

Prerequisites

To follow the steps on this page:

Query another data source

To query another data source:

You create PostgreSQL FDWs with the postgres_fdw extension, which is enabled by default in Tiger Cloud.

  1. Connect to your service

    See how to connect.

  2. Create a server

    Run the following command using your connection details:

    CREATE SERVER myserver
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '<host>', dbname 'tsdb', port '<port>');
  3. Create user mapping

    Run the following command using your connection details:

    CREATE USER MAPPING FOR tsdbadmin
    SERVER myserver
    OPTIONS (user 'tsdbadmin', password '<password>');
  4. Import a foreign schema (recommended) or create a foreign table

    • Import the whole schema:

      CREATE SCHEMA foreign_stuff;
      

      IMPORT FOREIGN SCHEMA public
      FROM SERVER myserver
      INTO foreign_stuff ;

    • Alternatively, import a limited number of tables:

      CREATE SCHEMA foreign_stuff;
      

      IMPORT FOREIGN SCHEMA public
      LIMIT TO (table1, table2)
      FROM SERVER myserver
      INTO foreign_stuff;

    • Create a foreign table. Skip if you are importing a schema:

      CREATE FOREIGN TABLE films (
          code        char(5) NOT NULL,
          title       varchar(40) NOT NULL,
          did         integer NOT NULL,
          date_prod   date,
          kind        varchar(10),
          len         interval hour to minute
      )
      SERVER film_server;

A user with the tsdbadmin role assigned already has the required USAGE permission to create PostgreSQL FDWs. You can enable another user, without the tsdbadmin role assigned, to query foreign data. To do so, explicitly grant the permission. For example, for a new grafana user:

CREATE USER grafana;


GRANT grafana TO tsdbadmin;


CREATE SCHEMA fdw AUTHORIZATION grafana;


CREATE SERVER db1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '<host>', dbname 'tsdb', port '<port>');


CREATE USER MAPPING FOR grafana SERVER db1
OPTIONS (user 'tsdbadmin', password '<password>');


GRANT USAGE ON FOREIGN SERVER db1 TO grafana;


SET ROLE grafana;


IMPORT FOREIGN SCHEMA public
       FROM SERVER db1
       INTO fdw;