DB migration for Postgres cluster (in a Phoenix app, using `fly_postgres`)

1

Hi,

I’m running an Elixir/Phoenix app, and upon app start I run migration (just the standard mix way of running db migration).

I just started experimenting with Fly’s postgres cluster, deploying to multiple regions. Upon app start, the migration seems to get run on all instances, causing the read replica to throw up:

2023-07-01T08:12:18Z app[32874409a3de18] syd [info]08:12:18.358 [error] Could not create schema migrations table. This error usually happens due to the following:
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]  * The database does not exist
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]  * The "schema_migrations" table, which Ecto uses for managing
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    migrations, was defined by another library
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]  * There is a deadlock while migrating (such as using concurrent
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    indexes with a migration_lock)
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]To fix the first issue, run "mix ecto.create" for the desired MIX_ENV.
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]To address the second, you can run "mix ecto.drop" followed by
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]"mix ecto.create", both for the desired MIX_ENV. Alternatively you may
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]configure Ecto to use another table and/or repository for managing
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]migrations:
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    config :petal_pro, PetalPro.Repo.Local,
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]      migration_source: "some_other_table_for_schema_migrations",
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]      migration_repo: AnotherRepoForSchemaMigrations
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]The full error report is shown below.
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]** (Postgrex.Error) ERROR 25006 (read_only_sql_transaction) cannot execute CREATE TABLE in a read-only transaction
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    (elixir 1.15.1) lib/enum.ex:1693: Enum."-map/2-lists^map/1-1-"/2
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:1005: Ecto.Adapters.SQL.execute_ddl/4
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    (ecto_sql 3.10.1) lib/ecto/migrator.ex:738: Ecto.Migrator.verbose_schema_migration/3
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    (ecto_sql 3.10.1) lib/ecto/migrator.ex:552: Ecto.Migrator.lock_for_migrations/4
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    (ecto_sql 3.10.1) lib/ecto/migrator.ex:428: Ecto.Migrator.run/4
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    (ecto_sql 3.10.1) lib/ecto/migrator.ex:170: Ecto.Migrator.with_repo/3
2023-07-01T08:12:18Z app[32874409a3de18] syd [info]    nofile:1: (file)

I’m using fly_postgres in my app and have adjusted the app code accordingly. Have I missed something? What’s the best practice to only trigger db migration on the primary region?

Thanks!

2

I ended up checking PRIMARY_REGION and FLY_REGION to only trigger it for the primary region instance. Hopefully this is the way to go.

3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.