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.