I need to transfer db from app_1 to app_2
I created backup on app_1
Then ran:
heroku pg:backups restore HEROKU_POSTGRESQL_COLOR --app app_2 heroku pgbackups:url --app app_1
HEROKU_POSTGRESQL_COLOR = database URL for app_2
Then I get:
! `pg:backups` is not a heroku command.
! Perhaps you meant `pgbackups`.
! See `heroku help` for a list of available commands.
So I ran:
heroku pgbackups:restore HEROKU_POSTGRESQL_COLOR --app app_2 heroku pgbackups:url --app app_1
Then I get the following:
! WARNING: Destructive Action
! This command will affect the app: app_2
! To proceed, type "app_2" or re-run this command with --confirm app_2
So I confirmed with:
> app_2
! Please add the pgbackups addon first via:
! heroku addons:add pgbackups
So then I ran: heroku addons:add pgbackups --app app_2
Adding pgbackups on app_2... failed
! Add-on plan not found.
Is there a way around this issue? any help would be greatly appreciated!
* Solution *
I ended up emailing Heroku, they advised that I need to heroku update; heroku plugins:update
but heroku update
is only available to heroku toolbelt only and I had the gem installed.
Solution:
Install Heroku toolbelt here
Then uninstall the gem:
gem uninstall heroku --all
run the following to get the version and it should output heroku-toolbelt
, instead of the gem, more info here
$ heroku --version
heroku-toolbelt/2.39.0 (x86_64-darwin10.8.0) ruby/1.9.3
To copy the databases over:
heroku pg:backups restore `heroku pgbackups:url --app app_1` HEROKU_POSTGRESQL_COLOR --app app_2
But even better—you can copy directly from one database to another without needing the backup:
Assuming app_2 database url is: HEROKU_POSTGRESQL_GOLD
heroku pg:copy app_1::DATABASE_URL GOLD -a app_2
That will copy the main database from app_1 to the GOLd database on app_2
Where the second db url is on app2_name
I found the simpler solution to reuse/share the same resource (postgres database in this case - or any others that allow sharing/reuse) with more than one app on heroku is doing the following:
Sample of the extended menu mentioned @ step #4 above!
That's all it takes to share the resource between the apps as it automatically updates all the related configuration settings on the target app. This comes handy since none of the add-on related configuration variables are directly editable, at least from the dashboard (have not checked through the CLI). Hope this helps anyone looking for similar thing.
I had a related issue. You can save a backup to your local machine, then upload it to some hosting, like amazon s3, and import from given url. This question and following answer may help you: Can't import to heroku postgres database from dump
You may find useful pgAdmin III (http://pgadmin.org/), a free db management tool specifically designed to do these types of tasks. You can edit/view/import/export from/to your Heroku db directly. Let me know if you need help in setup. (It's like MySQL Workbench, but for PostgreSQL).
If you look at
heroku docs
it saysSo you can use the
pgbackups functionality directly
without having to add any add-onsTo create a backup you can run
if you have multiple databases then you can specify database url like this
To
restore from a backup on another app
you can runYou can
transfer database
byYou can also
upload your database to a public url and then use that url to import database
on another app byand then
import
it byIf you are moving from one app to another and want to use same database for another app then you can follow these steps:
There are simple ways to do this, and there is a fast way to do it
The simple ways generally involve using a backup/restore methodology (including
pg:copy
, which is a backup that streams the data directly to apg_restore
process), but these are slow in creating the new database because you are restoring a logical definition of tables, loading the data, and creating indexes on the data.That's a lot of work, and for my 30GB standard-2 databases it can literally take hours.
The fast way to do it is to provision a follower of the database to be copied (e.g. production) on the application you want the data on (e.g. test). On the same 30GB databases that take hours to do a restore, this last took about 15 minutes.
The methodology I use is:
Why is this faster?
I believe that when creating a follower, Heroku creates the new database by either copying data files or restoring from a physical (file) backup, then replaying the logs to bring it up to date.