Tenant-Scoped Postgres Backup: Node.js pg_dump, Gzip, Object Storage, and Restore Links

Large-file throughput changes the backup design before the first API call. Short answer: run pg_dump in a worker, gzip the completed stream, write an immutable-per-run key that includes the tenant scope, and expose a short-lived signed download only after the restore or export authorization check. Keep the backup catalog in the application database, and test a real restore; an object that merely exists is not yet a recovery plan.

This is the shape I would use for a healthtech application storing product images and producing tenant-scoped exports. The database dump and the image objects are different recovery artifacts. They need a shared run identifier, but they should not be forced through one giant archive, because that makes retries, range reads, and throughput harder to reason about.

Failure modes that survive a successful upload

Start with boundaries, not SDK calls. A request handler should not own a multi-gigabyte backup. A queue worker should claim a backup job, capture a consistent logical dump, compress it, upload it, and record the resulting object key only after the upload is complete. The export service can then use the same run record to select image objects for one tenant without confusing a tenant's scope with the database's global scope.

The object name is part of the control plane. I use a structure like tenant/7f2c/runs/20260811T020000Z-4d91/db.sql.gz, where the run identifier is generated before work begins. Never make latest.sql.gz the only name. A scheduler retry and a slow first worker can otherwise race to the same key, leaving an operator with a plausible file and no reliable answer about which job produced it.

The application record should carry the tenant identifier, run identifier, database snapshot status, image-manifest status, object keys, byte counts, checksum, and completion time. Object metadata can help a human inspect a file, but it is a poor substitute for a queryable backup catalog. This separation also gives the authorization layer something concrete to check before it mints a restore link.

No magic here.

For large files, stream data between bounded buffers. Do not read an entire dump or image bundle into process memory just because the first test database was small. Set a maximum worker concurrency, measure upload duration and bytes per second, and make the local scratch path large enough for the compressed artifact. The throughput limit may be the database read, gzip CPU, disk, or network; guessing at it is expensive.

How do we prove the export is recoverable?

The orchestration language does not change the recovery contract. A Node.js worker can spawn pg_dump, but the critical path below keeps the I/O decisions visible in Python because this task's examples use one language consistently. The upload function stands for an S3-compatible object-storage client; its interface should support a streaming body and a private bucket, while the exact SDK remains an implementation choice.

import gzip import hashlib import os import pathlib import subprocess from datetime import datetime, timezone import requests DATABASE_URL = os.environ["DATABASE_URL"] OBJECT_ENDPOINT = os.environ["OBJECT_ENDPOINT"].rstrip("/") OBJECT_TOKEN = os.environ["OBJECT_TOKEN"] BUCKET = os.environ["BACKUP_BUCKET"] def create_dump(archive_path: pathlib.Path) -> str: digest = hashlib.sha256() with archive_path.open("wb") as archive: with gzip.GzipFile(fileobj=archive, mode="wb") as compressed: process = subprocess.Popen( ["pg_dump", "--format=plain", "--no-owner", DATABASE_URL], stdout=subprocess.PIPE, stderr=subprocess.PIPE, ) assert process.stdout is not None for chunk in iter(lambda: process.stdout.read(1024 * 1024), b""): compressed.write(chunk) digest.update(chunk) _, errors = process.communicate() if process.returncode != 0: raise RuntimeError(f"pg_dump failed: {errors.decode(errors='replace')}") return digest.hexdigest() def upload_archive(tenant_id: str, run_id: str, archive_path: pathlib.Path) -> str: timestamp = datetime.now(timezone.utc).strftime("%Y%m%dT%H%M%SZ") key = f"tenant/{tenant_id}/runs/{timestamp}-{run_id}/db.sql.gz" url = f"{OBJECT_ENDPOINT}/{BUCKET}/{key}" with archive_path.open("rb") as body: response = requests.put( url, headers={ "Authorization": f"Bearer {OBJECT_TOKEN}", "Content-Type": "application/gzip", }, data=body, timeout=900, ) response.raise_for_status() return key def make_restore_link(key: str) -> str: response = requests.post( f"{OBJECT_ENDPOINT}/{BUCKET}/presign/{key}", headers={"Authorization": f"Bearer {OBJECT_TOKEN}"}, json={"operation": "get", "expires_seconds": 900}, timeout=30, ) response.raise_for_status() return response.json()["url"] if __name__ == "__main__": run_id = os.environ["BACKUP_RUN_ID"] tenant_id = os.environ["TENANT_ID"] path = pathlib.Path(f"/tmp/{run_id}.sql.gz") checksum = create_dump(path) object_key = upload_archive(tenant_id, run_id, path) print({"key": object_key, "checksum": checksum}) 

The example intentionally records a digest while producing the compressed file, then uploads that file as a stream. In production, store the checksum and byte count in the run record, remove the scratch file after the catalog update, and make the worker's state transition explicit: running, uploaded, verified, or failed. A signed URL is an access capability, not a backup status. Issue it only after an authenticated operator has selected a run and the service has checked the tenant boundary.

One detail deserves scrutiny: the checksum above covers the uncompressed dump bytes while the uploaded artifact is compressed. That can be a useful application-level integrity value, but it is not a substitute for verifying the downloaded gzip file and successfully restoring it. If the team needs an artifact checksum, calculate and store a second digest over the compressed bytes after compression has finished.

The worker path: pg_dump, gzip, upload, and signed URLs

The most dangerous failures look successful in a dashboard.

Large image payloads add a separate trap: a database dump can be correct while the export is incomplete because an object manifest was built from a stale query or a paginated listing. Record the manifest version or query boundary with the run. If the object store offers multipart upload, use it for files beyond the service's single-request comfort limit, but do not call an upload complete until every part is accounted for and the final object can be read. In a tenant with 40,000 product images, for example, a page boundary that moves during export can quietly omit or repeat keys; the final archive may decompress cleanly, its database rows may look normal, and a dashboard may still show a green upload. The run must therefore retain the exact manifest inputs and count, compare the uploaded object count with that manifest, and fail closed when they differ. That extra bookkeeping feels excessive until an administrator asks for one tenant's complete export and the only evidence is a file size.

I would alert on missing expected runs, abnormal byte counts, checksum mismatches, and restore duration, rather than only on worker exceptions. A 413 response, a timeout at 900 seconds, or a sudden drop in bytes per second points to different corrective work. I'm not sure which limit will dominate until the production-shaped image set is measured; a small synthetic dump will not answer that question.

The design I would reject for production

The rejected design is a single application endpoint that accepts a tenant export request, runs pg_dump, loads the result into memory, and returns a public object URL. It combines user latency, database load, memory pressure, authorization, and retention into one request. That is convenient in a diagram and fragile under a real image corpus.

A private object bucket with a queue worker is a better default for this scenario, but it has a clear limit: it is not suitable when the recovery objective requires point-in-time recovery, immutable retention, cross-region replication, or a formal legal hold. Stick with a WAL-archiving system for point-in-time recovery, and choose storage controls that explicitly provide immutability when evidence must not be changed. A nightly logical dump can remain a useful independent artifact; it should not pretend to satisfy those requirements.

The other trade-off is operational ownership. Keeping the catalog, worker lease, manifest, checksum, restore drill, and alerts in the application gives a small team a coherent workflow, but it also means the team owns those controls. A managed backup product may be the right answer when the organization cannot operate restore testing and retention policy itself. The deciding axis is recovery evidence and large-file behavior, not which API has the shortest example.

Restore-first governance for production

Before shipping, restore one compressed dump into an isolated PostgreSQL instance, then verify tenant rows and a representative sample of image objects. Test an expired signed URL, an unauthorized tenant request, a duplicate worker claim, and a partial upload. Those are ordinary states, not edge cases.

The final run record should answer four questions without opening a bucket console: which tenant was included, which database and object keys were captured, what integrity checks passed, and when an operator last restored it successfully. If it cannot answer those questions, adding another storage provider will not repair the design.

References

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