Back to Top

Create a geodatabase in a PostgreSQL cloud-based database service

In this topic

  1. Complete the prerequisites
  2. Create a geodatabase in the database service instance

ArcGIS supports several cloud database-as-a-service offerings that are based on PostgreSQL. You can create an enterprise geodatabase in most of the PostgreSQL cloud-based database services that Esri supports.

When you create a geodatabase from ArcGIS AllSource 3.4, the geodatabase version is 11.4.0.x.

Complete the prerequisites

Before you can create a geodatabase, you must create the database instance in your cloud account, configure it to receive connections, create an sde user and schema, and grant the sde user privileges to create a geodatabase.

Follow the instructions provided by your cloud provider to complete these steps.

You must also copy the ArcGIS Server keycode authorization file to the cloud machine where you install the ArcGIS client that you'll use to create the geodatabase.

Complete the following prerequisite steps before creating a geodatabase:

  1. Confirm that the ArcGIS versions you use support the cloud-based database service type and version you want to use.
  2. Create the database service instance in your chosen cloud platform.
  3. Create a user login named sde for the database instance.
  4. Grant the sde user privileges required to create a geodatabase for the type of database service you're using.
  5. Create a schema named sde in the database instance and grant the sde user authority on the schema.
  6. Install the ArcGIS client—ArcGIS AllSource or ArcGIS Server—that you'll use to create the geodatabase.

    Install the ArcGIS client in the same cloud platform and region where the database service is running.

  7. Ensure that the database instance is configured to allow connections from the ArcGIS client.
  8. From the ArcGIS client you installed in step 6, create a database connection to the database instance, connecting as the sde user.

    See Database connections in ArcGIS AllSource for examples of instance strings.

    If the sde user is a database-authenticated user, you must save the sde user's password in the database connection.

  9. Obtain an ArcGIS Server keycodes file and place it in a location you can access from the ArcGIS client you'll use to create the geodatabase.

Now you can create a geodatabase in the database instance in the cloud.

Create a geodatabase in the database service instance

If you installed ArcGIS AllSource in the same cloud region as the database service, you can run the Enable Enterprise Geodatabase geoprocessing tool to create a geodatabase in the database instance, as described in the first section below.

Alternatively, you can run the EnableEnterpriseGeodatabase ArcPy function in a Python window or run a Python script from an ArcGIS AllSource or ArcGIS Server installation in the cloud.

Run the Enable Enterprise Geodatabase tool

Follow these steps to create a geodatabase using the Enable Enterprise Geodatabase tool in ArcGIS AllSource:

  1. Sign in to the virtual machine in the cloud environment and start ArcGIS AllSource.
  2. Open the Enable Enterprise Geodatabase tool.
  3. For the Input Database Connection parameter, provide the database connection file that you created as part of the prerequisite steps.
  4. For Authorization File, browse to and choose the keycodes file that was created when you authorized ArcGIS Server (Enterprise edition).

    This file is written to \\Program Files\ESRI\License<release#>\sysgen when you install ArcGIS Server on Windows and to /arcgis/server/framework/runtime/.wine/drive_c/Program Files/ESRI/License<release#>/sysgen when you install ArcGIS Server on Linux.

  5. Click Run.

You now have a geodatabase in the database instance.

You can find messages related to geodatabase creation in the sde_setup.log file, which is created in the directory specified for the TEMP variable on the virtual machine where the script is run. If you have problems creating a geodatabase, review this file to troubleshoot the problem.

Next, the database administrator can create login roles and matching schemas for users who will create, edit, and view data and create login roles for editors and data viewers.

Run the EnableEnterpriseGeodatabase ArcPy function

To run ArcPy functions, connect to one of the ArcGIS client machines in the cloud and run the function from a Python window.

  1. Sign in to the virtual machine in the cloud environment where ArcGIS AllSource or ArcGIS Server is installed.
  2. Access a Python window.

    See the following for information about accessing Python from ArcGIS installations:

    • Python window—Information about opening the window from ArcGIS AllSource
    • ArcGIS Server and ArcPy—Information about accessing the Python window on an ArcGIS Server installation

  3. Run the EnableEnterpriseGeodatabase ArcPy function from a Python window.

    Use the database connection file that you created and reference the ArcGIS Server authorization file you placed in an accessible location as part of the prerequisite steps.

    In the following example, the EnableEnterpriseGeodatabase function is run from an ArcGIS AllSource installation. The database connection file (postresdbaas.sde) is stored in an ArcGIS AllSource project folder (C:\proprojects\pg_proj), and the authorization file (keycodes) is stored in C:\authcodes.

    arcpy.management.EnableEnterpriseGeodatabase(r"C:\proprojects\pg_proj\postresdbaas.sde",r"C:\authcodes\keyfile")

    In the next example, the function is run from an ArcGIS Server installation on a Linux machine. The database connection file (postresdbaas.sde) is stored in /usr/connectionfiles, and the keycodes file is in the default ArcGIS Server location.

    arcpy.management.EnableEnterpriseGeodatabase("/usr/connectionfiles/postresdbaas.sde","/usr/arcgis/server/framework/runtime/.wine/drive_c/Program Files/ESRI/License/sysgen/keycodes")

You now have a geodatabase in the database instance.

You can find messages related to geodatabase creation in the sde_setup.log file, which is created in the directory specified for the TEMP variable on the virtual machine where the function is run. If you have problems creating a geodatabase, review this file to troubleshoot the problem.

Next, the database administrator can create login roles and matching schemas for users who will create, edit, and view data and create login roles for editors and data viewers.

Run a Python script from an ArcGIS client

To create the geodatabase by running a script from an ArcGIS AllSource (Desktop Standard or Desktop Advanced) or ArcGIS Server machine, you can use the script provided here.

Follow these steps to run a Python script that calls the CreateDatabaseConnection and EnableEnterpriseGeodatabase ArcPy functions to connect to the database and create a geodatabase in a PostgreSQL database service:

  1. Create a text file on the ArcGIS client machine and copy the following script into the file: 

    """
Name: enable_enterprise_gdb.py
Description: Provide connection information to an enterprise database
and enable enterprise geodatabase.
Type enable_enterprise_gdb.py -h or enable_enterprise_gdb.py --help for usage
"""

# Import system modules
import arcpy, os, optparse, sys


# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for " + arcpy.GetInstallInfo()['Version'] )

#Define help and options
parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQL_SERVER', 'ORACLE', 'POSTGRESQL', 'DB2','INFORMIX','DB2ZOS', ''], default="", help="Type of enterprise DBMS:  SQL_SERVER, ORACLE, POSTGRESQL, DB2, INFORMIX, or DB2ZOS.")
parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
parser.add_option ("--auth", dest="account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
parser.add_option ("-u", dest="User", type="string", default="", help="Geodatabase administrator user name")
parser.add_option ("-p", dest="Password", type="string", default="", help="Geodatabase  administrator password")
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
parser.add_option ("-l", dest="Authorization_file", type="string", default="", help="Full path and name of authorization file")


# Check if value entered for option
try:
	(options, args) = parser.parse_args()

	
#Check if no system arguments (options) entered
	if len(sys.argv) == 1:
		print("%s: error: %s\n" % (sys.argv[0], "No command options given"))
		parser.print_help()
		sys.exit(3)
	

	#Usage parameters for spatial database connection
	database_type = options.Database_type.upper()
	instance = options.Instance
	account_authentication = options.account_authentication.upper()
	username = options.User.lower() 
	password = options.Password	
	database = options.Database.lower()
	license = options.Authorization_file


	if( database_type ==""):	
		print(" \n%s: error: \n%s\n" % (sys.argv[0], "DBMS type (--DBMS) must be specified."))
		parser.print_help()
		sys.exit(3)		
		
	if (license == ""):
		print(" \n%s: error: \n%s\n" % (sys.argv[0], "Authorization file (-l) must be specified."))
		parser.print_help()
		sys.exit(3)

		
	# Get the current product license
	product_license=arcpy.ProductInfo()
	
	if (license == ""):
		print(" \n%s: error: %s\n" % (sys.argv[0], "Authorization file (-l) must be specified."))
		parser.print_help()
		sys.exit(3)
	
	# Checks required license level
	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
		print("\n" + product_license + " license found!" + "  Enabling enterprise geodatabase functionality requires an ArcGIS Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS Server license.")
		sys.exit("Re-authorize ArcGIS before enabling an enterprise geodatabase.")
	else:
		print("\n" + product_license + " license available!  Continuing to enable...")
		arcpy.AddMessage("+++++++++")
	
	# Local variables
	instance_temp = instance.replace("\\","_")
	instance_temp = instance_temp.replace("/","_")
	instance_temp = instance_temp.replace(":","_")
	Conn_File_NameT = instance_temp + "_" + database + "_" + username    
	
	if os.environ.get("TEMP") == None:
		temp = "c:\\temp"	
	else:
		temp = os.environ.get("TEMP")
	
	if os.environ.get("TMP") == None:
		temp = "/usr/tmp"		
	else:
		temp = os.environ.get("TMP")  
	

	Connection_File_Name = Conn_File_NameT + ".sde"
	Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"
	
	# Check for the .sde file and delete it if present
	arcpy.env.overwriteOutput=True
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)
	
	print("\nCreating Database Connection File...\n")	
	# Process: Create Database Connection File...
	# Usage:  out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true)
	arcpy.CreateDatabaseConnection_management(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=username, password=password, save_user_pass="TRUE")
	for i in range(arcpy.GetMessageCount()):

                if "000565" in arcpy.GetMessage(i):   #Check if database connection was successful

                        arcpy.AddReturnMessage(i)

                        arcpy.AddMessage("\n+++++++++")

                        arcpy.AddMessage("Exiting!!")

                        arcpy.AddMessage("+++++++++\n")

                        sys.exit(3)

                else:

                        arcpy.AddReturnMessage(i)

                        arcpy.AddMessage("+++++++++\n")
	
	# Process: Enable geodatabase...
	try:
		print("Enabling Enterprise Geodatabase...\n")
		arcpy.EnableEnterpriseGeodatabase_management(input_database=Connection_File_Name_full_path, authorization_file=license)
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
		arcpy.AddMessage("+++++++++\n")
	except:
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
			
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)
			
#Check if no value entered for option	
except SystemExit as e:
	if e.code == 2:
		parser.usage = ""
		print("\n")
		parser.print_help() 
		parser.exit(2)

  2. Save the file with a .py extension.
  3. Run the script, providing options and information specific to your instance.

    In the following examples, the enable_gdb.py file is run from a virtual machine in the same cloud region as the database service.

    In this example, the script is run from an ArcGIS Server installation on a Linux machine. It connects to a database named myauroradb in an Amazon Aurora PostgreSQL instance in the us-west-2 region (database-1-instance-1.zyxjtlpj9fer.us-west-2.rds.amazonaws.com). The keycodes file was placed in /usr/arcgis/auth on the ArcGIS Server machine.

    ./enable_gdb.py --DBMS POSTGRESQL -i database-1-instance-1.zyxjtlpj9fer.us-west-2.rds.amazonaws.com --auth DATABASE_AUTH 
-u sde -p sdeP@ss -D myauroradb -l '/usr/arcgis/auth/keycodes'

    In the next example, the script is run from a Microsoft Windows machine in Azure. It connects to a database named azurepgf in the Microsoft Azure Database for PostgreSQL (flexible server) instance my-pg-flex.postgres.database.azure.com. The keycodes file is in the default ArcGIS Server location.

    enable_gdb.py --DBMS POSTGRESQL -i my-pg-flex.postgres.database.azure.com --auth DATABASE_AUTH 
-u sde -p EsDeeEpass -D azurepgf -l '\\Program Files\ESRI\License\sysgen\keycodes'

    In the following example, the script is run from a machine where ArcGIS AllSource is installed and the keycodes file was placed in Users\MyUser\Documents\AuthFiles\keycodes. The script connects to a database (gcspostgres) in a Google Cloud SQL for PostgreSQL instance accessed through the public IP address 98.765.43.210.

    enable_gdb.py --DBMS POSTGRESQL -i 98.765.43.210 --auth DATABASE_AUTH 
-u sde -p Zpassw0rd -D gcspostgres -l '\\Users\MyUser\Documents\AuthFiles\keycodes'

    Tip:

    Type -h or --help at the command prompt for syntax help for this script.

You now have a geodatabase in the database instance.

You can find messages related to geodatabase creation in the sde_setup.log file, which is created in the directory specified for your TEMP or TMP variable on the virtual machine where the script is run. If you have problems creating a geodatabase, review this file to troubleshoot the problem.

Next, the database administrator can create login roles and matching schemas for users who will create, edit, and view data and create login roles for editors and data viewers.

Feedback on this topic?

In this topic
  1. Complete the prerequisites
  2. Create a geodatabase in the database service instance