Questions about Database and ArcSDE

DominikSchlarmann · ‎08-19-2013

Hello,
Over the weekend I've worked on setting up a database for my ArcGIS Server to enable WFS and Feature Access (editing, deleting, updating, creating features/attributes)
Some relevant information:

ArcGIS 10.2 installed on Windows Server 2012

PostgreSQL 9.2.2 64Bit is installed as well as PostGIS 2.0 http://resources.arcgis.com/en/help/system-requirements/10.2/index.html#//015100000075000000

I've copied the necessary 32Bit client files into the \bin-Directory of my ArcGIS Desktop 10.2 installation.

I've copied the necessary 64Bit client files into the \bin-Directory of my ArcGIS Server 10.2 installation. I'm even not sure, if I need this....

I've copied the necessary st_geometry file into the \lib-Directory of my PostgreSQL 9.2.2 installation.

I've registered the database and the folder via the ArcGIS Server manager in the data store tab. (see attached screenshot).

The connection via pgAdmin work. I can connect to the database using pgAdmin.

The connection via ArcCatalog-> Add Database Connection is finally working. I can connect to the database.

No I'm at the point, where I have to create a Enterprise Geodatabase: http://resources.arcgis.com/en/help/main/10.2/index.html#//002p000000t4000000
I've filed in the required fields and the wizard tries to creta Enterprise Geodatabase for a long while, but doesn't come to an end. It stucks at the last step:

Executing: CreateEnterpriseGeodatabase PostgreSQL myarcgisserver,4321 ghana DATABASE_AUTH postgres ***** SDE_SCHEMA sde ***** # C:\Users\Schlomm\Desktop\keycodes
Start Time: Mon Aug 19 12:50:14 2013
User has privileges required to create database objects.
Geodatabase admin user created.
Database created.
Validated authorization file.
User has required privileges for geodatabase setup.
XML support is enabled for the database instance.
Connected RDBMS instance is setup for Esri spatial type configuration. For now about 20 minutes without any change.

My goal: Being able to set up and use Feature Access and WFS services.
So...are my performed steps right or did I miss anything?
I've read about path variables and enabling geodatabases, but I don't know what and how to perform these steps.
Additionally it would be very nice, if someone could tell me the advantages of using ArcSDE. For now I haven't installed this.

Would be nice, to hear some tips.
Thanks in advance,
Dominik

Anonymous User · ‎08-19-2013

Original User: Schlomm

Hello,
To make it more easy to figure out, what's going wrong, I've started from scratch. Would be nice, if someone could guide me through the different steps. I've uninstalled PostgreSQL, uninstalled PostGIS, deleted any connection.
I've downloaded the file "PostgreSQL_DBMS_for_Windows_922_136133.exe" from the esri Customer Portal. This package include the PostgreSQL 9.2.2 executable installation.
So...no I have a new installation of PostgreSQL 9.2.2 64Bit without PostGIS and without modifying something except of the pg_hba.conf file to enable remote access. See screenshot "database.png" to get an overview how it looks right now.
I've also downloaded from the esri Customer Portal the "PostgreSQL_Client_Libraries_for_Windows_922_136131.exe" fiel, which includes the Client files for database support.
I've copied the 64Bit files to the \bin directory of my ArcGIS Sever installation
I've copied the 32bit files to the \bin directory of my ArcGIS Desktop installation, which is running in my personal computer.

As you can see in the first screenshot, I can access the database (running on the ArcGIS Server) by using pg Admin from my personal computer.
Now I want to add the Database connection to ArcCatalog. So I open ArcCatalog , go to "Add Database Connection" and there I fill in the necessary information (screenshot ArcCatalog.png)....by the way: It took me a while figuring out to define your port by using a comma -.-
The problem, which I have for now (look at screenshot ArcCatalog.png): I can't add the connection to the database, because I haven't got the chance to select one database. The existing database "postgres" (look screenshot of pg Admin; the database exists!) is not selectable via the "Add Database connection" assistant.

I think, it would be the est to figure out step by step what I have to do.
Thanks a lot,

Dominik

SachinKanaujia · ‎08-20-2013

Try typing in the database name yourself and connect?

Anonymous User · ‎08-20-2013

Original User: Schlomm

Thanks for your feedback!
...and thanks to forum subscription and instant email notification 😉

I've tried to type in the existing database manually (in my case the "postgres" database (look at first screenshot from post before)), but I get the error "Connections to system database are not allowed. Please provide a different database for the connection."

Anything I've forgotten?
Thanks anyway and I'm looking forward to get some further tips 🙂

SachinKanaujia · ‎08-20-2013

That doesnt look like the database name

Try giving dornroeschen or the IP address of the machine?

Anonymous User · ‎08-20-2013

Original User: Schlomm

Hi,
I think I have to specify the database name, haven't I?
The only exsiting database is "postgres" (http://forums.arcgis.com/attachment.php?attachmentid=26810&d=1376960221), but because of reasons, which I can't figure out, I can't access the postgres database via the "Add Database Connection" wizard. :confused:

Cheers,
Dominik

Edit: I think I have to grant access to the database or, as mentioned in the error message from Post # 4, creating a new one....but here I don't know, which type and with which settings.

SachinKanaujia · ‎08-20-2013

From your first post I can see the following

Executing: CreateEnterpriseGeodatabase PostgreSQL myarcgisserver,4321 ghana DATABASE_AUTH postgres ***** SDE_SCHEMA sde *****

Do you have the details of what you used to create the spatial database? You should be using the same. Some information is incorrect. Generally the User and database name will not be the same untill you have intentionally done so !!!!

Refer http://resources.arcgis.com/en/help/main/10.2/index.html#//002p000000t4000000

Creating Geodatabase section !!!

Anonymous User · ‎08-20-2013

Original User: Schlomm

Dear Sachin,
As I've written in my second post: I've completely uninstalled the old PostgreSQl database and set up a new one.
Refering to your link: http://resources.arcgis.com/en/help/main/10.2/index.html#//002p000000t4000000 I can say:
Install and configure PostgreSQl is done.
Place the ST_Geometry libraries in the PostgreSQL lib directory is done.
Place the PostgreSQL client files on your ArcGIS client machine is done.

So next step is:
Create a geodatabase - this is the step, which I've performed some minutes ago.
I've used the Create Enterprise Geodatabase (Data Management) tool to create a geodatabase.
First, I've tried to set up a new Enterprise Geodatebase without sde. This doesn't work! I get the following error (see third screenshot). This is a little bit of confusing because defining sde is optional.
Anyway...I've also tried to set up an Enterprise Geodatabase by defining sde. This time, it seems that I've sucessfuilly created an Enterprise Geodatabase (see second screenshot). But I still get an error (see screenshot) ""Error connecting to database as geodatabase admin. Failed to execute (CreateEnterpriseGeodatabase). Failed at Tue Aug 20 21:45:02 2013 (elapsed Time: 8,90 seconds).

Anyway: It seems, that the tool created a new database. Using pgAdmin I can access the database, which was created by the tool (see first screenshot). I can access the created database with the sde user (which is set as a superuser) and with the postgres user.
But the error confused me - Is there anyway to check if everything is working?

Thanks a lot for your feedback,
Dominik

KimPeter · ‎08-21-2013

Dominik,

It doesn't look like the tool got to the point of creating an sde schema in your database, so you do not have a geodatabase; geodatabase system tables are all stored in the sde schema. I'm pretty sure the reason the creation failed the first time is the mixed-case you specified for the database name. By default, object names in PostgreSQL are in lower case, which is seen in your pgAdmin screen shot. Try specifying the name of the database in lower case and run the tool again.

http://resources.arcgis.com/en/help/main/10.2/#/Creating_a_database_and_geodatabase_in_PostgreSQL/00...

Type the database name in lower case; upper- and mixed-case object names are not supported for geodatabases in PostgreSQL.

You have to specify an sde user name and password when creating a geodatabase in PostgreSQL. Since this tool also works with SQL Server and Oracle, for which the sde user name and password might not be needed, the tool shows those parameters as optional. However, they are not optional for PostgreSQL. Unfortunately, gp tools are not context sensitive in that way.

Since you already have an sde login role, so be sure you specify the password of that existing sde login when you run the tool again.

As indicated by one of the error messages you got, you cannot connect to the postgres system database from ArcGIS. The Create Enterprise Geodatabase will (and did) create a database for you to use. Be aware, though, that this database does not contain the PostGIS type, because it uses the template1 PostgreSQL database, as indicated in the side panel help for the Create Enterprise Geodatabase tool. If you want the geodatabase to contain the PostGIS geometry type, you must manually create a database using your PostGIS template, then you can specify that database when you run the Create Enterprise Geodatabase tool. Though, looking at your pgAdmin screenshot, it appears that you did not reinstall PostGIS...?

To answer your question about ArcSDE...at this point, the ArcSDE installation installs an ArcSDE application server, which is a way for your users to connect to the geodatabase using an ArcSDE service, and command line administration tools. You do not have to install that.

Other than the mixed-case name, it looks like you did everything correctly. You just got distracted by some other stuff along the way. 🙂

Anonymous User · ‎08-22-2013

Original User: Schlomm

Hello,
Thanks so much for your answer. Nice to see that there are people, who listen to my problems.
Back to my problem:
I've uninstalled the PostgreSQL 9.2.2 64Bit instance again and installed it again. It also allows remote access and the st_geometry.dll is stored in the lib folder of the postgreSQL installation. This time I've also installed PostGIS.
The client files are stored in my ArcGIS \bin folder.

You have to specify an sde user name and password when creating a geodatabase in PostgreSQL. Since this tool also works with SQL Server and Oracle, for which the sde user name and password might not be needed, the tool shows those parameters as optional. However, they are not optional for PostgreSQL. Unfortunately, gp tools are not context sensitive in that way.
Since you already have an sde login role, so be sure you specify the password of that existing sde login when you run the tool again.

Thanks for clarification!

So...now I've started the "Create Enterprise Geodatabase" again. I've specified the name of the database to "data". So now upper-case or mixed-case.
Unfortunately there is still another? error:

Executing: CreateEnterpriseGeodatabase PostgreSQL dornroeschen.xxxxxxx.de,5432 data DATABASE_AUTH postgres ***** SDE_SCHEMA sde ***** # C:\Users\Schlomm\Desktop\keycodes
Start Time: Thu Aug 22 16:49:38 2013
User has privileges required to create database objects.
Geodatabase admin user created.
Database created.
Validated authorization file.
User has required privileges for geodatabase setup.
XML support is enabled for the database instance.
Connected RDBMS instance is setup for Esri spatial type configuration.

Until now there is no progress for 35min, so I think something went wrong 😞 But what?`

I'm looking forward for further posts.
Thanks,
Dominik

Edit: After 39min the progress was completed 🙂