Supabase CLI - Provide option to restore a Postgres backup

[dthyresson]

Feature request

To encourage parity, between prod, staging and development when standing up a local Supabase dev environment, I often want

• a copy of prod/staging db
• w/ or without data (schema only)

To have a pre-setup database and environment w/out having to create the db and seed (in the event I do not use migrations or have something like https://github.com/amacneil/dbmate setup and in use).

Is your feature request related to a problem? Please describe.

Would like to standup a copy of prod/staging in local dev, either with or without data (ie, schema only).

Describe the solution you'd like

• Given a manual backup downloaded from supabase
• After supabase init and start
• exec a cli command to restore a local .backup file given the path
• With option to do schema only or also restore data
• This could run something like:

psql postgres://postgres:postgres@localhost:7432/postgres < [email protected]

But know the connection string from the docker config.

Describe alternatives you've considered

• Just run psql < manually

A clear and concise description of any alternative solutions or features you've considered.

Additional context

The ruby gem parity offers some of these tools: https://github.com/thoughtbot/parity

See:

development restore production
development restore staging

and the other db options.

It might be nice to have some settings that could define your remote Supabase db and thus offer a unified backup/download and backup/download and restore option from a single command.

Of course, with appropriate warnings about overwriting local dev db.

[dthyresson]

Sorry, I just realized perhaps this issues is better listed in the cli repo. If so, let me know and I will move it.

But, one hiccup I did think of when restoring a db is if existing user credentials would be signed with a different GOTRUE_JWT_SECRET ... and if then one's local dev environment would have to use that value and thus potentially expose the secret to others without good reason.

[kiwicopple]

transferring!

[sweatybridge]

CLI v1 offers way to mirror your staging/production database locally. You can run the following commands to dump schemas locally after linking to a hosted project.

supabase link --project-ref <your_project>
supabase db remote commit

There are a few flags that we pass to pg_dump to ensure that restoring works. If you are using a manual dump, possibly from another postgres provider, you need to verify locally that the dump works by creating a new migration.

supabase migration new manual_dump < dump.sql
supabase db reset

If reset succeeds without error, you may push the schema to a new supabase project with supabase db push.

To restore seed data to local database, you can create a file under supabase/seed.sql containing insert statements. With #160, we hope to expose a way to push seed data to your hosted supabase projects as well.