DEV Community

SladeBarrett9642
SladeBarrett9642

Posted on

Postgres Training Backups: Node.js pg_dump Gzip Streams for 2026 Object Retention

The reliable default for an edtech backup is a streamed pg_dump compressed with gzip, uploaded as a private object, and retained by a policy that can be replayed from metadata. Large-file throughput is the deciding constraint: keep the web process out of the byte path, make each archive addressable, and test restore rather than treating an upload receipt as proof.

Short answer: run the dump in a worker, stream compression into multipart object storage, write an immutable manifest beside it, and issue a signed restore URL only after a successful integrity check. A retention rule should be data, not a cron comment.

A training platform has awkward artifacts: recorded lessons, generated transcripts, and assessment exports can be large, while the Postgres rows describe who owns each artifact and when it may be deleted. The archive must preserve that relationship without making a learner's request wait for a database dump.

The invariants for reproducible training-artifact retention

Start with an archive manifest. It should contain the database name, dump format, gzip mode, object key, byte count, SHA-256 digest, creation timestamp, policy version, and the schema or application revision that produced it. Store the manifest as a small object and in an audit table. That duplication is intentional: the table supports queries, while the object travels with the archive.

The object key should be deterministic for a backup run, but not derived from a learner's email or other personal data. A pattern such as tenant/{tenant_id}/backup/{run_id}/postgres.dump.gz gives operators a bounded prefix for retention and gives a restore job an unambiguous input. Keep the bucket private. A signed URL is temporary authorization, not the identity of the backup.

Retention needs explicit clocks. Define which timestamp starts the clock, whether legal holds pause deletion, and what happens to a failed run. For example, keep daily artifacts for 35 days, monthly artifacts for 13 months, and never delete an object referenced by an active incident. The exact numbers belong to the institution's policy; the important part is that a replay sees the same policy version and produces the same decision.

One sentence matters here.

I treat a successful upload as an intermediate state. The final state is verified, after the worker has compared the local digest and size with object metadata and recorded the manifest.

How do Node.js workers handle pg_dump, gzip, upload, and signed restore links?

A Node.js service can enqueue a run and expose status, while a worker owns the long-lived process and streams. pg_dump writes to stdout, gzip consumes that stream, and a multipart client sends bounded parts to an S3-compatible bucket. The database transaction that creates the run record should commit before the worker starts; retries then have a stable run ID and idempotency key.

The sample below shows the critical path with generic command and object-client interfaces. It is Python because the example stays close to the process and stream semantics; the same boundaries apply to a Node.js worker using child-process and stream primitives.

import hashlib
import json
import subprocess
from datetime import datetime, timezone

PART_SIZE = 64 * 1024 * 1024

def create_archive(pg_url, bucket, key, object_store, policy_version):
    digest = hashlib.sha256()
    total = 0
    upload = object_store.create_multipart(bucket=bucket, key=key, content_type="application/gzip")
    parts = []
    try:
        dump = subprocess.Popen(
            ["pg_dump", "--format=custom", "--dbname", pg_url],
            stdout=subprocess.PIPE,
            stderr=subprocess.PIPE,
        )
        compressor = subprocess.Popen(["gzip", "-c"], stdin=dump.stdout, stdout=subprocess.PIPE)
        dump.stdout.close()
        while True:
            chunk = compressor.stdout.read(PART_SIZE)
            if not chunk:
                break
            digest.update(chunk)
            total += len(chunk)
            parts.append(object_store.upload_part(upload.id, len(parts) + 1, chunk))
        if compressor.wait() != 0 or dump.wait() != 0:
            raise RuntimeError("backup process failed")
        object_store.complete_multipart(upload.id, parts)
        manifest = {
            "key": key,
            "bytes": total,
            "sha256": digest.hexdigest(),
            "created_at": datetime.now(timezone.utc).isoformat(),
            "policy_version": policy_version,
        }
        object_store.put(bucket, key + ".manifest.json", json.dumps(manifest).encode(), content_type="application/json")
        return manifest
    except Exception:
        object_store.abort_multipart(upload.id)
        raise
Enter fullscreen mode Exit fullscreen mode

The worker must drain and record stderr, cap concurrent dumps, and apply backoff to object calls. A 64 MiB part is only an example; your mileage may vary because retry cost, memory, and provider limits differ. Never put a database password in a command-line argument that other local processes can inspect; use the process environment or a protected connection file.

A restore endpoint should authorize the operator against the manifest, request a short-lived signed GET, and log the run ID, actor, reason, and expiry. The URL should not be emailed as a permanent link. When a learner-support ticket needs a copy, the application can mint another link without changing the object.

Which failure boundaries should the runbook test?

The first test is a real restore into an isolated database. Download the archive, verify SHA-256 and byte count, run pg_restore, and execute a small set of application queries. A green upload metric cannot detect a dump that was truncated before compression finished. Schedule this test often enough to catch credential, schema, and permission drift.

The second test is interruption. Kill the worker after part 3 of a 1.2 GB archive. The run must become failed, its multipart upload must be aborted, and a retry must reuse the logical run policy without silently appending to a corrupt object. A reaper should find uploads left in started state and abort them after a bounded age.

The third test is policy conflict. If an artifact is under a legal hold, the retention worker must record skipped with the policy version and leave the object untouched. If metadata is missing, fail closed and alert; guessing a deletion date is an audit bug.

Short logs help.

Track dump duration, compressed bytes, part retries, completion latency, restore duration, and signed-link issuance. Include tenant and run IDs, but redact SQL, tokens, and learner content. Rate limits should produce a retriable state with jitter, not a flood of identical jobs.

What should you choose when object storage lacks a needed control?

An S3-compatible interface is useful for portability, but compatibility is not a promise that every policy feature exists. Compare the controls you actually need: multipart upload, server-side encryption, versioning or object lock, lifecycle rules, range reads, audit events, and regional placement. A self-hosted cluster gives control over disks and network locality while making your team responsible for replication, upgrades, and recovery. A managed bucket reduces that operations burden but may constrain retention semantics or cross-region behavior. A filesystem target can be simple for a single worker and is a poor fit for independent restore jobs or multi-region access.

The catch is operational ownership. Do not select a target that cannot express your deletion hold, encryption, or throughput requirement just because its API looks familiar. Stick with a filesystem when one host and a tested off-site copy are genuinely sufficient; choose object storage when independent workers, lifecycle automation, and signed access are requirements.

I am not sure a single retention schedule fits every school district, because legal and exam-record rules vary. Resolve that uncertainty with the institution's policy owner and a restore drill, not a guessed default.

The decision record should name the rejected option and its valid use case. For this scenario, proxying every archive through an API server is rejected for large files because it doubles the byte path and couples backup throughput to request limits. It remains valid for a small export that must be inspected synchronously.

References

Top comments (0)