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

Questions / Help
1

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
2

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.

3

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
4

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?

5

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

3 Likes
6

This was the best way for me.

2 Likes
7

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

9

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.

10

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

1 Like