Restoring a geodatabase to PostgreSQL

To restore a database from an archive file created using the pg_dump command, use the pg_restore command. Be sure to test your backup and recovery models with test databases.

A summary of the steps you take to restore a database are provided here. There are some specific things you must do when restoring a geodatabase or a database with ST_Geometry installed, such as restoring the public schema first. However, for general recovery instructions, such as syntax options, see your PostgreSQL documentation. Also, if you have PostGIS installed and are using Geometry storage, be sure to read PostGIS's documentation about creating backups and restoring databases. This procedure could vary depending on the version of PostGIS you are using.

1. Database names must be unique on the PostgreSQL instance, so drop the old database.

   dropdb –U sde mypgdb
   

2. Re-create the database.
   Note:

   Make sure that the new database has the same properties as the database you are going to restore, including name, encoding, and owner.

   createdb –U sde –E UTF8 –D gdbtablespace -O sde mypgdb
   

3. Restore the public schema and data using the pg_restore command.
   Caution:

   You must restore the public schema and its contents first. If you do not, some of your spatial data will not restore.

   For example, to restore the public schema of a database backup file named mypgdb1031.dump to database mygdb, run the following:

   pg_restore -U sde -n public -d mypgdb -f mypgdb1031.dump
   

4. Restore the remaining schemas and data.

   pg_restore –U sde –d mypgdb -f mypgdb1031.dump
   

5. Once data is restored, spatial indexes must be re-created on all the feature classes. Data owners can re-create spatial indexes from the feature class Properties dialog box, using the Add Spatial Index geoprocessing tool or a Python script.