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

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)

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

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

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.

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

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

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

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

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

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

$ 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 

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

So things are even easier now .. checkout the transfer command as part of 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

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

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

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> 

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.

First create an up to date backup of production:

heroku pgbackups:capture -a productionappslug --expire 

Find out what colour Heroku has named your database.

https://postgres.heroku.com/databases or 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` 

stagingappslug and liveappslug are whatever shortnames your heroku apps are called.

To transfer (copy) the production database (source database) to the staging database (target database) you will need to invoke pg:copy from the target application, referencing a source database.

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

To obtain the colour names of your databases, use:

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

See pg:copy

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

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.

I think its not --remote its --app try this:

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

This is what worked for me

• Download the backup from heroku console and uploaded it to s3.
• heroku pg:backups restore 'DUMP_FILE_URL_FROM_S3' DATABASE --app MY_APP

None, of the answers worked for me in 2023 or copied the database directly (which slowed down production for us), so I took another look at Heroku's PGBackups documentation, and pieced the following together:

heroku pg:backups:restore `heroku pg:backups:url --app production-app | cat` DATABASE_URL --app staging-app 

This command will get the publicly accessible backup URL of your latest backup of your production app. It will then restore that backup to your staging app.

For this command to work for you, you need to replace the name of your production and staging app. You can probably leave DATABASE_URL as is. It is a shorthand to identify your database, and the first one you create is called DATABASE by default. If you have multiple databases, find out the name of yours with:

heroku pg --app staging-app 

and then change the command to replace the database name:

heroku pg:backups:restore `heroku pg:backups:url --app production-app | cat` HEROKU_POSTGRESQL_COBALT_URL --app staging-app 

To transfer (copy) a production database to staging, you can use the pg:copy command (see Documentation).

1. Get the shortcut names of the Postgres databases of the respective app by running heroku addons --app myapp_production and heroku addons --app myapp_staging. In my case it was DATABASE for both apps.

2. Run heroku pg:copy myapp_production::DATABASE DATABASE --app myapp_staging. Note that the shortcut names are confusing since I have DATABASE twice, which refer to different databases. Double-check the command for correctness.

3. You will be warned that you are about to perform a desctructive action.

   » Warning: WARNING: Destructive action

   » This command will remove all data from DATABASE

   » Data from DATABASE will then be transferred to DATABASE

   Depending on your shortcut names this message may be misleading, but it is fine so long you followed the prior steps correctly.

4. As requested by the terminal, type myapp_staging to confirm copying the database from myapp_production to myapp_staging. This will create the tables and populate them with identical data.