Upgrading Heroku Postgres Databases

Last updated 31 May 2017

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.

Preferred Upgrade Method

The preferred method for upgrading Heroku Postgres will depend on a few factors:

• If the initial version of Postgres used on Heroku Postgres was prior to 9.3 and a pg:copy has not been done to upgrade, pg:copy should be used as the upgrade method. Postgres 9.3 introduced data checksums for early corruption detection. Using pg:copy will ensure the checksums get created and data corruption prevented going forward. Once that’s been done, pg:upgrade can be used for subsequent upgrades.
• If the initial version of Postgres used on Heroku Postgres is 9.3 or higher, pg:upgrade is the preferred method of upgrading.
• If the initial version of Postgres used on Heroku Postgres is 9.3 or higher and the database has a lot of “bloat”, extra space taken up by dead rows, pg:copy should be considered but not required. pg:copy will recreate all of the tables and indexes from scratch which will remove all the bloat from the database and save disk space. If downtime is a major consideration, then pg:upgrade should be used.

Upgrade with PG copy

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, it is the only supported upgrade path in the following cases:

• Upgrading from a Hobby to Standard, Premium, Private or Enterprise tier Database
• Upgrading a legacy infrastructure database

It is also the best way to upgrade the PostgreSQL version of Standard, Premium, Private 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, Private 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

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.

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

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

3b (alternative). Transfer data between applications

As an alternative, you can migrate data between databases that are attached to different applications.

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

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

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.

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, Private 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.

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

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

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

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.

$ 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, Private or Enterprise tier databases. It is used to upgrade the major version of PostgreSQL on your database to the latest version (currently 9.6). 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.6 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.

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

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.

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