Make it easy to wait for connection

[abitrolly]

https://hub.docker.com/_/postgres contains at least two instructions that tell to wait for DB to initialize successfully. These are hard to automate, because there are no instructions how to wait for it programmatically.

It would be nice is the instructions could at least confirm some of the best way to do this in docker-compose
https://stackoverflow.com/questions/35069027/docker-wait-for-postgresql-to-be-running

[wglambert]

You can grep for the line "PostgreSQL init process complete; ready for start up."

$ docker logs postgres
. . .
PostgreSQL init process complete; ready for start up.

2021-09-10 16:28:37.737 UTC [1] LOG:  starting PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-09-10 16:28:37.739 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-09-10 16:28:37.740 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2021-09-10 16:28:37.743 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-09-10 16:28:37.750 UTC [66] LOG:  database system was shut down at 2021-09-10 16:28:37 UTC
2021-09-10 16:28:37.758 UTC [1] LOG:  database system is ready to accept connections
[node2] (local) root@192.168.0.18 ~

$ docker logs postgres 2>&1 | grep "init process complete"
PostgreSQL init process complete; ready for start up.

[tianon]

Indeed, the "right" solution is going to depend entirely on your target environment and available tools -- there are a lot of solutions to this problem (and even varying definitions of "ready"), so we cannot reasonably provide a standard bit of guidance. For some, the port listening is sufficient (which is easy to test). For some, looping on something that tries to connect with a SELECT 1-style no-op query is better. For some, they want to attempt a full connection through their ORM stack and even perform database seeding before they claim the database as "ready" for use.

[abitrolly]

@tianon any working solution that will replace the text "wait for DB" will do. Otherwise the instructions are not helpful.

[wglambert]

See https://github.com/docker-library/healthcheck and https://github.com/docker-library/faq#healthcheck

[abitrolly]

@wglambert I've seen the links, and they don't solve the usability problem of choice. Just a single scenario that works without human supervision would suffice for the hub.

[abitrolly]

https://github.com/docker-library/faq#healthcheck

The FAQ says this.

many users will have their own idea of what "healthy" means and credentials change over time making generic health checks hard to define

I doubt that these many users have as much unique ideas, Instead FAQ could just list all the common sense cases with links to corresponding examples in https://github.com/docker-library/healthcheck.

1. pg_iready
2. curl
3. SELECT

Even if there are more, I doubt there are more than 10 cases to fit in the list.

[abitrolly]

It took a week to come up with this recipe.

• add a healthcheck section to docker-compose.yml

    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      interval: 5s
      timeout: 5s
      retries: 10

• Add a ./bin/wait-for-db.sh script that is called from GitHub Actions

#!/bin/bash

# healthy
# unhealthy
# starting

CONTAINER=warehouse_db_1
check() {
    docker inspect --format "{{.State.Healthcheck.Status }}" $CONTAINER
}

while [[ "$STATUS" != "healthy" ]]
do
    STATUS=$(check)
    echo "$STATUS"
    sleep 3
done

[abitrolly]

Actually had to add a timeout to avoid an endless loop in GitHub Actions. It is still not configurable, so not a completely easy way.

#!/bin/bash

# healthy
# unhealthy
# starting

CONTAINER=warehouse_db_1
check() {
    docker inspect --format "{{.State.Healthcheck.Status }}" $CONTAINER
}

while [[ "$STATUS" != "healthy" ]]
do
    STATUS=$(check)
    echo "$STATUS ${SECONDS}"
    (( SECONDS > 60 )) && exit 1
    sleep 3
done

[daveisfera]

I doubt that these many users have as much unique ideas, Instead FAQ could just list all the common sense cases with links to corresponding examples in https://github.com/docker-library/healthcheck.

1. pg_iready
2. curl
3. SELECT

For others that stumble on this, pg_isready works to say the database is ready to accept connections, but the roles and databases aren't fully ready yet, so running a command right after can cause problems. We've switch to the SELECT based method because of that

[abitrolly]

@daveisfera if you use initialization scripts then connection doesn't up until the init is over, like in https://github.com/pypa/warehouse/pull/9993/files

[daveisfera]

I'm not following. What part of that link is the "initialization scripts"?

[abitrolly]

@daveisfera this section is the initialization SQL script that is processed before the connection is up.

   volumes:
      - ./dev/example.sql.xz:/docker-entrypoint-initdb.d/example.sql.xz:ro

[daveisfera]

I don't believe that that will solve the problem. The issue that we saw is that occasionally pg_isready would exit with a done/completed status code, but then the command that was run would say that a database and/or user wasn't available. It didn't happen often, but would happen occasionally when running tests.