PostgreSQL CDC - Materialize

Materialize can ingest data from PostgreSQL (11+) databases using PostgreSQL’s native logical replication protocol for Change Data Capture (CDC). This allows you to continuously stream INSERT, UPDATE, and DELETE operations from your PostgreSQL database into Materialize.

Prerequisites

Before creating a PostgreSQL source, you must:

Enable logical replication in your PostgreSQL database
Create a publication for the tables you want to replicate
Create a dedicated replication user with appropriate permissions
Configure network access to allow Materialize to connect

Logical replication must be enabled before you can create a PostgreSQL source. This typically requires a database restart.

Step 1: Configure PostgreSQL

Enable Logical Replication

Check if logical replication is enabled:

-- In PostgreSQL
SHOW wal_level;

If wal_level is not set to logical, update your postgresql.conf:

wal_level = logical

Restart PostgreSQL for the change to take effect.

Create a Publication

Create a publication for the tables you want to replicate:

-- In PostgreSQL: Create publication for specific tables
CREATE PUBLICATION mz_source FOR TABLE table1, table2, table3;

-- Or for all tables in the database
CREATE PUBLICATION mz_source FOR ALL TABLES;

Create a Replication User

Create a dedicated user with replication permissions:

-- In PostgreSQL
CREATE USER materialize WITH REPLICATION PASSWORD '<password>';

-- Grant permissions on the publication
GRANT SELECT ON table1, table2, table3 TO materialize;
GRANT USAGE ON SCHEMA public TO materialize;

-- For all tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO materialize;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO materialize;

Step 2: Configure Network Security

Choose a network security option based on your deployment:

AWS PrivateLink
SSH Tunnel
Static IP Allowlist

AWS PrivateLink provides secure, private connectivity between Materialize and your PostgreSQL database in AWS.1. Create a target group for your PostgreSQL instance:

Target type: IP address
Protocol: TCP
Port: 5432 (or your custom port)

2. Create a Network Load Balancer in the same VPC as your PostgreSQL instance3. Create a TCP listener that forwards to your target group4. Create a VPC endpoint service and associate it with the NLB5. In Materialize, create the PrivateLink connection:

CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
    SERVICE NAME 'com.amazonaws.vpce.us-east-1.vpce-svc-0e123abc123198abc',
    AVAILABILITY ZONES ('use1-az1', 'use1-az4')
);

6. Retrieve the AWS principal:

SELECT principal
FROM mz_aws_privatelink_connections plc
JOIN mz_connections c ON plc.id = c.id
WHERE c.name = 'privatelink_svc';

7. Configure your VPC endpoint service to accept connections from the AWS principal

Use an SSH tunnel to connect to PostgreSQL through a bastion host.1. Get Materialize egress IPs:

SELECT * FROM mz_egress_ips;

2. Configure your bastion host to allow SSH connections from those IPs3. Create the SSH tunnel connection:

CREATE CONNECTION ssh_connection TO SSH TUNNEL (
    HOST 'bastion-host.example.com',
    PORT 22,
    USER 'materialize'
);

4. Get the public keys:

SELECT public_key_1, public_key_2
FROM mz_ssh_tunnel_connections
WHERE name = 'ssh_connection';

5. Add both public keys to ~/.ssh/authorized_keys on your bastion host

Allow connections from Materialize’s static egress IP addresses.1. Get Materialize egress IPs:

SELECT * FROM mz_egress_ips;

2. Update your PostgreSQL firewall/security group to allow connections from those IP addresses on port 5432

Step 3: Create a Connection in Materialize

Direct Connection
With AWS PrivateLink
With SSH Tunnel

CREATE SECRET pgpass AS '<POSTGRES_PASSWORD>';

CREATE CONNECTION pg_connection TO POSTGRES (
    HOST 'instance.foo000.us-west-1.rds.amazonaws.com',
    PORT 5432,
    USER 'materialize',
    PASSWORD SECRET pgpass,
    SSL MODE 'require',
    DATABASE 'postgres'
);

CREATE SECRET pgpass AS '<POSTGRES_PASSWORD>';

CREATE CONNECTION pg_connection TO POSTGRES (
    HOST 'instance.foo000.us-west-1.rds.amazonaws.com',
    PORT 5432,
    USER 'materialize',
    PASSWORD SECRET pgpass,
    AWS PRIVATELINK privatelink_svc,
    DATABASE 'postgres'
);

CREATE SECRET pgpass AS '<POSTGRES_PASSWORD>';

CREATE CONNECTION pg_connection TO POSTGRES (
    HOST 'internal-postgres.vpc.local',
    PORT 5432,
    USER 'materialize',
    PASSWORD SECRET pgpass,
    SSH TUNNEL ssh_connection,
    DATABASE 'postgres'
);

Step 4: Create a Source

All Tables
Specific Schemas
Specific Tables

Create subsources for all tables in the publication:

CREATE SOURCE pg_source
    FROM POSTGRES CONNECTION pg_connection
    (PUBLICATION 'mz_source')
    FOR ALL TABLES;

Create subsources for all tables from specific schemas:

CREATE SOURCE pg_source
    FROM POSTGRES CONNECTION pg_connection
    (PUBLICATION 'mz_source')
    FOR SCHEMAS (public, analytics);

Create subsources for specific tables only:

CREATE SOURCE pg_source
    FROM POSTGRES CONNECTION pg_connection
    (PUBLICATION 'mz_source')
    FOR TABLES (users, orders, products AS inventory);

Handling Unsupported Types

If your tables contain unsupported data types, use the TEXT COLUMNS option:

CREATE SOURCE pg_source
    FROM POSTGRES CONNECTION pg_connection (
        PUBLICATION 'mz_source',
        TEXT COLUMNS (table_name.column_with_unsupported_type)
    )
    FOR ALL TABLES;

How It Works

Replication Slots

Materialize automatically creates a replication slot in PostgreSQL for each source. The replication slot name is prefixed with materialize_ for easy identification.

-- Find the replication slot name
SELECT id, replication_slot
FROM mz_internal.mz_postgres_sources;

   id   |             replication_slot
--------+----------------------------------------------
  u8     | materialize_7f8a72d0bf2a4b6e9ebc4e61ba769b71

Each source uses a single replication slot for all tables in the publication, minimizing the performance impact on PostgreSQL.

Subsources

Materialize automatically creates a subsource for each table in the publication:

SHOW SOURCES;

         name         |   type
----------------------+-----------
 pg_source            | postgres
 pg_source_progress   | progress
 users                | subsource
 orders               | subsource
 products             | subsource

Progress Tracking

Monitor ingestion progress using the progress subsource:

SELECT lsn
FROM pg_source_progress;

The lsn (Log Sequence Number) should increase as Materialize consumes new WAL records from PostgreSQL.

Monitoring

Check Source Status

SELECT *
FROM mz_internal.mz_source_statuses
WHERE name = 'pg_source';

Monitor Snapshotting Progress

SELECT
    name,
    snapshot_committed,
    bytes_received,
    messages_received
FROM mz_internal.mz_source_statistics s
JOIN mz_sources src ON s.id = src.id
WHERE src.name = 'pg_source';

Monitor Replication Lag

In PostgreSQL, check replication slot lag:

-- In PostgreSQL
SELECT
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_name LIKE 'materialize_%';

Schema Changes

Compatible Changes

Materialize automatically handles these schema changes:

Adding a column with a DEFAULT value
Adding a nullable column
Dropping a column
Changing column default values

Incompatible Changes

These changes require manual intervention:

Changing a column’s data type
Adding a NOT NULL constraint
Renaming columns or tables

To handle incompatible changes:

-- 1. Drop the affected subsource
DROP SOURCE users;

-- 2. Add it back to the source
ALTER SOURCE pg_source ADD SUBSOURCE users;

Handling Errors

Source in Error State

Check the error message:

SELECT error
FROM mz_internal.mz_source_statuses
WHERE name = 'pg_source';

Common issues:

Replication slot deleted: Recreate the source
Connection refused: Check network security settings
Authentication failed: Verify credentials and permissions
Publication not found: Ensure the publication exists in PostgreSQL

Adding Subsources After Errors

After fixing schema issues:

-- List all subsources
SHOW SUBSOURCES ON pg_source;

-- Drop the errored subsource
DROP SOURCE table_name;

-- Add it back with the updated schema
ALTER SOURCE pg_source ADD SUBSOURCE table_name;

Best Practices

Resource Management

Create a dedicated cluster for PostgreSQL sources:

CREATE CLUSTER postgres_ingest SIZE = '100cc';

SET CLUSTER = postgres_ingest;

CREATE SOURCE pg_source FROM ...

For large initial snapshots, use a larger cluster temporarily:

-- Before creating the source
ALTER CLUSTER postgres_ingest SET (SIZE = '200cc');

-- After snapshotting completes
ALTER CLUSTER postgres_ingest SET (SIZE = '100cc');

Limit Data Volume

Create publications for specific tables, not all tables
Use schema filtering to limit subsources
Only replicate tables you need in Materialize

Publication Management

If you add or remove tables from the PostgreSQL publication, Materialize will not automatically detect these changes. You must manually add or drop subsources.

WAL Retention

Ensure PostgreSQL retains WAL files long enough:

-- In PostgreSQL
SHOW wal_keep_size;  -- or wal_keep_segments for older versions

If Materialize is offline for too long, the replication slot may fall behind and you’ll need to recreate the source.

Troubleshooting

Source Not Progressing

Check replication slot status in PostgreSQL:

-- In PostgreSQL
SELECT
    slot_name,
    active,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_name LIKE 'materialize_%';

Check for replication conflicts:

-- In PostgreSQL
SELECT * FROM pg_stat_replication;

High Memory Usage

For tables with large row sizes or high update rates:

Increase cluster size
Limit columns using table-specific filtering
Consider using separate sources for high-volume tables

Snapshotting Takes Too Long

Scale up the cluster temporarily during snapshotting
Reduce data volume by limiting tables or using filters
Run during off-peak hours to minimize database load
Monitor progress using mz_source_statistics

Connection Issues

Test connectivity from Materialize:

-- This will fail if there are connectivity issues
VALIDATE CONNECTION pg_connection;

Common solutions:

Verify firewall rules allow connections from Materialize IPs
Check that SSL/TLS settings match PostgreSQL configuration
Ensure the database user has replication permissions
Verify the PostgreSQL server is accepting replication connections

Supported PostgreSQL Services

Materialize supports PostgreSQL CDC from:

Amazon RDS for PostgreSQL
Amazon Aurora PostgreSQL
Google Cloud SQL for PostgreSQL
Azure Database for PostgreSQL
Self-hosted PostgreSQL
AlloyDB for PostgreSQL
Neon

Some managed PostgreSQL services require additional configuration steps. Consult your provider’s documentation for enabling logical replication.

Get Started

Core Concepts

Ingesting Data

Transform Data

Serve Results

Operations

Integrations

​Prerequisites

​Step 1: Configure PostgreSQL

​Enable Logical Replication

​Create a Publication

​Create a Replication User

​Step 2: Configure Network Security

​Step 3: Create a Connection in Materialize

​Step 4: Create a Source

​Handling Unsupported Types

​How It Works

​Replication Slots

​Subsources

​Progress Tracking

​Monitoring

​Check Source Status

​Monitor Snapshotting Progress

​Monitor Replication Lag

​Schema Changes

​Compatible Changes

​Incompatible Changes

​Handling Errors

​Source in Error State

​Adding Subsources After Errors

​Best Practices

​Resource Management

​Limit Data Volume

​Publication Management

​WAL Retention

​Troubleshooting

​Source Not Progressing

​High Memory Usage

​Snapshotting Takes Too Long

​Connection Issues

​Supported PostgreSQL Services

​Next Steps

Transform Data

MySQL CDC

Build docs developers (and LLMs) love

Prerequisites

Step 1: Configure PostgreSQL

Enable Logical Replication

Create a Publication

Create a Replication User

Step 2: Configure Network Security

Step 3: Create a Connection in Materialize

Step 4: Create a Source

Handling Unsupported Types

How It Works

Replication Slots

Subsources

Progress Tracking

Monitoring

Check Source Status

Monitor Snapshotting Progress

Monitor Replication Lag

Schema Changes

Compatible Changes

Incompatible Changes

Handling Errors

Source in Error State

Adding Subsources After Errors

Best Practices

Resource Management

Limit Data Volume

Publication Management

WAL Retention

Troubleshooting

Source Not Progressing

High Memory Usage

Snapshotting Takes Too Long

Connection Issues

Supported PostgreSQL Services

Next Steps