Articles

A managed PostgreSQL service is easy to start on and harder to leave. The database is only part of what you move: a Supabase project also carries users, files, scheduled jobs, realtime channels, and the keys every client holds. A plain dump and restore of a database past 100 GB takes hours, and nobody wants the application down for that long.

We moved a production PostgreSQL platform of more than 100 GB from a managed cloud service onto our own infrastructure. Auth, storage, realtime, and scheduled jobs came with it, and the move cost about 3.5 minutes of application downtime. The method: a bulk copy while the application serves, a short delta inside the window, and a rollback held ready until the new platform proves itself.

Plan the move as a bulk copy plus a short delta

The window does not need to hold the copy. Copy the bulk while the application keeps serving from the old database, then stop writes and copy only what changed since. The window then holds the final delta, the switch of connection settings, and the checks, and its length no longer depends on the size of the database.

In our move a live first pass copied the bulk, and the final in-window delta took 21 seconds.

The pattern needs two things from the schema. Each large table needs a way to find its new rows, such as an increasing id or a reliable updated_at column. And every process that writes to the database has to be stoppable, so the source is still during the final pass.

Copy roles, schema, and data in dependency order

Supabase documents a three-part dump for moving a project: roles first, then the schema, then the data. The restore runs in the same order. Its data step sets session_replication_role to replica, so triggers and foreign-key checks stay quiet while rows load out of order.

# Dump from the source project (Supabase CLI).
supabase db dump --db-url "$OLD_DB_URL" -f roles.sql --role-only
supabase db dump --db-url "$OLD_DB_URL" -f schema.sql
supabase db dump --db-url "$OLD_DB_URL" -f data.sql --use-copy --data-only \
  -x "storage.buckets_vectors" -x "storage.vector_indexes"

# Restore into the target, in order.
psql --single-transaction --variable ON_ERROR_STOP=1 \
  --file roles.sql \
  --file schema.sql \
  --command 'SET session_replication_role = replica' \
  --file data.sql \
  --dbname "$NEW_DB_URL"

Past 100 GB, the largest tables move faster as a directory-format dump. pg_dump -Fd -j dumps several tables at once, and pg_restore -j loads data and builds indexes in parallel. Keep the role and schema steps as above, and use the parallel path for the bulk of the rows.

Run the same PostgreSQL major version on both sides. The copy is then a dump and restore with no upgrade folded into it, and the binary copy format in the delta step works without conversion.

Rehearse the full copy on a scratch target before the real one. Read the whole restore log as well as its exit code, and turn each message into a line of the plan. A rehearsal also gives you real durations for the first pass, so the schedule is measured rather than guessed.

List the state that lives outside your tables

A dump of your application schemas carries tables, functions, and policies. A Supabase project keeps several kinds of state elsewhere, and each needs its own line in the plan.

Do the storage copy early. Files can move while the application serves, like the first database pass, and only new uploads need a final sync.

Stop every writer before the final pass

The final pass is only correct if nothing writes to the source while it runs. Stop scheduled writers first: cron jobs, workflow engines, queue workers, and anything that calls the database on a timer. Then stop the application, and confirm the source is still before the delta starts.

-- On the source: sessions that are still writing. Expect none.
select pid, usename, application_name, state, query_start
from pg_stat_activity
where datname = current_database()
  and pid <> pg_backend_pid()
  and state <> 'idle';

For an append-only table, the delta is every row above the target’s highest id. Binary format carries any value, including a line that would end a CSV copy, and it is safe between two servers on the same major version:

-- On the target: the highest id already copied.
select max(id) from events;

-- On the source (psql), with that id in place of 123456789.
\copy (select * from events where id > 123456789) to 'events.bin' with (format binary)

-- On the target.
\copy events from 'events.bin' with (format binary)

Tables that change in place need a different rule. Use an updated_at watermark when every write sets it, or reload a small table in full when it does not. Write the rule for each table into the plan before the window, so nobody has to choose one under the clock.

Set every sequence on the target past the highest id it guards. A restored sequence keeps its value from the dump, behind every row the delta added, so the first insert after the switch would collide.

Write the window as a timed run sheet

Write the window down as a numbered run sheet before the day. Each step names its command or change, the check that closes it, and the person who runs it. The rehearsal fills in a measured duration for every step, and the sum is the window you announce.

A run sheet also fixes the order of the steps that are easy to get backwards. Writers stop before the final pass, sequences move before the switch, and checks finish before traffic returns. Mark the point after which a rollback means reversing writes, so everyone knows when that line is crossed.

Keep the run sheet next to the rollback plan. Both use the same list of clients and settings, and a change to one belongs in the other.

Switch clients, then check that data moves

Switch every client to the target in one step: the application, background workers, and any service that holds a connection string or an API key. Keep the old values in the same change, so a rollback is the reverse of that change.

Before traffic returns, check that data moves on the target, as well as that services report healthy:

Keep the checks as a script, and run it in the rehearsal as well as in the window, so every query has already run once before it matters.

Stage the rollback and hold it through a soak

Decide before the window how you will go back, and keep that path open until the new platform has carried real load. We kept the rollback staged through a 72-hour soak.

A staged rollback has three parts. The source stays intact, with writers stopped and nothing deleted, and the old connection settings stay one change away. The plan also says how writes made on the target during the soak would return to the source; the delta scripts do that job when pointed the other way.

Write down what would trigger a rollback, and who decides, before the soak starts.

Settings we use

Recommendations

• Copy the bulk while the application serves, so the window’s length depends on the delta and not on the size of the database.
• Rehearse the full copy on a scratch target and read the whole restore log, because each object the dump leaves out shows up there first.
• Write a delta rule for every table before the window: an id watermark, an updated_at watermark, or a full reload.
• List roles, extensions, scheduled jobs, secrets, webhooks, files, auth settings, and API keys as separate plan items, since each needs its own step.
• Check that data moves on the target, as well as that services are healthy, before traffic returns.
• Keep the source intact and the old settings one change away until the soak ends.

References

• Supabase: Self-Hosting with Docker
• Supabase: Backup and restore using the CLI
• PostgreSQL: COPY
• PostgreSQL: pg_dump
• PostgreSQL: pg_restore
• PostgreSQL: session_replication_role

If you are weighing a move off a managed database, see Infrastructure and data or get a free estimate.