Postgres Container Ops

PostgreSQL Container Operations

Target version: postgres:16-alpine — this repository's gc-platform-postgres container (db gcplatform, owner role gcplatform, data on named volume postgres-data → /var/lib/postgresql/data). The second Postgres, gc-platform-langfuse-postgres (Langfuse observability tier), is out of scope for this skill unless explicitly asked.

Contract: auditing is read-only (docker volume inspect, docker inspect, docker ps, pg_isready). Every upgrade, dump/restore, volume, or server-config command below is emitted ONLY as a ⚠️ HUMAN CONFIRMATION REQUIRED block — the postgres-dba agent never executes upgrades, restores, or config changes. Image-layout facts marked (verified) are confirmed against the official docker-library postgres README; facts marked ⚠️ are established guidance to verify before acting.

Source of truth for image behaviour: https://github.com/docker-library/docs/blob/master/postgres/README.md (verified). pg_upgrade mechanics: https://www.postgresql.org/docs/current/pgupgrade.html ⚠️.

Connection / inspection:

docker exec gc-platform-postgres psql -U gcplatform -d gcplatform -c "<query>"
docker exec gc-platform-postgres pg_isready -U gcplatform -d gcplatform

1. Volume persistence audit

Where the data physically lives is the single most important container fact. Get it wrong and a container re-create silently loses the database.

# Named volume + its host mountpoint:
docker volume inspect postgres-data

# What is actually mounted inside the container, and where:
docker inspect --format '{{range .Mounts}}{{.Type}} {{.Name}} -> {{.Destination}}{{println}}{{end}}' gc-platform-postgres

# Restart-survival probe (read-only): the data dir must be on the named volume, not an anonymous one:
docker inspect --format '{{range .Mounts}}{{if eq .Destination "/var/lib/postgresql/data"}}{{.Name}} ({{.Type}}){{end}}{{end}}' gc-platform-postgres

Interpretation:

• PG ≤ 17 (this repo, on 16): the image declares its VOLUME at /var/lib/postgresql/data, and PGDATA defaults there. The data volume must be mounted at /var/lib/postgresql/data. (verified) Mounting at /var/lib/postgresql on PG ≤ 17 WILL NOT PERSIST data: the runtime auto-creates an anonymous volume at the declared path and data is written there instead, lost on container deletion. (verified)
• PG18+ layout change (matters for any 16→18 upgrade plan): from PostgreSQL 18 the image makes PGDATA version-specific — /var/lib/postgresql/<major>/docker (e.g. /var/lib/postgresql/18/docker) — and moves the declared VOLUME up to /var/lib/postgresql. Mounts/volumes must target the new location. (verified) This is precisely what enables the fast pg_upgrade --link path (see §5): old and new data directories coexist under one mounted /var/lib/postgresql. (verified)
• Opt-in early: you can adopt the PG18 layout on 16/17 by setting PGDATA explicitly (PGDATA=/var/lib/postgresql/17/docker) and mounting the volume at /var/lib/postgresql — but to migrate pre-existing data you must first move all database files into a <PG_MAJOR>/docker subdirectory of the volume. (verified) Treat that move as a mutation (human-only, with a fresh backup).
• Anonymous volume or bind-mount into the repo tree = CRITICAL. Anonymous volumes vanish with docker compose down; repo-tree bind-mounts corrupt on macOS Docker file-sharing. docker volume rm postgres-data is on the postgres-dba never-execute list — deletion is a human decision taken with a verified backup in hand.

Severity: CRITICAL (data not on the named volume postgres-data; anonymous volume; restart loses data); HIGH (bind-mount into repo tree); else OK.

Remediation: a volume/mount fix is a compose change → dispatch coder with a precise brief, then a human applies it. Never re-point or delete a volume without a verified restorable backup (see postgres-backup-restore §5 restore-drill).

2. /dev/shm sizing (shm_size)

docker inspect --format '{{.HostConfig.ShmSize}}' gc-platform-postgres   # bytes; 67108864 = 64MB

Interpretation: Docker defaults a container's /dev/shm to 64MB. PostgreSQL uses POSIX shared memory for parallel-query coordination; when /dev/shm is exhausted, parallel workers fail with ERROR: could not resize shared memory segment ... : No space left on device. The official postgres image documents 64MB as a known pitfall and recommends raising it (its compose example uses shm_size: 128mb; ⚠️ heuristic: ≥ 256MB for parallel workloads). (verified re: the 64MB default + the error; the 256MB figure is a heuristic.) This compose sets no shm_size, so the container is on the 64MB default.

Severity: MEDIUM (64MB on a workload that uses parallel query); LOW (no parallel query observed).

Remediation (compose change → coder + human docker compose up -d; restart required):

⚠️ HUMAN CONFIRMATION REQUIRED

# docker-compose.yml, postgres service
shm_size: 256mb

Verify after restart: docker inspect --format '{{.HostConfig.ShmSize}}' gc-platform-postgres (expect 268435456).

3. Healthcheck tuning

Current compose healthcheck: pg_isready -U gcplatform -d gcplatform. Read the live config:

docker inspect --format '{{json .Config.Healthcheck}}' gc-platform-postgres
docker inspect --format '{{.State.Health.Status}} | failing={{.State.Health.FailingStreak}} | restarts={{.RestartCount}}' gc-platform-postgres

Interpretation: pg_isready only confirms the server accepts connections — it does NOT prove the database is usable (it can return success mid-recovery, or while a hot table is lock-blocked). Tune the four knobs, not the probe command:

Anything but healthy (or a climbing FailingStreak/RestartCount) → read docker logs --tail 200 gc-platform-postgres before touching the healthcheck. A flapping healthcheck is usually a symptom (crash-loop, OOM, PANIC), not a misconfigured probe.

Severity: MEDIUM (no start_period and the stack falsely reports unhealthy during boot); LOW (cosmetic tuning); the underlying cause of an unhealthy container is rated on its own merits (CRITICAL for crash-loop/PANIC).

Remediation (compose change → coder + human restart):

⚠️ HUMAN CONFIRMATION REQUIRED

# docker-compose.yml, postgres service
healthcheck:
  test: ["CMD-SHELL", "pg_isready -U gcplatform -d gcplatform"]
  interval: 10s
  timeout: 5s
  retries: 5
  start_period: 30s

4. Minor-version bumps (pin policy)

docker inspect --format '{{.Config.Image}}' gc-platform-postgres   # confirm the running tag
docker image inspect --format '{{index .RepoDigests 0}}' postgres:16-alpine 2>/dev/null  # pinned digest, if pulled

Interpretation: the compose pins 16-alpine — a floating major-version tag that tracks the latest 16.x. Minor releases (16.x → 16.x+1) are backward-compatible bugfix/security drops; pulling a newer 16.x and restarting is the supported upgrade path and needs only a container restart (brief downtime). The risk with a floating tag is surprise: a docker compose pull + up -d silently moves the minor version.

⚠️ Heuristic pin policy:

• Reproducible environments: pin the explicit minor, e.g. 16.x-alpine (replace x with the current tested minor — 16.14 at the time of writing per the docker-library tag list), and bump it deliberately after reading the release notes.
• Always read the minor release notes before bumping (https://www.postgresql.org/docs/release/ ⚠️) — they list any behaviour fixes that could bite.
• A floating 16-alpine is acceptable for dev; pin the digest or explicit minor for anything you must reproduce.

Severity: LOW (floating tag in dev); MEDIUM (floating tag where reproducibility matters).

Remediation (compose change → coder; the pull + restart is human-executed):

⚠️ HUMAN CONFIRMATION REQUIRED

# After pinning the explicit minor in docker-compose.yml:
docker compose pull postgres
docker compose up -d postgres        # brief downtime: the server restarts on the new minor
docker exec gc-platform-postgres psql -U gcplatform -d gcplatform -c "SELECT version();"

A minor bump is NOT a major upgrade — no pg_upgrade, no dump/restore.

5. Major-version upgrade runbook (the core)

A major upgrade (16 → 17/18) changes the on-disk catalog format; the new server will not start on the old data directory until it is upgraded. Two paths:

pg_upgrade mechanics: https://www.postgresql.org/docs/current/pgupgrade.html ⚠️. Image layout facts: docker-library README (verified).