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

original: https://gist.github.com/simonw/50e14b9a3e829355d6d43f0f12f91e74

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).

⚠️ In the following, replace <appname> with the name of your Heroku app.

What database(s) are connected?

heroku pg:info --app=<appname>

In my case there was only 1 database connected (the original hobby-dev one)

Assigning a new database

Create a new database with the plan of your choice (here hobby-basic)

heroku addons:create heroku-postgresql:hobby-basic --app=<appname>

Copying old database to new database

After assigning the new database, I had two databases attached to the application. They looked something like this after running heroku pg:info --app=<appname>:

• 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