Move a geodatabase in PostgreSQL between AWS instances

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

To do this, both the source and target PostgreSQL database clusters must be compatible for transferring databases using a pg_dump file. See PostgreSQL documentation to see if the versions you are using are compatible.

If your PostgreSQL versions are compatible, create a backup of the source geodatabase, transfer the backup file to the target ArcGIS Enterprise on Amazon Web Services instance, create a database and login roles in the target PostgreSQL database cluster, and restore the database.

Note:

This workflow does not apply to Amazon Relational Database Services for PostgreSQL.

Check 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 Enterprise on Amazon Web Services instance.

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

Create backup of source geodatabase

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

Use the PostgreSQL pg_dumpcommand to create a dump file.

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 for more information on the pg_dump command.

Move backup file to destination instance

There are several ways to transfer the dump file to the target instance. See Strategies for data transfer to Amazon Web Services 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 Enterprise on Amazon Web Services instance, prepare the PostgreSQL database cluster.

Prepare target PostgreSQL database cluster

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.

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

Create 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 Enterprise on Amazon Web Services to another, one of the following must be true:

• Your source database cannot have the same name as a database on the destination database cluster.

  or

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

1. Log in to the target EC2 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;

Create 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 Enterprise 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.

Restore database

Use the PostgreSQL pg_restore command 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.

1. Connect to the destination ArcGIS Enterprise 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 for more information on the pg_restore command.

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.

Set database variables

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.

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;