Changing the Plan or Infrastructure of a Heroku Postgres Database

Last updated 18 June 2020

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

The processes described here do not necessarily change your database’s PostgreSQL version. If you want to upgrade your PostgreSQL version, see Upgrading Heroku Postgres Databases.

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

Update Method Description
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.

Can be used to upgrade the PostgreSQL minor version to the latest available.
pg:copy Works for all update scenarios. Required for all changes that involve a Hobby-tier database (migrating to one or from one). Requires downtime of about 3 minutes per GB, though this can vary substantially.

Can also be used to upgrade PostgreSQL version.

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 just 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 very little app downtime (usually less than a minute). Prior to 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 generally 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 is important that no new data is written to your current primary database during the upgrade process, because it will not be transferred to the new database. To accomplish this, place your app into maintenance mode. If you have scheduler jobs running as well, you should disable them.

Maintenance mode does not automatically scale down dynos. Web and any non-web dynos should be scaled down (e.g. heroku ps:scale worker=0) to ensure that no connections are writing data to the database.

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

Your application will be 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

Once the unfollow is successful, pg:info will show 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 will return 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 is not yet receiving new requests).

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

After the changeover, other followers of your original primary database do not 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.

4. Exit maintenance mode

To resume normal application operation, scale any non-web dynos back to their original levels (e.g., 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 is not connected to a Heroku application, you need to 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 is the only supported method for updates involving a Hobby-tier database (whether you are migrating to one or from one).

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.