Upgrade Heroku hobby-dev Postgres Database

Heroku provides two methods of upgrading its Postgres database. The one avaiable for the free and other hobby-tier plan is the pg-copy command. (The other method is pg:upgrade available for higher paid plans.)

Heroku upgrade guide has the step-by-step tutorial, make sure you also check it. Below are just my notes of upgrading a free (hobby-dev) Postgres from 9.6.7 to 12, the most recent supported version.

The steps

• Provision a new database
• (Turn on maintenance mode)
• Copy over data to the new database via pg-copy command
• Promote the new one as the primary database
• (Turn off maintenance mode)
• Shutdown the old one

It might be ok to skip the maintenance on/off operation if you're sure there will be no write operations to the db during the process. I did so for my personal blog as I'm the only one who can write to the database, and the whole process of provisioning ~ promoting only took about 2~3 minutes.

Notes of upgrading to Postgres v12

Provision a new free (hobby-dev) database, copy that HEROKU_POSTGRESQL_{COLOR}_URL from the result. In my case it's HEROKU_POSTGRESQL_ONYX_URL.

heroku addons:create heroku-postgresql:hobby-dev
# ...
# Created postgresql-trapezoidal-00733 as HEROKU_POSTGRESQL_ONYX_URL

Turn on maintenance mode.

heroku maintenance:on

Copy over the data. Replace the last argument with your newly provisioned database url. You may need to add --app appname if you have multiple apps.

heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_ONYX_URL

Promote the new database and turn off the maintenance.

heroku pg:promote HEROKU_POSTGRESQL_ONYX_URL
heroku maintenance:off

Run the pg:info to confirm the newly created database has the DATABASE_URL label on it. In my case it's right beside the HEROKU_POSTGRESQL_ONYX_URL, meaning our version 12.2 Postgres is the primary database now.

$ heroku pg:info
=== HEROKU_POSTGRESQL_ONYX_URL, DATABASE_URL
Plan:                  Hobby-dev
Status:                Available
Connections:           1/20
PG Version:            12.2
Created:               2020-04-04 08:51 UTC
Data Size:             10.6 MB
Tables:                7
Rows:                  2054/10000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported
Continuous Protection: Off
Add-on:                postgresql-trapezoidal-00733

=== HEROKU_POSTGRESQL_PURPLE_URL
Plan:                  Hobby-dev
Status:                Available
Connections:           0/20
PG Version:            9.6.17
Created:               2015-04-18 15:08 UTC
Data Size:             10.6 MB
Tables:                7
Rows:                  2053/10000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported
Continuous Protection: Off
Add-on:                blooming-sagely-7320

Time to destory the old database now. Grab the url from the result above and run:

heroku addons:destroy HEROKU_POSTGRESQL_PURPLE_URL

That's it 🙂

If you use Heroku relatively often, I also recommend my other post: Parity: Managing Multiple Heroku Environments with Ease.