Zero-Downtime PostgreSQL Backups to Cloudflare R2 with PGStash

Unmanaged database backups are a silent production risk. Running a standard pg_dump against a live, high-traffic database often locks tables, causes IOPS spikes, and risks running out of disk space before uploading the dump to S3.

The Architecture: Pipe, Encrypt, and Stream

PGStash bypasses local storage constraints by piping stdout from pg_dump straight through OpenSSL AES-256-CBC encryption directly into S3-compatible object storage like Cloudflare R2. This eliminates egress bandwidth costs and eliminates local IO write overhead.

Python Stream-Encryption to Cloudflare R2

import os
import subprocess
import boto3
from botocore.config import Config

def stream_backup_to_r2(db_url: str, bucket: str, key: str):
    s3_client = boto3.client(
        's3',
        endpoint_url=os.environ['R2_ENDPOINT_URL'],
        aws_access_key_id=os.environ['R2_ACCESS_KEY_ID'],
        aws_secret_access_key=os.environ['R2_SECRET_ACCESS_KEY'],
        config=Config(signature_version='s3v4')
    )

    # Spawn pg_dump without writing to local disk
    dump_proc = subprocess.Popen(
        ['pg_dump', f'--dbname={db_url}', '--format=custom', '--no-owner'],
        stdout=subprocess.PIPE
    )

    # Pipe dump stdout directly into AES-256 encryption process
    enc_proc = subprocess.Popen(
        ['openssl', 'enc', '-aes-256-cbc', '-pbkdf2', '-pass', f'pass:{os.environ["ENCRYPTION_KEY"]}'],
        stdin=dump_proc.stdout,
        stdout=subprocess.PIPE
    )
    dump_proc.stdout.close()

    # Stream the encrypted byte stream to R2
    s3_client.upload_fileobj(enc_proc.stdout, bucket, key)
    enc_proc.wait()
    print(f"[+] Backup successfully streamed and encrypted to R2: {key}")

Automated Cron Scheduling and Log Alerting

Automation requires strict observability. Couple the stream pipeline with systemd timers or cron jobs that emit JSON structured logs to monitor duration, payload size, and exit codes.

Automated Restore Verification Drills

An untested backup is not a backup. Trigger weekly automated restore drills inside an isolated Docker instance: pull the encrypted artifact from R2, decrypt in memory, inject into a fresh PostgreSQL instance, and run schema and integrity checks.

Adopting this architecture guarantees strict data retention compliance, slashes cloud egress costs, and keeps your Recovery Time Objective (RTO) under sub-minute thresholds during disaster scenarios.