Last updated November 01, 2024

Heroku recommends testing major PostgreSQL version upgrades for your Heroku Postgres add-ons before completing the upgrade in your production database.

Testing version upgrades helps identify any possible upgrade errors or incompatibility issues that could impact your database or application. Testing helps reduce the downtime for your application when you’re upgrading the production database.

The most common upgrade issues are related to type incompatibilities between PostgreSQL versions, object reference or dependency errors, or general application problems when an app or its PostgreSQL driver version doesn’t support the target PostgreSQL version.

Testing a PostgreSQL Version Upgrade with pg:upgrade

The pg:upgrade method is recommended for all Heroku Postgres databases, except for deprecated mini and basic plans.

To test the upgrade process, fork your existing production database on a non-production app to run and validate the complete upgrade process.

The following creates a fork of your production database on a staging or non-production app:

$ heroku addons:create heroku-postgresql:<database_plan> --fork production_app_name::DATABASE_URL --as PROD_DB_FORK -a staging_app_name

$ heroku addons:create heroku-postgresql:<database_plan> --as PROD_DB_FORK -a staging_app_name -- --fork production_app_name::DATABASE_URL

When the forked database is ready, start the version upgrade process and create a follower for it.

$ heroku addons:create heroku-postgresql:<database_plan> --follow staging_app_name::PROD_DB_FORK --as TEST_UPGRADE_FOLLOWER --app staging_app_name

$ heroku addons:create heroku-postgresql:<database_plan> --as TEST_UPGRADE_FOLLOWER --app staging_app_name -- --follow staging_app_name::PROD_DB_FORK staging_app_name

Follow the steps in the article to upgrade the testing follower. To complete the database version upgrade test, promote the upgraded database as the primary database for your non-production application. Verify that your application can connect and communicate with the database as expected.

$ heroku pg:promote TEST_UPGRADE_FOLLOWER --app staging_app_name

This approach lets you test the complete upgrade process and estimate how long your upgrade takes when you upgrade in production. This process is also helpful to confirm that your application is compatible with the new PG version, which is the case unless your application uses an old PostgreSQL client or driver.

When you’re finished testing the upgrade, you can delete the forked database and the upgraded follower.

Alternative Testing Approach

To speed up the version upgrade testing process, another approach is to create a follower of your production application in a non-production environment. You can run pg:upgrade on the follower directly.

Promote the upgraded database as the primary database in your non-production environment to verify that your application is compatible with the new version.

Testing a PostgreSQL Version Upgrade with pg:copy

Use the pg:copy method to upgrade the PostgreSQL versions of databases under 10 GB.

To test the upgrade process, provision a new database on a non-production application, specifying the target PostgreSQL version:

$ heroku addons:create heroku-postgresql:<database_plan> --version=15 --as DATABASE_UPGRADE_TEST --app staging_app_name

$ heroku addons:create heroku-postgresql:<database_plan> --as DATABASE_UPGRADE_TEST --app staging_app_name -- --version=15

When the new database is available, copy the data from your production database to the new database:

$ heroku pg:copy production_app_name::DATABASE_URL DATABASE_UPGRADE_TEST --app staging_app_name

After the data copy finishes, promote the database to your non-production application to verify that your application is compatible with the new version:

$ heroku pg:promote DATABASE_UPGRADE_TEST --app staging_app_name