How can I download db from heroku?

Question

I'm using heroku and I want to download the database from my app(heroku) so I can make some changes in it, I've installed pgbackups, but using heroku pgbackups:url downloads a .dump file

How can I download a postgresql file or translate that .dump into a postgresql file?

Answer 1

If you're using Heroku's pgbackups (which you probably should be using):

$ heroku pg:backups capture $ curl -o latest.dump `heroku pg:backups public-url` 

"Translate" it into a postgres db with

$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump 

See https://devcenter.heroku.com/articles/heroku-postgres-import-export

Answer 2

There's a command for this in the CLI - heroku db:pull which will do this for you. db:pull can be a bit slow mind you so you may be better to use the next option.

If you are using complex postgress data types (hstore, arrays etc) then you need to use the pgtransfer plugin https://github.com/ddollar/heroku-pg-transfer which will basically does a backup on Heroku and a restores it locally.

UPDATE: db:pull and db:push have been deprecated and should be replaced with pg:pull and pg:push - read more at https://devcenter.heroku.com/articles/heroku-postgresql#pg-push-and-pg-pull