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

Heya,

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 ROLE <schema_name> LOGIN PASSWORD '<pass>' SUPERUSER NOINHERIT CREATEDB;
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....

Outcomes