© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
SupabaseS
Supabase•7mo ago•
13 replies
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
--disable-triggers
I am hitting
pg_restore: error: could not execute query: ERROR:  permission denied: "RI_ConstraintTrigger_whatever" is a system trigger
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
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
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();        
// 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();        
Supabase banner
SupabaseJoin
Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.
45,816Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements

Similar Threads

pg_dump and pg_restore between projects
SupabaseSSupabase / help-and-questions
3y ago
pg_dump: error
SupabaseSSupabase / help-and-questions
4mo ago
how to pg_restore a supabase db
SupabaseSSupabase / help-and-questions
4y ago
Create Backup of DB using Pg Dump
SupabaseSSupabase / help-and-questions
14mo ago