Restore a geodatabase to PostgreSQL—ArcGIS AllSource

Available with Standard or Advanced license.

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.

Caution:

When you restore a geodatabase or a database with ST_Geometry installed, you must restore schemas in a specific order: first the public and sde schemas and then the remaining schemas. If you do not, some feature classes may not be restored and spatial indexes will not be created on those that are restored.

For general recovery instructions, such as syntax options, see the 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.

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
```
If you are restoring to a new database cluster, sign in to psql, re-create the sde login role, and grant it superuser authority.
```
CREATE ROLE sde LOGIN 
  ENCRYPTED PASSWORD '0shallpass'
  SUPERUSER INHERIT;
```

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 editor1;

CREATE ROLE editor2 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 reader2 LOGIN
  ENCRYPTED PASSWORD 'pw.4reader2'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT readers TO reader2;

If you are restoring to a new database cluster and the geodatabase uses the ST_Geometry spatial type, place the st_geometry library in the PostgreSQL lib directory.
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.
Re-create the database.
You can use the createdb statement at the command line or a psql statement to do this.
Note:
Ensure 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
```
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;
```
Alter the search path for the new database to include the sde schema.
```
ALTER DATABASE mypgdb
  SET SEARCH_PATH="$user",public,sde;
```
At the command line, restore the public and sde schemas and their data using the pg_restore command.
Run the command as a login with superuser privileges, such as the postgres user.
Read the PostgreSQL documentation for pg_restore syntax, as it varies depending on how you created the backup and what version of PostgreSQL you use.
Finally, restore the remaining schemas and data.
When the database restoration is complete, connect to the database from ArcGIS AllSource and examine the data to confirm all data was restored.

Feedback on this topic?

Caution:

Note: