Could not obtain connection to query metadata org.postgresql.util.PSQLException: The connection attempt failed

Question

I looked here, here, here does not solve my issue here is the error i'm getting:

2021-01-17 18:49:19.469  WARN 22952 --- [main] o.h.e.j.e.i.JdbcEnvironmentInitiator     
 : HHH000342: Could not obtain connection to query metadata

org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:315) ~[postgresql-42.2.18.jar:42.2.18]
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) ~[postgresql-42.2.18.jar:42.2.18]
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:225) ~[postgresql-42.2.18.jar:42.2.18]
    at org.postgresql.Driver.makeConnection(Driver.java:465) ~[postgresql-42.2.18.jar:42.2.18]
    at org.postgresql.Driver.connect(Driver.java:264) ~[postgresql-42.2.18.jar:42.2.18]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:560) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-3.4.5.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:68) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:176) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1224) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1255) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.3.3.jar:5.3.3]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.3.3.jar:5.3.3]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-5.3.3.jar:5.3.3]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) ~[spring-orm-5.3.3.jar:5.3.3]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.3.3.jar:5.3.3]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1847) ~[spring-beans-5.3.3.jar:5.3.3]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1784) ~[spring-beans-5.3.3.jar:5.3.3]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:609) ~[spring-beans-5.3.3.jar:5.3.3]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:531) ~[spring-beans-5.3.3.jar:5.3.3]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.3.jar:5.3.3]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.3.jar:5.3.3]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.3.jar:5.3.3]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.3.jar:5.3.3]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1159) ~[spring-context-5.3.3.jar:5.3.3]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:913) ~[spring-context-5.3.3.jar:5.3.3]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:588) ~[spring-context-5.3.3.jar:5.3.3]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144) ~[spring-boot-2.4.2.jar:2.4.2]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767) ~[spring-boot-2.4.2.jar:2.4.2]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759) ~[spring-boot-2.4.2.jar:2.4.2]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426) ~[spring-boot-2.4.2.jar:2.4.2]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:326) ~[spring-boot-2.4.2.jar:2.4.2]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1311) ~[spring-boot-2.4.2.jar:2.4.2]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1300) ~[spring-boot-2.4.2.jar:2.4.2]
    at com.springboot.project1.Project1Application.main(Project1Application.java:11) ~[classes/:na]
Caused by: java.net.UnknownHostException: bvkgnbxlgupmon:c802e8c6e31976c1ce11584fec0961bc604bce0df24501a6dfd2700240c7ba3f@ec2-3-231-241-17.compute-1.amazonaws.com
    at java.base/sun.nio.ch.NioSocketImpl.connect(NioSocketImpl.java:567) ~[na:na]
    at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:333) ~[na:na]
    at java.base/java.net.Socket.connect(Socket.java:648) ~[na:na]
    at org.postgresql.core.PGStream.createSocket(PGStream.java:231) ~[postgresql-42.2.18.jar:42.2.18]
    at org.postgresql.core.PGStream.<init>(PGStream.java:95) ~[postgresql-42.2.18.jar:42.2.18]
    at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:98) ~[postgresql-42.2.18.jar:42.2.18]
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:213) ~[postgresql-42.2.18.jar:42.2.18]
    ... 51 common frames omitted

Application.Properties:

spring.datasource.url=jdbc:postgresql://ec2-3-231-241-17.compute-1.amazonaws.com:5432/d8pv6nkjjc58qb?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
spring.datasource.username=beggnbxlgupmon
spring.datasource.password=c3402e8c6e31976c1ce11584gec0961dc604bce0df24501a6dfd2700240c7ba3f
spring.datasource.name=d8pv6nkjjc58qb
spring.jpa.database-platform = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto = none
spring.jpa.hibernate.show-sql=true
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
SPRING_DATASOURCE_TYPE = org.apache.tomcat.jdbc.pool.DataSource

Check security group on ec2 instance `ec2-3-231-241-17.compute-1.amazonaws.com` make sure port 5432 is allowed to access from application and try to `ping ec2-3-231-241-17.compute-1.amazonaws.com` to make sure DNS resolver is working fine — Thanh Nguyen Van, Jan 18 '21 at 01:12
how do I check the security group on ec2 instance you mean on the heroku ? — Nick Kahn, Jan 18 '21 at 01:13
when I ping `ec2-3-231-241-17.compute-1.amazonaws.com` getting timed out — Nick Kahn, Jan 18 '21 at 01:15
or `telnet ec2-3-231-241-17.compute-1.amazonaws.com 5432` and check security group — Thanh Nguyen Van, Jan 18 '21 at 01:30
I could not find the security group in heroku do you know where to look? — Nick Kahn, Jan 18 '21 at 01:36
i'm not sure I understand, why would I login to aws amazon and I don't even have account there and I was talking to you about the heroku db — Nick Kahn, Jan 18 '21 at 03:18
As you do not specify platforms in your question, it is assumed that your DB is in AWS and you manage it via AWS. Where is running your Spring Boot application? You don't mention it in the question — Juan Carlos Alafita, Jan 18 '21 at 03:46

Juan Carlos Alafita · Answer 1 · 2021-01-18T04:27:53.353

I dont know how is configured your DB Server. Check the options and try the way that fits your case.

The firts aproximation to resolve the problem its checking connectivity with the server, test from your machine, if its posible.

I would follow these steps to solve the problem.

1.- First test that the server accepts connection

You need the psql client available with PostgreSQL installation. Try an updated version, this allows you to connect to both new and old versions If you doesnt time to Install Postgres, you can use a Docker image Only the psql client https://hub.docker.com/r/jbergknoff/postgresql-client/

If you have the psql client, try to connect.

./psql -U beggnbxlgupmon -h ec2-3-231-241-17.compute-1.amazonaws.com
Password for user beggnbxlgupmon:
**psql: FATAL:  password authentication failed for user "beggnbxlgupmon"
FATAL:  no pg_hba.conf entry for host "x.x.xx.xx"**, user "beggnbxlgupmon", database "beggnbxlgupmon", SSL off

As can be seen, your DBA has configured IP restriction. Maybe your DBA allow connections only for certain IPs range.

Make sure that the IP or IP range where your application resides, it's allowed to connect to the DB Server.

Add the IP address of the SpringBoot application to the pg_hba.conf and make sure that the method is not set to reject. You need to modify the xxx.xxx.xxx.xxx and put the IP address to allow connections.

Updated

Security Group it's a firewall in your AWS EC2 instance. EC2 instance lives in AWS. Your DB Server is on EC2. Your instance is in AWS created via Heroku.

Additionaly, if necessary, Add the rule to allow the 5432 port in your firewall/Security Group to the range IP or specific IP. Maybe this help https://serverfault.com/questions/211244/allow-ports-on-ec2-only-for-heroku

2.- Make sure that the machine where your application reside, has connection to the Internet.

Some enviroments don't allow outbound connections to Internet. In order to discard the problem in the client. Verify that the application has access to the outside world.

thanks for the detail reply, I was able to connect using the psql and see my database and tables and able to execute the `select` statement and see my data so it means the connection is going through my psql and also I was able to connect through the broswers `http://127.0.0.1:54115/browser/` so now I'm thinking my application.properties might not be in the correct format ? — Nick Kahn, Jan 18 '21 at 15:52
I tested the format of your connection(application.propertie) and it is correct. — Juan Carlos Alafita, Jan 20 '21 at 03:50

Could not obtain connection to query metadata org.postgresql.util.PSQLException: The connection attempt failed

1 Answers1

1.- First test that the server accepts connection

2.- Make sure that the machine where your application reside, has connection to the Internet.