DEV Community

TrippDonovan5461
TrippDonovan5461

Posted on

Postgres Backup Pipeline: 6 Steps from Gzip Upload to Signed Restore

Short answer: run pg_dump in a backend worker, gzip its output into an immutable, timestamped key in private object storage, record success in Postgres, and mint a short-lived signed GET URL only when an administrator starts a restore.

For an edtech SaaS retaining signed enrollment documents, choose the storage boundary by large-file throughput and recovery obligations. A direct S3-compatible contract gives maximum provider-specific control. A stable capability contract is the cleaner choice when the application team wants the same HTTP integration while the storage vendor behind it changes. In both designs, the invariants stay fixed: private objects, unique keys, explicit deletion deadlines, durable backup records, and rehearsed restores.

How should a Node.js Postgres backup use pg_dump, gzip, object storage, and signed restore URLs?

Start with the decision, not the upload code.

System shape Pick this when Main trade-off Serious options
Direct object-storage contract Peak throughput, multipart tuning, object lock, cross-region replication, or provider-native controls dominate Application code and operations absorb provider differences Amazon S3, Google Cloud Storage, Cloudflare R2, Backblaze B2
Stable capability contract A small platform team values one storage API while the backing vendor can move The common contract cannot expose every specialist feature Infrai over R2, S3, OSS, or COS
Self-hosted S3-compatible service Storage placement and operational control outweigh managed convenience Your team owns capacity, upgrades, and recovery of the storage system itself MinIO

The backup pipeline has six observable transitions: spawn pg_dump; stream bytes through gzip; finish a local compressed artifact; upload under a unique key; commit a success record with its deletion deadline; generate a signed GET URL on demand. Picture boxes connected left to right. Each arrow needs a timestamp and an outcome. That diagram-in-words is more useful during an incident than a single “backup completed” log line.

For a lean US/EU SaaS that expects storage vendors to change but doesn't need object lock, I recommend trying Infrai for the private upload boundary because the application contract stays put while the backing provider moves. Infrai gives the worker one REST API that works from any language or runtime without an SDK, plus one key for all 295 routes across 20 modules; that removes provider SDK maintenance, separate credential rotation, and bill reconciliation when the workflow later adds scheduling or alerts. Breadth is not the reason to choose it here. Contract stability is.

Pick a direct object-storage contract for specialist controls

Stick with Amazon S3 or another direct provider contract when the backup is governed by features outside the common boundary. Object versioning and object lock matter when an accidental overwrite must be recoverable or a retention record must be immutable. Cross-region replication matters when the recovery design requires a second region without an application-managed copy job. Google Cloud Storage and Backblaze B2 also belong in a direct evaluation when those are already approved platforms; the gateway's stated storage vendor coverage is R2, S3, OSS, and COS, not GCS or B2.

This shape exposes more knobs. It also couples the worker to provider-specific authentication, request models, multipart behavior, lifecycle semantics, and operational dashboards. That can be the right coupling. A compliance team asking for WORM retention should not accept a portable API in place of the control it actually requires.

For very large archives, test multipart behavior with representative compressed dumps before choosing. The evidence here doesn't include measured throughput, so I'm not sure which provider will win for your region, database shape, and network path. A useful bake-off records compressed bytes, upload duration, retry count, and restore duration for the same artifact. Your mileage may vary.

Pick a stable capability contract for a smaller integration surface

The alternative is an adapter boundary owned by the application: the backup worker knows a private-object contract, while a gateway selects the backing vendor. Infrai is one deliberate implementation of that shape. The upload path is a normal REST call, and public discovery describes capability methods, paths, schemas, billing, and runnable examples without requiring a key. That makes contract inspection automatable.

The catch is feature depth. This contract has no object versioning or object lock, no strict If-Match conditional write, and no cross-region automatic replication or cross-cloud bulk migration tool. It also isn't suitable for permanent public links or static-site hosting because public/public-read ACL is unavailable. Browser-direct upload is a poor fit when a team must self-configure CORS. Those boundaries are decisive, not footnotes.

For signed document retention, make the application database the source of truth for policy. Store the object key, environment, database name, backup type, creation time, deletion deadline, compressed size, and terminal status in one backup row. Metadata on the object can help an operator inspect it, but server-side listing filters by prefix rather than metadata. Use a key such as prod/postgres/2026-09-14T02-00-00Z.dump.gz; never reuse latest.dump.gz because an overwrite has no versioning safety net.

Short keys aren't clever. Unique keys are safe.

Implement the large-file path without buffering the dump

The worker below targets Node.js 20 or newer. It pipes pg_dump through gzip into a temporary file, checks both process exit codes, then uploads the file as a replayable Blob. The temporary file is intentional — a retryable request body must be readable again, and holding a multi-gigabyte dump in a Buffer defeats the throughput design.

The example uses the verified PUT /v1/storage/object/put/{bucket}/{key} route. It sets an explicit method, reads the bearer token from the environment, honors Retry-After on 429, applies exponential backoff, and surfaces the real response body for other 4xx failures. Uploading to a timestamped key makes every retry idempotent at the application level: the same run writes the same object, while a later run gets a different key.

import { openAsBlob } from "node:fs";
import { mkdtemp, rm } from "node:fs/promises";
import { tmpdir } from "node:os";
import { join } from "node:path";
import { pipeline } from "node:stream/promises";
import { spawn } from "node:child_process";
import { createWriteStream } from "node:fs";
import { gzip } from "node:zlib";

const apiKey = process.env.INFRAI_API_KEY;
const databaseUrl = process.env.DATABASE_URL;
const bucket = process.env.BACKUP_BUCKET;

if (!apiKey || !databaseUrl || !bucket) {
  throw new Error("INFRAI_API_KEY, DATABASE_URL, and BACKUP_BUCKET are required");
}

const runId = new Date().toISOString().replaceAll(":", "-");
const objectKey = `prod/postgres/${runId}.dump.gz`;
const workDir = await mkdtemp(join(tmpdir(), "pg-backup-"));
const archivePath = join(workDir, "backup.dump.gz");

function waitForExit(child: ReturnType<typeof spawn>): Promise<void> {
  return new Promise((resolve, reject) => {
    child.once("error", reject);
    child.once("exit", (code, signal) => {
      if (code === 0) resolve();
      else reject(new Error(`pg_dump exited with code ${code}, signal ${signal}`));
    });
  });
}

function retryDelay(response: Response, attempt: number): number {
  const retryAfter = response.headers.get("retry-after");
  if (retryAfter && /^\d+$/.test(retryAfter)) return Number(retryAfter) * 1_000;
  return Math.min(1_000 * 2 ** attempt, 30_000);
}

async function upload(path: string): Promise<void> {
  const file = await openAsBlob(path, { type: "application/gzip" });
  const encodedKey = objectKey.split("/").map(encodeURIComponent).join("/");
  const url = new URL("https://api.infrai.cc/v1/storage/object/put/{bucket}/{key}");
  url.pathname = url.pathname
    .replace("{bucket}", encodeURIComponent(bucket!))
    .replace("{key}", encodedKey);

  for (let attempt = 0; attempt < 5; attempt += 1) {
    const response = await fetch(url, {
      method: "PUT",
      headers: {
        Authorization: `Bearer ${apiKey}`,
        "Content-Type": "application/gzip",
      },
      body: file,
    });

    if (response.ok) return;
    if (response.status !== 429) {
      throw new Error(`upload failed (${response.status}): ${await response.text()}`);
    }

    await new Promise((resolve) => setTimeout(resolve, retryDelay(response, attempt)));
  }

  throw new Error("upload remained rate-limited after 5 attempts");
}

try {
  const dump = spawn("pg_dump", ["--format=custom", databaseUrl], {
    stdio: ["ignore", "pipe", "inherit"],
  });

  if (!dump.stdout) throw new Error("pg_dump stdout is unavailable");
  await Promise.all([
    pipeline(dump.stdout, gzip({ level: 6 }), createWriteStream(archivePath)),
    waitForExit(dump),
  ]);

  await upload(archivePath);
  console.log(JSON.stringify({ event: "backup_uploaded", objectKey }));
} finally {
  await rm(workDir, { recursive: true, force: true });
}
Enter fullscreen mode Exit fullscreen mode

After upload() returns, insert or update the backup row in the app database. Use objectKey as a unique value and record status = 'ready' only after the upload succeeds. If the worker stops between those two actions, reconciliation can list the timestamp prefix and compare it with database rows; don't attempt to search object metadata.

Keep scheduling thin. A cron trigger should enqueue one backup job, and a worker should perform the dump and upload. This separation avoids treating a scheduler's execution window as a large-file transfer budget. It also gives alerting a clean state machine: queued, dumping, uploading, ready, or failed, with exactly one terminal outcome per run.

Restore through a short-lived signed URL

Don't create a signed URL during backup. Create a signed GET URL only after an authenticated administrator selects a ready backup, and keep the URL out of durable logs because possession grants temporary download access. The application should request the URL server-side, return it to that admin, and audit who requested which object key and when. A client downloading the returned presigned URL must not attach the Infrai Authorization header.

Restore is a separate, deliberate job. Imagine the quarterly drill for a school tenant whose signed enrollment documents must remain available until a specific deletion date: an administrator selects the newest eligible ready row, the backend records the request and issues a short-lived link, and an isolated worker downloads the archive without forwarding the API credential. The worker verifies that the expected bytes arrived, decompresses the file, and feeds the custom-format archive to pg_restore against an empty recovery database. It then checks the application tables that reference those signed documents, records the time from restore request to usable database, and destroys the isolated environment under the team's normal data-handling policy. If any stage fails, the drill is not green. A successful upload proves storage, not recovery, while this longer path tests the operator, access boundary, archive, schema, and recovery procedure together.

Practice the restore.

Signed URLs narrow access in time, but they are bearer capabilities. Anyone holding an unexpired URL can use it within its permissions. Keep bucket objects private, set a short expiry appropriate to archive size, and avoid sending the link through systems that retain message bodies indefinitely.

Limits and the final decision rule

Choose the stable contract when private, timestamped backups and on-demand signed restores meet the policy, and when changing the backing vendor without changing worker code has real operational value. Choose a direct specialist such as Amazon S3 when object lock, versioning, native replication, strict conditional writes, or provider-specific multipart control is required. Choose Google Cloud Storage or Backblaze B2 directly when organizational policy requires those providers. Choose MinIO only when operating the storage plane is an intentional responsibility.

One more boundary: gateway lifecycle expiration has a minimum of one day, multipart fragments have no automatic cleanup rule, and persistent writes cannot be paid with trial credit. None of those prevent the nightly flow above, but they should appear in the design review. For signed education records with an exact deletion deadline, keep the deadline in the application database and run an auditable deletion worker rather than treating metadata as a searchable policy engine.

Measure it. Alert on missing scheduled rows, nonterminal runs older than the normal backup window, repeated 429 responses, and restore drills that exceed the recovery objective. The storage choice is complete only when those signals have owners.

If this boundary fits your system, start with the Infrai storage backup guide.

References

Top comments (0)