postgresqlspring-bootconnection-stringrender.com
trying to deploy: org.postgresql.util.PSQLException: The connection attempt failed
Having account on render.com, I have created postgres database and got username, password and external link (host, port is 5432), So I specified the url as suggested here (jdbc:postgresql://<host>:<port>/<dbname>?user=<username>&password=<password>).
application.properties:
#spring.datasource.url=jdbc:postgresql://localhost:5432/webdev_simplified
spring.datasource.url=jdbc:postgresql://@dpg-cligukdkt82s73c4s830-a.oregon-postgres.render.com:5432/webdev_simplified?user=webdev_simplified_user&password=redacted
#spring.datasource.username=postgres
#spring.datasource.username=webdev_simplified_user
#spring.datasource.password=postgres
#spring.datasource.password=c8Ca0ZTgSkokADeeEeoIM9WFPPjPLXHx
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG
I have used what they call external url to connect to it from my local ubuntu. But when run mvn clean package, I get:
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v3.2.0)
2023-11-28T21:11:52.164+01:00 INFO 16808 --- [ main] com.example.j2.J2ApplicationTests : Starting J2ApplicationTests using Java 17.0.2 with PID 16808 (started by shepherd in /home/shepherd/Desktop/java/j2)
2023-11-28T21:11:52.166+01:00 INFO 16808 --- [ main] com.example.j2.J2ApplicationTests : No active profile set, falling back to 1 default profile: "default"
2023-11-28T21:11:53.139+01:00 INFO 16808 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2023-11-28T21:11:53.220+01:00 INFO 16808 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 69 ms. Found 1 JPA repository interface.
2023-11-28T21:11:53.793+01:00 INFO 16808 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2023-11-28T21:11:54.843+01:00 ERROR 16808 --- [ main] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Exception during pool initialization.
org.postgresql.util.PSQLException: The connection attempt failed.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:315) ~[postgresql-42.2.15.jar:42.2.15]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) ~[postgresql-42.2.15.jar:42.2.15]
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:225) ~[postgresql-42.2.15.jar:42.2.15]
...
I am sure I have the host correct beacuse I just copy/pasted it (just as the psasword). So what could go wrong?
here is source code: https://github.com/shepherd123456/springapp_with_postgres
PS: the error is long so I selected only parts I think may be important:
...
Caused by: java.net.UnknownHostException: @dpg-cligukdkt82s73c4s830-a.oregon-postgres.render.com
at java.base/sun.nio.ch.NioSocketImpl.connect(NioSocketImpl.java:567) ~[na:na]
at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:327) ~[na:na]
at java.base/java.net.Socket.connect(Socket.java:633) ~[na:na]
at org.postgresql.core.PGStream.createSocket(PGStream.java:231) ~[postgresql-42.2.15.jar:42.2.15]
at org.postgresql.core.PGStream.<init>(PGStream.java:95) ~[postgresql-42.2.15.jar:42.2.15]
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:98) ~[postgresql-42.2.15.jar:42.2.15]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:213) ~[postgresql-42.2.15.jar:42.2.15]
... 120 common frames omitted
2023-11-29T11:11:33.472+01:00 INFO 4223 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
2023-11-29T11:11:33.572+01:00 INFO 4223 --- [ main] org.hibernate.Version : HHH000412: Hibernate ORM core version 6.3.1.Final
2023-11-29T11:11:33.639+01:00 INFO 4223 --- [ main] o.h.c.internal.RegionFactoryInitiator : HHH000026: Second-level cache disabled
2023-11-29T11:11:33.981+01:00 INFO 4223 --- [ main] o.s.o.j.p.SpringPersistenceUnitInfo : No LoadTimeWeaver setup: ignoring JPA class transformer
2023-11-29T11:11:34.028+01:00 INFO 4223 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2023-11-29T11:11:35.030+01:00 ERROR 4223 --- [ main] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Exception during pool initialization.
org.postgresql.util.PSQLException: The connection attempt failed.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:315) ~[postgresql-42.2.15.jar:42.2.15]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) ~[postgresql-42.2.15.jar:42.2.15]
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:225) ~[postgresql-42.2.15.jar:42.2.15]
...
...
at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:495) ~[surefire-booter-3.1.2.jar:3.1.2]
2023-11-29T11:11:35.045+01:00 ERROR 4223 --- [ main] j.LocalContainerEntityManagerFactoryBean : Failed to initialize JPA EntityManagerFactory: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Unable to determine Dialect without JDBC metadata (please set 'javax.persistence.jdbc.url', 'hibernate.connection.url', or 'hibernate.dialect')
2023-11-29T11:11:35.047+01:00 WARN 4223 --- [ main] o.s.w.c.s.GenericWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Unable to determine Dialect without JDBC metadata (please set 'javax.persistence.jdbc.url', 'hibernate.connection.url', or 'hibernate.dialect')
2023-11-29T11:11:35.067+01:00 INFO 4223 --- [ main] .s.b.a.l.ConditionEvaluationReportLogger :
Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled.
2023-11-29T11:11:35.092+01:00 ERROR 4223 --- [ main] o.s.boot.SpringApplication : Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Unable to determine Dialect without JDBC metadata (please set 'javax.persistence.jdbc.url', 'hibernate.connection.url', or 'hibernate.dialect')
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1775) ~[spring-beans-6.1.1.jar:6.1.1]
...
...
[INFO]
[INFO] Results:
[INFO]
[ERROR] Errors:
[ERROR] J2ApplicationTests.contextLoads » IllegalState Failed to load ApplicationContext for [WebMergedContextConfiguration@62ea8931 testClass = com.example.j2.J2ApplicationTests, locations = [], classes = [com.example.j2.J2Application], contextInitializerClasses = [], activeProfiles = [], propertySourceDescriptors = [], propertySourceProperties = ["org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true"], contextCustomizers = [org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@74ea2410, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@248e319b, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@548d708a, org.springframework.boot.test.autoconfigure.actuate.observability.ObservabilityContextCustomizerFactory$DisableObservabilityContextCustomizer@1f, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizer@353352b6, org.springframework.boot.test.context.SpringBootTestAnnotation@85af5aae], resourceBasePath = "src/main/webapp", contextLoader = org.springframework.boot.test.context.SpringBootContextLoader, parent = null]
[INFO]
[ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
...
So this is saying UnknownHostException:
Caused by: java.net.UnknownHostException: @dpg-cligukdkt82s73c4s830-a.oregon-postgres.render.com
but then again, the host is copy/pasted from the site. You can see the image attached, in External Database Url the same url (starting with @). I will copy the unmodified (there is postgres instead of postgresql and also user and password are specified before url, not as query parameters as suggested from above linke) it from my render.com account here again:
postgres://webdev_simplified_user:c8Ca0ZTgSkokADeeEeoIM9WFPPjPLXHx@dpg-cligukdkt82s73c4s830-a.oregon-postgres.render.com/webdev_simplified
Solution
Based on the DNS error message and experimenting with my own simple jdbc example, you need to leave the @ off from your hostname. Just like you can't specify the username and password in front of the hostname, you also can't put a bare @ in front of it. It is interpreted as a literal character in the hostname.
Alas, not all drivers support all ways of formatting db URLs.