I am using ArcMap 10.3.1 and have downloaded the PostgreSQL 9.3.5 files from MyESRI.com. PostgreSQL is installed locally for testing purposes. I have everything working smoothly and am attempting to publish the data for use on a tablet in the Esri Collector app. In order to do this I have to register the database with ArcGIS Server.
I edited the pg_hba.conf to allow remote connections, since ArcServer is installed on another machine. When I attempt to register the database I get he error in the following screenshot. I have attempted an exhaustive Google sreach for a resolution with no success.
Glenn,
You're right. This is difficult and not much information out there. From what I could find, it looks like you need to add the 'allowed' machines to your pg_hba.conf since PostgreSQL does not allow external machines to connect by default.
There is some information about this set up in this thread:
Cannot Connect (pg_hba.conf Error)
This help document shows how to modify the pg_hba.conf file to add things
Configure a PostgreSQL database cluster to accept connections—Help | ArcGIS for Desktop
Check out the respond by Peter Birksmith in this thread here:
https://community.esri.com/message/590176?commentID=590176#comment-590176
and here:
https://community.esri.com/message/590176?commentID=590176#comment-590182
this may give you what you need.
Otherwise, the only other pertinent info that I could find would be in this thread:
Adrian,
Thank you for the extensive information. I am still at a loss though. I am pretty sure that everything is generally setup correctly as per the links you provided. Some of the things that I have read required you to set up a username and password for the ArcGIS Server in PostgreSQL, should this be the same username and password to log onto the machine? Others say that this is not required with PostgreSQL. Confusing!
Hi Glenn,
You mentioned that ArcGIS Server is on another machine than postgres. Could it be that you are trying to connect to postgres using localhost instead of the IP of the postgres machine? What are your database connection properties? Are you able to connect to the database in ArcCatalog on the ArcGIS Server machine (not through the ArcGIS Server properties)?
Jennifer, you may be right, but I am not sure what I am doing wrong to specify the server based upon IP address. Are you aware of any guidance documentation that you could link for me?
Hi Glenn.
If you are able, I would suggest that you download and install pgadminIII, version 1.22.1 - pgAdmin: Download - Windows™
The reason why I ask that you do this is because it will make your life a lot easier testing access to Postgres using pgAdminIII than by seeing odd errors in ArcMap.
Once you have pgAdminIII installed, then you need to add add a server
The Name will be anything you want that will give you an idea about the connection.
The Host can be either the IP address, Server name or FQDN (Fully Qualified Domain Name)... Tip Check what has been set in the pg_hba.conf file.
The Port number by default is 5432. Again this information can be found in the postgresql.conf file.
The Service can be left blank
The Maintenance DB leave as is. Postgres is the default.
Enter a Username and password.
*Note. The biggest issue you will face is how the pg_hba.conf has been configured.
MD5 means that you will require the Postgres admin to add a user which must supply a password. If you are the Postgres Administrator, logon as Postgres and add a user
Once this is done, then you need to modify the pg_hba.conf to add this new user to a specific database
E.g.
TYPE DATABASE USER ADDRESS METHOD
host postgres user IPAddress of Desktop md5
Please note here that MD5 means that a postgres logon requires a username and password
After this you then need to copy 5 files from your ESRI installation for Postgres
Once you've installed it, you want to select, copy, and paste the following files from `C:\Program
Files\PostgreSQL\9.3\bin` to `C:\Program Files\ArcGIS\Desktop10.3\bin`: libeay32.dll, libintl.dll, libpq.dll,
ssleay32.dll, and libiconv-2.dll
Ref: https://community.esri.com/thread/114415
Assuming that all of the above has been done, you should then test by opening pgAdminIII, adding a server, entering the relevant details and it should allow you access to Postgres.
If this doesn't work, ESRI won't work either.
See how you get on.
I really like the idea of trying to connect with PGADMINIII. The error message indicates something's up with PG_HBA.conf that's rendered it unusable. If you enable 'show extensions for known file types' in windows explorer, was there a .txt extension slapped on the end of PG_HBA?
Personally, I'd replace PG_HBA.conf with a backup and attempt to connect to the database with a local copy of PGADMINIII (or ArcGIS Desktop), since localhost will be allowed by default.