Restore from backup impossible as it creates multiple errors (permission denied, unique key constraints ...)

[opendeluxe]

Bug report

• [X ] I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Restoring a database backup into another supabase instance produces multiple errors of these kind:

1. duplicated key errors, even on schema_migrations (although this should never happen thanks to unique constraints)
2. permission errors on trigger functions

ERROR:  duplicate key value violates unique constraint "schema_migrations_pkey"
DETAIL:  Key (version)=(20211116024918) already exists.
CONTEXT:  COPY schema_migrations, line 1

 ERROR:  duplicate key value violates unique constraint "migrations_pkey"
DETAIL:  Key (id)=(0) already exists.
CONTEXT:  COPY migrations, line 1

 ERROR:  permission denied for table secrets
 ERROR:  multiple primary keys for table "mfa_amr_claims" are not allowed
 ERROR:  multiple primary keys for table "audit_log_entries" are not allowed
 ERROR:  multiple primary keys for table "flow_state" are not allowed
 ERROR:  multiple primary keys for table "identities" are not allowed
 ERROR:  must be owner of table subscription

 ERROR:  multiple primary keys for table "buckets" are not allowed
 ERROR:  relation "migrations_name_key" already exists
 ERROR:  multiple primary keys for table "migrations" are not allowed


 ERROR:  relation "identities_provider_id_provider_unique" already exists

pg_restore: from TOC entry 5686; 0 30425 TABLE DATA schema_migrations postgres
pg_restore: error: COPY failed for table "schema_migrations": ERROR:  duplicate key value violates unique constraint "schema_migrations_pkey"
DETAIL:  Key (version)=(20250329212834) already exists.
CONTEXT:  COPY schema_migrations, line 1
pg_restore: from TOC entry 4849; 0 16951 TABLE DATA secrets supabase_admin
pg_restore: error: could not execute query: ERROR:  must be owner of table secrets
Command was: ALTER TABLE vault.secrets DISABLE TRIGGER ALL;

To Reproduce

Create some tables, create some migrations, create a backup, try to restore (either locally or on supabase cloud)

Expected behavior

Restore would run through.

The current solution is absolutely not working and put any Supabase instance at risk as after incidents not a single database can be restored any more!

More information

maybe this is even related toi how Postgres works behind the scenes. But we definitely need a backup-solution that would just work out of the box. If pg_dump is not a good candidate for creating backups, supabase must offer a sokution that works with pg_basebackup, which is - to my current knowledge - the only working solution for producing valid backups.

[sweatybridge]

Have you tried restoring the backup locally using cli?

npx supabase@latest db start --from-backup db_cluster.backup

More details here https://supabase.com/docs/guides/local-development/restoring-downloaded-backup

[itinance]

Have you tried restoring the backup locally using cli?

npx supabase@latest db start --from-backup db_cluster.backup

More details here https://supabase.com/docs/guides/local-development/restoring-downloaded-backup

Facing a similar issue, I can say that this works. However, after that, the database has weird permission errors. Nobody is allowed to do schema changes after that. I reported the concrete issue here: #3531

[Hallidayo]

Hi, I've moved this issue over from the supabase repo

[sweatybridge]

Thanks, I will close this one and answer the other ticket.