hermes-skills/smart-home/smart-home-automation/references/influxdb-downsampling.md

InfluxDB Downsampling: HA Hot → Archive Pipeline

Architecture

Home Assistant → CT109 (hot, InfluxDB v2, 90-day retention)
                     ↓ hourly task
                CT134 (archive, InfluxDB v2, 5-year retention, 5m aggregates)

• CT109 (10.0.30.109:8086): Hot bucket ha_hot, 90-day RP, ~53 MB/day, ~76 shards
• CT134 (10.0.30.110:8086): Archive bucket ha_archive_5y_5m, 5-year RP, 5-minute mean aggregates

Critical Pitfall: aggregateWindow(mean) Fails on String Fields

Home Assistant's InfluxDB v2 integration writes string fields alongside numeric fields — icon_str, state_class_str, attribution_str, etc. The Flux aggregateWindow(fn: mean) operator cannot aggregate strings and crashes with:

unsupported input type for mean aggregate: string

Fix: Filter to Numeric Fields Before Aggregation

Add |> filter(fn: (r) => r._field == "value") BEFORE aggregateWindow():

from(bucket: "ha_hot")
  |> range(start: -91d, stop: -90d)
  |> filter(fn: (r) => r._measurement == "kWh" or r._measurement == "W" or r._measurement == "°C")
  |> filter(fn: (r) => r._field == "value")   // ← CRITICAL: only numeric fields
  |> aggregateWindow(every: 5m, fn: mean, createEmpty: false)
  |> to(bucket: "ha_archive_5y_5m", host: "http://10.0.30.110:8086")

Why Tasks Appear "Successful" But Produce No Data

When the hot bucket is younger than the task's range window (e.g. bucket is 76 days old, task queries -91d to -90d), the range returns empty. The task succeeds (no error, no data) but the archive stays at skeleton size (368 KB). This is NOT a sign of success — it means the pipeline hasn't started flowing yet.

Diagnostic: Check bucket age vs task range. If bucket_age < task_range_start, the task is running on empty windows.

Task Management via InfluxDB v2 CLI

# List tasks
influx task list --host http://10.0.30.109:8086 --token TOKEN

# Update a task (pipe Flux to stdin)
influx task update --id TASK_ID --host http://10.0.30.109:8086 --token TOKEN << 'EOF'
// Flux script here
EOF

# Run a task manually (retry)
influx task run create --id TASK_ID --host http://10.0.30.109:8086 --token TOKEN

# Check task runs (look for failed runs)
influx task run list --id TASK_ID --host http://10.0.30.109:8086 --token TOKEN

# Query archive bucket to verify data arrived
influx query 'from(bucket:"ha_archive_5y_5m") |> range(start: -1h) |> count()' \
  --host http://10.0.30.110:8086 --token TOKEN

Accessing InfluxDB CLI on CTs

InfluxDB v2 is installed as a systemd service on the CTs. Use SSH via Proxmox key:

# SSH to the CT (via PVE host or directly if SSH is enabled)
ssh -i ~/.ssh/id_ed25519_proxmox root@10.0.30.109 'influx task list --token TOKEN'

# Or via pct exec from PVE node
ssh -i ~/.ssh/id_ed25519_proxmox root@PVE_NODE "pct exec 109 -- influx task list --token TOKEN"

Task Reactivation (Verified 2026-07-14)

The downsampling task archive_5m_to_hdd_90d on CT109 was failing silently since July 11 with unsupported input type for mean aggregate: string. After applying the _field == "value" filter fix:

• Manual retry run: success ✅
• Test query (last hour, value fields only): 3,102 data points flowing correctly
• Archive bucket received first rows from the test run

The hot bucket is ~76 days old (76 shards). The task queries -91d to -90d — this window is currently empty. Once the bucket reaches 90 days (~14 days from now), the task will automatically begin transferring real data to the archive. Until then, successful runs produce no data — this is expected, not a failure.

K8s Migration Plan (Conceptualized 2026-07-14)

Consolidate CT109 (hot) + CT134 (archive) into a single InfluxDB v2.7 instance on K8s:

Key insight: influx restore --full preserves tokens. After restoring CT109's backup to K8s, the existing HA token works on K8s — HA only needs a URL change (10.0.30.109 → 10.0.30.204), no token rotation.

8 phases:

1. Deploy empty InfluxDB on K8s (StatefulSet, init mode, Cilium VIP .204)
2. Backup CT109 (influx backup)
3. Restore to K8s (influx restore --full — brings org, buckets, tokens, data)
4. Restore CT134 archive bucket separately (cross-org may need --bucket flag)
5. Create local downsampling task (no more remote CT109→CT134 call)
6. Cutover HA: change InfluxDB URL from 10.0.30.109 to 10.0.30.204
7. Verify data counts, task runs, HA writes
8. Decommission CT109 + CT134

Manifests partially created in clusters/main/influxdb/:

• namespace.yaml (ExternalSecret for admin creds)
• statefulset.yaml (InfluxDB 2.7, init mode, 15Gi PVC on ceph-hdd-replica)
• service.yaml (LoadBalancer, Cilium L2 VIP 10.0.30.204)
• init-configmap.yaml + init-job.yaml (PostSync hook: create archive bucket + task)

Pitfall: 1Password CLI not authenticated on Hermes host. OP_SERVICE_ACCOUNT_TOKEN is set but op account list returns []. Items must be created via the K8s service account token (see rke2-cluster-administration §5.3) or 1Password Web UI.

Storage Assessment

CT/Host	Role	Effective Data	Disk	Shards	Rate
CT109	hot (90d)	4.0 GB	30 GB	76	~53 MB/day
CT134	archive (5y)	368 KB → growing	50 GB	1 (empty)	depends on task
10.0.30.100	old v1 (deleted)	0 (data lost)	—	—	—

Effective data is far smaller than allocated disk. A K8s migration with a 15Gi PVC suffices for both buckets combined.

10.0.30.100 Investigation (2026-07-14)

Searched for old InfluxDB v1 data on Docker host 10.0.30.100. Found Portainer Stack 14 (monitoring stack) contained InfluxDB with cAdvisor/collectd data — NOT Home Assistant data. The data volume 506f355... was completely deleted (not in docker volume ls nor on disk). Only the v1 config file (/var/lib/docker/influx_cfg/influxdb.conf, dated March 2017) and an empty config volume remained. Years of v1 data were lost when the container was removed.

Portainer stack file location: /var/lib/docker/volumes/portainer_data/_data/compose/<STACK_ID>/v<N>/docker-compose.yml — versioned subdirectories, latest version has the active compose file.