12 Replies Latest reply on Apr 6, 2011 3:24 AM by MacJeremy

    SDE PostgreSQL multiple databases problem

    mtgabor
      Hi,

      we have successfully installed the ArcGIS Server SDE on the PostgreSQL, now it's running. We would like to create an additional SDE geodatabase which can be reach by direct connection.
      I've tried to complete the - post - installation process for the second postgre database according to the ArcGIS help ( http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/using_m1548144787.htm), but it stopped with an error message:

      Creating ST_GEOMETRY and ArcSde schema.....
      Error: (null) (-1015).
      Error: SDE release install not completed.
              Check SDEHOME\etc\sde_setup.log for more details.

      Has ideas, to solve this problem?

      Thanks

      Gabor
        • Re: SDE PostgreSQL multiple databases problem
          ktuszynska-esristaff
          Gabor,
          1. If you are running on windows(2008, 7...) are you executing the postinstaller "as administrator"
          2. Are you trying to run setup against a database that already has a geodatabase instead of creating a new database(step one of the postinstaller)
          3. What does the sde_setup.sde log say?

          Sincerely,
          Kasia Tuszynska
          • Re: SDE PostgreSQL multiple databases problem
            mtgabor
            Gabor,
            1. If you are running on windows(2008, 7...) are you executing the postinstaller "as administrator"
            2. Are you trying to run setup against a database that already has a geodatabase instead of creating a new database(step one of the postinstaller)
            3. What does the sde_setup.sde log say?

            Sincerely,
            Kasia Tuszynska



            Kasia,

            thank you for your fast response.
            1. The ArcSDE is running on Suse Linux. The sdesetup -o install ... command was requested as "sde" user.
            2. I've created a new database (without postgis, because we use esri geom) with pgadmin. The sde schema was also created with full control.
            3. I will check the log file tomorrow.

            Tank you.

            Gabor
            • Re: SDE PostgreSQL multiple databases problem
              hawatson
              Hi - did you resolve this problem?

              I am having the same issue setting up a stand-alone geodatabase on SLES/Posgresql:

              ESRI ArcSDE Server Setup Utility Fri Feb 25 12:34:04 2011
              ----------------------------------------------------------------
              Install or update ST_GEOMETRY, ArcSDE, GDB schema objects:
              Are you sure? (Y/N): Y
              Creating ST_GEOMETRY and ArcSde schema.....
              Error: (null) (-1015).
              Error: SDE release install not completed.


              sde_setup.log:

              [Fri Feb 25 12:34:06 2011] ST_GEOMETRY support being installed...
              [Fri Feb 25 12:34:06 2011] ST_GEOMETRY install not completed (-1015).
              [Fri Feb 25 12:34:06 2011] ERROR installing/upgrading ArcSDE, Error = -1015
              • Re: SDE PostgreSQL multiple databases problem
                kapnawesome
                Having the exact same issues on Red Hat.  Here is the relevant log. 

                [Fri Mar 18 15:31:01 2011] ev.SQLstate = 42P01
                SDE DBMS Error: -37  ERROR:  relation "sde.sde_layers" does not exist

                [Fri Mar 18 15:31:01 2011] ev.SQLstate = 42P01
                SDE DBMS Error: -37  ERROR:  relation "sde.sde_server_config" does not exist

                [Fri Mar 18 15:31:01 2011] ev.SQLstate = 42P01
                SDE DBMS Error: -37  ERROR:  relation "sde.sde_dbtune" does not exist

                [Fri Mar 18 15:31:01 2011] ev.SQLstate = 42P01
                SDE DBMS Error: -37  ERROR:  relation "sde.sde_server_config" does not exist

                [Fri Mar 18 15:31:01 2011] ST_GEOMETRY support being installed...
                [Fri Mar 18 15:31:01 2011] ev.SQLstate = 42P01
                SDE DBMS Error: -37  ERROR:  relation "sde.sde_coordinate_systems" does not exist

                [Fri Mar 18 15:31:01 2011] COORDINATE_SYSTEMS table being created...
                [Fri Mar 18 15:31:01 2011] COORDINATE_SYSTEMS table created...
                [Fri Mar 18 15:31:01 2011] ev.SQLstate = 42P01
                SDE DBMS Error: -37  ERROR:  relation "public.sde_spatial_references" does not exist

                [Fri Mar 18 15:31:01 2011] SPATIAL_REFERENCES table being created...
                [Fri Mar 18 15:31:01 2011] SPATIAL_REFERENCES table created...
                [Fri Mar 18 15:31:01 2011] ev.SQLstate = 42P01
                SDE DBMS Error: -37  ERROR:  relation "sde.sde_geometry_columns" does not exist

                [Fri Mar 18 15:31:01 2011] GEOMETRY_COLUMNS table being created...
                [Fri Mar 18 15:31:01 2011] GEOMETRY_COLUMNS table created...
                [Fri Mar 18 15:31:01 2011] ST_GEOMETRY install not completed (-1015).
                [Fri Mar 18 15:31:01 2011] ERROR installing/upgrading ArcSDE, Error = -1015

                Can anyone offer some help?
                • Re: SDE PostgreSQL multiple databases problem
                  ktuszynska-esristaff
                  Hi Everybody,
                  Sorry you are all running into the same thing.
                  So, the st_geometry type is not being installed during setup.
                  st_geometry.so needs to be present in:
                  SDEHOME/bin location
                  PG/ lib location  - where ever your LD_LIBRARY Path is pointing to for the postgres user, that location will depend on the Postgres version and how it was installed.

                  Sincerely,
                  Kasia
                  • Re: SDE PostgreSQL multiple databases problem
                    MacJeremy
                    Heya,

                    had similar problems when I started to install SDE and PostgreSQL. My setup is somewhat different, because I have PostgreSQL on one server and SDE on another. Had to do it that way because SDE 9.3.1 doesn't have an 64 bit installation for linux and postgres.

                    I have SDE installed into /opt and $SDEHOME is pointing to it. And whenever I have to install another service and database these are the steps I take:

                    Create or change the dbinit....sde, services.sde and /etc/services files as per manual. Here is an excerpt from my services.sde and /etc/services files:

                    /etc/services
                    #               6772-6784  Unassigned
                    gis-database-1  6772/tcp   # ArcSDE service gis-database-1
                    gis-database-1  6772/udp   # ArcSDE service gis-database-1

                    gis-database-2  6773/tcp   # ArcSDE service gis-database-2
                    gis-database-2  6773/udp   # ArcSDE service gis-database-2

                    gis-database-3  6774/tcp   # ArcSDE service gis-database-3
                    gis-database-3  6774/udp   # ArcSDE service gis-database-3


                    services.sde
                    gis-database-1 6772/tcp # ArcSDE service gis-database-1
                    gis-database-2 6773/tcp # ArcSDE service gis-database-2
                    gis-database-3 6774/tcp # ArcSDE service gis-database-3

                    export PGHOST="<database-server>" (if on the same machine then localhost)
                    export PGPORT="5432" (depends on the postgresql setup)
                    export SDE_DATABASE="<database_name>"

                    In the PostgresSQL - CLI (or any other means)

                    CREATE ROLE sde LOGIN PASSWORD '<pass>' SUPERUSER NOINHERIT CREATEDB; (only for the first database, not needed for the following ones)
                    CREATE TABLESPACE <ts_name> OWNER sde LOCATION '/srv/pgsql/data/...'; (only if you want to)
                    CREATE DATABASE <database_name> OWNER sde ENCODING 'UTF8' TABLESPACE <ts_name>;
                    GRANT ALL ON DATABASE <database_name> TO sde;
                    \c <database_name>
                    CREATE SCHEMA sde AUTHORIZATION sde;
                    GRANT ALL ON SCHEMA sde to sde;
                    GRANT USAGE ON SCHEMA sde TO public;
                    CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler VALIDATOR plpgsqsl_validator;

                    sdesetup -o install -d POSTGRESQL -D <database_name> -u sde -p <sde_user_password> -l <license_key>
                    sdemon -i gis-database-1 (or 2, or 3...) -o start

                    I found out that the exports are very much needed. Even if they are already stated in the dbinit file. But actully this is simplifying some procedures. Whenever you want to maintain another database (respectively SDE service) just change

                    export SDE_DATABASE="<database_name>"

                    Hope that helps some of you....


                    P.S.: Database names and instance names don't have to be the same. But it's easier that way.
                    • Re: SDE PostgreSQL multiple databases problem
                      kapnawesome
                      Thank You Kasia and MacJeremy for your responses.  Unfortuantely, neither seems to be helping me.

                      Kasia,

                      I checked that st_geometry was in the proper places (even $SDEHOME/bin, which I'm guessing might be a typo for $SDEHOME/lib):

                      -sh-3.2$ cd $SDEHOME/bin
                      -sh-3.2$ ls -la st_geo*
                      -rwxr-xr-x 1 sde sde 3912755 Mar 29 16:08 st_geometry.so
                      -sh-3.2$ cd $SDEHOME/lib
                      -sh-3.2$ ls -la st_geo*
                      -rwxr-xr-x 1 sde sde 3912755 Sep 17  2010 st_geometry.so
                      -rwxrwxrwx 1 sde sde 3904485 May 14  2010 st_geometry.so.orig
                      -sh-3.2$ echo $LD_LIBRARY_PATH
                      /var/sde/sdeexe100/lib:/usr/lib/pqsql:/usr/lib:/lib
                      -sh-3.2$ cd /usr/lib/pgsql
                      -sh-3.2$ ls -la st_geo*
                      -rwxr-xr-x 1 root root 3904485 Mar 17 16:48 st_geometry.so
                      -sh-3.2$ sdesetup -o install -d POSTGRESQL -s localhost -D norena -u sde -l /var/sde/authorization.ecp
                      Enter DBA password:

                      ESRI ArcSDE Server Setup Utility Tue Mar 29 16:23:33 2011
                      ----------------------------------------------------------------
                      Install or update ST_GEOMETRY, ArcSDE, GDB schema objects: Are you sure? (Y/N): Y

                      Checking INSTALL privileges for geodatabase ...
                      Current user has privilege to install geodatabase instance.

                      Checking geodatabase XML datatype support...
                      Underlying RDBMS database instance supports XML data type.

                      Creating ST_GEOMETRY and ArcSDE schema.....
                      Error: (null) (-1015).
                             SDE release install not completed.
                      Check SDEHOME\etc\sde_setup.log for more details.
                      -sh-3.2$

                      I'm still unable to create the geodatabase.  I want to emphasize that I'm trying to create the 2nd geodatabase for Postgres.  The first geodatabase was set up without a hitch.

                      Here is the output in the log file again:

                      [Tue Mar 29 16:18:47 2011] ev.SQLstate = 42P01
                      SDE DBMS Error: -37  ERROR:  relation "sde.sde_layers" does not exist

                      [Tue Mar 29 16:18:47 2011] ev.SQLstate = 42P01
                      SDE DBMS Error: -37  ERROR:  relation "sde.sde_server_config" does not exist

                      [Tue Mar 29 16:18:47 2011] ev.SQLstate = 42P01
                      SDE DBMS Error: -37  ERROR:  relation "sde.sde_dbtune" does not exist

                      [Tue Mar 29 16:18:47 2011] ev.SQLstate = 42P01
                      SDE DBMS Error: -37  ERROR:  relation "sde.sde_server_config" does not exist

                      [Tue Mar 29 16:18:47 2011] ST_GEOMETRY support being installed...
                      [Tue Mar 29 16:18:47 2011] ST_GEOMETRY install not completed (-1015).
                      [Tue Mar 29 16:18:47 2011] ERROR installing/upgrading ArcSDE, Error = -1015
                      [Tue Mar 29 16:23:35 2011] ev.SQLstate = 42P01
                      SDE DBMS Error: -37  ERROR:  relation "sde.sde_layers" does not exist

                      [Tue Mar 29 16:23:35 2011] ev.SQLstate = 42P01
                      SDE DBMS Error: -37  ERROR:  relation "sde.sde_server_config" does not exist

                      [Tue Mar 29 16:23:35 2011] ev.SQLstate = 42P01
                      SDE DBMS Error: -37  ERROR:  relation "sde.sde_dbtune" does not exist

                      [Tue Mar 29 16:23:35 2011] ev.SQLstate = 42P01
                      SDE DBMS Error: -37  ERROR:  relation "sde.sde_server_config" does not exist

                      [Tue Mar 29 16:23:35 2011] ST_GEOMETRY support being installed...
                      [Tue Mar 29 16:23:35 2011] ST_GEOMETRY install not completed (-1015).
                      [Tue Mar 29 16:23:35 2011] ERROR installing/upgrading ArcSDE, Error = -1015
                      -sh-3.2$


                      Thank you in advance to anyone who offers advice.
                      • Re: SDE PostgreSQL multiple databases problem
                        MacJeremy
                        Can you please post the logfile of the Postgres Database. (The part that is relevant while creating the second database)

                        It should be somewhere .../pgsql/data/logfile
                        In linux you can achieve this with tail -f logfile > logfile_setup_sde
                        • Re: SDE PostgreSQL multiple databases problem
                          kapnawesome
                          Thank you for your continued help.  Here is what I believe is the relevant log entry.


                          ERROR:  relation "sde.sde_layers" does not exist
                          STATEMENT:  select count(*) from norena.sde.sde_layers
                          ERROR:  relation "sde.sde_version" does not exist
                          STATEMENT:  SELECT 1 FROM sde.SDE_version
                          ERROR:  relation "sde.sde_server_config" does not exist
                          STATEMENT:  SELECT 1 FROM sde.SDE_server_config
                          ERROR:  relation "sde.sde_layers" does not exist
                          STATEMENT:  select count(*) from norena.sde.sde_layers
                          ERROR:  relation "sde.sde_layers" does not exist
                          STATEMENT:  select count(*) from norena.sde.sde_layers
                          ERROR:  relation "sde.sde_server_config" does not exist
                          STATEMENT:  select count(1) from norena.sde.sde_server_config
                          ERROR:  relation "sde.sde_process_information" does not exist
                          STATEMENT:  select count(*) from norena.sde.sde_process_information
                          ERROR:  relation "sde.sde_dbtune" does not exist
                          STATEMENT:  select count(keyword) from norena.sde.sde_dbtune
                          ERROR:  relation "sde.sde_server_config" does not exist
                          STATEMENT:  select count(1) from norena.sde.sde_server_config
                          NOTICE:  type reference norena.public.sde_spatial_references.srid%TYPE converted to integer
                          ERROR:  function norena.sde.sde_sref_def_delete(integer) does not exist
                          STATEMENT:  DROP FUNCTION norena.sde.SDE_sref_def_delete(i_sref_id  norena.public.sde_spatial_references.SRID%TYPE)
                          ERROR:  language "plpgsql" does not exist
                          HINT:  Use CREATE LANGUAGE to load the language into the database.
                          STATEMENT:  CREATE OR REPLACE FUNCTION norena.sde.SDE_sref_def_delete(i_sref_id  norena.public.sde_spatial_references.SRID%TYPE)   RETURNS INTEGER AS '
                          DECLARE
                             sql_code  INTEGER;
                          BEGIN
                             sql_code := -1;
                             DELETE FROM norena.public.sde_spatial_references  WHERE srid = i_sref_id;
                             IF NOT FOUND THEN
                               RAISE EXCEPTION ''Spatial Reference entry not found.'';
                             END IF;
                             sql_code := 0;
                             RETURN sql_code;
                          END;
                          ' LANGUAGE plpgsql SECURITY DEFINER;
                          LOG:  unexpected EOF on client connection
                          LOG:  unexpected EOF on client connection
                          • Re: SDE PostgreSQL multiple databases problem
                            MacJeremy
                            [QUOTE=kapnawesome;90162]Thank you for your continued help.  Here is what I believe is the relevant log entry.


                            <snip>
                            ERROR:  function norena.sde.sde_sref_def_delete(integer) does not exist
                            STATEMENT:  DROP FUNCTION norena.sde.SDE_sref_def_delete(i_sref_id  norena.public.sde_spatial_references.SRID%TYPE)
                            ERROR:  language "plpgsql" does not exist
                            HINT:  Use CREATE LANGUAGE to load the language into the database.
                            STATEMENT:  CREATE OR REPLACE FUNCTION norena.sde.SDE_sref_def_delete(i_sref_id  norena.public.sde_spatial_references.SRID%TYPE)   RETURNS INTEGER AS '
                                DECLARE
                                  sql_code  INTEGER;
                                BEGIN
                                  sql_code := -1;
                            </snip>

                            I guess that you found the error.

                            In my previous post you can read:

                            ....
                            CREATE SCHEMA sde AUTHORIZATION sde;
                            GRANT ALL ON SCHEMA sde to sde;
                            GRANT USAGE ON SCHEMA sde TO public;
                            CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler VALIDATOR plpgsqsl_validator;
                            ...

                            This is required for all newly created databases (respectively sde connections). See if you have an error with this command in your postgres logfile. If yes then you have a problem with your postgres installation...


                            • Re: SDE PostgreSQL multiple databases problem
                              kapnawesome
                              Thank you for highlighting the portion of your original post I missed. 

                              CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler VALIDATOR plpgsqsl_validator;

                              was indeed the last piece needed and after running it, everything went smoothly.
                              • Re: SDE PostgreSQL multiple databases problem
                                MacJeremy
                                Glad to be of help... :-)