supabase start failed after supabase stop --backup + data lost

[cdimitroulas]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

After creating some data in my local supabase instance, and running supabase stop --backup followed by supabase start, I see an error saying that a migration has failed. Running supabase start a second time goes through successfully but all the data from my DB has been lost.

To Reproduce

1. supabase start
2. Generate a migration to create a new table
3. Add some data, like a single user
4. supabase stop --backup
5. supabase start
6. See the error as it tries to apply the migration even though the table already exists.
7. If you run supabase start again, it will succeed but all the data that was supposed to be backed up will be gone.

Expected behavior

After running supabase stop --backup, I should be able to restart the DB using supabase start without any problems.

Screenshots

Error output after the first supabase start command:

> SUPABASE_INTERNAL_IMAGE_REGISTRY=docker.io npx supabase start

Setting up Edge Functions runtime...
Applying migration 20230420113929_create_profiles_table.sql...
Error: ERROR: relation "profiles" already exists (SQLSTATE 42P07)
At statement 0: create table profiles (                                                           
  id uuid references auth.users not null,                                         
  username text unique,                                                           
  avatarUrl text,                                                                 
  website text,                                                                   
  bio text,                                                                       
  joinedAt timestamp with time zone default timezone('utc'::text, now()) not null,
                                                                                  
  primary key (id),                                                               
  unique(username)                                                                
)                                                                                 
Try rerunning the command with --debug to troubleshoot the error.

System information

• OS: macOS
• Browser (if applies): NA
• Version of supabase-js: 1.53.0
• Version of Node.js: v16.15.1

Additional context

My guess would be that the migrations table is not being backed up correctly, resulting in supabase trying to reapply migrations that have already been applied before and resulting in errors.

[sweatybridge]

Yup the migration table isn't created when running local migrations, which is indeed a problem. I will look into it as part of the refactoring PR here #1049

[cdimitroulas]

Fastest reply to an issue I've ever had 😆 sounds good - let me know if I can help

[gcrespi]

Yup the migration table isn't created when running local migrations, which is indeed a problem.

I see here you are implementing a fix to skip the migrations if there was a backup, are you also planning to have the migration table on local instances?
This would be helpful for us, when we want to migrate the schema of our local instance and not loose any backed up data on the process.

[sweatybridge]

This would be helpful for us, when we want to migrate the schema of our local instance and not loose any backed up data on the process.

Do you mean you want to run a migration on local db that already contains some data? This isn't currently supported by cli but can be worked around by using psql -h localhost -U postgres -p 54322 -f name.sql. We can probably add a migration up command to support such use cases natively though.

If you are talking about backed up data from stop --backup, I believe it's best to keep the db exactly the same after restart. This is why I decided to skip the migrations completely if the backup volume is present.

[gcrespi]

Do you mean you want to run a migration on local db that already contains some data? This isn't currently supported by cli but can be worked around by using psql -h localhost -U postgres -p 54322 -f name.sql. We can probably add a migration up command to support such use cases natively though.

Yes, that was what I was looking for, such command would be much appreciated 🙏

[cdimitroulas]

I agree this would be great! Having to reset the entire DB in order to run a migration locally can be a bit tedious