postgresql – How can I download db from heroku?

postgresql – How can I download db from heroku?

If youre using Herokus 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

Theres 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

postgresql – How can I download db from heroku?

I found the first method suggested in the documentation pull/push even easier. No password or username needed.

pg:pull

pg:pull can be used to pull remote data from a Heroku Postgres
database to a database on your local machine. The command looks like
this:

$ heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

This command will create a new local database named “mylocaldb” and
then pull data from database at DATABASE_URL from the app “sushi”. In
order to prevent accidental data overwrites and loss, the local
database must not exist. You will be prompted to drop an already
existing local database before proceeding.

At first I had an error: /bin/sh: createdb: command not found; which I solved following this SO post.


An alternative described also in the documentation (I did not try it yet) is:

To export the data from your Heroku Postgres database, create a new
backup and download it.

$ heroku pg:backups:capture

$ heroku pg:backups:download

Source: Importing and Exporting Heroku Postgres Databases with PG Backups

Leave a Reply

Your email address will not be published.