To copy database from one heroku app to another -
heroku pg:backups capture [database_name]
heroku pg:backups restore $(heroku pg:backups public-url --app source_app) DATABASE_URL --app target_app
You can refer to https://devcenter.heroku.com/articles/heroku-postgres-backups for more information.
To copy database from local to heroku -
Dump your local database in compressed format using the open source pg_dump tool: PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump
where myuser
is your database username and mydb
is the database name.
In order for PG Backups to access and import your dump file you will need to upload it somewhere with an HTTP-accessible URL. I used cloudinary, but you can use aws or anyone you prefer.
heroku pg:backups restore 'the dump url on cloudinary or aws' DATABASE_URL
e.g: heroku pg:backups restore 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump' DATABASE_URL
To access heroku database from command line -
heroku pg:psql --app app_name DATABASE_URL
I tried a number of suggested solutions and nothing worked. After not finding anything that actually works, I came up with the following and it worked like a charm! Hopefully, this helps someone else:
heroku pg:backups:restore source-app::b005 --app destination-app
b005 is the id of the backup for the source-app.