Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Backup Heroku Postgres database and restore to local database

Grab new backup of database

Command: heroku pgbackups:capture --remote production

Response: >>> HEROKU_POSTGRESQL_COLOR_URL (DATABASE_URL) ----backup---> a712

Get url of backup download

Command: heroku pgbackups:url [db_key] --remote production

Example: heroku pgbackups:url a712 --remote production

>>> ""

Download backup dump file

Command: curl "http://[url]" > production.dump

Example: curl "" > production.dump

Restore backup dump into local db

Command: pg_restore --verbose --clean --no-acl --no-owner -h localhost -d [db_name] production.dump


This comment has been minimized.

Copy link

@siruguri siruguri commented Jun 18, 2015

The new format of these URLs is now:

heroku pg:backups capture


heroku pg:backups public-url

See mapping list on Heroku:


This comment has been minimized.

Copy link

@swstack swstack commented Jul 16, 2015

Worked nicely, thanks!


This comment has been minimized.

Copy link

@joel joel commented Dec 11, 2015

Playing with --jobs=4 --disable-triggers :

pg_restore --verbose --clean --jobs=4 --disable-triggers --no-acl --no-owner -h localhost -U user_name -d database_name tmp/last.dump


This comment has been minimized.

Copy link

@sfandrew sfandrew commented Jul 6, 2016

Is there way to use a GUI client like pgadmin to view the table with the dump?


This comment has been minimized.

Copy link

@Samarththebox Samarththebox commented Dec 13, 2016

Awesome! 👍


This comment has been minimized.

Copy link

@SidMorad SidMorad commented Mar 7, 2017

heroku pg:backups:capture
heroku pg:backups:download
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U user_name -d database_name latest.dump

After executing first two commands, you will have latest.dump file in your current directory.
And third command, will restore the database for you.


This comment has been minimized.

Copy link

@iabdulin iabdulin commented Jun 17, 2017

@SidMorad thank you! didn't know about heroku pg:backups:download


This comment has been minimized.

Copy link

@salvapineda salvapineda commented Sep 28, 2017

It asks for use and password, what should I put there?


This comment has been minimized.

Copy link

@benbartling benbartling commented Nov 17, 2017

Here is a rake task to perform this:

It has options to capture a new backup, download the latest backup, or just restore from an already downloaded backup.


This comment has been minimized.

Copy link

@vaskaloidis vaskaloidis commented Mar 17, 2018

@SidMorad So I had got an error: pg_restore: [archiver] unsupported version (1.12) in file header when I tried to run the PG Restorer locally.
Found out my Postgres Heroku vs. Local versions were off. Anyone running into the same error / issue, here is the fix:

First check your versions, verify they are off, and that's your problem:

pg_restore  --version

Returned 10.2 for me locally, so next I checked Heroku:

heroku run bash
pg_dump --version

It returned 10.3, so I simply upgraded my Local Postgres using Homebrew:

brew update postgresql

Then running pg_restore again afterwards worked fine for me!


This comment has been minimized.

Copy link

@aaronkelton aaronkelton commented Apr 23, 2018

@vaskaloidis the update command returns

Error: This command updates brew itself, and does not take formula names.
Use 'brew upgrade postgresql' instead.

For me it didn't work. brew upgrade postgresql returns "Error: postgresql 10.3 already installed". And pg_restore -V still returns "pg_restore (PostgreSQL) 9.6.5".

I ran brew doctor, which returned

Warning: You have unlinked kegs in your Cellar
Leaving kegs unlinked can lead to build-trouble and cause brews that depend on
those kegs to fail to run properly once built. Run `brew link` on these:

Running brew link postgresql returned "Error: Could not symlink bin/clusterdb", so I then ran brew link --overwrite postgresql to force the link and overwrite all conflicting files. That command returned "Linking /usr/local/Cellar/postgresql/10.3... 1665 symlinks created". Good to go!


This comment has been minimized.

Copy link

@valcaro87 valcaro87 commented Feb 12, 2019

why is it the sequence id's are not included? any help. thanks.


This comment has been minimized.

Copy link

@roostercrab roostercrab commented Jun 12, 2019

Heroku's instructions for anything are terrible, thank you to @SimMorad for finally giving me the commands that I have been digging for to quickly pull all the data from the Heroku's database, delete the database, rerun the migrations (for django but I imagine it's similar for ruby), then repopulate the data into Heroku's database.

For the question about where to get the database username and password, Heroku makes them up and changes them constantly for security so you have to get the most current ones each time. You do that by going to your app, then clicking on your Heroku Postgres link which should take you to which will have a bunch of numbers specific to your database. From there, click on Settings and then View Credentials... and populate the command with that information. It will ask you for your password after that. Here's the command from @SidMorad with the variables highlighted. I used it for django, the only difference for other frameworks would be in the migration command I imagine:

(-r development is because I have a pipeline for multiple apps, you can take it off if you only have one; or change it to what you called yours in heroku git)



heroku pg:backups:capture -r development
heroku pg:backups:download -r development

heroku pg:reset -r development

heroku run python migrate -r development

*** need to get credentials from heroku db and fill in the asterisked parts, names match what they are called in heroku ***
*** <--accessible from your app, Heroku Postgres then go to Settings, View Credentials...***
pg_restore --verbose --clean --no-acl --no-owner -h Host -U User -d Database latest.dump

It will look like this (my actual numbers were changed before posting, but it should look like random nonsense like this)
pg_restore --verbose --clean --no-acl --no-owner -h -U nvjrwueedkssdhd -d f88gu444c2vwdt latest.dump

Your command line will then ask you for the Password from heroku which is an extremely long series of characters from the same page that looks like this (again these are just random characters):

I have a theory that Heroku has made things difficult to learn on accident, and then people feel dumb because they can't figure it out. If you're in that position just know that you're not alone, it has been a year of suffering before I even started to get it to work in a reasonable fashion.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment