Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

[AndrewKniowski]

Is it possible for ArcMap (using 10.3.1) to use a postgresql database where the postgis extension is installed in a schema other than "public"?  I prefer to install extensions in a separate schema especially when they are large like postgis.  However, ArcGIS continues to throw errors when trying to import datasets from our geodatabase that otherwise works fine with this arrangement.  I setup a test database with postgis extension installed in the public schema and was able to import the datasets without error. 

[UmakantJoglekar2]

I remember doing something similar to this a few years back and if I remember right it had worked for us. AS mentioned in the following link, You must use the PostGIS database template to create the PostgreSQL database you use for your geodatabase or enable PostGIS in the database.

http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-postgresql/data-types-postgresql.htm

And I believe this template creates two tables in PUBLIC schema named:

public.spatial_ref_sys

public.geometry_columns

IF you make sure these table exists in PUBLIC schema ArcMap SHOULD be able to handle PostGIS data stored in some other schema. So above two tables should be present in both PUBLIC and the other schema and should also be identical.

I have not tried this with 10.4 but I do remember doing this a few years ago with ArcGIS 10 or 10.X. It would be worth a try to see if it helps.

Thank you!

[UmakantJoglekar2]

IF you make sure these tables exist in PUBLIC schema, ArcMap SHOULD be able to handle PostGIS data stored in some other schema. So, above two tables should be present in both PUBLIC and the other schema AND  they should also be identical.

I have not tried this with 10.4 but I remember doing this a few years ago with ArcGIS 10 or 10.X (dont remember the version of PostgreSQL and PostGIS) with success. It would be worth a try to see if it helps.

Thank you!

[AndrewKniowski]

Thanks for your thoughts.  I think you were on to something -Arc is most likely looking for information in one of those tables or a function and not finding it.  Unfortunately, the information at the link you mentioned is not correct and probably hasn’t been updated in a long time.  The “geometry_columns” table mentioned isn’t a table, and hasn’t been for a while in postgis I don’t believe –it’s a view and as such it is automatically updated. 

I did try manually creating a duplicate spatial_ref_sys table and geometry_columns view in the public schema but that did not change anything.  I also upgraded to ArcMap 10.4 and things actually go worse which is sad honestly.  I was no longer even able to connect to a database that does not have postgis installed in the public schema.  Prior to this I was able to connect and view the list of tables at least.  Again, so long as postgis and its associated functions, tables, and views are installed in the public schema I am able to view and access all the data including spatial tables in different schemas.  I also tried and had similar results with ArcPro 1.1.  The error messages were better in ArcMap however.  ArcPro failed blindly –it did nothing but returned no error.

I may just move the postgis extension to the public schema to avoid fighting the system for the time being. However, I do find this disappointing considering the great ease at which I can connect and access any and all of the database configurations in QGIS.  I hope ESRI takes these issues seriously and continues to improve these features which are currently lagging far behind those of the open source options.

Thanks again for your help and if anyone finds a better workaround please share!

[The_Right_Honorable_MatthewSyp]

I, too, find this supremely disappointing.  It will become more difficult since starting with version 2.3.0, PostGIS will not be able to relocate to a different schema from the one in which it was originally installed.  I've created an "ArcGIS Idea" to allow proper support for use of any schema and not to hard-code "public".  This is more of a defect than a "feature enhancement".

https://community.esri.com/ideas/12688 

[RomanChueh]

I am wondering if you have receive any feed back from ESRI regarding to this issue, or have you found a workaround to connect to PostgreSQL database without public schema?

Thanks.

[The_Right_Honorable_MatthewSyp]

No word from ESRI.  Has anyone tried this in Arc Pro? Maybe they have corrected it there.

[ClaudioRuschel]

PostGIS install a lot of functions in order to work properly. If these functions are not present in the "public" schema, it is needed to include the name of the schema at the function call. Maybe ArcGIS is not finding these functions.

[The_Right_Honorable_MatthewSyp]

ArcGIS is not finding PostGIS functions or types because it is referencing the public schema directly. The error message returns:

Underlying DBMS error[ERROR: schema “public” does not exist

LINE 1: SELECT public.postgis_lib_version()

^SQLSTATE=3F000]

This is easily reproduced by entering the same query directly into Postgres, and easily solved by either not specifying the schema (relying on the search path) or specifying the correct schema. Unfortunately, ArcGIS has no solution, easy or otherwise, that I have been able to find.

[The_Right_Honorable_MatthewSyp]

A support call was unable to resolve this issue. ArcGIS (Desktop or Pro) requires the "public" schema.  The suggested workaround was simply to create the schema, but that does not work.  It only changes the connection error message to:

Failed to connect to the specified DBMS instance.

A database connection exception has occurred.

Function or procedure does not exist.

The database log shows that it is still trying to run

SELECT public.postgis_lib_version()

So ArcGIS is requiring that PostGIS is installed in the "public" schema or it cannot connect.  This is not a PostGIS requirement.  (Schema presence does not solve the issue.).

This is a defect.