Problem: Backing up and restoring geodatabases in PostgreSQL 8.3.0 may cause errors
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.
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
To work around this problem, restore the public schema individually before restoring the full database. See examples below.
- Create a backup of the entire database.
pg_dump -h machine_name -p 5432 -U postgres -F c -v -f "C:\db_name.dump.backup" db_name
- 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.
psql -U postgres db_name -c "alter database db_name set search_path = E'"$user"',public,sde;
- Restore the contents of the public schema.
pg_restore -n public -h machine_name -p 5432 -U postgres -d db_name -v "C:\db_name.dump.backup"
- Restore the entire database for all schemas.
pg_restore -h machine_name -p 5432 -U postgres –d db_name -v "C:\db_name.dump.backup