Last updated September 30, 2024

This article describes how to upgrade the major PostgreSQL version of a Heroku Postgres database. The most recent major PostgreSQL version supported by Heroku is 16.

Upgrading your major PostgreSQL version can be done only via the Heroku CLI. It’s a significant operation that must be done with care.

Heroku supports two methods for upgrading. Both methods require some application downtime to ensure that no data is lost during the upgrade.

Upgrading with pg:upgrade

Essential-Tier

This method is supported for all Essential-tier Heroku Postgres plans except deprecated mini and basic plans. If you have an essential-0, essential-1, or essential-2 database, you can run pg:upgrade directly on your database instead of on a follower.

$ heroku pg:upgrade HEROKU_POSTGRESQL_RED --app example-app

Standard-Tier and Higher

For Standard-Tier and higher databases, you can only use pg:upgrade to upgrade a follower database, which remains on the same plan but stops following the current leader.

The pg:upgrade command uses the PostgreSQL pg_upgrade utility to upgrade your PostgreSQL version in-place.

If you must upgrade both the PostgreSQL version and your plan, provision a new follower on a different plan and perform a pg:upgrade as part of the changeover process.

Performing a pg:upgrade requires app downtime on the order of 30 minutes.

1. Provision a Follower Database

To begin, create a follower for your database and wait for the follower to catch up to the leader database. In the example, a follower with a standard-2 plan is created on the HEROKU_POSTGRESQL_LAVENDER_URL. You can provision the plan best suited for your needs. Replace HEROKU_POSTGRESQL_LAVENDER_URL with the config var of the database you’re upgrading.

$ heroku addons:create heroku-postgresql:standard-2 --follow HEROKU_POSTGRESQL_LAVENDER_URL --app example-app

$ heroku addons:create heroku-postgresql:standard-2 --app example-app -- --follow HEROKU_POSTGRESQL_LAVENDER_URL
Adding heroku-postgresql:standard-2 to example-app... done, v71 ($200/mo)
Attached as HEROKU_POSTGRESQL_WHITE
Follower will become available for read-only queries when up-to-date
Use `heroku pg:wait` to track status

$ heroku pg:wait
Waiting for database HEROKU_POSTGRESQL_WHITE_URL... performing final cleanup steps after upgrade

The follower is considered “caught up” when it is within 200 commits of the primary database. You can check how many commits the follower is behind with the pg:info command (see the Behind By row of the follower database):

$ heroku pg:info --app example-app
=== HEROKU_POSTGRESQL_LAVENDER
Plan:        Standard 0
Status:      available
...
=== HEROKU_POSTGRESQL_WHITE
Plan:        Standard 2
Status:      available
...
Following:   HEROKU_POSTGRESQL_LAVENDER (DATABASE_URL)
Behind By:   125 commits

2. Enter Maintenance Mode to Prevent Database Writes

It’s important that no new data is written to your current primary database during the upgrade process, because it doesn’t transfer to the new database. To accomplish this, place your app into maintenance mode. If you have scheduler jobs running as well, disable them. Databases continue to accrue billing hours while in maintenance mode.

$ heroku maintenance:on --app example-app
Enabling maintenance mode for example-app... done

3. Upgrade the Follower Database

Now that you are in maintenance mode and no additional data is being written to the primary database, you can upgrade the follower database.

Wait for the follower database to fully catch up to the primary (as indicated by being behind by 0 commits).

$ heroku pg:info --app example-app
=== HEROKU_POSTGRESQL_LAVENDER_URL
Plan:        Standard 0
Status:      available
...
=== HEROKU_POSTGRESQL_WHITE_URL
Plan:        Standard 2
Status:      available
...
Following:   HEROKU_POSTGRESQL_LAVENDER_URL (DATABASE_URL)
Behind By:   0 commits

If you don’t wait for the follower to catch up then you get an error message:

 ▸    database must not be too far behind leader, please wait until your follower catches up with its leader.

When the follower is caught up, use the pg:upgrade command to update the PostgreSQL version of the follower in place. Upgrading also causes the follower to unfollow the primary database. This step usually requires about 20 minutes to complete.

$ heroku pg:upgrade HEROKU_POSTGRESQL_WHITE --app example-app

You can monitor the progress of the upgrade with pg:wait.

$ heroku pg:wait --app example-app
Waiting for database HEROKU_POSTGRESQL_WHITE_URL... performing final cleanup steps after upgrade

As part of the pg:upgrade process, Heroku Postgres runs ANALYZE on your database: this recalculates statistics for your database to make sure the Postgres query planner has up-to-date information even after a version upgrade. All credentials and custom credentials are also rotated as a part of the upgrade process.

The pg:upgrade command can fail due to errors such as incompatible data types in a table in your database. If your upgrade fails, running pg:wait returns version upgrade error, check your Heroku email notifications for details You can restart the upgrade after resolving the error.

4. Promote or Attach the New Database

If DATABASE_URL was the config var for your previous primary database, use pg:promote to promote the newly upgraded database as the new DATABASE_URL. pg:promote creates an alternate attachment for the old primary database, assigned with a new HEROKU_POSTGRESQL_<color>_URL config var. The promotion process triggers a release and restarts the app.

$ heroku pg:promote HEROKU_POSTGRESQL_WHITE --app example-app
Promoting HEROKU_POSTGRESQL_WHITE_URL to DATABASE_URL... done

Now the follower database you created in step 1 is the primary database (DATABASE_URL) for the app, although the app isn’t receiving new requests yet.

If the database you’re upgrading has a different config var other than the default DATABASE_URL, use heroku addons:attach to promote your newly upgraded database with the required alias or attachment name. If you attached your original primary database to multiple apps, you must attach your new upgraded database to those apps with addons:attach.

$ heroku addons:create heroku-postgresql:standard-0 -a example-app -- --follow DATABASE_URL

$ heroku addons:create heroku-postgresql:standard-0 --follow DATABASE_URL --app example-app

$ heroku pg:connection-pooling:attach DATABASE_URL --as MY_DATABASE_CONNECTION_POOL -a example-app

5. Exit Maintenance Mode

To resume normal application operation, scale any non-web dynos back to their original levels (for example, heroku ps:scale worker=1).

Finally, turn off maintenance mode:

$ heroku maintenance:off --app example-app

Your application is now receiving requests to your updated database instance. You can confirm this by running heroku pg:info. The database denoted by DATABASE_URL is considered the primary database.

Upgrading with pg:copy

The pg:copy upgrade method uses native PostgreSQL backup and restore utilities. Instead of writing a database backup to disk, it streams the backup data directly to the restore process of a newly provisioned database. This process helps reduce bloat, the extra space taken up by dead rows on your database, and saves disk space.

The pg:copy method requires approximately 3 minutes of app downtime per GB of your current database, although this amount can vary substantially depending on your schema and database plan. You can estimate your required downtime by testing the upgrade (perform the upgrade process on a new database on a non-production application).

The pg:copy method supports upgrades between all supported Heroku Postgres plans and versions. It’s the only method for making version changes that involve a deprecated mini or basic database, or upgrading a deprecated mini or basic databases to a plan in another tier. pg:copy only copies your default credential and the data it has access to. Any additional credentials and data that only they can access don’t get copied.

1. Provision a New Database

Provision a new Heroku Postgres database with your desired plan (in the example below the standard-0 plan is used, but you can provision the plan best suited for your needs):

$ heroku addons:create heroku-postgresql:standard-0 --app example-app
Adding heroku-postgresql:standard-0 on example-app... done, v122 ($50/mo)
The database should be available in 3-5 minutes

Standard, Premium, and Private-tier databases take a few minutes to provision. You can use the pg:wait command to notify you when provisioning is complete:

$ heroku pg:wait -a example-app
Waiting for database HEROKU_POSTGRESQL_PINK_URL... performing final cleanup steps after upgrade

2. Enter Maintenance Mode to Prevent Database Writes

It’s important that no new data is written to your current primary database during the upgrade process, because it doesn’t transfer to the new database. To accomplish this, place your app into maintenance mode. If you have scheduler jobs running as well, disable them.

$ heroku maintenance:on -a example-app
Enabling maintenance mode for example-app... done

3. Transfer Data to the New Database

To copy data from your current database to the newly provisioned database, use the pg:copy command with the HEROKU_POSTGRESQL_COLOR name of your new database.

In this example, the DATABASE_URL is the source of the data in the transfer and HEROKU_POSTGRESQL_PINK is the target database.

$ heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_PINK --app example-app

!    WARNING: Destructive Action
!    Transfering data from DATABASE_URL to HEROKU_POSTGRESQL_PINK
!    This command will affect the app: example-app
!    To proceed, type "example-app" or re-run this command with --confirm example-app

> example-app

You can also use the add-on name of your new database, for example, postgresql-concave-52656:

$ heroku pg:copy DATABASE_URL postgresql-concave-52656 --app example-app

4. Promote or Attach the New Database

If DATABASE_URL was the config var for your previous primary database, use pg:promote to promote the newly upgraded database as the new DATABASE_URL. pg:promote creates an alternate attachment for the old primary database, assigned with a new HEROKU_POSTGRESQL_<color>_URL config var. The promotion process triggers a release and restarts the app.

$ heroku pg:promote HEROKU_POSTGRESQL_PINK --app example-app
Promoting HEROKU_POSTGRESQL_PINK_URL to DATABASE_URL... done

Now the database you created in step 1 is the primary database (DATABASE_URL) for the app, although the app isn’t receiving new requests yet.

If the database you’re upgrading has a different config var other than the default DATABASE_URL, use heroku addons:attach to promote your newly upgraded database with the required alias or attachment name. If you attached your original primary database to multiple apps, you must attach your new upgraded database to those apps with addons:attach.

$ heroku addons:create heroku-postgresql:standard-0 -a example-app -- --follow DATABASE_URL

$ heroku addons:create heroku-postgresql:standard-0 --follow DATABASE_URL -a example-app

5. Exit Maintenance Mode

To resume normal application operation, scale any non-web dynos back to their original levels (for example, heroku ps:scale worker=1).

Finally, turn off maintenance mode:

$ heroku maintenance:off --app example-app

Your application is now receiving requests to your new database instance. You can confirm this by running heroku pg:info. The database denoted by DATABASE_URL is considered the primary database.

Deprovisioning the Old Primary Database

After you upgrade your database, be sure to deprovision your old primary database:

$ heroku addons:destroy HEROKU_POSTGRESQL_LAVENDER --app example-app