AWS RDS Nightly Staging Database
I am trying to determine if it is possible to configure the RDS service in AWS to do the following, but have not had much luck with my Google searches.
When it does its instance backup, have it automatically delete an RDS instance and spin up a new one using the backup. The use case here is to have the nightly backups of a production database generate a fresh "staging" database to be used in testing without affecting production data. This cannot be a replication type function because changing data on the staging db will effectively break replication.
When it does its instance backup, have it automatically copy the file to an FTP server. This is so a nightly copy could be pulled at anytime to update local database for development.
I am looking at improving some devops processes, and am tired of having to manually dump the production database to get my local copy up to date and also to get the staging server caught up. If the above is not possible via AWS, does anyone know if I there would be any issues with me making a bash script to execute a series of AWS console commands to try to do this or what issues I may have?
REFERENCE http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.BackingUpAndRestoringAmazonRDSInstances.html
1) I'm pretty sure there isn't any pre-available automatic way to achieve this. One day AWS Lambda will probably be capable, when it is taught how to receive an event after the RDS backup has occurred.
2) I think you've misunderstood what the RDS backups do. They actually take a snapshot of the RDS instance (i.e. the hidden EC2 instance on which the RDS instance is running). There is no database dump file which you can obtain and store or use outside of AWS. Restoring an RDS instance backup is actually spinning up a temporary new RDS instance from the snapshot, then copying your data round (or pointing apps to the restored instance)
I'd strongly recommend use of a script that uses your DB-specific dump tool (mysqldump, pg_dump, or whatever it is for SQLServer) to dump the production database from the production RDS instance, then import that into a pre-existing staging RDS instance, on whatever schedule you like.