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