Test your Supabase backup before the day you need it

Somewhere there is a file with your database in it. A GitHub Action writes one every night, or you ran Supabase's three backup commands once, or your plan takes daily copies. It has the right name and about the size you would expect. Nobody has ever opened it.

Here is the part the setup guides leave for later: a backup that has never been restored is a claim, not a copy. The only way to test a Supabase backup is to restore it, on purpose, somewhere that does not matter, and look at what comes back. A dump cut off halfway, a dump with no rows in it and a dump of the wrong project all sit in storage looking exactly like a good one.

Think of a fire drill. A building holds one on an ordinary morning, when nothing is burning, because the first time anyone walks down those stairs should not be the day they fill with smoke. Outside, somebody counts heads against the list of who was in, and somebody writes the date on the sheet by the door. A restore drill is the same: walk the route, count, and write it down.

How do I know my Supabase backup actually works?

Restore it and look. That is the only test there is, because nothing about the file itself separates a good backup from a broken one.

The drill takes your newest copy, replays it into a project that holds nothing you care about, and asks three questions of the result. Is every table there, with about as many rows as the live one? Is the newest row from the night the copy was taken? Can you sign in as yourself? A good file answers yes to all three, and every way a backup goes wrong fails at least one of them.

Five ways a backup is wrong and still looks right

All five of these finish on the night without an error, and all five leave a file with the usual name in the usual place. They only come apart when the file is replayed.

The second and third are the subject of why your Supabase dump has no users in it, and both come from a dump command run with fewer flags than it needed. The first is the one that surprised us, so it gets a section of its own.

Does a backup file that was cut off fail when you restore it?

Not always. We cut one off partway through a table, replayed it with the flags Supabase's own guide uses, and psql finished without an error.

The test was small and easy to repeat. On 27 September 2026 we dumped a database of two tables, one with 5,000 rows and one with 300, cut the file at three different points, and replayed each version with psql from Postgres 17.11. Every replay used --single-transaction and --variable ON_ERROR_STOP=1, the two flags that are there to stop a restore at the first problem and undo everything it did.

• Cut in the middle of a value, psql stopped with an error and wrote nothing. That is the outcome you would hope for.
• Cut inside the last column of a row, it finished with an exit code of 0, which is how a program says it succeeded. The first table came back with 2,596 of its 5,000 rows, the last of them missing half its text, and the second table came back empty.
• Cut exactly between the two tables, it also finished with 0. The first table was complete and the second was empty.

The reason is the way a dump stores rows. Each table's rows sit in one block that ends with a line holding only \., and when the file runs out before that line, psql takes the end of the file as the end of the block. The only sign that more was meant to follow is a line that should have been at the very end.

That line is pg_dump signing off. A complete dump has the words PostgreSQL database dump complete near its end, and a dump that was cut off does not. Of the three files the Supabase CLI writes, the line survives only in data.sql, because the CLI strips every comment out of the schema file (checked with version 2.111 of the CLI). That makes data.sql the file to search, and the search is the second step of the drill.

Where should I restore it?

Into a Supabase project that holds nothing you care about: a spare project in your account, or a copy of Supabase running on your own computer. Never into the project your app uses.

A spare Supabase project is the closest thing to your real one, and it is the target Supabase's backup and restore guide is written for: its first step is to create a new project. On the free plan you are entitled to two active free projects, and paused ones do not count, so a spare usually costs nothing as long as your database fits the free plan. In a paid organization a new project's compute is charged by the hour, so delete it when the drill is finished.

Supabase on your own computer costs nothing and involves no account. The Supabase CLI runs the whole stack locally in Docker: supabase init, then supabase start, and it prints a database address on port 54322 and a publishable key for the local project. Before you start it, open supabase/config.toml and set major_version to your project's Postgres major version. The comment above that setting says it has to match, and your project's version is under Project Settings, then General.

There is one catch on Postgres 15. Unless the backup job set a version, the CLI took the copy with pg_dump 17, and the data file then carries SET transaction_timeout = 0 near its top, a setting Postgres 15 does not recognise, so the replay stops on that line. For the drill, set major_version to 17. Then give the backup job the one-line fix in the GitHub Action article, because the project you would restore into for real is still on 15.

A plain Postgres in Docker, with nothing of Supabase's in it, is not enough. A Supabase dump refers to things only Supabase creates, such as the auth schema your users live in and the authenticated role your security policies name, and the restore stops at the first line that mentions one.

One caution, because the spare now holds a real copy. It has your users' email addresses in it, so do not point your app or its webhooks at it, and delete it when you are done.

Scheduled jobs are the part that does not come across. If your project runs them through the pg_cron extension, the CLI's three files bring the extension back and none of its jobs, because the jobs are rows in cron.job and the data dump leaves those rows out. We checked on 4 October 2026 with version 2.119 of the CLI, by restoring a database that had a job scheduled. The spare stays quiet as a result, and a real restore from these files starts with no scheduled jobs either, so keep your cron.schedule calls somewhere you can run them again.

How to test a Supabase backup, step by step

Six steps, and the first three happen before you restore anything.

1. Find the newest copy and read its date. If it is older than the last scheduled run, the job has stopped, and that is your first finding.
2. Search data.sql for dump complete. One match means the file reached its end. None means it was cut off, whatever its size. A text editor's search works as well as a terminal:

   grep -c 'dump complete' data.sql
   

3. Search it for your users. A line beginning COPY "auth"."users" with rows under it means your accounts are in the file:

   grep -n 'COPY .*auth.*users' data.sql
   

4. Replay it into the spare with the command from Supabase's guide, pointed at the spare's connection string. On a local copy of Supabase that string is postgresql://postgres:postgres@127.0.0.1:54322/postgres.

   psql \
     --single-transaction \
     --variable ON_ERROR_STOP=1 \
     --file roles.sql \
     --file schema.sql \
     --command 'SET session_replication_role = replica' \
     --file data.sql \
     --dbname "postgresql://…the spare's connection string…"
   

5. Count the rows and read the newest one, in both databases. The next section has the query.
6. Sign in to the spare as yourself. The section after that has the command.

If step 4 stops with an error, the drill has done its job early. The troubleshooting notes at the foot of Supabase's guide cover the two errors people hit most, both about roles, and how to restore a Supabase backup explains what each flag in that command protects you from.

Two more stops in roles.sql are missing from those notes. We hit both on 4 October 2026, replaying the files of two Postgres 17 databases, one of them a Supabase project, into a fresh local copy of Supabase: "supabase_admin" is a reserved role, only superusers can modify it, on a line that begins ALTER ROLE "supabase_admin", and permission denied for parameter log_min_messages, on a line that begins GRANT SET ON PARAMETER. Both lines set up roles Supabase creates in every project for itself, so put -- at the start of each line to turn it into a comment and run the command again. Our backup Action comments them out as it takes the copy.

What to count, and against what

Count every table in the spare and in your live project with the same query, and put the two lists side by side. The copy should be a night behind the live database: a little lower on a table that grows, and never zero on a table that had rows.

This is the head count against the list of who was in. Paste the query into the SQL editor of each project and press Run. It counts the rows in every table of your own schema and of auth:

select table_schema, table_name,
       (xpath('/row/n/text()',
         query_to_xml(format('select count(*) as n from %I.%I', table_schema, table_name),
                      false, true, '')))[1]::text::bigint as row_count
  from information_schema.tables
 where table_schema in ('public', 'auth')
   and table_type = 'BASE TABLE'
 order by table_schema, table_name;

It reads every row of every table, so on a large database run it outside your busiest hours.

Then read the newest row of a table you know, on the spare only. Any table with a created_at column will do; put its name in place of orders:

select max(created_at) from public.orders;

The answer should be close to the time the backup ran. A date weeks older, or rows you do not recognise, means the file came from another project.

Why the sign-in is the test that proves your accounts

A count of auth.users proves the rows arrived. A sign-in proves they work: that the stored password, the spare's sign-in service and your email address still agree with each other.

Use your own account, with a password you know. The spare's project address and publishable key are in its Connect panel, or in the output of supabase start on a local copy:

curl -X POST 'https://…the spare's project ref….supabase.co/auth/v1/token?grant_type=password' \
  -H "apikey: sb_publishable_…" \
  -H "Content-Type: application/json" \
  -d '{"email": "you@example.com", "password": "…"}'

That is the sign-in call from Supabase's own documentation, aimed at the spare. A reply containing access_token means your account came across with a password that works. Invalid login credentials, for an account that signs in fine on your live app, means it did not, and why a dump arrives without the accounts is the article for that result.

If everyone signs in to your app with Google, this call has nothing to test, because the spare has no Google sign-in set up. Search the restored auth.users for your own email instead. That shows the account row came across, though not that its password works.

Files: the half a database restore cannot test

The database copy holds a row for every uploaded file and none of the files. If you copy your Storage buckets on a job of their own, which is the only way they get copied at all, give that copy a drill too.

Pick the three newest rows in the restored database:

select bucket_id, name from storage.objects order by created_at desc limit 3;

Find those three paths in your copy of the files and open each one. A path with no file behind it is an image your users would see broken after a real restore.

Can I test the backups Supabase takes for me?

On a paid plan, yes, with Restore to a New Project. It is the only way to open one of Supabase's own daily copies, because on current projects you cannot download them.

Supabase describes the feature as a way to perform testing safely. Pick a copy under the Restore to a New Project tab of the Backups page, and Supabase builds a new project from it with your users and their hashed passwords, ready for the same counts and the same sign-in. Two things to know before you press it. The new project is billed as a project of its own, and Supabase shows you the cost before it starts. And it copies everything, scheduled jobs included: Supabase says jobs run by pg_cron, pg_net and wrappers start running as soon as the restore completes, with no way to pause them first. If a job in your app sends email or charges a card, the test project will do it too.

If you also keep a file outside the account, that file needs a drill of its own.

How often should I test a restore?

Once now, then every three months, and again whenever anything about how the backup is taken changes.

The changes that matter are the ones that break a backup quietly: a new workflow or an edited one, a reset database password, a move to a new plan or a new Postgres version, a new table your app has started writing to. Each one is a point after which last quarter's drill no longer describes this quarter's file.

Then write it down, which is the sheet by the door. One line per drill, somewhere you can reach without the thing that broke: the date, which file, the counts that mattered, whether the sign-in worked, and how long the whole drill took. A text file in the backup repository will do, and so will a recurring calendar entry with the results pasted into it. The date answers "when did we last prove this" in the hour somebody needs to know, and the time it took is your first estimate of how long a real restore would keep your app down.

Where Reeve Care fits

Care keeps copies of your Supabase database outside your Supabase account, on your plan's schedule, checks every copy as it is taken, and puts it back with a button.

• Copies run on your plan's schedule, from once a day up to every six hours.
• A copy that was cut off is caught as it is taken. pg_dump's closing line has to be in the file, or the copy fails the check and never becomes the date on your dashboard.
• Every table is counted as the copy is written. When a table that had rows in the previous copy has none in this one, a person at Reeve hears about it.
• Your accounts are in the copy, and your uploaded files come too once you connect a Storage credential.
• Restoring is a button, and a copy of the current state is taken before anything is replaced.
• Any copy downloads as a zip with schema.sql, data.sql, roles.sql and a manifest holding the row count of every table, which is the "against what" half of this article, already written down.

A quarterly drill proves the copy you picked that day, and the check runs on every copy as it is taken. How the copy, the check and the restore work is drawn step by step on the Supabase backups page, and what each plan includes, schedule and all, is on the pricing page.

What to do this week

Before you close this tab, search your newest data.sql for dump complete. It is one search, and it tells you whether the file you have been calling a backup reaches its own last line. If you do not have a file to search yet, a free GitHub Action is the quickest way to start making them.