2

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
Nditah
  • 1,429
  • 19
  • 23

1 Answers1

3

EDIT Oct-2022: There's a new/improved script to perform database migration:

Before you begin:

  • Install Postgres so you can run psql and pg_dump.
  • Create a new Supabase project.
  • Store the old project's database URL as $OLD_DB_URL and the new project's as $NEW_DB_URL.

Migrating the database:

  • Enable Database Webhooks in your new project if you enabled them in your old project.
  • In your new project, enable all extensions that were enabled in your old project.

Run the following command from your terminal:

#!/usr/bin/env bash

#Edit here:
OLD_DB_URL=db.old_project_ref.supabase.co
NEW_DB_URL=db.new_project_ref.supabase.co
OLD_DB_PASS=secret_password_here
NEW_DB_PASS=secret_new_password_here

#Script:
# Default case for Linux sed, just use "-i"
sedi=(-i)
case "$(uname)" in
  # For macOS, use two parameters
  Darwin*) sedi=(-i "")
esac

PGPASSWORD="$OLD_DB_PASS" pg_dump -d postgres -U postgres \
  --clean \
  --if-exists \
  --quote-all-identifiers \
  --exclude-table-data 'storage.objects' \
  --exclude-schema 'extensions|graphql|graphql_public|net|pgbouncer|pgsodium|pgsodium_masks|realtime|supabase_functions|pg_toast|pg_catalog|pg_*|information_schema' \
  --schema '*' \
  -h "$OLD_DB_URL" > dump.sql

sed "${sedi[@]}" -e 's/^DROP SCHEMA IF EXISTS "auth";$/-- DROP SCHEMA IF EXISTS "auth";/' dump.sql
sed "${sedi[@]}" -e's/^DROP SCHEMA IF EXISTS "storage";$/-- DROP SCHEMA IF EXISTS "storage";/' dump.sql
sed "${sedi[@]}" -e 's/^CREATE SCHEMA "auth";$/-- CREATE SCHEMA "auth";/' dump.sql
sed "${sedi[@]}" -e 's/^CREATE SCHEMA "storage";$/-- CREATE SCHEMA "storage";/' dump.sql
sed "${sedi[@]}" -e 's/^ALTER DEFAULT PRIVILEGES FOR ROLE "supabase_admin"/-- ALTER DEFAULT PRIVILEGES FOR ROLE "supabase_admin"/' dump.sql


PGPASSWORD="$NEW_DB_PASS" psql -d postgres -U postgres \
  --variable ON_ERROR_STOP=1 \
  --file dump.sql \
  -h "$NEW_DB_URL" -p 6543

OLD ANSWER (obsolete):

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
Mansueli
  • 6,223
  • 8
  • 33
  • 57
  • When I run this I get permission denied error. Query 1 ERROR: ERROR: must be superuser to alter superuser roles or change superuser attribute. Has anything chnaged? – Tony Jul 30 '22 at 03:33
  • The page got removed, the instructions were gone, thanks for copying that here. – Pencilcheck Oct 25 '22 at 05:55
  • @Tony Read the instructions, you need to run SQL command against the database in the "migrate the database" section to promote your user to superuser to perform pg_dump – Pencilcheck Oct 25 '22 at 10:08
  • BTW, you can install just the CLI part of postgres via: brew libpq – Logemann Jan 25 '23 at 15:05