my spring boot app container can't connect to my postgresql database

Question

I'm trying to dockerize my spring boot app that uses postgresql so I first run ./mvnw clean package -DskipTests after I did cp target/docker-spring-boot-postgres-0.0.1-SNAPSHOT.jar src/main/docker

here's my code

docker-compose.yml

version: '2'

services:
  app:
    image: 'demo:latest'
    build:
      context: .
    container_name: app
    depends_on:
      - db
    environment:
      - SPRING_DATASOURCE_URL=jdbc:postgresql://db:5432/postgreTest
      - SPRING_DATASOURCE_USERNAME=postgres
      - SPRING_DATASOURCE_PASSWORD=B123456789
      - SPRING_JPA_HIBERNATE_DDL_AUTO=update

  db:
    image: 'postgres:alpine'
    container_name: db
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=B123456789

dockerfile

FROM adoptopenjdk:11-jre-hotspot
ARG JAR_FILE=*.jar
COPY ${JAR_FILE} application.jar
ENTRYPOINT ["java", "-jar", "application.jar"]

application.props

spring.datasource.url=jdbc:postgresql://db:5432/postgreTest?serverTimezone=UTC
spring.datasource.username=postgres
spring.datasource.password=B123456789
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

but i keep getting the same error

2021-07-16 15:25:31.986 WARN 1 --- [ main] o.h.e.j.e.i.JdbcEnvironmentInitiator : HHH000342: Could not obtain connection to query metadata app | app | org.postgresql.util.PSQLException: Connection to db:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Answer 1

What a nice question! At first glance at your config, I can't see anything that stands out as outright wrong. I believe it's possible that you are trying to connect to your db before postgres is fully started.

Waiting for your postgres process to start (rather than simply your container)

Provided that's the problem, you are most likely advised to not use a raw depends_on (which will just wait till the container itself is started, but rather a depends_on with a condition and a healthcheck.

With that approach you make sure you check whether postgres is really started, not just the container, but the database itself. I.e you can do something like:

// ...
services:
  postgres:
    // ...
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 1s    

  app:
    //... do your springboot app stuff here
    depends_on: 
      postgres:
        condition: service_healthy

EDIT: Making sure the database underneath actually exists

You mentioned in your comment that now you get another error saying it can't find your database. That's most likely because while the postgres process itself is started, the database your jdbc connection string refers to is not created.

By default, postgres databases you get out of the box are:

• template0
• template1
• postgres

See also this link for more information.

Since your jdbc string connects/expects to connect to another database: jdbc:postgresql://db:5432/postgreTest, you most likely need to create that database beforehand.

How to create the postgreTest database beforehand?

Well, there are multiple ways:

• more spring-y/java-y approach: using a raw jdbc connection in your spring context and doing create database statements before the rest of your context starts up
• more docker-y: Make sure your container has the database you need upon startup. This is the one I'd describe how to do below.

The postgres-docker container supports multiple properties, in addition to:

POSTGRES_USER=xyz
POSTGRES_PASSWORD=xxxxx

you can also override the default database name created (which is postgres, if your user is also postgres) by utilizing the POSTGRES_DB property. The documentation reads:

This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used

Basically, you'd do something like:

  db:
    image: 'postgres:alpine'
    container_name: db
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 1s 
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=B123456789
      - POSTGRES_DB=postgreTest

Which would end up creating your database, waiting for it to fully start (due to the healthcheck), and then also create the postgresTest database, and only then be considered healthy. Then your spring app would have its all dependencies and would be ready to start.

Docker-compose spec version

Note: btw, I notice you use version 2 of the compose specification, I'd switch to latest 3, as 2 (and 1) are very clunky and often don't allow for some possibilities which are fairly easy to achieve in the new spec version.