topic Re: Creating an enterprise geodatabase in Linux in Data Management Questions

Creating an enterprise geodatabase in Linux

Anonymous User — Wed, 21 Mar 2012 12:26:42 GMT

Original User: jkuoppam

Hi,

I've struggled with Enterprise Geodatabase installation for ArcGIS 10.1 Prerelease ín Linux, with Postgresql.

Postgresql has been installed according to its installation instructions [1]. The output is in the attachment below.

ArcSDE Application Server and the command line tools have been installed according to their installation instructions [2]. The output is in the attachment below.

Since I don't have network connectivity from my ArcGIS for Desktop to the server yet, I've tried to create the geodatabase using the script shown in http://resourcesbeta.arcgis.com/en/help/main/10.1/index.html#/Scripting_database_and_geodatabase_creation_in_PostgreSQL/002p000000sz000000/ . Outputs to stdout- and stderr are in the attachment below. (Had to zip it since this forums only accepts .txt files up to 12,5 kB)

It seems that an sde schema is created in Postgresql:

[INDENT]postgres=# select * from pg_namespace;
      nspname       | nspowner |               nspacl
--------------------+----------+-------------------------------------
pg_toast           |       10 |
pg_temp_1          |       10 |
pg_toast_temp_1    |       10 |
pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
public             |       10 | {postgres=UC/postgres,=UC/postgres}
information_schema |       10 | {postgres=UC/postgres,=U/postgres}
sde                |    16384 | {sde=UC/sde,=U/sde}[/INDENT]

However, starting ArcSDE fails. I've tried with different parameters and left them out even, but the response is always the same:

[INDENT]
[sde@devel ~]$ sdemon -o start -D gis -i 5151
Please enter ArcSDE DBA password:
-------------------------------------------------------
ArcSDE 10.1 for PostgreSQL Build 611 Wed Dec 21 12:41:15 2011
-------------------------------------------------------

FATAL: database "sde" does not exist
Connection to database 'sde' failed.
init_DB DB_instance_open_as_dba: -162

Could not start ArcSDE -- Check Network, $SDEHOME disk, DBMS settings and dbinit.sde.

[sde@devel ~]$[/INDENT]

What should I try next? What am I missing?

Regards,

--Jarkko

[1] http://wiki.postgresql.org/wiki/YUM_Installation
[2] http://resourcesbeta.arcgis.com/en/help/install-guides/arcsde-application-server-postgresql/10.1/index.html

Re: Creating an enterprise geodatabase in Linux

JonDeRose — Wed, 21 Mar 2012 19:01:08 GMT

Jarrko,

Are you able to make a direct-connection to the newly enabled geodatabase? After connecting through your Database Connection, right-click the connection and go to Geodatabase Connection Properties and verify you can see a DEFAULT version and then click OK.

From the "create_enterprise_gdb.err.log" the instalaltion looks to have completed successfully.

If so, the creation of the ArcSDE service on this Postgresql geoedatabase should be no different from that seen within previous releases. Verify that the service is assigned to a port as needed within the following locations:

$SDEHOME/etc/services.sde
/etc/services

I would expect to see something akin to esri_sde 5151/tcp in each of these locations.

Registering an ArcSDE service on UNIX or Linux
http://resourcesbeta.arcgis.com/en/help/main/10.1/index.html#/Registering_an_ArcSDE_service_on_UNIX_or_Linux/002p0000004v000000/

Following this you should be able to start with nothing more than sdemon -o start -i esri_sde (or -i 5151). For some reason the error (-162 SE_INVALID_DATABASE) thinks that you are trying to start the service to hit a database named "sde". Should this continue to fail please upload the contents of your $SDEHOME/etc for review.

Re: Creating an enterprise geodatabase in Linux

Anonymous User — Wed, 21 Mar 2012 19:14:21 GMT

Original User: vangelo

You probably need to specify the SDE_DATABASE variable in the dbinit.sde
associated with your instance. The complete list of environment variables
is in the documentation.

- V

Re: Creating an enterprise geodatabase in Linux

JarkkoKuoppamäki — Thu, 22 Mar 2012 07:34:41 GMT

Thanks guys for quick responses.

Adding the SDE_DATABASE variable into dbinit.sde did the trick, and I was finally able to start the sde service.

$SDEHOME/etc/services.sde and /etc/services both had �??esri_sde 5151/tcp�?� in them.

I'm not able to make a direct connection from my Desktop to the geodatabase yet, due to network configuration, but this is in the works and will be hopefully fixed today/tomorrow.

Re: Creating an enterprise geodatabase in Linux

Anonymous User — Wed, 28 Mar 2012 16:28:51 GMT

Original User: jkuoppam

I was able to connect to the database in ArcCatalog.

However, anything I try results in

invalid input syntax for type numeric: "-20037700,00000000000000000000000000000000"
::SQL state: 22P02]

...or similar. "Anything" being in this case New -> Feature Class, or copying and pasting feature classes from a fgdb, and probably something I've forgotten here. I assumed that the decimal separator (comma vs. dot) is to blame, but how can I be sure, and how to change this? The locale was UTF-8 by default and I changed that to Latin1 (iso88591), and en_US to fi_FI. However, this didn't have any effect.

Any ideas what to try next?

Here's the sde_setup.log from trying to "enable a geodatabase" via ArcCatalog:

[INDENT]
[Wed Mar 28 19:52:47 2012] ev.SQLstate = 42P01
SDE DBMS Error: -37 ERROR: relation "sde.sde_layers" does not exist
LINE 1: select count(*) from gis2.sde.sde_layers
                             ^

[Wed Mar 28 19:52:47 2012] ev.SQLstate = 42P01
SDE DBMS Error: -37 ERROR: relation "sde.sde_server_config" does not exist
LINE 1: select count(1) from gis2.sde.sde_server_config
                             ^

[Wed Mar 28 19:52:47 2012] ev.SQLstate = 42P01
SDE DBMS Error: -37 ERROR: relation "sde.sde_dbtune" does not exist
LINE 1: select count(keyword) from gis2.sde.sde_dbtune
                                   ^

[Wed Mar 28 19:52:47 2012] ev.SQLstate = 42P01
SDE DBMS Error: -37 ERROR: relation "sde.sde_server_config" does not exist
LINE 1: select count(1) from gis2.sde.sde_server_config
                             ^

[Wed Mar 28 19:52:47 2012] ST_GEOMETRY support being installed...
[Wed Mar 28 19:52:47 2012] ev.SQLstate = 42P01
SDE DBMS Error: -37 ERROR: relation "sde.sde_coordinate_systems" does not exist
LINE 1: select count(*) from gis2.sde.sde_coordinate_systems
                             ^

[Wed Mar 28 19:52:47 2012] COORDINATE_SYSTEMS table being created...
[Wed Mar 28 19:52:47 2012] COORDINATE_SYSTEMS table created...
[Wed Mar 28 19:52:47 2012] ev.SQLstate = 42P01
SDE DBMS Error: -37 ERROR: relation "public.sde_spatial_references" does not exist
LINE 1: select count(object_flags) from gis2.public.sde_spatial_r...
                                        ^

[Wed Mar 28 19:52:47 2012] SPATIAL_REFERENCES table being created...
[Wed Mar 28 19:52:48 2012] SPATIAL_REFERENCES table created...
[Wed Mar 28 19:52:48 2012] ev.SQLstate = 42P01
SDE DBMS Error: -37 ERROR: relation "sde.sde_geometry_columns" does not exist
LINE 1: select count(*) from gis2.sde.sde_geometry_columns
                             ^

[Wed Mar 28 19:52:48 2012] GEOMETRY_COLUMNS table being created...
[Wed Mar 28 19:52:48 2012] ev.SQLstate = 42P07
SDE DBMS Error: -62 ERROR: relation "sde_geometry_columns" already exists

[Wed Mar 28 19:52:48 2012] GEOMETRY_COLUMNS table created...
[Wed Mar 28 19:53:19 2012] ST_GEOMETRY support installed...
[Wed Mar 28 20:02:01 2012] ERROR inserting default entry in sde_spatial_references table.
Error: -51
DBMS error code: -51
ERROR: invalid input syntax for type numeric: "-400,00000000000000000000000000000000"

SQL state: 22P02
[Wed Mar 28 20:02:01 2012] ST_GEOMETRY install not completed (-514).
[Wed Mar 28 20:02:01 2012] ERROR installing/upgrading ArcSDE, Error = -514
[/INDENT]

Re: Creating an enterprise geodatabase in Linux

Anonymous User — Thu, 29 Mar 2012 17:26:58 GMT

Original User: kasi4188

Hi Jarrko,
Are you testing 10.1 prerelease?
We had fixed at least two bugs related to internationalization between prerelease and final, dealing specifically with decimal separators. I will list them in summary to give you an idea although I can not say for sure that they will fix the problem you are experiencing.
1. affecting: upgrade, creation of new gdb, enabling a db to become a gdb, creation of new feature class in an existing gdb, change to the way the spatial index creation code handles decimal separators, from dot to comma. Previously it could not handle commas
2. affecting dislplay in Desktop: change the way we display numeric fields with data containing decimals, be able to display both dot and comma decimal separators depending on the client os nationalization settings.

These fixes are not specific to linux, but rather a detecting the server/client internationalization settings on the os

Most Sincerely,
Kasia Tuszynska
ArcSDE Product Engineer

Re: Creating an enterprise geodatabase in Linux

JarkkoKuoppamäki — Fri, 30 Mar 2012 06:22:13 GMT

Hi Kasia,

thanks for the heads up. However, the problems arise before I have placed any data into the geodatabase, and by default Postgresql database cluster locale is/was en_US.UTF-8. I recreated it to fi_FI.iso88591 but that didn't have any affect.

I'm a bit sceptical whether the python script to create a geodatabase succeeded after all. When I select the database from the dropdown menu in Connection Properties dialog, the Postgresql log says:

[INDENT]ERROR: relation "sde.sde_layers" does not exist at character 22
STATEMENT: select count(*) from postgres.sde.sde_layers[/INDENT]

When I make the connection in ArcCatalog, the Postgresql log says:

[INDENT]ERROR: relation "sde.sde_logfiles" does not exist at character 174
STATEMENT: SELECT logfile_name, logfile_id, logfile_data_id,             registration_id, flags, session_tag, column_name, logfile_data_db, logfile_data_owner, logfile_data_table FROM gis.sde.sde_logfiles WHERE flags%2 = 0[/INDENT]

When I try New -> Feature Class in ArcCatalog with default values, the Postgresql log says:

[INDENT]ERROR: index "r7_sde_rowid_uk" does not exist
STATEMENT: drop index sde.r7_sde_rowid_uk
ERROR: function gis.sde.i7_get_ids(integer, integer) does not exist at character 8
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT: SELECT gis.sde.i7_get_ids ($1,$2)
ERROR: invalid input syntax for type numeric: "-20037700,00000000000000000000000000000000"
STATEMENT: SELECT s.srid FROM gis.public.sde_spatial_references s WHERE s.falsex = $1 and s.falsey = $2 and s.xyunits = $3 AND s.srtext = $4 AND COALESCE(xycluster_tol,0.0) = $5 Order By srid Desc
ERROR: invalid input syntax for type numeric: "-20037700,00000000000000000000000000000000"
STATEMENT: SELECT gis.sde.SDE_sref_def_insert ($1, $2::numeric, $3::numeric, $4::numeric, $5::numeric, $6::numeric, $7::numeric, $8::numeric, $9, $10, $11, $12, $13, $14::numeric, $15::numeric, $16::numeric)
ERROR: relation "i7" does not exist at character 13
STATEMENT: DELETE FROM i7 WHERE id_type = 2[/INDENT]

When I try New -> Table in ArcCatalog, the Postgresql log says:

[INDENT]ERROR: function gis.sde.i8_get_ids(integer, integer) does not exist at character 8
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT: SELECT gis.sde.i8_get_ids ($1,$2)[/INDENT]

...but it gets created anyway. Finally, when I try Import -> Feature Class (single) in ArcCatalog, the Postgresql log says:

[INDENT]ERROR: relation "sde.sde_logfiles" does not exist at character 174
STATEMENT: SELECT logfile_name, logfile_id, logfile_data_id,             registration_id, flags, session_tag, column_name, logfile_data_db, logfile_data_owner, logfile_data_table FROM gis.sde.sde_logfiles WHERE flags%2 = 0
ERROR: index "r9_sde_rowid_uk" does not exist
STATEMENT: drop index sde.r9_sde_rowid_uk
ERROR: function gis.sde.i9_get_ids(integer, integer) does not exist at character 8
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT: SELECT gis.sde.i9_get_ids ($1,$2)
ERROR: invalid input syntax for type numeric: "-20037700,00000000000000000000000000000000"
STATEMENT: SELECT s.srid FROM gis.public.sde_spatial_references s WHERE s.falsex = $1 and s.falsey = $2 and s.xyunits = $3 AND s.srtext = $4 AND COALESCE(xycluster_tol,0.0) = $5 Order By srid Desc
ERROR: invalid input syntax for type numeric: "-20037700,00000000000000000000000000000000"
STATEMENT: SELECT gis.sde.SDE_sref_def_insert ($1, $2::numeric, $3::numeric, $4::numeric, $5::numeric, $6::numeric, $7::numeric, $8::numeric, $9, $10, $11, $12, $13, $14::numeric, $15::numeric, $16::numeric)
ERROR: relation "i9" does not exist at character 13
STATEMENT: DELETE FROM i9 WHERE id_type = 2
ERROR: relation "sde.sde_logfiles" does not exist at character 174
STATEMENT: SELECT logfile_name, logfile_id, logfile_data_id,             registration_id, flags, session_tag, column_name, logfile_data_db, logfile_data_owner, logfile_data_table FROM gis.sde.sde_logfiles WHERE session_tag = 6 AND flags % 2 = 0[/INDENT]

Re: Creating an enterprise geodatabase in Linux

Anonymous User — Fri, 30 Mar 2012 14:02:10 GMT

Original User: TomLUX

Hy,
had the same problem.

After setting my regional settings on my Windows which runs the ArcGIS-Desktop to "English - UnitedStates", the enterprise geodatabase was successfully created.

I didn't tried out with the script, but should be similar. Just switch your client locale to "English - UnitedStates".

Tom

Re: Creating an enterprise geodatabase in Linux

tKasiaTuszynska — Wed, 04 Apr 2012 23:27:11 GMT

Jarkko,
The bug I am referring to should have made it impossible for you to create a gdb, or upgrade an existing one to prerelease if the settings on the client machine are non English, meaning that they use a "," instead of "." for decimal separation.
The "errors" you have posted are the queries we send to postgres to make sure that the objects to do not exist before they are created, so they are not really errors. If you are indeed running into the bug I am referring to you should see a postgres level error when trying to create the spatial index on the gdb_items table, you may have to switch to loggin "ALL" in postgresql.conf to see it.

Most Sincerely,
Kasia

Re: Creating an enterprise geodatabase in Linux

Anonymous User — Mon, 23 Apr 2012 06:19:30 GMT

Original User: jkuoppam

After setting my regional settings on my Windows which runs the ArcGIS-Desktop to "English - UnitedStates", the enterprise geodatabase was successfully created.

Tom,

a huge thank you. Changing the regional settings indeed fixed this.

Nevertheless, I hope Esri will fix this in the 10.1 Release version. I don't want to be forced to use "foreign" settings, nor I want to be changing the regional settings back and forth.

--Jarkko