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      [email protected]:xxx/xxx.git  (fetch)
origin      [email protected]: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.

This is great! Thanks!

This was really useful, thanks a lot

Thanks man, you rocks 🚀

Very helpful, thank you!

thanks that was so easy

Thanks man !

Very helpful.It saved my lot of time

short and easy to understand.

Thanks! this is great! 👍

Thanks!

This helped me a lot a while back, and today I needed some references once more after a long time and it is really helpful indeed! Cheers

This was very helpful to me - thank you for taking the time to put it out here! However, I do have a couple of confusions that I hope someone can help me with:

1. My current database doesn't seem to have a color. It just says "Heroku Postgres :: Database". What would be the HEROKU_POSTGRESQL_ bit for that?
2. This is more of a frustration than anything else: Why in the wide world did Heroku make it so difficult just to change my payment plan? Why can't I just change it from the interface? Looking at the plan descriptions and such, all I see is the easing of certain limitations (10mil rows instead of 10k, etc), so why does it require a brand new database rather than just changing a couple of those settings?

1. My current database doesn't seem to have a color. It just says "Heroku Postgres :: Database". What would be the HEROKU_POSTGRESQL_ bit for that?

For this you can use the add-on name that you see in heroku pg:info --app your-app

So the command would look like:
heroku pg:copy postgresql-asymmetrical-123456 HEROKU_POSTGRESQL_AMBER --confirm your-app --app your-app

Heroku by default creates latest version of Postgres that it supports, if you want to create specific version, you should be using something like this

heroku addons:create heroku-postgresql:standard-0 --version=9.5

How can I rename "HEROKU_POSTGRESQL_AMBER_URL" to "HEROKU_POSTGRESQL_URL"? I need plain env key names. Thanks