@stefanchrobot really nice job debugging. One thing that may help here is instead of running your migrations as an eval task, put the migrator in your supervision tree via Ecto.Migrator — Ecto SQL v3.10.1. This way it isn’t competing with other activity in your application, and it will also block application start until the migrations can run.

Yeah we’ve been doing this for a long time since it synergizes really well with the K8s liveliness probes and readiness probes. Basically what we have is two endpoints /alive and /ready. /alive unconditionally returns true but /ready looks like this:

  def ready(conn, _params) do
    if Application.get_env(:myapp, :ready) do
      json(conn, %{ready: true})
    else
      send_resp(conn, 503, "")
    end
  end

Then our supervision tree looks like:

Phoenix Endpoint, #( this has `/alive` 200, but `/ready` is 503 still.)
DB Migrator,
Repo,
...Other Children,
MyApp.DeploymentNotifier, # this is just a one shot genserver that sets the env variable used in `/ready`

K8s has two different timeouts for pods. The first is about whether it’s alive at all, and the second is whether it is ready. It also only hooks up nodes to the load balancer that are /ready. This is K8s specific but this paradigm is found in other deployment structures as well. Basically it just ensures your app has time to initialize anything it needs before getting traffic, while still getting some indication from the app that it is alive and starting to boot.

Not sure if this is the same problem, but on DO I found I had to set the :maintenance_database to defaultdb (Ecto default is postgres) if I wanted to do create/migrate as part of a deploy.

I only have one instance running, so the migrations are not attempted by multiple nodes.

For some reason if I connect to the DB manually before running migrations, things seem to work. If I don’t do it, the migration task doesn’t connect. Any ideas why that might be? I’m guessing maybe different connection timeouts?

Just to recap:

• Digital Ocean App Platform with hosted PostgreSQL over SSL
• MIGRATION_PRECONNECT=true ./bin/myapp eval MyApp.Release.migrate works
• MIGRATION_PRECONNECT=false ./bin/myapp eval MyApp.Release.migrate fails

defmodule MyApp.Release do
  @moduledoc """
  Release tasks.
  """

  require Logger

  @app :myapp

  def migration_preconnect do
    Application.ensure_all_started(:ssl)
    Application.ensure_all_started(:postgrex)

    database_url = System.get_env("DATABASE_URL")
    conn_opts = Ecto.Repo.Supervisor.parse_url(database_url)
    Logger.info("Connecting: #{inspect(Keyword.delete(conn_opts, :password))}")

    {:ok, pid} =
      Postgrex.start_link(
        conn_opts ++
          [
            ssl: true,
            ssl_opts: [
              verify: :verify_peer,
              cacerts: [
                "DATABASE_CA_CERT"
                |> System.get_env()
                |> then(fn pem ->
                  [{_type, der, _info}] = :public_key.pem_decode(pem)
                  der
                end)
              ]
            ]
          ]
      )

    Logger.info("Querying...")

    %Postgrex.Result{rows: [[count]]} =
      Postgrex.query!(pid, "SELECT count(*) FROM accounts;", [])

    Logger.info("Found #{count} account(s).")
  end

  def migrate do
    try do
      if System.get_env("MIGRATION_PRECONNECT") do
        migration_preconnect()
      end

      load_app()

      for repo <- repos() do
        {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, all: true))
      end
    catch
      kind, value ->
        Logger.warning("Migration failed: #{inspect(kind)}, #{inspect(value)}")
    end
  end

  def rollback(repo, version) do
    load_app()
    {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :down, to: version))
  end

  defp repos do
    Application.fetch_env!(@app, :ecto_repos)
  end

  defp load_app do
    Application.ensure_all_started(:ssl)
    Application.load(@app)
  end
end

Not sure if it’s the same issue though. Digital Ocean provides the cert via an ENV variable, so I’m always connecting with SSL.