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

When you restore a geodatabase or a database with ST_Geometry installed, you must restore schemas in a specific order: first the public schema, then the sde schema, then the remaining schemas.

For general recovery instructions, such as syntax options, see your PostgreSQL documentation. Also, if you have PostGIS installed and are using geometry or geography storage, be sure to read the PostGIS 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 database cluster; therefore, if you are restoring to the same database cluster, you must drop the existing database.

   dropdb –U sde mypgdb
   

2. If you are restoring to a new database cluster, log in to psql, re-create the sde login role, and grant it superuser authority.

   CREATE ROLE sde LOGIN 
     ENCRYPTED PASSWORD '0shallpass'
     SUPERUSER INHERIT;
   

3. If you are restoring to a new database cluster, re-create the login roles for all data owners. You can also re-create editor and read-only login roles at this time, though that is not required to restore the database.

   The following example script creates data owner, editor, and reader login roles and groups, and adds the login roles to the appropriate groups.

   --Re-create dataowners group and login roles.
   CREATE ROLE dataowners
     NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   
   CREATE ROLE owner1 LOGIN
     ENCRYPTED PASSWORD 'pw.4.owner1'
     NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   GRANT dataowners TO owner1;
   
   CREATE ROLE owner2 LOGIN
     ENCRYPTED PASSWORD 'pw.4.owner2'
     NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   GRANT dataowners TO owner2;
   
   --Re-create editors group and login roles.
   CREATE ROLE editors
     NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   
   CREATE ROLE editor1 LOGIN
     ENCRYPTED PASSWORD 'pw.4editor1'
     NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   GRANT editors TO owner1;
   
   CREATE ROLE owner2 LOGIN
     ENCRYPTED PASSWORD 'pw.4editor2'
     NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   GRANT editors TO editor2;
   
   --Re-create readers group and login roles.
   CREATE ROLE readers
     NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   
   CREATE ROLE reader1 LOGIN
     ENCRYPTED PASSWORD 'pw.4reader1'
     NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   GRANT readers TO reader1;
   
   CREATE ROLE owner2 LOGIN
     ENCRYPTED PASSWORD 'pw.4reader2'
     NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   GRANT readers TO reader2;
   

4. If you are restoring to a new database cluster, be sure to place the st_geometry library in the PostgreSQL lib location. See either Create a geodatabase in PostgreSQL on Linux or Create a geodatabase in PostgreSQL on Windows for details on placing the st_geometry library.
5. Re-create the database. You can use the createdb statement at the command line or a psql statement to do this.

   Note:

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

   The following example uses the createdb command line tool to create a database named mypgdb with encoding UTF8, owned by the sde login role, and located in tablespace tblspgdb:

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

6. Log back in to psql and create an sde schema owned by the sde login role in the new database. Grant usage on the sde schema to all login roles or groups that will access the geodatabase.

   CREATE SCHEMA sde
     AUTHORIZATION sde;
   
   GRANT USAGE ON SCHEMA sde TO dataowners;
   GRANT USAGE ON SCHEMA sde TO editors;
   GRANT USAGE ON SCHEMA sde TO readers;
   

7. Alter the search path for the new database to include the sde schema.

   ALTER DATABASE mypgdb
     SET SEARCH_PATH="$user",sde,public;
   

8. At the command line, restore the public schema and data using the pg_restore command. Run the command as a login with superuser privileges, such as the postgres or sde user.

   Caution:

   You must restore the public schema and its contents first. If you do not, some of your spatial data will not restore. Second, restore the sde schema. If you do not do this, spatial indexes will not be re-created on the feature classes.

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

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

9. Next, restore the contents of the sde schema.

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

10. Finally, restore the remaining schemas and data.

    pg_restore –U sde –d mypgdb mypgdb1031.dump
    

11. Once the database restoration is complete, test the database by connecting to it from ArcGIS Desktop and examining the data.