Aurora PostgreSQL Setup Guide
Follow these instructions to replicate your Amazon Aurora PostgreSQL database to your destination via Fivetran.
To connect your PostgreSQL database to Fivetran, you need:
- PostgreSQL version 7.3 or above
- IP (e.g. 220.127.116.11) or host (your.server.com)
- Port (usually 5432)
Note: We do not support serverless Aurora.
First, you'll need to decide whether to connect your Aurora PostgreSQL database directly or via an SSH tunnel. Security group configuration in subsequent steps will differ depending on this decision.
If you connect directly, you will need to create a rule in security group that allows us to access your database instance.
If you connect via a SSH tunnel, Fivetran will connect to a separate server in your network which provides an SSH tunnel to your database. This method is necessary if your database is in an inaccessible subnet. You will then configure your tunnel server's security group to allow us access and then configure the database instance's security to allow access from the tunnel.
If you have an SSH connection, please follow these instructions before proceeding to the next step.
Allow access to read replica
Creating a read replica is recommended but not necessary. Using a read replica will reduce the load on the master of Fivetran's queries. In practice, this load is negligible unless your database has a >100 million row table.
If you already have a read replica, you can skip to the next section Enable access.
In your RDS Dashboard, select the Aurora Postgres instance you would like to integrate:
Create aurora replica.
Make sure the replica is accessible from outside your VPC.
DB instance class for the replica. It need not to be as large as your master instance.
DB instance identifier.
DB Parameter group and write down the aurora replica's database
port number (you will need this later).
Then click on
Create Aurora replica.
Your RDS dashboard should now show the status of the replica as
It will take a few minutes for the Aurora replica to finish being created. The status will show
available when it is done.
The Fivetran data processing servers will need access to your Aurora PostgreSQL replica/master you want to integrate. Assuming your instance is in a VPC, access is controlled by two mechanisms. VPC Security Groups and Network ACLs (Access Control Lists). If your instance is not in a VPC, then you only need to configure Security Groups.
Configure security group
These instructions assume that your read replica is in a VPC (if not, you can still use these instructions as a guide because configuring a non-VPC security group is almost identical).
Click on your Aurora replica.
Go to your replica
Details and make it
Then click its
In the Security Group panel, select
Inbound tab then click
Add Rule to add a new
Custom TCP rule.
Enter your replica's port number (default 5432) in
Enter Fivetran's IP in
Description to your rule and click
Configure network ACLs
Click on your
VPC in Aurora replica
VPC. In the
Summary tab, select
You will see tabs for Inbound Rules and Outbound Rules. We will need to edit both.
Edit Inbound Rules
If you have a default VPC that was automatically created by AWS;
then it would have the setting, allow all incoming traffic as indicated by the Source value
and the fact that the
ALLOW entry is listed above the
If your inbound rules don't include
ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow inbound traffic to all ports
Edit Outbound Rules
Select "Outbound Rules":
If your outbound rules don't include an
ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow outbound traffic to all ports
Help on ACL configuration can be found here.
Open a connection to your master Aurora PostgreSQL database and execute the following SQL command to create
CREATE USER fivetran PASSWORD 'some-password';
some-password with a password of your choice.
Grant read-only access to Fivetran
Then grant the
fivetran user read-only access to all tables:
GRANT USAGE ON SCHEMA "public" TO fivetran; GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO fivetran; ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO fivetran;
The last command makes sure that any future tables will be accessible to fivetran. To grant access to schemas other
than the Postgres' default
public schema, simply replace
public with your other schema name. If you have several
schemas and you wish to grant access to all of them, you will need to repeat these three commands for each schema.
Restricted permissions (optional)
How to allow only a subset of tables
To grant access only to some tables in a schema, first make sure
fivetran has access to the schema itself:
GRANT USAGE ON SCHEMA "some_schema" TO fivetran;
Make sure you have removed any previously given permission to all tables in that schema.
ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" REVOKE SELECT ON TABLES FROM fivetran; REVOKE SELECT ON ALL TABLES IN SCHEMA "some_schema" FROM fivetran;
Then repeat this command for each table you wish to include.
GRANT SELECT ON "some_schema"."some_table" TO fivetran;
Any tables created in the future will be excluded from access by default. To include them run,
ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" GRANT SELECT ON TABLES TO fivetran;
To grant access to all tables except a few, unfortunately there is no way to do that directly in Postgres. You will just have to positively include all the tables you do want. Note that it is not possible to achieve exclusion by granting access to all tables and then revoking access for a subset of tables.
How to allow only a subset of columns
To grant access only to some columns in a table, first make sure you have removed any previously given permission to read all columns in the table:
REVOKE SELECT ON "some_schema"."some_table" FROM fivetran;
Then give permission to the specific columns, e.g.
GRANT SELECT (xmin, "some_column", "other_column") ON "some_schema"."some_table" TO fivetran;
Access to the hidden system column
xmin is required for incremental updates. Any new columns added to the table
in the future will not be accessible to
fivetran unless you rerun this command with the new column included.
To grant access to all columns except one, you would have to positively grant access to all the other columns.
Incremental update configuration
We support logical replication on Aurora PostgreSQL versions 10.6 or later. For earlier versions, we use the XMIN system column for incremental updates. As a result, our Aurora PostgreSQL connector does NOT support replicating deleted data for versions before 10.6.
Get Endpoint detail
Go to your Amazon Aurora replica you want to integrate and click on your replica.
Make a note of the replica
Endpoint URL and
Port number from the
Choose schema prefix
Each schema from your source database will be mapped to a schema in the destination by adding a prefix to the original schema name. For example, if your original database contains schemas "foo" and "bar" and if you choose the prefix "pre", then you will get schemas "pre_foo" and "pre_bar" in the output.
Finish Fivetran setup
- In your Fivetran setup form, enter your preferred Destination schema prefix.
- Enter the Host URL you found in Step 5.
- Enter the Port number you found in Step 5.
- Enter the User name you created in Step 3.
- Enter the Password you created in Step 3.
- Enter the Database name you want to replicate.
- Select your Connection Method. You can Connect directly or Connect using a SSH tunnel. See Step 1.
- If you connect using a SSH tunnel, enter the following details:
- SSH Host
- SSH Port
- SSH User
- Public Key
- (Optional) Enable the toggle Require TLS through tunnel if you want to use TLS.
- Click Save and Test. Your Aurora PostgreSQL database is now connected.