Database connection issues during deployment on Digital Ocean App Platform

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.

Thanks! I’ve updated the value for :maintenance_database. But it’s something else.

I’ve wrapped my migration function with try do ... catch ... end so that it doesn’t crash. This no longer stops the deployment process - the app is happily booting and connecting to the database Not sure what is going on here. It feels like it’s working sometimes. Sounds like some sort of a race condition.

Should I be manually starting the :postgrex application?

Sorry for a generic suggestion: have you tried the migration_lock option?

Late to the party, but if you have multiple instances on the app platform, but are they perhaps trying to run migrations at the same time?

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

I actually battled with this error yesterday but on heroku where I got quite unhelpful error messages:

** EXIT (exited #pid<0.98.0>) shutdown

Turned out it was the changed default value for ssl’s verify option (changed in OTP 26). So what @stefanchrobot wrote will probably solve it for you (but if you like me didn’t want to get the certificate injected into the platform yet), you can revert to the old behaviour (although it’s considered to be less safe):

# runtime.exs

config :my_app, MyApp.Repo,
  ssl: true,
  verify_ssl: true,

# repo.ex
defmodule MyApp.Repo do
 # ...
 @impl Ecto.Repo
  def init(_type, config) do
    config =
      if config[:verify_ssl] do
        Keyword.put(config, :ssl_opts, ssl_options(config[:hostname]))
      else
        config
      end

    {:ok, config}
  end


  defp ssl_options(_server) do
    [
      # The `verify` is (since OTP 26) set to `:verify_peer` as default since that is a much safer option.
      # However in order to do so we'll need to merge the AWS certificate into our castore (since it's not
      # there by default).
      #
      # See https://hexdocs.pm/postgrex/Postgrex.html#start_link/1-ssl-client-authentication for more details
      verify: :verify_none,
      cacert: :public_key.cacerts_get()
    ]
  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.

Right, I just got so used to the error by working on it the whole day yesterday that I assumed it would be the same .

How are the certificates injected into SSL during normal a normal start of postgres? Are you running OTP 26?

No worries. It might turn out that it does have something to do with SSL.

I’m on OTP26, but the issue was there with OTP25 too. Here’s more or less my repo config:

config :myapp, MyApp.Repo,
  url: System.get_env("DATABASE_URL"),
  maintenance_database: System.get_env("MAINTENANCE_DATABASE"),
  pool_size: System.get_env("DATABASE_POOL_SIZE") |> String.to_integer(),
  log: System.get_env("LOG_LEVEL_ECTO") |> String.to_existing_atom(),
  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)
    ]
  ]

As written above, the cert is provided in an ENV variable by Digital Ocean App Platform (PaaS), so it’s just a matter of passing it through.

@stefanchrobot Perhaps a very stupid question, but is the application live and running already so that it’s been verified that the ssl_opts is passed through from the repo configuration properly?

I’m assuming that your configuration lives inside the runtime configuration so that shouldn’t be a problem either..

Have you been able to look at the connection logs at the database to see if it’s trying to connect?