Backing up and restoring geodatabases in PostgreSQL

ThomasJurk

Hello

I try to establish a workflow to backup und restore geodatabases in postgreSQL.

The instructions in the article http://support.esri.com/en/knowledgebase/techarticles/detail/36522 are unapplicable for SDE 10 and postgres 8.4 . pg_restore throws a lot of errors while importing.

So far dumping the public schema seperatly from the rest of the database works.

pg_dump -n public -Fc > sde_public.dump
pg_dump -N public -Fc > sde_etc.dump
dropdb sde
ceatedb sde
echo "alter database sde set search_path = E'\$user',public,sde;" | psql
pg_restore --disable-triggers sde_public.dump
pg_restore --disable-triggers sde_etc.dump

while importing the dump following error occurs for each featrue class in the user schema:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2432; 1259 59311 INDEX a2_ix1_a brdt
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied for schema sde
LINE 1: SELECT srid FROM SDE.ST_GEOMETRY_COLUMNS WHERE  lower(OWNER_...
                         ^
QUERY:  SELECT srid FROM SDE.ST_GEOMETRY_COLUMNS WHERE  lower(OWNER_NAME) = lower('brdt') AND  lower(DATABASE_NAME) = lower('sde') AND  lower(SCHEMA_NAME) =e')
    Command was: CREATE INDEX a2_ix1_a ON a7 USING gist (shape);

Access the sde in arccatalog / arcmap works, but i think the missing indexes have an impact on the performace of the sde instance.

After some investigation i found that the broken sql is hard coded in the st_geometry.so used by the postgres binary. Of course, there are some dirty workarounds for that problem but for a clean backupworkflow they are not acceptable.

Any suggestions would help.
Thanks
Thomas

ManviLather1

Hello,

Try granting following permission to public from pgadmin:

GRANT USAGE ON SCHEMA sde TO public;

and

restore the user_schema once again.

Hope this helps!