Automating Zero-Downtime Encrypted Database Backups with PGStash CLI

Database backups are rarely a problem until you actually need to restore one under pressure. Most standard cron scripts dump unencrypted SQL files locally, consuming disk IO and leaving sensitive PII vulnerable on the host. PGStash CLI changes this by handling client-side AES-256 encryption and streaming directly to object storage without touching local disk.

Architecture and Direct S3/R2 Streaming

By streaming the output of native dump utilities directly through an in-memory AES-256 cipher pipeline to AWS S3 or Cloudflare R2, PGStash eliminates disk write overhead. Storage retention policies are managed on the CLI side with automated pruning rules.

Automating Backups with Python and Webhook Notifications

While PGStash CLI can run as a standalone systemd timer, you can orchestrate workflows via Python to perform health checks and trigger Slack, Discord, or Telegram webhooks upon job completion.

import subprocess
import json
import requests

def run_pgstash_backup(config_path: str, webhook_url: str):
    cmd = ["pgstash", "backup", "--config", config_path, "--json"]
    result = subprocess.run(cmd, capture_output=True, text=True)
    
    if result.returncode != 0:
        payload = {"text": f"🚨 *PGStash Backup Failed*:\n```{result.stderr}```"}
        requests.post(webhook_url, json=payload)
        raise RuntimeError(f"Backup failed: {result.stderr}")
    
    data = json.loads(result.stdout)
    payload = {
        "text": f"✅ *Backup Successful*\nDatabase: {data['db_name']}\nSize: {data['size_bytes']} bytes\nTarget: {data['storage_target']}"
    }
    requests.post(webhook_url, json=payload)

if __name__ == "__main__":
    run_pgstash_backup("/etc/pgstash/prod.yaml", "https://hooks.slack.com/services/XXX/YYY/ZZZ")

Automated Test-Restores: Trust, But Verify

Unverified backups are equivalent to having no backups. PGStash CLI includes built-in restore flags that spawn an isolated temporary container or ephemeral schema, restore the stream, verify table counts, and tear down the infrastructure automatically.