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).
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 :
curl -o latest.dump `heroku pg:backups public-url -a appname`
(again here replace appname with your application name)
curl command will respond with a similar output:
% Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 147k 100 147k 0 0 84584 0 0:00:01 0:00:01 --:--:-- 84612
Then, move to the directory that you have recently downloaded the
latest.dump. 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.