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.

[abitrolly]

Do tests run initialization scripts? If yes, then it should be a bug with pg_isready, because by the time init script finishes, there should be no time gap between opened port and DB/user finishing initialization.

[tianon]

If you run "pg_isready" inside the same container, it will likely connect to the unix socket (which is enabled even during initialization). You have to explicitly connect it to the TCP port instead (or use a separate container that can't access the unix socket at all).

[abitrolly]

@tianon pg_isready sends some handshake packet and waits for response. If a server sends the response when it is not ready, it must be a bug in server then,

[yosifkit]

On first start, while creating the initial database and running /docker-entrypoint-initdb.d/ scripts a PostgreSQL server is "ready":

postgres/docker-entrypoint.sh

Lines 326 to 331 in a83005b

	docker_temp_server_start "$@"
	docker_setup_db
	docker_process_init_files /docker-entrypoint-initdb.d/*
	docker_temp_server_stop

The temporary server does not listen on external ports, but would be pg_isready if accessed from within the container. Once initialization is complete, then that server is stopped and the entrypoint script execs to postgres server so that postgres is pid 1.

[abitrolly]

@yosifkit why temporary server won't use a different port for initialization?

[tianon]

If you do pg_isready --host 127.0.0.1 it should do the "right" thing and try connecting to the TCP port instead of the unix socket.

[daveisfera]

I’ve added -h localhost and that makes sense as a fix, so I’ll keep an eye on our tests to verify it works as expected