How to upgrade a Heroku PostgreSQL database to a new plan

how-to-upgrade-heroku-postgresql.md

How to upgrade a Heroku PostgreSQL database to a new plan

I started a project on a Hobby Dev plan (free, limit 10,000 rows), and then later needed to upgrade it to Hobby Basic ($9/month, limit 10,000,000 rows).

After assigning the new database, I had two databases attached to the application. They looked something like this:

• HEROKU_POSTGRESQL_OLIVE (postgresql-dimensional-3321) Old, free-tier (Hobby Dev) database
• HEROKU_POSTGRESQL_COPPER (postgresql-perpendicular-6628) New Hobby Basic, $9/month database

Here's how I ran the upgrade:

heroku maintenance:on
heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_COPPER_URL
heroku pg:promote HEROKU_POSTGRESQL_COPPER
heroku maintenance:off

The pg:promote command set the DATABASE_URL environment variable to the new database.

More instructions: https://devcenter.heroku.com/articles/upgrading-heroku-postgres-databases#upgrade-with-pg-copy-default

Setting up backups

I set up nightly backups of the database using the following command:

heroku pg:backups schedule DATABASE_URL --at '02:00 America/Los_Angeles'

To list available backus, use:

heroku pg:backups

To fetch the most recent backup and restore it into a local Postgres:

curl -o latest.dump `heroku pg:backups public-url`
createdb mynewdb
pg_restore --verbose --clean --no-acl --no-owner \
    -h localhost -U simonw -d mynewdb latest.dump

Thank you so much

Very useful, thanks!

+1, @levarberry agree

LIKE.A.GLOVE 💯

Mucho thanks for this. Agreed with all on this thread, Heroku's documentation sucked on this. You'd think they'd want to make it very clear and visible for something that makes them more money.

Thank you very much, fully agree with levarberry...

The hero we need.

Amazing. Thank you

Super nice, thank you so much.

Heroku should learn amazing skills from you.

🤗 for this

Thanks Simon - very helpful. One thing to add is that if you have multiple apps you will also need to include the app name:

heroku maintenance:on -a yourappname
heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_COPPER_URL -a yourappname
heroku pg:promote HEROKU_POSTGRESQL_COPPER -a yourappname
heroku maintenance:off -a yourappname

Many Thanks @simonw & @peternixey for sharing this

Thanks all. Heroku was great until I needed to rely on their docs for a damn db upgrade! How in the hell do they send an email stating I need to upgrade asap without comprehensive docs on how to do so!?

Still my go-to place for PG upgrades!

As of today, you can upgrade these databases with one command:

heroku addons:upgrade DATABASE_URL heroku-postgresql:mini --app myapp

This will change the plan but not require any maintenance or data migration. This only works for "in kind" Essential-tier database plan changes (hobby-dev, hobby-basic, mini, basic). If you want to change plans to another, larger plan, you'll need to use Simon's instructions. 💜

HEROKU_POSTGRESQL_COPPER_URL -> what is this for? I created a Python django heroku app and a postgre db addon in another app. But this HEROKU_POSTGRESQL_COPPER_URL came? Can I delete this? Otherwise, I have to pay for two databases