Recovery models for 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. For more complete instructions, see your PostgreSQL documentation at http://www.postgresql.org/docs/8.3/static/backup.html and http://www.postgresql.org/docs/8.3/static/app-pgrestore.html.

1. Database names must be unique on the PostgreSQL instance, so you should 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. 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 mypgdb -c "alter database mypgdb set search_path = E'"$user"',public,sde;

4. Restore the database using the pg_restore command.

   You must run the restore command twice; the first time to restore the contents of the public schema, and the second time to restore the contents of all the other schemas in your database.

   pg_restore -U sde -n public -d mygdb mypgdb1031.dump
   
   pg_restore –U sde –d mypgdb mypgdb1031.dump

Tip:

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.