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?

score 65 · Accepted Answer · edited Aug 17 '15 at 17:12

65

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

edited Aug 17 '15 at 17:12

benilov

1,234
11
11

answered Jun 13 '13 at 19:05

AlexQueue

6,353
5
35
44

pgbackups do not exist any longer, now it seems to be pg:backups. And there is no pg:backups:url. – fotanus Aug 03 '15 at 22:15
6

The first two commands are apparently verbatim, but in the third, I had to replace `myuser` with my Postgres username and `mydb` with the name of the database I wanted to transcribe the data into. – februaryInk Jan 22 '16 at 16:50

score 14 · Answer 2 · edited Dec 27 '18 at 17:52

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

score 6 · Answer 3 · edited May 23 '17 at 12:34

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

score 4 · Answer 4 · answered Apr 09 '19 at 05:42

To export the data from Heroku Postgres database, just follow below steps

Login to heroku
Go to APP->settings->reveal config variable
Copy DATABASE_URL
run pg_dump --DATABASE_URL_COPIED_IN_STEP_3 > database_dump_file

Note this will provide postgresql file or for dump file you can download directly from postgres addon interface.

score 0 · Answer 5 · answered Jul 29 '16 at 05:49

I think the easiest way to download and replicate the database on local server:

 **PGUSER**=LOCAL_USER_NAME PGPASSWORD=LOCAL_PASSWORD heroku pg:pull --app APP_NAME HEROKU_POSTGRESQL_DB_NAME LOCAL_DB_NAME

Go through this document for more info: https://devcenter.heroku.com/articles/heroku-postgresql#pg-push-and-pg-pull

score 0 · Answer 6 · answered Apr 09 '19 at 05:04

This is the script that I like to use.

namespace :heroku do

  desc "Import most recent database dump"
  task :import_from_prod => :environment do
    puts 'heroku run pg:backups capture --app APPNAME'
    restore_backup 'APPNAME'
  end

  def path_to_heroku
    ['/usr/local/heroku/bin/heroku', '/usr/local/bin/heroku'].detect {|path| File.exists?(path)}
  end

  def heroku(command, site)
    `GEM_HOME='' BUNDLE_GEMFILE='' GEM_PATH='' RUBYOPT='' #{path_to_heroku} #{command} -a #{site}`
  end

  def restore_backup(site = 'APPNAME')
    dump_file = "#{Rails.root}/tmp/postgres.dump"
    unless File.exists?(dump_file)
      pgbackups_url = heroku('pg:backups public-url -q', site).chomp
      puts "curl -o #{dump_file} #{pgbackups_url}"
      system "curl -o #{dump_file} '#{pgbackups_url}'"
    end
    database_config = YAML.load(File.open("#{Rails.root}/config/database.yml")).with_indifferent_access
    dev_db = database_config[Rails.env]
    system "pg_restore -d #{dev_db[:database]} -c #{dump_file}".gsub(/\s+/,' ')
    puts
    puts "'rm #{dump_file}' to redownload postgres dump."
    puts "Done!"
  end
end

score 0 · Answer 7 · answered Aug 14 '23 at 21:32

For me a simple heroku pg:pull DATABASE local-db-name -a app-name worked.

Warning: I do mean just the word "DATABASE", not the name of the database or anything fancy. If you run rake db:drop right before, it will display your local database name, which should be something like ABC_development.

How can I download db from heroku?

7 Answers7