topic ArcSDE for Postgre SQL 9.1 in Data Management Questions

ArcSDE for Postgre SQL 9.1

RaviSrivastava — Fri, 30 Aug 2013 07:49:05 GMT

Hi,
I am struggling in configuring ArcSDE 10.1 on Postgre 9.1 but while creating enterprise geodatabase, I am getting following error:

Connected RDBMS instance is not setup for Esri spatial type configuration.
Failed to execute

I have copied the required geometry file and other client libraries in the appropriate location. All the prerequisites are also met.

Please provide any any inputs in resolving the same.

Ravi

Re: ArcSDE for Postgre SQL 9.1

VinceAngelo — Fri, 30 Aug 2013 10:58:20 GMT

Please tell us exactly what you've done, rather than that it was correctly done, since
something isn't correct.

Which ArcGIS 10.1 service pack are you using? [At this point, it should be sp1]

Which PostgreSQL release are you using, on what platform? [I'm using 9.1.9 without
difficulty] From where did you obtain the executable?

Did you place the correct required library in the appropriate directory? [Newer libraries
are only available in Desktop and Server installs, in the 9.1 subdirectory, and it's a different
location depending on how the binary was built.]

- V

Re: ArcSDE for Postgre SQL 9.1

AndrewBrown — Thu, 24 Oct 2013 12:36:15 GMT

I've recently configured PGSQL 9.2 with direct connect. What I have found is that in addition to copying the st_geometry assembly to the postgresql db directory, I need to run Create Enterprise Geodatabase , then the Create Spatial Types tool under data management / workspace. Then I am able to create a Feature Class, however it treats it as a Query Layer. If I right click on the connection and "Enable Geodatabase". Everything seems to work as expected. This is all in comparison to what I'm used to , with Direct Connections in SQL Server 2008 where a single run of the Create Enterprise Geodatabase tool gets the GDB completely ready for use.

Re: ArcSDE for Postgre SQL 9.1

VinceAngelo — Thu, 24 Oct 2013 12:50:58 GMT

That's odd, since I was able to get ArcGIS 10.2 registered in a PG 9.2.4 database in
one Create Enterprise Geodatabase pass. Which exact PG 9.2 release are you using?
How many table and function objects are in your sde schema?

- V

Re: ArcSDE for Postgre SQL 9.1

AndrewBrown — Thu, 24 Oct 2013 13:29:27 GMT

Thanks Vince, the DBMS Version String is PostgreSQL 9.2.2 build 1600 64-bit. Your response prompted me to look into the file versions of all of the assemblies where I found a mismatch between the client library files that I have on my AGD 10.2 client and the files that I have on the PG server. For example, libeay32.dll for my client is 1.0.1.2 and the libeay32 assembly on the PG server is 1.0.1.3. I'll copy those over and try it again. The ST_Geometry assembly is 10.2.0.920

Re: ArcSDE for Postgre SQL 9.1

AndrewBrown — Thu, 24 Oct 2013 13:41:47 GMT

I have 347 Functions and 42 Tables in the sde schema on the gdb that I have successfully edited a point feature class on. There are only 186 functions and 3 tables on the gdb that I have not used "Enable Geodatabase" on. Both were created with the Create Enterprise Geodatabase tool.

Re: ArcSDE for Postgre SQL 9.1

VinceAngelo — Thu, 24 Oct 2013 14:04:49 GMT

Have you loaded spatial data as the 'sde' user? There should only be 345 functions and 40 tables
in a reference 'sde' schema.

Client compatibility is important, but there may be something else going on as well. If a fresh
install into a new database doesn't loak the full complement of tables, you should probably contact
Tech Support.

- V

EDIT: 345 functions and 40 tables with 10.2 (not 42); 343 functions with 40 tables at ArcGIS 10.1sp1

Re: ArcSDE for Postgre SQL 9.1

AndrewBrown — Thu, 24 Oct 2013 16:29:39 GMT

Yes, I did create a feature class with the sde login role on the gdb with 347 tables. For another test, I loaded AGD 10.2 on the same box as the PG server and created a new gdb. That worked as you'd expect by running the CEG tool. I then created a FC as sde login role and now have 347 functions and 42 tables for the sde schema. One thing I'm unsure about is where the new versions (libeay32.dll 1.0.1.3) of the client libraries on the server came from. They are in the 9.2 / bin directory. Does the ESRI PostGreSQL DBMS Installer for 9.2.2 put them there?

Re: ArcSDE for Postgre SQL 9.1

VinceAngelo — Thu, 24 Oct 2013 16:47:23 GMT

My main concern has been to copy the st_geometry.dll from the Desktop install (9.2\Windows64
in this case) to the PGHOME/lib directory (lib/postgresql on Linux), and to extract the DLLs from
a 32-bit PGHOME/bin to the Desktop bin directory. I haven't seen any changes to the 64-bit DLLs
under PGHOME/bin.

- V

Re: ArcSDE for Postgre SQL 9.1

AndrewBrown — Thu, 24 Oct 2013 17:15:48 GMT

Thanks for you help V, I am glad to know that it wasn't designed to be that painful to create an Enterprise Geodatabase in PostgreSQL.