无法从Supabase运行pg_dump

Question

我打算在Supabase备份postgres数据库

$ pg_dump -h db.PROJECT_REF.supabase.co -U postgres --clean --schema-only > supabase_backup.sql

我执行了命令

GRANT ALL ON ALL SEQUENCES IN SCHEMA auth TO postgres; 
grant all on auth.identities to postgres, dashboard_user;

但是，我还是

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

Answer 1

编辑10月-2022：有一个新的/改进的脚本来执行数据库迁移

在你开始之前：

• 安装Postgres，以便运行psql和pg_dump。
• 创建一个新的Supabase项目。
• 将旧项目的数据库URL存储为$OLD_DB_URL，将新项目的数据库URL存储为$NEW_DB_URL。

迁移数据库：

• 如果在旧项目中启用了数据库Webhooks，则在新项目中启用数据库Web挂钩。
• 在新项目中，启用旧项目中启用的所有扩展。

从终端运行以下命令：

set -euo pipefail

pg_dump \
  --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|information_schema' \
  --schema '*' \
  --dbname "$OLD_DB_URL" \
| sed 's/^DROP SCHEMA IF EXISTS "auth";$/-- DROP SCHEMA IF EXISTS "auth";/' \
| sed 's/^DROP SCHEMA IF EXISTS "storage";$/-- DROP SCHEMA IF EXISTS "storage";/' \
| sed 's/^CREATE SCHEMA "auth";$/-- CREATE SCHEMA "auth";/' \
| sed 's/^CREATE SCHEMA "storage";$/-- CREATE SCHEMA "storage";/' \
| sed 's/^ALTER DEFAULT PRIVILEGES FOR ROLE "supabase_admin"/-- ALTER DEFAULT PRIVILEGES FOR ROLE "supabase_admin"/' \
> dump.sql

psql \
  --single-transaction \
  --variable ON_ERROR_STOP=1 \
  --file dump.sql \
  --dbname "$NEW_DB_URL"

旧答案(过时)：

我相信您可能错过了改变迁移指南中角色的部分。我抄袭了下面的说明：

在开始之前

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.

迁移数据库

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