How to Automate Database Backups in the Cloud

Backups are an availability feature, not a checkbox

Every team agrees backups matter; far fewer can actually restore one under pressure. The point of automating backups is to make recovery *boring and reliable*. That means choosing the right backup strategy, automating it so it never depends on a human remembering, and, critically, testing restores.

Backup types you should understand

Logical dumps are simple and portable, great for smaller databases and for moving data between versions. Physical backups plus continuous WAL archiving give you PITR, the ability to restore to, say, "3 seconds before the bad migration ran." For anything production, PITR is the gold standard.

The 3-2-1 rule

A durable backup strategy follows 3-2-1:

• 3 copies of your data,
• on 2 different media/storage systems,
• with 1 copy off-site (different region/provider).

The off-site copy is what saves you from a regional outage or an account compromise. A backup sitting in the same place as the database it backs up is one failure away from useless.

Automating logical backups

The simplest automation: a scheduled job that runs a dump and ships it to object storage.

#!/usr/bin/env bash
set -euo pipefail
TS=$(date +%Y%m%d-%H%M%S)
FILE="backup-${TS}.sql.gz"

# Dump and compress
pg_dump "$DATABASE_URL" | gzip > "/tmp/${FILE}"

# Ship off-site (e.g., to S3-compatible object storage)
aws s3 cp "/tmp/${FILE}" "s3://my-backups/postgres/${FILE}"

# Clean up local copy
rm -f "/tmp/${FILE}"

Schedule it with cron, and you've got nightly off-site dumps. But nightly dumps alone mean you can lose up to a day of data, which is where retention and PITR come in.

RPO and RTO: define them first

Two numbers drive every backup decision:

• RPO (Recovery Point Objective): how much data you can afford to lose. Nightly dumps = up to 24h RPO. PITR = seconds.
• RTO (Recovery Time Objective): how fast you must be back. This favors physical backups / managed snapshots over slow logical restores.

Decide these *before* picking a strategy. "We can lose at most 5 minutes and must recover within 1 hour" tells you immediately that nightly pg_dump isn't enough.

Retention and lifecycle

Keeping every backup forever is expensive; keeping none is reckless. A common tiered policy:

• Hourly/continuous: last 24-48 hours.
• Daily: last 7-30 days.
• Weekly/monthly: last several months for compliance.

Use object-storage lifecycle rules to expire old backups automatically so retention enforces itself.

Don't forget encryption

Backups contain your most sensitive data and often travel off-site. Encrypt them at rest (server-side encryption on the bucket, or encrypt the dump before upload) and in transit. Restrict who can read the backup bucket as tightly as the database itself.

Managed backups: let the platform handle it

Rolling your own backup cron is fine, but it's one more thing to monitor (and silent backup failures are a classic horror story). Managed databases handle this for you. On PandaStack, managed databases, PostgreSQL (14.x, 16.x), MySQL (5.7, 8.x), MongoDB, and Redis via KubeBlocks on GKE, support scheduled and manual backups. You configure a schedule and the platform takes the backups; you can also trigger a manual backup before a risky change.

Backup retention scales with your plan: Free includes 7-day DB backup retention, Pro 15-day, and Premium 30-day. For a production database, longer retention plus the ability to take an on-demand backup right before a migration is exactly the safety net you want.

If you need backups for an app whose data isn't in a managed DB, you can still run the scheduled-dump pattern above as a cronjob on PandaStack, the platform supports cronjobs natively, so your backup script runs on a schedule without you maintaining a separate scheduler.

# As a PandaStack cronjob, e.g. daily at 02:00:
# 0 2 * * *  ->  run the pg_dump-to-object-storage script above
# DATABASE_URL is injected; object-storage creds set as env vars.

The step everyone skips: test the restore

A backup is a hypothesis until you've restored it. Schedule a regular restore drill:

1. 1Spin up a fresh, throwaway database.
2. 2Restore the latest backup into it.
3. 3Run a query or two to confirm the data is intact and consistent.
4. 4Record how long it took, that's your real RTO.

Do this monthly. The first time you restore should not be during an outage.

Conclusion

Automated backups come down to: pick a strategy that meets your RPO/RTO (PITR for production), follow 3-2-1 with an off-site encrypted copy, enforce retention with lifecycle rules, and, above all, test restores on a schedule. Whether you script it or lean on a managed database, the untested backup is the one that fails you.

PandaStack's managed databases give you scheduled and manual backups with plan-based retention, and native cronjobs for any custom backup script. Start with a managed database on the free tier at https://dashboard.pandastack.io.

References

• PostgreSQL backup and restore: https://www.postgresql.org/docs/current/backup.html
• PostgreSQL continuous archiving and PITR: https://www.postgresql.org/docs/current/continuous-archiving.html
• MySQL backup methods: https://dev.mysql.com/doc/refman/8.0/en/backup-methods.html
• The 3-2-1 backup rule (CISA): https://www.cisa.gov/sites/default/files/publications/data_backup_options.pdf