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

Answer 1

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

Answer 2

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.