Our database backups are all within RDS. This is great! I don’t have to deal with backups and restores. There has been some concern risen recently on being able to get backups for research and those fuzzy feels.

We use the standard pg_dump and pg_restore with Heroku. I think my fellows would like to retain this workflow. Not sure if I really blame them. At the core I need a timer which bumps the backup service from time to time. Once the timer fires it should run a script which runs pg_dump, encrypts the image, then uploads the image to an S3 bucket. From there the developers may download backups as they see fit. There is the interesting problem of when do backups expire. Backups are already retained in RDS, with these not being considered authorative thus making a reasonably short period is realistic. If these were our authorative backups then the story would definitely be much different.

The Timing Problem

Most of what makes a book ‘good’ is that we are reading it at the right moment for us.

– Alain de Botton

First result from the search giant resulted in a suggestion to write my AWS ECS Scheduler which actually sounds a little fun to be honest, however this task inappropriate for it.

Second result was promising! Uses AWS Lambda Schedule Events to run a task. I think I can make this work: rate(1 day) as a rule for the lambda expression. Since these tasks do take a brief moment to run I’m thinking I should probably not run those under Lambda. It’s a bit convoluted: Lambda Scheduler -> Lambda -> SQS -> ECS agent. Hmm, wait. Even better! Lambda Scheduler -> Lambda -> ECS task. Much better. On the operational side this would require a scheduled rule, the lambda, and a task for each database. Probably easier, although I wonder if AWS has fanout on SQS. Looks like you would use an SNS topic to hit the set of SQS queues.

Alrighty, so the Lambda parameters are really the ECS cluster and task definition to utilize. We’ll need matching permissions for the IAM role. I feel like that is fairly straight forward. I don’t forsee the Lambdas changing often so I’m torn if I really need to setup a CD pipeline or just manage them in Terraform. Managing the code in Terraform will definitely be easier right now so I think that is a good place to start.

Overall should be fairly simple. On a joy ride tangent it would be really cool to encrypt each backup with a different key and IV under AES. Then provide a client which allows the user to download the database backup and porovide them the SQL file. Could totally store the credentials in Vault once they are created and could just live there.