PostgreSQL

This guide shows how to continuously sync a table, view, or materialized view from Materialize into PostgreSQL. mz-catalog-sync is a reference implementation that reads a SUBSCRIBE stream with ENVELOPE UPSERT and applies each change to a destination table in PostgreSQL. Despite its name, the sync logic isn’t specific to mz_catalog objects: it only needs a relation name, a destination table, and a key column, so the same program syncs any relation you own alongside the catalog objects it ships with by default.

Use this pattern when you need your data queryable from PostgreSQL itself, for example to hand a servicing layer to a team that only speaks PostgreSQL, or to plug into tooling that expects a PostgreSQL source.

Before you begin

• Python 3.13 or later and uv on the host that runs the sync.
• A PostgreSQL database to sync into.
• Materialize SQL credentials to run SUBSCRIBE.

Step 1. Get the sync program

git clone https://github.com/MaterializeInc/mz-catalog-sync.git
cd mz-catalog-sync
uv sync

Step 2. Create the destination schema

The sync tracks per-object progress in a catalog_freshness table, and writes each synced relation to its own destination table. Create both in PostgreSQL:

CREATE TABLE catalog_freshness (
    mz_environment_id TEXT,
    catalog_object    TEXT,
    freshness         BIGINT,
    PRIMARY KEY (mz_environment_id, catalog_object)
);

For each relation you want to sync, add a destination table. Give its columns the same native types as the Materialize columns you’re syncing (don’t default to TEXT for everything), plus mz_environment_id and deleted_at bookkeeping columns:

CREATE TABLE mz_environment_items (
    mz_environment_id TEXT,
    id                INTEGER,
    name              TEXT,
    price             NUMERIC,
    deleted_at        BIGINT,
    PRIMARY KEY (mz_environment_id, id)
);

mz_environment_id lets you sync the same relation from more than one Materialize environment into a shared table without collisions. Deletes are soft: a deleted row keeps its columns and gets deleted_at set to the Materialize timestamp of the delete, rather than being removed. Filter WHERE deleted_at IS NULL when you query the destination table directly.

Step 3. Configure the relations to sync

Open main.py and add an entry to CATALOG_OBJECTS for each relation, next to the catalog objects it ships with by default:

CatalogObject(
    mz_name="public.items",
    pg_name="mz_environment_items",
    columns=["id", "name", "price"],
    keys=["id"],
),

• mz_name is the fully qualified Materialize relation to read from: a table, view, or materialized view.
• pg_name is the destination table from step 2.
• columns are the Materialize columns to sync, in the order the sync writes them.
• keys are the columns that make a row unique in mz_name; the sync uses them for the ENVELOPE UPSERT key and the ON CONFLICT clause on the PostgreSQL side.

Step 4. Run the sync

export MZ_DSN="postgres://<user>@<mz-host>:6875/materialize?sslmode=require"
export PG_DSN="postgres://<user>:<password>@<pg-host>:5432/<database>?sslmode=require"

uv run python main.py

The process runs until stopped. It reads a snapshot of every configured relation first, then applies inserts, updates, and deletes as they happen in Materialize. On restart, it reconciles: the snapshot marks every existing row for that environment as deleted before re-upserting what’s actually there, so a row removed from mz_name while the sync was down still ends up marked deleted_at.

Step 5. Validate

With the sync running, change data in the source relation and confirm it lands in PostgreSQL:

INSERT INTO items VALUES (3, 'gizmo', 4.99);
UPDATE items SET price = 24.99 WHERE id = 2;
DELETE FROM items WHERE id = 1;

SELECT id, name, price, deleted_at FROM mz_environment_items ORDER BY id;

Each change should be visible in PostgreSQL in well under a second.

Related pages

• SUBSCRIBE
• Sinks
• Troubleshooting sinks