DEV Community

Alessio
Alessio

Posted on

PGStash CLI: Zero-Downtime Database Backup, S3/R2 Encrypted Sync & Health Alert Suite

#python #automation

Data loss isn't a matter of if—it is a matter of when. Disks corrupt, cloud instances get terminated by human error, upgrade migrations go sideways, and ransomware actors specifically target unencrypted database volumes.

Yet, the state of database backup operations in many production stacks remains shockingly fragile: custom bash scripts running unmaintained pg_dump commands via crontab, dumping multi-gigabyte unencrypted .sql text files straight to local disks, filling up storage partitions, and silently failing for weeks without anyone noticing until disaster strikes.

PGStash CLI solves this critical operational liability. Engineered as a single, ultra-lightweight, zero-dependency Go binary and hardened Docker micro-utility, PGStash delivers zero-downtime, streaming database backups directly to any S3-compatible cloud storage with native client-side AES-256 encryption, intelligent lifecycle pruning, automated restore verification, and multi-channel instant health alerts.

The Production Reality: Why Traditional Backup Scripts Fail

Traditional database backup approaches suffer from five structural points of failure:

  1. Local Disk Exhaustion: Running pg_dump or mysqldump to a temporary directory before uploading requires $2\times$ or $3\times$ the database size in available disk space. When your database grows unexpectedly, the backup script exhausts local disk I/O and crashes your primary application.
  2. Plaintext Exposures: Backups written in plaintext to cloud buckets expose your entire customer data repository if IAM credentials leak or bucket permissions change.
  3. Silent Backup Rot: A backup is merely a hypothesis until it has been restored. Many engineering teams find out their dumps were corrupted, truncated, or incomplete only when trying to recover from an outage.
  4. Retention Script Complexity: Custom find /backups -mtime +30 -exec rm routines frequently misfire, either purging essential historical checkpoints or silently retaining old files until cloud storage bills balloon.
  5. No Telemetry on Silent Failures: When a cron job fails silently at 03:00 AM, team leads only discover it weeks later during an audit or an incident.

PGStash replaces fragile scripts with an enterprise-grade, memory-efficient backup engine.

Architectural Deep Dive: Streaming, Zero-Disk Footprint

PGStash does not buffer database dumps to your local filesystem. Instead, it leverages a continuous UNIX pipe architecture implemented through Go's io.Pipe, native database protocol streams, and concurrent S3 multipart upload workers.

┌─────────────────┐       ┌─────────────────┐       ┌─────────────────┐       ┌─────────────────┐
│ Database Engine │       │ Stream Compress │       │  AES-256-GCM    │       │ S3 Multi-Part   │
│ Postgres/MySQL/ │ ────> │   Zstandard /   │ ────> │   Client-Side   │ ────> │ Stream Uploader │
│     SQLite      │       │      Gzip       │       │   Encryption    │       │ (AWS/R2/MinIO)  │
└─────────────────┘       └─────────────────┘       └─────────────────┘       └─────────────────┘
 (Non-blocking Lock)       (Chunked Buffer)         (Cipher Block Chain)      (Direct HTTP/2 PUT)
Enter fullscreen mode Exit fullscreen mode

1. Memory-Bounded Pipeline ($O(1)$ Memory Overhead)

Data emitted by the database client binary (pg_dump, mariadb-dump, or SQLite's online vacuum API) is captured at stdout, channeled through a real-time zstd or gzip stream compressor, piped into an AES-256-GCM cipher stream, and immediately pushed to the cloud storage endpoint via an S3 multipart upload interface.

The application maintains a strict, configurable buffer footprint (typically under 64MB of RAM), meaning you can back up a 2TB database on an entry-level VPS without disk bloat or kernel out-of-memory (OOM) panics.

2. Client-Side Cryptographic Envelope

Encryption takes place before bits hit the network wire. Using AES-256 in Galois/Counter Mode (GCM), PGStash provides both high-grade confidentiality and cryptographic integrity verification. Without your master decryption key, any cloud compromise yields only indecipherable ciphertext.

3. Native Zero-Downtime Snapshots

  • PostgreSQL: Uses non-blocking read locks via standard transaction isolation snapshot mechanisms (--no-lock).
  • MySQL/MariaDB: Leverages --single-transaction and --quick flags to eliminate table locking on InnoDB storage engines.
  • SQLite: Employs the SQLite Online Backup API (sqlite3_backup_init) or WAL-checkpoint-safe read queries, preventing read/write stalls for active web applications.

Technical Specifications & Feature Matrix

Feature PGStash CLI Traditional Shell Scripts Standard Cloud Agent
Engines Supported PostgreSQL 12–17, MySQL 8+, MariaDB, SQLite 3 Custom per engine Typically 1 Engine
Disk Overhead 0 MB (Stream Piped) Full dump size ($1\times - 2\times$) Variable
Encryption AES-256-GCM Client-Side None (or manual OpenSSL piping) Server-Side Only
Cloud Targets AWS S3, Cloudflare R2, MinIO, Wasabi, Backblaze AWS CLI dependent Proprietary lock-in
Retention Engine Daily / Weekly / Monthly GFS (Automated) Manual crontab logic Basic retention rules
Dry-Run Restore Automated temporary container verification Rarely implemented Not supported
Alerting Discord, Slack, Telegram, Custom Webhooks Sendmail / Postfix (fragile) Dashboard notifications
Deployment Static Go Binary / Docker Compose / Systemd Shell script dependencies Heavy background daemon

Configuration & Usage

PGStash can be operated as a standalone CLI tool, a persistent Docker container, or a managed systemd background timer.

1. Unified Configuration (pgstash.yaml) 

version: "1.0"

database:
  engine: "postgres" # postgres | mysql | sqlite
  host: "127.0.0.1"
  port: 5432
  user: "db_user"
  password: "${DB_PASSWORD}" # Reads from environment
  name: "production_db"
  ssl_mode: "require"

storage:
  provider: "s3" # s3 | r2 | minio | wasabi
  endpoint: "https://<account_id>.r2.cloudflarestorage.com"
  bucket: "prod-backups"
  region: "auto"
  access_key_id: "${R2_ACCESS_KEY_ID}"
  secret_access_key: "${R2_SECRET_ACCESS_KEY}"
  path: "databases/prod-postgres"

security:
  encryption: "aes-256-gcm"
  passphrase: "${BACKUP_MASTER_KEY}"
  compression: "zstd" # zstd | gzip | none
  compression_level: 6

retention:
  keep_daily: 7
  keep_weekly: 4
  keep_monthly: 12
  prune_after_backup: true

verification:
  auto_verify: true
  verify_schedule: "weekly" # runs sandbox restore tests

notifications:
  on_success: true
  on_failure: true
  channels:
    discord:
      webhook_url: "${DISCORD_WEBHOOK_URL}"
    slack:
      webhook_url: "${SLACK_WEBHOOK_URL}"
    telegram:
      bot_token: "${TELEGRAM_BOT_TOKEN}"
      chat_id: "${TELEGRAM_CHAT_ID}"
Enter fullscreen mode Exit fullscreen mode

2. CLI Execution Examples

Execute a manual, on-demand streaming backup with zero local disk usage:

# Direct run using configuration file
pgstash backup --config /etc/pgstash/pgstash.yaml

# One-liner execution passing connection string directly
export BACKUP_MASTER_KEY="super-secret-key-32-chars-long!"
pgstash backup \
  --engine postgres \
  --uri "postgres://user:pass@127.0.0.1:5432/production_db?sslmode=disable" \
  --target "s3://prod-backups/pg/" \
  --encrypt \
  --notify-slack "https://hooks.slack.com/services/..."
Enter fullscreen mode Exit fullscreen mode

List, inspect, and verify existing encrypted snapshots stored on Cloudflare R2 or AWS S3:

# List all encrypted points-in-time
pgstash list --config /etc/pgstash/pgstash.yaml

# Download, decrypt, and stream directly into a target database
pgstash restore \
  --snapshot "prod-postgres-2025-05-18T03-00-00Z.zst.enc" \
  --target-uri "postgres://dev:pass@127.0.0.1:5433/restore_test_db"
Enter fullscreen mode Exit fullscreen mode

3. Automated Restore Verification (Synthetic Health Checks)

The single greatest point of failure in disaster recovery strategies is the absence of test restorations. PGStash addresses this directly:

When auto_verify: true is configured, PGStash initializes an ephemeral, sandboxed database process (via local test engine or container), decrypts the newly uploaded archive, streams it into the isolated test instance, checks database schema tables and row integrity, and reports the verification status directly to your Slack, Discord, or Telegram channel.

If corruption occurs during upload, you are alerted within minutes—not when your live cluster collapses.

4. Production Docker Compose Integration

Deploy PGStash as a non-intrusive sidecar alongside your application and database:

services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: secure_db_password
    volumes:
      - pgdata:/var/lib/postgresql/data

  pgstash:
    image: emergent/pgstash:latest
    restart: unless-stopped
    depends_on:
      - postgres
    environment:
      - DB_ENGINE=postgres
      - DB_HOST=postgres
      - DB_USER=app_user
      - DB_PASSWORD=secure_db_password
      - DB_NAME=app_db
      - R2_ACCESS_KEY_ID=${R2_ACCESS_KEY_ID}
      - R2_SECRET_ACCESS_KEY=${R2_SECRET_ACCESS_KEY}
      - STORAGE_ENDPOINT=https://${R2_ACCOUNT_ID}.r2.cloudflarestorage.com
      - STORAGE_BUCKET=infrastructure-backups
      - BACKUP_MASTER_KEY=${BACKUP_MASTER_KEY}
      - CRON_SCHEDULE=0 3 * * * # Nightly at 3:00 AM
      - DISCORD_WEBHOOK_URL=${DISCORD_WEBHOOK_URL}

volumes:
  pgdata:
Enter fullscreen mode Exit fullscreen mode

5. Bare-Metal Deployment: Systemd Timer

For host-level isolation without Docker overhead, PGStash provides ready-to-run systemd templates:

# /etc/systemd/system/pgstash.service
[Unit]
Description=PGStash Encrypted Database Backup Service
After=network-online.target

[Service]
Type=oneshot
User=pgstash
Group=pgstash
EnvironmentFile=/etc/pgstash/pgstash.env
ExecStart=/usr/local/bin/pgstash backup --config /etc/pgstash/pgstash.yaml
StandardOutput=journal
StandardError=journal
Enter fullscreen mode Exit fullscreen mode 
# /etc/systemd/system/pgstash.timer
[Unit]
Description=Run PGStash Database Backup Every 6 Hours

[Timer]
OnCalendar=*-*-* 00,06,12,18:00:00
Persistent=true

[Install]
WantedBy=timers.target
Enter fullscreen mode Exit fullscreen mode

Retention Policy Engine (Grandfather-Father-Son)

Storage costs scale linearly with uncontrolled backups. PGStash enforces an integrated, mathematically sound Grandfather-Father-Son (GFS) pruning policy directly over your cloud bucket:

  • Daily Checkpoints: Retain the latest $N$ daily snapshots for zero-latency point-in-time rollbacks.
  • Weekly Checkpoints: Retain 1 snapshot per week for $W$ weeks.
  • Monthly Checkpoints: Retain 1 snapshot per month for $M$ months.

The pruning algorithm runs atomically post-backup, evaluating metadata tags embedded directly within S3 object headers. It handles legacy deletions seamlessly without manual bucket-lifecycle rule management.

Get the Complete PGStash Production Bundle

Stop relying on hand-rolled shell scripts and hope. Get the battle-tested, end-to-end database backup suite for your engineering infrastructure.

What is Included in the Bundle:

  • Pre-compiled Go Binaries: Native, zero-dependency static binaries for Linux (AMD64, ARM64/Apple Silicon) and macOS.
  • Production-Ready Docker Images & Compose Stacks: Pre-configured sidecar patterns for PostgreSQL, MySQL, and SQLite.
  • Automated Verification Runner: Built-in sandboxed restore engine to test dumps.
  • Alert Templates: Native Slack, Discord, and Telegram webhook payloads.
  • Systemd Service & Timer Templates: Turnkey configuration for Ubuntu, Debian, Rocky, and RHEL servers.
  • Documentation & Runbooks: Full implementation and recovery guide for fast disaster response.

Protect your infrastructure, encrypt your mission-critical data, and sleep soundly knowing your restore process is verified daily.

👉 Purchase and Download PGStash CLI Now

Explore more developer micro-utilities and infrastructure automation tools at Micro Tools Hub.

Top comments (0)