Migrating a static MongoDB archive into Postgres with the Supabase MongoDB wrapper

The problem

You have a large MongoDB collection of historical documents that no longer changes - an append-only log, an event history, an archived dataset - and you want to stop running Mongo for it. The rest of your data has already moved to Postgres, but this one big legacy collection is static (write-once, read-occasionally) and rebuilding your migration tooling just for it is unappealing. The Supabase MongoDB wrapper gets floated as a way to just keep the data in Mongo and query it from Postgres, with no migration at all.

The wrapper is a live foreign-data-wrapper (FDW) connector: data stays in Mongo and Postgres queries it over the network on every scan. As a bridge while you migrate the rest it is fine; as a way to avoid migrating that data forever it means you keep running and paying for two databases, and you keep an inbound network path open between Supabase and your Mongo box indefinitely.

For a static archive, a one-time copy costs less than a second database running indefinitely for data that never changes, so use the wrapper once to copy, verify row counts, then drop the connection and decommission Mongo.

This guide is the whole path, verified end-to-end against a real hosted Supabase project (wrappers 0.6.2) reading a seeded MongoDB Atlas cluster.

Provenance: no supabase-lab experiment covers the MongoDB wrapper. Every observation here - the HV000 TLS reject, the zero-row keyset copy through the real wrapper, the Bucket not found CLI failure, the storage round trip - is the author’s own from that one project, undated beyond the wrappers 0.6.2 version, with no run artifact behind it. The guide carries no timings, batch throughput or sizes; where a number appears it is a Supabase list price or a range on synthetic data, labelled as such where it appears. The two platform facts borrowed from the lab (the statement-timeout signature and the upgrade eligibility payload) are cited to their modules in What to do about it.

What the wrapper is and is not

The wrapper maps a Mongo collection to a foreign table. Top-level BSON fields map to declared columns by exact name; a special _doc jsonb column receives the whole document for nested and array access.

create foreign table mongo.events (
  _id  text,
  _doc jsonb
)
  server mongo_server
  options ( database 'app', collection 'events' );

It supports where / order by / limit pushdown, and INSERT / UPDATE / DELETE when rowid_column is set. It does not do the following:

Limitation	Consequence for a “leave it in Mongo” plan
No aggregate pushdown	count/sum/avg/min/max fetch all rows and compute in Postgres - every dashboard query drags the collection over the network
No nested-field SQL without _doc	address.city as a column name does not traverse; you must go through _doc -> ... ->> ...
No multi-document transactions	Not a transactional store you can lean on
No import foreign schema	Every foreign table is declared by hand
Dynamic Supabase egress IPs	The remote must allow a broad IP range (see below) - a standing exposure

These limits do not affect a one-off copy; they apply to every query if the connector stays live in production.

It is a native wrapper, so it only exists on hosted projects

The MongoDB wrapper is a native FDW, not a Wasm one. Native wrappers are compiled into the Supabase platform Postgres image, so create foreign data wrapper mongodb_wrapper works on a hosted project but not on a plain local Postgres - supabase start’s local image does not ship mongodb_fdw_handler, and there is no fdw_package_url install path the way there is for Wasm wrappers (Snowflake, Stripe, and the like).

You cannot dry-run the full migration on a local stack. If you want to exercise the copy SQL before pointing it at a hosted project, run it against an ordinary Postgres table with the same two columns (_id text, _doc jsonb) loaded from a mongosh dump. The SQL is identical; switching to the real foreign table is a one-line change to the source relation.

Check the wrapper is installed

None of this works unless the wrapper is present on the project, so check first:

create extension if not exists wrappers with schema extensions;
create foreign data wrapper mongodb_wrapper
  handler mongodb_fdw_handler
  validator mongodb_fdw_validator;
select fdwname from pg_foreign_data_wrapper where fdwname = 'mongodb_wrapper';

A returned row means it is installed.

Setup

Store the connection string in Vault (never inline it in pg_foreign_server), create the server, then the foreign table.

-- 1. secret in Vault, capture the key id
select vault.create_secret(
  'mongodb+srv://user:password@cluster.example.mongodb.net/',
  'mongo_archive',
  'one-off archive migration'
);  -- returns a uuid

-- 2. server referencing the secret
create server mongo_server
  foreign data wrapper mongodb_wrapper
  options ( conn_string_id '<KEY_ID_FROM_ABOVE>' );

-- 3. schema + foreign table (_id text, _doc jsonb catch-all)
create schema if not exists mongo;
create foreign table mongo.events (_id text, _doc jsonb)
  server mongo_server
  options ( database 'app', collection 'events' );

-- live read: proves the connector works end to end
select _id, _doc->>'type', _doc->'payload'->>'name'
from mongo.events limit 3;

Dynamic egress IPs

The first live read will likely fail with a TLS reject on every shard:

ERROR: HV000: Kind: Server selection timeout: No available servers.
  ... Error: Kind: I/O error: received fatal alert: InternalError ...

This is the IP allowlist. Supabase’s outbound egress IPs are dynamic, so there is no fixed range to safelist. On MongoDB Atlas you must add 0.0.0.0/0 to Network Access (or front Mongo with a fixed-IP proxy and safelist only that). It is easy to miss because your own box (whose IP you allowlisted to seed the data) connects fine, while Supabase - a different, shifting source IP - does not.

The self-hosted case is worse

For a self-hosted Mongo box, 0.0.0.0/0 means opening your firewall to Supabase’s dynamic egress. That standing inbound exposure, for data that never changes, is the case for draining and decommissioning rather than running the connector live.

The copy: batched _id-keyset, not a single create-table-as-select (CTAS)

Do not copy with a single statement at scale:

-- do not do this at scale
create table public.events as
select _id, _doc from mongo.events;

A single CREATE TABLE AS streams the entire collection over the FDW in one transaction. On a managed Postgres running wal_level = logical, that is fully WAL-logged (the wal_level = minimal CTAS optimisation does not apply), so a large collection produces an equally large volume of WAL and backup churn, held in one long transaction that will trip statement_timeout long before it finishes.

Instead, page by _id keyset. ObjectId’s leading 4 bytes are a creation timestamp, and the wrapper returns _id as a 24-char lowercase hex string, so lexicographic _id order tracks insertion order and keyset pagination pushes down cleanly (where _id > <last> becomes a Mongo $gt, with the hex coerced back to an ObjectId). Each batch is its own bounded transaction.

# destination created up front (PK gives idempotency + a useful index)
psql -c "create table if not exists public.events (
           _id text primary key, _doc jsonb);
         create index if not exists events_doc_gin
           on public.events using gin (_doc);"

# batched keyset copy
last_id="000000000000000000000000"   # zero ObjectId - see gotcha below
while :; do
  next=$(psql -tA -c "
    with batch as (
      select _id, _doc from mongo.events
      where _id > '${last_id}' order by _id limit 1000
    ), ins as (
      insert into public.events (_id, _doc)
      select _id, _doc from batch
      on conflict (_id) do nothing
    )
    select coalesce(max(_id), '') from batch;")
  [ -z "$next" ] && break
  last_id="$next"
done

on conflict (_id) do nothing makes it idempotent and resumable: interrupt it, run it again, and it tops up to exact parity without duplicating.

Seed the keyset with the zero ObjectId

The keyset seeds with 000000000000000000000000, the all-zeros ObjectId, rather than an empty string. The two behave the same against a stub and differently through the real wrapper:

• Against a plain-text stub (_id is a Postgres text column), _id > '' matches every row, so the copy works and tests pass.
• Through the real wrapper, '' is not a valid 24-char hex, so it cannot coerce to an ObjectId. The pushed-down $gt filter matches nothing, and the migration silently copies zero rows with no error.

The all-zeros ObjectId is a valid 24-char hex that sorts below every real _id, so it works both locally and through the wrapper. A local test does not catch the empty-string bug because the stub does not share the wrapper’s type-coercion semantics. Test the copy against the real wrapper at least once before you trust it, even if a shaped stub is green.

Verify, then decommission

Take the source count from Mongo itself, in mongosh against the cluster:

// mongo_src - the cheap count. Through the wrapper, select count(*) from
// mongo.events is the aggregate the limitation table warns about: no pushdown,
// so it drags the whole collection over the network one more time.
db.events.countDocuments()

Then the target-side checks in Postgres:

select
  (select count(*) from public.events)                                     as pg_target,
  (select count(distinct _id) from public.events)                          as distinct_id,
  (select count(*) from public.events where _doc->>'type' is null)         as top_level_nulls,
  (select count(*) from public.events where _doc->'payload'->>'name' is not null) as nested_ok;

You want mongo_src = pg_target = distinct_id, top_level_nulls = 0, and nested_ok = pg_target (proving both top-level and nested jsonb survived, for whatever fields your documents carry). Then drop the connection:

drop server mongo_server cascade;   -- drops the foreign table too
-- and delete the Vault secret holding the Mongo credentials

Do the decommission in SQL, not from the Wrappers list

This route drops the server and the foreign table and touches no other connection. The Dashboard’s Delete button does not work at that scope: it runs drop foreign data wrapper if exists <name> cascade, which takes every server on that wrapper with it, and the list labels each row with the wrapper’s name, so two connections on one wrapper read as two rows that say the same thing. The scope of each path, and the RESTRICT sequence that removes exactly one connection, is measured in what one row’s drop takes with it.

The jsonb table is not a normalised schema

The select _id, _doc copy lands a raw jsonb table. It is queryable (_doc -> ... ->> ..., plus a GIN index for containment), but it does not have the relational shape of a set of typed, normalised tables. If you need the data in a normalised shape, the jsonb landing table is a staging step and you still need a transform pass - a bounded, one-time job against a local Postgres table, not an ongoing ETL against a live Mongo connector. Decide which you need before you start.

Storage cost

On Supabase’s Pro/Team plans, database disk (gp3) runs $0.125/GB/month while Storage runs $0.0213/GB/month - roughly 6x cheaper per GB, before you compress the dump or count the GIN index a queryable hot table needs. So if the archive is queried rarely, a compressed dump in Storage loaded on demand is the cheaper home; if it is queried regularly, the Postgres jsonb table is worth its disk.

The two rates are Supabase’s published Pro/Team list prices (re-checked against the docs), not a benchmark. The Storage round trip below (dump, compress, upload, download, reload, query) was run end-to-end against a real project.

Setting up the storage-archive path

For an archive that is queried rarely, the loop starts from the public.events landing table the copy above produced.

1. Dump the table to a compressed file. A portable jsonl line dump piped through zstd keeps it readable and re-loadable anywhere:

psql "$DB_URL" -c "\copy public.events to stdout" | zstd -19 > events.jsonl.zst

How much you save depends entirely on the data - jsonb with repetitive keys and string values compresses hard (10-70x on synthetic data), varied binary-ish payloads far less. Measure your own dump before assuming a ratio.

2. Create the destination bucket first. supabase storage cp does not create buckets - it fails with Bucket not found if the target does not exist, and the CLI has no create-bucket subcommand. Make it once in the Dashboard (Storage -> New bucket, keep it private), or over the Storage API:

curl -X POST "https://<ref>.supabase.co/storage/v1/bucket" \
  -H "Authorization: Bearer $SERVICE_ROLE_KEY" -H "apikey: $SERVICE_ROLE_KEY" \
  -H "Content-Type: application/json" \
  -d '{"id":"archive","name":"archive","public":false}'

3. Push the dump to Storage. Now the CLI upload works (ss:///<bucket>/<path>):

supabase storage cp events.jsonl.zst ss:///archive/events.jsonl.zst --experimental

The object now lives at the ~6x-cheaper Storage rate, independent of any provisioned disk.

4. Drop the table to free space inside the disk. Dropping the table also drops the GIN index:

drop table public.events;   -- releases the space + GIN index within the disk

Testing confirmed that drop reclaims space inside the volume (database size visibly shrinks - after a vacuum, since dead tuples are not reclaimed immediately) but Supabase’s gp3 disk auto-grows and never auto-shrinks. Your provisioned (billed) disk stays at its high-water mark: there is no manual shrink. It right-sizes to 1.2x the database size (minimum 8 GB) only during a project upgrade

• and if the project is already on the latest Postgres, the fallback is migrating to a new project. So the drop stops further growth; it does not, by itself, drop next month’s disk bill. Plan a Postgres minor upgrade after the export as a deliberate step, and check the upgrade eligibility endpoint before counting on it: a project already at the current version reports eligible: false with target_upgrade_versions: [], there is nothing to upgrade to, and the high-water mark stays until you migrate to a new project (platform-facts F06, supabase-lab, 2026-08-04).

5. Load on demand into a temporary table. Pull the dump back, decompress, and \copy into a temporary table so the disk footprint lasts only for the session:

supabase storage cp ss:///archive/events.jsonl.zst events.jsonl.zst --experimental
zstd -d events.jsonl.zst -o events.jsonl
psql "$DB_URL" <<'SQL'
create temp table events (_id text, _doc jsonb);
\copy events from 'events.jsonl'
-- ... query events here; add a GIN index only if this session needs one ...
SQL

The temp table is dropped when the session ends. Storage cost is low at rest, and each query pays a download and load; if that per-query cost is too high, keep the data resident in the jsonb table instead.

What to do about it

The practices below rest on this guide’s own observations and two borrowed lab measurements; a row without a run behind it says so.

Practice	Evidence	Module
Take the mongo_src count from Mongo with db.events.countDocuments(), never from select count(*) from mongo.events through the wrapper.	The wrapper has no aggregate pushdown, so that count is one more full scan of the archive over the network.	the limitation table above (documented wrapper behaviour)
On a restart, seed last_id from select max(_id) from public.events, not from the zero ObjectId.	The loop then skips batches already landed instead of re-reading them through the wrapper.	follows from the script’s shape (where _id > last_id ... on conflict do nothing); not measured
Confirm every _id is an ObjectId before trusting hex keyset order.	A collection with string or UUID _ids neither sorts nor coerces the way the script assumes.	the script’s premise (a 24-char lowercase hex from the wrapper); not measured
Set the timeout per batch session with set statement_timeout in its own psql -c, over the session pooler (5432) or the direct host.	A CTAS trips SQLSTATE 57014 canceling statement due to statement timeout (3467 ms wall for a 2 s timeout). Through the transaction pooler (6543) show statement_timeout reports the role default 2min whether you set it or not, because connection-time options (PGOPTIONS, options=) are dropped.	supabase-lab edge-resilience W20 and pdf-corpus-graph method notes (measured on other workloads)
Size the batch so one psql -c stays well inside that timeout, measuring rows/s on the first batch.	1000 is the script’s default, not a measured optimum.	design choice, not a result
Check pg_replication_slots before a multi-hour drain.	If the project carries a logical replication slot (a read replica, an in-flight pgmig move), the fully WAL-logged copy retains WAL on that slot until consumed.	inference from the measured watchdog rationale in the upgrade guide; this scenario not run
Read the eligibility payload before counting on a minor upgrade to right-size the disk.	A project at the current version reports eligible: false, target_upgrade_versions: [].	platform-facts F06 (measured 2026-08-04)

The 0.0.0.0/0 allow-list against a fixed-IP proxy, the 10-70x compression range and the storage-versus-disk break-even are design choices or unfixtured ranges; the exact Vault deletion call is left out because it was not verified.

Gotchas

• The wrapper is native, so it is hosted-only. create foreign data wrapper mongodb_wrapper works on a Supabase project but not on a plain local Postgres, and there is no fdw_package_url install path. Confirm it exists before you design around it.
• The remote must allow Supabase’s egress, and that egress is dynamic. Allow 0.0.0.0/0 on the Mongo side, or front it with a fixed-IP proxy and safelist only that; skip this and the first connection fails with a TLS reject while your own allowlisted machine connects fine.
• Seed keyset pagination with the all-zeros ObjectId (000000000000000000000000), not an empty string. '' cannot coerce to an ObjectId, so the pushed-down filter matches nothing and the copy silently moves zero rows.
• Do not use a single CREATE TABLE AS at scale. It runs as one WAL-logged transaction that trips statement_timeout; page by _id keyset in bounded batches.
• No aggregate pushdown. count/sum/avg/min/max fetch every row and compute in Postgres - do not run them against the live foreign table on a large collection.
• Nested fields only through _doc. Dotted column names do not traverse; use _doc -> ... ->> ... with a GIN index for containment queries.
• No import foreign schema, no multi-document transactions. Declare each foreign table by hand, and do not treat the connector as a transactional store.
• Drop the connection when the copy is done. drop server mongo_server cascade and delete the Vault secret, so no credentials or standing network path linger.

Modules

Module	Experiment	Test	Artifact
F06	platform-facts	f06-upgrade-surface.ts	none published
W20	edge-resilience	w20-statement-timeout.ts	out/2026-08-16, out/2026-08-17