SupabaseS
Supabase•6mo ago
sleepless

How to cleanly restore binary dump using pg_restore?

I believe I need to disable triggers for it to go smoothly but not sure what to do about permissions, with --disable-triggers I am hitting pg_restore: error: could not execute query: ERROR: permission denied: "RI_ConstraintTrigger_whatever" is a system trigger.

For context, I am trying to:
  • run npx supabase db reset first (run all the migrations and leave me with empty tables, no seed)
  • restore from data-only binary dump using pg_restore
Vaguely what I'm trying to do from my bun script
// backup
await $`pg_dump ${databaseUrl} \
    --data-only \
    --encoding=UTF8 \
    --format=custom \
    --compress=6 \
    --no-acl \
    --no-owner \
    --no-privileges \
    --quote-all-identifiers \
    --verbose \
    --disable-triggers \
    --schema=auth \
    --schema=public \
    --exclude-table=auth.schema_migrations \
    --exclude-table=supabase_migrations.schema_migrations \
    -f ${backupFile}`;

// restore (after npx supabase db reset)
await $`pg_restore --dbname="${dbUrl}" --data-only --disable-triggers --no-owner --no-privileges --verbose ${absoluteBackupPath}`.nothrow();
Was this page helpful?