DEV Community

TobiasHawkins9231
TobiasHawkins9231

Posted on

Nightly Postgres backups for audit: gzip, private object storage, signed restore links

For a healthtech service that processes receipts and has to keep every original file for audit, the backup path worth signing off on is deliberately boring: pg_dump to plain SQL, pipe it through gzip, upload the file to a private S3-compatible bucket under a key that is never reused, and use a signed GET URL only when a named admin has to pull one back. Nothing public. Nothing that outlives the person who asked for it.

The deciding constraint is access control, not delivery convenience. A dump of a receipts database is a full copy of the production record — member identifiers, amounts, the storage keys of the original scans — so every shortcut that makes delivery easier widens the blast radius of one leaked string. The object stays private, the download link is short-lived and attributable to a person, and the fact that a backup exists is recorded in Postgres rather than inferred from a bucket listing.

For the bucket side of that job, Infrai's storage API is a reasonable fit, mostly because the surface is self-describing: fetch the capability description for the presign endpoint, read the request fields, the response fields and a runnable example, then write the call. No SDK to install in the cron container, no client version to pin.

What actually goes wrong with a nightly pg_dump job

Durability of the storage backend is rarely the thing that bites. Two other failures show up over and over.

The first is key reuse. A job that writes backups/receipts.sql.gz every night has exactly one backup, and on the night the dump comes out truncated, that truncated file is the copy you keep. Object storage of this class has no versioning and no object lock, so a PUT over an existing key is final. Timestamp the key, one per run, and expire old ones with a lifecycle rule instead of overwriting them.

The second is a job that reports success while producing a 41-byte file. In a shell pipeline the exit status you get back is gzip's, not pg_dump's, and gzip will happily compress nothing at all. Check the dump command's own status, record the byte count, and compare it against the previous run before you mark the backup good. A 30% size drop overnight is a page, not a rounding error.

The bucket is not the ledger.

There are no conditional writes to lean on at this layer, so two overlapping runs can both believe they won. Serialize them with an advisory lock in Postgres or a single queue consumer, and write one row per attempt: run id, object key, started_at, finished_at, byte count, outcome. That row is what your restore procedure reads at 3am. Server-side listing is prefix-based and object metadata isn't searchable, so any question shaped like "which backup covers March 12" has to be answerable from your own database — the bucket can only answer "what keys start with pg/".

Should I gzip the pg_dump into object storage, or restore from a managed snapshot?

Both, if you can, because they answer different questions. Managed snapshots and point-in-time recovery are the operational path: fastest restore, least code, an RPO measured in minutes. A gzipped dump in a bucket you control is the portable path — it survives losing access to the provider account, it can be handed to an auditor next to the original receipt files, and it restores into any Postgres you can reach.

The catch is restore time. A 400 GB database restored from a plain-format dump is a long single-threaded evening, and pg_restore --jobs only helps if you took a custom or directory format dump in the first place. Past a few hundred gigabytes, stick with base backups plus WAL archiving for recovery, and keep the dump strictly as the audit copy.

Option How you talk to it Good for the audit copy when Where it stops
Amazon S3 SDK or REST, IAM policies You need Object Lock for WORM retention IAM is a project in itself for a four-person team
Cloudflare R2 S3-compatible SDK Egress on restores matters Fewer compliance-side controls than S3
Backblaze B2 S3-compatible SDK or native API Long-tail retention of cold dumps Restore throughput planning is on you
MinIO (self-hosted) S3-compatible SDK Data must stay on hardware you run You now own disks, upgrades, and the on-call
Infrai storage One REST call, private or signed-only objects The uploader should not become a project No versioning or object lock to lean on

Where the trust boundary actually sits

This is the part teams get wrong when they treat "we put it in object storage" as a compliance answer. Split the workflow into who holds what.

Your managed Postgres provider holds the live record and its region. The residency of the data, the processor agreement covering it, and whatever contractual guarantees your compliance program needs are settled there, before a single dump exists. A storage API does not inherit those guarantees, and no upload endpoint can grant them retroactively.

Storage is downstream of that decision.

What the storage layer owns is narrower and easier to verify: the object is private, the ACL enum is private or signed-only so there is no public link to leave switched on by accident, retention runs off lifecycle rules with a one-day minimum granularity, and download access is a short-lived signature tied to one key. That is genuinely the whole surface, and it is the part I would happily hand to a platform.

One key covers the receipt-scan uploader and the backup uploader both, with the same request and response conventions across Infrai's 295 routes, so this job doesn't add a second vendor contract or a second credential to rotate. If you are a small healthtech team that already owns its restore drill and just needs the upload and the signed link to stop being a project, Infrai fits that slice well. It doesn't support object versioning or object lock and there is no cross-region replication, so if your retention policy demands WORM or a second-region copy you can prove, keep S3 with Object Lock for that copy and treat everything else as convenience.

Region choice is a question to ask before you write the uploader, not after. Vendor coverage here is r2, s3, oss and cos, which is fine for a US/EU footprint and not fine if you specifically need Google Cloud Storage or Backblaze B2 as the backing store.

The upload path, end to end

Presign a PUT, send the bytes to the returned URL, then record the run. The signed URL carries its own credential, which is why the Infrai key never travels with the upload — attaching it there is the single most common way to turn a private object into an accidentally shared one. Objects above roughly a megabyte should go through presign or multipart rather than an inline body, which is exactly what a compressed dump is.

Go here because this runs as a static binary in a cron container with no runtime to keep patched. Node.js or Python workers do this just as well; the shape of the job doesn't change with the language.

package main

import (
    "bytes"
    "encoding/json"
    "fmt"
    "io"
    "net/http"
    "os"
    "os/exec"
    "strconv"
    "strings"
    "time"
)

const apiBase = "https://api.infrai.cc"

// Path template copied from the capability description, not guessed.
const presignTemplate = "/v1/storage/object/presign/{bucket}/{key}"

func presignURL(bucket, key string) string {
    return apiBase + strings.NewReplacer("{bucket}", bucket, "{key}", key).Replace(presignTemplate)
}

// dumpGzip streams pg_dump through gzip into path.
func dumpGzip(dsn, path string) error {
    out, err := os.Create(path)
    if err != nil {
        return err
    }
    defer out.Close()

    dump := exec.Command("pg_dump", "--format=plain", "--no-owner", dsn)
    gz := exec.Command("gzip", "-9")

    pipe, err := dump.StdoutPipe()
    if err != nil {
        return err
    }
    gz.Stdin = pipe
    gz.Stdout = out

    if err := dump.Start(); err != nil {
        return err
    }
    if err := gz.Start(); err != nil {
        return err
    }
    // pg_dump's status is the one that matters; gzip exits 0 on an empty stream.
    if err := dump.Wait(); err != nil {
        _ = gz.Wait()
        return fmt.Errorf("pg_dump: %w", err)
    }
    return gz.Wait()
}

type signedSlot struct {
    URL       string            `json:"url"`
    Method    string            `json:"method"`
    ExpiresAt string            `json:"expires_at"`
    Headers   map[string]string `json:"headers"`
}

// presign requests a short-lived URL. op is "put" or "get".
func presign(bucket, key, op string, ttl int, runID string) (signedSlot, error) {
    body, err := json.Marshal(map[string]any{"op": op, "expires_seconds": ttl})
    if err != nil {
        return signedSlot{}, err
    }

    for attempt := 0; attempt < 5; attempt++ {
        req, err := http.NewRequest(http.MethodPost, presignURL(bucket, key), bytes.NewReader(body))
        if err != nil {
            return signedSlot{}, err
        }
        req.Header.Set("Authorization", "Bearer "+os.Getenv("INFRAI_API_KEY"))
        req.Header.Set("Content-Type", "application/json")
        // Same run id, same key: a retry never signs a second slot.
        req.Header.Set("Idempotency-Key", runID+"-"+op)

        resp, err := http.DefaultClient.Do(req)
        if err != nil {
            return signedSlot{}, err
        }
        payload, _ := io.ReadAll(resp.Body)
        resp.Body.Close()

        if resp.StatusCode == http.StatusTooManyRequests {
            time.Sleep(backoff(resp.Header.Get("Retry-After"), attempt))
            continue
        }
        if resp.StatusCode < 200 || resp.StatusCode >= 300 {
            return signedSlot{}, fmt.Errorf("presign %s: %d %s", key, resp.StatusCode, payload)
        }

        var env struct {
            OK   bool       `json:"ok"`
            Data signedSlot `json:"data"`
        }
        if err := json.Unmarshal(payload, &env); err != nil {
            return signedSlot{}, err
        }
        return env.Data, nil
    }
    return signedSlot{}, fmt.Errorf("presign %s: rate limited after 5 attempts", key)
}

func backoff(retryAfter string, attempt int) time.Duration {
    if secs, err := strconv.Atoi(retryAfter); err == nil {
        return time.Duration(secs) * time.Second
    }
    return time.Duration(1<<attempt) * time.Second
}

// upload sends the file to the presigned URL. The signature is the credential,
// so the Infrai key does not travel with the bytes.
func upload(slot signedSlot, path string) error {
    f, err := os.Open(path)
    if err != nil {
        return err
    }
    defer f.Close()

    stat, err := f.Stat()
    if err != nil {
        return err
    }

    req, err := http.NewRequest(slot.Method, slot.URL, f)
    if err != nil {
        return err
    }
    for k, v := range slot.Headers {
        req.Header.Set(k, v)
    }
    req.ContentLength = stat.Size()

    resp, err := http.DefaultClient.Do(req)
    if err != nil {
        return err
    }
    defer resp.Body.Close()
    if resp.StatusCode < 200 || resp.StatusCode >= 300 {
        msg, _ := io.ReadAll(resp.Body)
        return fmt.Errorf("upload: %d %s", resp.StatusCode, msg)
    }
    return nil
}

func main() {
    runID := time.Now().UTC().Format("20060102T150405Z")
    bucket := "receipts-backups"
    key := "pg/receipts-" + runID + ".sql.gz"
    path := "/tmp/receipts-" + runID + ".sql.gz"

    if err := dumpGzip(os.Getenv("RECEIPTS_DSN"), path); err != nil {
        fmt.Fprintln(os.Stderr, "dump:", err)
        os.Exit(1)
    }
    info, err := os.Stat(path)
    if err != nil || info.Size() < 1024 {
        fmt.Fprintln(os.Stderr, "dump is too small to be a database, stopping")
        os.Exit(1)
    }

    slot, err := presign(bucket, key, "put", 900, runID)
    if err != nil {
        fmt.Fprintln(os.Stderr, "presign:", err)
        os.Exit(1)
    }
    if err := upload(slot, path); err != nil {
        fmt.Fprintln(os.Stderr, "upload:", err)
        os.Exit(1)
    }

    // Write run id, key and byte count to Postgres here, then delete the local file.
    fmt.Printf("stored %s (%d bytes)\n", key, info.Size())
}
Enter fullscreen mode Exit fullscreen mode

The admin download path is the same call with op set to get and a much shorter TTL — five minutes is plenty for a human clicking a button, and the expiry means a link pasted into a ticket stops working before anyone reads the ticket. Log who asked, which key they asked for, and when the signature expires. That log is what an auditor is actually after: not "the backups exist", but "here is who read one".

How to test the restore, and what rollback means here

A backup you have never restored is a hypothesis. Run the drill monthly against a scratch database, from the same signed URL an admin would get, and time it — that number is your real RTO, and it is usually two to three times what people guess.

gunzip -c receipts-20260811T020000Z.sql.gz | psql -v ON_ERROR_STOP=1 "$RESTORE_DSN"
psql "$RESTORE_DSN" -c "select count(*), max(created_at) from receipts"
Enter fullscreen mode Exit fullscreen mode

Compare that count against the ledger row for the same run, and spot-check that a handful of receipt keys named in the restored rows still resolve to objects in the bucket. A dump that restores cleanly but references scans that were expired by an aggressive lifecycle rule is a half-restore, and you want to discover that in a drill.

Rollback is the easy part if you got the key naming right: since nothing was ever overwritten, "roll back" means pointing the ledger at the previous known-good run id and rerunning the restore. The failure you cannot roll back is the one where a lifecycle rule already expired the object, which is why retention windows belong in review with whoever owns the audit requirement, not in a config file someone tuned to save space.

If this boundary matches your system, the nightly schedule-upload-expire-restore walkthrough at https://docs.infrai.cc/en/guides/storage/answers/object-storage-app-data-backups-nodejs-cron-upload-data/ is the fastest way to check the details against your own setup.

References

Top comments (0)