Problem connecting to postgis / postgre query layer - database cleint software issue?

MarkVolz · ‎07-10-2012

Hello,

I am trying to connect to a query layer and I am receiving the following error "Unable to connect to database server to retrice database list; please verify your server name, user name, and password info and try again. Cannot connect to database because the database client software failed to load. Be sure the database client software is installed and configured correctly"

I set up the database connection using the following:
platform - PostgreSQL
Instance - localhost (my computer name does not work either)
Authentication Type - Database
User name and password - set correctly
Database - cannot select (this is where the error appears)

I am using postgresql 9.0.5 and postgis 1.5.3. I can connect to the database using other software. Does anyone have any troubleshooting tips?

Thanks

FionaHatfield · ‎01-10-2013

Hi,
This is exactly the problem I am having.
my environment:
Windows Server 2008 R2 (64bit)
PostgreSQL 9.2 + PostGIS 2.0 (64bit)
ArcGIS Desktop 10.1 sp1 (32bit)

I am trying to connect via ArcCatalog to take a look at my loaded data.
I get the same error from ArcCatalog when I try to set up a connection.

I am a bit confused by the work around.
I have located the 4 libraries in C:\Program Files\PostgreSQL\9.2\bin
I have copied these to my ArcGIS Desktop (32bit) bin directory E:\Program Files (x86)\ArcGIS\Desktop10.1\bin

I still get the error, am I missing a critical step?
Thanks,
Fiona

FionaHatfield · ‎01-10-2013

OOps, I noticed that 9.2 was not supported (yet), so I have reconfigured my environment as follows:

PostgreSQL 9.1.3 (64bit) + Post GIS 2.0 (64bit)
Connecting using ArcGIS desktop 10.1 sp1 (32bit)

I have recopied the .dlls from the new postgresql install directory.
I am still getting the same error.

any insight is greatly appreciated,
Fiona

MarcoBoeringa · ‎01-10-2013

OOps, I noticed that 9.2 was not supported (yet), so I have reconfigured my environment as follows:

PostgreSQL 9.1.3 (64bit) + Post GIS 2.0 (64bit)
Connecting using ArcGIS desktop 10.1 sp1 (32bit)

I have recopied the .dlls from the new postgresql install directory.
I am still getting the same error.

any insight is greatly appreciated,
Fiona

Fiona, I think Vince already answered your question:

The supported versions are 9.0.5 / 1.5.1. PostgreSQL has a history of making non-trivial
changes in minor patches, so it's never safe to assume .x support even if .+1 or .+2 works.

- V

Seems ESRI currently only really "supports" 9.0.5 / 1.5.1 as highest versions. Using any other version, especially taking into account Vince's further remarks about "non-trivial" changes in new minor releases, is a potential gamble.

FionaHatfield · ‎01-10-2013

I feel so foolish, I did have the wrong versions of PostgreSQL installed...
Everything is working now, (just as the post suggested)!

the great news is I found some super resources as I tried to troubleshoot.
If anyone is seeing similar difficulties you should check out:
http://proceedings.esri.com/library/userconf/proc12/tech-workshops/tw_36.pdf

Thanks,
Fiona

MarcoBoeringa · ‎01-10-2013

the great news is I found some super resources as I tried to troubleshoot.
If anyone is seeing similar difficulties you should check out:
http://proceedings.esri.com/library/userconf/proc12/tech-workshops/tw_36.pdf

For some reason, Adobe Reader refuses to open this link in Firefox and fails with an error. It can be opened in Internet Explorer.

webbs · ‎03-13-2013

Hi all,

I am trying to set up query layers to work with ArcMap 10.1 SP 1 on a 64 bit, Windows 7 machine and PostgreSQL (any version would be fine).

Could someone who has succeeded in making a similar stack work post their steps, and (please, please, please) *current links* to the various components?

Here is a bunch of detailed background:

I have tried a 32 bit installation from Enterprise DB here: http://www.enterprisedb.com/products-services-training/pgdownload

It worked on the database side, but I kept getting a Could Not Connect error on the ArcMap side.

The version number for PostgreSQL is 9.0.12, and for Postgis is 1.5.5. I know these aren't the specified minor numbers, but I can't find binaries for the officially supported minor numbers anywhere.

Also, the only binary installations of PostGIS 1.5.5 I could find were 32 bit, so that is why I tried the 32 bit PostgreSQL installation. Stackbuilder 64 bit doesn't give an option for PostGIS 1.* anything.

Here is where I looked for PostGIS binaries besides using stack builder, with no 64 bit 1.5.* version:

http://download.osgeo.org/postgis/windows/pg90/

And finally ... QGIS works for this application right out of the box... Just saying...

MarcoBoeringa · ‎03-13-2013

This page of Refractions Research lists PostGIS 1.5.1:

http://postgis.refractions.net/download/windows/pg84/

And this page PostGreSQL 9.0.5:
http://www.postgresql.org/ftp/source/v9.0.5/

These pages you probably already know may be of use:

PostgreSQL database requirements for ArcGIS 10.1
http://resources.arcgis.com/en/help/system-requirements/10.1/index.html#//015100000075000000

Which versions of PostgreSQL and spatial types are supported with ArcGIS 10.1 and ArcGIS 10.1 SP1?
http://support.esri.com/en/knowledgebase/techarticles/detail/40553

webbs · ‎03-13-2013

This page of Refractions Research lists PostGIS 1.5.1:

http://postgis.refractions.net/download/windows/pg84/

These binaries are for PostgreSQL 8.4 compatible PostGIS installations, I think, so they won't work.

And this page PostGreSQL 9.0.5:
http://www.postgresql.org/ftp/source/v9.0.5/

The files there are for source downloads; building from source on a windows machine is a nightmare, hence the need for binaries.

I appreciate the quick answers, though unfortunately they aren't won't solve the issues.

Really, ESRI should be tracking the first two levels of PostgreSQL numbering (like "9.0"), but not the third level ("9.0.2"). Third level releases are supposed to be binary compatible -- and if they aren't, it is a bug. (As I understand it). I am sure the Postgres and Postgis devs would pay attention if ESRI opened a ticket due to binary incompatibility with a minor-minor release and fix it before releasing.

I copied the dll's into ArcMap's bin directory, and it still doesn't work.

I will try the 64 bit. The more recent documentation link from Marco says Postgis 2.0 will work.

webbs · ‎03-13-2013

Here is the latest:

My postgres installation information:

postgis20=# select postgis_full_version();?
postgis_full_version
--------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER
(1 row)

postgis20=# select version();
version
-------------------------------------------------------------
PostgreSQL 9.1.3, compiled by Visual C++ build 1500, 64-bit
(1 row)

As far as I can tell, this meets the criteria given in this link:

http://resources.arcgis.com/en/help/system-requirements/10.1/index.html#//015100000075000000

(Perhaps Windows 7 is not supported, as it is not mentioned? That seems inconceivable...)

However, I can't get query layers to work for the life of me, after about 3 hours of trying. I had to use unsupported versions of both Postgres and PostGIS in order to meet the version criteria (including custom-engineering the Enterprise DB URL).

The postgres libraries are already present in ArcMap/.../bin64, and I didn't want to screw up my system by copying the new ones.

I don't know if a tiny version problem is causing the error -- I could imagine there is code that checks for a version string in libpq and errors if it doesn't match exactly. All I know as a user is that it says "don't work, sorry" so I don't have any basis for diagnosing the problem.

If someone could steer me in the right direction, that would be great. If someone has a running setup, a cut-and-paste of the two version functions above would be useful.

VinceAngelo · ‎03-14-2013

PostgreSQL 9.1.3 support was introduced with 10.1 SP1, so this is now a supported configuration.

ArcGIS Desktop is still a 32-bit application. Clobbering the bin64 contents with new 64-bit
libraries won't change 32-bit connection issues. You need to place the 32-bit PG client libraries
in a place where they will be found (e.g. the Desktop bin directory).

- V