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

Question

Heroku made a change to the way postgressql extensions gets installed

This is screwing up new rails review apps in heroku with the following error.

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

This is screwing up things as I need to drop existing extensions and re-enable with heroku_ext schema. I use bin/rails db:structure:load which is before running a migration.

Also the structure.sql is going to diverge as heroku add the schema in review app and we need to run the creation manually in local dev machine.

Does anybody came across this issue?

Answer 1

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"

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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!

Answer 6

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.

Answer 7

Heroku has introduced an --extensions flag recently, which helps as a workaround. See this FAQ.

For example, if you're using the btree_gist extension, you can use heroku run pg:reset --extensions 'btree_gist' --app YOUR_APP_NAME before running your migrations, they should then work without any changes in your codebase.

Answer 8

1. Use the Heroku datastore durability tool to create a backup on the source database or heroku pg:backups:capture -a <SOURCE_APP>.
2. Determine which pg extensions the database uses (can check from psql with \dx)
3. create a comma-separated string of the extensions (e.g.:fuzzystrmatch,pg_stat_statements,pg_trgm,pgcrypto,plpgsql,unaccent,uuid-ossp')
4. Make sure your Heroku CLI is updated to at least version 7.63.0 (use heroku update to update)
5. Run this:

   heroku pg:backups:restore $(heroku pg:backups public-url -a <SOURCE_APP>) DATABASE_URL --extensions '<EXTENSIONS>' -a <TARGET_APP>
   

6. Reset dynos on the TARGET_APP