How to upgrade Heroku Postgres database plan?

upgrade.md

1. Assuming you have multiple Heroku apps and Git remote like so:

development https://git.heroku.com/xxx.git (fetch)
development https://git.heroku.com/xxx.git (push)
origin      git@bitbucket.org:xxx/xxx.git  (fetch)
origin      git@bitbucket.org:xxx/xxx.git  (push)
production  https://git.heroku.com/xxx.git (fetch)
production  https://git.heroku.com/xxx.git (push)
staging     https://git.heroku.com/xxx.git (fetch)
staging     https://git.heroku.com/xxx.git (push)

2. In this tutorial, I'm upgrading Hobby Basic plan to Standard 0 plan for my staging app.

List of Heroku Postgres plans

3. Start with:

$ cd into/the/rails/project

4. Turn on Maintenance Mode for your Heroku app:

$ heroku maintenance:on -r staging

5. Login as owner of the app by doing:

$ heroku login

6. Provision new database:

$ heroku addons:create heroku-postgresql:standard-0 -r staging && heroku pg:wait -r staging

7. Check your Heroku web UI > app > Resources. You should see your new database with name pattern like this - Heroku Postgres :: XXX. Take the color after the :: and capitalize it. For example, I saw Heroku Postgres :: Amber and I should use AMBER in next step.

8. Promote the new database to our Heroku app by taking the color from step 7 above and append it after HEROKU_POSTGRESQL_

$ heroku pg:promote HEROKU_POSTGRESQL_AMBER -r staging

9. Copy old database to new database:

$ heroku pg:copy HEROKU_POSTGRESQL_COPPER HEROKU_POSTGRESQL_AMBER -r staging --confirm your-heroku-app-name

Note:

HEROKU_POSTGRESQL_COPPER is my old database. If you not sure what database color you old database is, refer step 7 again.

10. Destroy old database:

$ heroku addons:destroy heroku-postgresql:hobby-basic -r staging

11. Check your data:

$ heroku run console -r staging

> Cat.count
=> 1694

12. Go to your Heroku app > Settings > Reveal Config Vars and make sure your old database color ENV variable is not there. For me, I can't see HEROKU_POSTGRESQL_COPPER_URL anymore and that's what I'm expecting. Only HEROKU_POSTGRESQL_AMBER_URL is there.

13. Turn off the Maintenace Mode:

$ heroku maintenance:off -r staging

14. Run heroku login command again to switch your Heroku CLI username. Otherwise, any deployment or changes you made after this will be tied to previous user (the user that do DB upgrade in steps above).

Done.