AnsweredAssumed Answered

Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Question asked by geonetadmin on Mar 22, 2011
Latest reply on Dec 21, 2011 by geonetadmin
Original User: MacJeremy


having some troubles with a setup. I have an SDE server (9.3.1) up and running on a Linux 32bit machine. My PostgreSQL (8.3.11) database server is running on another Linux machine (64bit). I have already 2 different applications with two different sde services and databases running. Everything works perfectly. Now I wanted to do some housekeeping in regard to the databases. I wanted to start using one database with one sde service and many user schemas through SDE. Why do i want to do that? Because of security, maintanance and many other reasons. And it was stated somewhere that this is the preferred way for SDE and ORACLE. I found a howto somewhere on the support site of ESRI. On further reading I also found how to do this with POSTGRESQL, not through the instance but through the user options on sdesetup, but not fully explained. So here are the steps I took that took me the furthest:

In PostgreSQL CLI (creating databases which are functioning and creating the schemas are very similar. If you need the steps I created the database with, in which I then created the schema, I'll post them here):

CREATE SCHEMA <schema_name> AUTHORIZATION <schema_name>;
GRANT ALL ON SCHEMA <schema_name> to <schema_name>;
GRANT ALL ON SCHEMA <schema_name> TO sde;
GRANT USAGE ON SCHEMA <schema_name> TO public;

sdesetup -o install -d POSTGRESQL -D <main_database_where_the_schema_resides> -u <schema_name>

But unfortunatly halfway through the setup it breaks. After that when I restart the above sdesetup statement it breaks again but at a later point in the install, and so on and so on. I recon that I could get to succesfully installing the geodatabase if I would repeat the sdesetup call numerous times, but ... I would prefer a method that would get me there without errors

Here is a little tryout. I created a database "gismain", created the sde schema, and ran sdesetup. This I can start through the SDE service and connect with ArcCatalog. No problem there. Now the SDE service of gismain is stopped (tried it with on, but there I had more difficulties than with the services off), and used the steps of creating the schema above for a schema called "gp".

But sdesetup breaks with:
sdesetup -o install -d POSTGRESQL -D gismain -u gp
Enter DBA password:

ESRI ArcSDE Server Setup Utility Tue Mar 22 18:33:45 2011
Install or update ST_GEOMETRY, ArcSDE, GDB schema objects:
  Are you sure? (Y/N): y
Creating ST_GEOMETRY and ArcSde schema.....
Successfully created ST_GEOMETRY and ArcSde schema.

Installing locators.....
Successfully installed locators.

Creating geodatabase schema.....
Error: DBMS object exists (-452).
Error: Error installing GDB schema.
Error: DBMS object exists (-452).
Error: Geodatabase schema object install not completed.
Check SDEHOME\etc\sde_setup.log or
    SDEHOME\etc\sde_dc<rdbms>.log for more details..

Whole logfiles of sdesetup log and postgres log are attached, where you can see that sdesetup is writing to the gp schema. It created four tables, and if I would restart sdesetup it would create another 3 or 4 tables. I tried many approches to solving this, checked role privileges and many other stuff, but no avail.

Help is needed and very much appreciated.

THX, in advance....