在Postgres数据库的多个架构上安装hstore的最佳方法？ [英] Best way to install hstore on multiple schemas in a Postgres database?

问题描述

我正在一个需要在多个架构上使用 hstore 的项目。
安装了 hstore 扩展程序的公共模式在任何地方都不可用，因为我的作用域没有查找公共。在一些试用中，我在名为 hstore的架构上创建了扩展，并在使用的每个可用范围（搜索路径）上使用了该架构。

I'm working on a project that needs to use hstore on multiple schemas. The 'public' schema, where the hstore extension was being installed isn't available everywhere, because my scope doesn't lookup at 'public'. On some tryouts, I've created the extension on a schema called 'hstore' and used the schema on every available scope (search path) used.

基于此，我有一些疑问：

Based on this, I've some questions:

• 可以为扩展名创建架构吗？还是在每个单一模式（例如 customer_1 ， customer_2 等）上创建扩展会更好。 。）？

• Is it ok to create a schema just for the extension? Or is it better to create the extension on every single schema (like, customer_1, customer_2, and so on...)?

在单独的架构中创建扩展是否会影响数据的存储位置？我正在使用多个架构来简化备份/还原，并且真的不希望pg将我的所有 hstore 数据存储在一个隐藏表中（例如 pg_large_objects 表示单个模式。

Does the creation of the extension in a separate schema affect where the data is stored? I'm using multiple schemas to make it easier to backup/restore, and really don't want pg to store all my hstore data in a hidden table (like pg_large_objects for blobs) on a single schema.

推荐答案

每个数据库不允许多次安装扩展。引用 创建扩展 ：

It is not allowed to install extensions multiple times per database. Quoting the manual on CREATE EXTENSION:

请记住，扩展本身不被视为在任何
模式中：扩展具有在数据库范围内必须唯一的
不合格名称。但是属于扩展名的对象可以在模式内。

Remember that the extension itself is not considered to be within any schema: extensions have unqualified names that must be unique database-wide. But objects belonging to the extension can be within schemas.

如果您不想包含 public 在您的 search_path ，将公共扩展安装到专用模式中（例如：扩展 c）。我将对所有这些使用单个模式，而不对每个扩展使用单独的模式。 其中有很多。
创建扩展 提供了一个安装到您选择的现有模式的选项：

If you don't want to include public in your search_path, install "public" extensions into a dedicated schema (example: extensions). I would use a single schema for all of them, not a separate schema for each extension. There are quite a few of them. CREATE EXTENSION offers an option to install to an existing schema of your choice:

    CREATE EXTENSION hstore SCHEMA extensions;

并确保该架构包含在 search_path 可能要使用它的用户。

And make sure the schema is included in the search_path of users who might want to make use of it.

• search_path如何影响标识符的解析度和当前模式

• How does the search_path influence identifier resolution and the "current schema"

扩展名所驻留的架构完全不影响数据存储。

Data storage is not affected at all by the schema the extension resides in.

这篇关于在Postgres数据库的多个架构上安装hstore的最佳方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！