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

I have developed a monkey-patch solution that is, well, definitely a hack, but better than pre-dating migrations, or deleting lines from schema.rb.

Put this in config/initializers. I called mine _enable_extension_hack.rb to make sure it gets loaded early.

module EnableExtensionHerokuMonkeypatch
  # Earl was here
  def self.apply_patch!
    adapter_const = begin
      Kernel.const_get('ActiveRecord::ConnectionAdapters::PostgreSQLAdapter')
    rescue NameError => ne
      puts "#{self.name} -- #{ne}"
    end

    if adapter_const
      patched_method = adapter_const.instance_method(:enable_extension)

      # Only patch this method if it's method signature matches what we're expecting
      if 1 == patched_method&.arity
        adapter_const.prepend InstanceMethods
      end
    end
  end

  module InstanceMethods
    def enable_extension(name)
      name_override = name

      if schema_exists?('heroku_ext')
        puts "enable_extension -- Adding SCHEMA heroku_ext"
        name_override = "#{name}\" SCHEMA heroku_ext -- Ignore trailing double quote"
      end

      super name_override
    end
  end
end

EnableExtensionHerokuMonkeypatch.apply_patch!

What does it do? It monkey-patches the enable_extension call that's causing the problem in db/schema.rb. Specifically, if it finds that there is a schema called "heroku_ext", it will decorate the parameter given to enable_extension so that the SQL that gets executed specifies the "heroku_ext" schema. Like this:

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" SCHEMA heroku_ext -- Ignore trailing double quote"

Without this hack, the generated SQL looks like this:

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements"

That's cleaner way of patching schema.rb

# config/initializers/patch_enable_extension.rb

require 'active_record/connection_adapters/postgresql_adapter'

# NOTE: patch for https://devcenter.heroku.com/changelog-items/2446
module EnableExtensionHerokuPatch
  def enable_extension(name, **)
    return super unless schema_exists?("heroku_ext")

    exec_query("CREATE EXTENSION IF NOT EXISTS \"#{name}\" SCHEMA heroku_ext").tap { reload_type_map }
  end
end

module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter
      prepend EnableExtensionHerokuPatch
    end
  end
end

EDIT: Here’s the official changelog as a reference https://devcenter.heroku.com/changelog-items/2446

You'll need to do the unthinkable because of the recent heroku changes and modify past migration files for your review apps to work with the new Heroku system for extensions.

1. Add a predated, or at the top of the first migration file connection.execute 'CREATE SCHEMA IF NOT EXISTS heroku_ext'
2. Potentially also add to the database.yml a schema_search_path that includes heroku_ext (or set it to public,heroku_ext if you hadn't customized it)
3. Grep all enable_extension('extension_name')
4. Replace them all by a connection.execute('CREATE EXTENSION IF NOT EXISTS "extension_name" WITH SCHEMA "heroku_ext")
5. Pray that is enough to fix the problem

After making those changes we still had to contact heroku support because of, in order:

• a pgaudit stack is not empty error
  • The fix here was to run a maintenance twice (because the postgres add-on which was scheduled for maintenance pre-dated the schema/extension changes change)
• a ERROR: function pg_stat_statements_reset(oid, oid, bigint) does not exist error
  • The fix here was a manual intervention from heroku on the databases. It was caused by heroku trying to run a pg_stat_statements_reset each time a schema is created.

This hack seems to be working for me. The following script runs in the postdeploy step in app.json.

#!/bin/bash -xue
# Create extensions in the schema where Heroku requires them to be created
# The plpgsql extension has already been created before this script is run
heroku pg:psql -a $HEROKU_APP_NAME -c 'create extension if not exists citext schema heroku_ext'
heroku pg:psql -a $HEROKU_APP_NAME -c 'create extension if not exists pg_stat_statements schema heroku_ext'

# Remove enable_extension statements from schema.rb before loading it, since
# even 'create extension if not exists' fails when the schema is not heroku_ext
mv db/schema.rb{,.orig}
grep -v enable_extension db/schema.rb.orig > db/schema.rb
rails db:schema:load

As of Tuesday 16th August 2022, we've found that a patch no longer seems to be needed and enable_extension appears to be working seamlessly as normal without the need to explicitly specify a schema.

When I mentioned this in our ongoing thread with Heroku Support, they said that some of their fixes around the heroku_ext change may have been released at the time of resolving https://status.heroku.com/incidents/2450, but that there are still further fixes in development and yet to be released - so my guess is that this particular issue is one on those that has been fixed!

The answer supplied by dzirtusss got me most of the way there, the last thing I had to do to get this working for my Rails application was to edit the search paths in config/database.yml to include heroku_ext. This is what I appended to my default configuration.

# config/database.yml
default:
  schema_search_path: public,heroku_ext

After adding the initialization script and the database.yml edit, I was able to successfully run a heroku pg:copy from one application to another with no errors and no unexpected behavior.