如何从heroku下载db？

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

我正在使用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文件？

4 个解决方案

#1

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

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

$ 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

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

在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-transfer which 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: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

更新：db：pull和db：push已被弃用，应替换为pg：pull和pg：push - 更多信息，请访问https://devcenter.heroku.com/articles/heroku-postgresql#pg-push-and- PG-拉

#3

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

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

pg:pull

PG：拉

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:

pg：pull可用于将远程数据从Heroku Postgres数据库提取到本地计算机上的数据库。该命令如下所示：

$ heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

$ heroku pg：拉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.

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

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帖子后解决了这个问题。

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 Postgres数据库导出数据，请创建一个新备份并下载它。
$ heroku pg:backups:capture

$ heroku pg:backups:download

Source: Importing and Exporting Heroku Postgres Databases with PG Backups

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

#4

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

#1