postgres-container-ops

Files (1)

• SKILL.md

SKILL.md (first lines)

# 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 — if `postgres-dba` is installed, that agent never executes upgrades, restores, or config changes; otherwise `database-reviewer` or the main agent does not either. 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:**

```bash
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.

```bash
# 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` stays on the never-execute list if `postgres-dba` is installed, and `database-reviewer` or the main agent does not run it otherwise — 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. If `coder` is installed, dispatch to that agent with a precise brief, then a human applies it. Otherwise the main agent loads the `coder` skill, writes the compose change inline, and 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`)

```bash
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

```yaml
# 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:

```bash
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:

| Knob | ⚠️ Heuristic | Why |
|---|---|---|
| `interval` | 5–10s | How often to probe. Too tight = needless load; too loose = slow failure detection. |
| `timeout` | 5s | Per-probe budget; must exceed a normal `pg_isready` round-trip or healthy-but-busy servers flap. |
| `retries` | 3–5 | Consecutive failures before `unhealthy`. Guards against a single slow probe. |
| `start_period` | 30–60s | Grace window while the server initialises; probes inside it don't count toward `retries`. This repo runs EF migrations + `IdentitySchemaMigrator` on backend boot, but Postgres itself starts fast — keep `start_period` modest. |

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

```yaml
# 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)

```bash
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.

From fmflurry/settings-opencode, MIT.

More from fmflurry/settings-opencode