Docker wait for postgresql to be running

Question

I am using postgresql with django in my project. I've got them in different containers and the problem is that i need to wait for postgres before running django. At this time i am doing it with sleep 5 in command.sh file for django container. I also found that netcat can do the trick but I would prefer way without additional packages. curl and wget can't do this because they do not support postgres protocol. Is there a way to do it?

Answer 1

for me i prefer using some simple while loops

while True:
    try:
        engine = create_engine(DATABASE_URL)
        break
    except Exception:
        print("cannot connect to database retry after 5 seconds..")
        time.sleep(5)

Answer 2

For Java devs. I've packed DatabaseStartupValidator into DataSource wrapper. Works like a charm:

public class WaitingDataSource implements DataSource, InitializingBean {

private final DataSource dataSource;
private final DatabaseStartupValidator databaseStartupValidator;

public WaitingDataSource(DataSource dataSource) {
    this.dataSource = Objects.requireNonNull(dataSource);
    this.databaseStartupValidator = new DatabaseStartupValidator();
    databaseStartupValidator.setDataSource(dataSource);
}

public WaitingDataSource(DataSource dataSource, int intervalSeconds, int timeoutSeconds) {
    this(dataSource);
    databaseStartupValidator.setInterval(intervalSeconds);
    databaseStartupValidator.setTimeout(timeoutSeconds);
}

@Override
public Connection getConnection() throws SQLException {
    return dataSource.getConnection();
}

@Override
public Connection getConnection(String username, String password) throws SQLException {
    return dataSource.getConnection(username, password);
}

@Override
public PrintWriter getLogWriter() throws SQLException {
    return dataSource.getLogWriter();
}

@Override
public void setLogWriter(PrintWriter printWriter) throws SQLException {
    dataSource.setLogWriter(printWriter);
}

@Override
public void setLoginTimeout(int seconds) throws SQLException {
    dataSource.setLoginTimeout(seconds);
}

@Override
public int getLoginTimeout() throws SQLException {
    return dataSource.getLoginTimeout();
}

@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
    return dataSource.getParentLogger();
}

@Override
@SuppressWarnings("unchecked")
public <T> T unwrap(Class<T> clazz) throws SQLException {
    return clazz.isInstance(this) ? (T) this : dataSource.unwrap(clazz);
}

@Override
public boolean isWrapperFor(Class<?> clazz) throws SQLException {
    return clazz.isInstance(this) || dataSource.isWrapperFor(clazz);
}

@Override
public void afterPropertiesSet() {
    this.databaseStartupValidator.afterPropertiesSet();
}

}

Answer 3

According to https://docs.docker.com/compose/compose-file/compose-file-v3/, the latest compose specification is available at: https://github.com/compose-spec/compose-spec/blob/master/spec.md and the part about the depends_on key is under: https://github.com/compose-spec/compose-spec/blob/master/spec.md#depends_on

It tells us everything we need about the condition:

depends_on

Short syntax

(...)

Long syntax

The long form syntax enables the configuration of additional fields that can't be expressed in the short form.

• restart: When set to true Compose restarts this service after it updates the dependency service. This applies to an explicit restart controlled by a Compose operation, and excludes automated restart by the container runtime after the container dies.

• condition: Sets the condition under which dependency is considered satisfied

  • service_started: An equivalent of the short syntax described above

  • service_healthy: Specifies that a dependency is expected to be "healthy" (as indicated by healthcheck) before starting a dependent service.

  • service_completed_successfully: Specifies that a dependency is expected to run to successful completion before starting a dependent service.

  • required: When set to false Compose only warns you when the dependency service isn't started or available. If it's not defined the default value of required is true.

Answer 4

@Vinicius Chan answer misses one important thing. initdb process temporary runs server which listens on socket. Because of this Sleeping until pg_isready returns true unfortunately is not always reliable. Read more on Initialization scripts.

the temporary daemon started for these initialization scripts listens only on the Unix socket

You could use this fact that temporary server is not available via TCP/IP and we could wait until that:

pg_isready -h localhost

You can use your target IP instead of localhost if you used listen_addresses option to start your server.

Related: https://github.com/docker-library/postgres/issues/474#issuecomment-416914741

Answer 5

I was trying await until a Postgres database within a container is ready, using java only. This is how I did it:

I'm representing a container that has a Postgres database using the following record:

public record DBContainer(String containerId, String driverClassName, String url, String username, String password) {}

Then, this method awaits for the container to be ready:

private static void waitForPostgresContainerToBeReady(DBContainer dbContainer) throws InterruptedException {
    while (!containerIsReady(dbContainer)) {
        System.err.println(String.format("Container %s is not ready", dbContainer.containerId()));
        Thread.sleep(Duration.ofMillis(300));
    }
    System.out.println(String.format("Container %s is ready", dbContainer.containerId()));
}

Additional helper methods:

// Check if the postgres database whithin the container is ready by trying to open a connection to it.
private static boolean containerIsReady(DBContainer dbContainer) {
    try {
        DataSource dataSource = getDataSource(dbContainer);
        Connection connection = dataSource.getConnection();
        boolean isOpen = !connection.isClosed();
        if (isOpen) {
            connection.close();
        }
        return isOpen;
    } catch (SQLException e) {
        return false;
    }
}

// Get a datasource from a DBContainer
public static DataSource getDataSource(DBContainer container) {
    DataSource dataSource = DataSourceBuilder.create()
            .driverClassName(container.driverClassName())
            .url(container.url())
            .username(container.username())
            .password(container.password())
            .build();
    return dataSource;
}

Answer 6

I've spent some hours investigating this problem and I got a solution. Docker depends_on just consider service startup to run another service. Than it happens because as soon as db is started, service-app tries to connect to ur db, but it's not ready to receive connections. So you can check db health status in app service to wait for connection. Here is my solution, it solved my problem. :) Important: I'm using docker-compose version 2.1.

version: '2.1'

services:
  my-app:
    build: .
    command: su -c "python manage.py runserver 0.0.0.0:8000"
    ports:
       - "8000:8000"
    depends_on:
      db:
        condition: service_healthy
    links:
      - db
    volumes:
      - .:/app_directory

  db:
    image: postgres:10.5
    ports:
      - "5432:5432"
    volumes:
      - database:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  database:

In this case it's not necessary to create a .sh file.

Answer 7

Trying with a lot of methods, Dockerfile, docker compose yaml, bash script. Only last of method help me: with makefile.

docker-compose up --build -d postgres
sleep 2
docker-compose up --build -d app

Answer 8

None of other solution worked, except for the following:

version : '3.8'
services :
  postgres :
    image : postgres:latest
    environment :
      - POSTGRES_DB=mydbname
      - POSTGRES_USER=myusername
      - POSTGRES_PASSWORD=mypassword
    healthcheck :
      test: [ "CMD", "pg_isready", "-q", "-d", "mydbname", "-U", "myusername" ]
      interval : 5s
      timeout : 5s
      retries : 5
  otherservice:
    image: otherserviceimage
    depends_on :
      postgres:
        condition: service_healthy

Thanks to this thread: https://github.com/peter-evans/docker-compose-healthcheck/issues/16

Answer 9

If you want to run it with a single line command. You can just connect to the container and check if postgres is running

docker exec -it $DB_NAME bash -c "\
until psql -h $HOST -U $USER -d $DB_NAME-c 'select 1'>/dev/null 2>&1;\
do\
  echo 'Waiting for postgres server....';\
  sleep 1;\
done;\
exit;\
"
echo "DB Connected !!"

Answer 10

There are couple of solutions as other answers mentioned.

But don't make it complicated, just let it fail-fast combined with restart: on-failure. Your service will open connection to the db and may fail at the first time. Just let it fail. Docker will restart your service until it green. Keep your service simple and business-focused.

version: '3.7'

services:

  postgresdb:
    hostname: postgresdb
    image: postgres:12.2
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=secret
      - POSTGRES_DB=Ceo

  migrate:
    image: hanh/migration
    links:
      - postgresdb
    environment:
      - DATA_SOURCE=postgres://user:secret@postgresdb:5432/Ceo
    command: migrate sql --yes
    restart: on-failure # will restart until it's success

Check out restart policies.

Answer 11

You can use the manage.py command "check" to check if the database is available (and wait 2 seconds if not, and check again). For instance, if you do this in your command.sh file before running the migration, Django has a valid DB connection while running the migration command:

...
echo "Waiting for db.."
python manage.py check --database default > /dev/null 2> /dev/null
until [ $? -eq 0 ];
do
  sleep 2
  python manage.py check --database default > /dev/null 2> /dev/null
done
echo "Connected."
# Migrate the last database changes
python manage.py migrate
...

PS: I'm not a shell expert, please suggest improvements.

Answer 12

An example for Nodejs and Postgres api.

#!/bin/bash
#entrypoint.dev.sh
echo "Waiting for postgres to get up and running..."
while ! nc -z postgres_container 5432; do
  # where the postgres_container is the hos, in my case, it is a Docker container.
  # You can use localhost for example in case your database is running locally.
  echo "waiting for postgress listening..."
  sleep 0.1
done
echo "PostgreSQL started"

yarn db:migrate

yarn dev

# Dockerfile
FROM node:12.16.2-alpine

ENV NODE_ENV="development"

RUN mkdir -p /app

WORKDIR /app

COPY ./package.json ./yarn.lock ./

RUN yarn install

COPY . .

CMD ["/bin/sh", "./entrypoint.dev.sh"]

Answer 13

Sleeping until pg_isready returns true unfortunately is not always reliable. If your postgres container has at least one initdb script specified, postgres restarts after it is started during it's bootstrap procedure, and so it might not be ready yet even though pg_isready already returned true.

What you can do instead, is to wait until docker logs for that instance return a PostgreSQL init process complete; ready for start up. string, and only then proceed with the pg_isready check.

Example:

start_postgres() {
  docker-compose up -d --no-recreate postgres
}

wait_for_postgres() {
  until docker-compose logs | grep -q "PostgreSQL init process complete; ready for start up." \
    && docker-compose exec -T postgres sh -c "PGPASSWORD=\$POSTGRES_PASSWORD PGUSER=\$POSTGRES_USER pg_isready --dbname=\$POSTGRES_DB" > /dev/null 2>&1; do
    printf "\rWaiting for postgres container to be available ... "
    sleep 1
  done
  printf "\rWaiting for postgres container to be available ... done\n"
}

start_postgres
wait_for_postgres

Answer 14

I have managed to solve my issue by adding health check to docker-compose definition.

  db:
    image: postgres:latest
    ports:
      - 5432:5432
    healthcheck:
      test: "pg_isready --username=postgres && psql --username=postgres --list"
      timeout: 10s
      retries: 20

then in the dependent service you can check the health status:

  my-service:
    image: myApp:latest
    depends_on:
      kafka:
        condition: service_started
      db:
        condition: service_healthy

source: https://docs.docker.com/compose/compose-file/compose-file-v2/#healthcheck

Answer 15

Inspired by @tiziano answer and the lack of nc or pg_isready, it seems that in a recent docker python image (python:3.9 here) that curl is installed by default and I have the following check running in my entrypoint.sh:

postgres_ready() {
    $(which curl) http://$DBHOST:$DBPORT/ 2>&1 | grep '52'
}

until postgres_ready; do
  >&2 echo 'Waiting for PostgreSQL to become available...'
  sleep 1
done
>&2 echo 'PostgreSQL is available.'

Answer 16

In your Dockerfile add wait and change your start command to use it:

ADD https://github.com/ufoscout/docker-compose-wait/releases/download/2.7.3/wait /wait
RUN chmod +x /wait

CMD /wait && npm start

Then, in your docker-compose.yml add a WAIT_HOSTS environment variable for your api service:

services:
  api: 
    depends_on:
      - postgres
    environment:
      - WAIT_HOSTS: postgres:5432

  postgres:
    image: postgres
    ports:
      - "5432:5432"

This has the advantage that it supports waiting for multiple services:

environment:
  - WAIT_HOSTS: postgres:5432, mysql:3306, mongo:27017

For more details, please read their documentation.

Answer 17

#!/bin/sh

POSTGRES_VERSION=9.6.11
CONTAINER_NAME=my-postgres-container

# start the postgres container
docker run --rm \
  --name $CONTAINER_NAME \
  -e POSTGRES_PASSWORD=docker \
  -d \
  -p 5432:5432 \
  postgres:$POSTGRES_VERSION

# wait until postgres is ready to accept connections
until docker run \
  --rm \
  --link $CONTAINER_NAME:pg \
  postgres:$POSTGRES_VERSION pg_isready \
    -U postgres \
    -h pg; do sleep 1; done

Answer 18

If the backend application itself has a PostgreSQL client, you can use the pg_isready command in an until loop. For example, suppose we have the following project directory structure,

.
├── backend
│   └── Dockerfile
└── docker-compose.yml

with a docker-compose.yml

version: "3"
services:
  postgres:
    image: postgres
  backend:
    build: ./backend

and a backend/Dockerfile

FROM alpine
RUN apk update && apk add postgresql-client
CMD until pg_isready --username=postgres --host=postgres; do sleep 1; done \
    && psql --username=postgres --host=postgres --list

where the 'actual' command is just a psql --list for illustration. Then running docker-compose build and docker-compose up will give you the following output:

Note how the result of the psql --list command only appears after pg_isready logs postgres:5432 - accepting connections as desired.

By contrast, I have found that the nc -z approach does not work consistently. For example, if I replace the backend/Dockerfile with

FROM alpine
RUN apk update && apk add postgresql-client
CMD until nc -z postgres 5432; do echo "Waiting for Postgres..." && sleep 1; done \
    && psql --username=postgres --host=postgres --list

then docker-compose build followed by docker-compose up gives me the following result:

That is, the psql command throws a FATAL error that the database system is starting up.

In short, using an until pg_isready loop (as also recommended here) is the preferable approach IMO.

Answer 19

If you have psql you could simply add the following code to your .sh file:

RETRIES=5

until psql -h $PG_HOST -U $PG_USER -d $PG_DATABASE -c "select 1" > /dev/null 2>&1 || [ $RETRIES -eq 0 ]; do
  echo "Waiting for postgres server, $((RETRIES--)) remaining attempts..."
  sleep 1
done

Answer 20

wait-for-it small wrapper scripts which you can include in your application’s image to poll a given host and port until it’s accepting TCP connections.

can be cloned in Dockerfile by below command

RUN git clone https://github.com/vishnubob/wait-for-it.git

docker-compose.yml

version: "2"
services:
   web:
     build: .
     ports:
       - "80:8000"
     depends_on:
       - "db"
     command: ["./wait-for-it/wait-for-it.sh", "db:5432", "--", "npm",  "start"]
   db:
     image: postgres

Answer 21

The simplest solution is a short bash script:

while ! nc -z HOST PORT; do sleep 1; done;
./run-smth-else;

Answer 22

This will successfully wait for Postgres to start. (Specifically line 6). Just replace npm start with whatever command you'd like to happen after Postgres has started.

services:
  practice_docker: 
    image: dockerhubusername/practice_docker
    ports: 
      - 80:3000
    command: bash -c 'while !</dev/tcp/db/5432; do sleep 1; done; npm start'
    depends_on:
      - db
    environment:
      - DATABASE_URL=postgres://postgres:password@db:5432/practicedocker
      - PORT=3000   
  db:
    image: postgres
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=practicedocker

Answer 23

Problem with your solution tiziano is that curl is not installed by default and i wanted to avoid installing additional stuff. Anyway i did what bereal said. Here is the script if anyone would need it.

import socket
import time
import os

port = int(os.environ["DB_PORT"]) # 5432

s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
while True:
    try:
        s.connect(('myproject-db', port))
        s.close()
        break
    except socket.error as ex:
        time.sleep(0.1)

Answer 24

Why not curl?

Something like this:

while ! curl http://$POSTGRES_PORT_5432_TCP_ADDR:$POSTGRES_PORT_5432_TCP_PORT/ 2>&1 | grep '52'
do
  sleep 1
done

It works for me.