Amazon Aurora PostgreSQL Setup Guide

Follow these instructions to replicate your Amazon Aurora PostgreSQL database to your destination using Fivetran.

Prerequisites

To connect your Amazon Aurora PostgreSQL database to Fivetran, you need:

PostgreSQL version 11 - 16
Your database host's IP (e.g., 1.2.3.4) or domain (your.server.com)
TLS enabled on your database. Follow Amazon's TLS setup instructions to enable TLS on your database.

IMPORTANT: Aurora Serverless V2 requires PostgreSQL version 13 or later.

Setup instructions

IMPORTANT: Do not perform the Choose connection method step if you want to use Hybrid Deployment for your data pipeline.

Choose connection method (TLS required)

IMPORTANT: You must have TLS enabled on your database to connect to Fivetran.

Decide whether to connect your Amazon Aurora PostgreSQL database directly, using an SSH tunnel, using AWS PrivateLink, or using Proxy Agent. How you configure your security groups will differ depending on this decision.

Connect directly

Fivetran connects directly to your database instance. This is the simplest method.

If you connect directly, you will create a rule in a security group that allows Fivetran access to your database instance.

Connect using SSH

Fivetran connects to a separate server in your network that provides an SSH tunnel to your database. You must connect through SSH if your database is in an inaccessible subnet.

If you connect via SSH, you will configure your tunnel server's security group to allow Fivetran access and configure your database's security to allow access from the tunnel.

Before you proceed to the next step, you must follow our SSH connection instructions.

Connect using AWS PrivateLink

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. PrivateLink is the most secure connection method. Learn more in AWS’ PrivateLink documentation.

Follow our AWS PrivateLink setup guide to configure PrivateLink for your database.

Connect using Proxy Agent

Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS.

To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.

Choose incremental sync method

To keep your data up to date after the initial sync, we use one of the following incremental sync methods. The logical replication and XMIN methods keep a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync. Fivetran Teleport Sync instead takes snapshots of tables to calculate differences.

TIP: We recommend using logical replication as your incremental sync method because it is faster than XMIN replication and allows Fivetran to detect deleted rows for tables with primary keys. Learn more in our updating data documentation.

Choose your incremental sync method:

NOTE: You will configure your incremental sync method in later steps.

Logical replication

IMPORTANT: You can only enable logical replication if your Amazon Aurora PostgreSQL version is 10 or later.

Logical replication is based on logical decoding of the PostgreSQL write-ahead log (WAL). Fivetran reads the WAL using the pgoutput plugin to detect any new or changed data. This plugin replicates from your custom publication without needing additional libraries.

To learn more, see our logical replication documentation.

XMIN

IMPORTANT: You must use the XMIN method if your Amazon Aurora PostgreSQL version is earlier than 10.

The XMIN method is based on the hidden xmin system column that is present in all PostgreSQL tables. With XMIN, Fivetran must scan every table in full to detect updated data. We do not recommend XMIN for near real-time data needs because XMIN replication is slower than logical replication and doesn't allow Fivetran to detect deleted rows.

Learn more in our XMIN documentation.

Fivetran Teleport Sync

Fivetran Teleport Sync is a proprietary incremental sync method that can add delete capture with no additional setup other than a read-only SQL connection. Updates will be captured using the XMIN system column.

Learn more in our Fivetran Teleport Sync documentation.

Create read replica (optional)

If you plan on using XMIN for replication, you can create a read replica (also called an Aurora reader) for Fivetran's exclusive use if you'd like. Using a read replica reduces the load of Fivetran's queries on your primary database, though this load is usually negligible unless your database has a table with more than 100 million rows. We recommend that you connect a read replica to Fivetran, but it's not required.

IMPORTANT: You cannot enable logical replication on a read replica.

If you already have a read replica, want to connect Fivetran to your primary database, or want to use logical replication, skip ahead to Step 4.

In your Amazon RDS Dashboard, select the Amazon Aurora PostgreSQL database that you want to replicate.
Click Actions, then select Add reader.
On the Add Reader page, find the Settings section. Add a DB instance identifier for your read replica.
In the DB instance size section, specify the DB instance class for the read replica. It does not need to be as large as your primary instance.
In the Connectivity section, ensure that the read replica is accessible from outside your VPC.
Click Additional configuration to reveal more configuration options.
Choose a DB cluster parameter group.
Click Add reader.
The read replica's status should now be creating. It will take a few minutes for the read replica to finish being created. The status will change to available when it is done.
Set the value of the max_standby_streaming_delay parameter to 15-30 min. This ensures that import/incremental queries complete before the replica server cancels them.

Enable database access

Grant Fivetran's data processing servers access to your database server. How you grant access depends on whether or not your database instance is in a VPC.

If your instance is in a VPC, you must configure the two methods that control access: VPC security groups and network access control lists (ACLs). If your instance is not in a VPC, you only need to configure security groups.

Find endpoint and port

Find the endpoint and port for the database that you want to connect to Fivetran.

In your RDS dashboard, click on the Amazon Aurora PostgreSQL database that you want to connect to Fivetran.
In the Connectivity & security section, find the Endpoint and Port and make a note of them. You will need them to configure Fivetran.

Configure security group

NOTE: These instructions assume that your database instance is in a VPC. If your database instance is not in a VPC, you can still use these instructions because configuring a non-VPC security group is an almost identical process.

In your RDS dashboard, click on the Amazon Aurora PostgreSQL database that you want to connect to Fivetran.
In the Connectivity & security section, ensure that your database's Public accessibility value is YES.
Click the link to your database's security group.
Click the security group ID.
In the Security Group panel, go to the Inbound tab, then click Edit inbound rules.
Click Add Rule. This creates a new Custom TCP Rule at the bottom of the list.
Fill in the new Custom TCP Rule.
- In the Port Range field, enter your database's port number that you found in the previous step. (The default port number is 5432.)
- What you enter in the Custom IP field depends on whether you're connecting directly or using an SSH tunnel.
  - If you're connecting directly, enter Fivetran's IPs for your database's region.
  - If you're connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.
- (Optional) Enter a brief description in the Description field.
Click Save rules.

Configure network ACLs (VPC only)

If your database instance is not in a VPC, skip ahead to Step 5.

Return to the RDS dashboard.
Click on your database instance.
Click the link to the instance's VPC.
Click the VPC ID.
In the Details section, click on the Network ACL.
Click the Network ACL ID.

You will see tabs for Inbound Rules and Outbound Rules. You must edit both.

Edit inbound rules

Select Inbound Rules.
If you have a default VPC that was automatically created by AWS, the settings already allow all incoming traffic. To verify that the settings allow incoming traffic, confirm that the Source value is 0.0.0.0/0 and 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 the Source to access the port number of your database instance. (The port will be 5432 for direct connections, unless you changed the default.) For additional help, see Amazon's Network ACL documentation.
- If you're connecting directly, enter Fivetran's IPs for your database's region.
- If you're connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.

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 1024-65535 for destination 0.0.0.0/0. For additional help, see AWS's Network ACLs documentation.

Create user

Create a database user for Fivetran's exclusive use.

Open a connection to your primary Amazon Aurora PostgreSQL database.
Create a user for Fivetran by executing the following SQL command. Replace <username> and some-password with a username and password of your choice.

CREATE USER <username> PASSWORD 'some-password';

Grant user read-only access

Grant the Fivetran user read-only access to all tables by running the following commands. To grant access to a schema other than PostgreSQL's default public schema, replace public with the schema name.

GRANT USAGE ON SCHEMA "public" TO <username>;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO <username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO <username>;

NOTE: The last command makes sure that any future tables will be accessible to Fivetran.

If you want to grant access to multiple schemas, you must run these three commands for each schema.

Restrict access to tables (optional)

If you want to limit Fivetran's access to your tables, grant the Fivetran user access to only the tables that you would like to sync. You must individually grant access for each table that you want to sync. You cannot grant access to all tables and then revoke access for a subset of tables.

Ensure that the Fivetran user has access to the schema that contains your table(s).
```
GRANT USAGE ON SCHEMA "some_schema" TO <username>;
```

Revoke any previously granted permission to all tables in that schema.

ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" REVOKE SELECT ON TABLES FROM <username>;
REVOKE SELECT ON ALL TABLES IN SCHEMA "some_schema" FROM <username>;

Repeat the following command for each table you want Fivetran to sync.
```
GRANT SELECT ON "some_schema"."some_table" TO <username>;
```
By default, any tables that you create in the future will not be accessible to the Fivetran user. To grant access to new tables, run the following command.
```
ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" GRANT SELECT ON TABLES TO <username>;
```

Restrict access to columns (optional)

If you want to limit Fivetran's access to the columns in your tables, grant the Fivetran user access to only certain columns. You must individually grant access for each column that you want to sync.

Revoke any previously granted permission to read all columns in the table.
```
REVOKE SELECT ON "some_schema"."some_table" FROM <username>;
```
Grant permission to the specific columns you want to sync (for example, some_column and other_column).
IMPORTANT: If you chose XMIN as your incremental sync method, you must grant us access to the hidden system column xmin. While it's not mandatory, you can also grant us access to the hidden system column ctid to make your initial sync faster.
```
GRANT SELECT (xmin, ctid, some_column, other_column) ON "some_schema"."some_table" TO <username>;
```

Once you restrict access to columns within a table, the Fivetran user will not have access to any new columns added to that table in the future. To grant access to new columns, you must rerun the command above.

Configure incremental sync method

Configure your chosen incremental sync method:

Logical replication

To enable logical replication, follow these steps:

Open a connection to your primary Amazon Aurora PostgreSQL database. You cannot enable logical replication on a read replica.
Ensure that your server has ample free space for the logs. As soon as Fivetran processes a log, we delete it. However, we don't delete logs if the sync is interrupted (for example, if we lose access to your database). In this case, logs may accumulate on your server and consume additional storage. The amount of additional disk space that these logs consume is proportional to the number of changes committed on the server. If we can't resume a lost connection quickly enough and you need more disk space, you can drop the replication slot, which deletes its unconsumed logs.
In your RDS dashboard, do the following:
i. Create a new parameter group (non-default group).
ii. Set the logical_replication parameter to 1.
iii. Set wal_sender_timeout parameter to 0.
iv. Apply the parameter group to the database.
vi. Wait until your database's parameter group status changes to pending-reboot, then reboot your database to apply the new parameter group.
Use a PostgreSQL console to log in to your primary database as a superuser (one that has the rds_superuser role).
Create a publication for your tables in your primary database. If you want, you can create a publication for only certain tables so that you add or remove tables from the publication later on. Only changes from tables in the publication are replicated to Fivetran. Each database can have multiple distinct publications. You must have CREATE privileges or above to run this command.
IMPORTANT: The publication name fivetran_pub quoted throughout this guide is used purely as an example. The actual publication name should be unique for every database and cannot start with a number.
```
CREATE PUBLICATION fivetran_pub FOR TABLE table2, table4, table8;
```
To add or remove a table from a publication, run the following command. You must have ownership rights over the table(s).
```
ALTER PUBLICATION fivetran_pub ADD/DROP TABLE table_name;
```
Alternatively, you can create a publication for all of your tables. However, you cannot remove any table from this publication later on. You must have superuser privileges to run this command.
```
 CREATE PUBLICATION fivetran_pub FOR ALL TABLES;
```
(Optional) You can choose which operations to include in the publication. For example, the following publication includes only INSERT and UPDATE operations.
```
CREATE PUBLICATION insert_only_pub FOR TABLE table1 WITH (publish = 'INSERT, UPDATE');
```
To add partitioned tables for PostgreSQL version 13 or later, run the following command to enable publish_via_partition_root.
```
CREATE PUBLICATION fivetran_pub FOR ALL TABLES WITH (publish_via_partition_root=true);
```
Create a logical replication slot for the database you want to sync by running the following command. You must use the standard output plugin pgoutput. Ensure that you are connected to the correct database when you create your replication slot, or your connector will not be able to find the slot.
IMPORTANT: You must create a unique replication slot for every connector that uses the same PostgreSQL cluster. Replication slot names cannot start with a number. (The replication slot name fivetran_pgoutput_slot quoted throughout this guide is used purely as an example.)
IMPORTANT: You need to create the replication slot after you have created the publication.
```
SELECT pg_create_logical_replication_slot('fivetran_pgoutput_slot', 'pgoutput');
```
Verify that your chosen tables are in the publication.
```
SELECT * FROM pg_publication_tables;
```
Grant the Fivetran user permission to read the replication slot.
```
GRANT rds_replication TO <username>;
```
Log in as the Fivetran user.
Verify that the Fivetran user can read the replication slot by running the following command. Replace fivetran_pgoutput_slot with your replication slot name and fivetran_pub with the publication name.
```
SELECT count(*) FROM pg_logical_slot_peek_binary_changes('fivetran_pgoutput_slot', null, null, 'proto_version', '1', 'publication_names', 'fivetran_pub');
```
If the query succeeds, then permissions are sufficient.

XMIN

You do not need to do any additional configuration for the XMIN method. However, we recommend that you filter frozen pages from incremental syncs. Filtering makes incremental syncs significantly shorter, which means that we use fewer resources in your source database.

(Optional) To filter frozen pages, run the following commands on your primary database as a superuser:

CREATE EXTENSION pg_visibility;

CREATE SCHEMA fivetran;

CREATE OR REPLACE FUNCTION fivetran.get_all_pages(v_table_name character varying)

RETURNS TABLE (
    pagenumber integer,
    all_visible_yn boolean,
    all_frozen_yn boolean)
LANGUAGE plpgsql
SECURITY definer

AS $function$
declare
begin
    RETURN QUERY
    SELECT blkno::int as pageNumber,
           all_visible as all_visible_yn,
           all_frozen as all_frozen_yn
    FROM pg_visibility_map($1::regclass);
END;
$function$;

GRANT USAGE ON SCHEMA fivetran TO <username>;

GRANT EXECUTE ON FUNCTION fivetran.get_all_pages TO <username>;

Fivetran Teleport Sync

If you are connecting with a standby or read replica, run the following SQL command on your primary database as the Fivetran user:

CREATE AGGREGATE BIT_XOR(IN v bigint) (SFUNC = int8xor, STYPE = bigint);

If you are not connecting with a read replica, you do not need to do any additional configuration. The aggregate that the Teleport mechanism will later use is automatically created for you.

Finish Fivetran configuration

In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the host field, enter the endpoint URL that you found in Step 4. Alternatively, you can enter your database host's IP (for example, 1.2.3.4).
Enter the port number that you found in Step 4.
Enter the Fivetran-specific user that you created in Step 5.
Enter the password for the Fivetran-specific user that you created in Step 5.
Enter the name of your database (for example, your_database).
(Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Hybrid Deployment Agent associated with your destination is pre-selected in the Select an existing agent drop-down menu. To use a different agent, select the agent of your choice, and then select the same agent for your destination.
(Not applicable to Hybrid Deployment) Choose your connection method. If you selected Connect via an SSH tunnel, copy or make a note of the Public Key and add it to the authorized_keys file while configuring the SSH tunnel, and provide the following information:
- SSH hostname (do not use a load balancer's IP address/hostname)
- SSH port
- SSH user
Choose your update method. If you selected Logical Replication, enter both the name of your database's replication slot and publication name accordingly.
Click Save & Test. Fivetran tests and validates our connection to your Amazon Aurora PostgreSQL database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your Amazon Aurora PostgreSQL database and that it is properly configured:

The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It then checks that we can connect to your database using the SSH Tunnel. (We skip this test if you aren't connecting using SSH.)
The Connecting to Host 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 Validating Certificate Test generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you selected an indirect connection method and then disabled the Require TLS through Tunnel toggle.)
NOTE: Aurora does not return the entire certificate chain when we query for it, so the root certificate may not be selectable during the Validating Certificate Test stage.
The Connecting to Database Test checks that we can access your database.
The Connecting to WAL Replication Slot Test confirms that the database associated with the replication slot matches the name you supplied in the setup form. It then verifies that the replication slot uses the pgoutput. Lastly, it makes sure that the Fivetran user has replication privileges. (We skip this test if you selected XMIN or Teleport as your incremental sync method)
The Checking Configuration Values Test checks a set of WAL-configured values against the recommended settings and detects if they are below the recommended range. (We skip this test if you selected XMIN as your incremental sync method.)
The Publication Test verifies that the supplied publication name exists in your database.
The Validating Speed Setup test validates Fivetran can fetch data from your source database quickly enough. During this test, we measure our ability to download sample data from your source database to Fivetran, but we do not perform a historical sync. We start a timer, then download the sample data in memory. We then calculate the connector speed based on how much data we downloaded and how long it took to download. The test shows a warning if the download speed is less than 5MB/sec.
The XMIN Extensions test checks that the correct extensions are enabled for XMIN. (This test is skipped if you select Logical Replication as your incremental sync method.)