Will a Heroku database exist after it's app is deleted - and can I transfer it to another app?

Question

I have a Heroku app that is running on the Cedar-10 stack which will soon be deprecated. I am following the Migrating to the Celadon Cedar-14 Stack guide and have created an instance of my app on the new stack. However, this has also created another (empty) PostgreSQL database automatically.

• Can I upgrade to the new stack but continue to use the existing database?
• When I delete app on the old stack, will it delete the database that is associated with it automatically?

I see that the database URL is set in the environment variable $DATABASE_URL - does that mean I can somehow update that and "link" the old database to the new app?

When searching for information on this I've come across heroku's pg:copy and pg:transfer directives, but it seems strange to duplicate the database when it is working fine, and has a paid "upgrade" and backups already associated with it.

Answer 1

You certainly can change your configuration variables to point from the new app's database to the existing database. I've done this before without issue (just make sure you have a fresh backup).

So within your new app run heroku config. You'll see a reference to your database in DATABASE_URL. You can then change the value with:

heroku config:set DATABASE_URL=<existing-database-url>

At that point you'd have your old app and new app pointing at the same database without issue. As you said, you're paying for the database upgrade on your existing database so deleting your old app shouldn't delete the database; if it does (again you have backups right?) that would be the time to contact Heroku!

If you're not comfortable working with the command line, you can also change the values in your app's Settings tab on the Heroku app dashboard.

---

With all of that said, you shouldn't actually need to create a new app in order to upgrade your stack. Again, I've done a stack upgrade before and you ought to be able to do it in place; which the Heroku docs confirm.

Answer 2

The problem with the configuration variable approach

While it is possible to share a database between two applications in Heroku by copying the DATABASE_URL configuration variable of the first application to the second, only the first application will have a primary connection (add-on) to the database.

If you remove the first application, your database will disappear from the postgres dashboard in Heroku. Although it seems the second application will still be able to use the database. (Tested on 2015-10-25.)

It is also worth noting that:

Heroku do reserve the right to change database URLs as required. If this occurs, it will cause your secondary connections to fail, and you'll need to update the URLs accordingly.

What this means is that if Heroku decide to change the URL for your database, your new application will fail, and since the database is now gone from your dashboard, you will not be able to retrieve the new URL without approaching Heroku. (If at all.)

This implies that there is a more intrinsic connection between an application and its database than merely the DATABASE_URL, and it might not be the best idea to use this as a mechanism for transfer.

---

An alternative approach using PGBackups

Heroku official documentation recommends using the free PGBackups add-on (which, honestly, you should always be running anyway) to transfer data between Postgres instances. The tool uses the native pg_dump and pg_restore tools.

Particularly, you can use the PG copy feature, described below:

PG copy uses the native PostgreSQL backup and restore utilities. Instead of writing the backup to a disk however, it streams it over the wire directly to the restore process on the new database.

They conveniently provide a means for you to copy a database between applications with a single command, and without any intermediate storage required:

As an alternative, you can migrate data between databases that are attached to different applications.

To copy the source database to the target database you will need to invoke pg:copy from the target application, referencing a source database.

Example:

heroku pg:copy source-application::OLIVE HEROKU_POSTGRESQL_PINK -a target-application

Where source-application is your existing stack, and target-application your newly created one. Not relevant to you, but still worth mentioning, is that any existing data in target-applications database will be destroyed.

After this, you might need to promote the database in the new application like so:

heroku pg:promote HEROKU_POSTGRESQL_PINK

Answer 3

Although @Drenmi's approach is probably the safest option it is possible to transfer database "ownership" between apps. Make sure you make a backup first.

In Heroku databases are treated as Add-Ons. So when you for example push a rails app heroku will add a postgres db addon to the app.

First use heroku addons to get a list of the addons attached:

$ heroku addons --app old_app

Add-on                                   Plan  Price
───────────────────────────────────────  ────  ─────
heroku-postgresql (resting-fairly-4672)  dev   free 
 └─ as HEROKU_POSTGRESQL_CYAN                       

We can then attach the database to the new app.

heroku addons:attach resting-fairly-4672 --app new_app

And detach it from the old app.

heroku addons:detach resting-fairly-4672 -app old_app

See https://devcenter.heroku.com/articles/managing-add-ons