84 lines
3.3 KiB
Python
84 lines
3.3 KiB
Python
"""
|
|
One-shot backfill: inserts 30 minutes of historical readings for all new racks.
|
|
Only inserts for rack_ids that have zero existing readings — safe to run multiple times.
|
|
"""
|
|
import asyncio
|
|
import math
|
|
import os
|
|
import random
|
|
from datetime import datetime, timezone, timedelta
|
|
|
|
import asyncpg
|
|
|
|
DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://dcim:dcim_pass@localhost:5432/dcim")
|
|
SEED_MINUTES = 30
|
|
INTERVAL_MINS = 5
|
|
SITE_ID = "sg-01"
|
|
|
|
ROOMS = [
|
|
{"room_id": "hall-a", "racks": [f"SG1A01.{i:02d}" for i in range(1, 21)] + [f"SG1A02.{i:02d}" for i in range(1, 21)]},
|
|
{"room_id": "hall-b", "racks": [f"SG1B01.{i:02d}" for i in range(1, 21)] + [f"SG1B02.{i:02d}" for i in range(1, 21)]},
|
|
]
|
|
|
|
|
|
async def backfill() -> None:
|
|
url = DATABASE_URL.replace("postgresql+asyncpg://", "postgresql://")
|
|
print("Backfill: connecting to database...")
|
|
conn = await asyncpg.connect(url)
|
|
|
|
try:
|
|
rows: list[tuple] = []
|
|
now = datetime.now(timezone.utc)
|
|
|
|
for room in ROOMS:
|
|
room_id = room["room_id"]
|
|
for rack_id in room["racks"]:
|
|
# Skip if this rack already has readings
|
|
count = await conn.fetchval(
|
|
"SELECT COUNT(*) FROM readings WHERE site_id = $1 AND rack_id = $2",
|
|
SITE_ID, rack_id,
|
|
)
|
|
if count > 0:
|
|
print(f" Skipping {rack_id} ({count} rows exist)")
|
|
continue
|
|
|
|
num = int(rack_id[-2:])
|
|
base_temp = 21.5 + num * 0.15
|
|
base_load = 2.0 + (num % 5) * 0.8
|
|
base_id = f"{SITE_ID}/{room_id}/{rack_id}"
|
|
|
|
for minutes_ago in range(SEED_MINUTES, -1, -INTERVAL_MINS):
|
|
t = now - timedelta(minutes=minutes_ago)
|
|
hour = t.hour
|
|
day_factor = 1.0 + 0.04 * math.sin(math.pi * (hour - 6) / 12)
|
|
biz_factor = (1.0 + 0.25 * math.sin(math.pi * max(0, hour - 8) / 12)
|
|
if 8 <= hour <= 20 else 0.85)
|
|
|
|
temp = base_temp * day_factor + random.gauss(0, 0.2)
|
|
humidity = 44.0 + random.gauss(0, 1.0)
|
|
load = base_load * biz_factor + random.gauss(0, 0.1)
|
|
|
|
rows += [
|
|
(t, f"{base_id}/temperature", "temperature", SITE_ID, room_id, rack_id, round(temp, 2), "°C"),
|
|
(t, f"{base_id}/humidity", "humidity", SITE_ID, room_id, rack_id, round(humidity, 1), "%"),
|
|
(t, f"{base_id}/power_kw", "power_kw", SITE_ID, room_id, rack_id, round(max(0.5, load), 2), "kW"),
|
|
]
|
|
print(f" Queued {rack_id}")
|
|
|
|
if not rows:
|
|
print("Backfill: nothing to insert — all racks already have data.")
|
|
return
|
|
|
|
await conn.executemany("""
|
|
INSERT INTO readings
|
|
(recorded_at, sensor_id, sensor_type, site_id, room_id, rack_id, value, unit)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
|
|
""", rows)
|
|
print(f"Backfill: inserted {len(rows)} readings across {len(rows) // (SEED_MINUTES // INTERVAL_MINS + 1) // 3} racks. Done.")
|
|
|
|
finally:
|
|
await conn.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
asyncio.run(backfill())
|