Upgrade to the latest Postgres version on Heroku (stating and production)

[matyikriszta]

We are currently on a deprecated version of Postgres on both our staging and production apps. Need to upgrade to the latest or at least supported version by 2025-Oct-28.

Your database (GOLD on codebar-production) is on the deprecated Postgres version 14 that will reach end-of-life on 2025-Nov-28. To ensure your database has the best performance, security, and features, we recommend you upgrade to the latest Postgres version. Upgrading to a supported version is necessary to maintain the integrity and reliability of your service. If you don’t upgrade your Postgres version by 2025-Oct-28, we'll upgrade your database version to Postgres 17. See Upgrading the Version of a Heroku Postgres Database for more details, or open a support ticket if you have any questions.

[olleolleolle]

Tagging @till as a contributor on this issue.

Till now has access to the production and staging environments in Heroku, which allows access to making changes to Postgres.

We learned that the GOLD on codebar-production is an older, not-in-use Postgres instance. MAUVE is in use, and it has the "DATABASE_URL" moniker.

[till]

I created a backup on staging like so:

heroku pg:backups:capture reading-deftly-5332 --app codebar-staging
Starting backup of reading-deftly-5332... done

Use Ctrl-C at any time to stop monitoring progress; the backup will continue running.
Use heroku pg:backups:info to check progress.
Stop a running backup with heroku pg:backups:cancel.

Backing up ORANGE to b011... done

Verified that it was taken:

heroku pg:backups:info --app codebar-staging
=== Backup b011

Database:         ORANGE
Started at:       2025-08-05 18:25:19 +0000
Finished at:      2025-08-05 18:25:47 +0000
Status:           Completed
Type:             Manual
Original DB Size: 13.04MB
Backup Size:      141.03KB (99% compression)

=== Backup Logs

2025-08-05 18:25:22 +0000 pg_dump: last built-in OID is 16383
...

And apparently it worked. :)

[till]

Initiated the database upgrade to version 17:

heroku pg:upgrade:run --app=codebar-staging --version 17
 ›   Warning: Destructive action
 ›   You're upgrading reading-deftly-5332 to Postgres version 17.
 ›
 ›   You can't undo this action.
 ›

To proceed, type codebar-staging or re-run this command with --confirm codebar-staging: codebar-staging
Starting upgrade on reading-deftly-5332... done
Started the upgrade to Postgres version 17. You can monitor the progress with heroku pg:upgrade:wait.

[till]

The staging upgrade completed, but it took ~2 hours. I reached out to support as to what to expect on the production databases.

[jonodrew]

I thought we agreed we don't need to do it on prod?

[olleolleolle]

I thought we agreed we don't need to do it on prod?

It is not strictly required, but it can be done. We want to know the landscape, learn what it takes.

[till]

@jonodrew yeah, no rush. Though I think my preference would be to have the same version everywhere and then not worry about it again.

I'll report back what their support says. And we can chat next time around if we want it or not.

Otherwise, we still have to backup/remove the database that's not being used (anymore). And look into what is is.