Is This Content Helpful?
We're glad to know this article was helpful.
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.
To work around this problem, restore the public schema individually before restoring the full database. See examples below.
pg_dump -h machine_name -p 5432 -U postgres -F c -v -f "C:\db_name.dump.backup" db_name
psql -U postgres db_name -c "alter database db_name set search_path = E'"$user"',public,sde;
pg_restore -n public -h machine_name -p 5432 -U postgres -d db_name -v "C:\db_name.dump.backup"
pg_restore -h machine_name -p 5432 -U postgres –d db_name -v "C:\db_name.dump.backup