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

[MarkVolz]

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

[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!

[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]

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!

[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]

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?

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

[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

[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!

[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!

[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