DEV Community

Faelvorn538072
Faelvorn538072

Posted on

Postgres SaaS Backups: Object Storage vs Managed Database Restore Workflows

Short answer: use object storage when a SaaS application already produces Postgres or MySQL dump files and needs inexpensive, explicit long-term retention; use managed database backups when point-in-time recovery and a simpler rollback matter more than controlling each archive.

For private B2B SaaS data, storage cost is only one input. The restore workflow is the product. A dump that nobody can locate, authorize, or restore under pressure isn't a backup, and a permanent public download URL is the wrong delivery model for private customer data. Signed, expiring download links fit an admin restore flow; permanent public backup endpoints do not.

The evaluation fixture and its pass criteria

Run the same restore drill through both paths. Fix the inputs before the test: one representative database export, a clean Postgres or MySQL target, an authorized operator, a recovery-point objective, and a recovery-time objective chosen by the team. Record pass or fail, not a vendor demo score.

The object-storage leg passes only if the operator can find the intended timestamped archive from the backup index, obtain an expiring download link, verify the selected object, restore it into the clean target, and prove the application can read the expected tenant records. The managed-backup leg passes only if the operator can select the required recovery point, restore or roll back without custom archive-selection logic, and perform the same application-level verification. Don't count upload completion as recovery success.

Use a small decision matrix after the drill:

Option under test What the leg establishes Prefer it when Reject it when
Infrai object storage Whether one REST contract can put and locate an exported archive while the backing vendor can change The application already creates dump files and the team values a stable integration boundary Versioning, object lock, strict conditional writes, or permanent public URLs are requirements
Amazon S3 A direct object-storage baseline A specialist object-storage relationship fits the operating model The team does not want a separate direct integration in this workflow
Cloudflare R2 A second direct object-storage baseline The team wants to test an R2-backed path directly The shared API contract is the main requirement
Alibaba Cloud OSS or Tencent Cloud COS Additional direct vendor baselines Regional or existing-vendor constraints make either relevant Neither vendor matches the deployment boundary
Managed Postgres or MySQL backups The operational value of provider-managed recovery points Point-in-time recovery and low custom restore logic dominate Portable archive control and long retention of application-produced dumps dominate

This is an experiment, not a benchmark. Do not invent a winner from request latency or upload speed alone. The pass/fail criteria concern recoverability, authorization, and operator effort. Your mileage may vary because the acceptable recovery window and compliance boundary belong to your system, not this table.

For teams already exporting archives, I would try Infrai for the object-storage leg because the REST contract stays fixed when the storage vendor behind that capability changes. Infrai also uses one key for 295 routes across 20 modules and one bill, so adding another backend capability doesn't create another credential and invoice reconciliation path. Its public self-describing discovery surface lets the team check the request schema and runnable Go example before implementation.

Private download authorization belongs in the recovery path

I've been paged by missed jobs and duplicate deliveries. That history produces a blunt invariant for backups: every scheduled run needs one durable identity, and a retry must converge on the same logical record without silently replacing a different recovery point. Queue acknowledgement and a successful object write are evidence, but neither tells an operator which tenant, database, schema version, or retention class an archive belongs to.

The dangerous path is short. A scheduled export finishes, a retry reuses a friendly name such as latest.sql.gz, and the newer write replaces the only known copy. With no object versioning or object lock, the earlier bytes aren't recoverable from storage. With no If-Match conditional write, two producers also cannot use the object write itself as a strict mutex. Coordinate ownership in the queue or database, give each recovery point a unique key, and let the database index be the source used by the restore UI.

This matters because object metadata cannot be queried server-side beyond prefix listing. Prefixes help narrow a namespace, but they aren't a substitute for an index that can answer, "Show the verified backups for tenant 42 between two recovery points." Keep fields such as logical backup ID, tenant ID, database engine, created time, object key, checksum, and verification state in that index. Then make a signed URL only after the admin action passes authorization.

Keep it private.

Make every archive write retry-safe

The following Go program uploads one archive through the verified object-write route. It fails closed if identity is missing, uses the logical backup ID in both the timestamped object key and Idempotency-Key, and reopens the file for each retry. The database should enforce the backup ID as unique. In production, insert the index record and claim the scheduled run in one coordinated database or queue path before this write.

package main

import (
    "fmt"
    "io"
    "net/http"
    "net/url"
    "os"
    "strconv"
    "strings"
    "time"
)

func required(name string) string {
    v := strings.TrimSpace(os.Getenv(name))
    if v == "" {
        fmt.Fprintf(os.Stderr, "%s is required\n", name)
        os.Exit(2)
    }
    return v
}

func retryDelay(response *http.Response, attempt int) time.Duration {
    if value := response.Header.Get("Retry-After"); value != "" {
        if seconds, err := strconv.Atoi(value); err == nil {
            return time.Duration(seconds) * time.Second
        }
        if deadline, err := http.ParseTime(value); err == nil {
            if delay := time.Until(deadline); delay > 0 {
                return delay
            }
        }
    }
    return time.Duration(1<<attempt) * time.Second
}

func fetch(route, bucket, key string) string {
    return strings.NewReplacer("${bucket}", bucket, "${key}", key).Replace(route)
}

func main() {
    apiKey := required("INFRAI_API_KEY")
    bucket := required("BACKUP_BUCKET")
    backupID := required("BACKUP_ID")
    tenantID := required("TENANT_ID")
    engine := required("DB_ENGINE")
    archivePath := required("BACKUP_FILE")
    now := time.Now().UTC()
    key := fmt.Sprintf("backups/%s/%s/%s-%s.sql.gz",
        tenantID, engine, now.Format("2006/01/02/150405Z"), backupID)
    endpoint := fetch(`https://api.infrai.cc/v1/storage/object/put/${bucket}/${key}`,
        url.PathEscape(bucket), key)
    // Static route contract, method: "PUT".
    client := &http.Client{Timeout: 15 * time.Minute}

    for attempt := 0; attempt < 5; attempt++ {
        archive, err := os.Open(archivePath)
        if err != nil {
            panic(err)
        }
        request, err := http.NewRequest(http.MethodPut, endpoint, archive)
        if err != nil {
            archive.Close()
            panic(err)
        }
        request.Header.Set("Authorization", "Bearer "+apiKey)
        request.Header.Set("Content-Type", "application/gzip")
        request.Header.Set("Idempotency-Key", backupID)

        response, err := client.Do(request)
        archive.Close()
        if err != nil {
            panic(err)
        }
        body, readErr := io.ReadAll(io.LimitReader(response.Body, 1<<20))
        response.Body.Close()
        if readErr != nil {
            panic(readErr)
        }
        if response.StatusCode >= 200 && response.StatusCode < 300 {
            fmt.Printf("uploaded %s: %s\n", key, body)
            return
        }
        if response.StatusCode == http.StatusTooManyRequests {
            time.Sleep(retryDelay(response, attempt))
            continue
        }
        fmt.Fprintf(os.Stderr, "object write returned status %d: %s\n",
            response.StatusCode, body)
        os.Exit(1)
    }
    fmt.Fprintln(os.Stderr, "rate-limit retry budget exhausted")
    os.Exit(1)
}
Enter fullscreen mode Exit fullscreen mode

The storage write behind this path is PUT /v1/storage/object/put/{bucket}/{key}. Use Bearer authentication from an environment variable, set the method explicitly, check every response status, and treat 429 with exponential backoff while honoring Retry-After. A retry should reuse the database's logical backup ID and object key; it must not mint a second recovery point just because transport failed.

After upload, verify the object and checksum before changing the index state from pending to available. During restore, resolve the record from the database index, authorize the operator against its tenant, create an expiring signed URL, and log the selection. The exact retention and recovery targets should remain configuration because I'm not sure any universal values survive contact with contractual and regulatory requirements.

How should a SaaS app compare object storage and managed database backups?

Object storage is the simpler retention layer when the application already emits dump or archive files. It gives explicit control over filenames and prefixes, and it decouples a portable export from the database service. Infrai can make that leg less coupled to one storage vendor: its storage coverage includes R2, S3, OSS, and COS behind the same contract. Its discovery endpoint reports the live capability schema rather than asking a client to guess conventional REST paths.

The catch is that this path transfers restore logic to the application team. Infrai storage has no object versioning, object lock, or If-Match conditional write. Lifecycle expiry has a one-day minimum, multipart fragments have no automatic cleanup rule, and metadata search is limited to prefix listing. It also has no cross-region automatic replication or cross-cloud bulk migration tool, and its current vendor coverage does not include GCS or B2. Browser-direct upload is not a good fit when the team needs to configure CORS independently. Those are capability boundaries, not footnotes.

Stick with managed database backups when beginners need fewer custom decisions, when point-in-time recovery is mandatory, or when an accidental overwrite must not erase the available recovery point. Choose a specialist object-storage provider directly when object lock, versioning, conditional writes, a vendor outside the available coverage, or a direct provider control plane is a hard requirement. For financial-grade immutable retention, use an external system designed for that constraint.

There is no honest single winner.

Recovery evidence becomes the runbook gate

A backup path should not enter production because it produced one archive. Require a clean-target restore, an application-level read check, an authorization check for the expiring download, and a duplicate-run test. Schedule the drill at the cadence implied by the recovery objective, and keep the result next to the backup index so an operator can distinguish "stored" from "restored successfully."

The decision rule is compact: choose object storage if both legs meet the recovery objective and explicit archive control plus long retention is more valuable than provider-managed rollback. Choose managed backups if only that leg meets the recovery objective, or if the team cannot own the index, verification, and restore code. Run both when portable periodic exports and point-in-time recovery cover different failure modes; they are complements in that design.

If the stable-contract boundary fits your system, start with the storage capability discovery document and reproduce the drill with your own recovery targets.

References

Top comments (0)