ArcGIS Pro doesn't recognise Postgres spatial property

Hal-AhmedShehata

Greetings,

I have two mirroring deployments (Dev & Prod) of postgres with postgis extsnsion (i.e. not esri enterprise geodatabase) on AWS rd servers . The database on Prod was accidently deleted and then restored from old backup files each taken for individual schemas. Now, when I drag the database tables as query layers to ArcGIS Pro, it doesn't recognise the spatial properties despite having geom column. Instead it gets added to TOC as standalone table:

I got in contact with Esri tech support and by comparing Dev instance that's still working normally to Prod instance, they suggested that the login/user connecting to the database on Prod doesn't have enough permissions on the database objects (db, schema, extension, functions, etc) to that on Dev. However, when I gave that user access to all items to replicate Dev setup, ArcGIS Pro still doesn't recognise spatial properties.

Some troubleshooting steps I did before esri's involvment was to make sure geom column is existing, SRID is existing.

Any idea on what I can check to view data spatially from Prod?

CalvinHarmin

Not sure if this is relevant, but are you able to connect to the db as the original schema owner (i.e. using the same user name as schema, not the 'owner' that is potentially different than the schema name) and does it behave the same as the user/role you are trying to grant permissions to?

Did you restore the schema and objects under a different schema name, possibly done by editing the logical backup's SQL commands if you used pg_dump for backup?

Hal-AhmedShehata

Yes, I have a username called "geouser" and there is a schema in the database called "geouser". I can connect to the db using this user. However, the schemas having the geospatial data are different, which is the same set up in Dev. Also, "geouser" became the owner of the (db, public schema, geouser schema, spatial-ref_sys table in public schema and more).

Our IT did the restore, I'm not sure if they followed a specific order or workflow.

Worth mentioning that I can connect to this db from QGIS and open the data normally

CalvinHarmin

Since you mentioned the database / objects were not created as an (enterprise) geodatabase, I am guessing the ESRI documentation here is not so relevant.

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-postgresql/restore-geodatabas...

In the case of EGDB's, the restore process has a few specific steps/orders that must be taken. But since your data is not in an EGDB, I don't know if any of that is relevant, especially since you are showing normal behavior in QGIS.