Problem Connecting to Remote psql via URL

I’m trying to connect to a remote using a URL (in Phoenix).

I already have a local DB that works. The commented-out are the defaults for that. I leave these in below in case it’s part of the problem?

config :myApp, MyApp.Repo,
  url: "postgres://my_db_name:my_db_password@raja.db.elephantsql.com/my_db_name"
  #username: "postgres",
  # password: "postgres",
  # hostname: "localhost",
  # database: "myapp_dev",
  # stacktrace: true,
  # show_sensitive_data_on_connection_error: true,
  # pool_size: 10

I get this error:

[error] Postgrex.Protocol (#PID<0.824.0>) failed to connect
: ** (Postgrex.Error) FATAL 53300 (too_many_connections) 
too many connections for role "my_db_name"

Using psql to test, I can connect to my_db_name=>

psql postgres://my_db_name:my_db_password@raja.db.elephantsql.com/my_db_name

Note the local DB is called myapp_dev while the remote is called my_db_name. In case this is interfering somehow.

I consulted other posts like plus the Ecto URL docs. Seems like I am missing something fundamental still.

The error is “too many connections” and that makes me think that you need to look at your pool_size. If I remember correctly Ecto’s default is 10.

Looking at Database plans and pricing | ElephantSQL it seems the free plan allows only 5 concurrent connections. So this could explain the error.

Just try setting the correct pool_size: 5 in your config.

Edit: for dev you should keep the other options like

stacktrace: true,
show_sensitive_data_on_connection_error: true,
2 Likes

This was the solution! I’m getting other, more clear errors now. I’ll post a new question if I need help w those.

It would have been hard for me ever figure out this was the reason, so Thanks!

People are so helpful in this community : )

1 Like

fyi in the same way I’ve been playing around with https://bit.io/ and encountering errors :smiley:

Once I figure out how to connect over TLS I will let you know.

1 Like

Ohh, that looks good. Thanks for tool tip!

Actually since it’s elephantDB specific:

I can’t drop the DB now.

mix ecto.reset (which I use alot) returns

9:35:29.583 [error] GenServer #PID<0.701.0> terminating
** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "XXX", user "my_db_name", database "postgres", SSL off
    (db_connection 2.4.3) lib/db_connection/connection.ex:100: DBConnection.Connection.connect/2
    (connection 1.1.0) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 4.3) proc_lib.erl:240: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol
** (Mix) The database for MyApp couldn't be dropped: killed

psql says I can’t do it while I’m logged into it.

Edit: Oh, it says no pg_hba.conf entry. I guess this is something. SInce it’s a remote DB, how can I fix this?

What problems are you finding with https://bit.io? I was able to connect just by adding the ssl: true and ssl_opts to my Repo config:

%URI{host: database_host} = URI.parse(database_url)
...
# inside repo config:
    ssl: true,
    ssl_opts: [
      verify: :verify_peer,
      # Cert extracted from: https://curl.se/docs/caextract.html and put inside /priv dir
      cacertfile: Path.join(:code.priv_dir(:my_app), "cert/cacert.pem"),
      # see https://pspdfkit.com/blog/2022/using-ssl-postgresql-connections-elixir/
      server_name_indication: to_charlist(database_host),
      customize_hostname_check: [
        # Hosting provider uses a wildcard certificate. By default, Erlang does not support
        # wildcard certificates. This function supports validating wildcard hosts
        match_fun: :public_key.pkix_verify_hostname_match_fun(:https)
      ]
    ]

1 Like

No issue with bit.io. Was with elephant. Issue was with wrong pool size, which I marked as solution above. :slightly_smiling_face: