Ecto connection error with gcloud postgres instance

Questions / Help
, ,
1

I am trying to connect to a gcloud postgres instance from my application
this the config

config :hello, Hello.Repo,
  username: System.get_env("POSTGRES_USER", "postgres"),
  password: System.get_env("POSTGRES_PASSWORD", "postgres"),
  database: "hello_dev",
  hostname: "/cloudsql/<project_name>:<region>:<instance_name>,
  pool_size: 10

but getting error non-existing domain
I tried to use socket in place of hostname but it’s not working

Thanks for the help.

2

Well, what you specified as a hostname does not indeed look like a valid one.

Try ping '/cloudsql/<project_name>:<region>:<instance_name>' in your terminal.

Also you have a syntax error – at least here in the forum post, maybe not inside your config file – where you are missing a closing " after the supposed hostname.

3

the ping didn’t work. I tried to use external_ip instead of the instance name string. But getting connection timeout.
When I run cloud SQL proxy in local, can connect using localhost but I am looking for a way to connect without proxy

And the error was in the forum only.

4

Is your app and you db on the same instance? You probably want to try to internal IP in that case assuming they are at different addresses.

5

My db is runing on google cloud and i am deploying my application to google cloud run.
Now I have only external IP enbaled that can be used with cloud sql proxy.
So the right question would be how to connect the app in cloud run to the db instance with external IP

6

There’s a whole library that they provide to make that connect in Python, go and Java. Honestly I think your best bet is to enable an internal IP, put them on the same box (localhost) or use the external IP and figure out how to use the Unix socket option.

7

I tried to use the private IP using VPC connector. But getting the same connection error
followed this article connect to cloud sql

8

Upon reading this again I realized I forgot to tell you that you should try to replace hostname with socket_dir.

…Though judging by that Postgrex PR – by @hauleth – it seems that hostname should work as well. Or I am reading it wrong, maybe the hostname support is only through specifying the PGHOST variable, not necessarily the hostname config key.

9

One reference to a past thread: mix ecto.create and passwordless postgres - #2 by thomas.fortes

10

Another person on the forum also pasted a CloudSQL config here: Preparing the production environment for deployment in GCP with Cloud SQL database

11

…and another one: Ecto high database time with Cloud SQL - #3 by zatae

12

…and one more because why not :003:

13

Thank you for all the thread links.
I tried to use socket_dir: "/cloudsql/<project_name>:<region>:<instance_name> in config.
But i am getting db connection error failed to connect: ** (DBConnection.ConnectionError) tcp connect (/cloudsql/<project_name>:<region>:<instance_name>/.s.PGSQL:5432): no such file or directory

Not sure what am i doing wrong here?

14

I was able to fix the issue by adding `cloud sql connection instance in my terraform config.

metadata {
   annotations = {
      "run.googleapis.com/cloudsql-instances" = google_sql_database_instance.postgres_instance.connection_name
      ...
   }
}

And in the direct deployment from gcloud console, my service account was different so i was missing the permissions to connect to cloud sql.