How do I solve postgresql error "connection attempt failed"?

Question

I wanted to build my springboot project. Then I want to dockerize my code. But when I built, I got error. I think this occured caused by postgresql setting. But I could not find reason.

Could you please help me?

docker-compose.yml file;

version: '2'
services:
  web:
    build: .
    ports:
      - 8080:8080
  db:
    container_name: productdb
    image: postgres:9.5
    volumes:
    - sample_db:/var/lib/postgresql/data
    environment:
    - POSTGRES_PASSWORD=bright
    - POSTGRES_USER=postgres
    - POSTGRES_DB=productdb
    - PGDATA=/var/lib/postgresql/data/pgdata
    ports:
    - "5432:5432"

volumes:
  productdb: {}

application.yml file;

server:
  port: 8761
eureka:
  client:
  registerWithEureka: false
  fetchRegistry: false
  server:
  enableSelfPreservation: false
  waitTimeInMsWhenSyncEmpty: 0

spring:
  application:
    name: product-service
  datasource:
    url: jdbc:postgresql://db:5432/productdb
    username: postgres
    password: xxxx
    initialization-mode: always
  jpa:
    show-sql: true
    hibernate:
      ddl-auto:
    properties:
      hibernate:
        temp:
          use_jdbc_metadata_defaults: false

Error looks like;

org.postgresql.util.PSQLException: The connection attempt failed.

Thank you

Is the 'web' container hosting your spring app? If yes, can you ping from inside your web container to the postgresql container? — lvthillo, Feb 10 '19 at 12:08
Yes my app is web container. But i could not ping to postgresql container. When I used jdbc:postgresql://localhost:5432/productdb in application.yml then I can build successfully but then when I wanted to run docker, following error occured. org.postgresql.util.PSQLException: Connection to localhost:5432 refused — Ibrahim Ates, Feb 10 '19 at 12:14
You don't need to connect with localhost, (localhost = container itself, you need to connect with the container name of the postgres container when you want to connect from another container; — lvthillo, Feb 11 '19 at 08:28
lvthillo, yeah I know. That is why I ve changed localhost. Now I am using as above. But when I wanted to clean-install mvn package, I ve got error "org.postgresql.util.PSQLException: The connection attempt failed." — Ibrahim Ates, Feb 11 '19 at 08:55

JRichardsz · Answer 1 · 2019-09-24T14:26:21.307

If your docker-compose.yml file is well configured, it should be start two containers:

docker ps

source: https://intelligentbee.com/2017/09/18/setup-docker-symfony-project/

One for app and one for db.

These containers are in the same host, so if your web need to connect to the database, you must the ip instead : localhost, 127.0.0.1 or 0.0.0.0

You cat get the ip with this

hostname -I| awk '{printf $1}'

If your web and your database would be in different host, you can use the public ip where is hosted the database. But as you are using docker-compose this is not the case.

I suggest you to test if your database is ready and available, before using it in your web app.

In order to test your database , You can following one of these approaches:

Check db status with telnet

There are several way , but the easiest option is the telnet command. For instance, in order to test if mysql container is ready to use in the same machine where was started:

telnet localhost 3306

If your mysql is ready, telnet must show you a result like the following picture:

Any other negative result, would indicate that your mysql container is exited or wrong.

Note:Change 3306 for the correct postgress port

Check db status with Database IDE

Other option for UI users is testing the database connection using some Database IDE. Just download one of the several postgress client IDEs and testing your database.

Don't hardcode parameters

It is a good practice to externalize configuration using environment variables. Spring and docker know and allow us to use them.

So, modify your application.yml :

From

datasource:
    url: jdbc:postgresql://db:5432/productdb

To

datasource:
    url: jdbc:postgresql://${DATABASE_HOST}:5432/productdb

For development, in your eclipse use run as configurations >> environment section

For production you can:

export variable before run
pass it to your docker run sentence...

docker run -d \
--name my_funny_api \
-p 8080:8080 \
-e "DATABASE_HOST=10.10.01.52" \
-i -t my_funny_api_image

or

export HOST_IP=$(hostname -I| awk '{printf $1}')

docker run -d \
--name my_funny_api \
-p 8080:8080 \
-e "DATABASE_HOST=${DATABASE_HOST}" \
-i -t my_funny_api_image

Finally to avoid manually task to manage your variables, you can use : http://github.com/jrichardsz/tachikoma-ops

JRichardsz, Before running on docker when I try to build I got "attemption failed" error. — Ibrahim Ates, Feb 10 '19 at 15:09
mvn clean package, does not start the app. Just compile it. Could you attach the entire fail log? Maybe is a unit test error. — JRichardsz, Feb 11 '19 at 23:45

score 1 · Answer 2 · answered Mar 30 '20 at 23:15

Using DataGrip software and DB in DigitalOcean. Got error

[08001] The connection attempt failed. java.net.SocketTimeoutException: connect timed out.

Made sure my current IP was one of the allowed inbound connections and that worked. (Even though the error should probably have been different.)

Hope this is useful to someone eventually.

score 0 · Answer 3 · answered Oct 17 '20 at 19:58

Your DB should accept connections outside of the container

sudo docker run --name pg -p 5432:5432 -v pg_data:/var/lib/postgres/data -e POSTGRES_DB=mydb -e POSTGRES_USER=pg_user -e POSTGRES_PASSWORD=pg_password -d postgres -c "listen_addresses=*"

"listen_addresses=" It will accept connection outside of the container*

You can use follow credential to connect your spring boot project

db_user=pg_user

db_password=pg_password

db_url=jdbc:postgresql://localhost:5432/mydb

How do I solve postgresql error "connection attempt failed"?

3 Answers3

Check db status with telnet

Check db status with Database IDE

Don't hardcode parameters

Linked