Upgrading Heroku Postgres Databases
Last updated 08 September 2016
Table of Contents
This article describes how to upgrade Heroku Postgres databases. The upgrade processes described here can be used both to upgrade (or downgrade) the plan of a Heroku Postgres database, as well as to upgrade the PostgreSQL version of a database. Upgrading a Heroku Postgres database can only be done via the Heroku CLI.
Upgrading an active Heroku Postgres database is a significant operation and should be done with care.
There are three different upgrade processes available. In all cases, some amount of application downtime is required so that no data is written to the new or old databases during the changeover.
| Upgrade Method | Uses & Requirements |
|---|---|
| PG copy (default) | Works for all upgrade scenarios. Required for upgrading Hobby tier databases (hobby-dev and hobby-basic) within the Hobby tier and to the production tier. Can be used for upgrading legacy infrastructure and/or upgrading Postgres versions.
Requires downtime of about 3 minutes per GB, though this can vary substantially. |
| Follower Changeover | Upgrades the plan of a production database while keeping PostgreSQL version the same.
Only works with Standard, Premium, or Enterprise tier databases (or legacy production databases). Can be used for upgrading legacy infrastructure. Requires several hours to prepare a follower (during which an application can remain active), and less than 1 minute of downtime during the changeover. |
| pg:upgrade | Upgrading the version of very large (> 10 GB) production databases. Only works on Standard, Premium, or Enterprise databases (or legacy production databases). |
The PG copy upgrade path is the most compatible and works between all database plans. The other upgrade paths are only applicable in some cases, but can be significantly faster than PG copy.
If you are using the beta PostGIS functionality, you must use the PG copy method to upgrade your Postgres version. The PostGIS extension will be set up automatically, but beware of outstanding PostGIS issues when upgrading to a new version. If you are only upgrading your plan (and keeping your Postgres version the same), you can use a follower changeover with PostGIS.
Upgrade with PG copy (default)
PG copy uses the native PostgreSQL backup and restore utilities. Instead of writing the backup to a disk however, it streams it over the wire directly to the restore process on the new database.
Use cases and time required
PG copy requires approximately 3 minutes per GB, although this can vary substantially depending on schema and database plan. It will perform faster on larger database plans that have faster I/O. The best way to get an accurate estimate of time required is to perform a dry run (go through the upgrade procedure but skip the Promote step).
PG copy supports upgrades between all Heroku Postgres plans. Additionally, is the only supported upgrade path in the following cases:
- Upgrading from a Hobby to Standard, Premium, or Enterprise tier Database
- Upgrading a legacy infrastructure database
It is also the best way to upgrade the PostgreSQL version of Standard, Premium, and Enterprise tier databases as it preserves the database checksums which are used for early corruption detection.
1. Provision new database
Choose the new database tier and plan and provision it.
$ heroku addons:create heroku-postgresql:standard-0 Adding heroku-postgresql:standard-0 on sushi... done, v122 ($50/mo) The database should be available in 3-5 minutes
Standard, Premium and Enterprise tiers take a few minutes to provision. If you are using one of these, you can use the pg:wait command to notify you when the provisioning is complete:
$ heroku pg:wait Waiting for database HEROKU_POSTGRESQL_PINK_URL... available
If you are upgrading from an older version of PostgreSQL your new database by default will be 9.5. If you wish to remain on an identical version you should use the version flag, e.g. --version 9.3.
2. Prevent new database updates
It is important that no new data is written to your application during the upgrade process or it will not be copied 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.
Your application will be unavailable starting at this point in the upgrade process.
$ heroku maintenance:on Enabling maintenance mode for sushi... done
Maintenance mode does not automatically scale down any dynos. Web and any non-web dynos should be scaled down (e.g. heroku ps:scale worker=0) to make sure that no connections are writing data to the database.
3. Transfer data to 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 sushi ! WARNING: Destructive Action ! Transfering data from DATABASE_URL to HEROKU_POSTGRESQL_PINK ! This command will affect the app: sushi ! To proceed, type "sushi" or re-run this command with --confirm sushi > sushi
4b (alternative) Transfer data between applications
As an alternative, you can migrate data between databases that are attached to different applications.
For the purpose of consistency, the database being migrated from will be called the source database while the database being migrated to will be called the target database.
To copy the source database to the target database you will need to invoke pg:copy from the target application, referencing a source database. This is a destructive operation: the transfer operation will drop existing data and replace it with the contents of the source database. The contents of the database prior to a transfer will not be recoverable. If the target database already contains data, capturing a backup with pg:backups capture prior to transferring is a good idea.
$ heroku pg:copy source-application::OLIVE HEROKU_POSTGRESQL_PINK -a target-application ! WARNING: Destructive Action ! Transferring data from source-application::OLIVE to HEROKU_POSTGRESQL_PINK ! This command will affect the app: target-application ! To proceed, type "target-application" or re-run this command with --confirm new-application
5. Promote new database
At this point the new database is populated with the data from the original database but is not yet the active database for your application. If you want the new upgraded database to be the primary database for your application, you will need to promote it.
$ heroku pg:promote HEROKU_POSTGRESQL_PINK Promoting HEROKU_POSTGRESQL_PINK_URL to DATABASE_URL... done
The upgraded database is now the primary database (though the application is not yet receiving new requests).
6. Make application active
To resume normal application operation, scale any non-web dynos back to their original levels (e.g. heroku ps:scale worker=1). (If the application was not previously using non-web dynos, skip this step in order to avoid scaling any dynos that you may not need).
Finally, turn off maintenance mode.
$ heroku maintenance:off
Your application is now receiving requests to your upgraded database instance. This can be confirmed by running heroku pg:info. The database denoted by DATABASE_URL is considered the primary database.
Upgrade with follower changeovers
Upgrading a databases with a follower changeover entails provisioning a follower for a database on the newly desired plan, allowing the follower to receive catch-up to the old database, and then promoting it to be the primary database for the application
0. Use cases and time required
Follower upgrades are used when you want to change the plan of a production database (Standard, Premium, or Enterprise tiers, or legacy production plans). Due to a limitation with followers, the major PostgreSQL version cannot change during the upgrade.
The advantage a follower upgrade is that it requires very little downtime, usually less than a minute. Prior to the changeover, it can take several hours to prepare a new follower (during which your application will still be active), so plan accordingly.
1. Create follower
To begin, create a new follower for your database and wait for the follower to catch up to the leader database.
$ heroku addons:create heroku-postgresql:standard-2 --follow HEROKU_POSTGRESQL_LAVENDER_URL --app sushi Adding heroku-postgresql:standard-2 to sushi... 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... available
Once a follower is caught up, it will generally be within 200 commits of the database. Monitor how many commits a follower is behind with the pg:info command (looking at 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. Prevent new database updates
It is important that no new data is written to your application during the upgrade process or 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.
Your application will be unavailable starting at this point in the upgrade process.
$ heroku maintenance:on Enabling maintenance mode for sushi... done
Maintenance mode does not automatically scale down any dynos. Web and any non-web dynos should be scaled down (e.g. heroku ps:scale worker=0) to make sure that no connections are writing data to the database.
3. Promote follower
In maintenance mode no new data will be written to the leader database, assuming that the dynos have been scaled down and any open connections aren’t writing data. Wait for the follower database to catch up to the leader (as indicated by being behind by 0 commits).
$ heroku pg:info === 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 relinquish its follower duties and make it a full, writeable, database. Promoting it will then set it as the primary database (at the DATABASE_URL location) used by your application:
$ heroku pg:unfollow HEROKU_POSTGRESQL_WHITE_URL $ heroku pg:promote HEROKU_POSTGRESQL_WHITE Promoting HEROKU_POSTGRESQL_WHITE_URL to DATABASE_URL... done
The follower database is now the primary database (though the application is not yet receiving new requests).
5. Make application active
To resume normal application operation, scale any non-web dynos back to their original levels (e.g. heroku ps:scale worker=1). (If the application was not previously using non-web dynos, skip this step in order to avoid scaling any dynos that you may not need).
Finally, turn off maintenance mode.
$ heroku maintenance:off
Your application is now receiving requests to your upgraded database instance. This can be confirmed 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 will need to retrieve the HEROKU_POSTGRESQL_WHITE_URL and update your application to use it as your primary database.
Be sure to remove the _URL suffix from the database name in this command.
$ heroku addons:destroy HEROKU_POSTGRESQL_LAVENDER
Upgrade with pg:upgrade
pg:upgrade uses the PostgreSQL pg_upgrade utility to upgrade your PostgreSQL version in-place. Unlike other database upgrade paths, this means that no new instance of your database is created, and your database will remain on the same plan as before. If you need to upgrade both the PostgreSQL version and plan, you can provision a follower on a new plan and perform a pg:upgrade as part of the changeover process.
0. Use cases and time required
pg:upgrade is only supported on Standard, Premium, or Enterprise tier databases. It is used to upgrade the major version of PostgreSQL on your database to the latest version (currently 9.5). It should complete the upgrade in less than 5 minutes.
Postgres 9.3 added data checksums for early corruption detection. If you use pg:upgrade to upgrade a 9.2 database to 9.5 your database won’t be able to use this feature. This means that a corrupt data page on disk could go undiscovered for a long time, which might mean you wouldn’t be able to recover with rollback. In order to enable data checksums, use pg:copy instead.
If you are upgrading a Postgres 9.3 database with checksums using pg:upgrade, those checksums will be preserved.
You can check if your database has checksums enabled by running this query in heroku pg:psql:
=> SELECT setting = 'on' AS enabled FROM pg_settings WHERE name = 'data_checksums'; enabled --------- t (1 row)
pg:upgrade should only be used when the downtime required for a PG copy upgrade is unacceptably long for your business.
1. Create follower
To begin, create a new follower for your database and wait for the follower to catch up to the leader database.
$ heroku addons:create heroku-postgresql:standard-2 --follow HEROKU_POSTGRESQL_LAVENDER_URL --app sushi Adding heroku-postgresql:standard-2 to sushi... 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... available
Once a follower is caught up, it will generally be within 200 commits of the database. Monitor how many commits a follower is behind with the pg:info command (looking at 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. Prevent new database updates
It is important that no new data is written to your application during the upgrade process or 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.
Your application will be unavailable starting at this point in the upgrade process.
$ heroku maintenance:on Enabling maintenance mode for sushi... done
Maintenance mode does not automatically scale down any dynos. Web and any non-web dynos should be scaled down (e.g. heroku ps:scale worker=0) to make sure that no connections are writing data to the database.
3. Upgrade the follower
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 requires a few minutes to complete.
$ heroku pg:upgrade HEROKU_POSTGRESQL_WHITE --app sushi
You can monitor the progress of the upgrade with pg:wait.
heroku pg:wait --app sushi
While the database is upgrading, pg:info will report a status of Upgrading. When the process is complete, the status will change to Available.
4. Promote the new database
Promote the newly upgraded database to set it as the primary database (DATABASE_URL) used by your application:
$ heroku pg:promote HEROKU_POSTGRESQL_WHITE Promoting HEROKU_POSTGRESQL_WHITE_URL to DATABASE_URL... done
The follower database is now the primary database (though the application is not yet receiving new requests).
5. Make application active
To resume normal application operation, scale any non-web dynos back to their original levels (e.g. heroku ps:scale worker=1). (If the application was not previously using non-web dynos, skip this step in order to avoid scaling any dynos that you may not need).
Finally, turn off maintenance mode.
$ heroku maintenance:off
Your application is now receiving requests to your upgraded database instance. This can be confirmed 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 will need to retrieve the HEROKU_POSTGRESQL_WHITE_URL and update your application to use it as your primary database.
Post upgrade
After upgrading your database, you should deprovision the old database so that you aren’t paying for an unused database.
$ heroku addons:destroy HEROKU_POSTGRESQL_LAVENDER
Dataclips that were associated with the old database must be reassigned to the new database. Follow the instructions on Dataclips Recovery to resolve any recoverable Dataclips.