I changed Postgis Schema Name in QGIS, now my geometry column is corrupted, how do I fix

Question

I changed name of PostGIS2.5 Schema from public to supervisor using QGIS 3.2.4;

after that I discovered that my data was now unable to display geometry. If you check the geometry column in pgAdmin4, it now reads "supervisor.geometry" instead of just, "geometry"

See how the geom column looks like

when I try to change data type of the column in pgAdmin, its showing that "geometry" data type no longer exists, what can be found is supervisor.geometry

Answer 1

You have logged-in as user Postgres (who can presumably do whatever it wants in the DB) and renamed the public schema and now extensions stored within the public schema are not working anymore.

Honestly, about everything is wrong in this sentence.

Rename it back to public - in lower case, that should fix your immediate issue.

Then, decide what you want: have postgis functions in another schema? Prevent creating objects in public? Allow creating new object in another schema by default? Move your GIS tables to another schema?

Then read about installing or moving the Postgis extension in another schema, about creating new users, about creating a new schema, about granting usage/read/write access to schema and tables to users, about setting the search_path etc.

And, last but not least, keep the following two tips in mind:

1. leave the public schema alone (instead, prevent users from using it if you wish)
2. never use a super-user account such as Postgres for daily tasks (instead, create different DB users with just enough privileges for their tasks. There is no need to be able to delete the entire DB if the user is supposed to simply read/write to a few tables)

Answer 2

What you used to know as geometry was actually public.geometry thanks to default search_path setting (db setting, not an environment variable) of "$user", public.

Whenever you referred to a table, type or anything without schema specified, Postgres first checked if there's a schema with the same name as your current user ("$user") and if that object is in there. Then it checked public and since it's where PostGIS types and functions are by default, it worked. It actually also checks pg_catalog for built-in stuff as well as session-specific pg_temp_nnn where your temp objects are.

Now that you renamed public to Supervisorrrrr, geometry is no longer in any schema it's checking in search_path list. You could run

select set_config( 'search_path'
                  ,(current_setting('search_path')||', "Supervisorrrrr"')
                  ,false);

But it's best you change it back to avoid breaking other things.

alter schema "Supervisorrrrr" rename to public;

If you want to move out your things from public, run a series of alters:

create schema "Supervisorrrrr";
alter table public.your_table set schema "Supervisorrrrr";
alter view public.your_view set schema "Supervisorrrrr";

To keep things tidy (-er/-ish) in the future, you could

• Require your users to always use schema-qualified names, which might be inconvenient
• Create a dedicated, private schema for each of your users, named after their usernames. That way by default, their stuff ends up in their schema, without interfering with others in the Wild West Republic.
• If you're working alone and/or that's a throwaway GIS lab you don't wont to tinker too much with, only clean it up a bit, next time you (or others) want to build and modify structures

  create schema your_new_schema_for_new_stuff;
  set search_path = your_new_schema_for_new_stuff, public;
  

  That way subsequent queries will target the new schema by default, without littering public, while still being able to refer to types and functions in public.
• Do not operate as a superuser by default - not in the DB, not in your operating system, not anywhere.