Replicate data with Fivetran

Neon's logical replication feature allows you to replicate data from your Neon Postgres database to external destinations.

Fivetran is an automated data movement platform that helps you centralize data from disparate sources, which you can then manage directly from your browser. Fivetran extracts your data and loads it into your data destination.

In this guide, you will learn how to define a Neon Postgres database as a data source in Fivetran so that you can replicate data to one or more of Fivetran's supported destinations.

Prerequisites

• A Fivetran account
• A Neon account
• Read the important notices about logical replication in Neon before you begin

Enable logical replication in Neon

To enable logical replication in Neon:

1. Select your project in the Neon Console.
2. On the Neon Dashboard, select Settings.
3. Select Logical Replication.
4. Click Enable to enable logical replication.

You can verify that logical replication is enabled by running the following query from the Neon SQL Editor:

SHOW wal_level;
 wal_level
-----------
 logical

Create a Postgres role for replication

It is recommended that you create a dedicated Postgres role for replicating data. The role must have the REPLICATION privilege. The default Postgres role created with your Neon project and roles created using the Neon CLI, Console, or API are granted membership in the neon_superuser role, which has the required REPLICATION privilege.

neon roles create --name replication_user

Grant schema access to your Postgres role

If your replication role does not own the schemas and tables you are replicating from, make sure to grant access. For example, the following commands grant access to all tables in the public schema to Postgres role replication_user:

GRANT USAGE ON SCHEMA public TO replication_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replication_user;

Granting SELECT ON ALL TABLES IN SCHEMA instead of naming the specific tables avoids having to add privileges later if you add tables to your publication.

Create a publication

Create the Postgres publication. Include all tables you want to replicate as part of the publication:

CREATE PUBLICATION fivetran_pub FOR TABLE <tbl1, tbl2, tbl3>;

The publication name is customizable. Refer to the Postgres docs if you need to add or remove tables from your publication.

Create a replication slot

Fivetran requires a dedicated replication slot. Only one source should be configured to use this replication slot.

Fivetran uses the pgoutput plugin in Postgres for decoding WAL changes into a logical replication stream. To create a replication slot called fivetran_slot that uses the pgoutput plugin, run the following command on your database using your replication role:

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

The name assigned to the replication slot is fivetran_pgoutput_slot. You will need to provide this name when you set up your Fivetran source.

Create a Postgres source in Fivetran

1. Log in to your Fivetran account.

2. On the Select your datasource page, search for the PostgreSQL source and click Set up.

3. In your connector setup form, enter a value for Destination Schema Prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created. In this example, we'll use neon as the prefix.

4. Enter the connection details for your Neon database. You can get these details from your Neon connection string, which you'll find in the Connection Details widget on the Dashboard of your Neon project. For example, let's say this is your connection string:

   postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require

   From this string, the values in the Fivetran Create a source dialog would show as below. Your actual values will differ, with the exception of the port number.

   • Host: ep-cool-darkness-123456.us-east-2.aws.neon.tech
   • Port: 5432
   • Username: alex
   • Password: AbC123dEf
   • Database Name: dbname

5. For Connection Method, select Logical replication of the WAL using the pgoutput plugin and enter values for the Replication Slot and Publication Name. You deifned these values earlier (fivetran_pgoutput_slot and fivetran_pub, respectively).

   

6. If you are using Neon's IP Allow feature to limit IP addresses that can connect to Neon, add Fivetran's IPs to your allowlist in Neon.

   

   For instructions, see Configure IP Allow. You'll need to do this before you can validate your connection in the next step. If you are not using Neon's IP Allow feature, you can skip this step.

7. Click Save & Test. Fivetran tests and validates the connection to your database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

   During the test, Fivetran asks you to confirm the certificate chain by selecting the certificate to use as the trust anchor. Select the CN=ISRG Root X1, 0=Internet Security Research Group, C=US option. This certificate is valid unitl until 2035-06-04.

   When the connection test is completed, you should see an All connection tests passed! message in Fivetran, as shown below:

   

8. Click Continue.

9. On the Select Data to Sync page, review the connector schema and select any columns you want to block or hash.

   

10. Click Save & Continue.

11. On the How would you like to handle changes? page, specify how you would like to handle future schema changes. For this example, we'll select We will allow all new schemas, tables and columns. Choose the option that best fits your organization's requirements.

    

12. Click Continue. Your data is now ready to sync.

    

13. Click Start Initial Sync to enable syncing.

References

• Fivetran Generic PostgreSQL Setup Guide