A set URL is not a schema

F7.2 made the database host resolve: runtime.exs reads DATABASE_URL, Postgrex connects over Fly’s IPv6 network, and the connection authenticates against portal on portal-postgres. A connection that opens and authenticates is the trap: it proves the URL resolves, the credentials are accepted, and the named database exists — and proves nothing about the schema inside it. On the first bring-up that schema was empty. portal had been created, but no migration had ever run against it, so it carried zero tables.

Two steps hide inside “the database is ready.” First the database is created — the named database exists and a role can connect to it. Then it is migrated — the tables, indexes, and the schema_migrations bookkeeping row are applied. A DATABASE_URL that is merely set carries the first and says nothing of the second. The deploy work left the migrate step for the bring-up, which is why the first machine met a reachable database with no tables in it.

The inspector below contrasts the two states. Select DATABASE_URL set and a \dt in psql returns no relations — the connection works, the schema is empty. Select schema created and the same \dt lists courses, events, and schema_migrations, the state after the migration ran.

A built release carries no mix, and on the first bring-up the machine could not boot well enough to run a migration on its own: the engine reads the event log synchronously at startup, so an unmigrated database crashed the release before it could apply a schema (the boot-time coupling is the next dive). The schema had to be applied from outside the machine. The path the bring-up took opens a tunnel onto the cluster and runs the release-native migrator locally against it.

fly proxy 15432:5432 -a portal-postgres opens localhost:15432 and forwards it to portal-postgres.internal:5432 — the Postgres app’s port, reached over Fly’s private network. With the tunnel up, Portal.Release.migrate/0 runs on the operator’s own machine with DATABASE_URL repointed at the proxy — the URL takes the shape postgres://<user>:<pass>@localhost:15432/portal, where the host and port name the local end of the tunnel and the credentials are the cluster’s. The migrator is the same Portal.Release.migrate/0 a machine would run; only the connection is redirected through the forwarded port.

The on-machine alternative does exist once the release can boot: fly ssh console -a echo-portal -C '/app/bin/portal eval "Portal.Release.migrate()"' runs the same entry point inside the release on echo-portal. The proxy path is what applies the very first schema, before the machine can stay up long enough to run anything itself.

The proxy path applies the schema only if two things hold, and neither is on the surface of the fly proxy documentation. The first is which node the proxy fronts. portal-postgres is an HA postgres-flex cluster: one writable primary and one or more read-only replicas. A migration is a write — CREATE TABLE and the row it writes into schema_migrations both mutate the database — so it must land on the primary. Run against a replica it fails, because a replica is read-only.

The check is one query. psql … -c "SELECT pg_is_in_recovery()" returns f on the writable primary and t on a replica (a replica is permanently in recovery, replaying the primary’s log). The proxy must front the node that answers f. The second precondition is the role: the migration needs CREATE on the database, since both CREATE TABLE and the schema_migrations bookkeeping require it — the bring-up used a superuser role, which carries it.

The dated record of the bring-up reads: the migration was run this way against the writable primary (pg_is_in_recovery() returned f, a superuser role), applying 20260604063825_create_courses then 20260604063826_create_events — after which courses, events, and schema_migrations were populated. Those two migrations create the schema the boot then reads: courses with a :bigint Snowflake primary key (create table(:courses, primary_key: false) plus add :id, :bigint, primary_key: true, autogenerate off), title/slug as null: false, a published boolean defaulting to false, timestamps(type: :utc_datetime), and a create unique_index(:courses, [:title]); and events — the append-only log behind Portal.EventStore.Postgres — with a :bigint Snowflake id, stream/type as null: false, a :integer seq, a :map jsonb data defaulting to %{}, timestamps(type: :utc_datetime, updated_at: false), and a create index(:events, [:stream, :seq]).

One host-scheme seam stays open as a roadmap detail. The .internal name the proxy uses is the direct-6PN, same-org host this umbrella reaches over; a Fly Managed Postgres cluster is reached over .flycast instead and needs fly mpg attach to wire it in. Choosing the wrong scheme is one more :nxdomain source — a name that does not resolve, not a database that is down. The check simulator below runs the writable-primary precondition: choose the node the proxy fronts and read pg_is_in_recovery() and whether the migration succeeds.