postgresql Heroku 将数据库从一个应用程序转移到另一个应用程序

Heroku transfer db from one app to another

提问by neo

I need to transfer db from app_1 to app_2

我需要将 db 从 app_1 转移到 app_2

I created backup on app_1

我在 app_1 上创建了备份

Then ran:

然后跑：

heroku pg:backups restore HEROKU_POSTGRESQL_COLOR --app app_2 heroku pgbackups:url --app app_1

heroku pg:backups 恢复 HEROKU_POSTGRESQL_COLOR --app app_2 heroku pgbackups:url --app app_1

HEROKU_POSTGRESQL_COLOR = database URL for app_2

HEROKU_POSTGRESQL_COLOR = app_2 的数据库 URL

Then I get:

然后我得到：

 !    `pg:backups` is not a heroku command.
 !    Perhaps you meant `pgbackups`.
 !    See `heroku help` for a list of available commands.

So I ran:

所以我跑了：

heroku pgbackups:restore HEROKU_POSTGRESQL_COLOR --app app_2 heroku pgbackups:url --app app_1

heroku pgbackups：恢复 HEROKU_POSTGRESQL_COLOR --app app_2 heroku pgbackups:url --app app_1

Then I get the following:

然后我得到以下信息：

!    WARNING: Destructive Action
!    This command will affect the app: app_2
!    To proceed, type "app_2" or re-run this command with --confirm app_2

So I confirmed with:

所以我确认了：

> app_2
 !    Please add the pgbackups addon first via:
 !    heroku addons:add pgbackups

So then I ran: heroku addons:add pgbackups --app app_2

然后我跑了： heroku addons:add pgbackups --app app_2

Adding pgbackups on app_2... failed
 !    Add-on plan not found.

Is there a way around this issue? any help would be greatly appreciated!

有没有办法解决这个问题？任何帮助将不胜感激！

* Solution *

* 解决方案 *

I ended up emailing Heroku, they advised that I need to heroku update; heroku plugins:updatebut heroku updateis only available to heroku toolbelt only and I had the gem installed.

我最终给 Heroku 发了电子邮件，他们建议我需要，heroku update; heroku plugins:update但heroku update仅适用于 heroku 工具带，并且我安装了 gem。

Solution:

解决方案：

Install Heroku toolbelt here

在此处安装 Heroku 工具带

Then uninstall the gem:

然后卸载 gem：

gem uninstall heroku --all

run the following to get the version and it should output heroku-toolbelt, instead of the gem, more info here

运行以下命令来获取版本，它应该输出heroku-toolbelt，而不是 gem，更多信息在这里

$ heroku --version
  heroku-toolbelt/2.39.0 (x86_64-darwin10.8.0) ruby/1.9.3

To copy the databases over:

复制数据库：

heroku pg:backups restore `heroku pgbackups:url --app app_1` HEROKU_POSTGRESQL_COLOR --app app_2

But even better—you can copy directly from one database to another without needing the backup:

但更好的是，您可以直接从一个数据库复制到另一个数据库，而无需备份：

Assuming app_2 database url is: HEROKU_POSTGRESQL_GOLD

假设 app_2 数据库 url 是： HEROKU_POSTGRESQL_GOLD

heroku pg:copy app_1::DATABASE_URL GOLD -a app_2 

That will copy the main database from app_1 to the GOLd database on app_2

这会将主数据库从 app_1 复制到 app_2 上的 GOLd 数据库

回答by mohamed-ibrahim

its only 1 command to copy database from app to app now you don't have to backup:

它唯一的 1 个命令可以将数据库从应用程序复制到应用程序，现在您不必备份：

heroku pg:copy app_name_to_copy_from::database_color_to_copy_from database_color_to_copy_to --app app_name_to_copy_to

check it here

在这里检查

回答by Mandeep

If you look at heroku docsit says

如果你看heroku docs它说

PG Backups as an add-on has been deprecated. The commands exist as part of the Heroku Postgres namespace in the CLI. The new functionality is live and available for use.

PG 备份作为附加组件已被弃用。这些命令作为 CLI 中 Heroku Postgres 命名空间的一部分存在。新功能已上线并可供使用。

So you can use the pgbackups functionality directlywithout having to add any add-ons

因此您pgbackups functionality directly无需添加任何附加组件即可使用

To create a backup you can run

要创建备份，您可以运行

 heroku pg:backups capture --app app_name

if you have multiple databases then you can specify database url like this

如果你有多个数据库，那么你可以像这样指定数据库 url

heroku pg:backups capture HEROKU_POSTGRESQL_PINK

To restore from a backup on another appyou can run

为了restore from a backup on another app您可以运行

heroku pg:backups restore b001 DATABASE_URL --app app_name

You can transfer databaseby

你可以transfer database通过

heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_PINK_URL --app app_name

You can also upload your database to a public url and then use that url to import databaseon another app by

您也可以upload your database to a public url and then use that url to import database在另一个应用程序上通过

heroku pg:backups public-url b001 --app app_name

and then importit by

然后import通过

heroku pg:backups restore 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump' DATABASE -a app_name

If you are moving from one app to another and want to use same database for another app then you can follow these steps:

如果您要从一个应用程序转移到另一个应用程序并希望为另一个应用程序使用相同的数据库，那么您可以按照以下步骤操作：

• Login to your heroku account
• Select your old app and go to settings tab
• Reveal config vars for your old app
• Copy DATABASE_URL
• Go back and select your new app
• Replace new apps DATABASE_URL with the old apps value

• 登录您的 heroku 帐户
• 选择您的旧应用程序并转到设置选项卡
• 显示旧应用程序的配置变量
• 复制 DATABASE_URL
• 返回并选择您的新应用
• 用旧的应用程序值替换新应用程序 DATABASE_URL

回答by pixelearth

heroku pg:copy app1_name::HEROKU_POSTGRESQL_ONYX_URL HEROKU_POSTGRESQL_AQUA_URL --app app2_name

Where the second db url is on app2_name

第二个 db url 在 app2_name 上

回答by Narayanan R

I found the simpler solution to reuse/share the same resource (postgres database in this case - or any others that allow sharing/reuse) with more than one app on heroku is doing the following:

我发现在 heroku 上使用多个应用程序重用/共享相同资源（在这种情况下为 postgres 数据库 - 或任何其他允许共享/重用的数据库）的更简单解决方案正在执行以下操作：

1. Go to the older (source) app dashboard on Heroku
2. Select the "Resources" tab
3. Locate the resource (postgres database, in our case here)
4. Click on the icon next to the plan name at the right most part of the row listing the resource
5. Select the "Attach to another app" option and select the newer (target) app name from the list that shows up

1. 转到 Heroku 上较旧的（源）应用程序仪表板
2. 选择“资源”选项卡
3. 找到资源（postgres 数据库，在我们这里的例子中）
4. 单击列出资源的行最右侧的计划名称旁边的图标
5. 选择“附加到另一个应用程序”选项，然后从显示的列表中选择较新的（目标）应用程序名称

Sample of the extended menu mentioned @ step #4 above!

上面提到的@步骤#4 的扩展菜单示例！

That's all it takes to share the resource between the apps as it automatically updates all the related configuration settings on the target app. This comes handy since none of the add-on related configuration variables are directly editable, at least from the dashboard (have not checked through the CLI). Hope this helps anyone looking for similar thing.

这就是在应用程序之间共享资源所需的全部内容，因为它会自动更新目标应用程序上的所有相关配置设置。这很方便，因为任何与附加组件相关的配置变量都不能直接编辑，至少从仪表板（尚未通过 CLI 检查）。希望这可以帮助任何寻找类似事物的人。

回答by coderhs

As per the website the addon is depreciated. So that could be the reason for the failure message.

根据网站，该插件已折旧。所以这可能是失败消息的原因。

Backups as an add-on has been deprecated.

作为附加组件的备份已被弃用。

Since your aim is to move the db from one app to another, why don't you try the instructions mentioned in the link below.

由于您的目标是将数据库从一个应用程序移动到另一个应用程序，为什么不尝试下面链接中提到的说明。

https://devcenter.heroku.com/articles/heroku-postgres-backups#direct-database-to-database-copies

https://devcenter.heroku.com/articles/heroku-postgres-backups#direct-database-to-database-copies

回答by Danny Ocean

I had a related issue. You can save a backup to your local machine, then upload it to some hosting, like amazon s3, and import from given url. This question and following answer may help you: Can't import to heroku postgres database from dump

我有一个相关的问题。您可以将备份保存到本地计算机，然后将其上传到某个主机，例如 amazon s3，并从给定的 url 导入。此问题和以下答案可能对您有所帮助：Can't import to heroku postgres database from dump

回答by David Aldridge

There are simple ways to do this, and there is a fast way to do it

有一些简单的方法可以做到这一点，并且有一种快速的方法来做到这一点

The simple ways generally involve using a backup/restore methodology (including pg:copy, which is a backup that streams the data directly to a pg_restoreprocess), but these are slow in creating the new database because you are restoring a logical definition of tables, loading the data, and creating indexes on the data.

简单的方法通常涉及使用备份/恢复方法（包括pg:copy将数据直接流式传输到pg_restore进程的备份），但这些方法在创建新数据库时很慢，因为您正在恢复表的逻辑定义，加载数据，并在数据上创建索引。

That's a lot of work, and for my 30GB standard-2 databases it can literally take hours.

这是很多工作，对于我的 30GB 标准 2 数据库，它实际上可能需要几个小时。

The fast way to do it is to provision a follower of the database to be copied (e.g. production) on the application you want the data on (e.g. test). On the same 30GB databases that take hours to do a restore, this last took about 15 minutes.

这样做的快速方法是在您想要数据的应用程序（例如测试）上提供要复制（例如生产）的数据库的追随者。在同样需要数小时才能进行恢复的 30GB 数据库上，这最后需要大约 15 分钟。

The methodology I use is:

我使用的方法是：

# Get the name of the source database addon (e.g. postgresql-clean-12345)
heroku pg:info -a production-app

# Create a follower on the destination app (choose your own plan)
# You create the follower on the new app because otherwise it is 
# perpetually associated with the source as it's "billing app"
heroku addons:create heroku-postgresql:standard-2 --follow postgresql-clean-12345 -a test-app
heroku pg:wait -a test-app

# Quiesce the destination app
heroku scale web=0 worker=0 -a test-app
heroku maintenance:on -a test-app

# Get the colour of the new database (e.g. HEROKU_POSTGRESQL_GRAY_URL)
heroku pg:info -a test-app

# Unfollow the source database.
# If you want to upgrade the database, do that now instead of the
# unfollow.
heroku pg:unfollow HEROKU_POSTGRESQL_GRAY_URL -a test-app

# Promote the new database on the destination app
heroku pg:promote HEROKU_POSTGRESQL_GRAY_URL -a test-app

# Get the colour of the old database, if any(e.g. HEROKU_POSTGRESQL_MAROON_URL)
heroku pg:info -a test-app

# Destroy the old database (if any)
heroku addons:destroy HEROKU_POSTGRESQL_MAROON_URL -a test-app

# Bring the test app back up
heroku scale web=1 worker=1 -a test-app
heroku maintenance:off -a test-app

Why is this faster?

为什么这样更快？

I believe that when creating a follower, Heroku creates the new database by either copying data files or restoring from a physical (file) backup, then replaying the logs to bring it up to date.

我相信在创建跟随者时，Heroku 通过复制数据文件或从物理（文件）备份中恢复来创建新数据库，然后重播日志以使其更新。

回答by Elvn

You may find useful pgAdmin III (http://pgadmin.org/), a free db management tool specifically designed to do these types of tasks. You can edit/view/import/export from/to your Heroku db directly. Let me know if you need help in setup. (It's like MySQL Workbench, but for PostgreSQL).

您可能会发现有用的 pgAdmin III ( http://pgadmin.org/)，这是一个免费的数据库管理工具，专门设计用于执行这些类型的任务。您可以直接编辑/查看/导入/导出到您的 Heroku 数据库。如果您在设置方面需要帮助，请告诉我。（它类似于 MySQL Workbench，但适用于 PostgreSQL）。