PostgreSQL with Docker Compose: a private database on a CPU VM

Docker Compose can run PostgreSQL with an explicit image version, a named data volume, and a database network that has no published host port. This walkthrough builds a disposable development database, creates a separate application role, and gives you checks for authenticated access and data surviving container recreation.

Use it on a Linux host where you control Docker, including a suitable CPU VM. Start with synthetic data. If you are still choosing who should operate the database, compare managed PostgreSQL with a self-managed VM first.

Validation, November 9, 2026: the Compose configuration was checked locally with Docker Compose 5.1.4, including the tools profile, secret reference, network isolation, image version, and data mount. Shell syntax was checked. The local Docker engine was unavailable, so image pull, container startup, SQL execution, password rejection, persistence, and recovery were not run. The commands below describe checks to perform; they are not recorded test results. Hivenet connectivity and cloud behavior remain untested.

Prepare an isolated project and a password file

You need a maintained Linux host, Docker Engine, the Compose plugin, OpenSSL, and permission to run Docker. Confirm that the engine responds as well as the CLI. For host setup, use the Docker on a Compute VM guide. The shell commands here use a Linux shell, not PowerShell.

docker version
docker compose version

Create a new directory. Stop if pg-compose-lab already exists; use a distinct project directory and Compose project name rather than reusing an existing database volume. Run each command only after the previous one succeeds. Keep this shell open so the restrictive file-creation mask also applies when you save the configuration.

mkdir pg-compose-lab
cd pg-compose-lab
umask 077
mkdir secrets
openssl rand -hex 32 > secrets/postgres_password.txt
printf 'secrets/\n' > .gitignore

The password is generated into a file rather than printed or put in shell history. Keep that file out of Git, shared archives, and screenshots. A local Compose secret is a mounted file, not an encrypted secret-management service. Protect the host and its backups; anyone controlling the Docker daemon can access the database. Our Compose secrets guide explains the delivery and rotation limits.

Save the PostgreSQL Docker Compose file

Save the following as compose.yaml beside the secrets directory. Both services use postgres:18.6-bookworm, listed in the Docker Official Image documentation at this review. This names a patch version and base distribution; a tag is still mutable. Record the pulled digest if you need to reproduce the exact image bytes.

name: pg-compose-lab
services:
  db:
    image: postgres:18.6-bookworm
    environment:
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres
      POSTGRES_PASSWORD_FILE: /run/secrets/postgres_password
      POSTGRES_INITDB_ARGS: --auth-host=scram-sha-256
    secrets:
      - postgres_password
    volumes:
      - pgdata:/var/lib/postgresql
    networks:
      - database
    healthcheck:
      test: ["CMD", "pg_isready", "-h", "127.0.0.1", "-U", "postgres", "-d", "postgres"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s
    restart: unless-stopped
  client:
    image: postgres:18.6-bookworm
    profiles: [tools]
    entrypoint: [psql]
    networks:
      - database
networks:
  database:
    internal: true
volumes:
  pgdata:
secrets:
  postgres_password:
    file: ./secrets/postgres_password.txt

For PostgreSQL 18, the image uses /var/lib/postgresql/18/docker as its default data directory and expects the volume at /var/lib/postgresql. The usual PostgreSQL 17-and-earlier mount is /var/lib/postgresql/data. Do not copy an old mount unchanged or switch major versions against an existing data directory without an upgrade plan.

There is no ports entry. The database and temporary client share an internal network; db:5432 is the database address there. The host and your laptop do not get a published database endpoint. This limits routing, not access by a host administrator or a container deliberately attached to that network. Compose networking explains service-name discovery. See our networking guide before adding an application service.

Only db receives the administrator secret. The optional client service runs psql on demand and receives no administrator credentials. The POSTGRES_USER setting creates a database superuser, so keep it separate from the application role you create below.

Check configuration, then start the database

From the project directory, validate both the default configuration and the optional client profile. These commands parse configuration without starting PostgreSQL:

docker compose config --quiet
docker compose --profile tools config --quiet

If either exits unsuccessfully, fix the reported configuration error before continuing. Configuration validation does not prove that the image exists, the secret can be read at runtime, or the storage works. With a working engine, pull the selected image and start only the database:

docker compose pull db
docker image inspect postgres:18.6-bookworm --format "{{json .RepoDigests}}"
docker compose up --wait --wait-timeout 120 db
docker compose ps

The wait budget is an example, not a startup guarantee. Investigate a timeout with docker compose logs --tail 100 db. Do not delete the volume to silence an initialization error. The Compose up reference defines the wait behavior.

The probe uses TCP to check whether PostgreSQL is accepting connections. pg_isready does not need valid login credentials to report server status, so a healthy result does not prove the application password or SQL permissions work. An unhealthy result also does not make restart: unless-stopped restart a running container. Keep those distinctions from the healthcheck guide when adding application dependencies.

Create a separate application role and test its access

Open the administrator session through the container’s local Unix socket. The image permits this local administration path; it does not test remote password authentication. Docker access already carries administrative power.

docker compose exec db psql -X -v ON_ERROR_STOP=1 -U postgres -d postgres

At the psql prompt, enter the following commands. \password app prompts twice for a new application password. Generate and keep that password in your password manager; do not reuse the administrator password. Stop on any error, including an existing role or database, and inspect the project before retrying.

CREATE ROLE app LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
\password app
CREATE DATABASE appdb OWNER app;
\connect appdb
CREATE SCHEMA app AUTHORIZATION app;
\q

The role attributes prevent cluster administration, while ownership of appdb and its application schema allows this development role to create its own tables. This is deliberately broader than a restricted production runtime role. A deployed application may need separate migration ownership and narrower grants. PostgreSQL’s psql documentation describes the password prompt and error-stop option.

Now connect from the temporary client container. Enter the application password when prompted. The database host is db; localhost inside this client would refer to the client itself.

docker compose run --rm client -X -v ON_ERROR_STOP=1 -h db -U app -d appdb -W

At that psql prompt, create a disposable marker. Confirm the connection identifies appdb and app, and that the final query returns row 1 with the exact marker text:

SELECT current_database(), current_user, version();
CREATE TABLE app.compose_check (id integer PRIMARY KEY, note text NOT NULL);
INSERT INTO app.compose_check VALUES (1, 'container-recreation-check');
SELECT id, note FROM app.compose_check;
\q

Repeat the client command once with an intentionally wrong password. Expect a failed authentication and no SQL session. If it connects, stop and inspect the authentication configuration before adding data. The server’s host rule and the actual connection route matter; a local socket login cannot substitute for this check.

For an application, use the same service-name route and deliver its credentials through a mechanism that application supports. Do not hand it the administrator secret. The client prompt is suitable for this manual exercise; automated clients can use a protected libpq password file with appropriate permissions instead of a password embedded in a command or connection URL.

Once the application-access checks pass, load a daily CSV into PostgreSQL with a Python ETL pipeline. Its optional job service joins this lab’s private database network. Local validation checks passed; SQL execution, reruns, container behavior, scheduling, and cloud deployment remain untested.

Check that data survives container recreation

Exit psql before continuing. In this disposable project, recreate the database container while keeping its named volume:

docker compose up --force-recreate --wait --wait-timeout 120 db
docker compose run --rm client -X -v ON_ERROR_STOP=1 -h db -U app -d appdb -W -c "SELECT id, note FROM app.compose_check ORDER BY id;"

Enter the same application password. The expected result is the original row and marker text. Record the actual result; a successful container restart alone is not a persistence check. If the row is missing, inspect the project name, volume mount, and data directory before writing more data. Do not rerun the table creation just to make the check pass.

A named volume separates database files from the replaceable container, but it still depends on its storage host. It does not supply an independent backup or recovery from a lost VM. Use the Docker volumes guide for that boundary and the existing PostgreSQL backup and restore procedure for an isolated recovery exercise. That procedure’s runtime limits still apply.

For a normal teardown of this lab’s containers and network, run docker compose down from this directory. It keeps the named volume. Do not add --volumes or -v unless you intend to delete this lab’s database. Returning with the same project name and volume should reuse its data; verify that before relying on it.

Understand initialization, updates, and failures

The image’s setup variables and files under /docker-entrypoint-initdb.d apply to an empty data directory. Changing the password file after initialization does not rotate an existing database password, and adding an initialization script does not migrate an existing database. Use explicit SQL changes or a migration process, and coordinate credential changes with clients.

Common failures have different causes. A name-resolution failure suggests the wrong service name or network. Authentication failure calls for checking the role, database, password, and host authentication rules. Missing data after recreation calls for checking the mount and project identity. A healthcheck timeout calls for reading startup logs and checking resources. None of these is a reason to erase a volume without understanding its contents.

Before a patch update, record the current image digest and test the replacement against a disposable copy. Before a major-version change, follow PostgreSQL’s supported upgrade procedure and rehearse recovery. Simply replacing 18 with another major version in the image tag is not a database migration.

When you add application workers, review PgBouncer connection pooling and compatibility checks before increasing database connection limits. The pooling guide assumes a separately configured private endpoint; the Compose example here does not deploy PgBouncer. Its database and cloud runtime checks remain untested.

Evaluate the CPU VM with the same limits

Hivenet’s vCPU documentation includes database hosting for development or testing. Use that scope here. Confirm the available OS, Docker support, storage, location, and access route on the actual instance; this article has not validated a Hivenet deployment or a production database design.

A Docker volume cannot override the VM lifecycle. Under Hivenet’s stop and termination rules, a stopped instance expires after 10 days and its local data is deleted. Keep recovery copies outside the instance and test their restoration.

Size a CPU evaluation from the dataset, concurrent queries, memory use, and storage behavior, then measure the representative workload. A passing smoke query establishes none of those limits. Record configuration checks, access tests, persistence results, and recovery evidence separately before deciding what the environment is ready to hold.