Amazon RDS PostgreSQL Setup Guide

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.

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 using SSH, you will configure your SSH 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

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.

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.

1. In your Amazon RDS Dashboard, select the PostgreSQL instance you want to replicate.

2. In the instance details page, click Actions > Create read replica.

3. In DB instance identifier, enter a unique name for the replica (for example, pg-read-fivetran).

4. In DB instance class, choose a size appropriate for expected read load (it can be smaller than the primary).

5. In Connectivity > Public access:

   • Select Publicly accessible only if you chose to connect directly.
   • Select Not publicly accessible if you chose to connect using SSH, AWS PrivateLink, or Proxy Agent.

   

6. In Additional configuration > DB parameter group, attach (or create and attach) a parameter group that sets max_standby_streaming_delay to 15–30 minutes (900000–1800000 ms). This ensures that import/incremental queries complete before the replica server cancels them.

7. At the bottom of the page, click Create read replica and wait for the status to change from creating to available.

If you prefer to limit Fivetran's access to your tables, grant permissions only on the tables that you want to sync. You must grant access table by table; you cannot grant access to a schema and then selectively revoke permissions for a subset of tables.

1. Ensure that the Fivetran user has access to the schema that contains your table(s).

   GRANT USAGE ON SCHEMA "some_schema" TO <username>;
   

2. Revoke any previously granted permissions 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>;
   

3. Repeat the following command for each table you want Fivetran to sync.

   GRANT SELECT ON "some_schema"."some_table" TO <username>;
   

4. By default, any tables that you create in the future will be excluded from the Fivetran user's access. To grant access to new tables, run the following command.

   ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" GRANT SELECT ON TABLES TO <username>;
   

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.

1. Ensure that you have revoked any previously granted permission to read all columns in the table.

   REVOKE SELECT ON "some_schema"."some_table" FROM fivetran;
   

2. Grant permission to the specific columns you want to sync (for example, some_column and other_column).

   If you chose Query-Based as your incremental sync method, you must grant us access to the hidden system columns xmin and ctid. This speeds up your initial sync and enables capturing deletes.

   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.

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.

To enable logical replication, follow these steps:

1. Go to your Amazon RDS PostgreSQL database.

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

3. Set the rds.logical_replication parameter to 1 by following these steps:
   i. Create a new parameter group (non-default group).

   ii. Enable the logical_replication flag in that group by setting the value to 1.

   iii. Set wal_sender_timeout to 0.

   iv. Apply the parameter group to the database.

   

   v. Wait until the status changes to pending-reboot, then reboot the database to apply the new parameter group.

   

4. Use a PostgreSQL console (such as a SQL workbench or psql) to log in to your primary database as a superuser. Superusers have the rds_superuser role.

5. Create a publication for your tables. 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.

   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');
   

   (Optional) 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);
   

6. Create a logical replication slot by running the following command. You must use the standard output plugin pgoutput. Ensure you are connected to the correct database when creating the replication slot; otherwise, your connection will not be able to locate it.

   You must create a unique replication slot for every connection 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.)

   You need to create the replication slot after you have created the publication.

   If your PostgreSQL server version is 16 or later and you want to sync from a standby, create the replication slot based on your deployment type:

   • For a cluster deployment, create the replication slot on the primary.
   • For a non-cluster deployment, create the replication slot on the standby.

   SELECT pg_create_logical_replication_slot('fivetran_pgoutput_slot', 'pgoutput');
   

7. Verify that your chosen tables are in the publication.

   SELECT * FROM pg_publication_tables;
   

8. Grant the Fivetran user permission to read the replication slot.

   GRANT rds_replication TO <username>;
   

9. Log in as the Fivetran user.

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

The Query-Based method is based on the hidden xmin and ctid system columns that are present in all PostgreSQL tables. With Query-Based replication, Fivetran must scan every table in full to detect updated data. We do not recommend Query-Based for near real-time data needs because Query-Based replication is slower than logical replication.

Learn more in our Query-Based documentation.

You do not need to do any additional configuration for the Query-Based 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 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.

If you are trying to connect 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.