Skip Navigation
Show nav
Heroku Dev Center
  • Get Started
  • Documentation
  • Changelog
  • Search
  • Get Started
    • Node.js
    • Ruby on Rails
    • Ruby
    • Python
    • Java
    • PHP
    • Go
    • Scala
    • Clojure
  • Documentation
  • Changelog
  • More
    Additional Resources
    • Home
    • Elements
    • Products
    • Pricing
    • Careers
    • Help
    • Status
    • Events
    • Podcasts
    • Compliance Center
    Heroku Blog

    Heroku Blog

    Find out what's new with Heroku on our blog.

    Visit Blog
  • Log inorSign up
View categories

Categories

  • Heroku Architecture
    • Dynos (app containers)
    • Stacks (operating system images)
    • Networking & DNS
    • Platform Policies
    • Platform Principles
  • Command Line
  • Deployment
    • Deploying with Git
    • Deploying with Docker
    • Deployment Integrations
  • Continuous Delivery
    • Continuous Integration
  • Language Support
    • Node.js
    • Ruby
      • Rails Support
      • Working with Bundler
    • Python
      • Background Jobs in Python
      • Working with Django
    • Java
      • Working with Maven
      • Java Database Operations
      • Working with the Play Framework
      • Working with Spring Boot
      • Java Advanced Topics
    • PHP
    • Go
      • Go Dependency Management
    • Scala
    • Clojure
  • Databases & Data Management
    • Heroku Postgres
      • Postgres Basics
      • Postgres Getting Started
      • Postgres Performance
      • Postgres Data Transfer & Preservation
      • Postgres Availability
      • Postgres Special Topics
    • Heroku Data For Redis
    • Apache Kafka on Heroku
    • Other Data Stores
  • Monitoring & Metrics
    • Logging
  • App Performance
  • Add-ons
    • All Add-ons
  • Collaboration
  • Security
    • App Security
    • Identities & Authentication
    • Compliance
  • Heroku Enterprise
    • Private Spaces
      • Infrastructure Networking
    • Enterprise Accounts
    • Enterprise Teams
    • Heroku Connect (Salesforce sync)
      • Heroku Connect Administration
      • Heroku Connect Reference
      • Heroku Connect Troubleshooting
    • Single Sign-on (SSO)
  • Patterns & Best Practices
  • Extending Heroku
    • Platform API
    • App Webhooks
    • Heroku Labs
    • Building Add-ons
      • Add-on Development Tasks
      • Add-on APIs
      • Add-on Guidelines & Requirements
    • Building CLI Plugins
    • Developing Buildpacks
    • Dev Center
  • Accounts & Billing
  • Troubleshooting & Support
  • Integrating with Salesforce
  • Databases & Data Management
  • Heroku Postgres
  • Postgres Basics
  • Upgrading the Version of a Heroku Postgres Database

Upgrading the Version of a Heroku Postgres Database

English — 日本語に切り替える

Last updated January 23, 2023

Table of Contents

  • Upgrading with pg:upgrade
  • Upgrading with pg:copy
  • Deprovisioning the Old Primary Database

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

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’s a significant operation that must 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 Essential-tier plans.

This method is recommended, unless:
  • You have an Essential-tier plan
  • Your database doesn’t have checksums enabled
  • Your database has a lot of “bloat”

Requires downtime of about 30 minutes, although this amount can vary.
pg:copy Works for all Heroku Postgres plans (including Essential-tier plans), but only for databases under 10 GB.

Requires downtime of about 3 minutes per GB, although this amount can vary substantially.

If you use Heroku Connect to sync Salesforce data with your database, see Heroku Connect’s article on upgrading the Heroku Postgres database version.

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 before 9.3, checksums are probably not enabled for it.

Check whether your database has checksums enabled (on) by running this query in a heroku pg:psql session:

-- `on` means checksums are enabled, `off` means they are not enabled
=> SHOW data_checksums;
 data_checksums
----------------
 on
(1 row)

If your database currently doesn’t 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 remains on the same plan but stops following the current leader.

If you must 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 can fail due to out-of-memory errors for databases with a large number of schemas. If your database has more than 1,000 schemas or stores more than 10,000 objects, test the upgrade in advance and open a Support ticket for assistance if this upgrade fails.

 

You can upgrade your database to versions 12, 13 and 14. You can specify the version that pg:upgrade uses with the --version flag (for example, --version 13). If no --version flag is set, the upgrade defaults to 14.

Performing a pg:upgrade requires app downtime on the order of 30 minutes. This method is supported for all Heroku Postgres plans except Essential-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 can provision the plan best suited for your needs.

$ heroku addons:create heroku-postgresql:standard-2 --follow HEROKU_POSTGRESQL_LAVENDER_URL --app example-app
Adding heroku-postgresql:standard-2 to example-app... 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 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 example-app
=== 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’s important that no new data is written to your current primary database during the upgrade process, because it doesn’t transfer to the new database. To accomplish this, place your app into maintenance mode. If you have scheduler jobs running as well, disable them. Databases continue to accrue billing hours while in maintenance mode.

Maintenance mode doesn’t automatically scale down dynos. Scale down web and any non-web dynos (for example, heroku ps:scale worker=0) to ensure that no connections are writing data to the database.

 

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

$ heroku maintenance:on --app example-app
Enabling maintenance mode for example-app... 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 --app example-app
=== 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 don’t wait for the follower to catch up then you get an error message:

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

When 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 20 minutes to complete.

$ heroku pg:upgrade HEROKU_POSTGRESQL_WHITE --app example-app

You can monitor the progress of the upgrade with pg:wait.

$ heroku pg:wait --app example-app

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 before 5.0 have a known compatibility issue with Postgres 10 and above. To work around this issue, upgrade your version of Rails or use the monkey patch suggested in the Rails issue.

4. Promote the New Database

Promote the newly upgraded database to set it as the primary database (DATABASE_URL) used by your application. pg:promote also creates an alternate attachment for the old primary database, assigned with a new HEROKU_POSTGRESQL_<color>_URL config var. The promotion process triggers a release and restarts the app.

$ heroku pg:promote HEROKU_POSTGRESQL_WHITE --app example-app
Promoting HEROKU_POSTGRESQL_WHITE_URL to DATABASE_URL... done

The follower database is now the primary database (although the application isn’t yet receiving new requests).

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

After the promotion, followers of your original primary database don’t 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 --app example-app

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 reattaches the connection pooler to the new primary under the same name DATABASE_CONNECTION_POOL.

Attachments under non-default names aren’t reattached and you must 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 example-app

5. Exit Maintenance Mode

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

Finally, turn off maintenance mode:

$ heroku maintenance:off --app example-app

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 isn’t connected to a Heroku application, you must 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 amount 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 promoting it).

The pg:copy method supports upgrades between all supported Heroku Postgres plans and versions. It’s the only method for making version changes that involve an Essential-tier database, or upgrading an Essential-tier database to a plan in another tier. pg:copy only copies your default credential and the data it has access to. Any additional credentials and data that only they can access don’t get copied.

For Essential-tier databases that use the scheduled backups feature, your schedules are lost after upgrading to production-tier plans. Set up your schedules again after the upgrade to avoid missing a backup.

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 can provision the plan best suited for your needs):

$ heroku addons:create heroku-postgresql:standard-0 --app example-app
Adding heroku-postgresql:standard-0 on example-app... 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 the most recent supported version, specify the version to use with the --version flag (for example, --version 11).

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 -a example-app
Waiting for database HEROKU_POSTGRESQL_PINK_URL... available

2. Enter Maintenance Mode to Prevent Database Writes

It’s important that no new data is written to your current primary database during the upgrade process, because it doesn’t transfer to the new database. To accomplish this, place your app into maintenance mode. If you have scheduler jobs running as well, disable them.

Maintenance mode doesn’t automatically scale down dynos. Scale down web and any non-web dynos (for example, heroku ps:scale worker=0) to ensure that no connections are writing data to the database.

 

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

$ heroku maintenance:on -a example-app
Enabling maintenance mode for example-app... 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 example-app

!    WARNING: Destructive Action
!    Transfering data from DATABASE_URL to HEROKU_POSTGRESQL_PINK
!    This command will affect the app: example-app
!    To proceed, type "example-app" or re-run this command with --confirm example-app

> example-app

4. Promote the New Database

At this point, the new database is populated with the data from the original database, but isn’t yet the active database for your application. To make the new upgraded database the primary database for your application, you promote with:

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

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

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

After the promotion, followers of your original primary database don’t 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 example-app

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 (for example, heroku ps:scale worker=1).

Finally, turn off maintenance mode:

$ heroku maintenance:off --app example-app

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 isn’t connected to a Heroku application, you must 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 --app example-app

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.

Keep reading

  • Postgres Basics

Feedback

Log in to submit feedback.

Information & Support

  • Getting Started
  • Documentation
  • Changelog
  • Compliance Center
  • Training & Education
  • Blog
  • Podcasts
  • Support Channels
  • Status

Language Reference

  • Node.js
  • Ruby
  • Java
  • PHP
  • Python
  • Go
  • Scala
  • Clojure

Other Resources

  • Careers
  • Elements
  • Products
  • Pricing

Subscribe to our monthly newsletter

Your email address:

  • RSS
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku Blog
    • Heroku News Blog
    • Heroku Engineering Blog
  • Heroku Podcasts
  • Twitter
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku
    • Heroku Status
  • Facebook
  • Instagram
  • Github
  • LinkedIn
  • YouTube
Heroku is acompany

 © Salesforce.com

  • heroku.com
  • Terms of Service
  • Privacy
  • Cookies
  • Cookie Preferences