[GIS] Can’t connect with the PostgreSQL database with ArcGIS

arcgis-desktopconnectionpostgispostgresql

I have created a database in PostgreSQL, spatially enabled it, and now I need to connect it with ArcGIS (we use only ArcGIS products here on my university), everything on localhost, I've tried everything I could find here on stackexchange and on the ESRI support forums, nothing seems to work, I'm a little desperate

Here, I will describe my entire situation and the solutions I've tried:

  • I used to have a 64bit PG instalation on my 64bit machine, reinstalled both Postgre and PostGIS and recreated a database on 32 bit according to ESRI support forums, nothing changed.
  • I use 10.5 ArcGIS Desktop, so, it comes with the driver (client files) for Postgre with it according to another post here in stackexchange, didn't work, I've also tried to replace those 5 .dll's files from PostgreSQL folder in the ArcGIS\bin folder, nothing changed.

  • When I had a 64bit installation I have also tried to copy the "C:\Program Files (x86)\ArcGIS\Desktop10.5\DatabaseSupport\PostgreSQL\9.5\Windows64" files to my PostgreSQL folder, when 32bit I did not, first because the last version there is 9.5 and my PG is 10.4 and then because it seemed like those files were for 64bit (the Windows64 at the folder's name)

  • I have changed the pg_hba.conf to accept any connection as above

    IPv4 local connections:
    host all all 0.0.0.0/0 md5

  • The postgresql.conf file is set to listen to any connection (at least I think so), configuration is set as follows

    listen_addresses = '*'

  • Just to note, anytime I did any alteration on the conf files I had the postgresql service restarted

  • I can connect with pgAdmin and QGIS locally and remotely JUST FINE (frustating how fine it is)

Here I'll have you some prints of my sad situation

That's how my pgAdmin looks when showing the properties of my connection

That's how my pgAdmin looks when showing the properties of my connection

Now, on ArcCatalog, when I try Database Connection and try to open the droplist of databases

enter image description here

And if I'm stubborn enough and try to manually set the database name

enter image description here

And then, it won't connect either.

Best Answer

ArcGIS database support has been 64-bit only since 10.1; you should not ever use a 32-bit database server for ArcGIS use (any Forum that recommended that would be at least a decade old, and not be relevant for PostgreSQL use).

PostgreSQL requires release equivalence for extensions, so if DLLs for your release are not present in the support folder, it is not possible to create an enterprise geodatabase in that database release with that version of ArcGIS. The highest geodatabase support for a 10.5 client is PG 9.5 (10.6 supports 9.6). PG 10.x support was introduced at ArcGIS 10.6.1, for PG 10.3 (and higher) with PostGIS 2.4.

Esri maintains support for four PG releases for each ArcGIS client release. You should always consult the documentation choosing your current ArcGIS release in the selector to determine the PostgreSQL/PostGIS combinations supported by that release. Note that Esri supports the specified release and higher, so you only need to match the major version (e.g., 10) and the minor version can be higher than the specified supported version (e.g., 10.4, 10.5,...). PostGIS support has similar flexibility, but be careful of upgrading beyond the supported minor release.

Note that Esri ST_Geometry DLL must be installed to enable an Enterprise geodatabase in local installations of PostgreSQL. The only exception is for Amazon AWS RDS (relational database as a service) installations, which use only the native PostGIS geometry/geography data types.

You should not ever connect as the postgres user from an ArcGIS Database Connection interface. The only appropriate use of the admin login is in the Create Enterpise Geodatabase utility (required because it (optionally) creates the database and sde login) or in the Create Database User utility (because CREATE ROLE is required.

Finally, PostgreSQL is alone among the ArcGIS-supported RDBMS in that ArcObjects tools (meaning UI and ArcPy, as well) will accept a database connection as an output workspace, even if that database is not enterprise geodatabase enabled. This has been true since at least ArcGIS 10.4. So, as long as the PG and PostGIS are supported, you can create tables and feature classes in a PG database without an ArcGIS Server license (these will be simple tables without versioning or enterprise geodatabase functionality, but it's far more than the basic query-only Query Layers/Query Table support offered with Oracle, SQL Server, etc.).

Related Question