Changing the Plan or Infrastructure of a Heroku Postgres Database

Last updated December 19, 2022

This article describes available methods for changing the plan or underlying infrastructure of a Heroku Postgres database.

This article documents the process of upgrading your PostgreSQL plan and infrastructure, which in some cases can also change your PostgreSQL version. If you only want to upgrade your PostgreSQL version, see Upgrading the Version on Heroku Postgres Databases.

Starting November 28, 2022, free Heroku Dynos, free Heroku Postgres, and free Heroku Data for Redis® plans will no longer be available. If you have apps using any of these resources, you must upgrade to paid plans by this date to ensure your apps continue to run and retain your data. Eligible students can apply for platform credits through our new Heroku for GitHub Students program. See our blog and FAQ for more info.

Heroku supports three methods for changing Postgres plans and infrastructure. All methods require some application downtime to ensure that no data is lost during the update.

Update Method Description
heroku addons:upgrade Updates the plan and infrastructure of a production database while preserving its major PostgreSQL version.

Works with Standard, Premium, Private, or Shield-tier databases. Requires up to several hours to prepare a replacement (during which the application can remain active), and less than 1 minute of downtime during the changeover. Repoints any database followers to the new primary.

You can also use this command to change Essential-tier databases (Hobby-Dev, Hobby-Basic, Mini, or Basic) to another plan in that tier. To upgrade Essential-tier databases to plans on other tiers, use pg:copyinstead.

Can be used to upgrade the PostgreSQL minor version to the latest available.
Follower changeover Updates the plan and infrastructure of a production database while preserving its major PostgreSQL version.

Only works with Standard, Premium, Private, or Shield tier databases. Requires up to several hours to prepare a follower (during which the application can remain active), and less than 1 minute of downtime during the changeover. Requires recreation of followers to point to the new primary.

Can be used to upgrade the PostgreSQL minor version to the latest available.

In general, addons:upgrade is preferable to a follower changeover, due to follower repointing and Heroku-controlled mechanisms for running maintenance.
pg:copy Works for all update scenarios. Required for all version changes that involve an Essential-tier database, or to upgrade an Essential-tier database to a plan in another tier. Requires downtime of about 3 minutes per GB, though this amount can vary substantially. Requires recreation of followers to point to the new primary.

Can also be used to upgrade major PostgreSQL version.

Updating with addons:upgrade

1. Run the heroku addons:upgrade Command.

This command prepares your database for a plan upgrade. This process isn’t instant, and can take several hours in the case of larger databases.

$ heroku addons:upgrade HEROKU_POSTGRESQL_LAVENDER_URL heroku-postgresql:premium-0 -a sushi

When your database is ready for an upgrade, you receive an email about upcoming scheduled maintenance for your database

2. Run (or Wait for) the Scheduled Maintenance

When ready, the addons:upgrade command schedules your database for a maintenance that performs the upgrade on the database.

This is the same as the maintenance mechanism described in Heroku Postgres Maintenance Windows.

When you’re ready to run maintenance on your database, refer to Running a maintenance manually. After the maintenance is complete, your database will be on hardware matching the new plan.

If your database has one or more followers, when the upgrade maintenance occurs, Heroku attempts to point the followers to the replacement database. Pointing the followers to the replacement requires the follower database to be restarted.

In some cases, if we’re unable to point the follower at the replacement, we automatically prepare and promote a replacement database.

Updating with Follower Changeover

To update a database with a follower changeover, you:

  1. Provision a follower database with the plan you want to change to, and wait for it to catch up to your primary database. If you change to update the underlying hardware for your plan, choose the same plan your database is currently on.
  2. Place your app in maintenance mode.
  3. Promote the follower to be your primary database.
  4. Exit maintenance mode.

A follower changeover requires little app downtime (usually less than a minute). Before the changeover, however, it can take several hours to prepare the follower (during which your application is still active), so plan accordingly.

1. Provision the Follower Database

Create a new follower for your database and wait for the follower to catch up to the primary database:

$ heroku addons:create heroku-postgresql:standard-2 --follow HEROKU_POSTGRESQL_LAVENDER_URL --app sushi
Creating heroku-postgresql:standard-2 on sushi... $200/month
! WARNING: Follower will become available for read-only queries when up-to-date.
Use `heroku pg:wait` to track status.
postgresql-shaped-12345 is being created in the background. The app will restart when complete...
Use heroku addons:info postgresql-shaped-12345 to check creation progress

$ heroku addons:wait
Creating postgresql-shaped-12345... done
Created postgresql-shaped-12345 as HEROKU_POSTGRESQL_WHITE_URL

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 sushi
=== 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.

Maintenance mode doesn’t automatically scale down dynos. Scale down web and any non-web dynos (for example, heroku ps:scale worker=0) to ensure that no connections are writing data to the database.

If the database you’re updating is used by Heroku Connect, pause the synchronization at this point to ensure that the connection isn’t writing data to the database. Instructions are available in Heroku Connect: Upgrading your database.

Your application is unavailable starting at this point in the upgrade process.

$ heroku maintenance:on
Enabling maintenance mode for sushi... done

3. Promote the Follower Database

Now that you are in maintenance mode and no additional data is being written to the primary database, you can promote the follower database to take over from the current primary one.

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

$ heroku pg:info --app sushi
=== 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

When the follower is caught up and no new data is being generated, issue an unfollow command to convert the follower into a full, writeable database.

$ heroku pg:unfollow HEROKU_POSTGRESQL_WHITE_URL --app sushi
 ▸    WARNING: Destructive action
 ▸    postgresql-shaped-12345 will become writeable and no longer follow HEROKU_POSTGRESQL_LAVENDER. This cannot be undone.
 ▸
 ▸    To proceed, type sushi or re-run this command with --confirm sushi

> sushi
postgresql-shaped-12345 unfollowing... done

After the unfollow is successful, pg:info shows that the database is now a fork of its parent (Forked From: HEROKU_POSTGRESQL_LAVENDER).

You can also verify that the database is out of read-only mode by running the SQL command: SELECT pg_is_in_recovery();, which returns false when the new database is ready for read-write connections.

Then, promote it to set it as the primary database (at the DATABASE_URL location) used by your application:

$ heroku pg:promote HEROKU_POSTGRESQL_WHITE_URL --app sushi
Promoting HEROKU_POSTGRESQL_WHITE_URL to DATABASE_URL on sushi... done

The follower database is now the primary database (although the application isn’t yet receiving new requests).

If your original primary database was attached to multiple apps, you must attach your new database to those apps with heroku addons:attach.

After the changeover, other followers of your original primary database don’t automatically start to follow your new primary.

Create new followers for the new primary database as needed:

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

Be sure to deprovision your old followers after you no longer need them.

If your old primary was using connection pooling, and it was attached with the default name DATABASE_CONNECTION_POOL, the promote reattaches the connection pooler to the new primary under the same name DATABASE_CONNECTION_POOL.

Attachments under non-default names aren’t reattached and you must activate connection pooling on your new primary if you wish to keep using connection pooling on your new primary with the same non-default name as the old primary:

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

4. Exit Maintenance Mode

To resume normal application operation, scale any non-web dynos back to their original levels. For example, if you had previously scaled down a worker dyno, you would scale it back up now with heroku ps:scale worker=1.

Finally, turn off maintenance mode:

$ heroku maintenance:off
Disabling maintenance mode for sushi... done

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.

If your Heroku Postgres database isn’t connected to a Heroku application, you must retrieve the HEROKU_POSTGRESQL_WHITE_URL and update your application to use it as your primary database.

Updating with pg:copy

The pg:copy command supports updates between all supported Heroku Postgres plans and versions. Additionally, it’s the only supported method for version changes that involve an Essential-tier database (Hobby-Dev, Hobby-Basic, Mini, or Basic), or to upgrade an Essential-tier database to a plan in another tier.

Instructions for using pg:copy are available in Upgrading Heroku Postgres Databases.

Deprovisioning the Old Primary Database

After you update your database with either a follower changeover or pg:copy, be sure to deprovision your old primary database:

$ heroku addons:destroy HEROKU_POSTGRESQL_LAVENDER_URL

Dataclips that were associated with the old primary database must be reassigned to the new database. Follow the instructions on Dataclip recovery to resolve any recoverable Dataclips.

Feedback

Log in to submit feedback.