Backup Heroku Postgres database and restore to local database

gistfile1.md

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

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

Worked nicely, thanks!

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

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

Awesome! 👍

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.

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

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

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.

@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!

@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!

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

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.