Ingest data from Neon - Materialize

Neon is a fully managed serverless PostgreSQL provider. It separates compute and storage to offer features like autoscaling, branching and bottomless storage.

This page shows you how to stream data from a Neon database to Materialize using the PostgreSQL source.

Before you begin

Make sure you have a Neon account.
Make sure you have access to your Neon instance via psql or the SQL editor in the Neon Console.

A. Configure Neon

The steps in this section are specific to Neon. You can run them by connecting to your Neon database using a psql client or the SQL editor in the Neon Console.

1. Enable logical replication

{{< warning >}} Enabling logical replication applies globally to all databases in your Neon project, and cannot be reverted. It also restarts all computes, which means that any active connections are dropped and have to reconnect. {{< /warning >}}

Materialize uses PostgreSQL's logical replication protocol to track changes in your database and propagate them to Materialize.

As a first step, you need to make sure logical replication is enabled in Neon.

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

You can verify that logical replication is enabled by running:

sql

SHOW wal_level;

The result should be:

 wal_level
-----------
 logical

2. Create a publication and a replication user

Once logical replication is enabled, the next step is to create a publication with the tables that you want to replicate to Materialize. You'll also need a user for Materialize with sufficient privileges to manage replication.

For each table that you want to replicate to Materialize, set the replica identity to FULL:
postgres
```
ALTER TABLE <table1> REPLICA IDENTITY FULL;
```
postgres
```
ALTER TABLE <table2> REPLICA IDENTITY FULL;
```
REPLICA IDENTITY FULL ensures that the replication stream includes the previous data of changed rows, in the case of UPDATE and DELETE operations. This setting enables Materialize to ingest Neon data with minimal in-memory state. However, you should expect increased disk usage in your Neon database.
Create a publication with the tables you want to replicate:

For specific tables:
postgres
```
CREATE PUBLICATION mz_source FOR TABLE <table1>, <table2>;
```
For all tables in the database:
postgres
```
CREATE PUBLICATION mz_source FOR ALL TABLES;
```
The mz_source publication will contain the set of change events generated from the specified tables, and will later be used to ingest the replication stream.

Be sure to include only the tables you need. If the publication includes additional tables, Materialize will waste resources on ingesting and then immediately discarding the data.
Create a dedicated user for Materialize, if you don't already have one. The default user created with your Neon project and users created using the Neon CLI, Console or API are granted membership in the neon_superuser role, which has the required REPLICATION privilege.

While you can use the default user for replication, we recommend creating a dedicated user for security reasons.

{{< tabs >}} {{< tab "Neon CLI">}}

Use the roles create CLI command to create a new role.

bash

neon roles create --name materialize

Navigate to the Neon Console.
Select a project.
Select Branches.
Select the branch where you want to create the role.
Select the Roles & Databases tab.
Click Add Role.
In the role creation dialog, specify the role name as "materialize".
Click Create. The role is created, and you are provided with the password for the role.

Use the roles endpoint to create a new role.

bash

curl 'https://console.neon.tech/api/v2/projects/<project_id>/branches/<branch_id>/roles' \
-H 'Accept: application/json' \
-H "Authorization: Bearer $NEON_API_KEY" \
-H 'Content-Type: application/json' \
-d '{
"role": {
    "name": "materialize"
}
}' | jq

{{< /tabs >}}

4. Grant the user the required permissions on the schema(s) you want to replicate:

postgres

GRANT USAGE ON SCHEMA public TO materialize;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO materialize;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO materialize;

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

B. (Optional) Configure network security

{{< note >}} If you are prototyping and your Neon instance is publicly accessible, you can skip this step. For production scenarios, we recommend using IP Allow to limit the IP addresses that can connect to your Neon instance. {{</ note >}}

If you use Neon's IP Allow feature to limit the IP addresses that can connect to your Neon instance, you will need to allow inbound traffic from Materialize IP addresses.

In the Materialize console's SQL Shell, or your preferred SQL client connected to Materialize, run the following query to find the static egress IP addresses, for the Materialize region you are running in:
mzsql
```
SELECT * FROM mz_egress_ips;
```
In your Neon project, add the IPs to your IP Allow list:
1. Select your project in the Neon Console.
2. On the Neon Dashboard, select Settings.
3. Select IP Allow.
4. Add each Materialize IP address to the list.

If you use Neon's IP Allow feature to limit the IP addresses that can connect to your Neon instance, you will need to allow inbound traffic from Materialize IP addresses.

In your Neon project, add the IPs to your IP Allow list:
1. Select your project in the Neon Console.
2. On the Neon Dashboard, select Settings.
3. Select IP Allow.
4. Add Materialize IP addresses to the list.

C. Ingest data in Materialize

The steps in this section are specific to Materialize. You can run them in the Materialize console's SQL Shell or your preferred SQL client connected to Materialize.

1. (Optional) Create a cluster

{{< note >}} If you are prototyping and already have a cluster to host your PostgreSQL source (e.g. quickstart), you can skip this step. For production scenarios, we recommend separating your workloads into multiple clusters for resource isolation. {{< /note >}}

{{% include-from-yaml data="ingest_postgres" name="create-a-cluster" %}}

2. Create a connection

Once you have configured your network, create a connection in Materialize per your networking configuration.

Run the CREATE SECRET command to securely store the password for the materialize PostgreSQL user you created earlier:
mzsql
```
CREATE SECRET pgpass AS '<PASSWORD>';
```
You can access the password for your Neon user from the Connection Details widget on the Neon Dashboard.
Use the CREATE CONNECTION command to create a connection object with access and authentication details for Materialize to use:
mzsql
```
CREATE CONNECTION pg_connection TO POSTGRES (
  HOST '<host>',
  PORT 5432,
  USER '<user_name>',
  PASSWORD SECRET pgpass,
  SSL MODE 'require',
  DATABASE '<database>'
);
```
You can find the connection details for your replication user in the Connection Details widget on the Neon Dashboard. A Neon connection string looks like this:
bash
```
postgresql://materialize:[email protected]/dbname?sslmode=require
```
- Replace <host> with your Neon hostname (e.g., ep-cool-darkness-123456.us-east-2.aws.neon.tech).
- Replace <role_name> with the dedicated replication user (e.g., materialize).
- Replace <database> with the name of the database containing the tables you want to replicate to Materialize (e.g., dbname).

3. Start ingesting data

{{% include-example file="examples/ingest_data/postgres/create_source_cloud" example="ingest-data-step" %}}

4. Monitor the ingestion status

{{% include-from-yaml data="ingest_postgres" name="check-the-ingestion-status" %}}

5. Right-size the cluster

{{% include-from-yaml data="ingest_postgres" name="right-size-the-cluster" %}}

D. Explore your data

{{% include-from-yaml data="ingest_postgres" name="next-steps" %}}

Considerations

{{% include-from-yaml data="postgres_source_details" name="postgres-considerations" %}}