How to capture, download and restore a pg backup from Heroku production PostgreSQL to your local development machine

This minipost will guide you through the process of capturing, downloading and restoring the live production pg dump to your local development machine in order to sync our development database with the production latest. Commands from the Heroku toolbelt and the pg_restore command will be used.

We do assume that you have installed on your local development machine the heroku toolbelt that will simplify the capturing process with just one command. We also assume that you have a working project deployed on the heroku platform with the name: appname (where appname - your application's name).

Move to the application directory by:

cd appname

In order to capture a live Heroku pg backup of your appname, use the following command:

heroku pg:backups capture --app appname

(replace appname with your application name)

Heroku, will respond with output similar to the following:

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 b001... done

Wait until the process of heroku backup capture finishes before you proceed to the next step.

In order to download the live backup :

heroku pg:backups:download --app appname

(again here replace appname with your application name)

The curl command will respond with a similar output:

Getting backup from ⬢ appname... done, #001
Downloading latest.dump... ████████████████████████▏  100% 00:00 149.58KB

To install the latest.dump to your local development machine, run the following:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U devuser -d appname-dev latest.dump

In the above pg_restore command, do not forget to replace devuser with the user associated with the database. You should also replace the appname-dev with the database name you are using for this project.

After these two replacements, you can run the line above. You will be prompted to insert a password, use the ubuntu user password.