Amazon RDS PostgreSQL Setup Guide

Logical replication

IMPORTANT: Because of AWS limitations, you can only use logical replication if you connect Fivetran to your Amazon RDS PostgreSQL master instance. You can only enable logical replication with the test_decoding plugin if your PostgreSQL server version is 9.4 - 13.2. You can only enable logical replication with the pgoutput plugin if your PostgreSQL version is 10.4 or later. Prior minor versions have breaking bugs.

Logical replication is based on logical decoding of the PostgreSQL write-ahead log (WAL). Fivetran reads the WAL to detect any new or changed data. We recommend using logical replication with the test_decoding plugin because it is faster than XMIN replication and allows Fivetran to detect deleted rows for tables with primary keys.

Learn more in our logical replication documentation.

XMIN

NOTE: You can use XMIN for any Amazon RDS PostgreSQL deployment, including a read replica.

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.

Connect directly

Fivetran connects directly to your Amazon RDS PostgreSQL database. This is the simplest and most secure 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 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 to give Fivetran access to your SSH tunnel.

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.

1. In your Amazon RDS dashboard, click on the database instance you want to connect to Fivetran.

   

2. In the Connectivity & security section, find the database’s port number and make a note of it. You will need the port number to configure Fivetran.

   

3. In the Security column, click the link to the database instance’s security group.

   

4. In the Security Group panel, click Actions, then select Edit inbound rules from the drop-down menu.

   

5. Click Add Rule. This creates a new Custom TCP Rule at the bottom of the list.

6. Fill in the new Custom TCP Rule.

   • In the Port Range field, enter your database instance’s port number. If you created a new read replica for Fivetran, this is the port number that you found in step 3 of this section. (The port will be 5432 for direct connections, unless you changed the default.)
   • 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.

   

7. Click Save rules.

Configure network ACLs (VPC only)

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

1. Return to the RDS dashboard.

2. Click on your database instance.

   

3. Click the link to the instance’s VPC.

   

4. Select the VPC ID.

   

5. In the Summary tab, click the Network ACL link.

   

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

Edit inbound rules

1. Select Inbound Rules.

   

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

   

3. If your inbound rules don’t include 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 ACLs 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

1. Select Outbound Rules.

   

2. If you have a default VPC that was automatically created by AWS, the settings already allow all outbound traffic. To verify that the settings allow outbound traffic, confirm that the Destination value is 0.0.0.0/0 and that the ALLOW entry is listed above the DENY entry.

   

3. 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 the following Destinations:

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

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.

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

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.

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

   NOTE: If you chose XMIN as your incremental update mechanism, you must grant us access to the hidden system column xmin.

   GRANT SELECT (xmin, "some_column", "other_column") ON "some_schema"."some_table" TO fivetran;
   

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 with the test_decoding plugin

To enable logical replication with the test_decoding plugin, follow these steps:

1. Go to your Amazon RDS PostgreSQL master database. You cannot enable logical replication on a read replica.

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. Log in to a PostgreSQL console (such as a SQL workbench or psql) as a superuser. Superusers have the rds_superuser role.

5. Create a logical replication slot for the database you want to sync by running the following command. You must use the output plugin test_decoding supplied in the postgresql-contrib subpackage.

   IMPORTANT: The replication slot name fivetran_replication_slot quoted throughout this guide is used purely as an example. The actual replication slot name should be unique for every connector using the same PostgreSQL cluster. Replication slot names cannot start with a number.

   SELECT pg_create_logical_replication_slot('fivetran_replication_slot', 'test_decoding');
   

6. Grant permission to the Fivetran user for reading the replication slot.

   GRANT rds_replication TO <username>; 
   

   NOTE: The Fivetran user does not need the rds_superuser role.

7. Log in as the Fivetran user.

8. Verify that the Fivetran user can read the replication slot by running the following command:

   SELECT count(*) FROM pg_logical_slot_peek_changes('fivetran_replication_slot', null, null);
   

   If the query succeeds, then permissions are sufficient.

IMPORTANT: You must periodically tune the checkpoint_timeout and max_wal_size parameters based on your PostgreSQL database operations. If you do not, you may experience replication failures. To learn how to tune, read this tuning checkpoints documentation.

Logical replication with the pgoutput plugin Beta

To enable logical replication with the pgoutput plugin, follow these steps:

1. Go to your Amazon RDS PostgreSQL master database. You cannot enable logical replication on a read replica.

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. Log in to a PostgreSQL console (such as a SQL workbench or psql) as a superuser. Superusers have the rds_superuser role.

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

   IMPORTANT: The replication slot name fivetran_pgoutput_slot quoted throughout this guide is used purely as an example. The actual replication slot name should be unique for every connector using the same PostgreSQL cluster. Replication slot names cannot start with a number.

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

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

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

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.

   ALTER ROLE <username> WITH REPLICATION;
   

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.

IMPORTANT: You must periodically tune the checkpoint_timeout and max_wal_size parameters based on your PostgreSQL database operations. If you do not, you may experience replication failures. To learn how to tune, read this tuning checkpoints documentation.

XMIN

You do not need to do any additional configuration for the XMIN method.