Handling Heroku's "heroku_ext" schema for Postgres extensions

[UPDATE 2023-07-17: Great news! Heroku has announced that they’re rolling back this policy, and that Postgres extensions will once again be allowed to be installed in any schema of all Postgres add-ons by August 7th, 2023.]

[UPDATE 2022-09-26: Heroku has posted an FAQ on this issue with some workarounds for apps that have not migrated their extensions to the new schema.]

Have you installed any Postgres extensions on Heroku? Did your CI start failing recently? Review Apps suddenly stop working? Seeing errors like this one in your build log?

ERROR:  extension "btree_gist" must be installed in schema "heroku_ext"

Beginning August 1, 2022, Heroku began requiring Postgres extensions be installed to a special new schema named heroku_ext as opposed to the default public schema. (In Postgres, a database can contain numerous “schemas”, which are each effectively a namespace for tables and other objects.) The heroku_ext schema is created for you by Heroku in all new and existing Postgres databases. Additionally, while all new CREATE EXTENSION commands must include WITH SCHEMA heroku_ext, pre-existing extensions already created in a database’s public schema will continue to behave as they always have.

This has caused some confusion and frustration, especially because it limits the effectiveness of commands like pg:copy, renders pg_restore of backups that create extensions in the public schema unusable, and results in inconsistencies between environments (i.e. production is likely to have its existing extensions installed to public, but any new Heroku environment must install them to heroku_ext).

To top this all off, if your application didn’t previously deal with multiple Postgres schemas, it does now. This change has some important ramifications for Rails applications, too.

In this post, I’ll share the changes I needed to make to work through this change in a medium-complexity, 8-year-old Rails application that has lived its entire life on Heroku and which leverages two Postgres extensions: btree_gist and tablefunc.

Fixing Heroku CI and review apps

I first noticed this issue when Heroku CI started failing to set up the environment cleanly (with the aforementioned “extension must be installed in schema heroku_ext” error), resulting in a handful of test failures. After attempting a few workarounds, it became clear that the best way out of this problem was through. I decided to:

1. Move all extensions to the heroku_ext schema in every environment by creating a migration that drops each extension from public, creates the heroku_ext schema (if it doesn’t exist), and recreates each extension in the heroku_ext schema
2. Ensure that rake db:migrate and rake db:schema:load continue to work in every Heroku and non-Heroku environment by (ugh) editing a couple old migrations and dynamically modifying structure.sql dumps so that they work regardless of where they run
3. Set up the search_path so that objects on heroku_ext can be referenced without prefixing the schema name (e.g. heroku_ext.function_name()) anywhere else in the source code

‍

Move all extensions to the heroku_ext schema

The first step was to write a mostly-reversible migration:

$ bin/rails g migration move_extensions_to_heroku_ext

Here’s a template for the up and down directives I landed on. Note that in order to drop any extensions, you’ll need to drop all your objects that reference them. Of course, this is much more difficult in cases where the extension includes column types like PostGIS and production data may be affected. In my case, I was able to drop and recreate any functions, triggers, and constraints that relied on an extension.

class MoveExtensionsToHerokuExt < ActiveRecord::Migration[7.0]
  def up
    drop_stuff_that_uses_these_extensions!
    drop_extensions!

    create_heroku_ext_schema!

    create_extensions!(schema_name: "heroku_ext")
    recreate_stuff_that_uses_these_extensions!
  end

  def down
    drop_stuff_that_uses_these_extensions!
    drop_extensions!

    # Don't drop the `heroku_ext` schema, since it already exists in Heroku

    create_extensions!(schema_name: "public")
    recreate_stuff_that_uses_these_extensions!
  end

  private

  def drop_stuff_that_uses_these_extensions!
    execute <<~SQL
      -- Drop any objects that depend on objects owned by your extensions here
    SQL
  end

  def drop_extensions!
    execute <<~SQL
      DROP EXTENSION IF EXISTS btree_gist;
      DROP EXTENSION IF EXISTS tablefunc;
    SQL
  end

  def create_heroku_ext_schema!
    execute <<~SQL
      CREATE SCHEMA IF NOT EXISTS heroku_ext;
    SQL
  end

  def create_extensions!(schema_name:)
    execute <<~SQL
      CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA #{schema_name};
      CREATE EXTENSION IF NOT EXISTS tablefunc WITH SCHEMA #{schema_name};
    SQL
  end

  def recreate_stuff_that_uses_these_extensions!
    execute <<~SQL
      -- Recreate any objects dropped in order to drop the extensions
    SQL
  end
end

With any luck, a migration like this one will do the job. But even still, more work remains!

Ensure db:migrate and db:schema:load continue working in every environment

Once all your database environments have any Postgres extensions installed into a second schema named heroku_ext, it’s important that all your current and future non-Heroku and Heroku-based environments behave consistently.

Fixing db:migrate

Up to this point, rake db:migrate had worked in every environment, given a clean database as a starting point. And in my Procfile, I had this release directive:

release: bin/rake db:migrate

While Heroku CI does not execute the Release phase when running tests, it will run on each Review App before its configured postdeploy script is executed. Of course, running rake db:migrate on Heroku will now fail when an old migration attempts to install an extension to the default public schema.

I tried working around this by skipping the release phase script for review apps and initializing them with db:schema:load instead, but (when that turned out to be non-trivial) ultimately landed on making a couple of minor edits to old migrations.

In practice, this meant editing the first migration that created an extension from this:

execute "create extension btree_gist"

[Note: Any time I make a tweak to an old migration, however minor, it’s usually a smell that I’m due to consider squashing all my old migrations and declaring a new epochal database schema.]

To this:

execute "CREATE SCHEMA IF NOT EXISTS heroku_ext"
execute "create extension btree_gist with schema heroku_ext"

And to tack on with schema heroku_ext to each subsequent migration that created an extension.

Fixing db:schema:load

If you’ve installed Postgres extensions to your Heroku database, you’ve probably already graduated from dumping your schema in the limited-but-portable db/schema.rb format to the accurate-but-messy db/structure.sql dump, as governed by Active Record’s schema_format setting:

config.active_record.schema_format = :sql

When this is set, rake db:schema:dump will shell out to pg_dump and persist its output to db/structure.sql.

There’s just one problem: by creating a heroku_ext schema in your migrations above, your structure.sql file will now contain this line:

--
-- Name: heroku_ext; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA heroku_ext;

This will work fine in non-Heroku environments, but because Heroku databases already have a schema named heroku_ext, the rake db:schema:load task (and any Rake task that depends on it, like db:setup) will fail with:

ERROR:  schema "heroku_ext" already exists

In order to ensure that db:schema:load will work in every environment, this is the structure.sql output I wished I had:

CREATE SCHEMA IF NOT EXISTS heroku_ext;

Alas, there’s no simple way to do this. You might try this arcane configuration to add the --if-exists flag to the pg_dump command

ActiveRecord::Tasks::DatabaseTasks.structure_dump_flags = ["--clean", "--if-exists"]

But this produces a SQL dump that first cleans the database by executing (among other things) DROP SCHEMA IF EXISTS heroku_ext, which isn’t allowed on Heroku, as our user is not the owner of the schema.

Instead, I had to resort to the least invasive monkey-patch of db:schema:dump that I could think of, by calling enhance on the task and then doing a simple find-and-replace for the command.

I did this by adding the following to my app’s Rakefile after Rails.application.load_tasks:

Rake::Task["db:schema:dump"].enhance do
  path = "db/structure.sql"
  IO.write(path, File.open(path) { |f|
    f.read.gsub "CREATE SCHEMA heroku_ext;", <<~SQL.chomp
      -- WARNING: CUSTOM HACK!
      -- Add "IF NOT EXISTS" to only create schema if not created by Heroku
      CREATE SCHEMA IF NOT EXISTS heroku_ext;
    SQL
  })
end

As you might expect, this results in db/structure.sql that contains this:

--
-- Name: heroku_ext; Type: SCHEMA; Schema: -; Owner: -
--

-- WARNING: CUSTOM HACK!
-- Add "IF NOT EXISTS" to only create schema if not created by Heroku
CREATE SCHEMA IF NOT EXISTS heroku_ext;

With this patch, rake db:schema:load can now be run both on Heroku (where heroku_ext is always pre-defined) and off it (where it isn’t).

Set up search_path

To avoid referencing your extensions’ objects with a heroku_ext prefix—which would clutter the source code and further couple the system to this platform-specific implementation detail—the Postgres search_path must be modified to include it.

Interestingly, Rails supports setting a schema_search_path right in your database.yml:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  url: <%= ENV['DATABASE_URL'] %>
  schema_search_path: public,heroku_ext

Using this feature will ensure each connection to the database will have the search path set appropriately. Given that it’s built into Rails, it seems more appropriate than permanently altering the database or manually subscribing to each new connection’s :checkin callback.

There’s just one wrinkle. Setting the schema_search_path option had two unintended side effects:

1. It started dumping a CREATE SCHEMA public command in structure.sql, compounding the issue we just had to workaround above
2. It stopped dumping the extensions on heroku_ext altogether, resulting in them being missing from environments that ran db:schema:load

To make all this behave, one more magical incantation is necessary in config/application.rb:

config.active_record.dump_schemas = :all

At least in my case, this had the effect of once again dumping what we came to expect above while also setting the search_path correctly on each Postgres connection.

This seems not great

This is meant to be nothing more than a quick-and-dirty “how to” article, since I imagine most Heroku power users are going to encounter this sooner than later.

That said, it’s disappointing that this change rolled out without much in the way of change management. No e-mail announcement. No migration tool. No monkey patches baked into their buildpacks.

[Note: If you rely on full backups for high-volume production systems, you should probably move to Heroku’s continuous protection instead. I wrote more about the ergonomics of working with large backups in an earlier post.]

The fact that many users’ backups are no longer able to be restored seems especially problematic, and poses a serious operational reliability concern. Hopefully Heroku responds by easing this migration for folks somehow.

In the meantime, I hope this article helps a few people work through this issue. Because this is a brand new change, feel free to email me if anything in the post is superseded by a better approach or otherwise falls out of date.