PostgreSQL in a Container for a .NET App

The reason most .NET developers on Windows install Docker is a database, and PostgreSQL in a container is the one this site's readers ask about most. This part starts the official image the way a .NET app needs it, with a user, a database, an init script and a named volume, then does the things that go wrong: kills the container and watches the data survive, connects from the app on Windows with the right and the wrong password, connects from another container on the right port, backs up and restores through the container, and feeds PostgreSQL 16 data to the 18 image. Every output is quoted from the run.

Three variables, one folder, one volume

docker run -d --name dw-p5-db -e POSTGRES_USER=clinic -e POSTGRES_PASSWORD=dw-p5-pass -e POSTGRES_DB=clinic -v dw-p5-data:/var/lib/postgresql -v "${PWD}\db:/docker-entrypoint-initdb.d:ro" -p 15852:5432 postgres:18

Two seconds later the log shows what the entrypoint did. It initialised a cluster, with Data page checksums are enabled. as the 18 default, started a temporary server that listens on a Unix socket only, created the database, ran the mounted script, and shut the temporary server down before starting the real one:

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/01-schema.sql
CREATE TABLE
INSERT 0 3

PostgreSQL init process complete; ready for start up.

That is why "database system is ready to accept connections" appears twice in every first start, and why a script that waits for the first one connects to a server that is about to stop. The real server logs listening on IPv4 address "0.0.0.0", port 5432, and docker exec dw-p5-db psql -U clinic -d clinic showed the three patients without asking for a password: inside the container psql uses the socket, which initdb set to trust, as its own warning line says. The image's documentation states the rule: "a password will be required if connecting from a different host/container."

What POSTGRES_USER really does

People expect a postgres superuser to exist regardless. It does not. \du listed one role, clinic, with superuser rights, and the usual admin login failed:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

A database called postgres still exists, owned by clinic, so tools that connect to it by default still work, with the clinic user. The documentation's sentence is exact: the variable "will create the specified user with superuser power and a database with the same name." The data directory inside the volume is /var/lib/postgresql/18/docker, the versioned layout from Part 3, so a future 19 image would sit beside it rather than on top of it.

The volume outlives the container, with one lesson

A fourth patient was inserted, the container was removed with docker rm -f, and the same docker run was issued again on the same volume. The new log opened with:

PostgreSQL Database directory appears to contain a database; Skipping initialization

No init script, no table creation, and the four rows were there. The documentation warns twice that the variables and the scripts "will only have an effect if you start the container with a data directory that is empty". The lesson the run added: rm -f does not shut the server down, and the new container had to recover:

database system was not properly shut down; automatic recovery in progress

The committed row survived, as it should, but the identity sequence jumped and the next patient got id 37. docker stop first, then docker rm: the log then says received fast shutdown request and the next start reports a clean shutdown and no recovery.

Two refusals you will meet

Without a password the container exits in a second:

Error: Database is uninitialized and superuser password is not specified.
       You must specify POSTGRES_PASSWORD to a non-empty value for the
       superuser. For example, "-e POSTGRES_PASSWORD=password" on "docker run".

With the 16-era mount point, -v dw-p5-wrong:/var/lib/postgresql/data, and a brand-new, empty volume, the 18 image refuses to start as well. Its message says there "appears to be PostgreSQL data in: /var/lib/postgresql/data (unused mount/volume)", and the volume was shown to be empty, so read the suffix, not the sentence: the 18 entrypoint refuses any mount at the old path. Both messages have Fixes posts, for the password and the mount point.

The app on Windows talks to the container

The clinic API's appsettings.json names Host=localhost;Port=15852 and the user and password from the docker run; dotnet run on Windows, and /patients returned the four rows as JSON while /health said {"status":"ok","database":"reachable"}. With the password changed to a wrong one, both endpoints returned a 500 with an empty body, Production hides exceptions, and the app log held the reason:

Npgsql.PostgresException (0x80004005): 28P01: password authentication failed for user "clinic"

The server side logs the same failure with the rule that applied:

DETAIL:  Connection matched file "/var/lib/postgresql/18/docker/pg_hba.conf" line 128: "host all all all scram-sha-256"

Two facts from that line: connections from outside the container use scram-sha-256, so a password is always required, and the 28P01 that lands in your .NET log is the same one the Fixes post decodes.

From another container: 5432, not 15852

A second container on a shared network reached the database with pg_isready -h dw-p5-db -p 5432: dw-p5-db:5432 - accepting connections. The same probe with 15852 printed no response, and so did the probe from a container with no network at all. -p 15852:5432 publishes the container's 5432 on Windows as 15852, and that mapping applies to traffic arriving at the host; another container talks to the database directly and uses the port PostgreSQL listens on. So the connection string is Host=localhost;Port=15852 from Windows and Host=dw-p5-db;Port=5432 from a container, and Part 6's compose file will say Host=db;Port=5432. Over TCP the password is needed even between containers; PGPASSWORD in the client's environment supplies it for psql.

Backup and restore through the container

The obvious docker exec dw-p5-db pg_dump -U clinic clinic > clinic.sql in Windows PowerShell 5.1 produces a file psql cannot read, because the redirect writes UTF-16: the first bytes are FF FE, and the restore stops with invalid byte sequence for encoding "UTF8": 0xff. The form that works writes inside the container and copies out:

docker exec dw-p5-db pg_dump -U clinic -f /tmp/clinic.sql clinic
docker cp dw-p5-db:/tmp/clinic.sql clinic.sql

PowerShell also has no < input redirection ("The '<' operator is reserved for future use"), so restore with docker cp in and psql -f, or through cmd /c. Restoring an 18 dump into a 16 container worked apart from one line, because pg_dump 18 writes a SET transaction_timeout = 0; that 16 does not know; the supported direction, the newer pg_dump reading the older server, ran clean. From inside the 18 container, pg_dump -h host.docker.internal -p 15854 reached the 16 container through its published port and restored five rows with no errors.

PostgreSQL 16 data on the 18 image

The 16 container's volume mounted on the 18 image at the old path got the entrypoint's "in 18+" refusal before PostgreSQL started. Mounted at the 18 data path instead, the server itself refused:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 16, which is not compatible with this version 18.4 (Debian 18.4-1.pgdg13+1).

Those are the two different errors behind two Fixes posts, and neither is an upgrade path. The upgrade is the dump and restore above, which PostgreSQL 16 vs 18 walks through with the features that make it worth doing.

Frequently asked

How do I run PostgreSQL in Docker for a .NET app?

docker run -d with POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB, a named volume at /var/lib/postgresql for the 18 image, a folder of SQL scripts mounted at /docker-entrypoint-initdb.d, and -p 15852:5432. The app on Windows connects with Host=localhost;Port=15852 and the user and password from the run; the scripts and variables apply only when the volume is empty.

Why does my .NET app get 28P01 password authentication failed against Docker PostgreSQL?

The password in the connection string differs from POSTGRES_PASSWORD, or the volume was initialised earlier with a different password that the new environment variable cannot change. Inside the container psql needs no password because the socket uses trust; every TCP connection, from Windows or another container, matches the scram-sha-256 rule in pg_hba.conf and must authenticate.

Which port does a container use to reach Docker PostgreSQL?

The container port, 5432, with the database container's name as the host, on a shared user-defined network. The published host port, for example 15852, exists only on the Windows side; inside the container nothing listens there, and pg_isready reports no response.

Next: Part 6, Compose: the database and the API from one file, with a healthcheck that makes the app wait, an .env for the password, and down -v when you want a clean slate.