Newly launched Elixir (Phoenix) app can’t connect to postgres

Phoenix
,
1

Hello, I’ve been trying to launch a new Phoenix (1.7) app on fly using the fly launch command (following the instructions in the Getting Started guide) but keep getting the following error:

[error] Could not create schema migrations table. This error usually happens due to the following:
    * The database does not exist
    * The "schema_migrations" table, which Ecto uses for managing
      migrations, was defined by another library
    * There is a deadlock while migrating (such as using concurrent
      indexes with a migration_lock)
  To fix the first issue, run "mix ecto.create" for the desired MIX_ENV.
  To address the second, you can run "mix ecto.drop" followed by
  "mix ecto.create", both for the desired MIX_ENV. Alternatively you may
  configure Ecto to use another table and/or repository for managing
  migrations:
      config :hello_elixir, HelloElixir.Repo,
        migration_source: "some_other_table_for_schema_migrations",
        migration_repo: AnotherRepoForSchemaMigrations
  The full error report is shown below.
  ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2986ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:
    1. Ensuring your database is available and that you can connect to it
    2. Tracking down slow queries and making sure they are running fast enough
    3. Increasing the pool_size (although this increases resource consumption)
    4. Allowing requests to wait longer by increasing :queue_target and :queue_interval
  See DBConnection.start_link/2 for more information
      (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
      (elixir 1.14.3) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
      (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:1005: Ecto.Adapters.SQL.execute_ddl/4
      (ecto_sql 3.10.1) lib/ecto/migrator.ex:738: Ecto.Migrator.verbose_schema_migration/3
      (ecto_sql 3.10.1) lib/ecto/migrator.ex:552: Ecto.Migrator.lock_for_migrations/4
      (ecto_sql 3.10.1) lib/ecto/migrator.ex:428: Ecto.Migrator.run/4
      (ecto_sql 3.10.1) lib/ecto/migrator.ex:170: Ecto.Migrator.with_repo/3
      nofile:1: (file)

Commenting out the deploy release command in the fly.toml file (release_command = "/app/bin/migrate") allows the app to deploy, but the deployed app is still not actually able to connect to the database and continuously throws this error:

[error] Postgrex.Protocol (#PID<0.1814.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (app-db.flycast:5432): timeout

The database is up and accessible from my local machine, so the problem seems to be just connecting to it from the app machine.

The DATABASE_URL secret was set automatically when running fly launch and appears to be set correctly.

Any pointers would be gratefully received!

1 Like
2

I have the same issue in LHR region. I contacted support (who were helpful) and here is what they suggested trying:

  • scale app up to 2 and try redeploying (this worked for me at first, but not any more)
  • try changing the domain in your database URL from xxx.flycast to top1.nearest.of.xxx.internal (haven’t tried)

As the issue has resurfaced for me, I’m going to do more debugging and will update if i find fixes.

3

Have a working solution (thanks to the support team), here’s what I did

  1. detach postgres
  2. destroy postgres
  3. create new postgres instance, copy connection string
  4. attach postgres to app
  5. unset DATABASE_URL
  6. set DATABASE_URL

fly secrets set DATABASE_URL=“postgres://user:password@top1.nearest.of.your-db-name.internal:5432/app?sslmode=disable” -a app

  1. Deploy app, should work now
4

Thanks @biku !

I was also trying to deploy to LHR, so possibly this issue is just affecting that region?

I also contacted support and received the same suggestions of scaling the app up to 2 (which worked) and/or changing the database URL from xxx.flycast to top1.nearest.of.xxx.internal (which I also didn’t try).

The issue seemed to resolve itself for me on Friday and I was once again able to launch a new app without having to scale up or change the database URL. However, I haven’t tried launching an app since then, so it’s interesting to hear that the issue resurfaced for you.

Thanks for the latest workaround steps, I’ll give those a go if I encounter the same issue in the future. However, for anyone on the Fly team reading this, it seems like there could be some underlying issue, as it doesn’t seem like it should be necessary to manually destroy and recreate the initial postgres instance as part of launching an app! :slight_smile:

5

We’re taking a look at Flycast routing on a specific host in lhr that seems to be misbehaving.

To confirm, the work around is just to switch the database connection string to use the .internal string instead of .flycast . You don’t need to delete your postgres instance.

6

Thanks @Sam-Fly, much appreciated!