I am currently running postgresql 9.6.1 with postGIS 2.3 on windows 7 after upgrading several weeks ago from 9.4. After the upgrade my database worked without issue for over a fortnight, however this morning I tried to move a table with geometry data to a different tablespace which returned the error: type "public.geometry" not found.
After investigation, all geom columns in my database are now missing the geometry data type and are unable to load in QGIS. I made no structural changes to my database to trigger this, I had just stopped a slow running query on a single table.
I have tried restarting the service and my PC, have checked that the extension still exists, have reinstalled PostGIS 2.3 and have tried redefining tables with a geometry type through pgadmin 4.
Does anyone have suggestions other than uninstall and reinstall all of postgresql?
As you did PostgreSQL upgrade, you got your PostGIS extension removed and your spatial data types wiped out along with corresponding columns. So, there's now way you can get your data back except you restore it from backup.