Change scheme for postgres extension on heroku

Question

We have an existing heroku App that we sometimes pg:backups restore to other apps. Restoring fails since August 1st (https://devcenter.heroku.com/changelog-items/2446)

The existing app has extenions in public, newly created apps have extensions in heroku_ext. So I wanted to move the extensions to heroku_ext

some-app::DATABASE=> alter extension hstore set schema heroku_ext;
ERROR:  permission denied for schema heroku_ext

Schemas:

some-app::DATABASE=> \dn
       List of schemas
    Name    |     Owner
------------+----------------
 heroku_ext | postgres
 public     | some-user

Extensions (just showing one, for brevity)

some-app::DATABASE=> \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 hstore             | 1.7     | public     | data type for storing sets of (key, value) pairs

I can not drop/recreate the extension since there are hstore columns depending on it. How can I change the schema of the extension?

UPDATE:

got in touch with heroku and they say they are working on the issue and provided following info as workarounds:

Manually migrating Postgres plugins by:

pulling the affected database into a local database manually migrating the relevant extensions locally creating a new database on the app pushing the local DB with migrated extensions into the new database promoting the new database Manually updating schema references. For example:

Changing all instances of WITH SCHEMA public; to WITH SCHEMA heroku_ext; and all instances of: DEFAULT public to DEFAULT heroku_ext by using find & replace in a text editor or by changing the output of pg_dump using sed

Answer 1

You can use the extensions flag when running pg:backups:restore.

heroku pg:backups:restore <backup id> STAGING_DATABASE_URL --extensions 'public.hstore,some_other_extension' -a example_app

Just released:

https://help.heroku.com/ZOFBHJCJ/heroku-postgres-extension-changes-faq

https://github.com/heroku/cli/pull/2043