DEV Community

Alessio
Alessio

Posted on Edited on Originally published at py-forge.com

Encrypted PostgreSQL Backups to S3 or R2 with One Python Script (and a Tested Restore)

#postgres #database #devops #backup

Most self-hosted Postgres backups are a pg_dump in a cron job, writing to the same disk as the database. That covers the easy failure, someone dropping a table, and none of the others: the disk dies, the server gets compromised, or the dump has been silently empty for three weeks. What you actually want is a dump that's compressed, encrypted before it leaves the machine, stored off-site, checked after every run, and pruned so the bucket doesn't grow forever.

PGStash is a single Python script that does that for PostgreSQL, MySQL and SQLite, with any S3-compatible storage (AWS S3, Cloudflare R2, MinIO, Wasabi). Below is a full run against a real PostgreSQL 17 database and a local S3-compatible endpoint, including a restore.

Install and check

The download contains main.py. I saved it as pgstash.py:

pip install -r requirements.txt     # boto3, cryptography
python3 pgstash.py doctor
Enter fullscreen mode Exit fullscreen mode 
=== PGStash System Health & Dependency Check ===
• Python Version:       3.13.5 [OK]
• Cryptography Library: Available (AES-256 Enabled)
• AWS Boto3 SDK:        Available (Cloud Ready)
• pg_dump utility:      Available
• mysqldump utility:    Available
• sqlite3 engine:       Available (Built-in standard Python driver)

Production ready? YES
Enter fullscreen mode Exit fullscreen mode

For PostgreSQL you need pg_dump on the PATH (from the postgresql-client package, at the same or a newer major version than your server). MySQL needs mysqldump. SQLite needs nothing extra.

One backup, end to end

S3 credentials come from the usual AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY environment variables. The database password can come from DB_PASSWORD.

export BACKUP_KEY='correct-horse-battery-staple'
python3 pgstash.py backup --db-type postgres --db-name shop \
  --db-host 127.0.0.1 --db-port 5544 --db-user postgres \
  --encryption-key "$BACKUP_KEY" \
  --s3-bucket acme-backups --s3-endpoint http://127.0.0.1:5055
Enter fullscreen mode Exit fullscreen mode 
[INFO] Starting zero-downtime backup pipeline for: shop (postgres)
[INFO] Initiating dump for [POSTGRES] target: shop
[INFO] Compressing raw snapshot with GZIP...
[INFO] Applying AES-256-GCM client-side encryption...
[INFO] Executing self-test restoration verification...
[INFO] Decrypting archive payload for integrity verification...
[INFO] Decompressing gzip layer...
[INFO] Verification Successful! Extracted size: 26,916 bytes | SHA256: 018cd71543f2ea27...
[INFO] Successfully uploaded to s3://acme-backups/postgres/shop/postgres_shop_20261020_002529Z.tar.gz.enc
[INFO] Pipeline finished successfully in 0.31s!
Enter fullscreen mode Exit fullscreen mode

The order matters. The archive is decrypted and decompressed locally before it's uploaded, and if that check fails, nothing is uploaded and the exit code is 1. For R2 or MinIO, --s3-endpoint is your provider's endpoint. Leave it out for AWS S3.

Keys follow <engine>/<database>/<engine>_<database>_<UTC timestamp>…, so each database gets its own folder. For SQLite, --db-name is the file path, and only the file name is used in the key:

python3 pgstash.py backup --db-type sqlite --db-name /var/lib/app/shop.db \
  --encryption-key "$BACKUP_KEY" --s3-bucket acme-backups
# -> s3://acme-backups/sqlite/shop.db/sqlite_shop.db_20261020_002459Z.tar.gz.enc
Enter fullscreen mode Exit fullscreen mode

SQLite is copied with SQLite's online backup API, so the app can keep writing during the copy.

Encryption

Archives are encrypted on the client with AES-256-GCM. The key is derived from your passphrase with PBKDF2-SHA256 (100,000 iterations) and a random 16-byte salt per archive. The storage provider only ever sees ciphertext. GCM is authenticated, so a wrong key or a modified file fails loudly instead of producing garbage:

python3 pgstash.py verify --path latest.enc --encryption-key "$BACKUP_KEY"   # Verification Successful!
python3 pgstash.py verify --path latest.enc --encryption-key wrong; echo $?
# [ERROR] Verification FAILED:
# 1
Enter fullscreen mode Exit fullscreen mode

Keep the passphrase somewhere other than the server you're backing up, like a password manager. Without it the backups can't be read.

Restoring

There's no restore subcommand. Restoring is decrypt, gunzip, then your database's normal restore tool. These few lines use the script's own decryption class:

# restore.py
import gzip, sys
from pgstash import EncryptionVault

src, out, key = sys.argv[1], sys.argv[2], sys.argv[3]
data = EncryptionVault.decrypt_bytes(open(src, "rb").read(), key)
open(out, "wb").write(gzip.decompress(data))
Enter fullscreen mode Exit fullscreen mode 
python3 restore.py latest.enc shop.dump "$BACKUP_KEY"
createdb shop_restored && pg_restore -d shop_restored shop.dump
psql -d shop_restored -tAc "select count(*), sum(total) from orders"
# 5000|18753750.0
Enter fullscreen mode Exit fullscreen mode

All 5,000 rows came back. The PostgreSQL dump is in pg_dump custom format (-F c), so pg_restore can also restore single tables. A restore drill like this once a month is the only real proof that your backups work.

Retention

prune keeps the newest backup per day, week and month (defaults: 7 daily, 4 weekly, 12 monthly) and deletes the rest. It works on one database at a time, so --prefix is required:

python3 pgstash.py prune --s3-bucket acme-backups --prefix postgres/shop/
Enter fullscreen mode Exit fullscreen mode 
[INFO] Retrieved 1 archives matching prefix 'postgres/shop/'
[INFO] Retaining 1 archives. Pruning 0 expired archives.
Enter fullscreen mode Exit fullscreen mode

Or add --prune to backup, which prunes that database's folder right after uploading.

Scheduling

export-bundle writes ready-to-edit templates:

python3 pgstash.py export-bundle --dir ./deploy
# docker-compose.yml  pgstash.service  pgstash.timer
Enter fullscreen mode Exit fullscreen mode

The systemd pair runs pgstash backup --all-env --prune once a day at 02:30 UTC, reading settings from /etc/pgstash/pgstash.env (DB_TYPE, DB_HOST, DB_NAME, DB_USER, DB_PASSWORD, ENCRYPTION_PASSPHRASE, S3_BUCKET, S3_ENDPOINT, plus the AWS keys). A plain crontab line works just as well. Set SLACK_WEBHOOK_URL, DISCORD_WEBHOOK_URL or TELEGRAM_BOT_TOKEN + TELEGRAM_CHAT_ID to get a message on success and on failure.

Limitations

  • Memory: the dump is compressed and encrypted in memory, so the machine needs RAM for roughly the dump size. That's fine for small and medium databases, but not for multi-hundred-GB ones. Use physical backups (pgBackRest, WAL-G) there.
  • Logical dumps only, with no point-in-time recovery between backups.
  • Archive files end in .tar.gz(.enc) but contain a single gzip-compressed dump, not a tar.
  • Two SQLite files with the same name in different folders share a key prefix. Give them distinct names.
  • The S3 bucket must already exist.

FAQ

Is "zero-downtime" real? For the database, yes. pg_dump takes a consistent snapshot without blocking normal reads or writes. Schema changes (DDL) wait until the dump finishes.

What happens if boto3 is missing? The run stops with an error telling you to install it.

Python version? Python 3 with boto3 and cryptography.

PGStash is a one-time purchase that includes the source and README: get PGStash ($49). More details are on the PGStash product page.

Top comments (0)