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.
hlx
March 24, 2023, 3:08pm
2
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
hlx
March 24, 2023, 3:27pm
4
fyi in the same way I’ve been playing around with https://bit.io/ and encountering errors
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.