How do I transfer production database to staging on Heroku using pgbackups? Getting error
heroku, postgresql
Solution
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)
Problem
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?