I have my Heroku production database scheduled to make daily backups, and I want to restore the backups onto my staging database daily as well. This way I can keep the staging box in sync with production for testing/debugging purposes and have a daily test of the restoration process run automatically.
I've tried to schedule a bash script to run on the staging box to perform the restore. The script I have uses the Heroku CLI to pull the url of the latest backup and perform the restoration. The problem I have is with authenticating the Heroku CLI. Since I can't open a browser on the dyno, I need to find a safe way to authenticate.
Should I pull a .netrc file from somewhere? Is it even a good idea to give a dyno the CLI? Is there a better way to go about this without standing up another server to run the restorations?
You can put an authorization token in the HEROKU_API_KEY env variable on your staging environment. Generate the token with heroku auth:token.
Then set the token on staging with heroku config:set HEROKU_API_KEY=token -a staging
From a security standpoint, this means your staging environment pretty much has full access to your production environment.
A more secure way is to have a scheduled task run on the production app or a new app just for this purpose that copies the db backup to an S3 bucket the staging app has access to. The staging app the restores from the backup in the s3 bucket. Staging needs no access to production.
This is a good idea anyway - if you lose access to Heroku you'll still have access to your backups.
There's a buildpack for this - https://github.com/kbaum/heroku-database-backups. I encourage you to read the code in the build pack - it is a pretty simple processes. I would also either fork the buildpack, or just write your own code because it will have full access to your production environment. I woud never trust a third party buildpack with that.
Bonus points if your job scrubs sensitive information from your production database for staging. It could do this by: