I need to run `mix ecto.reset` in production

Phoenix Forum Questions / Help
, ,
1

My dev environment is my MacBook and my prod environment is on Fly with a Postgres database I have configured via Fly.io.

I cannot run mix tasks on my Fly.io setup because it is working with releases.

I have a lib/myapp/release.ex file which runs Ecto migrations for me when I deploy to prod.

But I’m still in heavy learning/development mode overall, so my prod environment is not really production and I have no users – it is better thought of as a staging environment.

I’m constantly refactoring my schemas as I learn and experiment, so in development I constantly run mix ecto.reset to drop everything in my database and re-run my migrations from scratch.

But in my prod environment I cannot run mix ecto.reset. I could just add a migration that drops everything, but I’d rather not end up with hundreds of migrations before arriving at my final starting point.

So how can I add code to my release.ex module to just drop everything and start from scratch every time I deploy to production? Once I’m ready to actually keep my data around and get some users I’ll obviously remove the code that drops all the data every time I deploy.

1 Like
2

Hi @tadasajon! You are in the right direction, you can use the same technique recommended for running migrations:

defmodule MyApp.Release do
  def greet(), do: IO.puts "Hello World"
end

And then you can eval the code:

_build/prod/rel/my_app/bin/my_app eval "MyApp.Release.greet"

You can check the code inside those ecto tasks for more information on how to do it programmatically for you app release:

TLDR; Take a look at Ecto.Adapters.Postgres.storage_down and Ecto.Adapters.Postgres.storage_up functions. You can see an example of how to do it in the test helper of this lib I’m working on.

3 Likes
3

This was super-helpful @thiagomajesk. Following your example, I was able to add the following function to lib/myapp/release.ex:

  def reset do
    load_app()
    Ecto.Adapters.Postgres.storage_down(Myapp.Repo.config())
    Ecto.Adapters.Postgres.storage_up(Myapp.Repo.config())
    migrate()
  end

and I have adjusted my fly.toml file to include these lines:

[deploy]
  release_command = "/app/bin/myapp eval Myapp.Release.reset"

But I’m unfortunately still hitting a snag. When I deploy my app the reset function runs as expected, but I get the following 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 guess this is because I already have a previous version of my app up and running and it is holding some database connections.

I thought I could solve this by running fly suspend and bringing my app down for a few minutes, but after running fly suspend I am unable to run fly deploy, since I get the error:

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

I’ll update here if I come up with a solution.

3 Likes
4

Rather than dropping the database, could you just migrate down? SSH into your box, and run something like Ecto.Migrator.run(MyApp.Repo, :down, all: true)

Then on your release, you can just migrate back up with your new migrations, or write a function to migrate them all down and back up.

3 Likes
5

If this is for development, why don’t you destroy the whole instance and create a new one? Doesn’t Fly have tools to do that easily?

1 Like
6

That’s basically the solution I’m coming around to – I’ll just have to figure out how to do all that programmatically.

7

Hey @tadasajon - did you ever figure out a solution that doesn’t involve destroying the database?

1 Like
8

Nope – I ended up writing a script that would just drop the entire DB and start over

1 Like
9

just followed your advice, it seems to get the job done:

  def migrate do
    load_app()

    for repo <- repos() do
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :down, all: true))
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, all: true))
    end
  end

then ran fly deploy ...., i have to keep in mind to remove that 1 line before i commit it.

2 Likes
10

Another option if you don’t want to have to edit your migrate def, is to ssh into your fly machine, start IEx, and then just call Ecto.Migrator.run(MyApp.Repo, :down, all: true). Then just do another release and your default migrate def will create your tables again. Steps below.

$fly ssh console
# app/bin/my_app remote
iex(my_app@5521219a)1> Ecto.Migrator.run(MyAPP.Repo, :down, all: true)
3 Likes
11

This may help someone else.

Let’s say you modified everything locally so

fly deploy
fly status
fly ssh console --pty -C "/app/bin/XXXXXXXXXX remote"
iex> alias XXXXXXXXXX.Repo
iex> q = "DROP TABLE schema_migrations, AAAAA, BBBBB, CCCCC;"
iex> Ecto.Adapters.SQL.query!(Repo, q, [])
iex> Ecto.Migrator.run(Repo, :up, all: true)
2 Likes
12

Hackish but effective for my case.

  @doc "Create DB and tables if not there"
  def initialize() do
    # Create the database if one is not there
    case My.Repo.config() |> Ecto.Adapters.Postgres.storage_up() do
      :ok ->
        #Create tables if not there
        Ecto.Migrator.run(My.Repo, :up, all: true)

      _ ->
        # ... or wait for Postgres to be up
        Process.sleep(1000)
        initialize()
    end
13

I found dropping and recreating the public Postgres schema most effective, especially in the context of Kube/Fargate deployments where previous instances may still connected to the database.

This is how we do it in for our dev instances:

def migrate do
  load_app()

  for repo <- repos() do
    Ecto.Adapters.Postgres.execute_ddl(repo, "drop schema public cascade;", [])
    Ecto.Adapters.Postgres.execute_ddl(repo, "create schema public;", [])

    {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, all: true))
  end

  # Seed the schemas
end
1 Like