Upgrading Heroku Postgres Databases

Last Updated: 24 February 2015

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.

The PGBackups:Transfer 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 PGBackups:Transfer.

Upgrade with PGBackups:transfer (default)

PGBackups::transfer 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

PGBackups:transfer 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).

PGBackups:transfer 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. Install PGBackups

The PGBackups add-on is required. If you don’t already have it installed on your app, do so:

$ heroku addons:add pgbackups

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

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

$ 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

4. Transfer data to new database

To transfer data from your current database to the newly provisioned database, use the pgbackups:transfer 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 pgbackups:transfer DATABASE_URL HEROKU_POSTGRESQL_PINK_URL --app sushi

 !    WARNING: Destructive Action
 !    Transfering data from DATABASE_URL to HEROKU_POSTGRESQL_PINK_URL
 !    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.

To transfer the source database to the target database you will need to invoke pgbackups 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 pgbackups:capture prior to transfering is a good idea.

$ heroku pgbackups:transfer old-application::OLIVE HEROKU_POSTGRESQL_PINK_URL -a new-application
 !    WARNING: Destructive Action
 !    Transfering data from old-application::OLIVE to HEROKU_POSTGRESQL_PINK_URL
 !    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_URL
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.

$ 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_URL
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_URL
Plan:        Standard 0
Status:      available
...
=== HEROKU_POSTGRESQL_WHITE_URL
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.

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

$ 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 pgbackups:transfer 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_URL
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_URL
Plan:        Standard 0
Status:      available
...
=== HEROKU_POSTGRESQL_WHITE_URL
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.

$ 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_URL --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.

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_URL