topic Re: Problem connecting to postgis / postgre query layer - database cleint software is in Data Management Questions

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

MarkVolz — Tue, 10 Jul 2012 15:06:12 GMT

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

Re: Problem connecting to postgis / postgre query layer - database cleint software is

VinceAngelo — Tue, 10 Jul 2012 15:28:19 GMT

What version of ArcSDE and ArcGIS are you using? PostgreSQL 9.0.5 isn't supported
until ArcGIS 10.1 (with PostGIS 1.5.1).

- V

Re: Problem connecting to postgis / postgre query layer - database cleint software is

MarkVolz — Tue, 10 Jul 2012 15:51:19 GMT

vangelo,

I am using ArcGIS 10.1. I am not using ArcSDE. I am trying to connect directly to a postgresql / postgis database, which I have done in a workshop using File>Add Data> Add Query Layers.

Re: Problem connecting to postgis / postgre query layer - database cleint software is

VinceAngelo — Tue, 10 Jul 2012 15:56:03 GMT

Do you have the correct client DLL installed? 32-bit clients require 32-bit libraries.

- V

Re: Problem connecting to postgis / postgre query layer - database cleint software is

MarkVolz — Tue, 10 Jul 2012 16:06:17 GMT

vangelo,

how can I download / or verify that I am using the correct dll? Right now I have a 32 bit server and 32 bit client. Eventually we will move the server to 64 bit, with a mixture of 32 and 64 bit clients.

I should also ask what is the current supported version of postgres / postgis. Is it postgres 9.0.5 and postgis 1.5.1, or is it 9.0.x and postgis 1.5.x. In otherwords can I install postgresql 9.0.8 and postgis 1.5.4?

Re: Problem connecting to postgis / postgre query layer - database cleint software is

VinceAngelo — Tue, 10 Jul 2012 16:44:59 GMT

The database server application wordsize doesn't matter -- only the client does. Esri ships
client ibraries in the install media -- use them.

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

Re: Problem connecting to postgis / postgre query layer - database cleint software is

MarkVolz — Tue, 10 Jul 2012 17:08:09 GMT

where are the dlls stored on the ArcGIS 10.1 disc for the POSTGRESQL / PostGIS query layers, and where should I place them?

Re: Problem connecting to postgis / postgre query layer - database cleint software is

VinceAngelo — Tue, 10 Jul 2012 17:47:01 GMT

It's been nearly a decade since I last saw a media kit, so I have no idea what your
procedure would be. I know the files are in the download directory somewhere, but
the best way to determine where is to communicate with Customer Service.

- V

Re: Problem connecting to postgis / postgre query layer - database cleint software is

stevepeedell — Tue, 24 Jul 2012 11:32:30 GMT

Not sure if the necessary postgres client dll's are on the install media - I don't get to see DVD's any more these days, but what is strange is that these are not automatically deployed as part of the install process. At 10.1 you DO have to manually copy the dll's to your bin directory - surely this could be an install option (ask user which db they are using, and deploy the relevant dll's)?

Anyway, the workaround is to find the dll's listed below. If you are registered properly with the ESRI customer care portal you can apparently get them there, we got them from our international distributor....

Windows 32-bit PostgreSQL 9.0.5 Client Libraries:
Libpq.dll
Libeay32.dll
Libintl-8.dll
Ssleay32.dll

Place these files in the bin folders of your Windows 32-bit Esri client application.

I will no doubt post some further examples of our adventures in PostGIS/ArcGIS integration, but for now am happy to report that with the above fix we are able to use our PostGIS tables.........

Re: Problem connecting to postgis / postgre query layer - database cleint software is

VinceAngelo — Tue, 24 Jul 2012 13:43:33 GMT

Normally, the client libraries are part of the server install, so there's no need for Esri to
distribute or install client libraries (which do increase the install footprint, after all).

The problem comes when you start mix-n-match with the application word size
(client and server) without ArcSDE to do the client middle-man. There's a number
of ways to accomplish the net result, but copying the DLLs will serve.

- V

Re: Problem connecting to postgis / postgre query layer - database cleint software is

FionaHatfield — Thu, 10 Jan 2013 13:12:43 GMT

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

Re: Problem connecting to postgis / postgre query layer - database cleint software is

FionaHatfield — Thu, 10 Jan 2013 15:10:43 GMT

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

Re: Problem connecting to postgis / postgre query layer - database cleint software is

MarcoBoeringa — Thu, 10 Jan 2013 15:50:05 GMT

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.

Re: Problem connecting to postgis / postgre query layer - database cleint software is

FionaHatfield — Thu, 10 Jan 2013 16:13:51 GMT

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

Re: Problem connecting to postgis / postgre query layer - database cleint software is

MarcoBoeringa — Thu, 10 Jan 2013 20:29:16 GMT

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.

Re: Problem connecting to postgis / postgre query layer - database cleint software is

webbs — Wed, 13 Mar 2013 21:03:00 GMT

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...

Re: Problem connecting to postgis / postgre query layer - database cleint software is

MarcoBoeringa — Wed, 13 Mar 2013 21:23:25 GMT

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

Re: Problem connecting to postgis / postgre query layer - database cleint software is

webbs — Wed, 13 Mar 2013 21:58:41 GMT

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.

Re: Problem connecting to postgis / postgre query layer - database cleint software is

webbs — Wed, 13 Mar 2013 22:42:04 GMT

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.

Re: Problem connecting to postgis / postgre query layer - database cleint software is

VinceAngelo — Thu, 14 Mar 2013 09:58:35 GMT

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