postgresql 如何从heroku下载db？

How can I download db from heroku?

提问by Sascuash

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:urldownloads a .dump file

我正在使用 heroku 并且我想从我的应用程序（heroku）下载数据库，以便我可以对其进行一些更改，我已经安装了 pgbackups，但是使用heroku pgbackups:url下载了一个 .dump 文件

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

如何下载 postgresql 文件或将该 .dump 转换为 postgresql 文件？

回答by AlexQueue

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

如果您正在使用 Heroku 的 pgbackups（您可能应该使用）：

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

"Translate" it into a postgres db with

将其“翻译”为 postgres 数据库

$ 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

请参阅https://devcenter.heroku.com/articles/heroku-postgres-import-export

回答by John Beynon

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

在 CLI 中有一个用于此的命令 -heroku db:pull它将为您执行此操作。db:pull可能有点慢，所以你最好使用下一个选项。

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-transferwhich will basically does a backup on Heroku and a restores it locally.

如果您使用复杂的 postgress 数据类型（hstore、数组等），那么您需要使用 pgtransfer 插件https://github.com/ddollar/heroku-pg-transfer，它基本上会在 Heroku 上进行备份并在本地恢复它.

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

更新：db:pull并db:push已被弃用，应替换为pg:pull和pg:push- 在https://devcenter.heroku.com/articles/heroku-postgresql#pg-push-and-pg-pull阅读更多

回答by J0ANMM

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

我发现文档 pull/push 中建议的第一种方法更容易。不需要密码或用户名。

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_URLfrom 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.

PG：拉

pg:pull 可用于将远程数据从 Heroku Postgres 数据库拉到本地机器上的数据库中。该命令如下所示：

$ heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

此命令将创建一个名为“mylocaldb”的新本地数据库，然后DATABASE_URL从应用程序“sushi”的数据库中提取数据。为了防止意外的数据覆盖和丢失，本地数据库必须不存在。在继续之前，系统将提示您删除现有的本地数据库。

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

起初我有一个错误：/bin/sh: createdb: command not found; 我在这个 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

要从 Heroku Postgres 数据库导出数据，请创建新备份并下载。

$ heroku pg:backups:capture

$ heroku pg:backups:download

Source: Importing and Exporting Heroku Postgres Databases with PG Backups

来源：使用 PG 备份导入和导出 Heroku Postgres 数据库

回答by MZaragoza

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

回答by svikramjeet

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

要从 Heroku Postgres 数据库导出数据，只需按照以下步骤操作

1. Login to heroku
2. Go to APP->settings->reveal config variable
3. Copy DATABASE_URL
4. run pg_dump --DATABASE_URL_COPIED_IN_STEP_3 > database_dump_file

1. 登录到heroku
2. 转到APP->设置->显示配置变量
3. 复制 DATABASE_URL
4. 跑 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.

请注意，这将提供 postgresql 文件或转储文件，您可以直接从 postgres 插件界面下载。

回答by niraj

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

阅读本文档了解更多信息：https: //devcenter.heroku.com/articles/heroku-postgresql#pg-push-and-pg-pull