Guide: Upgrade the major version of your PostgreSQL source

View as Markdown

This guide shows you how to upgrade the major version (for example, PostgreSQL 15 to 16) of the upstream database behind a Materialize PostgreSQL source while keeping Materialize serving fresh results.

The challenge specific to Materialize is that the source holds an active logical replication slot on the upstream primary. A major-version upgrade replaces the primary with a new instance running the new version, and the replication slot does not carry over automatically. How you handle the slot determines whether reads stay fresh through the upgrade.

The approach in this guide keeps Materialize continuously fresh. You keep the existing source running against the old primary the entire time, build the new-version primary in parallel, and hydrate a second source against it before cutting consumers over.

NOTE: This procedure involves a brief write freeze on the application at the moment of cutover, which is inherent to any major-version upgrade. Materialize keeps serving fresh reads throughout.

WARNING! Managed upgrade services that swap the primary out from under Materialize, such as Amazon RDS Blue/Green deployments, cannot be used while a Materialize source is attached. See Why managed Blue/Green deployments don’t work.

Upgrade with a parallel source

app writes ──▶ PG (old major) ──native logical replication──▶ PG (new major)
                    │                                              │
                    ▼                                              ▼
              existing source                                new source
              (serving fresh)                          (snapshotting + catching up)

1. Stand up the new-version primary

Provision a new PostgreSQL instance running the target major version, with logical replication enabled (for RDS, rds.logical_replication = 1; see enable logical replication).

2. Create the table definitions on the new primary

Logical replication does not replicate DDL, so the tables you replicate must already exist on the new primary, with matching definitions and with REPLICA IDENTITY FULL set:

-- On the new-version primary, recreate each replicated table...
CREATE TABLE my_table (
    id      bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    balance numeric NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- ...then set REPLICA IDENTITY FULL on it.
ALTER TABLE my_table REPLICA IDENTITY FULL;

For anything beyond a handful of tables, generate the definitions from the old primary with pg_dump --schema-only rather than transcribing them by hand.

3. Replicate old to new with native logical replication

Check capacity on the old primary

The upgrade subscription adds a second logical consumer alongside Materialize’s own slot, so confirm the old primary has room for it before you create it:

Setting	Guidance
`max_replication_slots`	At least the total number of logical subscriptions plus any physical replicas.
`max_wal_senders`	At least `max_replication_slots` plus the number of active physical replicas.
`max_slot_wal_keep_size`	Large enough to retain WAL while the new primary snapshots the tables and catches up. If it is too small, the slot is dropped and you start over; see replication slot overcompacted.

Depending on your workload, other settings may also need headroom. Raising max_replication_slots or max_wal_senders requires a restart of the old primary, so plan for it before the upgrade window.

Create the publication and subscription

On the old primary, create (or reuse) a publication for the tables you replicate to Materialize:

-- On the old primary.
CREATE PUBLICATION upgrade_pub FOR TABLE my_table;

On the new primary, subscribe to it:

-- On the new primary.
CREATE SUBSCRIPTION upgrade_sub
    CONNECTION 'host=OLD_PRIMARY_HOST port=5432 dbname=DB user=REPL_USER password=...'
    PUBLICATION upgrade_pub;

A single publication can feed multiple subscribers, so the old primary now has two logical consumers at once, Materialize’s slot and this subscription’s slot, coexisting without conflict. Confirm both are active:

-- On the old primary.
SELECT slot_name, plugin, active FROM pg_replication_slots;

WARNING! Ensure the new primary can reach the old primary on the PostgreSQL port. In a cloud VPC, the upstream endpoint typically resolves to a private IP, so the security group must allow instance-to-instance traffic, not just the client’s IP.

4. Create a parallel source in Materialize

Leave your existing source untouched and serving. Create a second connection and source pointed at the new primary, in its own schema and on its own cluster so the subsources and downstream objects don’t collide and don’t contend with production for resources:

CREATE SCHEMA upgrade;
CREATE CLUSTER upgrade_compute (SIZE = '<size>');

CREATE SOURCE upgrade.my_source
    FROM POSTGRES CONNECTION pg_new (PUBLICATION 'upgrade_pub')
    FOR ALL TABLES;

The new source begins snapshotting and then catches up. Snapshot time scales with data volume and is the long pole of the upgrade, but it happens in the background while the existing source keeps serving fresh. Recreate your downstream views, materialized views, and indexes in the upgrade schema on the upgrade_compute cluster, mirroring the schema and cluster your production objects use.

5. Cut over

Once the new source has caught up, perform a coordinated cutover:

Freeze application writes to the old primary.
Drain replication: wait until the new primary matches the old primary exactly. Comparing an order-independent fingerprint across both databases is a reliable check:
```
SELECT count(*), sum(id), min(id), max(id), count(DISTINCT id) FROM my_table;
```
Synchronize sequences. Native logical replication does not advance sequences on the target. Copy each sequence’s value forward, or post-cutover inserts will collide on the primary key:
```
-- Read on the old primary...
SELECT last_value FROM my_table_id_seq;
-- ...then set on the new primary.
SELECT setval('my_table_id_seq', <last_value>);
```
Drain both sources: wait until both sources in Materialize reflect the frozen state, then verify the fingerprint matches across the old primary, new primary, existing source, and new source.
Swap the schemas and clusters. Rather than repointing each consumer, swap the production schema and cluster with their upgrade counterparts. Each swap is atomic, so consumers keep referencing the same schema-qualified names and the same cluster name:
```
ALTER SCHEMA public SWAP WITH upgrade;
ALTER CLUSTER prod_compute SWAP WITH upgrade_compute;
```
To roll back, run the same statements again.
Resume application writes, now pointed at the new primary.

Throughout the cutover, reads against Materialize stay live and fresh. The existing source serves until the swap, and the new source is already caught up at the swap.

WARNING! If you have sinks, create them in a dedicated schema and cluster that is excluded from the swap. Sinks must not be recreated as part of a blue/green cutover; instead, cut them over to the new definition of their upstream dependencies after the swap. This mirrors the guidance in blue/green deployments with dbt.

NOTE: Any active SUBSCRIBE commands attached to a swapped cluster will break at the swap. On retry, the client automatically connects to the newly deployed cluster.

6. Decommission

Drop the subscription on the new primary, drop the old source in Materialize, and decommission the old primary and the swapped-out cluster.

Why managed Blue/Green deployments don’t work

Amazon RDS Blue/Green deployments let AWS build the upgraded “green” instance, keep it in sync, and swap endpoints at switchover. This is an appealing shortcut, but it is not compatible with an attached Materialize source.

Creating the deployment fails at CREATING_READ_REPLICA_OF_SOURCE with “external replication on the blue primary instance”. One of the documented prerequisites is that the DB instance is not the source or target of external replication, and Materialize’s logical replication slot is external replication. No RDS setting bypasses this.

Releasing the slot by dropping the source does let the deployment be created, but that is not a viable production step:

DROP SOURCE defaults to RESTRICT and fails when the source has dependents. Forcing it with CASCADE drops the entire downstream dataflow, including tables, views, materialized views, indexes, and sinks, which means rebuilding and rehydrating your environment, not a brief pause.
Even after the switchover, Materialize’s original slot on blue is unusable, so the new source must snapshot from scratch against green.

The result is a staleness window that begins when you detach from blue and lasts through provisioning and a full re-snapshot. Use the parallel source procedure above instead: it keeps the existing source serving fresh for the entire upgrade, and the only coordinated pause is the write freeze at cutover.

Considerations

DDL is not replicated. Pre-create the target schema on the new instance before subscribing.
Sequences are not advanced on the target by native logical replication. Synchronize them at cutover.
REPLICA IDENTITY FULL must be set on replicated tables so Materialize captures all column values on updates and deletes.
A publication can feed multiple subscribers, so Materialize’s slot and the upgrade subscription’s slot coexist on the old primary without conflict, as long as the old primary has slot and WAL sender capacity for both.