AWS RDS Backups

Question

So I recently started using AWS and Elastic Beanstalk with RDS.

I wonder whats the best practices for creating database backups?

So far my setup is this.

1. Enable automatic backups.
2. bash script that creates manual snapshots everyday and removes manual snapshots older than 8 days.
3. bash script that creates a sql dump of the database and uploads it to S3.

Reason why I am creating the manual snapshots is if I were to delete the database by misstake, then I still have snapshots.

The bash scripts is on an EC2 instance launched with a IAM role which is allowed to execute these scripts.

Am I on the right track here?

I really appreciate answers, thanks.

Answer 1

A bit of context...

That automated backups are not saved after DB deletion is a very important technical gotcha. I've seen it catch devs on the team unawares, so thanks for bringing this up.

After the DB instance is deleted, RDS retains this final DB snapshot and all other manual DB snapshots indefinitely. However, all automated backups are deleted and cannot be recovered when you delete a DB instance. source

I suspect for most folks, the final snapshot is sufficient.

Onto the question at hand...

1. Yes. 110%. Absolutely.

2. I wouldn't create manual snapshots; rather, copy the automated ones.

   Option 1: You already have the automated snapshots available. Why not just copy the automated snapshot (less unnecessary DB load; though, admittedly less of an issue if you're multi-AZ since you'll be snapshoting from the replica), which created a manual snapshot. I'd automate this using the aws sdk and a cron job.

   Option 2: Requires manual adherence. Simply copy your automated snapshots (to create manual snapshots) before terminating a DB.

3. Unclear why you'd need the s3 dump if you have the snapshots.

   For schema changes?: If you're doing it for schema changes, these should really be handled with migrations (we use knex.js, but pick your poison). If that's a bridge too far, remember that there's an option for schema-only dumps (pg_dump --schema-only). Much more manageable.

   Getting at the data?: Your snapshots are already on s3, see faq. You can always load a snapshot, and sql dump it if you choose. I don't see an immediately obvious reason for purposely duplicating your data.