Aurora PostgreSQL Destination Setup Guide

Follow our setup guide to connect Aurora PostgreSQL database as a destination to Fivetran.

Prerequisites

To connect Aurora PostgreSQL to Fivetran, you need the following:

• PostgreSQL version 7.3 or later
• Database host's IP (e.g., 1.2.3.4) or domain (your.server.com)
• Port (usually 5432)
• A Fivetran role with the Create Destinations or Manage Destinations permissions

IMPORTANT: We do not support serverless Aurora.

Setup instructions

Choose connection method

Decide whether to connect to your Aurora PostgreSQL database directly, using an SSH tunnel, or using AWS PrivateLink. For more information, see our destination connection options documentation.

Allow Fivetran access

You must allow Fivetran access to your Aurora PostgreSQL database:

• If your instance is in a Virtual Private Cloud (VPC), you must configure:

  • VPC Security Groups
  • Network Access Control Lists (ACLs)

• If your instance is not in a VPC, then you only need to configure Security Groups

Configure security group

The following instructions assume that your database is in a VPC.

NOTE: You can use these instructions to configure a non-VPC security group because the process is almost identical.

1. Log in to your RDS dashboard.

2. In the Databases pane, select your Aurora PostgreSQL database.

   

3. In the database details window, make a note of the Port number. You will need this to configure Fivetran.

   

4. Click the VPC security groups link.

   

5. In the Security Groups section, go to the Inbound rules tab.

   

6. Click Edit inbound rules.

   

7. Click Add Rule.

   

   A new Custom TCP Rule is created at the bottom of the list with a blank space for a Port Range and a Source IP address.

8. Enter the following details:

   • In the Port Range field, enter the port number that you found earlier (usually 3306)
   • In the Source field, select Custom IP and then enter Fivetran's IPs

   

9. Click Save rules.

   

Configure Network ACLs

1. Return to the RDS dashboard and expand the view on the database.

   

2. Click the VPC link.

   

3. Select the VPC ID link.

   

4. In the Summary tab, click the Main network ACL link.

   

5. Go to the Inbound Rules tab.

   

   • If you have a default VPC that was automatically created by AWS, then the settings already allow all incoming traffic as indicated by the Source value 0.0.0.0/0 and the fact that the ALLOW entry is listed above the DENY entry.

     

   • If your inbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow Fivetran's IPs to access the port number of your database (usually 3306).

   NOTE: See Amazon's Network ACLs documentation for more information.

6. Go to the Outbound Rules tab.

   

   • If your outbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, click Edit outbound rules to edit the rules to allow outbound traffic to all ports 1024-65535 for Destination.

Create Fivetran user

1. Connect to your Aurora PostgreSQL database and execute the following query to create a user for Fivetran. Choose a memorable username (for example, fivetran). Replace <password> with a password of your choice:

   CREATE USER fivetran PASSWORD <password>;
   

2. Execute the following query to grant the fivetran user the following privileges:

   • CREATE: Allows the user to create new schemas in the database
   • TEMPORARY: Allows the user to create temporary tables while using the database

   GRANT CREATE, TEMPORARY ON DATABASE <database> TO fivetran;
   

Complete Fivetran configuration

1. Log in to your Fivetran account.
2. Go to the Destinations page and click Add destination.
3. Enter a Destination name of your choice and then click Add.
4. Select Aurora PostgreSQL as the destination type.
5. In the destination setup form, enter the Host name or the IP address of the database server.
6. Enter the Port number. For example, 5432.
7. Enter the User name you created in Step 3.
8. Enter your Password.
9. Enter the Database name you want to replicate to.
10. Choose your Connection method:
    • Connect directly
    • Connect via an SSH
    • Connect via Private Networking

    NOTE: The Connect via Private Networking option is available only for Business Critical accounts.

11. If you choose Connect via an SSH tunnel in the Connection method drop-down menu, enter the following details:
    • SSH Host
    • SSH Port
    • SSH User
    • (Optional) Enable the Require TLS through tunnel toggle if you want to use TLS.
12. Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and cloud region as described in our Destinations documentation.

    IMPORTANT: If you are using AWS PrivateLink, select AWS in the Cloud service provider drop-down menu.

13. Choose your Time zone.
14. (Optional for Business Critical accounts) To enable regional failover, set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make a note of the IP addresses of the secondary region and safelist these addresses in your firewall.
15. Click Save & Test.

Fivetran tests and validates the Aurora PostgreSQL destination connection. On successful completion of the setup tests, you can sync your data using Fivetran connectors to the Aurora PostgreSQL destination.

In addition, Fivetran automatically configures a Fivetran Platform Connector to transfer the connector logs and account metadata to a schema in this destination. The Fivetran Platform Connector enables you to monitor your connectors, track your usage, and audit changes. The connector sends all these details at the destination level.

IMPORTANT:

• If you are an Account Administrator, you can manually add the Fivetran Platform Connector on an account level so that it syncs all the metadata and logs for all the destinations in your account to a single destination. If an account-level Fivetran Platform Connector is already configured in a destination in your Fivetran account, then we don't add destination-level Fivetran Platform Connectors to the new destinations you create.
• If your PostgreSQL database uses pgbouncer as the connection pooler, make sure it pools the connections in session mode.

Setup tests

Fivetran performs the following Aurora PostgreSQL connection tests:

• The SSH Tunnel Test validates the SSH tunnel details you provided in the setup form and then checks the connectivity to the instance using the SSH Tunnel if you are connecting using an SSH tunnel.

• The Database Host Connection Test validates the database credentials you provided in the setup form. The test verifies that the host is not private and then checks the connectivity to the host.

• The Certificate Validation Test generates a pop-up window where you must choose which certificate you want Fivetran to use. The test then validates that certificate and checks that we can connect to your database using TLS. We skip this test if you aren't connecting directly.

• The Permission Test checks that we have the correct permissions to create schemas and tables in your database.

  NOTE: The tests may take a couple of minutes to finish running.

Related articles

description Destination Overview

assignment Release Notes

settings API Destination Configuration

home Documentation Home