how can I drop and re-create a production database attached to my app?

I have asked this question on ElixirForum as well: I need to run `mix ecto.reset` in production - Questions / Help - Elixir Programming Language Forum

My problem is that I am currently heavily iterating on my database models, so I will end up with scores or hundreds of migration files that are mostly just tiny tweaks if I keep building up migrations.

But if I instead edit a migration to make it do exactly what I want, then my deployment complains because the migration has already been run.

I’m not really in production, so I don’t mind just dropping all my data and re-running my migrations from the beginning, so I wrote a script to do that, but when I try to run the script upon deployment I get an error:

{:error, "ERROR 55006 (object_in_use) database \"myapp\" is being accessed by other users\n\nThere are 11 other sessions using the database."}

I tried to suspend my app so I could run my script without a live version of my app holding the connections, but then I get the error:

Error App 'myapp' is currently suspended. Resume before deploying.

So… how can I just drop and re-build my database?

Thanks.

1 Like

I guess I can just go to the Fly dashboard and destroy my database app there and then create a new one and re-attach it to my app, changing the DATABASE_URL entirely – this would mean I wouldn’t have to worry about having a reset script to run when I deploy – but I’d rather just have a script do the work for me when I deploy, at least for the next couple of months while I’m heavily iterating.

It might be less trouble to drop and recreate everything within the database’s public schema instead of the database itself. Then you don’t need to worry about database users and permissions.

1 Like

Could you explain this idea just one step further. Are you suggesting that I do something like add an initial migration that just drops all my tables? How do I get around the schema_migrations table that apparently keeps track of where I am in my migrations?

Yeah just delete all the tables, including schema_migrations, and you’ve essentially got a clean database.

3 Likes

This was the best way for me.

2 Likes

@fedeotaran @michael Could you please explain how to do that?

Yes, you have to drop all the types, functions and tables in the database (without dropping the database) and then deploy again.
In the new deploy the migration command will re-create all the tables again.

In my case, I connected to the database from my machine via using WireGuard.
I used the TablePlus tool to delete everything.

Just in case, keep in mind that this will delete all data from the database.

Thank you very much, I did it and it worked successfully. :smile:

1 Like