Backups and restore

Added in 4.12.0. internal/backup takes logical, table-data-only snapshots of the historian and restores them. Off by default (HOTLOOP_BACKUP_ENABLED): a deployment that has not configured a backup volume should not have this process quietly writing into an ephemeral container filesystem.

What this is

A snapshot of every row in every user-data table, taken over the same pgx wire-protocol connection internal/store already uses, with PostgreSQL’s native COPY table TO STDOUT / COPY table FROM STDIN. A backup is a directory: one file per table plus a small manifest.json (HotLoop version, the schema migrations the database had applied, timestamp, and each table’s row count and columns with their types).

What this is not

• Not pg_dump/pg_restore. The Gateway’s runtime image (gcr.io/distroless/static-debian12:nonroot) is deliberately a static binary and nothing else, no shell, no package manager, no external binaries of any kind. That is a design decision this feature does not get to override; see the Containerfile’s own header comment. Everything here goes over the same database connection the app already holds.
• Not a schema backup. internal/store/migrations, applied automatically by store.Migrate on every start (HOTLOOP_DB_MIGRATE, on by default), already reproduce the schema from nothing. A backup only ever captures table data, restoring one always assumes the schema will be rebuilt by migrations first, not by anything this package writes.
• Not point-in-time recovery. There is no WAL archiving and no continuous replication here. A backup is a snapshot at the moment it ran; anything written after that moment and before the next backup is not covered.
• Not off-box storage. The backup directory (HOTLOOP_BACKUP_DIR, meant to be backed by the backup.persistence PVC this chart provisions) is still a single volume in the same cluster. Getting backups onto genuinely durable, replicated, or offline storage, copying the volume to object storage, snapshotting the PVC, whatever an operator’s actual infrastructure provides, is the operator’s job, the same way this project already treats durable storage as infrastructure’s responsibility rather than something the app reinvents a multi-cloud backend adapter for.

Protect the backup files

Backup files are sensitive. The users.copy file holds bcrypt password hashes, and sessions.copy and write_audit.copy hold session records and the write audit trail. Files are created with mode 0640 (and the backup directory 0750), and a pre-existing file is an error rather than overwritten, but that is a floor, not a substitute for protecting the volume: keep the backup volume on encrypted-at-rest storage, restrict who and what can read it, and apply the same care to any off-box copy an operator makes.

The devices.copy file holds each device’s address and protocol configuration as they are stored, which includes the credentials the drivers log in with. How readable they are depends on the key, below. Since 4.15.1 the API hides every device credential from every role, admin included; a backup file is not behind the API. See AUTH.md.

Device credentials and the secret key

A backup is the file that gets copied to a laptop, a share and a ticket. So the credentials in it are encrypted, with a key that is never in the database and never in a backup.

• With HOTLOOP_SECRET_KEY_FILE set (the chart sets it, with a key it generates on install and keeps across upgrades and uninstalls), every keyed credential (the password of OPC UA, MQTT, HTTP, MTConnect and UniFi devices, HTTP and MTConnect header values such as API keys, the password of each other MQTT broker discovery listens to, and the password of each historian target) is encrypted in the database, and devices.copy, mqtt_sources.copy and historian_targets.copy hold the ciphertext. The manifest lists the key it was encrypted with as secretKeyIds: an id, never the key.
• Without it, those credentials are in the database and in devices.copy in the clear, and HotLoop says so in its startup log. UniFi consoles, MQTT brokers with a password and historian targets with a password can’t be saved at all without a key. Set one and restart: everything already stored is encrypted at that start.
• Either way, a credential written into a URL (mqtt://user:pass@host, a ?token= query value) or into free text such as an HTTP body stays in the clear. Put it in the password option instead.

Restoring needs the same key. --restore-from checks the manifest before it loads a row: a backup encrypted with a key the restoring HotLoop does not have is refused, and the error names the key it needs. Restore with that key file mounted. If the key is gone for good, add --restore-keep-unreadable-credentials: everything else comes back, and each device whose credential can’t be opened stays down, saying which key it needs, until somebody enters the credential again. A backup taken before there was a key restores anywhere, and its credentials are encrypted as soon as the restore finishes.

Keep the key somewhere the backup isn’t. A key stored next to its backups protects nothing. With the chart it’s the <release>-secret-key Secret; back that up the way you back up the rest of the cluster’s secrets, separately.

Why COPY table TO STDOUT alone does not work

The obvious approach, issue COPY table_name TO STDOUT for each table and stream the result to a file, was tried against a real timescale/timescaledb:2.29.1-pg16 instance while building this feature, and it is actively wrong, not just less general:

• Against tag_history when it is a TimescaleDB hypertable, that exact statement does not error. It answers with a NOTICE (“hypertable data are in the chunks, no data will be copied”) and copies zero rows. A backup built this way “succeeds” and is silently empty for exactly the table this project cares most about not losing.
• Against tag_history when it is the native-partitioning fallback (a plain table declared PARTITION BY RANGE, used when TimescaleDB is not available), the same statement fails outright: ERROR: cannot copy from partitioned table.

Postgres’s own hint in the second case is the fix internal/backup uses for every table, unconditionally, hypertable or not, partitioned or not:

COPY (SELECT * FROM tag_history) TO STDOUT WITH (FORMAT binary)

Verified against the same instance: this form reads every row across all of a hypertable’s chunks, or all of a partitioned table’s partitions, transparently and correctly, no need to enumerate chunk or partition tables by name. This codebase uses neither compressed hypertables nor continuous aggregates (see internal/store/migrations and internal/store/migrate.go, tag_history is a plain hypertable or a plain range-partitioned table, nothing more exotic), so neither of those features’ additional COPY caveats apply here.

COPY table FROM STDIN, used for restore, needs no such rewrite: it always names a table, never a query, and both TimescaleDB and Postgres’s native partitioning route an incoming row to the correct chunk or partition exactly as an ordinary INSERT would, also verified against the same instance.

What is backed up, and in what order

Every user-data table, in foreign-key dependency order (a table always comes after every table its own foreign keys point to):

devices → tags → tag_latest → alarm_definitions → alarm_state →
alarm_events → automations → automation_runs → blueprints → scripts → script_runs →
recipes → recipe_versions → recipe_applications → events → write_audit →
settings → users → sessions → fleet_members → report_definitions →
report_runs → entity_state_log → tag_history

(Abridged; backup.Tables is the whole list, the entity and equipment tables and entity_last_state included.) The logbook’s entity_state_log is partitioned by month in both history modes and goes just before tag_history, the only table larger. A restore loads its rows before any of its monthly partitions exist, so they land in its default partition. The logbook’s maintenance, when a recording Gateway starts, makes last month’s and this month’s partitions around the rows that belong in them, and deletes older rows from the default partition as they pass the retention, so the retention reaches every restored row. See LOGBOOK.md.

Scripts come back with their history. A run the backup caught in the middle comes back running, and the Gateway’s first start marks it cancelled, interrupted by restart, the same as a run a crash cut off: nothing resumes a run. See SCRIPTS.md.

Recipes come back with every version and every application. An application the backup caught halfway comes back applying, and the Gateway’s first start marks it failed, pointing at its call id in the write audit: nothing resumes one, and nothing undoes one. See RECIPES.md.

schema_migrations is deliberately excluded: store.Migrate rebuilds it, and every table’s DDL, from the embedded migrations. It is the one entry in backup.ExcludedTables, which records why each excluded table is excluded.

The list is kept by hand, which is how a table can be added by a migration and silently left out of every backup afterward. Two tests guard against it, and both run against a real database in both storage modes. TestEveryTableIsBackedUpOrExcluded reads the tables that exist from information_schema and fails if any is in neither Tables nor ExcludedTables, naming it. TestTablesAreInForeignKeyOrder reads the foreign keys from the database and fails if a table is listed before one it references. The scheduled-reports tables (report_definitions, report_runs) are the first tables added under these guards. The reports’ output files are on the reports volume and are not part of a database backup. tag_history is loaded last on restore, it has no foreign key of its own and is by a wide margin the largest table, so every small configuration table fails fast, if it is going to fail at all, before that one long step even starts.

settings has one deliberate exception. The history_mode row is written by store.ensureHistorySchema itself, once, the moment Migrate builds a schema from nothing, it records whether this server built tag_history as a TimescaleDB hypertable or a natively partitioned table, which is a fact about the database Migrate just ran against, not a fact about the plant. It is excluded from both the backup and the restore of settings: restoring a source install’s historical decision onto a target whose capabilities differ (or even just onto a fresh Migrate run against the very same class of server) could rewrite this row to describe a shape the target does not actually have. The target’s own Migrate has already made the right call before Restore ever looks at settings, and that call is left alone.

On-demand backups

• POST /api/backup (backup.run, admin-only) takes a backup right now and returns the manifest.
• GET /api/backup (backup.read, admin-only) lists what is on the backup volume: name, size on disk, and the manifest (table list, row counts, HotLoop version, timestamp) for each one.

Both are admin-only by omission from the operator role, the same narrowing fleet.restart/fleet.config/notify.test already apply to other operational, “do it now” or administratively-scoped actions. A backup listing’s row counts, including users and sessions, read closer to an administrative diagnostic than to the plant-floor reads (devices, tags, alarms) the operator role is meant to see, so backup.read was not split out to a lower permission the way some reads are; that is a judgment call, not a technical constraint, and could be revisited if a real operator workflow needs it.

Scheduling and retention

HOTLOOP_BACKUP_INTERVAL (e.g. 24h) runs a backup on a fixed tick. This is deliberately a plain interval, not a cron expression parser, the realistic need is “back this up every so often,” and this project’s own convention is not to build for a need nobody has yet. Leaving it unset (or 0) disables the scheduler; the on-demand endpoint still works, for an operator or an external cron who would rather trigger it themselves.

HOTLOOP_BACKUP_RETAIN (default 7) keeps the newest N backup directories, deleting older ones after every successful backup. 0 or less keeps everything, a choice an operator has to make deliberately on a small volume.

Restoring a backup

Restore is deliberately not an API endpoint. Automating “restore now” as a live, network-triggerable action against a database that may be serving production traffic is a real hazard, one wrong click, or one compromised admin session, overwrites live data, that this project is not building without being explicitly asked to. Restore is a flag on the binary itself, run once, by hand, by an operator who has already decided the target database is meant to receive it:

hotloop --restore-from=/data/backups/20260918T220000Z

What this does, in order:

1. Loads configuration and connects to the database exactly like an ordinary start, store.Open runs the same migrations, building the schema restore is about to load data into.
2. Refuses a backup it cannot load without losing something: one taken at a newer schema than this database’s, or one with a table or column this version no longer has. Both are checked before a single row is loaded. See “Restoring onto a different version” below.
3. Refuses if any target table already has data beyond what Migrate itself just seeded (the one history_mode row in settings), restore only ever runs against a database with nothing in it yet. This is the safety check: a table with even one existing row stops the whole restore before any COPY FROM STDIN runs.
4. Loads every table, in dependency order, inside a single transaction, into the columns the manifest names.
5. Resets the backing sequence for every table with a bigserial primary key (tags, alarm_events, automation_runs, events, write_audit, sessions) to one past the highest restored id, COPY FROM STDIN loads explicit id values without touching the sequence, so skipping this would leave the very next ordinary insert colliding with a restored row.
6. Re-counts every table and compares against the manifest’s row counts, inside the same still-open transaction.
7. Commits only if every count matched. A mismatch, or any error at any earlier step, rolls the whole transaction back, leaving the target database exactly as empty as it was found, never partially loaded.
8. Exits. Nothing else in the process starts: no poll scheduler, no alarm engine, no API server. A restore has no use for any of them, and starting the runtime against a database mid-restore would be its own hazard.

Restoring onto a different version

The release you restore with is rarely the release that took the backup. The backup was taken months ago on whatever was running then, and the box you are rebuilding runs today’s. So here is what restores onto what.

An older backup onto a newer HotLoop: yes. That is the case that matters, and it is tested from every schema level there has been, 0001_core through today, on TimescaleDB and on plain PostgreSQL, with the app started afterward.

• Every manifest records each table’s columns, names and types. Restore loads each file into exactly those columns, by name. A column added since the backup takes its default. write_audit’s entity_id, service and call_id come back empty on every audit row from before entity services, which is what those rows were: plain tag writes.
• A table added since the backup stays empty, and the app fills what it can when it starts. The entity registry’s first sync gives every restored tag its read entity, every armed tag its control entity and every alarm definition its alarm entity, with the names the registry would have given them. Devices from a backup older than the equipment tree get placed on it by their site, area and line text during the restore itself, the same way the upgrade placed them. What was never in the backup can’t come back: labels, composite entities, and entity names an operator customised after the backup was taken.

A newer backup onto an older HotLoop: no, refused. The manifest records every migration the backup’s database had applied. If it has one this database has never run, restore stops before loading a row and says so:

refusing to restore: this backup was taken by HotLoop <its version> with the
database schema at <its newest migration>, which is newer than this database's
schema at <this database's newest migration> (HotLoop <this version>). ...
Restore it with HotLoop <its version> or a later release

“Newer” means migrations, not version strings. A version string says which binary took the backup, and dev builds, release candidates and hotfix branches make comparing those a guess. The migrations say what the schema actually was, and they’re compared as a set, so a missing migration is caught wherever it falls in the order. The versions are in the message because the version is what you can act on: which release to restore with.

A backup with a column this version no longer has: refused, by name. The day a migration drops, renames or retypes a column, a backup from before it has data the new table has no place for. Restore won’t guess. It names every such column, across every table, before loading anything:

• a column the running table doesn’t have is refused, unless backup.columnRenames maps it to its new name. A migration that renames a column adds that entry, and a test restoring a backup from before it, in the same change. There are no renames today, so the map is empty;
• a column whose type changed is refused. Binary COPY carries no types, so the bytes would be read as the new type or rejected halfway through;
• a column the running table has added as NOT NULL with no default is refused, because nothing could fill it.

The way out is the same for all three: restore with the release that took the backup, then upgrade that database in place, which is how the migration was meant to meet that data.

Loading by name also covers the change nobody notices: a column dropped and re-added, or a table rebuilt, so the columns sit in a different order. Loading by position would put one column’s values into another’s without an error whenever the types line up. By name, each lands where it belongs.

Backups taken before manifests recorded any of this (every backup made before this release) have no column lists and no schema. They still restore:

• Without column lists, restore falls back to position. A file with fewer columns than the table goes into the table’s first columns, and the rest take their defaults. That is right for every schema so far, because every migration to date only added columns, and ALTER TABLE ADD COLUMN always appends. A file with more columns than the table is refused by PostgreSQL itself.
• Without a schema, restore assumes the backup is as new as it could possibly be: 0007_service_audit.sql, the newest migration there was when manifests started recording it. That assumption can only ever refuse a restore that would have worked, never let through one that wouldn’t. In practice it refuses nothing, because any HotLoop with this check has already run every migration up to that one.

The first backup this release takes records everything, so the fallback only matters for backups already sitting on a backup volume.

Restore procedure, step by step

1. Get the backup directory onto the machine or volume the target deployment’s hotloop binary can read, this is the point where an operator’s own off-box copy of the backup volume comes back into play.
2. Point HOTLOOP_DB_* at the target database. It must be reachable and have no data yet in any of the tables internal/backup.Tables lists (a brand new, empty database is the normal case; store.Migrate will build the schema in step 3).
3. Run hotloop --restore-from=<path to the backup directory>.
4. Read the final log line: restore complete with the table and row counts, or an error explaining exactly what was refused and why.
5. Start hotloop normally against that same database.

Moving between databases

A backup is the same thing whatever database took it: PostgreSQL’s binary COPY files and a manifest that names every column’s Postgres type. On SQLite there’s no COPY, so internal/backup writes those same bytes itself, with pgx’s own encoders, and reads them back the same way. So moving a plant from one database to another is a backup on the old one and a restore into the new one. Nothing else.

• SQLite to Postgres or TimescaleDB. The Pi that outgrew its file. Take a backup, point HOTLOOP_DB_* at the new server (HOTLOOP_DB_TYPE=postgres), restore. On a TimescaleDB server the history lands in a hypertable.
• Postgres or TimescaleDB to SQLite. A plant going down onto a small box. Same thing the other way, with HOTLOOP_DB_TYPE=sqlite.
• Plain Postgres to TimescaleDB, and back. The history mode is sticky, so pointing a Gateway at an IoT box’s plain Postgres keeps its partitions. To get a hypertable, restore into a fresh TimescaleDB. Going the other way gets you partitions.

What’s checked on the way in is what’s checked for any restore: every column by name and type against the target, with SQLite’s columns read as their Postgres types, so a backup from one restores into the other or refuses for the same reason it would anywhere. history_mode is never copied: the target keeps what its own migrations decided. Sequences come out right on both: on Postgres fixSequences moves them, and SQLite’s AUTOINCREMENT moves itself past any id restored into it.

TestRestoreMovesBetweenDatabases does each of these for real: SQLite to Postgres and back, partitions to a hypertable and back, every table seeded, backed up, restored and checked value by value.

Configuration

Variable	Default	Details
HOTLOOP_BACKUP_ENABLED	false	The feature is off, including the API routes, until this is set
HOTLOOP_BACKUP_DIR	/data/backups	Where backups are written; meant to be a PVC (backup.persistence in the chart)
HOTLOOP_BACKUP_RETAIN	7	Newest N backups kept; 0 or less keeps everything
HOTLOOP_BACKUP_INTERVAL	unset	e.g. 24h; unset means on-demand only

--restore-from=<dir> is a cmd/hotloop flag, not an environment variable, see “Restoring a backup” above.

What has and has not been verified

Verified, not merely tested, see this repository’s own distinction between the two, and the reasoning below for why that distinction mattered here specifically.

The real risk with a backup/restore feature is a test suite that proves a file was created without proving the data in it is actually recoverable, exactly the shape of the two real bugs caught in the two PRs immediately before this one (an SMTP channel whose tests all passed against an unauthenticated relay while the authenticated path was silently broken; a genuine data race caught only because the race detector was actually run, not skipped). So the test that matters here is the full round trip, run against a real timescale/timescaledb:2.29.1-pg16 instance:

• Write real data through the normal store API, a device, a tag, a tag reading (tag_latest and tag_history both), an alarm definition with live state and a recorded event, an automation with a finished run, a system event, a write-audit row, a custom setting, a user with a session, a fleet member.
• Back it up.
• Stand up a second, independently created and migrated, genuinely empty database, not the source database wiped in place.
• Restore into it.
• Assert the restored values match what was written, not just that row counts line up: the device’s name and address, the tag’s scale, the exact history reading, the alarm’s state and trip value, the alarm event, the automation and its run’s status, the custom setting, the user’s role, the session, the fleet member.
• Assert history_mode in the restored database reflects what that database’s own Migrate decided, not the source’s recorded value.
• Assert the sequence fix actually works: insert a new row into a restored bigserial table after the restore and confirm it neither collides with a restored id nor errors.
• Assert the safety check actually refuses: attempting to restore into a database that still holds the original seeded data fails with an error naming the problem, and the original data is provably untouched afterward.
• Retention (Prune) and listing (List) are exercised directly at the filesystem level: the newest N backups survive, older ones are deleted, and a listing reports them newest-first with the right row counts.
• The interval scheduler (Manager.tick) is exercised end to end against a real database, started, left to tick more than once, stopped, and this is also the test the race detector was run against (go test ./internal/backup/... -race -count=10, inside a golang:1.26 container, since this development machine has no cgo compiler) for ten iterations with no races reported.
• POST /api/backup and GET /api/backup were exercised against a real running hotloop instance, not only through go test.

Not verified: behavior under a tag_history large enough that a single backup or restore takes so long the surrounding transaction becomes a practical concern (lock contention, connection pool exhaustion under concurrent normal traffic), the round-trip test’s data volumes are small by design, since what it needs to prove is correctness, not throughput.

Restoring across versions is tested, not assumed. TestRestoreFromEverySchemaLevel builds a database the way each release left it, migration by migration with data written at each step, takes the backup that release would have taken, and restores it onto today’s schema twice: once with today’s manifest and once with the manifest every earlier release wrote. Then it starts the runtime, the alarm engine and the entity registry, the way the app does, and checks the device is on the tree, every tag and the alarm have an entity, an operator’s entity name survived, and the audit rows are intact with the newer columns empty. It runs on TimescaleDB and on plain PostgreSQL. TestRestoreRefusesABackupFromANewerSchema restores today’s backup onto a database whose schema stops at 0005_entities.sql and checks it’s refused with both versions and both schema levels in the message and nothing loaded. TestRestoreMatchesColumnsByName applies a test-only migration to a throwaway database (a reordered column, a dropped one, a renamed one with and without a mapping, a retyped one, a new NOT NULL one) and checks each loads correctly or is refused by name. A renamed column with no mapping, a dropped one and a retyped one are refused rather than handled: see “Restoring onto a different version”.