Best way to install hstore on multiple schemas in a Postgres database?

Question

I am working on a project that should use hstore for several schemes. The "public" scheme in which the hstore extension is hstore is not available everywhere because my area is not looking for "public". On some attempts, I created an extension on a diagram called "hstore" and used the diagram for each available area (search path).

Based on this, I have a few questions:

• Is it possible to create a schema for expansion only? Or is it better to create an extension for each individual scheme (for example, customer_1 , customer_2 , etc.)?

• Is an extension extension created in a separate schema where data is stored? I use several schemes to simplify backup / restore, and really do not want pg to save all my hstore data in a hidden table (e.g. pg_large_objects for blobs) on the same scheme.

Answer 1

You cannot install extensions several times for each database. Citation Guide for CREATE EXTENSION :

Remember that the extension itself is not considered a schema: extensions have unqualified names that must be unique databases on a scale. But objects related to the extension may be within the scope of the schemes.

If you do not want to include public in the search_path , set the "public" extensions to the selected scheme (example: extensions ). I would use one scheme for all of them, and not a separate scheme for each extension. There are a lot of them. CREATE EXTENSION offers the ability to install an existing circuit of your choice:

  CREATE EXTENSION hstore SCHEMA extensions; 

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

• How does the identifier identifier search_path and the "current scheme"

The data warehouse does not affect the schema in which the extension is located at all.

Answer 2

Try installing the hstore extension on all circuits

 create extension hstore schema pg_catalog;