pg_restore "terminating connection due to administrator command" right…

A script waits for a fresh Postgres container with pg_isready and then restores or migrates into it. It fails now and then. The errors look like the server crashed, but it did not:

pg_restore: error: could not execute query: FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
Command was: ALTER TABLE ONLY public.restored
    ADD CONSTRAINT restored_pkey PRIMARY KEY (id);

or, for a client that connects a moment later:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  the database system is shutting down
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  the database system is starting up
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

In the incident behind this unit, a nightly restore test (docker compose up -d db on an empty volume, a pg_isready loop inside the container, then pg_restore) met "the database system is starting up", and about 27 later test suites failed after it.

Cause

On an empty data directory the official postgres image's docker-entrypoint.sh starts a temporary server to run /docker-entrypoint-initdb.d/*. It starts it with listen_addresses='', so the server listens on the unix socket only. When the init scripts finish, it stops that server with pg_ctl -m fast and starts the real one with TCP.

pg_isready with no -h checks the unix socket. It answers "accepting connections" from the temporary server, before the init scripts have run. A client that starts work then is either killed by the fast shutdown ("terminating connection due to administrator command") or connects in the gap between the two servers ("shutting down", "starting up", "No such file or directory"). Whether a given run fails depends on whether its work spans that restart, so the failure is intermittent.

The log of one failing run shows the sequence. These are docker timestamps, trimmed:

04:29:21.002 LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"     <- temporary server, socket only
04:29:21.017 LOG: database system is ready to accept connections                    <- pg_isready (socket) says ready
04:29:24.118 LOG: received fast shutdown request                                    <- init scripts done
04:29:24.121 FATAL: terminating connection due to administrator command             <- the restore's connection
04:29:24.905 LOG: database system is shut down
04:29:24.921 PostgreSQL init process complete; ready for start up.
04:29:24.976 LOG: listening on IPv4 address "0.0.0.0", port 5432                   <- the real server
04:29:25.011 LOG: database system is ready to accept connections

Fix

Wait over TCP. The temporary server has no TCP listener, so the check answers only once the real server is up:

# before: ready on the temporary init server
until docker compose exec -T db pg_isready -q -U "$POSTGRES_USER"; do sleep 1; done
# after: ready only on the real server
until docker compose exec -T db sh -c 'pg_isready -q -h 127.0.0.1 -U "$POSTGRES_USER"'; do sleep 1; done

The same applies to a compose healthcheck: use pg_isready -h 127.0.0.1 ..., not plain pg_isready. Also check pg_restore's exit code. In every failing run below the rows were there and only the primary key was missing, so a script that ignores the exit code silently gets a table with no primary key.

Verification (reproduced before, gone after)

Environment:

• GitHub-hosted runner, image ubuntu24 20261004.327.1 (Ubuntu 24.04.5 LTS, kernel 6.17.0-1022-azure). It is a shared Azure VM with 2 vCPU (AMD EPYC 9V45) and 7.75 GiB RAM.
• Docker 28.0.4.
• pgvector/pgvector:pg16 (sha256:7b822b0aac60967beb1ea5e576b8602c94c300a157d187f385ae3e0da199b90a), PostgreSQL 16.15, built on the official postgres entrypoint.

Setup:

• Each trial is a new container from that image on an empty data directory, with POSTGRES_PASSWORD=x.
• The init script is one SQL file in /docker-entrypoint-initdb.d that creates 400 tables, each with an index. It stands in for a migrations directory.
• The dumps were made with pg_dump -Fc -t restored: a table of 300,000 rows (6.6 MB) and one of 3,000,000 rows (66 MB), each with a primary key.
• The wait loop runs docker exec <c> pg_isready -q [-h 127.0.0.1] -U postgres every 100 ms.
• Then docker exec <c> pg_restore -U postgres -d postgres /tmp/d.dump runs. After the real server is up, the script counts the init tables, the restored rows and the indexes.

Probe: right after the wait returns, run psql -c 'select 1' over the socket every ~50 ms for 8 s. 5 trials per wait. I ran it in two separate workflow runs (a fresh VM each, same day, same image):

• socket wait, run 1: 8, 7, 6, 7, 6 failed connections out of 91–97 attempts. The errors were "shutting down" ×3–4, "starting up" ×2 and "No such file" ×0–2.
• socket wait, run 2: 2, 2, 2, 2, 2 failed out of 86–90. The errors were "shutting down" ×1 and "No such file" ×1.
• TCP wait (run 2): 0, 0, 0, 0, 0 failed out of 91–94.

Restore of the 3M-row dump started as soon as the wait returned, 5 trials each (run 2):

The socket check says "ready" about 1.8 s before the server really is (median 1,474 vs 3,290 ms).

Not every run fails, which is why this looks flaky:

• The 300k-row restore (about 0.5 s) finished inside the temporary server's lifetime and succeeded 10/10 with either wait.
• In run 1, the 3M-row restore succeeded 5/5 after the socket wait. Per trial (ready after ms / restore ms / exit): 2390/4760/0, 2211/5450/0, 2254/5594/0, 2202/4817/0, 2403/5289/0, all with 3,000,000 rows and the primary key. The TCP-wait trials in that run: 4197/5513/0, 4543/6245/0, 4227/5799/0, 3788/5804/0, 3815/6209/0. Run 1 did not record the temporary server's shutdown time for these trials, so why its restores survived is not established; the sample log above (run 2) has the shutdown 3.1 s after "ready", and these restores ran 4.8–5.6 s. What was measured: the same dump and wait failed 5/5 in run 2 and 0/5 in run 1, while the TCP wait passed 10/10 across both runs.
• With the TCP wait it succeeded in every trial of both runs.

Limits

• The image here is pgvector/pgvector:pg16, which uses the official docker-library postgres entrypoint. Images with their own entrypoint (Bitnami and others) may not run a socket-only init server.
• The init server exists only on the first start with an empty data directory. Restarts of an initialised container have no such window, so the bug shows up only in fresh-volume paths: CI, restore tests, new environments.
• Whether work started on the temporary server fails depends on timing: init script length, data size and machine speed. The shared 2-vCPU VM changed the window between runs (2 vs 6–8 failed probes). The counts show that the window exists and that the TCP wait removes it; they do not predict a rate on other machines.
• The TCP wait assumes the real server listens on 127.0.0.1 inside the container. That is the image's default, listen_addresses='*'. If you narrowed listen_addresses, use an address it does listen on.