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?

CodePudding user response：

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 "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.

Hopefully this is enough to unblock your review apps