topic Re: Error registering a database in PostgreSQL in ArcGIS Enterprise Questions

Error registering a database in PostgreSQL

GlennKurcon — Fri, 19 Aug 2016 19:32:17 GMT

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.

Re: Error registering a database in PostgreSQL

AdrianWelsh — Fri, 19 Aug 2016 21:24:57 GMT

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:

https://community.esri.com/message/556460

Re: Error registering a database in PostgreSQL

GlennKurcon — Mon, 22 Aug 2016 12:36:39 GMT

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!

Re: Error registering a database in PostgreSQL

JenniferMcCall4 — Mon, 22 Aug 2016 12:43:37 GMT

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)?

Re: Error registering a database in PostgreSQL

GlennKurcon — Mon, 22 Aug 2016 19:54:21 GMT

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?

Re: Error registering a database in PostgreSQL

PeterBirksmith — Tue, 23 Aug 2016 02:12:30 GMT

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.

Re: Error registering a database in PostgreSQL

RandallWilliams — Tue, 23 Aug 2016 16:48:27 GMT

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.