Using a backup file to move a geodatabase in PostgreSQL to an ArcGIS Server on Amazon Web Services site

You can move an existing enterprise geodatabase for PostgreSQL from a local server to an ArcGIS Server on Amazon Web Services instance or from one ArcGIS Server on Amazon Web Services instance to another using a backup file.

To do this, create a backup of the source geodatabase, transfer the backup file to the target ArcGIS Server on Amazon Web Services instance, create a database and login roles in the target PostgreSQL database cluster, then restore the database.

Checking for custom variable settings

Any custom settings you had on the source database cluster that you want to retain must be added to the new database cluster. Configuration settings are stored in the postgresql.conf file. Make a copy of this file and move it to the target database cluster on your ArcGIS Server on Amazon Web Services instance.

The postgresql.conf file on the ArcGIS Server on Amazon Web Services instance can be found at /data on the mounted drive.

Creating a backup of the source geodatabase

You can create a backup of the database to transfer the data files to an ArcGIS Server on Amazon Web Services instance.

Use the PostgreSQL pg_dump application to create a dump file.

Steps:

1. Connect to the server where the source geodatabase is stored.
2. Execute the pg_dump command at a shell command prompt to create a backup of the database.

   pg_dump -U postgres -F c > /data/spdbbu11012.dump
   

   See the PostgreSQL documentation at http://www.postgresql.org/docs/9.0/static/app-pgdump.html for more information on the pg_dump application.

Moving the backup file to the destination ArcGIS Server on Amazon Web Services instance

There are several ways to transfer the dump file to the target instance. See Strategies for data transfer to Amazon for different options for moving data. You can use the same method to move the postgresql.conf file, if necessary.

Be sure to place the dump file on the Elastic Block Store (EBS) volume.

Once the dump file is on the target ArcGIS Server on Amazon Web Services instance, prepare the PostgreSQL database cluster.

Preparing the target PostgreSQL database cluster to restore

You must have a database to which you will restore the dump file. Also, any login roles that own data in the source database must exist in the target database cluster.

Placing the configuration file

If you are using a customized postgresql.conf file, make a backup copy of the default postgresql.conf file on the target instance and place the customized file in /data on the mounted drive.

Creating an empty database

The database you create must have the same name as the database on the source PostgreSQL database cluster for which you created a dump file. Database names must be unique within a database cluster. That means if you are moving databases from one ArcGIS Server on Amazon Web Services to another, one of the following must be true:

• Your source database cannot be one of the default databases (egdb or geodata).

  or

• You must delete the default database of the same name from the target PostgreSQL database cluster before you can restore the transferred backup file.

Steps:

1. Log in to the target ArcGIS Server on Amazon Web Services instance as the root user.
2. Open a command shell and log in to psql as the sde user.
3. Create a database into which you will restore the dump file.

   Keep the following information in mind:

   • The name, owner, and encoding of the target database must be the same as those of the source database.
   • If you used a nondefault database template for the source database, such as a PostGIS database template, use that template for the target database.
   • You can store the new database in an existing tablespace or create a new tablespace specifically for this database. If you want to use a new tablespace, you must create it before you create the database.

   CREATE DATABASE <dbname>
     WITH OWNER = sde
          TEMPLATE = template0
          ENCODING = '<encoding of db>'
          TABLESPACE = 
          LC_COLLATE = ''
          LC_CTYPE = ''
          CONNECTION LIMIT = -1;
   

Creating login and group roles

The target database cluster must contain login roles for each user who owns data in the source database.

If you are moving a database from one ArcGIS Server on Amazon Web Services instance to another and you are using the default login roles, you do not need to create new roles in the target database cluster. However, if data in the source database is owned by nondefault login roles, you must create login roles with the same names in the target PostgreSQL database cluster. Similarly, if you want to use group logins in the target database, you must re-create those and grant them to the login roles.

See the PostgreSQL documentation for information on creating group and login roles and adding logins to groups.

Restoring the database

Use the PostgreSQL pg_restore application to restore the database.

Caution:

You must run the pg_restore command twice: once to restore the public schema, which contains the sde_spatial_references system table, and a second time to restore the rest of the data. If you do not do this, no spatial data will be restored.

Steps:

1. Connect to the destination ArcGIS Server on Amazon Web Services instance (the one to which you moved the dump file).
2. Open a command shell and log in to psql as the sde user.
3. Execute the pg_restore command at a command shell prompt to restore the public schema in the database.

   The sde user must be a superuser in PostgreSQL to execute the pg_restore command.

   pg_restore -U sde -n public -d spdb /data/spdbbu11012.dump
   Password:
   

4. Execute the pg_restore command a second time to restore the entire database.

   pg_restore -U sde -d spdb /data/spdbbu11012.dump
   Password:
   

   See the PostgreSQL documentation at http://www.postgresql.org/docs/9.0/static/app-pgrestore.html for more information on the pg_restore application.

After the database is restored on the target PostgreSQL database cluster, check to be sure the schemas and tables you were expecting are present in the new database. To do this, query the PostgreSQL catalog views that store this information. For example, you could query the pg_tables catalog view to see all the tables in the database and the schema in which they are stored.

Setting variables on the database

Now you will use the custom database variable information you gathered earlier to set variables on the new database.

These steps instruct you on how to add and set the search_path variable. The same basic steps are used to set custom values for other database variables.

Steps:

1. Connect to the new database from psql as the sde user.
2. From the psql prompt, set the search path for the database to include the sde schema.

   SET search_path TO "$user",public,sde;