Create a geodatabase on Amazon Relational Database Service for PostgreSQL

When you use ArcGIS Server Cloud Builder on Amazon Web Services to launch an ArcGIS Server on Amazon Web Services (AWS) instance using the Esri AMI that includes Amazon Relational Database Service (RDS) for PostgreSQL, two enterprise geodatabases are created in the RDS instance. If you want additional geodatabases—for example, if you want to customize the name or location of the geodatabases you use, or you have multiple departments that maintain their own discrete data and require their own geodatabases—you can create additional databases using pgAdmin or SQL, create sde schemas in each database, and run the Enable Enterprise Geodatabase geoprocessing tool to create a geodatabase in each database.

When you manually configure your site using the AWS Management Console, no RDS instance, sde login role, databases, or geodatabases are created. If you want to use enterprise geodatabases in an Amazon RDS for PostgreSQL instance with one of these ArcGIS Server on Amazon Web Services instances, follow these steps:

1. Create a PostgreSQL database instance and database from the Amazon RDS console.

   See the Amazon Web Services (AWS) documentation for instructions on creating a PostgreSQL database RDS instance and database. A good place to start is with this tutorial: Creating a PostgreSQL DB Instance and Connecting to a Database on a PostgreSQL DB Instance.

2. Load the PostGIS extension to the public schema of the database you created.

   CREATE EXTENSION postgis SCHEMA public;

3. Connect to your PostgreSQL RDS database instance from a SQL client. Connect as the master user.
4. From the SQL client, create an sde login role in the PostgreSQL database cluster.

   You must grant the sde login the rds_superuser role to allow it to enable geodatabase functionality in it.

   CREATE ROLE sde WITH PASSWORD 'MeyeP@$$w0rd' LOGIN;
   GRANT rds_superuser TO sde;

5. From the SQL client, create a schema named sde in the database you created, and make the sde login the owner of the sde schema.

   CREATE SCHEMA sde;
   ALTER SCHEMA sde OWNER TO sde;

   Note:

   Use all lowercase characters for the user and schema name; mixed and uppercase character names are not supported with the geodatabase.

6. Alter privileges and set the search path on the database.

   ALTER DATABASE <database> SET search_path = "$user", public, sde;
   GRANT ALL ON DATABASE <database> TO public;
   GRANT ALL ON DATABASE <database> TO sde;

7. Create a database connection from ArcGIS to the database. Connect using the sde login. You can create a database connection using one of the following methods:
   • Add Database Connection in the Catalog window in ArcMap or ArcCatalog.
   • Add a New Database Connection in the Project pane in ArcGIS Pro.
   • Run the Create Database Connection geoprocessing tool in ArcMap, ArcCatalog, or ArcGIS Pro.
   • Run a Python script that calls the Create Database Connection tool from a machine where ArcGIS for Server, ArcMap, or ArcGIS Pro is installed.

   You will use the resultant database connection file (.sde) as input in the next step.

8. Run the Enable Enterprise Geodatabase geoprocessing tool or a Python script that calls the tool to create a geodatabase in the database.

   Note:

   You cannot use the Create Enterprise Geodatabase geoprocessing tool or script to create an sde user and geodatabase in PostgreSQL RDS.

9. Use SQL to create a login role and schema (with the same name) to load data to the geodatabase.

   In this example, the user's name is dataloader.

   CREATE ROLE dataloader WITH PASSWORD 'dkey2DL' LOGIN;
   CREATE SCHEMA dataloader;
   ALTER SCHEMA dataloader OWNER TO dataloader;

   Note:

   Use all lowercase characters for the user and schema name; mixed and uppercase character names are not supported with the geodatabase.

10. Grant this login usage on the sde schema and grant the sde login usage on this new schema.

    GRANT USAGE ON SCHEMA sde TO dataloader;
    GRANT USAGE ON SCHEMA dataloader TO sde;

11. Create another database connection from ArcGIS, connecting as the login you created to load data.
12. Register your geodatabase with your ArcGIS Server instance using this new database connection file.