Phoenix with Managed Postgres

Illustration by Annie Ruygt of a Phoenix bird resting with Frankie the balloon looking on

For general connection configuration that applies to all languages — connection lifetime, idle timeouts, proxy restart behavior, and troubleshooting — see Connect Your Client. This guide explains the key Managed Postgres (MPG)-specific adjustments you need when connecting a Phoenix app. We’ll focus on:

Connection Pooling Settings
Running Migrations
Using Oban with MPG
Troubleshooting and Common Issues

This guide doesn’t cover Phoenix or Ecto setup—we assume you already have that in place.

Connection Pooling with PgBouncer + Ecto

Fly.io MPG uses PgBouncer for connection pooling. By default, MPG clusters run in Session mode but Ecto requires Transaction mode due to how it handles connection pooling. To configure PgBouncer:

Open your MPG cluster in the dashboard.
Go to Connect → Pooler settings.
Set Pool mode to Transaction.

You’ll also need to update your repo config:

config :my_app, MyApp.Repo,
  url: System.fetch_env!("DATABASE_URL"),
  pool_size: 8,
  timeout: 15_000,                 # query timeout
  max_lifetime: 540_000..600_000,  # max connection lifetime; the range staggers reconnects
  prepare: :unnamed

prepare: :unnamed is required because named prepared statements don’t work with PgBouncer in transaction mode.

Running Migrations

While you’ll need to use Transaction mode with Ecto for most cases, Migrations are a special case. Migrations rely on advisory locks and session stickiness, which PgBouncer’s transaction pooling mode doesn’t support. For running migrations you’ll need to configure your app to use the direct database URL instead. Update your secrets to add a DIRECT_DATABASE_URL

fly secrets set \
  DATABASE_URL="postgresql://...@pgbouncer.YOUR_CLUSTER.flympg.net/fly-db" \
  DIRECT_DATABASE_URL="postgresql://...@direct.YOUR_CLUSTER.flympg.net/fly-db"

In your fly.toml, update your release command to use the direct connection for running your migration:

[deploy]
  release_command = "/bin/sh -lc 'DATABASE_URL=$DIRECT_DATABASE_URL bin/migrate'"

Or run a migration manually:

fly ssh console -C '/bin/sh -lc "DATABASE_URL=\"$DIRECT_DATABASE_URL\" bin/migrate"'

Using Oban with MPG

If you’re using the Oban library for handling Job queues, you’ll need to make a few adjustments as PgBouncer in transaction mode doesn’t support LISTEN/NOTIFY, which Oban uses for job notifications. You have a few options:

1. Use a non-Postgres notifier

# Distributed Erlang notifier
config :my_app, Oban,
  repo: MyApp.ObanRepo,
  notifier: Oban.Notifiers.PG,
  queues: [default: 10]

Or with Phoenix PubSub:

config :my_app, Oban,
  repo: MyApp.ObanRepo,
  notifier: {Oban.Notifiers.Phoenix, pubsub: MyApp.PubSub},
  queues: [default: 10]

2. Run Oban on a direct connection

If you have a requirement to use Postgres LISTEN/NOTIFY, you can set up Oban to use a direct database connection instead of using PG Bouncer

config :my_app, Oban,
  repo: MyApp.DirectRepo,
  notifier: Oban.Notifiers.Postgres,
  queues: [default: 10]

This uses direct DB connections. Each connection is an Ecto pool entry that bypasses PgBouncer. Because direct connections are limited, keep the pool very small.

Legacy fallback (Oban < 2.14)

Older versions required the Repeater plugin. Since Oban 2.14 (2023), polling fallback is built-in and Repeater is deprecated.

Troubleshooting and common errors

Troubleshooting checklist

Set PgBouncer pool mode to Transaction.
Set prepare: :unnamed on every Repo.
Use the pooled URL for app traffic and the direct URL for migrations and other session-scoped operations (advisory locks, etc.).
Keep Ecto pools modest on the Basic plan (API repo 8, Oban repo 6). Reduce if most connections sit idle.
Check MPG metrics around error timestamps for restarts or memory pressure.

Common errors and fixes

tcp recv (idle): closed or tcp recv (idle): timeout — These occur when the Fly proxy or PgBouncer closes an idle connection, often during routine proxy deployments. Ecto reconnects automatically, so these are transient. To reduce their frequency, lower your pool size so fewer connections sit idle. For a full explanation of why this happens and how to configure connection lifetime and idle timeouts, see Client-Side Connection Configuration — Troubleshooting.
FATAL 08P01 protocol_violation on login — Set prepare: :unnamed and ensure PgBouncer is in Transaction mode.
Oban jobs not running — Use a non-Postgres notifier (PG or Phoenix) behind PgBouncer, or run Oban on a direct Repo. On Oban ≥ 2.14, do not add Repeater (polling fallback is automatic when PubSub isn’t available).
Migrations hanging or failing — Run migrations with the direct database URL (via release_command or a one-off SSH command), not through PgBouncer.
non-existing domain - :nxdomain - Make sure ipv6 is enabled

​Connection Pooling with PgBouncer + Ecto

​Running Migrations

​Using Oban with MPG

​1. Use a non-Postgres notifier

​2. Run Oban on a direct connection

​ Legacy fallback (Oban < 2.14)

​Troubleshooting and common errors

​Troubleshooting checklist

​Common errors and fixes