53

I'm still getting my head wrapped around Heroku's plans. But I know I'm going to have around 3M rows in the db so I need to upgrade from hobby-dev to hobby-basic.

However, I can't find any documentation or help about this level of upgrade. Only docs to go from Hobby to Standard.

Do I need to create a new PG Add-On and then wipe out my hobby-dev db?

brianrhea
  • 3,674
  • 3
  • 34
  • 57
  • 1
    Upgrading from hobby-dev to hobby-basic is exactly the same as hobby to standard. – Damien MATHIEU Jul 06 '18 at 19:50
  • 1
    Maybe so, but I can't find the documentation that describes how I would do this. This page (https://devcenter.heroku.com/articles/upgrading-heroku-postgres-databases) says that `pg:upgrade` "Works for all Heroku Postgres plans except hobby-tier plans." Specifically, how would I upgrade from hobby-dev to hobby-basic? – brianrhea Jul 06 '18 at 20:25
  • 1
    https://devcenter.heroku.com/articles/upgrading-heroku-postgres-databases#upgrading-with-pg-copy – Damien MATHIEU Jul 07 '18 at 07:08

1 Answers1

126

This answer assumes that you're using Heroku CLI. Any instance of "YOUR_APP_NAME" in a command should be replaced by the application name of the Heroku App you're working with.

You will also need on hand the connection URL (shown here as DATABASE_URL) of your current hobby-dev database to be upgraded.

1. Provision a new hobby-basic database:

heroku addons:create heroku-postgresql:hobby-basic -a YOUR_APP_NAME

This will output a name for the new database containing a color. You will need to refer to this later. For example:

HEROKU_POSTGRESQL_PINK_URL

2. Optionally put db into maintenance mode to ensure that no data is added to the db while it's being copied.

heroku maintenance:on --app YOUR_APP_NAME

3. Copy the existing hobby-dev db to the hobby-basic db

heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_PINK --app YOUR_APP_NAME

Heroku will now print the following message.

heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_PINK --app YOUR_APP_NAME

!    WARNING: Destructive Action
!    Transfering data from DATABASE_URL to HEROKU_POSTGRESQL_PINK
!    This command will affect the app: YOUR_APP_NAME
!    To proceed, type "YOUR_APP_NAME" or re-run this command with --confirm YOUR_APP_NAME

YOUR_APP_NAME

4. Confirm db transfer by typing the actual name of your application

YOUR_APP_NAME

5. Promote your new database

heroku pg:promote HEROKU_POSTGRESQL_PINK --app YOUR_APP_NAME

The color-based name of the database you promote should be copied from the output you got up in step 1. Do not copy and paste the line above word for word, it will not work.

6. If you put your db into maintenance mode earlier, turn it off.

heroku maintenance:off --app YOUR_APP_NAME

defraggled
  • 1,014
  • 11
  • 13
brianrhea
  • 3,674
  • 3
  • 34
  • 57
  • 2
    New version requires to specify the app name when provisioning a new db. e.g : heroku addons:create heroku-postgresql:hobby-basic -a YOUR_APP_NAME – rajeeva9 Oct 31 '18 at 09:16
  • thanks @rajeeva9 ... updated answer to reflect this new requirement – brianrhea Oct 31 '18 at 15:07
  • 4
    Great answer. The only thing I'd add is the (optional) removal of the hobby-dev database at the end: `heroku addons:destroy heroku-postgresql:hobby-dev -app YOUR_APP_NAME`. You'll need to confirm by typing your app name. – Rob MacEachern Nov 15 '18 at 23:51
  • Thanks @brianrhea, should we drop the old database to be fully clean ? – Gregdebrick Dec 27 '18 at 09:58
  • 2
    Fantastic TL;DR, save me a ton of time. I'd also add that for - `heroku maintenance:on` - `heroku maintenance:off` - `heroku pg:promote` You'd also want to add the `--app YOUR_APP_NAME` suffix to those commands, if you have multiple Heroku apps in your account – bourgeois247 Jan 16 '19 at 11:44
  • 5
    This should literally be the heroku guide on how to do this. – Waclock Aug 08 '19 at 14:29
  • Fantastic! it worked great for me, i would just mention on Rob's answer the following change(double dash before app: heroku addons:destroy heroku-postgresql:hobby-dev YOUR_APP_NAME – Kirumosama Nov 23 '21 at 05:13