Skip to content

Example: Postgres state store

Credentials in Secrets only

Never put database passwords on the KollectDatabaseSink CR. The operator reads DSN keys from a referenced Secret (dsn, url, connectionString, or DATABASE_URL).

This walkthrough sets up a relational state-of-record sink for namespace inventory exports. Postgres is the default MVP path in samples (postgres-inventory-demo) and the recommended backend for portals and SQL analytics (ADR-0402, ADR-0201).

Pair with Deployment inventory for the full Profile → Target → Inventory pipeline, or apply only the sink + secret pieces here.

Overview

flowchart LR
  Inv[KollectInventory]
  Sink[KollectDatabaseSink<br/>Postgres]
  Secret[(Secret<br/>dsn / url)]
  PG[(Postgres<br/>inventory_items)]

  Inv --> Sink
  Sink --> Secret
  Sink -->|upsert + delete recon| PG

Step 1 — DSN secret

KollectDatabaseSink.spec.postgres.databaseRef points at a Secret containing the connection string. The operator reads dsn, url, connectionString, or DATABASE_URL — never inline credentials on the sink CR.

Create the Secret in the namespace referenced by databaseRef (sample uses kollect-system):

apiVersion: v1
kind: Secret
metadata:
  name: inventory-postgres-dsn
  namespace: kollect-system
type: Opaque
stringData:
  dsn: postgres://kollect:example@postgres.kollect-system.svc:5432/inventory?sslmode=disable

kubectl apply -f - <<'EOF'
apiVersion: v1
kind: Secret
metadata:
  name: inventory-postgres-dsn
  namespace: kollect-system
type: Opaque
stringData:
  dsn: postgres://kollect:example@postgres.kollect-system.svc:5432/inventory?sslmode=disable
EOF

For local kind dev, apply the disposable Postgres manifest (config/samples/dev/postgres.yaml — see the Quick start) or point at an external instance. Never commit real credentials to Git.

Step 2 — KollectDatabaseSink

Sample: config/samples/kollect_v1alpha1_kollectdatabasesink.yaml

apiVersion: kollect.dev/v1alpha1
kind: KollectDatabaseSink
metadata:
  name: postgres-inventory-demo
  namespace: default
spec:
  type: postgres
  cluster: kind-kollect-dev
  connectionTest: true
  provisioning:
    mode: ensure         # existing never touches DDL (bring your own schema)
  options:
    statement_timeout: "30000"
  postgres:
    databaseRef:
      name: inventory-postgres-dsn
      namespace: kollect-system
    schema: public
    table: inventory_items
Field Role
spec.cluster Labels rows for multi-cluster fan-in
spec.postgres.schema / table Target table; DDL created on first export with provisioning.mode: ensure
spec.provisioning.mode ensure (default) creates the table; existing never issues DDL (ADR-0416)
spec.connectionTest Samples/CI only — sets ConnectionVerified

Step 3 — Wire inventory

Reference the sink from KollectInventory in the same namespace:

spec:
  databaseSinkRefs:
    - postgres-inventory-demo

kubectl apply -k config/samples/
kubectl wait --for=condition=ConnectionVerified kollectdatabasesink/postgres-inventory-demo \
  -n default --timeout=60s

Delete reconciliation

Why deletes matter

Postgres is a relational SoR — stale rows must disappear when objects leave the snapshot.

After each export the backend upserts current rows and deletes rows for that inventory + cluster that are not in the snapshot (ADR-0401, ADR-0402).

Integration coverage: internal/sink/postgres/export_integration_test.go.

Troubleshooting

Symptom Likely cause
ConnectionVerified=False Missing Secret or wrong databaseRef namespace
SecretResolveFailed Secret lacks dsn / url key