During a presentation on ArcGIS Online at SSP’s inaugural iLLUMINATE, I suggested leveraging SQL Server Express as cost effective Enterprise Database solution. Following the presentation, I was approached by a representative of Esri, and was asked if we had considered implementing PostgreSQL as another free solution. At the same time, I received an email of similar nature from my coworker, Bill Bott.
Am I missing the next gravy train? Truth of the matter is, at SSP we deploy on the database format requested by our clients. And as stated in my presentation, this is usually Oracle, SQL Server, or SQL Server Express.
Time to broaden my horizons. What is PostgreSQL all about? Similar to my approach to my most recent blog post about implementing cloud infrastructure, in this article I am going to document the process for implementing PostgreSQL as I do so. I may even provide an opinion or two ...
I am not going to turn this into a nerd post, however. I won't compare all the crap (i.e., Unicode) that most people don't understand or care about. What I will say is that unlike SQL Server or Oracle, PostgreSQL is FREE. That very fact I do care about, and so do others. Being free makes the product immediately worthy of database consideration. One may say that SQL Server Express is free, of course. That's true, but SQL Server Express has limitations that PostgreSQL just does not have (for instance, a size limit of 10 GB (2008 R2 and more recent)).
I went to the PostgreSQL download site for windows to get the latest Enterprise DB software. BUT, I was met with my first question: What is the latest version of PostgreSQL that is compatible with the ArcGIS version I have installed? My current installed version of ArcGIS is 10.2.1. So the easiest way to find out the latest ArcGIS installation compatibility is via a file manager. Simply navigate to the PostgreSQL folder — c:\Program Files (x86)\ArcGIS\Desktop10.2\DatabaseSupport\PostgreSQL — as shown in Figure 2. Here we observe that the latest compatible version is 9.2. Great!
During the installation of version 9.2.21 for Windows x86-64, the user can accept most of the default values. The user is also prompted for an administrative password (Figure 3). For purpose of this example, will pick one at random (SailRethorn). Obviously, don't forget the importance of this password because if will be used often.
Following completion of the setup, it is necessary to copy a *.dll file to be compatible with ArcGIS. Inside the same folder location above (c:\Program Files (x86)\ArcGIS\Desktop10.2\DatabaseSupport\PostgreSQL\9.2\Windows64) the file "st_geometry.dll" needs to be copied into the c:\Program Files\PostgreSQL\9.2\lib folder (Figure 4).
To complete PostgreSQL compatibility, I needed to lastly acquire the applicable ArcGIS client files. These can be downloaded from my.esri.com (Figure 5).
Once the 32-bit *.dll files are downloaded, they need to be copied and pasted into the c:\Program Files (x86)\ArcGIS\Desktop10.2\bin folder.
We lastly need to make one minor configuration change to allow for connections to the database. The configuration file pg_hba.conf (c:\Program Files\PostgreSQL\9.2\data) needs to be modified via a text editor. In this case, we are going to allow everyone to connect. (In my next article next month, I'll cover how you can restrict user access.) After opening the file, go to the bottom of the text, and type the following "host (tab) all (tab) all (tab) all (tab) md5" as shown in Figure 6
Once the above steps are conducted, we are now ready to kick some PostgreSQL butt and chew bubble gum (and we are all out of bubble gum). As shown if Figure 7, we use the Create Enterprise Geodatabase tool within ArcCatalog to create a geodatabase titled "ssp."
Upon tool completion, a confirmation screen appears (Figure 8).
An empty geodatabase is now created on the hard drive of my personal Skye-provided computer. Without conducting addition steps, a connection to the geodatabase may be made as the sde user (Figure 9).
As a final test, we should be able to create a feature class within the geodatabase. As shown in Figure 10. In this case, my test worked. LIFE IS GOOD. Look for Part II in a few weeks.
It is my sincere hope that these procedures provide assistance to the reader. Know that SSP is here to help.
Contact us if you're interested in learning about trying PostgreSQL. We can help answer your questions. You can also write a question in the comments, and we will respond.