postgresql 如何使用 pgbackups 将生产数据库转移到 Heroku 上的登台？获取错误

How do I transfer production database to staging on Heroku using pgbackups? Getting error

提问by Luciano

On Heroku, I am trying to copy the production database into my staging app using the pgbackups addon. I followed the instructions on the addon page: https://devcenter.heroku.com/articles/pgbackups

在 Heroku 上，我尝试使用 pgbackups 插件将生产数据库复制到我的登台应用程序中。我按照插件页面上的说明进行操作：https: //devcenter.heroku.com/articles/pgbackups

First I captured the DB:

首先我捕获了数据库：

heroku pgbackups:capture --app production-app 

That worked:

那有效：

HEROKU_POSTGRESQL_PURPLE (DATABASE_URL)  ----backup--->  b238

Capturing... done
Storing... done

However when I try to restore it on the staging app:

但是，当我尝试在登台应用程序上恢复它时：

heroku pgbackups:restore DATABASE `heroku pgbackups:url --app production-app` --remote staging

I get the following error message:

我收到以下错误消息：

DATABASE_URL does not match any of your databases
 !    Could not resolve database DATABASE
 !    
 !    Available databases: 

I have also tried typing in the full URL:

我也试过输入完整的 URL：

 heroku pgbackups:url b238 --app production-app
 heroku pgbackups:restore DATABASE "https://s3.amazonaws.com/..." --remote staging

and also tried naming the app (instead of --remote staging):

并尝试命名应用程序（而不是 --remote staging）：

heroku pgbackups:restore DATABASE `heroku pgbackups:url --app production-app` --app staging-app

None of these worked. It's interesting to note that the error message says there are no "Available databases". I'm assuming it is referring to the staging app which is indeed empty. If I type:

这些都没有奏效。有趣的是，错误消息指出没有“可用的数据库”。我假设它指的是确实为空的登台应用程序。如果我输入：

heroku pgbackups

I get:

我得到：

 !    No backups. Capture one with `heroku pgbackups:capture`.

To find the available backups (production), I need to type:

要找到可用的备份（生产），我需要输入：

heroku pgbackups --app production-app

and I get the list of current backups. I don't know if this is normal or even if it is related to the problem, but I thought I should mention it.

我得到了当前备份的列表。我不知道这是否正常，或者是否与问题有关，但我想我应该提一下。

I have read and tried every answer here on SO but nothing worked. Any ideas?

我已经阅读并尝试了这里的所有答案，但没有任何效果。有任何想法吗？

回答by Lucas Nelson

Update for mid-2017 (stealing from Takehiro Mouri's answer - simplify the DATABSE_NAME part)

2017 年年中的更新（从Takehiro Mouri的答案中窃取- 简化 DATABSE_NAME 部分）

Update for mid-2015...

2015 年年中更新...

The pgbackups add-on has been deprecated. No more pgbackups:transfer.

pgbackups 附加组件已被弃用。没有了pgbackups:transfer。

To copy a database from yourappto yourapp_staging:

将数据库从yourapp复制到yourapp_staging：

# turn off the web dynos in staging
heroku maintenance:on -a yourapp-staging

# if you have non-web-dynos, do them too
heroku ps:scale worker=0 -a yourapp-staging

# backup the staging database if you are paranoid like me (optional)
heroku pg:backups capture -a yourapp-staging

# execute the copy
heroku pg:copy your-app::DATABASE_URL DATABASE_URL -a yourapp-staging

Then when it's complete, turn staging back on:

然后当它完成时，重新打开暂存：

# this is if you have workers, change '1' to whatever
heroku ps:scale worker=1 -a yourapp-staging

heroku maintenance:off -a yourapp-staging

(source: https://devcenter.heroku.com/articles/upgrading-heroku-postgres-databases#upgrade-with-pg-copy-default)

（来源：https: //devcenter.heroku.com/articles/upgrading-heroku-postgres-databases#upgrade-with-pg-copy-default）

回答by Takehiro Mouri

UPDATE: You can run this command to transfer the database from production to staging: heroku pg:copy your-app::DATABASE_URL DATABASE_URL -a yourapp-staging

更新：您可以运行此命令将数据库从生产转移到暂存： heroku pg:copy your-app::DATABASE_URL DATABASE_URL -a yourapp-staging

It will prompt you to confirm the action, and once you have done that, all of the data will be migrated.

它将提示您确认操作，一旦您确认，所有数据都将被迁移。

回答by Jonathon Batson

UPDATE: This no longer works. Please refer to @lucas-nelson's answerbelow.

更新：这不再有效。请参考下面@lucas-nelson 的回答。

So things are even easier now .. checkout the transfer command as part of pgbackups

所以现在事情变得更容易了..检查传输命令作为 pgbackups 的一部分

heroku pgbackups:transfer HEROKU_POSTGRESQL_PINK sushi-staging::HEROKU_POSTGRESQL_OLIVE -a sushi

https://devcenter.heroku.com/articles/upgrade-heroku-postgres-with-pgbackups#transfering-databases-between-heroku-applications

https://devcenter.heroku.com/articles/upgrade-heroku-postgres-with-pgbackups#transfering-databases-between-heroku-applications

This has worked beautifully for me taking production code back to my staging site.

这对我将生产代码带回我的临时站点非常有效。

回答by davb

Here's a simple and safe solution to this using Heroku's add-on attachments and forking. It does not require backups, no downtime, and does not overwrite any database.

这是使用 Heroku 的附加附件和分叉的简单而安全的解决方案。它不需要备份，没有停机时间，也不会覆盖任何数据库。

You need to attach the production database to the staging app first, then fork on the staging app.If you fork on the production app, then attach to the staging app, the billing app will be the production app and you won't be able to detach the fork from it.

您需要先将生产数据库附加到暂存应用程序，然后在暂存应用程序上分叉。如果您在生产应用程序上分叉，然后附加到临时应用程序，计费应用程序将是生产应用程序，您将无法从中分离分叉。

1.First find out the add-on name of your production database (here it is postgres-prod-123):

1.首先找出您的生产数据库的附加组件名称（这里是postgres-prod-123）：

$ heroku addons --app myapp-production
heroku-postgresql (postgresql-prod-123)  standard-0  /month
 └─ as DATABASE

2.Then attachthe production database add-on to your staging app. Give it a name like PRODUCTION_DBto make it easy to recognize:

2.然后将生产数据库附加组件附加到您的登台应用程序。给它起一个像PRODUCTION_DB这样容易识别的名字：

$ heroku addons:attach postgresql-prod-123 --app myapp-staging --as PRODUCTION_DB

3.Then create a forkof the production database on the staging app:

3.然后在暂存应用程序上创建生产数据库的分支：

$ heroku addons:create heroku-postgresql:standard-0 --fork PRODUCTION_DB_URL --as STAGING_DB --app myapp-staging

4.Finally promote the fork to be the primary database of your staging app:

4.最后将 fork 提升为您的登台应用程序的主要数据库：

$ heroku pg:promote STAGING_DB --app myapp-staging

Done!Your staging app is now using a copy of your production database. Note that your previous staging database is still there, you may want to destroy it after you've made sure everything works.

完毕！您的暂存应用程序现在正在使用您的生产数据库的副本。请注意，您之前的临时数据库仍然存在，您可能希望在确保一切正常后销毁它。

To clean up, detach the production database from the staging app:

要清理，请从暂存应用程序中分离生产数据库：

$ heroku addons:detach postgresql-prod-123 --app myapp-staging

回答by Josh

This works for me: heroku pg:copy you-app-production::DATABASE DATABASE -a you-app-staging

这对我有用： heroku pg:copy you-app-production::DATABASE DATABASE -a you-app-staging

回答by Sanjay Prajapati

You can do this using below command

您可以使用以下命令执行此操作

heroku pg:copy <production_app_name>::HEROKU_POSTGRESQL_BLACK_URL OLIVE -a <staging_app_name> --confirm <staging_app_name>

回答by Daniel Morris

First create an up to date backup of production:

首先创建一个最新的生产备份：

heroku pgbackups:capture -a productionappslug --expire

Find out what colour Heroku has named your database.

找出 Heroku 为您的数据库命名的颜色。

https://postgres.heroku.com/databasesor https://dashboard.heroku.com/apps/STAGINGAPPSLUG/resources

https://postgres.heroku.com/databases或 https://dashboard.heroku.com/apps/STAGINGAPPSLUG/resources

Then load the production db backup into staging (changing RED to whatever colour yours is):

然后将生产数据库备份加载到暂存中（将红色更改为您的任何颜色）：

heroku pgbackups:restore HEROKU_POSTGRESQL_RED -a stagingappslug `heroku pgbackups:url -a productionappslug`

stagingappslugand liveappslugare whatever shortnames your heroku apps are called.

stagingappslug和liveappslug是您的 heroku 应用程序的简称。

回答by user664833

To transfer (copy) the productiondatabase (sourcedatabase) to the stagingdatabase (targetdatabase) you will need to invoke pg:copyfrom the targetapplication, referencing a sourcedatabase.

要将生产数据库（源数据库）传输（复制）到临时数据库（目标数据库），您需要pg:copy从目标应用程序调用，引用源数据库。

heroku pg:copy source-application::OLIVE HEROKU_POSTGRESQL_PINK -a target-application

heroku pg:copy source-application::OLIVE HEROKU_POSTGRESQL_PINK -a target-application

Another example:

另一个例子：

heroku pg:copy my-production-app::HEROKU_POSTGRESQL_OLIVE HEROKU_POSTGRESQL_PINK --app my-staging-app

heroku pg:copy my-production-app::HEROKU_POSTGRESQL_OLIVE HEROKU_POSTGRESQL_PINK --app my-staging-app

To obtain the colour namesof your databases, use:

要获取数据库的颜色名称，请使用：

heroku pg --app my-production-app
heroku pg --app my-staging-app

See pg:copy

见pg:copy

回答by kbjerring

I was struggling with the same issue. According to the answer to this question, the problem could be your heroku gem version. I just upgraded my version (from 2.26.2 to 2.26.6) and now it works.

我正在努力解决同样的问题。根据这个问题的答案，问题可能出在你的 heroku gem 版本上。我刚刚升级了我的版本（从 2.26.2 到 2.26.6），现在它可以工作了。

回答by Evolve

After NO LUCK. (Im using heroku gem 2.31.4) I did the following (help for the weary)

在没有运气之后。（我使用heroku gem 2.31.4）我做了以下（疲倦的帮助）

1. Login into Heroku Database console

2. Login to staging > 'settings' > PGRestore > Copy 'Connection Settings' into a text file.

3. Login to production > Snapshots, press '+' to make a new backup as of now. Then press download. Download into the apps /tmp folder or whever you want.

4. Set staging to maintenance mode

   $ heroku maintenance:on

5. Run the command like so, with Connection Settings text and dump file at the end: PGPASSWORD={...bits of stuff here...} -p 5432 'tmp/b048.dump.dump'

6. After run:

   $ heroku maintenance:off

7. Login to staging and check that things match. Find a recent transaction you know is in production if you can via. $ heroku run console for both apps and check ids match.

1. 登录 Heroku 数据库控制台

2. 登录 staging > 'settings' > PGRestore > Copy 'Connection Settings' 到一个文本文件中。

3. 登录到生产 > 快照，按“+”创建一个新的备份。然后按下载。下载到 apps /tmp 文件夹或任何你想要的地方。

4. 将暂存设置为维护模式

   $ heroku 维护：开

5. 像这样运行命令，最后是连接设置文本和转储文件：PGPASSWORD={...这里有一些东西...} -p 5432 'tmp/b048.dump.dump'

6. 运行后：

   $ heroku 维护：关闭

7. 登录登台并检查是否匹配。如果可以，请查找您知道正在生产的最近交易。$ heroku 为两个应用程序运行控制台并检查 ID 是否匹配。