PostgreSQL to Serverless SQL Migration [2026 Guide]

A clean PostgreSQL exit is not a dump-and-pray exercise. The production-safe pattern is: convert the schema, bulk-load historical rows, stream live changes, verify, then cut over only when replication has drained. This walkthrough uses CockroachDB Cloud Basic as the concrete destination because Cockroach Labs publishes an official PostgreSQL-to-distributed-SQL migration flow built around MOLT Fetch, MOLT Replicator, and MOLT Verify.

• Use separate tools for each phase: molt fetch for the backfill, replicator pglogical for live change capture, and molt verify for data checks.
• Prepare PostgreSQL correctly: logical replication needs wal_level=logical, plus enough replication slots and WAL senders for the stream.
• Treat schema drift as a release blocker: DDL during replication is risky, and PostgreSQL-only features still need review on the target.
• Make cutover boring: pause or queue writes, wait for the apply rate to hit zero, verify, then switch the application DSN.

Plan the Migration

The current Cockroach Labs guidance favors a separate workflow instead of the older all-in-one replication mode. That matters because it keeps the migration easier to observe: you get a checkpoint from the backfill, then explicitly start continuous replication from that point. Architecturally, this is the right shape even if you later swap in a different distributed SQL vendor.

Step 1: Prepare Source and Target

Prepare PostgreSQL for logical replication

First, confirm that you are talking to the primary. Cockroach's replication tooling requires the source primary, and PostgreSQL's logical replication settings are publisher-side concerns.

SELECT pg_is_in_recovery();
ALTER SYSTEM SET wal_level = 'logical';

If pg_is_in_recovery() returns t, you are on a replica and should stop. Also note that PostgreSQL documents wal_level as a startup-only parameter, so changing it requires a restart. On the publisher, PostgreSQL also requires maxreplicationslots to cover your expected subscriptions plus reserve, and maxwalsenders to be at least that high plus any physical replicas.

Create a dedicated migration user. The exact privilege model depends on how tightly you want to scope ownership, but the pattern below is the minimal baseline for data reads:

CREATE USER migration_user WITH PASSWORD 'replace-me';
GRANT CONNECT ON DATABASE appdb TO migration_user;
GRANT USAGE ON SCHEMA public TO migration_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migration_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO migration_user;
ALTER USER migration_user WITH LOGIN REPLICATION;
GRANT CREATE ON DATABASE appdb TO migration_user;

If you want MOLT to manage publications without temporary superuser access, the replicated tables also need to be owned by that user.

Prepare the distributed SQL target

Load a converted schema into the target before moving rows. CockroachDB is PostgreSQL-wire compatible, but Cockroach's own compatibility guide is explicit that some PostgreSQL features and behaviors differ. For faster initial loads, Cockroach recommends dropping non-primary-key constraints and secondary indexes first, then recreating them after data is synchronized.

CREATE DATABASE appdb;
CREATE SCHEMA IF NOT EXISTS public;

Keep this rule simple: source and target schema definitions must match the rows you plan to replicate. If your source uses PostgreSQL-specific constructs, fix them now instead of during the cutover.

Step 2: Run the Initial Load

The backfill seeds the target with a consistent snapshot and creates the replication checkpoint you need for live sync. In the PostgreSQL flow, MOLT can also create the publication and slot automatically if you pass both required pglogical flags.

export SOURCE='postgresql://migration_user:REDACTED@pg-primary.example.com:5432/appdb?sslmode=require'
export TARGET='postgresql://roach_user:REDACTED@cockroach.example.com:26257/appdb?sslmode=verify-full'

molt fetch --source "$SOURCE" --target "$TARGET" --schema-filter 'public' --table-filter 'customers|orders|payments' --bucket-path 's3://my-migration-bucket/appdb' --table-handling truncate-if-exists --pglogical-replication-slot-name molt_slot --pglogical-publication-and-slot-drop-and-recreate

This is the point where teams make their most expensive mistake: they optimize for purity instead of observability. Keep the table filter tight for the first production move. If you need the target to stay queryable during the load, Cockroach documents --use-copy as the online option, but the default IMPORT INTO path is faster for large backfills.

Expected output

message=starting fetch
message=dropping and recreating publication molt_fetch
message=fetch complete cdc_cursor=0/3F41E40

If you do not see a completion record with a checkpoint, do not proceed to CDC.

Step 3: Start CDC and Cut Over

Once the backfill is complete, start the live replicator against the same source and target. For PostgreSQL, Cockroach's documented flow uses replicator pglogical with the slot name created during fetch and the publication name molt_fetch.

replicator pglogical --sourceConn "$SOURCE" --targetConn "$TARGET" --targetSchema appdb.public --slotName molt_slot --publicationName molt_fetch --stagingSchema appdb._replicator --stagingCreateSchema --metricsAddr :30005 -v

Leave that process running while production still writes to PostgreSQL. During this phase, the application remains online and the target catches up in the background. What you are waiting for is not elapsed time; it is confidence.

Cutover checklist

1. Freeze DDL. Cockroach documents that schema changes while replication is in progress can break the pipeline.
2. Pause or queue source writes. This is the brief drain window that turns a risky cutover into a predictable one.
3. Wait until replication has drained. Cockroach documents two signals: no new upserted rows logs, or a combined apply rate of zero from the metrics endpoint.
4. Run final verification.
5. Switch application connection strings to the new cluster.
6. Rebuild the secondary indexes and non-primary-key constraints you deferred for load speed.

sum by (schema) (rate(apply_upserts_total[$__rate_interval]) + rate(apply_deletes_total[$__rate_interval]))

When that expression is effectively zero and your application write queue is empty, stop the replicator cleanly and flip traffic.

Verify and Troubleshoot

Verification and expected output

Run molt verify before and after the final write drain. The first pass tells you whether the bulk load is sane. The second pass proves the live sync closed the gap.

molt verify --source "$SOURCE" --target "$TARGET" --table-filter 'customers|orders|payments'

message=starting verify on public.orders, shard 1/1
message=finished row verification on public.orders (shard 1/1) num_missing=0 num_mismatch=0 num_extraneous=0

If you want a fast human spot-check, compare row counts for your highest-churn tables on both sides before the DSN switch. Verification is your go/no-go gate; row counts are just a sanity layer.

Top 3 migration failures

1. Replication never catches up: verify you are connected to the PostgreSQL primary, confirm wal_level=logical, and make sure slot and sender capacity are sized for the stream.
2. Replication breaks after a schema change: stop making DDL changes during CDC. The safest move is a release freeze from fetch start until post-cutover validation finishes.
3. Rows still differ after cutover: check for operations that the documented flow does not capture cleanly in your process, especially source-side TRUNCATE, and confirm the target schema still matches the source.

When to Choose Each and What's Next

Choose PostgreSQL when:

• You need exact PostgreSQL behavior and rely on extensions or features your distributed target does not fully match.
• Your write scale still fits a vertically scaled primary plus replicas.
• Your team is not ready to absorb new transaction, topology, and retry semantics yet.

Choose serverless distributed SQL when:

• You need horizontal write scaling, multi-region survivability, or simpler managed operations.
• Your workload is already application-led and you can tolerate a disciplined migration project.
• You want a PostgreSQL-compatible developer surface but cannot keep solving growth with bigger single nodes.

What's next

• Rehearse the entire sequence at least twice on masked production-shaped data.
• Define a rollback window and keep the old PostgreSQL system intact until the bake-in period ends.
• Track post-cutover query latency, retries, and connection behavior before declaring the migration complete.
• Read the official references you will actually use during the change window: CockroachDB Load and Replicate, MOLT Verify, PostgreSQL logical replication configuration, and CockroachDB PostgreSQL compatibility.