When ever you are doing a complex ticket and want to be really sure it works in production, it’s better to take a copy of production db and test your changes locally. We mainly use Heroku for our Ruby on Rails deployments and this is to show how we do the production backup stuff.

First we must tell heroku to capture your production data base at current time. For that in your app folder type this command:

$ heroku pg:backups:capture

You could get the information about the running backup using the following command:

$ heroku pg:backups:info

If your DB is large, it will take some time, once the backup is completed, heroku pg:backups:info might throw out an output as shown:

=== Backup b001
Database:         DATABASE
Started at:       2021-11-20 14:13:16 +0000
Finished at:      2021-11-20 14:21:34 +0000
Status:           Completed
Type:             Manual
Original DB Size: 904.40MB
Backup Size:      8.65MB (91% compression)

=== Backup Logs
2021-11-20 14:13:16 +0000 pg_dump: last built-in OID is 16383
2021-11-20 14:13:16 +0000 pg_dump: reading extensions
2021-11-20 14:13:16 +0000 pg_dump: identifying extension members
2021-11-20 14:13:16 +0000 pg_dump: reading schemas
2021-11-20 14:13:16 +0000 pg_dump: reading user-defined tables
2021-11-20 14:13:21 +0000 pg_dump: reading user-defined functions
2021-11-20 14:13:23 +0000 pg_dump: reading user-defined types

If you would like to give your data to a Data Analyst who is not added to your heroku project, you can get a downloadable URL of your backup using this command:

$ heroku pg:backups:url

You can get a dump of your backup using the following command:

$ heroku pg:backups:download

This will create a file called latest.dump in your project folder, to restore it you can use this command:

$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -d local_db
_name latest.dump