Skip to main content
Feedback

Amazon RDS PostgreSQL connection

You can set up AWS RDS/Aurora Postgres as a target in Data Integration, by creating the proper requirements for loading, in addition to proper configuration.

Prerequisites

  • Make sure you have signed up for AWS and a console Admin user. If you do not 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 to use the AWS table import function. For more information, refer to Amazon RDS and Aurora documentation.

Setting up your RDS instance or Aurora cluster to Data Integration

Open Data Integration IPs in security groups

  1. Navigate 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 is defined for 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

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

Creating 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;

Creating a user for Data Integration in your database

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 the 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 created with the next privileges on the database:
-- Optional - you can use the existing schema. Create a new one if wanted.
CREATE SCHEMA IF NOT EXISTS 'data_integration';
       
-- REQUIRED 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 the 'data_integration' user to 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 - Configuring SSH tunnel instance in AWS

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

Configuring 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. Enter your credentials from the Redshift cluster and database (host address, database, username, and password of Data Integration user name) and for the Data Integration user that you created.
  2. Test the connection and Save.
On this Page