Back Up a Local Database Before You Change It

A local Supabase database looks disposable because supabase db reset can rebuild it in seconds. That is true for the schema and the seed data. It is not true for everything else: the rows you inserted while chasing a bug, the table you edited in Studio on Tuesday, the state a colleague reproduced once and never wrote down. Reset is fast because it throws all of that away. Before a risky change, take a backup you can restore, and rehearse the restore while nothing is broken.

What does supabase db reset actually do?

The CLI reference for db reset describes it as resetting the local database to a clean state, and it requires the local stack to be running. The command rebuilds the local Postgres container and reapplies the migration files in supabase/migrations, in order. If test data is defined in supabase/seed.sql, that runs after the migrations. The reference is blunt about the rest: "Any other data or schema changes made during local development will be discarded."

The local workflow guide says the same in its command table: db reset "Drops everything, applies migrations, runs seed." It is the command that makes a repository reproducible for a teammate, and it is the command that erases your afternoon.

Two neighbouring commands are easy to confuse with reset. supabase stop is not destructive: the CLI getting-started page states that "Stopping doesn't reset your local database", so data persists until a reset. supabase stop --no-backup is the exception, and the same page is explicit: it "deletes your local schema and data changes". The page also recommends saving before running it, with supabase db diff -f for schema changes and supabase db dump --local --data-only for data. That sentence is a good summary of the whole article.

Reset also has a remote mode. With --linked, the same command applies local migrations to the linked project, and the workflow guide calls it out in bold: "Danger: db reset --linked is destructive: it erases all data in the linked remote database." Keep that in mind when a script is copied between environments.

What is the difference between a schema dump, a data dump and a full backup?

supabase db dump runs pg_dump inside a container with flags that exclude the schemas Supabase manages, including auth, storage and the schemas created by extensions. The three outputs people call "a backup" are three different flags:

• Schema dump. This is the default. The CLI reference says the default dump "does not contain any data or custom roles". You get tables, columns, functions, policies and grants, and no rows.
• Data dump. --data-only "Dumps only data records". Rows only, no definitions. -x excludes specific schema.table pairs, which is useful when one large table is noise.
• Roles. --role-only "Dumps only cluster roles". Roles are cluster-level objects, so they do not travel with either of the other two files.

A full logical backup of a local project is all three files, taken together. The command targets the linked remote project by default, so for local work pass --local; -f writes to a path you choose. The reference also documents a --dry-run flag that prints the pg_dump script instead of running it, which is a quick way to see what you are about to capture.

What none of this includes is worth stating plainly. A pg_dump covers Postgres. Files uploaded through Storage are not rows in that dump. Supabase makes the same distinction for hosted projects, where its backup documentation says that database backups "do not include objects you store via the Storage API". If your local test data includes uploaded files, the database dump is only part of the state you care about.

Hosted backups are a separate mechanism again. Supabase states that it backs up Pro, Team and Enterprise projects daily and keeps 7, 14 or up to 30 days depending on the plan, while for free projects the docs recommend regular exports with db dump plus off-site copies. A local stack has none of that. Before you change it, the export is your job.

What a reset cannot bring back

A reset replays files. It recreates what is written in supabase/migrations and supabase/seed.sql, and nothing else. Anything that exists only in the running database is gone: rows your application created during a test run, a table you built by hand in Studio, a policy you edited there to see whether an idea worked, a seed table you mutated to reproduce a report.

Schema drift is the quiet version of the same problem. A column added through the Studio or through a SQL prompt is real in the database and absent from the repository. The next reset removes it, and if the application reads that column, it fails at the query that expects it, which may be several screens away from the actual cause. The migrations guide recommends capturing such changes with db diff, which creates a migration file from the difference, and it is worth doing while the change is still fresh rather than after the reset.

The same guide notes that migrations are applied in timestamp order, and that concurrent pushes from different machines can conflict. On one machine the practical version is simpler: if a change is not in a migration, it does not survive a reset, no matter how carefully it was built.

Why is a backup you never restored a hope rather than a plan?

A dump is not a backup until a restore has produced a working database. Until then you are trusting a file whose contents you have not read. Three failures are common, and all of them are silent at capture time. A default dump without --data-only looks like a backup and contains no rows. A data dump without the schema restores rows into a database that does not have the tables. A set that omits roles restores cleanly and then fails on permissions the first time the application connects.

The rehearsal is cheap. The CLI getting-started page shows the default local connection string, postgresql://postgres:postgres@localhost:54322/postgres, and notes that you can open the local instance with psql or any other Postgres client. Create a scratch database on that instance, apply the schema file, then the data file, then count the rows in the table that matters and run one query you would actually run in the app. Drop the scratch database afterwards. You now know that the files restore, how long it takes and whether a table you expected is empty.

Restore is not a read-only operation. Applying a dump over a database replaces what is in it: restoring the schema over an existing schema fails on objects that already exist, and restoring data writes over rows with the same keys. Restore into a scratch database to test, and take a fresh backup of the current state before any restore you mean to keep. The dump you are restoring may be older than the mistake you are fixing.

What routine works before and after a risky change?

The exact sequence depends on your project, but the shape is stable, and every step below uses only commands documented by the CLI.

• Start clean and current. supabase start brings the stack up and, per the workflow guide's command table, applies migrations and seed.
• Capture schema you changed by hand. supabase db diff -f before_change writes a migration from the difference. db diff defaults to --local according to the workflow guide.
• Dump the three pieces. supabase db dump --local -f backups/schema.sql, then supabase db dump --local --data-only -f backups/data.sql, then supabase db dump --local --role-only -f backups/roles.sql if you created custom roles.
• Rehearse. Restore the files into a scratch database, check a row count and run one real query, as described above.
• Change the database. Run the migration, the SQL or the refactor, and use supabase db reset when you need a clean replay from migrations.
• Close the loop. Capture what worked as a migration with supabase db diff -f change_name, then commit it. Dump again if the resulting data is worth keeping.

The last step is the one people skip. A backup rescues the current afternoon; a migration rescues every future reset. Without the second, the same data is lost again next week, and the backup becomes the only copy of something that should have been in the repository.

What do you do when the change goes wrong?

Take a fresh dump of the broken state before touching it. You may want to inspect what the failed migration wrote, and the restore will overwrite it. Then restore into the working database, knowing that everything currently there is replaced:

psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "drop schema public cascade; create schema public;"

psql "postgresql://postgres:postgres@localhost:54322/postgres" -f backups/schema.sql

psql "postgresql://postgres:postgres@localhost:54322/postgres" -f backups/data.sql

The first line is the destructive one. It removes the schema and everything in it, which is why the fresh dump comes first and why the restore you rehearsed should be the one you run. If the restore itself fails because the files are incomplete, supabase db reset still rebuilds the schema and seed from the repository; what it cannot bring back is the data that was never written to a file.

If you run several local projects, the manual routine is where a manager earns its place. Supabase Local Manager, an independent project in early access, keeps a backups area per project: it creates named backups, lists restore points and restores one. A restore replaces the project's current data, so take a fresh backup before you use it, exactly as you would with psql.