Upgrading Heroku Postgres Databases

Last Updated: 17 March 2015

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 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. Required for upgrading legacy infrastructure.

Requires downtime of about 3 minutes / 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). Requires several hours to prepare a follower (during which an application can remain active), and less than 1 minute of downtime during the changover.
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.

0. 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:add 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.3. If you wish to remain on an identical version you should use the version flag, e.g. --version 9.2.

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 will want to disable those.

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

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

Any non-web dynos should be scaled down as well (maintenance mode automatically scales down all web dynos).

$ heroku ps:scale worker=0
Scaling worker processes... done, now running 0

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 (PINK in this example). In this example, the DATABASE_URL is the source of the data in the transfer.

 $ heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_PINK --app sushi

 !    WARNING: Destructive Action
 !    Transfering data from DATABASE_URL to HEROKU_POSTGRESQL_PINK
 !    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 old-application::OLIVE HEROKU_POSTGRESQL_PINK -a new-application
 !    WARNING: Destructive Action
 !    Transferring data from old-application::OLIVE to HEROKU_POSTGRESQL_PINK
 !    To proceed, type "new-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 wish for 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 (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).

$ heroku ps:scale worker=1

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 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 master database.

$ heroku addons:add 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 will want to disable those.

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

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

Any non-web dynos should be scaled down as well (maintenance mode automatically scales down all web dynos).

$ heroku ps:scale worker=0
Scaling worker processes... done, now running 0

3. Promote follower

In maintenance mode no new data will be written to the master database. Wait for the follower database to catch up to the master (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_URL
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).

4. Make application active

To resume normal application operation, scale any non-web dynos back to their original levels (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).

$ heroku ps:scale worker=1

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:remove 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, your database will remain on the same plan as before. If you need to upgrade both the PostgreSQL version and plan, you can perform a pg:upgrade followed by a follower upgrade.

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 (9.3). It requires very little time to upgrade (< 5 minutes).

pg:upgrade has a significant downside, which is that it does not preserve the checksum information for your database. This means that Heroku Postgres' early corruption detection facility will not work with your database.

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 master database.

$ heroku addons:add 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 will want to disable those.

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

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

Any non-web dynos should be scaled down as well (maintenance mode automatically scales down all web dynos).

$ heroku ps:scale worker=0
Scaling worker processes... done, now running 0

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

4. Promote the new database

Promoting the newkly upgraded database setting it as the primary database (at the DATABASE_URL) used by your application:

$ heroku pg:promote HEROKU_POSTGRESQL_WHITE_URL
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).

4. Make application active

To resume normal application operation, scale any non-web dynos back to their original levels (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).

$ heroku ps:scale worker=1

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 de-provision the old database so that you aren’t paying for an un-used database.

$ heroku addons:remove HEROKU_POSTGRESQL_LAVENDER