Copying Prod DB => Staging on Heroku

TL;DR

heroku pg:backups capture --app my-production-app
heroku pg:backups:url --app my-production-app
heroku pg:backups:restore 'https://xfrtu.s3.amazonaws.com/183b8a...' --app my-staging-app

--------------------------------

Even on personal projects, it's great to have a staging environment to play with (and break) without interfering with your production server.

One problem, however, is having two different data sets. It's important that staging is a replica of production, not only in code but in data too.

Here's how to copy your latest db data from production to staging on Heroku:

heroku pg:backups capture --app my-production-app

It will show output like this:

Starting backup of postgresql-angular-41117... done




Use Ctrl-C at any time to stop monitoring progress; the backup will continue running.

Use heroku pg:backups:info to check progress.

Stop a running backup with heroku pg:backups:cancel.

Backing up DATABASE to b002... done

Now you want to get a public url for this backup:

heroku pg:backups:url --app my-production-app

Which will give you a long url like:

https://xfrtu.s3.amazonaws.com/183b8ad6-5b2a-4ecf-be0d-804e411ca459/2020-06-12T00%3A11%3A02Z/3c6ead6d-524f-4f21-8325-6841ea692e61?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAQKF7VQWOLOW4WWWL%2F20200612%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20200612T001351Z&X-Amz-Expires=3600&X-Amz-SignedHeaders=host&X-Amz-Signature=542c5d25a70fd16b3ce51a1d2f1408117970d39f1a1c64a78178d755a9304868

And now you can restore that to your other Heroku server:

heroku pg:backups:restore 'https://xfrtu.s3.amazonaws.com/183b8a...' --app my-staging-app

And that's it! Once you know the right commands (and keep them somewhere handy, perhaps in a LinkedIn article or something), you can copy your db over within a couple minutes.

Happy coding!