How to Set up GCP PostgreSQL Destination in Daton

This subtopic provides step-by-step instructions to create integrations for GCP Postgres as a warehouse in Daton, detailing the prerequisites and the integration process in detail.

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.

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.

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.

   
5. Select the New subnet segment and enter the subsequent configuration parameters for a subnet:

• Provide a Name for the subnet.

• Select a Region.

• Enter an IP address range. This is the primary IPv4 range for the subnet. For more information, see IPv4 subnet ranges.

• Customize the rest according to the requirements and click Done.

1. In the Firewall rules section, select an SSH connection with port number 22.

2. Customize the rest according to the requirements and click Create. Your VPC network is successfully configured.

Task 2: Configure a GCP PostgreSQL Database

1. Create a Database Instance

2. Create a User Account

3. Create a Database

4. Create a VM Instance

5. Connect to your Cloud SQL Instance

6. Connect to the Database

Create a Database Instance

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

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

7. In the Choose region and zonal availability section, select the region and zone for your instance.

   
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.

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

2. To open the Overview page of an instance, click the above-created instance name.

3. Select Users from the SQL navigation menu.

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.

Create a Database

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

2. To open the Overview page of an instance, click the above-created instance name.

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.

Create a VM Instance with SSH Tunneling

1. Create a VM Instance

2. Configure SSH Tunnelling

Create a VM Instance

1. In the Google Cloud console, go to the VM instances page.

2. Click Create instance.

3. Specify a Name for your VM.

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)

   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.

      
   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

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

     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

     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.

2. Create a new PostgreSQL Database connection.

3. In the connection settings:

• Provide the IP address of the above-created Database Instance as the Host. You may find the endpoint in the following location:

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

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

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

Task 3: Integrate GCP PostgreSQL with Daton

1. Log in to your Daton account and search for GCP PostgreSQL in the list of Destinations, then click Configure.

2. Enter the Integration Name and click Next.

   
3. Enter the following credentials to configure your warehouse.

• GCP PostgreSQL JDBC URL in the format: jdbc:postgresql://Public_IP_address:5432/your_db_name You can find the IP Address here:

• Your database Username and Password. You can find the Username here:

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

• Note: Before clicking on Next, make sure you have pasted the SSH key on your VM (Check Configure SSH Tunnelling)

• Click Next.

1. Select the Schema of your choice.

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.