Upgrading the Version of a Heroku Postgres Database
Last updated 14 January 2021
Table of Contents
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 12.
If you want to upgrade your database’s minor version, or if you only want to change your Postgres plan or underlying infrastructure, see Changing the Plan or Infrastructure of a Heroku Postgres Database.
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.
Upgrade Method | Description |
---|---|
pg:upgrade | Works for all Heroku Postgres plans except hobby-tier plans. This method is recommended unless:
Requires downtime of about 10 minutes, although this can vary. |
pg:copy |
Works for all Heroku Postgres plans (including hobby-tier plans), but only for databases under 10GB in size.
Requires downtime of about 3 minutes per GB, although this can vary substantially. |
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. This command can only be used to upgrade a follower database, which will remain on the same plan but stop following the current leader.
If you need to upgrade both the PostgreSQL version and your plan, you can provision a new follower on a different plan and perform a pg:upgrade
as part of the changeover process.
The pg:upgrade
command is intended for databases with a moderate amount of relations and schemas. If your database has thousands of schemas, reach out to postgres@heroku.com for assistance.
You can upgrade your database to versions 9.6, 10, 11, and 12. You can specify the version that pg:upgrade
should use with the --version
flag (e.g., --version 11
). If no --version
flag is set, the upgrade will default to 12.
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.
Maintenance mode does not automatically scale down dynos. Web and any non-web dynos should be scaled down (e.g. heroku ps:scale worker=0
) to ensure that no connections are writing data to the database.
Your application will be unavailable starting at this point in the upgrade process.
$ 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.
Versions of Rails pre 5.0 have a known compatibility issue with Postgres 10 and above. Please upgrade your version of rails, use the monkey patch suggested in the Rails issue or pg:upgrade
to 9.6.
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
.
After the promotion, followers of your original primary database do not automatically start to follow your new primary.
Create new followers for the new primary database as needed:
$ heroku addons:create heroku-postgresql:standard-0 --follow DATABASE_URL -a sushi
Be sure to deprovision your old followers after you no longer need them.
If your old primary was using connection pooling, and it was attached with the default name DATABASE_CONNECTION_POOL
, the promote will reattach the connection pooler to the new primary under the same name DATABASE_CONNECTION_POOL
.
Attachments under non-default names will not be reattached and you should activate connection pooling on your new primary if you wish to keep using connection pooling on your new primary with the same non-default name as the old primary:
$ heroku pg:connection-pooling:attach DATABASE_URL --as MY_DATABASE_CONNECTION_POOL -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.
If your Heroku Postgres database is not connected to a Heroku application, you need to retrieve the HEROKU_POSTGRESQL_WHITE_URL
and update your application to use it as your 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
If you want to upgrade your database to a version of PostgreSQL other than than the most recent supported version, specify the version to use with the --version
flag (e.g., --version 9.6
).
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.
Maintenance mode does not automatically scale down dynos. Web and any non-web dynos should be scaled down (e.g. heroku ps:scale worker=0
) to ensure that no connections are writing data to the database.
Your application will be unavailable starting at this point in the upgrade process.
$ 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
.
After the promotion, followers of your original primary database do not automatically start to follow your new primary.
Create new followers for the new primary database as needed:
$ heroku addons:create heroku-postgresql:standard-0 --follow DATABASE_URL -a sushi
Be sure to deprovision your old followers after you no longer need them.
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.
If your Heroku Postgres database is not connected to a Heroku application, you need to retrieve the HEROKU_POSTGRESQL_WHITE_URL
and update your application to use it as your 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
Dataclips that were associated with the old primary database must be reassigned to the new database. Follow the instructions on Dataclip recovery to resolve any recoverable Dataclips.