> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Integrate Postgres with Tiger Cloud

> Query other Postgres data bases using foreign data wrappers

export const SERVICE_SHORT = 'service';

export const VPC = 'VPC';

export const CLOUD_LONG = 'Tiger Cloud';

export const PG = 'Postgres';

export const SERVICE_LONG = 'Tiger Cloud service';

export const SELF_LONG = 'self-hosted TimescaleDB';

You use {PG} foreign data wrappers (FDWs) to query external data sources from a {SERVICE_LONG}. These external data sources can be one of the following:

* Other {SERVICE_LONG}s
* {PG} databases outside of {CLOUD_LONG}

If you are using {VPC} peering, you can create FDWs in your Customer VPC to query a {SERVICE_SHORT} in your {CLOUD_LONG} project. However, you can't create FDWs in your {SERVICE_LONG}s to query a data source in your Customer VPC. This is because {CLOUD_LONG} {VPC} peering uses AWS PrivateLink for increased security. See [VPC peering documentation][vpc-peering] for additional details.

{PG} FDWs are particularly useful if you manage multiple {SERVICE_LONG}s with different capabilities, and need to seamlessly access and merge regular and time-series data.

## Prerequisites

To follow the steps on this page:

* Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.<p />

  You need [your connection details][connection-info]. This procedure also
  works for [{SELF_LONG}][enable-timescaledb].

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted

[connection-info]: /integrations/find-connection-details

## Query another data source

To query another data source:

<Tabs label="Query another data source">
  <Tab title="Tiger Cloud">
    You create {PG} FDWs with the `postgres_fdw` extension, which is enabled by default in {CLOUD_LONG}.

    1. **Connect to your service**

       See [how to connect][connect].

    2. **Create a server**

       Run the following command using your [connection details][connection-info]:

       ```sql theme={"dark"}
       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][connection-info]:

       ```sql theme={"dark"}
       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:

         ```sql theme={"dark"}
         CREATE SCHEMA foreign_stuff;

         IMPORT FOREIGN SCHEMA public 
         FROM SERVER myserver 
         INTO foreign_stuff ;
         ```

       * Alternatively, import a limited number of tables:

         ```sql theme={"dark"}
         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:

         ```sql theme={"dark"}
         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 {PG} 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:

    ```sql theme={"dark"}
    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;
    ```
  </Tab>

  <Tab title="Self-hosted TimescaleDB">
    You create {PG} FDWs with the `postgres_fdw` extension. See [documenation][enable-fdw-docs] on how to enable it.

    1. **Connect to your database**

       Use [`psql`][psql] to connect to your database.

    2. **Create a server**

       Run the following command using your [connection details][connection-info]:

       ```sql theme={"dark"}
       CREATE SERVER myserver 
       FOREIGN DATA WRAPPER postgres_fdw 
       OPTIONS (host '<host>', dbname '<database_name>', port '<port>');
       ```

    3. **Create user mapping**

       Run the following command using your [connection details][connection-info]:

       ```sql theme={"dark"}
       CREATE USER MAPPING FOR postgres 
       SERVER myserver 
       OPTIONS (user 'postgres', password '<password>');
       ```

    4. **Import a foreign schema (recommended) or create a foreign table**

       * Import the whole schema:

         ```sql theme={"dark"}
         CREATE SCHEMA foreign_stuff;

         IMPORT FOREIGN SCHEMA public 
         FROM SERVER myserver 
         INTO foreign_stuff ;
         ```

       * Alternatively, import a limited number of tables:

         ```sql theme={"dark"}
         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:

         ```sql theme={"dark"}
         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;
         ```
  </Tab>
</Tabs>

[vpc-peering]: /manage-data/capabilities/security/vpc/

[sql-editor]: /deploy-and-operate/tiger-cloud/get-started/run-queries-from-console#ops-mode-sql-editor/

[connect]: /deploy-and-operate/tiger-cloud/get-started/run-queries-from-console

[connection-info]: /integrations/find-connection-details/

[enable-fdw-docs]: https://www.postgresql.org/docs/current/postgres-fdw.html

[psql]: /integrations/psql/