@relferreira - but then once the dump is finished, it’s very important to then switch back:

ALTER ROLE postgres NOSUPERUSER

@TomasHubelbauer adding -N auth -N realtime didn’t solve the original issue that @peterp reported for me, i.e.:

pg_dump: error: query failed: ERROR:  permission denied for table migrations
pg_dump: error: query was: LOCK TABLE supabase_functions.migrations IN ACCESS SHARE MODE

In this case I also had to add -N supabase_functions (though probably not ideal for when you want to fully inspect a dump) I raised another issue here that is related: https://github.com/supabase/supabase/issues/7865 it’s unclear to me why SUPERUSER is needed at all, I don’t remember that being a part of the dump workflow when I’ve worked with Postgres previously…

For anyone who’s looking to just dump/backup all their Supabase database data to have a local backup and do not care about the auth and realtime schema contents:

pg_dump -d postgresql://postgres@db.{project}.supabase.co:5432/postgres -N auth -N realtime > dump.sql

You’ll be asked for the password you set during the setup of your Supabase project. If you don’t have pg_dump, do not want to install full Postgres locally and are a macOS user, look into https://postgresapp.com/ (https://github.com/PostgresApp/PostgresApp) - you can skip the database initialization step and just add the CLI tools to your command line.

@JasonChiu-dev see the following comment: https://github.com/supabase/supabase/discussions/3464#discussioncomment-1616225:

grant all on auth.identities to postgres, dashboard_user;

For anyone facing this problem, what worked for me was execute this SQL in the editor:

ALTER ROLE postgres SUPERUSER

as described in this tutorial: https://supabase.com/docs/guides/database#migrating-between-projects

@bigonha can you try following the updated migration instructions here: https://supabase.com/docs/guides/database#migrating-between-projects?

Hi @peterp, this has been fixed for new projects. If you can send your project ref here or to beta@supabase.io, I will add the patch to your database too.

Hi @peterp, we encountered this issue internally as well. For now, you can exclude the storage.migrations table in your pg_dump. The rest of the tables in storage schema should be fine. We will roll out the fix to be able to dump the migrations table as well soon.