Use PostGIS functions from an other schema (than public)

Question

Context

I have to set up a postgresql database with several schemas.

To separate things, I decided to install postgis elsewhere than the public schema, based on https://postgis.net/2017/11/07/tip-move-postgis-schema/ and Using schema other than public in PostGIS? for example.

I also installed other extensions, such as pgrouting or fuzzystrmatch or citext for example, which by default went into the main of my project schemas called main. And also postgis_topology which is set up into its own schema called "topology", if I'm not wrong.

For my own culture ; is it a good practice to centralise all extensions in one unique schema?

Error

Now, I want to use postgis functions in all my project schemas, but unfortunately, the postgis functions don't seem to be available as I went into this error while creating a database:

psql:create_db.sql:104: ERROR:  type "geometry" does not exist

What's really strange for me is that, OK, the script stops on this error, but when I manually create the table that precisely raises this error, for example with pgAdmin, it goes well!

Question

So why, during the execution of the script, it somehow doesn't find postgis when creating a table with a geometry column, but when creating the table in pgAdmin it does?

It sounds like an async process that is not finished...

Information

"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

"2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"

Answer 1

The PostGIS extension must be in the user search_path. Setting the search_path using ALTER DATABASE or ALTER ROLE will have effect at the next session only. To use the new path immediately, one would rather use SET search_path TO "$user", public, postgis, topology;