Add Postgres Database to Milner #35

New issue

Closed

opened 2026-09-09 14:52:03 -07:00 by tepichord · 1 comment

tepichord commented 2026-09-09 14:52:03 -07:00

Owner

Copy link

• Host a Postgres Database that can be used across the entirety of the Milner cluster for various applications
• Data from Postgres must be persisted across cluster teardowns & restarts
• Existing services must be migrated to utilize Postgres if easily available, otherwise file storage is sufficient.

- Host a Postgres Database that can be used across the entirety of the Milner cluster for various applications - Data from Postgres must be persisted across cluster teardowns & restarts - Existing services must be migrated to utilize Postgres if easily available, otherwise file storage is sufficient.

tepichord added the

status

planning

enhancement

effort

medium

labels 2026-09-09 14:52:03 -07:00

tepichord added this to the MVP project 2026-09-09 14:52:03 -07:00

tepichord commented 2026-09-09 16:17:00 -07:00

Author

Owner

Copy link

Naina:

Overview

Deploy a cluster-wide PostgreSQL via the CloudNativePG (CNPG) operator on a single-replica Cluster, backed by a new dedicated worker data volume that survives teardowns (un-managed per the README removed-block pattern). Because Talos ships no StorageClass and CNPG provisions via PVCs (not hostPath like Vault), this also deploys the Rancher local-path-provisioner — which is exactly the local-path StorageClass the Woodpecker PR (#37) already assumes but nothing provides today. No existing service can migrate yet (Vault/Pocket ID don't use Postgres, Woodpecker isn't deployed), so this ships the substrate + one ready-to-consume database.

Technical Approach

1. Add the Postgres data volume to the worker (worker/main.tf)

# local: postgres_disk_name = "${var.cluster_name}-worker-1-postgres.qcow2"
resource "libvirt_volume" "postgres_data" {
  name     = local.postgres_disk_name
  pool     = var.pool_name
  capacity = 53687091200 # 50 GiB — shared local-path root (CNPG 20Gi PVC + future PVC consumers)
  target = { format = { type = "qcow2" } }
}

Attach as the next free virtio disk (vdc today, vdd if #34's pocket-id disk merges first — order by index, never a hard-coded letter) and add a third/fourth Talos UserVolumeConfig:

apiVersion: v1alpha1
kind: UserVolumeConfig
name: postgres
provisioning:
  diskSelector:
    match: "!system_disk && disk.size > 40u * GiB"

Tighten every data-disk selector to be mutually exclusive — vault's bare !system_disk (worker/main.tf) matches any non-system disk and will grab the new one¹. Size bands vs. the fixed capacities (vault 20 GiB², pocket-id 10 GiB if #34 lands³, postgres 50 GiB):

# vault:     20 GiB disk
diskSelector = { match = "!system_disk && disk.size > 15u * GiB && disk.size < 40u * GiB" }
# pocket-id: 10 GiB disk (only if #34 merged; else add when it does)
diskSelector = { match = "!system_disk && disk.size < 15u * GiB" }
# postgres:  50 GiB disk
diskSelector = { match = "!system_disk && disk.size > 40u * GiB" }

Whatever the merge order with #34, worker/main.tf must converge on this one exclusive set. Mount activates only at boot → apply then terraform -chdir=worker apply -replace='libvirt_domain.worker' (OPERATIONS.md §4e vault precedent⁴).

2. Make the volume teardown-durable (the issue's hard requirement) — per README.md "How Changes Ship", genuine persistent volumes are un-managed so destroy never removes them⁵:

removed {
  from = libvirt_volume.postgres_data
  lifecycle { destroy = false }
}

The qcow2 stays in the CP-owned talos-images pool (external 4 TB_^unverified_ data_root_path)⁶ across worker destroy/cleanup.sh and re-provisioning; a recreated worker re-attaches and re-mounts it with data intact. Exact adopt-sequence (create while managed → add removed, drop the declaration → later terraform import on rebuild) is an implement-time reconcile against Terraform 1.7 semantics⁷ — the acceptance criterion below pins the outcome.

3. Create the postgres/ Terraform root — copy the remote-state/providers preamble from vault/main.tf verbatim, then three namespaces:⁸

namespace	PSA label	reason
local-path-storage	privileged	provisioner DaemonSet + helper pods need hostPath/root (Talos #10817)9
cnpg-system	none (default)	operator is hardened non-root
postgres	restricted	CNPG pods are non-root, hardened by default10

resource "kubernetes_namespace_v1" "postgres" {
  metadata {
    name = "postgres"
    labels = { "pod-security.kubernetes.io/enforce" = "restricted" }
  }
}

4. Deploy the local-path provisioner (helm_release, ns local-path-storage)¹¹¹²

resource "helm_release" "local_path_provisioner" {
  name       = "local-path-provisioner"
  repository = "oci://ghcr.io/rancher/local-path-provisioner/charts"
  chart      = "local-path-provisioner"
  version    = "0.0.37"               # image rancher/local-path-provisioner:v0.0.37 (arm64 ✓)
  namespace  = kubernetes_namespace_v1.local_path_storage.metadata[0].name
  values     = [file("${path.module}/values.yaml")]
}

postgres/values.yaml — provision from the new qcow2 mount, keep the class named local-path (woodpecker's values already target it¹³), not default:

image:
  tag: "v0.0.37"
nodeSelector:
  node-role: worker
nodePathMap:
  - node: DEFAULT_PATH_FOR_NON_LISTED_NODES
    paths:
      - /var/mnt/postgres      # Talos user volume from step 1
storageClass:
  create: true
  name: local-path             # matches woodpecker/values.yaml assumption (#37)
  defaultClass: false
  reclaimPolicy: Delete
  volumeBindingMode: WaitForFirstConsumer
resources:
  requests: { cpu: 100m, memory: 128Mi }

5. Deploy the CNPG operator (helm_release, ns cnpg-system) — chart cloudnative-pg 0.29.0 (operator 1.30.0)¹⁴ from https://cloudnative-pg.github.io/charts; K8s v1.34 is in its supported matrix¹⁵ (Milner runs v1.34.0¹⁶). values.yaml sets config.clusterWide: true (default¹⁷ — one operator serves future Clusters in other roots) and resources.requests: {cpu: 100m, memory: 200Mi}.

6. Declare the single-instance Cluster (kubernetes_manifest, ns postgres, depends_on the operator release so CRDs exist¹⁸)

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres
  namespace: postgres
spec:
  instances: 1
  nodeSelector:
    node-role: worker          # CNPG 1.30 default operand image is multi-arch (arm64 ✓); no imageName pin needed
  bootstrap:
    initdb:
      database: app            # app db + owner, creds land in the generated `postgres-app` secret
      owner: app
  storage:
    size: 20Gi
    storageClass: local-path

Cluster-wide reachability is CNPG's auto-created ClusterIP service¹⁹ — no Ingress, no ProxyGroup:

postgres-rw.postgres.svc.cluster.local:5432   # primary; apps use <cluster>-rw, never pod IPs
kubectl -n postgres get secret postgres-app -o jsonpath='{.data.password}' | base64 -d

7. Wire tests + docs — tests/test_static.py: add postgres/ to the terraform fmt dirs, postgres/values.yaml to the yamllint list; add test_terraform_test_postgres() to tests/test_terraform.py if the Woodpecker PR has landed it by implement time. Update README.md workload list, ARCHITECTURE.md topology/service-table/storage-distribution, AGENTS.md repo map, and add an OPERATIONS.md Postgres section (connect creds, worker -replace note, emergency pg_dump).

Implementation Details

• Files created: postgres/main.tf, postgres/values.yaml, postgres/README.md, postgres/.gitignore (copy of vault's), postgres/tests/plan.tftest.hcl + postgres/tests/fixtures/cp.tfstate (copy of vault's fixture).
• Files modified: worker/main.tf (volume, disk attach, UserVolumeConfig, selector tightening, removed block), worker/tests/plan.tftest.hcl, tests/test_static.py, possibly tests/test_terraform.py, README.md, ARCHITECTURE.md, AGENTS.md, OPERATIONS.md.
• Existing worker tests that break and must be updated (indices shift): user_volume_config_is_in_patches (asserts 2 patches + vault match == "!system_disk"), worker_domain_cdrom_is_scsi (disks[2] cdrom → disks[3]), installer_iso_ejects_when_disabled (post-eject disk count 2 → 3)²⁰. Add runs for the postgres volume (capacity/format/name), the vdc/vdd attach, and the postgres UserVolumeConfig + selector exclusivity.
• Dependencies (pinned): CNPG operator chart cloudnative-pg 0.29.0 from https://cloudnative-pg.github.io/charts (operator 1.30.0, supports K8s 1.34/1.35/1.36)²¹; local-path-provisioner chart 0.0.37 (OCI ghcr.io/rancher/local-path-provisioner/charts, image rancher/local-path-provisioner:v0.0.37, arm64 multi-arch verified); CNPG default operand ghcr.io/cloudnative-pg/postgresql:18.4 (multi-arch, not pinned).
• Constraints: arm64-only images (both new images verified); no new dev-shell tools — verify with psql via kubectl exec into the CNPG pod (flake.nix unchanged²²); postgres namespace CRD ordering handled by depends_on on the operator release; secrets stay in the CNPG-generated postgres-app Secret²³ (no VSO yet, no hand-run bootstrap).
• Out-of-scope notes:
  • No existing-service migration — nothing deployed today uses Postgres (Vault: no; Pocket ID #34: SQLite by design; Woodpecker #37: not merged, SQLite default). The postgres-rw service + postgres-app secret are the contract a future app (e.g. evolved-npcs) consumes; Woodpecker can flip to WOODPECKER_DATABASE_* postgres env later if wanted.
  • No Tailscale/external exposure — the issue asks for cluster-wide, which ClusterIP satisfies; pgAdmin-style tailnet access is a later Service type=LoadBalancer + loadBalancerClass: tailscale add-on.
  • The local-path StorageClass is cluster-shared substrate: woodpecker's PVCs will bind to the same /var/mnt/postgres volume (50 GiB covers CNPG 20Gi + known ~13 GiB consumers with headroom; a second provisioner root/volume is a future storage issue).
  • No ScheduledBackup/WAL archiving (needs an S3-compatible endpoint; local-path can't do volume snapshots) — the durable qcow2 is the backup story, pg_dump is the emergency runbook.
  • Vault's own 20 GiB qcow2 has the same teardown exposure (no removed block today²⁴) — fixing it (and pocket-id's, post-#34) is repo-wide follow-up, not this issue.
  • CNPG enableSuperuserAccess stays default false.

Acceptance Criteria

• worker/main.tf shows the 50 GiB postgres qcow2 + virtio attach + postgres UserVolumeConfig (mount /var/mnt/postgres); all data-disk selectors (vault, postgres, + pocket-id if merged) are mutually exclusive; updated terraform -chdir=worker test is green.
• Teardown durability holds: terraform -chdir=worker destroy / worker/cleanup.sh leaves milner-worker-1-postgres.qcow2 and its data in the pool; re-provisioning the worker re-attaches and the data is still there (manual verify).
• postgres/ root plan tests green: local-path-storage privileged + postgres restricted PSA labels, provisioner helm release (chart/version/root path), operator helm release (chart/repo/version), Cluster CR fields (instances 1, storageClass: local-path, 20Gi, initdb app/app, node-role: worker).
• Live: postgres-1 pod Ready on the worker, PVC Bound on local-path, postgres-app secret exists; a throwaway pod in another namespace connects to postgres-rw.postgres.svc.cluster.local:5432, creates/drops a table, and the data survives a pod restart and a worker -replace.
• Updated pytest suite green (terraform fmt incl. postgres/, yamllint on postgres/values.yaml); README/ARCHITECTURE/AGENTS/OPERATIONS updated and cross-references consistent.

Testing Plan

• Static: python -m pytest tests/ — terraform fmt now includes postgres/; yamllint covers postgres/values.yaml; ruff unchanged.
• Plan assertions: terraform -chdir=postgres test — namespace PSA labels, helm chart/repo/version + provisioner nodePathMap, Cluster manifest fields (mock kubernetes/helm providers, fixture cp.tfstate copied from vault).
• Worker root: terraform -chdir=worker test — new postgres volume/attach/UserVolumeConfig runs; updated index-sensitive runs (patch count, disk positions, eject disk count); selector-exclusivity assertion across all UserVolumeConfigs.
• Live smoke (manual, after apply): kubectl get pods,pvc -n postgres; kubectl exec -n postgres postgres-1 -- psql -U app -d app -c 'create table t(n int); insert into t values (1);'; connect from a second namespace via postgres-rw; kubectl -n postgres delete pod postgres-1 (operator recreates, data intact); worker -replace then re-verify; OPERATIONS.md pg_dump/restore dry-run.

Related Issues/PRs

• Pod - Hashicorp Vault (#33) — merged; the namespace/helm/worker-volume pattern this mirrors.
• Create Pocket ID service on Milner (#34) — sibling issue editing the same worker/main.tf disk selectors; selectors must converge on one mutually exclusive set.
• Pod - Woodpecker CI (#37) — its values require the local-path StorageClass this issue provisions.
• Create Annia pod (#25) / Letta pod (#24) — likely first Postgres consumers.

Research Findings

• CNPG is the recommended operator; Bitnami is dead for new free deploys — Broadcom retired the free docker.io/bitnami catalog (Sep 2025), so the Bitnami Helm chart's images no longer pull freely. CNPG (CNCF, EDB-originated) is the actively developed successor to Zalando/Bitnami and the de-facto standard.
• CNPG needs a StorageClass; it cannot hostPath like Vault — it provisions one PVC per instance and officially discourages static PVs. On Talos the supported equivalent is local-path-provisioner rooted at a UserVolumeConfig mount (/var/mnt/...), with the provisioner's namespace labeled privileged (Talos issues #10817, #11251). CNPG pods themselves are hardened non-root and satisfy PSA restricted.
• arm64 + version matrix verified — operator image cloudnative-pg:1.30.0 and operand postgresql:18.4 are linux/amd64 + linux/arm64; CNPG 1.30.x supports K8s 1.34 (Milner runs v1.34.0). local-path-provisioner v0.0.37 image and chart are arm64 multi-arch (chart OCI at ghcr.io/rancher/local-path-provisioner/charts).
• Credentials & exposure — CNPG self-generates a kubernetes.io/basic-auth <cluster>-app Secret and auto-creates <cluster>-rw/-ro/-r ClusterIP services on 5432; apps should use the service names. Vault/VSO sync is a later integration (VSO not yet deployed). External TCP exposure is a Service type=LoadBalancer + loadBalancerClass: tailscale add-on, not Ingress.
• Backups — CNPG has none by default; in-tree Barman object-store support is deprecated (1.31 removal) for the plugin, and volume-snapshot backups need a CSI driver local-path lacks (plus a single-instance hang bug #2948). The durable qcow2 + emergency pg_dump is the right homelab tier; ScheduledBackup/PITR waits until an S3-compatible endpoint exists.

Pre-Mortem

1. #34's concurrent worker/main.tf changes never converge on exclusive disk selectors — both issues edit the same volume/UserVolumeConfig blocks, and if merge order leaves Vault's !system_disk wildcard (or a stale index-sensitive test) in place, the postgres qcow2 gets grabbed by the wrong volume at boot and /var/mnt/postgres never mounts. The fix half-lands against the other branch and the exclusive-selector acceptance criterion quietly slips.
2. The removed-block/import adopt-sequence is reconciled wrong, so teardown durability fails — Terraform's destroy = false inside removed plus the later import on rebuild has subtle semantics, and a mis-step either deletes the qcow2 on destroy (the issue's hard requirement) or leaves a rebuilt worker unable to re-attach, so the manual durability check fails and the PR is reworked repeatedly.
3. Storage provisioning stalls at runtime — the local-path-provisioner doesn't actually serve the /var/mnt/postgres root (PSA privileged label misplaced, Talos local-path regression, or the disk-selector/mount race at boot), leaving the CNPG PVC Pending and postgres-1 never Ready, which only surfaces in the live smoke after all plan tests pass.

Naina: ## Overview Deploy a cluster-wide PostgreSQL via the CloudNativePG (CNPG) operator on a single-replica `Cluster`, backed by a new dedicated worker data volume that survives teardowns (un-managed per the README `removed`-block pattern). Because Talos ships no StorageClass and CNPG provisions via PVCs (not hostPath like Vault), this also deploys the Rancher local-path-provisioner — which is exactly the `local-path` StorageClass the Woodpecker PR (#37) already assumes but nothing provides today. No existing service can migrate yet (Vault/Pocket ID don't use Postgres, Woodpecker isn't deployed), so this ships the substrate + one ready-to-consume database. ## Technical Approach 1. **Add the Postgres data volume to the worker** (`worker/main.tf`) ```hcl # local: postgres_disk_name = "${var.cluster_name}-worker-1-postgres.qcow2" resource "libvirt_volume" "postgres_data" { name = local.postgres_disk_name pool = var.pool_name capacity = 53687091200 # 50 GiB — shared local-path root (CNPG 20Gi PVC + future PVC consumers) target = { format = { type = "qcow2" } } } ``` Attach as the next free virtio disk (`vdc` today, `vdd` if #34's pocket-id disk merges first — order by index, never a hard-coded letter) and add a third/fourth Talos `UserVolumeConfig`: ```yaml apiVersion: v1alpha1 kind: UserVolumeConfig name: postgres provisioning: diskSelector: match: "!system_disk && disk.size > 40u * GiB" ``` **Tighten every data-disk selector to be mutually exclusive** — vault's bare `!system_disk` (worker/main.tf) matches *any* non-system disk and will grab the new one^{[1](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/worker/main.tf?display=source#L337)}. Size bands vs. the fixed capacities (vault 20 GiB^{[2](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/worker/main.tf?display=source#L139)}, pocket-id 10 GiB if #34 lands^{[3](https://git.gloom.garden/tepichord/milner/issues/34)}, postgres 50 GiB): ```hcl # vault: 20 GiB disk diskSelector = { match = "!system_disk && disk.size > 15u * GiB && disk.size < 40u * GiB" } # pocket-id: 10 GiB disk (only if #34 merged; else add when it does) diskSelector = { match = "!system_disk && disk.size < 15u * GiB" } # postgres: 50 GiB disk diskSelector = { match = "!system_disk && disk.size > 40u * GiB" } ``` Whatever the merge order with #34, `worker/main.tf` must converge on this one exclusive set. Mount activates only at boot → apply then `terraform -chdir=worker apply -replace='libvirt_domain.worker'` (OPERATIONS.md §4e vault precedent^{[4](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/OPERATIONS.md?display=source#L184)}). 2. **Make the volume teardown-durable (the issue's hard requirement)** — per README.md "How Changes Ship", genuine persistent volumes are un-managed so `destroy` never removes them^{[5](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/README.md?display=source#L158)}: ```hcl removed { from = libvirt_volume.postgres_data lifecycle { destroy = false } } ``` The qcow2 stays in the CP-owned `talos-images` pool (external 4 TB_^unverified_ `data_root_path`)^{[6](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/control-plane/main.tf?display=source#L112)} across worker `destroy`/`cleanup.sh` and re-provisioning; a recreated worker re-attaches and re-mounts it with data intact. Exact adopt-sequence (create while managed → add `removed`, drop the declaration → later `terraform import` on rebuild) is an implement-time reconcile against Terraform 1.7 semantics^{[7](https://developer.hashicorp.com/terraform/language/block/removed)} — the acceptance criterion below pins the outcome. 3. **Create the `postgres/` Terraform root** — copy the remote-state/providers preamble from `vault/main.tf` verbatim, then three namespaces:^{[8](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/vault/main.tf?display=source#L35)} | namespace | PSA label | reason | |---|---|---| | `local-path-storage` | `privileged` | provisioner DaemonSet + helper pods need hostPath/root (Talos #10817)^{[9](https://github.com/siderolabs/talos/issues/10817)} | | `cnpg-system` | none (default) | operator is hardened non-root | | `postgres` | `restricted` | CNPG pods are non-root, hardened by default^{[10](https://cloudnative-pg.io/docs/1.30/security/)} | ```hcl resource "kubernetes_namespace_v1" "postgres" { metadata { name = "postgres" labels = { "pod-security.kubernetes.io/enforce" = "restricted" } } } ``` 4. **Deploy the local-path provisioner** (helm_release, ns `local-path-storage`)^{[11](https://github.com/orgs/rancher/packages/container/package/local-path-provisioner%2Fcharts%2Flocal-path-provisioner)}^{[12](https://github.com/rancher/local-path-provisioner/releases/tag/v0.0.37)} ```hcl resource "helm_release" "local_path_provisioner" { name = "local-path-provisioner" repository = "oci://ghcr.io/rancher/local-path-provisioner/charts" chart = "local-path-provisioner" version = "0.0.37" # image rancher/local-path-provisioner:v0.0.37 (arm64 ✓) namespace = kubernetes_namespace_v1.local_path_storage.metadata[0].name values = [file("${path.module}/values.yaml")] } ``` `postgres/values.yaml` — provision from the new qcow2 mount, keep the class named `local-path` (woodpecker's values already target it^{[13](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/woodpecker/values.yaml?display=source#L4)}), not default: ```yaml image: tag: "v0.0.37" nodeSelector: node-role: worker nodePathMap: - node: DEFAULT_PATH_FOR_NON_LISTED_NODES paths: - /var/mnt/postgres # Talos user volume from step 1 storageClass: create: true name: local-path # matches woodpecker/values.yaml assumption (#37) defaultClass: false reclaimPolicy: Delete volumeBindingMode: WaitForFirstConsumer resources: requests: { cpu: 100m, memory: 128Mi } ``` 5. **Deploy the CNPG operator** (helm_release, ns `cnpg-system`) — chart `cloudnative-pg` 0.29.0 (operator 1.30.0)^{[14](https://github.com/cloudnative-pg/charts/blob/cloudnative-pg-v0.29.0/charts/cloudnative-pg/Chart.yaml)} from `https://cloudnative-pg.github.io/charts`; K8s v1.34 is in its supported matrix^{[15](https://cloudnative-pg.io/docs/1.30/supported_releases/)} (Milner runs v1.34.0^{[16](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/control-plane/terraform.tfvars?display=source#L10)}). `values.yaml` sets `config.clusterWide: true` (default^{[17](https://github.com/cloudnative-pg/charts/blob/main/charts/cloudnative-pg/values.yaml)} — one operator serves future Clusters in other roots) and `resources.requests: {cpu: 100m, memory: 200Mi}`. 6. **Declare the single-instance `Cluster`** (kubernetes_manifest, ns `postgres`, depends_on the operator release so CRDs exist^{[18](https://github.com/cloudnative-pg/charts/blob/main/charts/cloudnative-pg/values.yaml)}) ```yaml apiVersion: postgresql.cnpg.io/v1 kind: Cluster metadata: name: postgres namespace: postgres spec: instances: 1 nodeSelector: node-role: worker # CNPG 1.30 default operand image is multi-arch (arm64 ✓); no imageName pin needed bootstrap: initdb: database: app # app db + owner, creds land in the generated `postgres-app` secret owner: app storage: size: 20Gi storageClass: local-path ``` Cluster-wide reachability is CNPG's auto-created ClusterIP service^{[19](https://cloudnative-pg.io/docs/1.30/service_management/)} — no Ingress, no ProxyGroup: ``` postgres-rw.postgres.svc.cluster.local:5432 # primary; apps use <cluster>-rw, never pod IPs kubectl -n postgres get secret postgres-app -o jsonpath='{.data.password}' | base64 -d ``` 7. **Wire tests + docs** — `tests/test_static.py`: add `postgres/` to the `terraform fmt` dirs, `postgres/values.yaml` to the yamllint list; add `test_terraform_test_postgres()` to `tests/test_terraform.py` if the Woodpecker PR has landed it by implement time. Update README.md workload list, ARCHITECTURE.md topology/service-table/storage-distribution, AGENTS.md repo map, and add an OPERATIONS.md Postgres section (connect creds, worker `-replace` note, emergency `pg_dump`). ## Implementation Details - **Files created:** `postgres/main.tf`, `postgres/values.yaml`, `postgres/README.md`, `postgres/.gitignore` (copy of vault's), `postgres/tests/plan.tftest.hcl` + `postgres/tests/fixtures/cp.tfstate` (copy of vault's fixture). - **Files modified:** `worker/main.tf` (volume, disk attach, UserVolumeConfig, selector tightening, `removed` block), `worker/tests/plan.tftest.hcl`, `tests/test_static.py`, possibly `tests/test_terraform.py`, `README.md`, `ARCHITECTURE.md`, `AGENTS.md`, `OPERATIONS.md`. - **Existing worker tests that break and must be updated** (indices shift): `user_volume_config_is_in_patches` (asserts 2 patches + vault `match == "!system_disk"`), `worker_domain_cdrom_is_scsi` (`disks[2]` cdrom → `disks[3]`), `installer_iso_ejects_when_disabled` (post-eject disk count 2 → 3)^{[20](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/worker/tests/plan.tftest.hcl?display=source#L326)}. Add runs for the postgres volume (capacity/format/name), the vdc/vdd attach, and the `postgres` UserVolumeConfig + selector exclusivity. - **Dependencies (pinned):** CNPG operator chart `cloudnative-pg` `0.29.0` from `https://cloudnative-pg.github.io/charts` (operator 1.30.0, supports K8s 1.34/1.35/1.36)^{[21](https://github.com/cloudnative-pg/cloudnative-pg/releases/tag/v1.30.0)}; local-path-provisioner chart `0.0.37` (OCI `ghcr.io/rancher/local-path-provisioner/charts`, image `rancher/local-path-provisioner:v0.0.37`, arm64 multi-arch verified); CNPG default operand `ghcr.io/cloudnative-pg/postgresql:18.4` (multi-arch, not pinned). - **Constraints:** arm64-only images (both new images verified); no new dev-shell tools — verify with `psql` via `kubectl exec` into the CNPG pod (flake.nix unchanged^{[22](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/flake.nix?display=source)}); `postgres` namespace CRD ordering handled by `depends_on` on the operator release; secrets stay in the CNPG-generated `postgres-app` Secret^{[23](https://cloudnative-pg.io/docs/1.30/applications/)} (no VSO yet, no hand-run bootstrap). - **Out-of-scope notes:** - No existing-service migration — nothing deployed today uses Postgres (Vault: no; Pocket ID #34: SQLite by design; Woodpecker #37: not merged, SQLite default). The `postgres-rw` service + `postgres-app` secret are the contract a future app (e.g. evolved-npcs) consumes; Woodpecker can flip to `WOODPECKER_DATABASE_*` postgres env later if wanted. - No Tailscale/external exposure — the issue asks for cluster-wide, which ClusterIP satisfies; pgAdmin-style tailnet access is a later `Service type=LoadBalancer` + `loadBalancerClass: tailscale` add-on. - The `local-path` StorageClass is cluster-shared substrate: woodpecker's PVCs will bind to the same `/var/mnt/postgres` volume (50 GiB covers CNPG 20Gi + known ~13 GiB consumers with headroom; a second provisioner root/volume is a future storage issue). - No ScheduledBackup/WAL archiving (needs an S3-compatible endpoint; local-path can't do volume snapshots) — the durable qcow2 is the backup story, `pg_dump` is the emergency runbook. - Vault's own 20 GiB qcow2 has the *same* teardown exposure (no `removed` block today^{[24](https://git.gloom.garden/tepichord/milner/src/commit/2b21c2e237670b7d060c1702d05487e609dcfedc/README.md?display=source#L154)}) — fixing it (and pocket-id's, post-#34) is repo-wide follow-up, not this issue. - CNPG `enableSuperuserAccess` stays default `false`. ## Acceptance Criteria - [ ] `worker/main.tf` shows the 50 GiB `postgres` qcow2 + virtio attach + `postgres` UserVolumeConfig (mount `/var/mnt/postgres`); all data-disk selectors (vault, postgres, + pocket-id if merged) are mutually exclusive; updated `terraform -chdir=worker test` is green. - [ ] Teardown durability holds: `terraform -chdir=worker destroy` / `worker/cleanup.sh` leaves `milner-worker-1-postgres.qcow2` and its data in the pool; re-provisioning the worker re-attaches and the data is still there (manual verify). - [ ] `postgres/` root plan tests green: `local-path-storage` privileged + `postgres` restricted PSA labels, provisioner helm release (chart/version/root path), operator helm release (chart/repo/version), Cluster CR fields (instances 1, `storageClass: local-path`, `20Gi`, `initdb` app/app, `node-role: worker`). - [ ] Live: `postgres-1` pod Ready on the worker, PVC Bound on `local-path`, `postgres-app` secret exists; a throwaway pod in another namespace connects to `postgres-rw.postgres.svc.cluster.local:5432`, creates/drops a table, and the data survives a pod restart and a worker `-replace`. - [ ] Updated pytest suite green (`terraform fmt` incl. `postgres/`, yamllint on `postgres/values.yaml`); README/ARCHITECTURE/AGENTS/OPERATIONS updated and cross-references consistent. ## Testing Plan - Static: `python -m pytest tests/` — terraform fmt now includes `postgres/`; yamllint covers `postgres/values.yaml`; ruff unchanged. - Plan assertions: `terraform -chdir=postgres test` — namespace PSA labels, helm chart/repo/version + provisioner `nodePathMap`, Cluster manifest fields (mock kubernetes/helm providers, fixture cp.tfstate copied from vault). - Worker root: `terraform -chdir=worker test` — new postgres volume/attach/UserVolumeConfig runs; updated index-sensitive runs (patch count, disk positions, eject disk count); selector-exclusivity assertion across all `UserVolumeConfig`s. - Live smoke (manual, after apply): `kubectl get pods,pvc -n postgres`; `kubectl exec -n postgres postgres-1 -- psql -U app -d app -c 'create table t(n int); insert into t values (1);'`; connect from a second namespace via `postgres-rw`; `kubectl -n postgres delete pod postgres-1` (operator recreates, data intact); worker `-replace` then re-verify; OPERATIONS.md `pg_dump`/restore dry-run. ## Related Issues/PRs - [Pod - Hashicorp Vault (#33)](https://git.gloom.garden/tepichord/milner/pulls/33) — merged; the namespace/helm/worker-volume pattern this mirrors. - [Create Pocket ID service on Milner (#34)](https://git.gloom.garden/tepichord/milner/issues/34) — sibling issue editing the same `worker/main.tf` disk selectors; selectors must converge on one mutually exclusive set. - [Pod - Woodpecker CI (#37)](https://git.gloom.garden/tepichord/milner/pulls/37) — its values require the `local-path` StorageClass this issue provisions. - [Create Annia pod (#25)](https://git.gloom.garden/tepichord/milner/issues/25) / [Letta pod (#24)](https://git.gloom.garden/tepichord/milner/issues/24) — likely first Postgres consumers. ## Research Findings - **CNPG is the recommended operator; Bitnami is dead for new free deploys** — Broadcom retired the free `docker.io/bitnami` catalog (Sep 2025), so the Bitnami Helm chart's images no longer pull freely. CNPG (CNCF, EDB-originated) is the actively developed successor to Zalando/Bitnami and the de-facto standard. - **CNPG needs a StorageClass; it cannot hostPath like Vault** — it provisions one PVC per instance and officially discourages static PVs. On Talos the supported equivalent is local-path-provisioner rooted at a `UserVolumeConfig` mount (`/var/mnt/...`), with the provisioner's namespace labeled `privileged` (Talos issues #10817, #11251). CNPG pods themselves are hardened non-root and satisfy PSA `restricted`. - **arm64 + version matrix verified** — operator image `cloudnative-pg:1.30.0` and operand `postgresql:18.4` are linux/amd64 + linux/arm64; CNPG 1.30.x supports K8s 1.34 (Milner runs v1.34.0). local-path-provisioner v0.0.37 image and chart are arm64 multi-arch (chart OCI at `ghcr.io/rancher/local-path-provisioner/charts`). - **Credentials & exposure** — CNPG self-generates a `kubernetes.io/basic-auth` `<cluster>-app` Secret and auto-creates `<cluster>-rw/-ro/-r` ClusterIP services on 5432; apps should use the service names. Vault/VSO sync is a later integration (VSO not yet deployed). External TCP exposure is a `Service type=LoadBalancer` + `loadBalancerClass: tailscale` add-on, not Ingress. - **Backups** — CNPG has none by default; in-tree Barman object-store support is deprecated (1.31 removal) for the plugin, and volume-snapshot backups need a CSI driver local-path lacks (plus a single-instance hang bug #2948). The durable qcow2 + emergency `pg_dump` is the right homelab tier; ScheduledBackup/PITR waits until an S3-compatible endpoint exists. ## Pre-Mortem 1. **#34's concurrent worker/main.tf changes never converge on exclusive disk selectors** — both issues edit the same volume/UserVolumeConfig blocks, and if merge order leaves Vault's `!system_disk` wildcard (or a stale index-sensitive test) in place, the postgres qcow2 gets grabbed by the wrong volume at boot and `/var/mnt/postgres` never mounts. The fix half-lands against the other branch and the exclusive-selector acceptance criterion quietly slips. 2. **The `removed`-block/import adopt-sequence is reconciled wrong, so teardown durability fails** — Terraform's `destroy = false` inside `removed` plus the later `import` on rebuild has subtle semantics, and a mis-step either deletes the qcow2 on `destroy` (the issue's hard requirement) or leaves a rebuilt worker unable to re-attach, so the manual durability check fails and the PR is reworked repeatedly. 3. **Storage provisioning stalls at runtime** — the local-path-provisioner doesn't actually serve the `/var/mnt/postgres` root (PSA `privileged` label misplaced, Talos local-path regression, or the disk-selector/mount race at boot), leaving the CNPG PVC Pending and `postgres-1` never Ready, which only surfaces in the live smoke after all plan tests pass.

tepichord added

status

implement

and removed

status

planning

labels 2026-09-09 16:26:50 -07:00

tepichord referenced this issue from a commit 2026-09-09 18:47:34 -07:00

feat(postgres): add postgres/ Terraform root with CNPG operator and local-path provisioner

tepichord referenced this issue from a commit 2026-09-09 18:47:34 -07:00

test(postgres): add plan tests for postgres root

tepichord referenced this issue from a commit 2026-09-09 18:47:34 -07:00

feat(worker): add postgres 50 GiB data volume, vdc attach, and UserVolumeConfig

tepichord referenced this issue from a commit 2026-09-09 18:47:34 -07:00

test(worker): update tests for postgres volume, disk, and UserVolumeConfig

tepichord referenced this issue from a commit 2026-09-09 18:47:34 -07:00

test: add postgres to static and terraform test suites

tepichord referenced this issue from a commit 2026-09-09 18:47:34 -07:00

docs: add Postgres to README, ARCHITECTURE, AGENTS, and OPERATIONS

tepichord referenced this issue from a commit 2026-09-09 18:47:34 -07:00

docs(operations): fix Vault subsection numbering and minor capitalization

tepichord referenced this issue from a pull request that will close it, 2026-09-09 18:47:40 -07:00

Add Postgres Database to Milner #40

tepichord added

status

reviewing

and removed

status

implement

labels 2026-09-09 18:47:41 -07:00

tepichord 2026-09-18 19:08:07 -07:00

• closed this issue
• added
  status
  document
  and removed
  status
  reviewing
  labels

tepichord referenced this issue from a commit 2026-09-19 14:47:47 -07:00

feat(pocket-id): move the identity provider's state into Postgres (#35)

tepichord referenced this issue 2026-09-19 14:49:16 -07:00

feat(pocket-id): move the identity provider's state into Postgres (#35) #72

tepichord referenced this issue from a commit 2026-09-19 15:05:37 -07:00

Merge pull request 'feat(pocket-id): move the identity provider's state into Postgres (#35)' (#72) from feat/35-pocket-id-postgres into main

tepichord referenced this issue from a commit 2026-09-19 15:50:03 -07:00

docs(pocket-id): count tables not views, and name the CLI's mount prefix (#35)

tepichord referenced this issue from a commit 2026-09-20 00:16:30 -07:00

docs(preview): update one console secret without rewriting all three (#35)

tepichord referenced this issue from a commit 2026-09-20 00:16:30 -07:00

docs(pocket-id): record what a client actually holds, not just its callbacks (#35)

tepichord referenced this issue from a commit 2026-09-20 00:16:30 -07:00

docs(pocket-id): the CLI login cannot reach a headless control plane (#35)

tepichord referenced this issue from a commit 2026-09-20 00:16:30 -07:00

docs(headlamp): a secret change needs a restart, and the plan prints the old value (#35)

tepichord referenced this issue from a commit 2026-09-20 00:16:30 -07:00

docs(pocket-id): verify a client secret against the token endpoint (#35)

tepichord referenced this issue from a commit 2026-09-20 00:16:30 -07:00

docs(woodpecker): where a PAT comes from, and what a rebuilt database does to it (#35)

tepichord referenced this issue from a commit 2026-09-20 00:16:30 -07:00

docs(woodpecker): record the token and webhook copies the cut-over stranded (#35)

Sign in to join this conversation.

No Branch/Tag specified

Branches Tags

main

feat/109-nix-base

feat/109-layered-nix-store

feat/110-zot-push-credential

feat/90-annia-pause-lever-oz-console

feat/87-tailscale-egress-from-cluster

chore/ci-step-resources

docs/vault-secret-prompt

feat/42-support-backups-persistent-data

feat/41-integrate-restic-tfstate-backup

feat/3-setting-up-agent-development

kvm-kubernetes-setup

No results found.

Labels

Clear labels   

blocked

  

bug

Something is not working

  

duplicate

This issue or pull request already exists

  

effort

high

  

effort

low

  

effort

medium

  

effort

minimal

  

enhancement

New feature

  

help wanted

Need some help

  

invalid

Something is wrong

  

pr

feedback

Used for PRs, indicates that feedback has been given and agent needs to make changes

  

pr

reviewing

Used for PRs, indicates that a human is waiting to review the pull request, no action needed by the agent

  

question

More information is needed

  

status

backlog

Ignore, no work needed yet, human will assign this work by changing to status/planning

  

status

document

Record findings, create flashcards, and document knowledge in knowledgebase for the future

  

status

done

Completed when PR is merged, no more work needed.

  

status

implement

Make the code changes. Should be done by a faster and cheaper model

  

status

pending

Give human a chance to review planning details

  

status

planning

Ready to begin. Fill out implementation details and things like acceptance criteria, etc. A smarter thoughtful llm should be delegated for this

  

status

reviewing

PR is created and feedback loop begins between human and agent

  

waiting

  

wontfix

This won't be fixed

No labels

blocked

bug

duplicate

effort

high

effort

low

effort

medium

effort

minimal

enhancement

help wanted

invalid

pr

feedback

pr

reviewing

question

status

backlog

status

document

status

done

status

implement

status

pending

status

planning

status

reviewing

waiting

wontfix

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

MVP

Assignees

Clear assignees

No assignees

1 participant

Notifications

Subscribe

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

tepichord/milner#35

No description provided.