pgAdmin restore of a Supabase backup: "Failed (exit code: 1)"
Restoring backup on the server 'my-project'
Failed (exit code: 1).pgAdmin runs pg_restore for a restore and shows this status whenever pg_restore exits with code 1. pg_restore does that after any error it skipped, also one that changes nothing. A restore of the public schema into a new Supabase project always has a few of those, so read the errors themselves rather than the status.
We ran the pg_restore commands that the Restore dialog of pgAdmin 9.19 builds, with pg_restore 18.4, against the supabase/postgres 17.12 image. The test schema had two tables with 1,020 rows, row-level security policies, a foreign key to auth.users, a grant to a role of our own and a table in Realtime.
Where pgAdmin shows the errors
The Processes tab lists the restore job. View details opens the Process Watcher with the command pgAdmin ran and the full pg_restore log. Our restore into a new project ended like this:
pg_restore: error: could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: error: could not execute query: ERROR: insert or update on table "profiles" violates foreign key constraint "profiles_id_fkey"
pg_restore: error: could not execute query: ERROR: role "report_reader" does not exist
pg_restore: error: could not execute query: ERROR: permission denied to change default privileges
Command was: ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON TABLES TO postgres;
pg_restore: warning: errors ignored on restore: 6Errors you can ignore
Two of them are expected. "schema "public" already exists" is the first statement in the file: the project already has that schema, and pg_restore puts everything into it. "permission denied to change default privileges" appears three times, for tables, sequences and functions. Only supabase_admin may set its own default privileges, and a new project already has the values the dump asks for.
Errors that need a fix
| Error | Cause | What to do |
|---|---|---|
| violates foreign key constraint | A table references auth.users, which is empty in the new project. pg_restore loads the rows and adds constraints at the end, so the rows arrive and the foreign key does not. | Move the Auth users first, then run the ALTER TABLE statement pg_restore printed under "Command was" in the Query Tool. |
| role "…" does not exist | A grant to a role you created. Roles belong to the server, not to a schema, so a schema backup does not contain them. | Create the role in the new project and run the GRANT again, or create your roles before the restore. |
| schema "auth" does not exist | The target is plain Postgres, not a Supabase project. In our test a table whose column default calls auth.uid() was not created, and its rows were lost with it. | Restore into a Supabase project or into the supabase/postgres image. |
Any other error that names one of your own tables, functions or policies means part of the schema did not come back. One change gives no error at all: tables you had added to Realtime are no longer in it. The supabase_realtime publication is not part of the public schema, so a schema backup does not carry it. Add those tables again under Database, Publications in the Supabase dashboard.
Restore options that make it worse
Most of the Restore dialog can stay at its defaults. We tried the options that sound safer, each on a fresh copy of the same new project:
| Option in pgAdmin | pg_restore flag | Result in our test |
|---|---|---|
| Single transaction (Query Options) | --single-transaction | The first error, "schema "public" already exists", rolled back the whole restore. No tables. |
| Exit on error (Options) | --exit-on-error | Stopped at that same first error. No tables. |
| Clean before restore (Query Options) | --clean | Dropped and recreated the public schema. The data came back, but the default privileges supabase_admin had in public were gone, so tables that role creates later no longer get grants for anon and authenticated. |
| Owner under Do not save (Data Options) | --no-owner | The same 6 errors, because postgres owned every object. With objects owned by other roles, they end up owned by the role you restore as. |
Check the restore
Do not rely on the job status either way. Run this in the Query Tool on the source project and on the new one and compare. It counts rows exactly instead of using planner estimates:
select c.relname as table_name,
c.relrowsecurity as rls,
(select count(*) from pg_policies p
where p.schemaname = 'public' and p.tablename = c.relname) as policies,
(xpath('/row/n/text()', query_to_xml(
format('select count(*) as n from public.%I', c.relname),
false, true, '')))[1]::text::bigint as row_count
from pg_class c
where c.relnamespace = 'public'::regnamespace and c.relkind in ('r', 'p')
order by 1;Foreign keys and Realtime tables are worth comparing too:
select conrelid::regclass as table_name, conname
from pg_constraint
where connamespace = 'public'::regnamespace and contype = 'f';
select tablename
from pg_publication_tables
where pubname = 'supabase_realtime';How to test a Supabase backup covers the same checks outside pgAdmin.
A full database dump gives far more errors
Starting the backup from the database instead of the schema dumps everything, including auth, storage and the extensions Supabase installs. Restoring that into a new project gave 145 errors in an earlier test: "must be owner of table users", "permission denied for schema auth" and others. The new project already has those schemas, and they belong to roles you cannot act as.
To back up only your own tables, right-click the public schema and choose Backup there, and do the same for any other schema of yours. Restoring into Supabase: "must be owner of table" explains which entries to leave out and which policies to recreate.
pg_dump version mismatch
pg_dump: error: aborting because of server version mismatch
pg_dump: detail: server version: 17.12; pg_dump version: 15.19pg_dump has to be at least as new as the server. pgAdmin keeps a binary path per PostgreSQL server version under File, Preferences, Paths, Binary paths. For a Postgres 17 server it uses the PostgreSQL 17 entry. If that entry is empty, it uses the one marked Set as default, and without one it looks in the standard PostgreSQL install locations. Validate path shows which version each entry finds.
Which pg_dump that finds depends on how pgAdmin was installed. The Windows and macOS installers ship the latest version, and the container image ships every supported version. The DEB and RPM packages use what is installed on the machine, so that is where an older pg_dump usually turns up. Install the client tools for your server version and set the path for that version. pg_dump: "aborting because of server version mismatch" on Supabase covers the same error outside pgAdmin.
Storage files are not in it
A pgAdmin backup holds the storage.objects rows, not the files. See do Supabase backups include Storage.
ReviveDB backs up the database and Auth, Storage files and Edge Functions, and restore-tests every database backup. What each Supabase backup option covers.