jessica dussault

One way to upgrade a postgres database in Heroku...and one way not to

Date
22 August, 2025
Category
code
Tags
Heroku database

I've been doing some maintenance on an app deployed to Heroku. This app had a database using Postgres 14, which Heroku only supports through November of this year. It was time to upgrade!

I haven't used Heroku much in my life, but the upgrade process looked pretty straightforward. Just run pg:upgrade:run, go grab some water, and when you get back it will be done! The alternative is to create a new database, populate it, then hook it up to your application and kill off your old copy. I was seduced by the one-liner and the fact that my coworkers said it had worked for them seamlessly, so I decided to try pg:upgrade:run.

I ran it on the staging database first, because what else is staging for if not to make sure you won't break production? I put staging into maintenance mode, ran heroku pg:upgrade:run --app staging and then waited.

And I waited a little more.

After a couple minutes I was nervous. I ran heroku pg:upgrade:wait to see the status and it simply said "provisioning." I tried getting info on the database -- still just "provisioning." After 20 minutes I asked my coworkers when I should be nervous. 30 minutes. Then 40 minutes. I opened a Heroku support ticket asking what I had done wrong. Was it because I tried to jump from version 14 to 17? Everything I had read said it would be okay and I didn't need to step through versions! After 90 minutes, the database finally finished provisioning and everything seemed fine, but there was no way was I going to run that command against production!

Time for a little more effort

Alright, poor little staging database, let's try this again. This time, using the "copy the database" approach:

  1. Create a database: heroku addons:create heroku-postgresql:[type]-1 -a staging
  2. Put the app in maintenance mode: heroku maintenance:on -a staging
  3. Scale down any workers that might be talking to the old database: heroku ps:scale worker=0 -a staging
  4. Capture a backup: heroku pg:backups:capture -a staging
  5. Copy the current database to the new database: heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_NEWCOLOR -a staging
  6. Promote the new database to connect to the application: heroku pg:promote HEROKU_POSTGRESQL_NEWCOLOR -a staging
  7. Delete the old database
  8. Remove maintenance mode

Yes, it's more steps, but it's nice to feel more in control. When you run pg:upgrade:run, you're putting all your eggs in one basket. You can't roll that database back once you've started. With the copy strategy, I understand exactly what the state of things are. If the new database fails to provision, no biggie, turn off maintenance mode and the old database will still be operating.

Using the copy process took maybe 10 minutes total, and would have been faster if I weren't double checking all my commands like the paranoid parrot that I am. I am happy to report that the production database is now using Postgres 17!

Why pg:upgrade:run took so long

Two days after I opened a ticket, I heard back from Heroku support:

The cause of the issue was a buggy underlying cluster related to essential-1 database, as a remediation step our engineering changed the cluster, post which the issue was resolved.

Sounds like I just had bad luck on my first ever go at upgrading the database with the pg:upgrade:run command. I'm not sure that I'm going to be trying again, though, since copying the database feels both safer and intuitive.