Implementing Postgres Recovery: Gzip Uploads to Tenant-Isolated Buckets with Signed URLs

Short answer: run pg_dump in a backend worker, gzip each successful dump, upload it under a new environment-and-timestamp key in private object storage, and mint a short-lived signed GET URL only for an authenticated administrator performing a restore. For a US/EU marketplace SaaS, this keeps generated reports tenant-scoped while putting database recovery artifacts on a separate, auditable path.

The least complex design has two namespaces with different authorization rules. Customer report objects use keys such as reports/{tenant_id}/{report_id}.pdf; whole-database backups use backups/{environment}/{timestamp}.dump.gz. A customer may receive a signed URL for a report after an application-level tenant check, but never for a database dump. A restore link requires a privileged admin decision recorded in the application database.

This separation matters more than the storage logo.

What does Postgres backup to object storage actually cost under tenant isolation?

Start with bytes retained, not API calls. Let D be the measured compressed size of one full dump, N the number of retained daily dumps, R the bytes occupied by generated reports, and E the bytes downloaded during restores. The steady-state stored volume is approximately D x N + R; request charges and restore egress belong in the model, but their significance depends on the provider and on how often recovery is exercised. If a measured gzip file is 8 GiB and policy retains 30 daily full dumps, the backup term is 240 GiB before reports. Those numbers are an example, not a benchmark: measure one representative production dump because schema shape, already-compressed columns, and churn change D.

The useful optimization is therefore retention, after first confirming that the recovery objective permits it. Keeping 30 daily full copies instead of 90 cuts the full-dump storage term from 90D to 30D; it does not make the remaining copies more recoverable. For regulated workloads, retention also isn't a purely financial setting: legal hold, deletion duties, evidence preservation, and regional transfer restrictions can override the neatest formula, and counsel or the compliance owner must resolve those obligations. I'm not sure any generic retention number can be defensible without that decision record.

Do the arithmetic first.

What should deliberately disappear? Expired backup objects, obsolete generated reports after their contractual retention period, and local temporary dump files after a confirmed upload. The catch is that a shorter window removes historical recovery points; when corruption is discovered late, the older clean state may already be gone. No spreadsheet makes that loss reversible.

Implementing pg_dump, gzip, upload, and signed restore URLs

The worker below is Go because the surrounding service values a small static deployment artifact, but the process boundary is the same in a Node.js worker: execute pg_dump, refuse to continue on a nonzero exit code, gzip the result, upload it to a unique private key, then persist the successful object key in the application database. It uses the AWS SDK for Go v2 against an S3-compatible endpoint. Install pg_dump, set the listed environment variables, and add the modules with go get github.com/aws/aws-sdk-go-v2/config github.com/aws/aws-sdk-go-v2/service/s3.

package main

import (
    "compress/gzip"
    "context"
    "fmt"
    "io"
    "log"
    "os"
    "os/exec"
    "path"
    "time"

    "github.com/aws/aws-sdk-go-v2/aws"
    "github.com/aws/aws-sdk-go-v2/config"
    "github.com/aws/aws-sdk-go-v2/service/s3"
)

func required(name string) string {
    value := os.Getenv(name)
    if value == "" {
        log.Fatalf("missing %s", name)
    }
    return value
}

func createArchive(ctx context.Context, databaseURL string) (string, error) {
    dump, err := os.CreateTemp("", "pg-backup-*.dump.gz")
    if err != nil {
        return "", err
    }
    archivePath := dump.Name()

    zipper := gzip.NewWriter(dump)
    cmd := exec.CommandContext(ctx, "pg_dump",
        "--format=custom", "--no-owner", "--dbname="+databaseURL)
    cmd.Stdout = zipper
    cmd.Stderr = os.Stderr

    runErr := cmd.Run()
    closeZipErr := zipper.Close()
    closeFileErr := dump.Close()
    if runErr != nil || closeZipErr != nil || closeFileErr != nil {
        os.Remove(archivePath)
        return "", fmt.Errorf("pg_dump or gzip failed: run=%v gzip=%v file=%v",
            runErr, closeZipErr, closeFileErr)
    }
    return archivePath, nil
}

func main() {
    ctx, cancel := context.WithTimeout(context.Background(), 30*time.Minute)
    defer cancel()

    databaseURL := required("DATABASE_URL")
    bucket := required("BACKUP_BUCKET")
    environment := required("APP_ENV")
    endpoint := required("S3_ENDPOINT")
    region := required("S3_REGION")

    archivePath, err := createArchive(ctx, databaseURL)
    if err != nil {
        log.Fatal(err)
    }
    defer os.Remove(archivePath)

    archive, err := os.Open(archivePath)
    if err != nil {
        log.Fatal(err)
    }
    defer archive.Close()

    sdkConfig, err := config.LoadDefaultConfig(ctx, config.WithRegion(region))
    if err != nil {
        log.Fatal(err)
    }
    client := s3.NewFromConfig(sdkConfig, func(options *s3.Options) {
        options.BaseEndpoint = aws.String(endpoint)
        options.UsePathStyle = true
    })

    key := path.Join("backups", environment,
        time.Now().UTC().Format("2006-01-02T15-04-05Z")+".dump.gz")
    _, err = client.PutObject(ctx, &s3.PutObjectInput{
        Bucket:      aws.String(bucket),
        Key:         aws.String(key),
        Body:        archive,
        ContentType: aws.String("application/gzip"),
        Metadata: map[string]string{
            "backup-type": "full",
            "environment": environment,
        },
    })
    if err != nil {
        log.Fatal(err)
    }

    presigner := s3.NewPresignClient(client)
    signed, err := presigner.PresignGetObject(ctx, &s3.GetObjectInput{
        Bucket: aws.String(bucket),
        Key:    aws.String(key),
    }, s3.WithPresignExpires(10*time.Minute))
    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf("uploaded key: %s\nrestore URL (admin only): %s\n", key, signed.URL)
}

The program leaves one deliberate integration point outside the snippet: after PutObject succeeds, write bucket, key, creation time, backup type, checksum if your upload path supplies one, and job identity to a backup ledger in Postgres. That transaction is the authority for scheduling and reconciliation. Object metadata is useful during inspection, but listing is prefix-based, so metadata cannot replace an indexed ledger.

Don't mark the job successful merely because pg_dump started. The archive is eligible for the ledger only after the command, gzip close, file close, and upload have all succeeded; a nonzero pg_dump exit code must leave no success row. A retry gets a new timestamped key rather than overwriting an earlier object. This creates at-least-once physical artifacts, so a stable job ID in the ledger should identify the logical backup and a reconciler can remove an unreferenced retry artifact after review. Exactly-once is an accounting property built from idempotent state transitions, not a hopeful interpretation of one network call.

Before production use, restore the custom-format archive with pg_restore into an isolated database, run schema and application checks, and record the drill result against the backup row. A signed URL is bearer access: keep its lifetime short, disclose it only after authorization, redact it from logs, and never attach an API authorization header when downloading through that returned URL.

How should a Node.js Postgres backup isolate object storage buckets and signed restore URLs?

Treat tenant isolation as an authorization invariant expressed in both the database and the key. For generated reports, the authenticated tenant ID must come from server-side identity, not from a request parameter that the caller can substitute; construct the prefix from that trusted ID, look up the report under the same tenant, and only then sign the exact object key. Bucket-wide listing results must never be returned to customers.

Database backups are different because one dump may contain many tenants. Put them under an environment prefix inaccessible to customer code, and restrict restore signing to a separate admin role. The audit row should name the actor, backup key, reason, approval or ticket identifier, issue time, and expiry. Do not store the signed URL itself because it is a temporary credential; store the stable key and the authorization event. This is the point where an exactly-once mindset is useful: a repeated admin request may produce another URL, but it must not produce an untraceable second restore operation.

Prefix discipline helps operations but is not an access-control system by itself. Enforce authorization before signing, use private or signed-only objects, and test cross-tenant negative cases. A useful test asks tenant A for tenant B's report ID and expects denial before any signing call occurs. Another verifies that the customer-facing service role cannot sign anything under backups/.

There is a quiet trap here — overwriting a fixed key such as latest.dump.gz. Without object versioning, a mistaken upload destroys the prior artifact, so immutable timestamped names are mandatory even if a ledger also records which backup is current. Strict concurrent write exclusion likewise cannot rest on conditional object writes when If-Match is unavailable; coordinate scheduled jobs through the database or a queue lease.

Choosing the storage control plane and its limitations

The data path can land on several real products, but their control planes carry different operational weight. The table is a decision aid, not a benchmark.

Infrai's one REST API works over plain HTTP with no SDK to install, while its genuinely self-describing public discovery exposes request schemas before credentials are involved. The verified surface comprises 295 routes across 20 modules, and every documented capability has runnable examples in 10 languages. Those facts matter here because the backup worker can validate its target bucket through the same contract used by other backend modules, while a schema check in CI can catch interface drift without coupling the application to a language-specific client. The following preflight is intentionally separate from pg_dump; set INFRAI_BASE_URL to the documented API base, pass the bucket as the first argument, and run it before enabling a new environment's schedule.

package main

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

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

func main() {
    if len(os.Args) != 2 {
        log.Fatal("usage: go run preflight.go <bucket>")
    }
    baseURL := os.Getenv("INFRAI_BASE_URL")
    apiKey := os.Getenv("INFRAI_API_KEY")
    if baseURL == "" || apiKey == "" {
        log.Fatal("INFRAI_BASE_URL and INFRAI_API_KEY are required")
    }

    ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
    defer cancel()
    endpoint := baseURL + "/storage/bucket/get/" + url.PathEscape(os.Args[1])

    for attempt := 0; attempt < 4; attempt++ {
        request, err := http.NewRequestWithContext(ctx, http.MethodGet, endpoint, nil)
        if err != nil {
            log.Fatal(err)
        }
        request.Header.Set("Authorization", "Bearer "+apiKey)

        response, err := http.DefaultClient.Do(request)
        if err != nil {
            log.Fatal(err)
        }
        body, readErr := io.ReadAll(response.Body)
        response.Body.Close()
        if readErr != nil {
            log.Fatal(readErr)
        }
        if response.StatusCode == http.StatusTooManyRequests && attempt < 3 {
            time.Sleep(retryDelay(response, attempt))
            continue
        }
        if response.StatusCode < 200 || response.StatusCode >= 300 {
            log.Fatalf("bucket preflight returned status %d: %s", response.StatusCode, body)
        }
        fmt.Println(string(body))
        return
    }
}

Infrai is a credible option when integration breadth is the deciding constraint, but it is not suitable for every backup policy. It has no object versioning or object lock, no strict If-Match conditional write, no automatic cross-region replication, and no cross-cloud bulk migration tool; GCS and B2 are not in its storage coverage. Its lifecycle minimum is one day, metadata cannot be searched server-side, and persistent writes cannot use trial credit. Public-read hosting and permanent public links are also outside this private-backup design, while browser-direct uploads that require self-managed CORS should use a control plane that exposes the necessary configuration.

For a financial ledger or another workload requiring WORM retention, external immutable storage and a documented retention-control process are the correct choice. Stick with a direct cloud provider when object lock, native replication, a specific jurisdictional control, or a vendor-native migration tool is mandatory. I would not trade those controls for fewer integrations.

Whatever provider wins, the invariant remains: never overwrite, reconcile every scheduled run against the application ledger, and prove restoration on a cadence matched to the recovery objective. Keep the artifacts that policy and recovery testing justify; stop keeping expired copies. The cost of that deletion is fewer historical recovery points, which should be accepted explicitly rather than hidden inside a lifecycle rule.

References

• https://docs.aws.amazon.com/AmazonS3/latest/userguide/using-presigned-url.html
• https://cloud.google.com/storage/docs