We run a single shared Postgres container on our VPS that backs three services — a chip-shop catalog API, a receipt-scanning service, and an image-safety service — sitting behind two public-facing sites. Until recently, the only backups that existed were manual: someone would run pg_dump by hand right before a risky migration or data change, just in case. That worked, but it meant we were only ever protected against the specific changes we remembered to be nervous about — not against the everyday stuff, like an accidental delete or a bug that corrupts data days before anyone notices.
So we set up proper automated backups: daily, local and off-site, without adding much moving parts to maintain.
The approach
A few constraints shaped the design:
- The box already has three live databases in one shared Postgres instance, plus a couple of archived/historical ones we didn’t need to touch.
- The existing manual backups used pg_dump -Fc (Postgres’s custom compressed dump format) — so the automated version should use the exact same format, for consistency and so restores work the same way regardless of which kind of dump you’re looking at.
- The VPS already runs a bunch of periodic jobs via systemd timers (log rotation, apt updates, etc.) rather than a crontab — so the new backup job should fit that same pattern instead of introducing a different scheduling mechanism.
- Local-only backups aren’t enough — if the disk or the whole VPS goes down, the backups go with it. We needed a copy living somewhere else entirely.
What we built
A small shell script does the actual work:
- pg_dump -Fc each of the three live databases into a dedicated subdirectory (kept deliberately separate from the existing manual “pre-change” dumps, so the automated retention logic can never accidentally sweep up someone’s deliberate rollback point).
- Push the new dump files to Azure Blob Storage using rclone.
- Delete local dumps older than 14 days.
A systemd service wraps that script, and a systemd timer fires it once a day with a few minutes of random jitter (so it doesn’t always land on exactly the same second as every other scheduled job on the box).
For the off-site copy, we created a dedicated Azure resource group and a Storage Account using the Cool access tier — cheap, appropriate for data you access rarely, and for a database this size (a couple hundred MB), the monthly cost rounds to a few cents. Rather than writing our own logic to prune old backups in the cloud, we set a lifecycle management policy directly on the blob container: anything older than 60 days gets deleted automatically by Azure itself. That’s one less thing to maintain and one less place a bug could quietly break retention.
The storage account credentials live in an rclone config file on the VPS, readable only by root — never committed to any repository.
Testing it
Before trusting the timer, we ran the service manually once and checked every link in the chain: the dump files appeared locally with sane sizes, the same files showed up in the Azure container with matching byte counts, and — importantly — the pre-existing manual “before we did something risky” dumps were completely untouched, since they live in a different directory than anything the automated script ever reads or writes.
The result
Every day, without anyone having to remember:
- Three fresh, compressed database dumps land locally, with two weeks of history kept on the box.
- The same dumps get pushed to Azure Blob Storage, with two months of history kept there automatically.
- Restoring is a single pg_restore command, whether you’re pulling from the local copy or pulling a slightly older one back down from Azure first.
The whole thing cost nothing to build (we’re well within Azure’s free tiers for everything else this VPS does) and a few cents a month to run. The bigger win isn’t the cost — it’s that backups now happen whether or not anyone remembers to think about them, which is exactly the point.
