Restoring a supabase dump to local postgres

Supabase contains a ton of extra schemas, roles and extensions that prevents restoring a local db from a supabase db. There are some tools out there but thery're all tedious to use. Here's how you can do it with a single pg_dump command

pg_dump --exclude-schema graphql --exclude-schema auth --exclude-schema graphql_public \
  --exclude-schema pgsodium --exclude-schema pgbouncer --exclude-schema storage \
  --exclude-schema realtime --exclude-schema vault --exclude-extension pgsodium \
  --exclude-extension pg_graphql --exclude-extension supabase_vault \
  --exclude-extension extensions --exclude-extension pg_stat_statements \
  --exclude-extension pgcrypto --exclude-extension uuid-ossp \
  --disable-triggers \
  --no-owner \
  --no-privileges \
  --no-publications \
  --format custom \
  --file ./backup.dat \
  your_db_url_here

pg_dump has flags for disabling triggers during the dump but not one for omitting them entirely. We intentionally don't exclude the extensions schema here because there are some event triggers that call functions on that schema, and excluding it breaks the restore.

Create a new database to restore on first

psql -d postgres -U youruser -h localhost -c "create database yourdatabase template template0";

Now restore the dump and get rid of the unnecessary extensions schema that we couldn't safely omit in the original dump. This last step is important because there are event triggers on it that are not needed outside supabase and will cause errors once they're triggered.

psql -f ./backup.dat -d yourdatabase -U youruser -h localhost -c "drop schema extensions cascade"

I highly recommend creating the pg_stat_statements extension manually afterwards.