kawaisunn

Connecting ArcGIS Desktop 10.3 to PostgreSQL 9.3-PostGIS 2.2 local server

Blog Post created by kawaisunn on Mar 22, 2018

I use ArcGIS Desktop frequently, and through a recent geospatial data collaboration was exposed to PostgreSQL. Future efforts could be streamlined if I used a Postgres environment, and I was curious about an open database management solution. After a little research I decided to install a compatible version of PostgreSQL on my workstation and explore. I found it wasn't quite as straightforward for a n00b as my initial research indicated, and I ran into issues both documented and not well documented. Here is what I wish I could have found earlier to expand on the ESRI and PostgreSQL-related documentation. This is not meant to be a walkthrough, but rather tips that might help someone stuck and frustrated who is relying heavily on documented procedures from the software developers. I'm assuming this is being read by someone new to PostgreSQL (like me) and a passing familiarity with ArcGIS Desktop 10.x and relational databases, and has not been successful connecting ArcGIS to a new local host PostgreSQL install and creating an Enterprise geodatabase instance on that server using ArcGIS.

 

Have hope! Its not difficult after its done, and hopefully my experience will save someone the time I spent pouring through the many pages of documentation and forums and threads looking up errors and overcoming obstacles. Creating the Enterprise database and connecting to it should take less than 5 minutes or so, the entire process including finding, downloading, installing, and setting up PostgreSQL and the related software might take about an hour.

 

This was done on a Windows 7 Enterprise 64-bit install running ArcGIS Desktop10.3.3, and a valid ArcGIS server license file (no ArcGIS server installed on local host):

  • In retrospect, I spent too much time agonizing over details probably not crucial to setting up a functioning test environment.
  • According to ESRI documentation, PostgreSQL 9.3 seemed a sure fit to my ArcGIS installation. After a couple of PostgreSQL installations and purges, I found a bundled PostgreSQL 9.3 with pgAdmin 3 and Application Stack Builder installation. These are necessary, and my time would have been better spent simply using a packaged bundle rather than putting the installations together piecemeal.  pgAdmin 3 is a GUI front-end for Postgres, and Application Stack Builder is used during install to include PostGIS 2.2 (also indicated as compatible) needed for geospatial data. Without promoting any service, the bundle was easy to find but dismissed initially because the site required a valid email to access the download. It installed properly to C:\Program Files\PostgreSQL\9.3 (not C:\Postgre*\ . . .) using default settings.
  • I wanted to use an administrative command console as much as possible, but ran into several problems, and eventually ended up doing most of the setup using pgAdmin. In hindsight, because my goal was to set up the environment, I would have accomplished it faster without trying to tweak the shell to behave as expected.
  • Once the initial setup was done (i.e. setting logs to write to a file, postgres admin password set, pg_hba.config permissions for connections, PATHs, all according to documentation), I used pgAdmin to create a new ordinary database. This was done by left-clicking on 'Databases' in the object browser pane in pgAdmin and selecting Create new database. I named it, made the owner postgres, under the Definition tab left the defaults Encoding = UTF8 and Tablespace = <default tablespace>, and under Privleges set Role = public (use Add/Change button) and gave it ALL permissions=True. The new database appeared in the server tree.
  • I used pgAdmin to create a new superuser named 'sde' that had access to the new database. I gave sde all permissions, figuring security settings could be adjusted as needed afterwards. This was done by left-clicking on Login Roles in the pgAdmin object browser pane and selecting New login role. Under Properties tab Role name=sde; under Definition tab set password; under Role privileges set everthing=True; all other tabs and settings default. 
  • In ArcCatalog used Add Database Connection interface with these settings:  Platform=PostgreSQL; Instance=127.0.0.1; Username=’sde’ and password; Save username password=True; Database=’name of generic new postgres db’ NOTE: trying to use the dropdown list for Database threw errors-manually typed name worked.
  • In ArcCatalog used Enable Enterprise Geodatabase tool to point at the new db connection and the server license file. The sysadmin for ESRI products where I work furnished the license file. It took Catalog just over one minute to reconfigure the database.

I used Catalog to move an existing GIS dataset into the new database. I was able to query tables using pgAdmin, and in ArcMap I was able to connect to the server without a problem and open featureclasses from inside the new geodatabase. 

 

Obviously more testing is needed to truly declare the environment fully functional, I still have an issue with psql CREATE* functions not impacting the PostgreSQL cluster as expected, and I winked at best practices, but now I have at least a partially working test environment to begin exploring possibilities. 

Outcomes