Backing up and restoring geodatabases in PostgreSQL 8.3.0 may cause errors

Description

When using an enterprise geodatabase in PostgreSQL or storing ST_Geometry data in a PostgreSQL database, restoring data can result in some of the user data tables being blank.

Cause

When using the PostgreSQL pg_restore tool, the restoration occurs in alphabetical order by the schema name. Since user spatial data has dependencies on the sde_spatial_reference system table, the restoration of user data before the restoration of this table causes the data to restore improperly.

The sde_spatial_reference table, which resides in the public schema, must be present for user spatial data to be restored correctly. Thus, user data that resides in schemas named with letters a – pua are blank after the database is restored, whereas user spatial data that resides in schemas named puc – z restore properly because the public schema with the necessary table is already in place.

Solution or Workaround

1. Create a backup of the entire database.
   
   

   Code:
   pg_dump -h machine_name -p 5432 -U postgres -F c -v -f "C:\db_name.dump.backup" db_name

   
   
2. Decide where to restore.
   a. Use the backup file to restore against the same database.
   b. Use the backup file to restore against a new database of the same name.
   
   i. Drop and re-create the database. Make sure that it has the same properties as the backed up database, including name, encoding, and owner.
   
   
   ii. Set the search_path variable on the re-created database to search the user, public, and sde schemas. Alter the database to set this variable at the command line.
   
   

   Code:
   psql -U postgres db_name -c "alter database db_name set search_path = E'"$user"',public,sde;

   
   
3. Restore the contents of the public schema.
   
   

   Code:
   pg_restore -n public -h machine_name -p 5432 -U postgres -d db_name -v "C:\db_name.dump.backup"

   
   
4. Restore the entire database for all schemas.
   
   

   Code:
   pg_restore -h machine_name -p 5432 -U postgres –d db_name -v "C:\db_name.dump.backup