Skip to content

Instantly share code, notes, and snippets.

Embed
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

>>> "https://s3.amazonaws.com/hkpgbackups/app11111118@heroku.com/a712.dump?AWSAccessKeyId=XXXXXXXXXXXXXX&Expires=1367876175&Signature=XXXXXXX%EEEEEEEYWWWWWWW%3D"

Download backup dump file

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

Example: curl "https://s3.amazonaws.com/hkpgbackups/app11111118@heroku.com/a712.dump?AWSAccessKeyId=XXXXXXXXXXXXXX&Expires=1367876175&Signature=XXXXXXX%EEEEEEEYWWWWWWW%3D" > production.dump

Restore backup dump into local db

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

@siruguri

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

and

heroku pg:backups public-url

See mapping list on Heroku: https://devcenter.heroku.com/articles/mapping-pgbackups-commands#creating-a-backup

@swstack

This comment has been minimized.

Copy link

@swstack swstack commented Jul 16, 2015

Worked nicely, thanks!

@joel

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

@sfandrew

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?

@Samarththebox

This comment has been minimized.

Copy link

@Samarththebox Samarththebox commented Dec 13, 2016

Awesome! 👍

@SidMorad

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.

@iabdulin

This comment has been minimized.

Copy link

@iabdulin iabdulin commented Jun 17, 2017

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

@salvapineda

This comment has been minimized.

Copy link

@salvapineda salvapineda commented Sep 28, 2017

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

@benbartling

This comment has been minimized.

Copy link

@benbartling benbartling commented Nov 17, 2017

Here is a rake task to perform this: https://gist.github.com/benbartling/c03de6b43bd5c150d717bc92c6017dd1

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

@vaskaloidis

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
exit

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!

@aaronkelton

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:
  postgresql

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!

@valcaro87

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.

@roostercrab

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 https://data.heroku.com/datastores/ 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)

WAY TO BACKUP, DELETE, MIGRATE (DJANGO) AND THEN RESTORE ORIGINAL POSTGRES DB DATA FOR HEROKU

THIS WILL DELETE YOUR DATA BEFORE YOU RESTORE IT SO PRACTICE ON SOMETHING INCONSEQUENTIAL BEFORE DOING ON SOMETHING THAT YOU CAN'T AFFORD TO LOSE DATA ON!!!

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

heroku pg:reset -r development

heroku run python manage.py migrate -r development

*** need to get credentials from heroku db and fill in the asterisked parts, names match what they are called in heroku ***
*** https://data.heroku.com/datastores/ <--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 ec2-34-267-351-999.compute-3.amazonaws.com -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):
4f69ba471fdb64a9b11a3b739bc83382952a6284c20333a4ad8893bcf221a24dc

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