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

Question

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

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:

 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):

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?

Answer 1

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

Update for mid-2015...

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

To copy a database from yourapp to 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)