Can't connect to fly postgres via JDBC

Questions / Help
1

Hello, I’m trying to deploy a Keycloack server to fly.io using machines.

I’ve created a Postgres dev cluster and used the attach command to create a user and a database.
I set the environment variable KC_DB_URL to the following:

jdbc:postgresql://top2.nearest.of.my-auth-pgql.internal:5432/my_auth

as well as KC_DB_USERNAME and KC_DB_PASSWORD accordingly to the provided credentials from the attach command.

I made sure both postgres and my keycloack server are in the same organization.
However when Keycloack starts up i get this error:

022-10-30T22:47:47.782 app[148e474fd77789] lhr [info] 2022-10-30 22:47:47,781 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode

2022-10-30T22:47:47.783 app[148e474fd77789] lhr [info] 2022-10-30 22:47:47,783 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to obtain JDBC connection

2022-10-30T22:47:47.785 app[148e474fd77789] lhr [info] 2022-10-30 22:47:47,785 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: The connection attempt failed.

2022-10-30T22:47:47.786 app[148e474fd77789] lhr [info] 2022-10-30 22:47:47,786 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: top2.nearest.of.my-auth-pgql.internal

Caused by: java.net.UnknownHostException: top2.nearest.of.my-auth-pgql.internal

note that the important error is the last one:

Caused by: java.net.UnknownHostException: top2.nearest.of.my-auth-pgql.internal

which seems that jdbc or the fly machine is not able to resolve the host provided
Any help would be appreciated

1 Like
2

I had a similar issue. After a lot of trial and error, I managed to connect to PostgreSQL with JDBC.

I don’t use the DATABASE_URL suggested by Fly.io. I tried to convert it to a JDBC URL, but it didn’t work.

I create the JDBC connection string joining the Username, Password and Hostname printed by the flyctl CLI upon database creation.

This is the format that works for me:

"jdbc:postgresql://HOSTNAME:5432/?user=postgres&password=PASSWORD"

I noticed that HOSTNAME matches POSTGRES_APP.internal, where POSTGRES_APP is the name that Fly.io assigned to your PostgreSQL app.

Also, replace HOSTNAME and 5432 with localhost and your port of choice when you are proxying the database (e.g. flyctl proxy 15432:5432 -a POSTGRES_APP).

3

Hey @jackdbd

Thanks for this, however, i tried appending username and password as a query string but I still get an unknown host error as above :frowning: very disappointing