How to Set up RDS PostgreSQL Destination in Daton

Prerequisites

Before you get started, ensure you have the following:

• Active Daton and AWS account

• Administrative user for your AWS account to create or configure a Daton-specific RDS PostgreSQL database

Task 1: Configure a VPC security connection for Amazon RDS for PostgreSQL

1. Log in to your RDS dashboard.

2. On the VPC dashboard, choose Create VPC.

3. For Resources to create, choose VPC and more and perform the following.

   1. Name the VPC.

   2. Provide the details according to custom requirements and set the rest to default.

      You can now visualize the relationships between the VPC resources you've configured.

   3. When you are finished configuring your VPC, choose Create VPC.

   

   
4. Your VPC connection is set.

   

Task 2: Create and Connect to an RDS for PostgreSQL Database Instance

1. Create a Subnet group for the database

2. Create a PostgreSQL Database

3. Connect to an EC2 Instance

4. Connect to the Database

Create a Subnet group for the database

1. Navigate to the RDS in the Amazon Console.

2. Navigate to the Subnet groups section and click Create DB subnet group.

   
3. On the DB subnet creation page, perform the following:

   • Provide your Subnet group name and description.

     
   • Select the above-created VPC identifier.

     
   • Add Availability zones and Subnets, click Create When choosing the Subnets, please ensure to exclusively select the private subnets.

     
4. A Subnet group has been created.

   

Create a PostgreSQL Database

1. In the Amazon RDS navigation pane, choose Databases.

2. Choose Create database and perform the following:

   1. Select the Engine type as PostgreSQL and the Template as Free tier.

      
   2. Provide a Name in the DB instance identifier, a Master username, and create a Master Password.

      
   3. In the Instance configuration section, set Burstable classes to db.t3.micro. This can be configured as per your specific requirements.

      
   4. In the Storage section, select the fields as per requirement. It is recommended to set the Storage Type to General Purpose SSD (gp2).

      
   5. In the Connectivity section,

      1. Select Don't connect to an EC2 compute resource

      2. Select the above-created VPC and DB subnet groups

      3. Set the Public access to No

      4. Create a new VPC security group for firewall purposes, Name it, and Select an Availability Zone according to your preferences.

      5. Check the Create an RDS Proxy checkbox.

      Keep the rest as default or customize according to your requirements.

      
   6. Select your Database Authentication method, Monitoring preferences, and Additional Configuration as per your requirements.

      
3. Now check your Estimated Monthly cost and click on Create Database. A new Database will be successfully created.

Please note that you can customize the database according to your specific requirements. To know more follow the link, Creating an Amazon RDS DB instance.

Connect to an EC2 Instance

1. Launch an EC2 Instance

2. Add the EC2 Subnet group to your Database Subnet group

Launch an EC2 Instance

1. On the AWS Management Console, go to the Amazon EC2 console.

2. Choose EC2 Dashboard, and then choose Launch instance.

   
3. Provide a Name to the EC2 instance.

   
4. Under Application and OS Images (Amazon Machine Image), choose an AMI of your choice.

   
5. Select the t2.micro option under the Instance type.

6. For the Key pair (login), you have two options. If you already have a key pair, just pick its name. If you want a new key pair, click on Create new key pair. Then, follow the steps in the Create key pair window to make the new key pair.

   

   For more information about creating a new key pair, see Create a key pair in the Amazon EC2 User Guide.

7. In the Network settings section, configure these values while leaving the rest at their default:

   1. Select the previously created VPC.

   2. Select one of its Public Subnets.

   3. Set Auto-assign public IP to Enable.

   4. Create a Firewall security group and Name it.

      
8. Now check the settings of the EC2 instance for configuration or keep them as default and click on Launch. A note saying the Instance is successfully launched will appear. This is how your EC2 instance would look like:

   

Add the EC2 Subnet group to your Database Subnet group

Here we edit the inbound subnet groups to connect the EC2 instance with the Database. This step creates a communication tunnel between the EC2 instance and the Database.

1. In the Amazon RDS dashboard, choose the above-created database instance and click on the VPC security groups URL under the Security section.

   
2. You will be redirected to the Security Groups page of the dedicated Database instance. Find the Edit inbound rules button and click on it.

   
3. On the edit page, perform the following:

   1. Add rule. Under the Type dropdown, select PostgreSQL and select the source as the security groups corresponding to the EC2 instance.

   2. Add another rule. Here, select the Port range as 3306 and add the IP address accordingly.

   

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:

   1. Provide the Endpoint of the above-created Database Instance. You may find the endpoint in the following location:

      
   2. Provide the Master Username and Password of the Database instance.

4. In the SSH tunneling section:

   1. Insert the Public IPv4 DNS of the EC2 instance. You may find the Public IPv4 DNS of the EC2 instance in the following location:

      
   2. Provide the Key Pair used while Launching the EC2 Instance. Remember that, this Key pair is supposedly a .pem file.

   3. Test the SSH connection.

5. Save the Database by clicking on the Finish button. Your RDS 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 Amazon RDS for PostgreSQL Database Instance with Daton

1. Log in to your Daton account and search for RDS 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:

   

   1. RDS PostgreSQL JDBC URL in the format: jdbc:postgresql://AmazonRDSPostgreSQL_Database_Endpoint:3306/your_db_name

   2. RDS PostgreSQL Master Username and Password

   3. Set Use Tunnel to SSH and provide Proxy Host. Proxy Host is your Public IPv4 DNS that can be found in details of the EC2 instance in consideration.

   4. Set the Proxy Port to 22 and the Proxy user to ec2-user(Default). You may also create a separate user in the EC2 instance.

4. Select the Schema of your choice.

   

   Now Submit. Your warehouse is configured successfully.

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.