Skip to main content
Feedback

Amazon RDS PostgreSQL connection

Overview

This guide will show you how to set up AWS RDS/Aurora Postgres as a target in Data Integration.

Along the way, the guide will show you how to create the proper requirements for loading, in addition to proper configuration

Prerequisites

  • Before you use this guide, please make sure you’ve signed up for AWS and that you have a console admin user. If you don’t have one of these prerequisites, you can start here.
  • Postgres in AWS RDS and/or Aurora must be in a version greater than 10.7 in order to use the AWS table import function. For more information, see Amazon RDS and Aurora Documentation.

Setting up your RDS instance or Aurora cluster to Data Integration

Open Data Integration IPs in security groups

  1. Go to the RDS Console in AWS.
  2. Under the Databases in the main menu, click the Postgres instance/Aurora cluster you want to use.
  3. Go to Connectivity and Security in the Security section, find the VPC security group that defined to the instance/cluster and click on its name.
  4. In the Security group screen, go to Inbound Rules -> Edit Inbound Rules.
  5. Add Data Integration IP addresses and comment them as "Data Integration". Click on Save Rule. Data Integration IP Addresses
  6. In the Security group screen, navigate to Outbound > Rules > Edit Outbound Rules.
  7. Set the Outbound Rule to only allow the IP address of:
    0.0.0.0/0

Where to find my RDS/Aurora PostgreSql host (Endpoint)?

  1. Open the RDS console and then choose Databases to display a list of your DB instances.
  2. Choose the PostgreSQL DB instance name to display its details.
  3. On the Connectivity & security tab, copy the endpoint. Also, note the port number. You need both the endpoint and the port number to connect to the DB instance.

Create AWS extensions

To load data from the FileZone to the RDS/Aurora cluster, it's essential to confirm that AWS extensions are installed in the Postgres database.

To install the extensions:

  1. Access the database using a superuser role username.
  2. Run the following commands: 
        CREATE EXTENSION IF NOT EXISTS aws_commons CASCADE;
        CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE;

Create a user for Data Integration in your Database

Now, Connect to your PostgreSQL database, and create a user for Data Integration.

  1. Connect your database with the endpoint with your master user and database, using PGAdmin or DBeaver clients.
  2. In the Client, send next SQL statement (change the <MyPassword> to the wanted password ): CREATE USER data_integration WITH PASSWORD '<MyPassword>';
  3. Grant Create on Database for Data Integration:
    GRANT CREATE ON DATABASE <db> TO data_integration;
  4. Grant the user just created with the next privileges on the database: 
        -- Optional - you can use existing schema. Create new one if wanted.
        CREATE SCHEMA IF NOT EXISTS 'data_integration';
        
        -- REQUIRED in order to make sure the loading is running as expected
        GRANT ALL ON ALL FUNCTIONS IN SCHEMA aws_s3 to data_integration;
        GRANT ALL ON SCHEMA aws_s3 TO data_integration;
        GRANT ALL ON ALL FUNCTIONS IN SCHEMA aws_commons to data_integration;
        GRANT ALL ON SCHEMA aws_commons TO data_integration;
        
        /*
        Grant separately for each schema you want 'data_integration' user can use. 
        For example, we're using the schema 'public' and schema 'data_integration'
        */
        -- Schema: public
        GRANT CREATE, USAGE ON SCHEMA public TO data_integration;
        GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON ALL TABLES IN SCHEMA public TO data_integration;
        
        -- Schema: data_integration
        GRANT CREATE, USAGE ON SCHEMA data_integration TO data_integration ;
        GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON ALL TABLES IN SCHEMA data_integration TO data_integration;
        
        /* ... */

Optional - Configure SSH tunnel instance in AWS

If there's a need to configure an SSH Tunneling instance in AWS. In order to do that, you can follow the steps in the next document.

Configure the Amazon RDS/Aurora for PostgreSQL connection in Data Integration

  1. Go to Create River and select Source to Target River.
  1. From the Target list, choose Amazon RDS/Aurora for PostgreSQL.
  1. Now, enter your credentials information from the Redshift cluster and database (ie. host address, database, username and password of Data Integration user name ) and for the Data Integration user that you’ve created.
  2. Test the connection and Save.
On this Page