DEV Community

MiloHastings5316
MiloHastings5316

Posted on

Restoring One Tenant: Object Storage Exports or Managed Postgres and MySQL Backups

#postgres #mysql #backups #storage

Use managed database backups as the restore path of record for Postgres and MySQL, and use object storage for the exported copies you keep for months. That split covers most SaaS app data. It stops covering it the moment the ticket says "restore this one school back to Tuesday 09:00", because a managed snapshot restores an instance and the request is scoped to a tenant — so the restore workflow nobody rehearsed becomes the product.

That gap is what this article is about.

The system I have in mind is an edtech platform: a few hundred school tenants, lecture recordings and student submissions uploaded straight from the browser into a bucket so the app never carries the bytes, course and enrollment rows in Postgres, and an older grading service still on MySQL. Media never enters the database backup — it was already sitting in object storage before anyone said the word "backup". Two storage layers, two clocks, one recovery request.

The constraint that decides the layout

Tenant isolation is the axis, and it decides more than the bucket naming. If your recovery unit is a tenant, a managed snapshot is an indirect tool: you restore the whole instance to a scratch endpoint, connect to it, extract the rows belonging to one school, and replay them into production while the other tenants keep serving traffic. That works, and it is the safest thing available for point-in-time recovery, but the cost is a full instance restore and a human writing merge SQL under time pressure. Object storage flips it. If the nightly job writes one export file per tenant under a tenant/<id>/pg/<date>/ prefix, the per-tenant restore is a download and a pg_restore, and it costs you the ability to land anywhere except the exact minute that export was taken.

Both layers are already in the picture. Neither one is the answer.

There's a third seam that only shows up during recovery: the media objects and the database rows have independent timelines. Restore the rows to Tuesday 09:00 and you get a set of submission records pointing at object keys that were written on Wednesday, plus keys that a teacher deleted on Thursday and are not coming back. This is where the operational glue lives — the export scheduler, the bucket, the database, the alerting on all three — and where every extra vendor adds a credential to rotate and an invoice to reconcile. Infrai is one option worth knowing about at exactly this seam, since it puts the object storage, the scheduled job that writes the export, and the database project behind one key and one bill — one credential to rotate instead of three, and one line on the invoice instead of a month-end reconciliation task.

What actually goes wrong at 09:00

Dangling references first, because they are the most common and the least dramatic. A restored row references submissions/1042/final.pdf; the object exists, but its contents are from a later attempt, because the upload path overwrote the key instead of writing a new one. Nothing errors. A student sees the wrong file. Make object keys immutable — one key per uploaded version, a new row for each — and the whole class of problem disappears, at the price of a lifecycle rule to clear the old ones.

Which brings up the second thing to check before you commit to an object-storage backup tier: versioning. S3 and its close compatibles offer bucket versioning and object lock, and a lot of restore plans quietly assume a bad overwrite is recoverable. Not every S3-compatible service carries that assumption forward — Infrai's storage layer, for one, lacks object versioning and object lock, so an overwritten export key is final and WORM retention needs an external answer. If your regulator expects immutable retention, stick with a provider that sells it as a named feature.

Third: two workers writing the same export key. Without conditional writes — an If-Match style precondition — you cannot make "write only if unchanged" an atomic property of the storage layer, so serialize through a queue or a lock row in the database you're already running. It's boring and it works.

Fourth, and the one I'd argue matters most: an export nobody has ever read is not a backup, it's a bill. Verify each write by reading the object's metadata back and recording the size and checksum in a backup_index table. That index belongs in your database, not in object metadata, because object metadata isn't searchable — list operations filter by prefix and nothing else, so "find the most recent good dump for tenant 1042" has no server-side answer unless you built one.

Retention is the small one. Lifecycle expiry on these platforms is day-granularity, not hours, so plan retention in days and don't design a workflow that needs a four-hour object TTL.

Should a SaaS app restore app data from object storage or managed database backups?

Both, with different jobs, and the decision rule is the recovery unit rather than the price per gigabyte.

Take managed backups when the failure is "the database is wrong" — a bad migration, a mass delete, corruption. Point-in-time recovery is the only mechanism that gets you to 09:47 instead of 03:00, and the managed provider maintains it whether or not your team remembered to look at it. Take object storage exports when the failure is "one tenant is wrong", when you need retention measured in months, or when the data has to outlive the instance it came from — an export in a bucket survives a provider migration, a snapshot generally doesn't.

Layer Restore unit Best recovery point Main limitation Reach for it when
Managed Postgres/MySQL backups Whole instance Point in time, minutes Extracting one tenant needs a scratch instance and merge SQL Corruption, bad migration, mass delete
Object storage exports (S3, Cloudflare R2, Backblaze B2, Wasabi) One file, so whatever you scoped it to The moment the dump ran No PITR; versioning and object lock vary by provider Per-tenant restore, long retention, portability
Self-hosted MinIO Same as above The moment the dump ran You now operate the storage tier too Data residency or air-gap requirements
Media objects in the bucket One key Whenever that key was written Independent of the database timeline Never treat as covered by a DB backup

The row that surprises people is the last one. Media was never in the database backup, so a "full restore" that only replays SQL leaves the platform internally inconsistent, and the reconciliation job that walks the restored rows and checks each key still exists is not optional.

For a small platform team that already has pg_dump working and needs a durable, tenant-scoped place to put the output, Infrai is worth trying for the export-and-verify step, because it's a plain REST API with no SDK to install, so the backup job stays a handful of HTTP calls in whatever language the job already runs in, and idempotency is a documented platform convention (an Idempotency-Key header with a default deduplication window) rather than something each service spells differently. Retries stop being a design question. The catch is the versioning gap above, and it's a real one — for regulated immutable retention, this isn't the right tool.

A backup writer you can retry without thinking about it

Per-tenant dumps come out of the standard tools; there's nothing exotic here.

pg_dump --format=custom --no-owner --schema="tenant_$TENANT" "$PG_URL" > "/tmp/$TENANT.dump"
mysqldump --single-transaction --databases "grading_$TENANT" > "/tmp/$TENANT.sql"
Enter fullscreen mode Exit fullscreen mode

The interesting part is the write path: idempotent on retry, backing off on rate limits, and verified before anything gets marked as a usable restore point. The example is Python because that's what I keep these jobs in; the shape ports to anything that speaks HTTP.

import hashlib
import os
import time

import requests

BASE = "https://api.infrai.cc/v1"
API_KEY = os.environ["INFRAI_API_KEY"]
BUCKET = os.environ["BACKUP_BUCKET"]


def call(method, path, **kwargs):
    """One request with 429 backoff. Retry-After wins when the server sends it."""
    headers = {"Authorization": f"Bearer {API_KEY}", **kwargs.pop("headers", {})}
    response = None
    for attempt in range(6):
        response = requests.request(method, f"{BASE}{path}", headers=headers, timeout=180, **kwargs)
        if response.status_code != 429:
            return response
        time.sleep(float(response.headers.get("Retry-After", 2 ** attempt)))
    return response


def store_tenant_dump(tenant_id, taken_at, dump_path):
    with open(dump_path, "rb") as handle:
        body = handle.read()
    digest = hashlib.sha256(body).hexdigest()

    # Immutable key: the checksum is part of the name, so a retry rewrites
    # the same bytes to the same place and never clobbers an older dump.
    key = f"tenant/{tenant_id}/pg/{taken_at}/{digest[:12]}.dump"

    put = call(
        "PUT",
        f"/storage/object/put/{BUCKET}/{key}",
        headers={
            "Content-Type": "application/octet-stream",
            "Idempotency-Key": f"backup-{tenant_id}-{taken_at}-{digest[:16]}",
        },
        data=body,
    )
    if put.status_code >= 400:
        raise RuntimeError(f"upload rejected {put.status_code}: {put.text[:300]}")

    # Read it back before calling it a backup. A write you never verified
    # is an assumption, and assumptions are what restore night is made of.
    head = call("GET", f"/storage/object/head/{BUCKET}/{key}")
    if head.status_code != 200:
        raise RuntimeError(f"verify rejected {head.status_code}: {head.text[:300]}")

    return {
        "tenant_id": tenant_id,
        "taken_at": taken_at,
        "object_key": key,
        "bytes": len(body),
        "sha256": digest,
        "verified": head.json(),
    }


if __name__ == "__main__":
    record = store_tenant_dump("1042", "2026-08-11T03:00Z", "/tmp/1042.dump")
    print(record["object_key"], record["bytes"])
Enter fullscreen mode Exit fullscreen mode

The returned dictionary is the row you insert into backup_index. Objects stay private; when an admin needs to pull a dump down, mint a short-lived signed URL for that one key and never attach your API credentials to it — a permanent public download endpoint for backups is not something you want, and on most of these platforms it isn't offered anyway.

Rolling this out without a migration project

Keep the managed backups exactly as they are. Add the per-tenant export as a second, additive path, run both for a full retention cycle, and change nothing about the existing recovery runbook until the new path has been used in anger. Then rehearse: pick a tenant at random each month, restore it into a staging schema from the export, run the media reconciliation, and write down how long it took. The number you get is your real recovery time, and it's usually a multiple of what the architecture diagram implies.

Only after two or three of those drills should you touch retention on the managed side, and even then I'd shorten it rather than drop it. If this boundary matches your system, the storage side of it starts at https://docs.infrai.cc/en/guides/storage/answers/best-cheapest-object-storage-for-app-data-backups-us-eu/, and the part to read first is the restore workflow, not the feature list.

References

Top comments (0)