Change scheme for postgres extension on heroku-CodePudding

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

CodePudding user response：

Found a workaround that solves my issue with heroku pg:copy

heroku addons:create heroku-postgresql:standard-0 --fork source-app-name::DATABASE --app target-app-name

Instead of copying database contents, this creates a clone of your original database, so the schema is compatible.

Hint: use

heroku pg:promote DB_NAME_OF_CLONE --app target-app-name

to replace the old (schema-incompatible DB) with the new one.

CodePudding user response：

We're also seeing this. It's a shame that Heroku has not publicly addressed this issue. Note that even Heroku-created backups won't restore properly at this point without modification.

We have four applications that we are backing up. Three appear to restore just fine, one fails. The one that fails is using the gis extension--it appears that extensions that include data are failing.

Note that there is at least one extension out there that does not install to the public schema. If you are going to do a mass search & replace on your backup file, make certain that you are only changing those going into the public schema.