Here are some notes on the PostgreSQL 13 to 15 upgrade process on Heroku. The Projectify backend uses PostgreSQL as its database backend. Before, Projectify would run on PostgreSQL 13.13. While PostgreSQL 13 is still actively supported by the PostgreSQL maintainers at the time of writing, Debian 12 supports PostgreSQL 15. The current version of PostgreSQL is 16.1.

Debian 12 came out in June 2023. Even after upgrading, developing Projectify locally still depended on PostgreSQL 13. Between having a containerized continuous integration, and using Nix flakes to develop locally, I’ve wanted to use newer PostgreSQL for a while now. With Debian 12, PostgreSQL 15 is the officially supported version, and all runtime environments can finally catch up.

The upgrade procedure

Upgrading PostgreSQL versions is effortless and highly automatable. If you are using a managed PostgreSQL instance on Heroku, the process consists of the following steps:

Spin up a follower instance to the existing PostgreSQL 13 instance
Wait for the follower to catch up
Enter maintenance mode in the app
Upgrade the follower to PostgreSQL 15
Switch the follower to be the new primary database
Leave maintenance mode
Clean up and remove the old database instance

This is well documented in the Heroku Dev Center and this post is another data point of how a successful upgrade looks like.

Create a follower instance

First, I dial into a nix shell with Heroku using the following invocation:

nix-shell -p heroku

This downloads and launches a bash session in which you can run the Heroku command-line tool called heroku:

this path will be fetched (8.09 MiB download, 87.36 MiB unpacked):
  /nix/store/vwq72x1pw7ks1w3xfa1wm2l0dnirlhwp-heroku-7.66.4
copying path '/nix/store/vwq72x1pw7ks1w3xfa1wm2l0dnirlhwp-heroku-7.66.4' from 'https://cache.nixos.org'...
[nix-shell:~/projects/projectify/projectify-backend]$

I log in using heroku login, and proceed to create the follower database using the following, where MYAPP stands for the name of the Projectify backend app:

heroku addons:create heroku-postgresql:standard-0 --follow DATABASE --app MYAPP

This is how it should look like when Heroku correctly creates the follower database:

Creating heroku-postgresql:standard-0 on ⬢ MYAPP... $50/month
 ▸    Release command executing: config vars set by this add-on will not be available until the command succeeds. Use `heroku releases:output` to view the log.
The database should be available in 3-5 minutes.
Use `heroku pg:wait` to track status.
postgresql-XXXXXXXXX-11111 is being created in the background. The app will restart when complete...
Use heroku addons:info postgresql-XXXXXXXXX-11111 to check creation progress
Use heroku addons:docs heroku-postgresql to view documentation

Catching up with the primary

After adding the new follower database, Heroku instructs you to wait using the pg:wait command. Run heroku pg:wait --app MYAPP, and a few minutes later, you should see the following output:

Waiting for database postgresql-XXXXXXXXX-11111... available

The command heroku pg:info --app MYAPP lets you review the state of all provisioned databases. Note that DATABASE_URL is the previous database that Projectify has been using, and HEROKU_POSTGRESQL_NAVY is the new database. Note that the PostgreSQL versions in the output are still 13.13 at this point:

=== DATABASE_URL
Plan:                  Standard 0
Status:                Available
[...]
PG Version:            13.13
[...]
Created:               2022-06-30 07:28
[...]

=== HEROKU_POSTGRESQL_NAVY_URL
Plan:                  Standard 0
Status:                Available
[...]
PG Version:            13.13
[...]
Created:               2024-01-31 02:28
[...]
Following:             DATABASE
Behind By:             0 commits
[...]

According to this output, HEROKU_POSTGRESQL_NAVY follows the primary database DATABASE and has caught up with any pending commits. (0 commits in the output)

Entering maintenance mode

It’s important to avoid changes to the primary databases from now on so that primary and database don’t fall out of sync. Heroku recommends entering maintenance mode by running the following command:

heroku maintenance:on --app MYAPP

When maintenance mode becomes active, the command prints the following:

Enabling maintenance mode for ⬢ MYAPP... done

To confirm that maintenance mode is active, I try connecting to the Projectify API at https://api.projectifyapp.com/. I’m presented with a Heroku maintenance screen and know that it’s safe to move to the next step.

Upgrading the follower

The follower database NAVY is now ready for the PostgreSQL 15 upgrade. Following the instructions from the Heroku documentation, I run the following command:

heroku pg:upgrade HEROKU_POSTGRESQL_NAVY_URL --app MYAPP

The upgrade process requires you to confirm the name of the app to go through with the upgrade:

 ▸    WARNING: Destructive action
 ▸    postgresql-XXXXXXXXX-11111 will be upgraded to a newer PostgreSQL version, stop following DATABASE, and become writable.
 ▸
 ▸    This cannot be undone.
 ▸    To proceed, type MYAPP or re-run this command with --confirm MYAPP

> MYAPP
Starting upgrade of postgresql-XXXXXXXXX-11111... heroku pg:wait to track status

The Heroku docs tell you to wait for the upgrade to finish and use the following command:

heroku pg:wait --app MYAPP

This command runs as long as the database upgrade takes. When it’s done, the command prints the following:

Waiting for database postgresql-XXXXXXXXX-11111... performing final cleanup steps after upgrade

The upgrade finished without any complications. The follower instance is ready and becomes the primary DATABASE_URL in the next step.

Follower to primary switch-over

The following command changed the new database to become the primary database for the Projectify app:

heroku pg:promote HEROKU_POSTGRESQL_NAVY_URL --app MYAPP

Heroku prints out the following to confirm that the promotion worked:

Ensuring an alternate alias for existing DATABASE_URL... HEROKU_POSTGRESQL_AMBER_URL
Promoting postgresql-XXXXXXXXX-11111 to DATABASE_URL on ⬢ MYAPP... done
Checking release phase... pg:promote succeeded. It is safe to ignore the failed Detach DATABASE (@ref:postgresql-XXXXXXXXXX-11111) release.

This means that Heroku switched the previous primary to use the new database called HEROKU_POSTGRESQL_AMBER and that it’s safe to leave maintenance mode. To review the new state of the app, I ran heroku pg:info one more time. This is the result:

=== DATABASE_URL, HEROKU_POSTGRESQL_NAVY_URL
Plan:                  Standard 0
Status:                Available
[...]
PG Version:            15.5
[...]
Created:               2024-01-31 02:28
[...]
Forked From:           HEROKU_POSTGRESQL_AMBER
[...]

=== HEROKU_POSTGRESQL_AMBER_URL
Plan:                  Standard 0
Status:                Available
[...]
PG Version:            13.13
[...]
Created:               2022-06-30 07:28
[...]
Forks:                 HEROKU_POSTGRESQL_NAVY
[...]

This Heroku app now has two database instances running:

DATABASE_URL, or HEROKU_POSTGRESQL_NAVY, the upgraded follower of HEROKU_POSTGRESQL_NAVY running PostgreSQL 15.5 and the current primary database that the Django backend is using.
HEROKU_POSTGRESQL_AMBER, running PostgreSQL 13.13. This is the former primary database that HEROKU_POSTGRESQL_NAVY followed.

Before, DATABASE_URL in the top would show 13.13 as the PG version. This means that the primary database version upgrade has been successful.

Leaving maintenance mode

To leave maintenance mode, I run the command heroku maintenance:off --app MYAPP. Heroku prints out the following to confirm that maintenance mode is now off:

Disabling maintenance mode for ⬢ MYAPP... done

I manually test the app by logging in to the backend and frontend and confirm that everything works well.

Decommissioning the previous database instance

There’s no need to keep the previous database around and in the next step I’m going to decommission it. No one should leave their user’s PII hanging around where it doesn’t serve them. Saving $50/month on managed database costs is also nice.

I run the following command using the Heroku command-line tool:

heroku addons:destroy HEROKU_POSTGRESQL_AMBER --app MYAPP

Heroku prompts you one more time confirm destructive actions like this. I type the app name and Heroku tells me I’m done:

 ▸    WARNING: Destructive Action
 ▸    This command will affect the app MYAPP
 ▸    To proceed, type MYAPP or re-run this command with --confirm MYAPP

> MYAPP
Destroying postgresql-XXXXXXXXXX-11111 on ⬢ MYAPP... done

This means I’m done upgrading PostgreSQL on Heroku. I can now continue updating all development environments to use PostgreSQL 15. The changes to the Projectify backend repository are minimal:

diff --git a/.circleci/config.yml b/.circleci/config.yml
index 144897e..2b70c4a 100644
--- a/.circleci/config.yml
+++ b/.circleci/config.yml
@@ -16,7 +16,7 @@ executors:
           DJANGO_SETTINGS_MODULE: projectify.settings.test
           DJANGO_CONFIGURATION: Test
           DATABASE_URL: "postgres://projectify:projectify@localhost:5432/projectify"
-      - image: postgres:13.5
+      - image: postgres:15.5
         environment:
           POSTGRES_DB: projectify
           POSTGRES_USER: projectify
diff --git a/README.md b/README.md
index 9593e23..0318953 100644
--- a/README.md
+++ b/README.md
[...] adjust documentation
diff --git a/flake.nix b/flake.nix
index ef9d849..faa4ddd 100644
--- a/flake.nix
+++ b/flake.nix
@@ -17,7 +17,7 @@
         pkgs = nixpkgs.legacyPackages.${system};
         inherit (poetry2nix.lib.mkPoetry2Nix { inherit pkgs; }) mkPoetryEnv defaultPoetryOverrides;
         projectDir = self;
-        postgresql = pkgs.postgresql_13;
+        postgresql = pkgs.postgresql_15;
         overrides = defaultPoetryOverrides.extend (self: super: {
           django-cloudinary-storage = super.django-cloudinary-storage.overridePythonAttrs (
             old: {

On Debian, I install PostgreSQL using

sudo apt install postgresql-15

And that concludes the upgrade procedure.

JWP Consulting GK

Upgrading PostgreSQL 13 to 15