HelloI 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.ThanksThomas
