I intend to backup my postgres database at Supabase
$ pg_dump -h db.PROJECT_REF.supabase.co -U postgres --clean --schema-only > supabase_backup.sql
I ran the command
GRANT ALL ON ALL SEQUENCES IN SCHEMA auth TO postgres;
grant all on auth.identities to postgres, dashboard_user;
Yet, I still get
pg_dump: error: query failed: ERROR: permission denied for table schema_migrations
pg_dump: error: query was: LOCK TABLE realtime.schema_migrations IN ACCESS SHARE MODE
I believed you may have missed the part to alter the role in the migration guide. I've copied the instructions below:
Before you begin
Make sure Postgres is installed so you can run psql and pg_dump.
Create a new Supabase project.
If you enabled Function Hooks on your old project, enable it on your new project.
Store the old project's database URL as $OLD_DB_URL and the new project's as $NEW_DB_URL.
Migrate the database
Run ALTER ROLE postgres SUPERUSER in the old project's SQL editor
Run pg_dump --clean --if-exists --quote-all-identifiers -h $OLD_DB_URL -U postgres > dump.sql from your terminal
Run ALTER ROLE postgres NOSUPERUSER in the old project's SQL editor
Run ALTER ROLE postgres SUPERUSER in the new project's SQL editor
Run psql -h $NEW_DB_URL -U postgres -f dump.sql from your terminal
Run TRUNCATE storage.objects in the new project's SQL editor
Run ALTER ROLE postgres NOSUPERUSER in the new project's SQL editor