# How to Set up GCP PostgreSQL Destination in Daton

## Prerequisites 

To follow the given instructions while creating an integration, you may require the following:

1. An account on Google Cloud Console and Daton.
2. An active project in the Google Cloud console. If not, see [Create a Project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#creating_a_project).
3. The Cloud SQL Admin and Compute Viewer roles on your user account.

## Task 1: Configure a VPC Security Connection

1. Log in to your [Google Cloud Console](https://console.cloud.google.com/).
2. On the **VPC network** dashboard, click **Create VPC Network**.
3. Provide a **Name** for the network.
4. Opt for the **Custom** option in the Subnet creation mode. 

   <figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2FBQjjOmf0UpUuPnMu8Umm%2F1GCP%20VPC%20SUBNET.png?alt=media&#x26;token=024ef8f1-7749-4bf9-a7a9-b2f07346e7d1" alt=""><figcaption></figcaption></figure>
5. Select the **New subnet** segment and enter the subsequent configuration parameters for a subnet:

* Provide a **Name** for the subnet.&#x20;
* Select a **Region**.&#x20;
* Enter an **IP address range**. This is the primary IPv4 range for the subnet.\
  &#x20;For more information, see [IPv4 subnet ranges](https://cloud.google.com/vpc/docs/subnets#manually_created_subnet_ip_ranges).
* Customize the rest according to the requirements and click **Done**.&#x20;

<figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2FTk15OUZ4ESJcuyGJNvUr%2F2GCP%20VPC%20SUBNET.png?alt=media&#x26;token=95f24c7a-87ae-46df-99a0-edd37541ab21" alt=""><figcaption></figcaption></figure>

6. In the **Firewall rules** section, select an SSH connection with port number 22.
7. Customize the rest according to the requirements and click **Create**. \
   Your VPC network is successfully configured.

## Task 2: Configure a GCP PostgreSQL Database&#x20;

1. [Create a Database Instance](#create-a-gcp-postgresql-database-instance)
2. [Create a User Account ](#create-a-user-account)
3. [Create a Database](#create-a-database)
4. [Create a VM Instance](#create-a-vm-instance)
5. [Connect to your Cloud SQL Instance](#connect-to-your-cloud-sql-instance)
6. [Connect to the Database](#connect-to-the-database)

### Create a Database Instance

1. In the Google Cloud console, go to the [Cloud SQL Instances page](https://console.cloud.google.com/sql/instances?_ga=2.221589720.1578733882.1693261521-1452745294.1693261521\&project=daton-210514).
2. Click **Create instance**.
3. Within the **Create an instance** page, proceed to the **Choose your database engine** section, and then select **PostgreSQL** clicking on it.
4. Enter an **ID** for your instance and create a P**assword** for the Postgres user.
5. Select the **Database Version** for your instance.
6. Select the **Cloud SQL Edition** for your instance as per your subscription plan.&#x20;
7. In the Choose **region and zonal availability** section, select the region and zone for your instance.

   <figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2FbJxoN3hyUDH6aaqfnbvC%2F2GCP%20Instance%20info.png?alt=media&#x26;token=39b6e965-b5e5-409c-8b3c-f3ae1635ec29" alt=""><figcaption></figcaption></figure>
8. In the **Customize** your instance section, update the settings for your instance:

* **Machine type**: Select from Shared core or Dedicated core.
* **Storage:** Select your storage type and capacity. You can also enable automatic storage that automatically provides more storage for your instance when free space runs low.
* **Connections:** Select **Public IP.** You can then add authorized networks to connect to the instance.
* **Data protection:** You can automate backups and configure the rest as per requirements.
* You can also configure **Maintenance**, **Flags**, and **Labels** per your requirements.

<figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2FIeo4tiMm9fknnJ14Y3Y6%2F2GCP%20Customize%20Instance%20%20(1).png?alt=media&#x26;token=febe60a6-3903-4308-9f58-a3bdfb6c3068" alt=""><figcaption></figcaption></figure>

9. Click **Create Instance.** \
   A new Database Instance will be successfully created.

### Create a User Account

1. In the Google Cloud console, go to the [Cloud SQL Instances](https://console.cloud.google.com/sql) page.
2. To open the Overview page of an instance, click the above-created **instance name**.&#x20;
3. Select **Users** from the SQL navigation menu.&#x20;
4. Click **Add user account.**
5. Select **Built-in authentication** and add a **Username** and a strong **Password** that you can remember.
6. Click **Add**.\
   A new User will be successfully created.

<figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2F5Jp0i0Y4K2wBl86ChqGv%2F3GCP%20Create%20Useracc.png?alt=media&#x26;token=6eb23478-bf1a-46dd-93ce-8555f6c32ffd" alt=""><figcaption></figcaption></figure>

### Create a Database

1. In the Google Cloud console, go to the [Cloud SQL Instances](https://console.cloud.google.com/sql) page.
2. To open the Overview page of an instance, click the above-created **instance name**.&#x20;
3. Select **Databases** from the SQL navigation menu.
4. Click **Create Database**.
5. In the New Database dialog, specify the **name** of the database.
6. Click **Create**.\
   Your Database will be successfully created.

<figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2FeXHg6Rc0pJa48Gordno6%2F3GCP%20Create%20Database.png?alt=media&#x26;token=d9409d0c-545e-4b33-a60d-5b95ab40d170" alt=""><figcaption></figcaption></figure>

### Create a VM Instance with SSH Tunneling&#x20;

1. [Create a VM Instance](#create-a-vm-instance)
2. [Configure SSH Tunnelling](#configure-ssh-tunnelling)

#### Create a VM Instance

1. In the Google Cloud console, go to the[ VM instances](https://console.cloud.google.com/compute/instances) page.
2. Click **Create instance**.
3. Specify a **Name** for your VM.&#x20;
4. Change the **Zone** for this VM. Compute Engine randomizes the list of zones within each region to encourage use across multiple zones.
5. Select a **Machine configuration** for your VM.
6. Expand the **Advanced Options** section.
   1. Expand the **Networking** section and add a tag to the network.
   2. For **Network interfaces**, specify the network details:
      1. In the **Network** field, select the VPC network that contains the subnet you created above.
      2. In the **Subnet** field, select the subnet for the VM to use.\
         (You can configure External IP addresses as static or ephemeral. If a VM requires a fixed external IP address that does not change, you can obtain a static external IP address. You can reserve new external IP addresses or promote existing ephemeral external IP addresses.)
      3. Click **Done**.
7. Configure the rest of the settings as per your requirements or keep them as default.
8. To create and start the VM, click **Create**.\
   A VM Instance will be successfully created.

#### Configure SSH Tunnelling

1. In the Google Cloud console, go to the **VM instances** page.
2. In the list of virtual machine instances, click **SSH** in the row of the above-created instance.
3. Open a terminal and create an SSH key pair. A .pem file will get downloaded on your machine.
4. To add the SSH keys to the VM, perform the following:
   1. Copy the SSH Public Key from Daton [(Check Task 3, Step 3)](#task-3-integrate-gcp-postgresql-with-daton)
   2. Click on the above-created instance on the VM instances page.
   3. Click Edit.
   4. Under the Security and Access section, click Add Item for SSH Keys.
   5. Paste the Public Key in the dialog box.&#x20;

      <figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2FOPSwem33PfyGel42jqku%2F3GCP%20VM%20SSH.png?alt=media&#x26;token=2727fbf4-db12-44dc-85e7-0b6203ebad15" alt=""><figcaption></figcaption></figure>
   6. Save the Instance.\
      Your SSH connection with the VM instance is successfully configured.

### Connect to your Cloud SQL Instance

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   [Go to Cloud SQL Instances](https://console.cloud.google.com/sql)
2. To open the Overview page of an instance, click the above-created **instance name**.
3. From the SQL navigation menu, select **Connections**.
4. Click the Networking tab.
5. Select the Public IP checkbox. and perform the following:
   * [Connect with the VPC](#connect-with-the-vpc)
     1. Click **Add Network**.
     2. Provide the name of the above-created **VPC** and the **IP range** of the subnet corresponding to it.
     3. Click **Done**.
   * [Connect with the VM Instance](#connect-with-the-vm-instance)
     1. Click **Add Network**.
     2. Provide the name of the above-created **VM Instance** and the corresponding **External IP Address**.
     3. Click **Done**.
6. Click **Save.**

### Connect to the Database

1. Launch a Database Management tool of your choice.&#x20;
2. Create a new PostgreSQL Database connection.
3. In the connection settings:&#x20;

* Provide the IP address of the above-created Database Instance as the Host. You may find the endpoint in the following location:&#x20;

<figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2F0iTxSz0QqJE5kIbx8qu6%2F3GCP%20Database%20Instance%20IP.png?alt=media&#x26;token=28cc74dd-d1b9-4a07-970e-d91773949203" alt=""><figcaption></figcaption></figure>

* Provide the Name of the above-created Database and Corresponding Username and Password.

4. In the SSH tunneling section:

* Insert the **VM External IP Address** as the Host.
* Add the **User** specified in the VM Instance.
* Add the **Public Key** file downloaded on your local machine.
* Click **Test Tunnel Configuration.**

5. Now Test the Connection and click **Finish**.\
   Your GCP PostgreSQL Database is successfully configured.

> Note that, you can query in your now created database to create Schemas and Tables as per your requirement.&#x20;

## Task 3: Integrate GCP PostgreSQL with Daton

1. Log in to your [Daton account](https://daton.sarasanalytics.com/u/integrations-list) and search for GCP PostgreSQL in the list of Destinations, then click **Configure**.
2. Enter the Integration Name and click **Next.**&#x20;

   <figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2FVbMYd5W4JeSa05ka3bXR%2F3GCP%20Daton%20inte.png?alt=media&#x26;token=5d025441-968e-49c4-98f4-82178212f21b" alt=""><figcaption></figcaption></figure>
3. Enter the following credentials to configure your warehouse.

<figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2FD1yriPfoGAsZGA0t4UT4%2F3GCP%20Daton%20inte%20data.png?alt=media&#x26;token=e0dd311d-7fef-4e09-a1e1-8b0baddd0da2" alt=""><figcaption></figcaption></figure>

* **GCP PostgreSQL** **JDBC URL** in the format: **jdbc:postgresql://Public\_IP\_address:5432/your\_db\_name**\
  You can find the IP Address here:&#x20;

<figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2F0iTxSz0QqJE5kIbx8qu6%2F3GCP%20Database%20Instance%20IP.png?alt=media&#x26;token=28cc74dd-d1b9-4a07-970e-d91773949203" alt=""><figcaption></figcaption></figure>

* Your database **Username** and **Password.** \
  You can find the Username here:&#x20;

<figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2Fsdaqwi7fH1jkjhCzUtDT%2F3GCP%20Daton%20inte%20data%20(1).png?alt=media&#x26;token=33e0d102-895d-4047-b4d5-98170055af2d" alt=""><figcaption></figcaption></figure>

* Set Use Tunnel to **SSH** and provide Proxy Host. \
  Proxy Host is your **Public IP** that can be found in the details of the VM instance in consideration.
* Enter Proxy Port as "22" and Proxy User will be the name username you create in VM. &#x20;
* Note: Before clicking on Next, make sure you have pasted the SSH key on your VM [(Check Configure SSH Tunnelling)](#configure-ssh-tunnelling)
* Click **Next.**

4. Select the Schema of your choice.&#x20;

<figure><img src="https://15515196-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LbJ1eMq5M51cIpU163R%2Fuploads%2Fp5UCUv7SqdofVku0ethu%2F3GCP%20Daton%20inte%20schema%20(1).png?alt=media&#x26;token=e1e41c08-b69c-4fc4-b87e-991862bd2ebc" alt=""><figcaption></figcaption></figure>

Hit the **Submit** button. Your warehouse will be successfully integrated.

> Note:
>
> 1. You may copy the SSH Key that appears at the bottom of the Setup page and add it to you repository for secure authentication.
> 2. You can query in your created database to create additional Schemas and Tables as per your requirement.&#x20;


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://daton-sarasanalytics.gitbook.io/daton/integrations/destinations/gcp-postgresql/how-to-set-up-gcp-postgresql-destination-in-daton.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.