Upgrading the Version of a Heroku Postgres Database

Last updated 10 September 2018

This article describes how to upgrade the major PostgreSQL version of a Heroku Postgres database. The most recent major PostgreSQL version supported by Heroku is 10.

Upgrading your major PostgreSQL version can be done only via the Heroku CLI. It is a significant operation that should be done with care.

Heroku supports two methods for upgrading. Both methods require some application downtime to ensure that no data is lost during the upgrade.

Data checksums

Postgres 9.3 introduced data checksums for early data corruption detection. If you initially provisioned your Heroku Postgres database with a version of PostgreSQL prior to 9.3, checksums are probably not enabled for it.

You can check whether 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)

If your database currently does not have checksums enabled and the downtime required for a pg:copy upgrade is acceptable for your app, use the pg:copy upgrade method to ensure that checksums are enabled as part of the upgrade.

Database bloat

If your database has a lot of “bloat” (extra space taken up by dead rows) and the downtime required for a pg:copy upgrade is acceptable for your app, use the pg:copy upgrade method to recreate all of the tables and indexes from scratch. Doing so removes all of the bloat from the database and saves disk space.

If downtime is a major consideration, then use pg:upgrade instead.

Upgrading with pg:upgrade

The pg:upgrade command uses the PostgreSQL pg_upgrade utility to upgrade your PostgreSQL version in-place. Unlike with other database upgrade methods, no new instance of your database is created, and your database remains on the same plan as before.

If you need to upgrade both the PostgreSQL version and your plan, you can provision a follower on a new plan and perform a pg:upgrade as part of the changeover process.

Performing a pg:upgrade requires app downtime on the order of 10 minutes. This method is supported for all Heroku Postgres plans except hobby-tier plans.

1. Provision a follower database

To begin, create a new follower for your database and wait for the follower to catch up to the leader database. In the example below the standard-2 plan is used, but you should provision the plan best suited for your needs.

$ 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

The follower is generally considered “caught up” when it is within 200 commits of the primary database. You can check how many commits the follower is behind with the pg:info command (see 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. Enter maintenance mode to prevent database writes

It is important that no new data is written to your current primary database during the upgrade process, because 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 database

Now that you are in maintenance mode and no additional data is being written to the primary database, you can upgrade the follower database.

Wait for the follower database to fully catch up to the primary (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

If you do not wait for the follower to catch up then you may get an error message:

 ▸    database must not be too far behind leader, please wait until your follower catches up with its leader.

Once the follower is caught up, 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 usually requires about 5 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

As part of the pg:upgrade process, Heroku Postgres runs ANALYZE on your database: this recalculates statistics for your database to make sure the Postgres query planner has up to date information even after a version upgrade.

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 (although the application is not yet receiving new requests).

If your original primary database was attached to multiple apps, you need to attach your new database to those apps with heroku addons:attach.

$ heroku addons:create heroku-postgresql:standard-0 --follow DATABASE_URL -a sushi

5. Exit maintenance mode

To resume normal application operation, scale any non-web dynos back to their original levels (e.g., heroku ps:scale worker=1).

Finally, turn off maintenance mode:

$ heroku maintenance:off

Your application is now receiving requests to your updated database instance. You can confirm this by running heroku pg:info. The database denoted by DATABASE_URL is considered the primary database.

Upgrading with pg:copy

The pg:copy upgrade method uses native PostgreSQL backup and restore utilities. Instead of writing a database backup to disk, it streams the backup data directly to the restore process of a newly provisioned database.

The pg:copy method requires approximately 3 minutes of app downtime per GB of your current database, although this can vary substantially depending on your schema and database plan. You can estimate your required downtime by performing a dry run (perform the upgrade process on a new database without then promoting it).

The pg:copy method supports upgrades between all supported Heroku Postgres plans and versions. It is the only upgrade method that supports hobby-tier plans. pg:copy will only copy your default credential and the data it has access to. Any additional credentials and data that only they can access will not be copied.

1. Provision a new database

Provision a new Heroku Postgres database with your desired plan (in the example below the standard-0 plan is used, but you should provision the plan best suited for your needs):

$ 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 Shield tiers take a few minutes to provision. You can use the pg:wait command to notify you when provisioning is complete:

$ heroku pg:wait
Waiting for database HEROKU_POSTGRESQL_PINK_URL... available

2. Enter maintenance mode to prevent database writes

It is important that no new data is written to your current primary database during the upgrade process, because 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. Transfer data to the 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

4. Promote the 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. To make the new upgraded database the primary database for your application, you promote it like so:

$ heroku pg:promote HEROKU_POSTGRESQL_PINK
Promoting HEROKU_POSTGRESQL_PINK_URL to DATABASE_URL... done

The upgraded database is now the primary database (although the application is not yet receiving new requests).

If your original primary database was attached to multiple apps, you need to attach your new database to those apps with heroku addons:attach.

$ heroku addons:create heroku-postgresql:standard-0 --follow DATABASE_URL -a sushi

5. Exit maintenance mode

To resume normal application operation, scale any non-web dynos back to their original levels (e.g., heroku ps:scale worker=1).

Finally, turn off maintenance mode:

$ heroku maintenance:off

Your application is now receiving requests to your new database instance. You can confirm this by running heroku pg:info. The database denoted by DATABASE_URL is considered the primary database.

Deprovisioning the old primary database

After you upgrade your database, be sure to deprovision your old primary database:

$ heroku addons:destroy HEROKU_POSTGRESQL_LAVENDER