PostGIS geometry data type no longer recognised

1
votes

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?

1
postgresqlpostgis
Perhaps try the populate_geometry_columns function - songololo
Thanks for your response, I was not aware of this function. Unfortunately I cannot get this to run correctly within a pgadmin query, returning the error "function does not exist" though I have found it within the public schema's functions. Does this need to be executed on the command line? The help on the function indicates that the table name should be passed as a string, however I have tried other forms as well. - A Smith
No it should run from any sql terminal, it sounds like some of the postgis functionality is somehow not available to your Db. Have you possibly tried reenabling the postgis extension? - songololo
I have tried reinstalling PostGIS on the assumption it was a corrupt dll with no success. I have tried to recreate the extension but it will not let me without first dropping the existing extension, and I can't drop that because it has dependants! I am considering a complete reinstall and restore from backup of all of postgres at the moment. - A Smith
For any who come across this, in the end I rebuilt my database and restored from backup. This seems to have solved my problem so far. - A Smith

1 Answers

0
votes

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.