nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

LyonMNGIS

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

Finde mehr Beiträge mit dem Tag

Accepted answers

All comments

DiegoGamboa

To connect PostgreSQL with ArcGIS you need:

Windows 32-bit PostgreSQL 9.0.5 Client Libraries:
libeay32.dll
libiconv-2.dll
libintl-8.dll
libpq.dll
ssleay32.dll

Install postgresql 9.0.3-1 version (32 bits if you Arcgis is )

Copy dlls from bin folder (C:\Program Files (x86)\PostgreSQL\9.0\bin)

Paste dll in C:\Program Files (x86)\ArcGIS\Desktop10.2\bin

I tested it and i'm working with postgreSQL and postgis Connection.

source: http://resources.arcgis.com/es/help/main/10.2/index.html#//002p0000003q000000

Sorry for my english, is not my native language

MarcoBoeringa

Ok, you might have convinced me. I will look at reinstalling tomorrow.

Forkandwait, although Vince's recommendation of reinstalling ArcGIS for Desktop entirely makes sense if you have been uncareful or highly confused while doing all this, I think you don't necessarily need to re-install if instead you have been careful in tracking and documenting which files you copied to ArcGIS's Bin folder. In that last case, simply removing any files you added to the Bin, and copying the right 32 bit files as a replacement, should solve the issues without a complete re-install.

In essence, the files you need to remove and replace by their 32 bit counterparts, are the five files I referenced before, based on ESRI's Help documentation:

libeay32.dll, libiconv-2.dll, libintl-8.dll, libpq.dll, and ssleay32.dll

So ... where do I find the files I need again? In the postgres installation, copy to bin64? Or the .exe mentioned a couple of threads back? If there is a link to the download on the customer care site that would be great...

NO, you SHOULDN'T be copying anything to a bin64 folder.

What you need to do is to get the 32 bit versions of the five above mentioned files from the ESRI Customer Care portal (in the "pg_client_windows86" client library download as documented on this Help page), and copy these as a replacement in the ArcGIS for Desktop "Bin" folder, as described here. Also make sure to follow all other possibly necessary steps described there!

The Customer Care portal is here:

https://customers.esri.com

If you are logged in (If not: login!), you should have access to downloads. You can find the necessary files under

- "Software Downloads"
- "DMBS Support Files"
- Scroll down until you see "PostgreSQL Client Libraries (Windows)", should be 2.21 MB big file download, so small.
- Click "Download"

In the downloaded file should be the "pg_client_windows86" client set of libraries containing the necessary 32 bit version of the above mentioned five files. Copy these to the ArcGIS for Desktop "Bin" folder.

Good luck!

webbs

You *can't* place 64-bit libraries in a 32-bit application's "bin" folder and have them
work (the operating system will find the matching file, and notice it's the wrong bit size,
then return "File not Found" [not "wrong size file found"]).

The reason I recommend re-installing ArcGIS Desktop is that it's difficult to know what
32-bit files have been clobbered after multiple passes. Re-install eliminates all possible
conflicts.

- V

Ok, you might have convinced me. I will look at reinstalling tomorrow.

So ... where do I find the files I need again? In the postgres installation, copy to bin64? Or the .exe mentioned a couple of threads back? If there is a link to the download on the customer care site that would be great...

(I am sorry to try everyone's patience, but I really don't think this is well documented, including multiple stories about where to find files, etc, etc.... I do appreciate everyone who has answered's help, though!)

Thanks!

VinceAngelo

You *can't* place 64-bit libraries in a 32-bit application's "bin" folder and have them
work (the operating system will find the matching file, and notice it's the wrong bit size,
then return "File not Found" [not "wrong size file found"]).

The reason I recommend re-installing ArcGIS Desktop is that it's difficult to know what
32-bit files have been clobbered after multiple passes. Re-install eliminates all possible
conflicts.

- V

MarcoBoeringa

Thanks for your help, Vince. Replies below.

Umm, I installed PG with 64 bit, which is specifiied as OK here:

http://resources.arcgis.com/en/help/...00000075000000

What? If the install is from PG 64 bit, then why would I need to run the file command, etc?

I don't really want to reinstall (yuck!) I am going to find out our customer number and download the file you speak of. Is it a self-installer (from the "exe")? It should just clobber the old files, right?

Forkandwait, I don't think you need to re-install yet, nor re-do your 64bit PostgreSQL, as you correctly remark a 64 bit PostgreSQL install is listed as "supported"...

If I understand Vince well, he is actually trying to say to you that the thing you CAN'T do is, is to copy the 64 bit DLL's from your essentially 64 bit PostgreSQL install, to the ArcGIS for Desktop Bin folder. You need to copy 32 bit DLLs there, either from a 32 bit PostgreSQL install, or by downloading them from the ESRI Customer Care portal. I guess downloading them from the ESRI site, and replacing all the files you put in your ArcGIS Bin, is the easiest and safest thing. If it than still fails, maybe more drastic measures are in order...

webbs

Thanks for your help, Vince. Replies below.

You could have used a 32-bit G install, but using a 64-bit install won't work.

Umm, I installed PG with 64 bit, which is specifiied as OK here:

http://resources.arcgis.com/en/help/...00000075000000

Linux will tell you which files are of what application width ('file'), but Windows won't.

What? If the install is from PG 64 bit, then why would I need to run the file command, etc?

The safest thing to do at this point is to uninstall ArcGIS Desktop and re-install it (including
the SP1 patch). Then you can install the 32-bit PG libraries from the Esri download site (my
file is named "PostgreSQL_Client_Libraries_for_Windows_101_130031.exe") into the ArcGIS
Desktop bin directory.

I don't really want to reinstall (yuck!) I am going to find out our customer number and download the file you speak of. Is it a self-installer (from the "exe")? It should just clobber the old files, right?

VinceAngelo

You could have used a 32-bit PG install, but using a 64-bit install won't work.

Linux will tell you which files are of what application width ('file'), but Windows won't.

The safest thing to do at this point is to uninstall ArcGIS Desktop and re-install it (including
the SP1 patch). Then you can install the 32-bit PG libraries from the Esri download site (my
file is named "PostgreSQL_Client_Libraries_for_Windows_101_130031.exe") into the ArcGIS
Desktop bin directory.

- V

webbs

Don't know if this is the issue, but this Help page:

Setting up a connection to PostgreSQL

states you actually need 5 files for a 32-bit client like ArcGIS for Desktop. You seem to be missing the underlined file:

"pg_client_windows86: Includes the client files required to make a connection from a 32-bit client on a Windows operating system to a PostgreSQL database. Files are libeay32.dll, libiconv-2.dll, libintl-8.dll, libpq.dll, and ssleay32.dll"[/URL]

So I have all these now in my ../bin directory, but maybe only they need to be downloaded from the ESRI site rather than copied over from the Postgres install?

The error says my "client libraries failed to load", or some such. I am now logging connections to the Postgres server and I don't see any at all (except my psql test connections).

Thanks!

MarcoBoeringa

I placed the following in both my blahblah/desktop10.1/bin64 and my stuff/bin directory, copying from my recent 9.1 Postgres installation:

libeay32.dll libintl-8.dll libpq.dll ssleay32.dll

Still running the same Postgres installation as described from the version queries above.

Don't know if this is the issue, but this Help page:

Setting up a connection to PostgreSQL

states you actually need 5 files for a 32-bit client like ArcGIS for Desktop. You seem to be missing the underlined file:

"pg_client_windows86: Includes the client files required to make a connection from a 32-bit client on a Windows operating system to a PostgreSQL database. Files are libeay32.dll, libiconv-2.dll, libintl-8.dll, libpq.dll, and ssleay32.dll"

The four file client setup is for 64-bit clients, I guess the Help means ArcGIS for Server / ArcSDE.

By the way, it is slightly confusing on this Help page, but if I understand it well. the pg_client_windows86 does NOT refer to the Windows itself being 32- or 64-bit, but to the specific software you are running being either 32-bit (like ArcGIS for Desktop), or 64-bit (like ArcGIS for Server / ArcSDE). As the Help page also states:

"Be sure to download the correct libraries for your ArcGIS client. For ArcGIS for Desktop and ArcGIS Engine, you need the 32-bit libraries. For ArcGIS for Server, you need the 64-bit client libraries."

You probably already read this one, but this Help page may be of use to others too, as it lists some of the prerequisites for setting up Query Layers:

Preparing to use query layers

webbs

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

I placed the following in both my blahblah/desktop10.1/bin64 and my stuff/bin directory, copying from my recent 9.1 Postgres installation:

libeay32.dll libintl-8.dll libpq.dll ssleay32.dll

Still running the same Postgres installation as described from the version queries above.

The parameters on my database connection dialogue--

database platform: PostgreSQL
Instance: 127.0.0.1
authentication type: database authentication
username and password are correct

I have changed the hba file to "trust" for ip4.

Still doesn't work...

I wonder if there are any postgresql.conf things that need changing?

@vangelo -- if you have an installation working on your end, could you run the version queries and post or email the output? I might still be off on versions (release candidates or some such).

I guess I will have to demo query layer functionality to my boss using QGIS while we figure out the ESRI connection problems.

Thanks to everyone for their help and patience!

VinceAngelo

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

webbs

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.

webbs

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.

MarcoBoeringa

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

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

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.

FionaHatfield

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

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

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

FionaHatfield

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

VinceAngelo

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

stevepeedell

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

VinceAngelo

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

LyonMNGIS

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

VinceAngelo

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

LyonMNGIS

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?

VinceAngelo

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

- V

LyonMNGIS

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.

VinceAngelo

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

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.