Replicate Data with Estuary Flow

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

Estuary Flow is a real-time data streaming platform that allows you to connect, transform, and move data from various sources to destinations with sub-100ms latency.

In this guide, you will learn how to configure a Postgres source connector in Estuary Flow for ingesting changes from your Neon database, enabling you to replicate data from Neon to any of Estuary Flow's supported destinations, with optional transformations along the way.

Prerequisites

• An Estuary Flow account (start free, no credit card required)
• 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 Project settings.
3. Select Beta.
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 Console, CLI, or API are granted membership in the neon_superuser role, which has the required REPLICATION privilege.

neon roles create --name cdc_role

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. Run these commands for each schema:

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

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 a publication with the name estuary_publication. Include all the tables you would like to ingest into Estuary Flow.

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

Refer to the Postgres docs if you need to add or remove tables from your publication. Alternatively, you also can create a publication FOR ALL TABLES.

Upon startup, the Estuary Flow connector for Postgres will automatically create the replication slot required for ingesting data change events from Postgres. The slot's name will be prefixed with estuary_, followed by a unique identifier.

Allow Inbound Traffic

If you are using Neon's IP Allow feature to limit the IP addresses that can connect to Neon, you will need to allow inbound traffic from Estuary Flow's IP addresses. Refer to the Estuary Flow documentation for the list of IPs that need to be allowlisted for the Estuary Flow region of your account. For information about configuring allowed IPs in Neon, see Configure IP Allow.

Create a Postgres Source Connector in Estuary Flow

1. In the Estuary Flow web UI, select Sources from the left navigation bar and click New Capture.

2. In the connector catalog, choose Neon PostgreSQL and click Connect.

3. 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. Your connection string will look like this:

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

   

   Enter the details for your connection string into the source connector fields. Based on the sample connection string above, the values would be specified as shown below. Your values will differ.

   • Name:: Name of the Capture connector
   • Server Address: ep-cool-darkness-123456.us-east-2.aws.neon.tech:5432
   • User: cdc_role
   • Password: Click Add a new secret..., then specify a name for that secret and AbC123dEf as its value
   • Database: dbname

   

4. Click Next. Estuary Flow will now scan the source database for all the tables that can be replicated. Select one or more tables by checking the checkbox next to their name. Optionally, you can change the name of the destination name for each table. You can also take a look at the schema of each stream by clicking on the Collection tab.

   

5. Click Save and Publish to provision the connector and kick off the automated backfill process.

Previewing the Data

Once the connector is up and running state, navigate to the Collections page in the Estuary Flow dashboard and click on the collection being filled by your capture.