ArcGIS Server on Amazon EC2 - Using a backup file to move a geodatabase to an EGDB instance

You can move an existing ArcSDE geodatabase for PostgreSQL from one enterprise geodatabase (EGDB) instance to another or from a local server to an EGDB instance.

To do this, create a backup of the source geodatabase, transfer the backup file to the target EGDB instance, create a database and login roles in the target PostgreSQL database cluster, then restore the database.

Checking for custom variable settings

When you move the database contents to a new database cluster, you create an empty database to which you will restore the dump file. Therefore, any custom settings you had on the source database must be added to the new database before you restore.

For example, ArcGIS applications require the search_path variable for the database be set to include the sde schema. You must add this and any other variables that are set on the source database to the new database. To do this, you must know what the settings are on the source database.

You can check for these settings in pgAdmin III.

Steps:

Connect to the source database cluster using pgAdmin III.
Expand the Databases node in the Object browser window.
Right-click the source database and click Properties.
The Database dialog box opens.
Click the Variables tab to see what custom database variables have been set.
Make a note of these variables and their settings. You will use this information to re-create these variables on the new database.

Creating a backup of the source geodatabase

You can create a backup of the database to transfer the data files to an EGDB instance.

Use the PostgreSQL pg_dump application to create a dump file.

Steps:

Connect to the server where the source geodatabase is stored.
Execute the pg_dump command at an MS-DOS prompt to create a backup of the database.
```
pg_dump -U postgres -F c > d:\backups\spdb.dump
```
See the PostgreSQL documentation at http://www.postgresql.org/docs/8.3/static/app-pgdump.html for more information on the pg_dump application.

Moving the backup file to the EGDB instance

There are several ways to transfer the dump file to the target EGDB instance. See Strategies for data transfer to Amazon for different options for moving data.

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

Once the dump file is on the target EGDB 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.

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 from one EGDB instance to another, either

Your source database cannot be the default sdegdb database.
or
You must delete the default sdegdb database from the target PostgreSQL database cluster before you can restore the transferred backup file.

Steps:

Start pgAdmin III (Start > All Programs > PostgreSQL 8.3 > pgAdmin III).
Log in to the target PostgreSQL database cluster as the postgres super user.
Expand the database cluster in the Object browser window.
Right-click Databases and click New Database.
The New Database dialog box opens.
Provide the information necessary to create your target database. 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, click Cancel on the New Database dialog box and create the tablespace on the EBS drive. Then reopen the New Database dialog box to create your target database.
The following is an example of information provided to create a database:
Click OK to create the database.
The new database appears in the list of databases in the Object browser window in pgAdmin III.

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:

Right-click the database in the Object browser window and click Properties.
Click the Variables tab.
Choose search_path from the Variable Name drop-down list.
Type "$user",public,sde in the Variable Value text box.
Click Add/Change.
The Variables list should look like this:

Add any other variables you require by choosing the variable from the Variable Name drop-down list, setting the value, and clicking Add/Change.

Creating login 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 EGDB 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.

Steps:

Right-click Login Roles in the Object browser window.
Click New Login Role.
Provide the information necessary to create the login role. Keep the following information in mind:
- The role names must match those in the source database.
- At a minimum, the roles must have permission to create database objects.
Click OK to create the login role.
Repeat steps 1 through 4 for each login role that owns data in the source database.

Restoring the database

Use the PostgreSQL pg_restore application to restore the database.

Steps:

Connect to the server where the source geodatabase is stored.

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.

Execute the pg_restore command at an MS-DOS prompt to restore the public schema in the database.
The user executing the pg_restore command must be a super user in PostgreSQL.
```
pg_restore -U sde -n public -d spdb d:\backups\spdb.dump
Password:
```
Execute the pg_restore command a second time to restore the entire database.
```
pg_restore -U sde -d spdb d:\backups\spdb.dump
Password:
```
See the PostgreSQL documentation at http://www.postgresql.org/docs/8.3/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, connect to the database cluster in pgAdmin III, right-click the new database, click Refresh, then expand the database and explore the schemas and their contents.

1/30/2013