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.
Last updated
Was this helpful?
To follow the given instructions while creating an integration, you may require the following:
An account on Google Cloud Console and Daton.
An active project in the Google Cloud console. If not, see .
The Cloud SQL Admin and Compute Viewer roles on your user account.
Log in to your .
On the VPC network dashboard, click Create VPC Network.
Provide a Name for the network.
Opt for the Custom option in the Subnet creation mode.
Select the New subnet segment and enter the subsequent configuration parameters for a subnet:
Provide a Name for the subnet.
Select a Region.
Customize the rest according to the requirements and click Done.
In the Firewall rules section, select an SSH connection with port number 22.
Customize the rest according to the requirements and click Create. Your VPC network is successfully configured.
Click Create instance.
Within the Create an instance page, proceed to the Choose your database engine section, and then select PostgreSQL clicking on it.
Enter an ID for your instance and create a Password for the Postgres user.
Select the Database Version for your instance.
Select the Cloud SQL Edition for your instance as per your subscription plan.
In the Choose region and zonal availability section, select the region and zone for your instance.
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.
Click Create Instance. A new Database Instance will be successfully created.
To open the Overview page of an instance, click the above-created instance name.
Select Users from the SQL navigation menu.
Click Add user account.
Select Built-in authentication and add a Username and a strong Password that you can remember.
Click Add. A new User will be successfully created.
To open the Overview page of an instance, click the above-created instance name.
Select Databases from the SQL navigation menu.
Click Create Database.
In the New Database dialog, specify the name of the database.
Click Create. Your Database will be successfully created.
Click Create instance.
Specify a Name for your VM.
Change the Zone for this VM. Compute Engine randomizes the list of zones within each region to encourage use across multiple zones.
Select a Machine configuration for your VM.
Expand the Advanced Options section.
Expand the Networking section and add a tag to the network.
For Network interfaces, specify the network details:
In the Network field, select the VPC network that contains the subnet you created above.
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.)
Click Done.
Configure the rest of the settings as per your requirements or keep them as default.
To create and start the VM, click Create. A VM Instance will be successfully created.
In the Google Cloud console, go to the VM instances page.
In the list of virtual machine instances, click SSH in the row of the above-created instance.
Open a terminal and create an SSH key pair. A .pem file will get downloaded on your machine.
To add the SSH keys to the VM, perform the following:
Click on the above-created instance on the VM instances page.
Click Edit.
Under the Security and Access section, click Add Item for SSH Keys.
Paste the Public Key in the dialog box.
Save the Instance. Your SSH connection with the VM instance is successfully configured.
In the Google Cloud console, go to the Cloud SQL Instances page.
To open the Overview page of an instance, click the above-created instance name.
From the SQL navigation menu, select Connections.
Click the Networking tab.
Select the Public IP checkbox. and perform the following:
Click Add Network.
Provide the name of the above-created VPC and the IP range of the subnet corresponding to it.
Click Done.
Click Add Network.
Provide the name of the above-created VM Instance and the corresponding External IP Address.
Click Done.
Click Save.
Launch a Database Management tool of your choice.
Create a new PostgreSQL Database connection.
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.
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.
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.
Enter the Integration Name and click Next.
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.
Click Next.
Select the Schema of your choice.
Hit the Submit button. Your warehouse will be successfully integrated.
Note:
You may copy the SSH Key that appears at the bottom of the Setup page and add it to you repository for secure authentication.
You can query in your created database to create additional Schemas and Tables as per your requirement.
Enter an IP address range. This is the primary IPv4 range for the subnet. For more information, see .
In the Google Cloud console, go to the .
In the Google Cloud console, go to the page.
In the Google Cloud console, go to the page.
In the Google Cloud console, go to the page.
Copy the SSH Public Key from Daton
Log in to your and search for GCP PostgreSQL in the list of Destinations, then click Configure.
Note: Before clicking on Next, make sure you have pasted the SSH key on your VM
