postgresql - 更改 heroku 上 postgres 扩展的方案

Question

我们有一个现有的 heroku 应用程序，我们有时会 pg:backups restore 到其他应用程序。自 8 月 1 日起恢复失败 ( https://devcenter.heroku.com/changelog-items/2446 )

现有应用程序在 public 中有扩展，新创建的应用程序在 heroku_ext 中有扩展。所以我想将扩展移动到 heroku_ext

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

模式:

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

扩展(为简洁起见，只显示一个)

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

我无法删除/重新创建扩展，因为有 hstore 列依赖于它。如何更改扩展的架构？

更新:

联系了 heroku，他们说他们正在解决这个问题并提供以下信息作为解决方法:

Manually migrating Postgres plugins by:

pulling the affected database into a local database manually migratingthe relevant extensions locally creating a new database on the apppushing the local DB with migrated extensions into the new databasepromoting the new database Manually updating schema references. Forexample:

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

Answer 1

您可以在运行 pg:backups:restore 时使用 extensions 标志。

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

刚刚发布:

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

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