Postgres: How to determine when new container is truly "ready"

[rarkins]

To wait for a postgres container to be "ready", I've been using a script like this:

# Wait for PG to be ready
until $PSQL -c "select version()" &> /dev/null
do
    echo "waiting for postgres container..."
    sleep 2
done

But it seems even this is too early. Is there any unequivocal way that another container can tell once the postgres container is ready to be provisioned, without resorting to more arbitrary wait timers?

[spmacdonald]

I have done this:

while ! pg_isready 
do
    echo "$(date) - waiting for database to start"
    sleep 10
done

and it seems to work, but I am still learning...

[rarkins]

Hi @spmacdonald,
Your sleep 10 is still what I'd call an "arbitrary wait timer". The challenge (problem?) with the current postgres image is that it is essentially "ready" twice - so the first time it reports ready it's not really ready:

Here's the first time:

postgres          | LOG:  database system is ready to accept connections
postgres          | LOG:  autovacuum launcher started
postgres          |  done
postgres          | server started
postgres          | ALTER ROLE

Then it runs its entrypoints, e.g. here is an example if adding postgis:

postgres          | /docker-entrypoint.sh: running /docker-entrypoint-initdb.d/postgis.sh
postgres          | CREATE DATABASE
postgres          | UPDATE 1
postgres          | Loading PostGIS extensions into template_postgis
postgres          | CREATE EXTENSION
postgres          | CREATE EXTENSION
postgres          | CREATE EXTENSION
postgres          | CREATE EXTENSION
postgres          | Loading PostGIS extensions into postgres
postgres          | CREATE EXTENSION
postgres          | CREATE EXTENSION
postgres          | CREATE EXTENSION
postgres          | CREATE EXTENSION

And then the restart:

postgres          | LOG:  received fast shutdown request
postgres          | LOG:  aborting any active transactions
postgres          | LOG:  autovacuum launcher shutting down
postgres          | waiting for server to shut down....LOG:  shutting down
postgres          | LOG:  database system is shut down
postgres          |  done
postgres          | server stopped
postgres          |
postgres          | PostgreSQL init process complete; ready for start up.
postgres          |
postgres          | LOG:  database system was shut down at 2016-04-29 03:31:41 UTC
postgres          | LOG:  MultiXact member wraparound protections are now enabled
postgres          | LOG:  database system is ready to accept connections
postgres          | LOG:  autovacuum launcher started

And only _now_ is it truly ready. If you kicked off a seeding/provisioning script once you saw PG "ready" the first time, you're likely to hit a failure when it restarts. I think all containers need to have a deterministic way to indicate when they're actually-truly "won't restart on you" ready without resorting to sleep timers.

[md5]

@rarkins What is the $PSQL in your command? Is it connecting to a separate container? If so, then I'm surprised that it's "too early". The initialization process you're talking about in this container should not be exposing ports in a way that anything outside the container can connect.

[rarkins]

@md5 sorry, $PSQL is just an alias defined earlier in my file:

PSQL="psql -h localhost -p 5432 -U postgres -v ON_ERROR_STOP=1"

I am using host networking in docker, which might explain the difference?

[md5]

@rarkins You're going to have a hard time with a lot of assumptions in various Docker images if you're using host networking.

[yosifkit]

The way the container is designed is that when it is listening on the network port via its non-loopback interface, it is ready to accept connections. Accepting connections on the loopback connection is for the scripts in /docker-entrypoint-initdb.d/; once the scripts are done, the server is killed and then started as pid 1 of the container.

[gravis]

The best way to wait for your container to be up and ready is this:

until docker run -it --rm --link [YOUR-PG-CONTAINER-NAME]:pg postgres:9.5 psql -U postgres -h pg -c "select 1" -d postgres; do sleep 1; done

That's what we're using at Gemnasium, it avoid the "double start" issue.

[sydcanem]

We do sleep 2 after done to accomodate the next restart.

until pg_isready
do
    echo "."
    sleep 1
done
sleep 2

[yelizariev]

improved version of @gravis's solution

 until docker run --rm --link [YOUR-PG-CONTAINER-NAME]:pg postgres:9.5 pg_isready -U postgres -h pg; do sleep 1; done

it works even if password is required

Update as noted by @enumag if you use network, then you need to add --net too:

until docker run --rm --link [CONTAINER]:pg --net [NETWORK] postgres:9.5 pg_isready -U postgres -h pg; do sleep 1; done

[jasonkeene]

@gravis @yelizariev Thank you for your solutions!

[fdietze]

Is the -w option for pg_ctl helpful here?
https://www.postgresql.org/docs/current/static/app-pg-ctl.html

-w
Wait for the startup or shutdown to complete. Waiting is the default option for shutdowns, but not startups. When waiting for startup, pg_ctl repeatedly attempts to connect to the server. When waiting for shutdown, pg_ctl waits for the server to remove its PID file. This option allows the entry of an SSL passphrase on startup. pg_ctl returns an exit code based on the success of the startup or shutdown.

[valk91]

As @rarkins I've struggled with the server restart too. pg_isready is not helpful if you apply a schema, seed a data right away (e.g CI workflow).
I got inspired by https://github.com/vishnubob/wait-for-it
which Docker mentions in its docs here https://docs.docker.com/compose/startup-order/

HOST=db
PORT=5432
RETRIES=10

until docker-compose exec container bash -c '(echo > /dev/tcp/$HOST/$PORT) > /dev/null 2>&1' || [ $RETRIES -eq 0 ]; do
    echo "Waiting for Postgres server, $((RETRIES--)) remaining attempts..."
        sleep 1
    done

[cjlint]

The simplest solution for me was watching the logs for PostgreSQL init process complete; ready for start up, and _then_ watching the logs for database system is ready to accept connections

[vitorreis]

thanks for the solution @gravis

[enumag]

I tried to use the solution posted by @gravis and later improved by @yelizariev. In my case there was one more problem:

docker: Error response from daemon: Cannot link to /[CONTAINER], as it does not belong to the default network.

I found a fix here. I just had to add --net [NETWORK] to the command, where network name can be found using docker network ls.

Here is the final version:

until docker run --rm --link [CONTAINER]:pg --net [NETWORK] postgres:9.5 pg_isready -U postgres -h pg; do sleep 1; done

[Mobe91]

This should be solved if #282 is implemented, right?

[enumag]

The problem is that you can't use healthcheck when using docker-compose.yml version 3 since depends_on no longer supports condition.

https://docs.docker.com/compose/compose-file/#depends_on

[mausch]

As explained in this comment, solutions that merely check for connectivity (using psql or pg_isready or anything else) will not work in the general case with this image since it does a postgres restart after the initial setup (if you are mounting an existing database you're fine though).
You need a solution like the one outlined by cjlint.
Here's a bash snippet that does that:

for i in {1..7}; do
    docker logs <YOUR_CONTAINER> 2>&1  | grep -Pzl '(?s)init process complete.*\n.*ready to accept connections'
    if [ $? -eq 0 ]; then
        break
    fi
    if [ $i -eq 7 ]; then
        echo "Postgres did not start up successfully"
        exit 1
    fi
    sleep 2
done

[enumag]

@mausch Unfortunately your solution is can't be used it from inside of another container - which is what you want most of the time. Any tips for that situation?

[MaerF0x0]

For future google hits:

I'm using /usr/local/bin/dockerize -wait tcp://localhost:5432 -timeout 1m in a CI job .

[sizief]

If using docker version 2.1

healthcheck:                                                                 
  test: ["CMD-SHELL", "psql -h localhost -p 5432 -U postgres -v ON_ERROR_STOP=1 -c 'select version()' &> /dev/null"]
   test: ["CMD-SHELL", "pg_isready -U postgres"] #Are you really up?    
    interval: 2s                                                            
    timeout: 30s                                                            
    retries: 15    

and then

depends_on:                                                                 
  db:                                                                       
     condition: service_healthy   

[dliff]

@enumag
Been struggling with this as I cannot access the logs for my db container from web which @mausch 's solution seems to require (unless there is a way to, I'm actually new to Docker).

The other solutions aren't effective because my docker image, the kartoza/postgis with Postgis, seems to actually fully start Postgres once, then restart. So it will pass health check one time, then restart.

For those of us whose Postgres image needs to start x amount of times, I have this shell script that uses a pg function to get the pid and keep track of how many distinct times Postgres has started.

Have not tested this on platform besides Linux, or with a goal other than 2.

```# set goal to how many times you want postgres to start before continuing
count=0
goal=2

lastpid=""
pid=""

function getpid()
{
lastpid="$pid"
pid=$(PGPASSWORD=$POSTGRES_PASSWORD psql "$DB_NAME" -At -h "$HOST" -U "$POSTGRES_USER" -c 'SELECT pg_backend_pid()')
}

until getpid && [ "$count" -eq "$goal" ]; do

&2 echo "Postgres has started $count out of $goal time(s)."
if [ "$pid" ] && [ "$pid" != "$lastpid" ];
then
count=$((count+1))
fi
sleep 1
done

&2 echo "Postgres has started $goal time(s)... executing command"
```

[ezpuzz]

Use this:
( docker-compose logs -f postgres & ) | grep -m2 'ready to accept'
or
( docker logs -f postgres & ) | grep -m2 'ready to accept'

[tianon]

As noted back in https://github.com/docker-library/postgres/issues/146#issuecomment-215856076, the best way to test if the container is "ready" is to connect to it using its external IP address (it does not listen externally until the initialization process is fully complete).

In the future, these sorts of questions/requests would be more appropriately posted to the Docker Community Forums, the Docker Community Slack, or Stack Overflow.

[jackodsteel]

Sorry to necro a closed thread, but I think I've found the best solution.
Based on @yosifkit's advice about the initial server only responding to loopback requests, a simple change can be made to the pg_isready or psql call to make it only return true for the restarted version.

Instead of -h localhost like is common, if you make -h the external IP, it'll only succeed for the restarted version, even when running locally.

If using docker-compose v2 healthchecks this is ideal, and probably ideal for other use cases too.

Here's the relevant parts of my final compose file:

services:
  server:
    depends_on:
      db:
        condition: service_healthy
  db:
    image: postgres:latest
    healthcheck:
      test: "pg_isready -q -h db"
      interval: 3s
      timeout: 5s
      retries: 5

Note that -h db makes it refer to its own docker network IP which works perfectly for this purpose, however if using a script or other means you can just sub it out for an environment variable or anything that's not localhost/loopback.

[Vespira]

Is it possible to now do the healthcheck properly, since compose v3 doesn't support the depends_on conditions anymore ?

source link : https://docs.docker.com/compose/compose-file/ at the "depends_on" section.

EDIT : My bad, according to https://docs.docker.com/compose/startup-order/, Docker tends to prevent to manage this kind of healthchecks. apparently, the task of waiting or retrying connections is given to orchestrator tools like Kubernetes or Swarm and the applications itself.

[gajus]

Can someone provide a Dockerfile showing an end to end solution of how one is supposed to check if Postgres service is ready?

I am currently using a script such as:

#!/bin/bash

set -e

until pg_isready; do
  sleep 0.1
done

# @see https://github.com/cobrainer/pg-docker-with-restored-db#further-notes-about-the-wait-for-pg-isreadysh
until ! pg_isready; do
  sleep 0.1
done

until pg_isready; do
  sleep 1
done

>&2 echo "Postgres is up - you can execute commands now"

but it is pretty hacky.

[gajus]

It seems that:

until pg_isready -h $(hostname -i); do
  sleep 0.1
done

does the trick.