Cannot connect ecto to pgbouncer deployed in fly io

Questions / Help
, ,
1

Hi everyone.
We are trying to use pgbouncer in front of a postgres. The pgbouncer is deployed as a flyio app,
and our elixir service is also deployed in fly io too.
We have also verified we can connect to the pgbouncer using psql and with running Postgrex.start_link/1 directly.
However, when using ecto, we are still receiving errors.

Example with Postgrex

iex(1)> Mix.install([:postgrex])
:ok
iex(2)> {:ok, pid} = Postgrex.start_link(hostname: "some-pg-bouncer-stg.fly.dev", port: 6432, username: "usernamehere", password: "passwordhere", database: "dbhere", socket_options: [:inet6])
{:ok, #PID<0.244.0>}
iex(3)> Postgrex.query!(pid, "SELECT id from tickets limit 1", [])
%Postgrex.Result{
  command: :select,
  columns: ["id"],
  rows: [
    [84058]
  ],
  num_rows: 1,
  connection_id: 1803789234,
  messages: []
}

However, setting the same options in our config files

config :db, DB.Repo,
  username: "usernamehere",
  password: "passwordhere",
  hostname: "some-pg-bouncer-stg.fly.dev",
  database: "dbhere",
  port: 6432,
  socket_options: [:inet6],

causes our application to keep failing to connect with logs like

[error] Postgrex.Protocol (#PID<0.522.0>) failed to connect: ** (DBConnection.ConnectionError) tcp recv (idle): closed

Any ideas on what options to pass to the Ecto.Repo config or what other changes are needed to make this work?

2

I am on my phone but there is a prepare: :unnamed option you can pass. I believe it is documented in Postgrex docs/README. Search for pgbouncer. :slight_smile:

1 Like
3

Thanks!
Initially I got it to work without prepare: :unnamed, but it turned out pgbouncer was set to session mode.
Then I set prepare: :unnamed in the Ecto.Repo config and switched to transaction mode in pgbouncer and everything just worked.