Upgrading Heroku Postgres Databases

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.

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.

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.

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 plan (Standard 0 in the example below) 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:

If you are upgrading from an older version of PostgreSQL your new database by default will be 9.6. 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.

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.

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.

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.

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):

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

You now have a new follower database that is running on your new plan, or new (minor) Postgres version. However this database is currently read-only, and the old database is still the leader. Now that you are in maintenance mode, you can promote the new database to take over from the old one.

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).

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:

The follower database is now the primary database (though the application is not yet receiving new requests).

4. 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:

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):

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:

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.