Changing the Plan or Infrastructure of a Heroku Postgres Database
Last updated February 02, 2021
Table of Contents
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 may also change your PostgreSQL version. If you only want to upgrade your PostgreSQL version, see Upgrading Heroku Postgres Databases.
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.
Only 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. 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 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. 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. Note that this process is not instant, and may take several hours in the case of larger databases.
$ heroku addons:upgrade HEROKU_POSTGRESQL_LAVENDER_URL heroku-postgresql:premium-0 -a sushi
Once your database is ready for an upgrade, you will receive an email about upcoming scheduled maintenance for your database
2. Run (or wait for) the scheduled maintenance
Once ready, the addons:upgrade command schedules your database for a maintenance that performs the upgrade on the database.
This is exactly the same as the maintenance mechanism described in Heroku Postgres Maintenance Windows.
When you are 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, once 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 are be unable to point the follower at the replacement, we will automatically prepare and promote a replacement database.
Updating with follower changeover
To update a database with a follower changeover, you:
- 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.
- Place your app in maintenance mode.
- Promote the follower to be your primary database.
- 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.
If your old primary was using connection pooling, and it was attached with the default name DATABASE_CONNECTION_POOL, the promote will reattach the connection pooler to the new primary under the same name DATABASE_CONNECTION_POOL.
Attachments under non-default names will not be reattached and you should 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 (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.