Scripting database and geodatabase creation in PostgreSQL

You can copy and alter one of the example scripts provided. One creates both a database and geodatabase in a PostgreSQL database cluster. The other creates a geodatabase in an existing, preconfigured PostgreSQL database.

There are additional tasks you must complete before running the script. Those tasks and the script itself are described in the following steps:

Steps:
  1. Install and configure PostgreSQL on your server.
  2. Alter the pg_hba.conf file to allow connection to the database cluster.
  3. Create a directory and tablespace to store your PostgreSQL database.
  4. If you will be running the Python script from a different computer than the server where PostgreSQL is installed, install the PostgreSQL libpq client on the computer from which you will run the Python script.

    The PostgreSQL client files can be downloaded from the Esri Customer Care Portal. If you will be running the script from an ArcGIS 32-bit client, download the 32-bit PostgreSQL client files. If you will be running the script from ArcGIS for Server, download the 64-bit PostgreSQL client files. See Setting up a connection to PostgreSQL for more information.

  5. Copy one of these example scripts to the Python window in ArcGIS for Desktop or to any Python IDE, such as PythonWin or WING, on a computer where ArcGIS for Desktop (Standard or Advanced), ArcGIS for Server Enterprise (Standard or Advanced), or ArcGIS Engine with the Geodatabase Update extension is installed.

    This example creates an sde user and schema, grants the sde user privileges to create a geodatabase, specifies a tablespace to store the database, creates a database, then creates the geodatabase.

    """
    Name: create_enterprise_gdb.py
    Description: Provide connection information to a DBMS instance and create an enterprise geodatabase.
    Type  create_enterprise_gdb.py -h or create_enterprise_gdb.py --help for usage
    Author: Esri
    """
    
    # Import system modules
    import arcpy, os, optparse, sys
    
    
    # Define usage and version
    parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for 10.1 release")
    
    #Define help and options
    parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, or POSTGRESQL.")                   
    parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
    parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
    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="Dbms_admin", type="string", default="", help="DBMS administrator user")
    parser.add_option ("-P", dest="Dbms_admin_pwd", type="string", default="", help="DBMS administrator password")
    parser.add_option ("--schema", dest="Schema_type", type="choice", choices=['SDE_SCHEMA', 'DBO_SCHEMA'], default="SDE_SCHEMA", help="Schema Type for SQL Server geodatabase, SDE or DBO. Default=SDE_SCHEMA")
    parser.add_option ("-u", dest="Gdb_admin", type="string", default="", help="Geodatabase administrator user name")
    parser.add_option ("-p", dest="Gdb_admin_pwd", type="string", default="", help="Geodatabase administrator password")
    parser.add_option ("-t", dest="Tablespace", type="string", default="", help="Tablespace name")
    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
    	database = options.Database.lower()	
    	account_authentication = options.Account_authentication.upper()
    	dbms_admin = options.Dbms_admin
    	dbms_admin_pwd = options.Dbms_admin_pwd
    	schema_type = options.Schema_type.upper()
    	gdb_admin = options.Gdb_admin
    	gdb_admin_pwd = options.Gdb_admin_pwd	
    	tablespace = options.Tablespace
    	license = options.Authorization_file
    	
    	
    	if (database_type == "SQLSERVER"):
    		database_type = "SQL_SERVER"
    	
    	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)			
    	
    	if(database_type == "SQL_SERVER"):
    		if(schema_type == "SDE_SCHEMA" and gdb_admin.lower() != "sde"):
    			print "\n%s: error: %s\n" % (sys.argv[0], "To create SDE schema on SQL Server, geodatabase administrator must be SDE.")
    			sys.exit(3)
    		if (schema_type == "DBO_SCHEMA" and gdb_admin != ""):
    			print "\nWarning: %s\n" % ("Ignoring geodatabase administrator specified when creating DBO schema...")
    		if( account_authentication == "DATABASE_AUTH" and dbms_admin == ""):
    			print "\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified with database authentication")
    			sys.exit(3)
    		if( account_authentication == "OPERATING_SYSTEM_AUTH" and dbms_admin != ""):
    			print "\nWarning: %s\n" % ("Ignoring DBMS administrator specified when using operating system authentication...")	
    	else:
    		if (schema_type == "DBO_SCHEMA"):
    			print "\nWarning: %s %s, %s\n" % ("Only SDE schema is supported on", database_type, "switching to SDE schema..." )
    			
    		if( gdb_admin.lower() == ""):
    			print "\n%s: error: %s\n" % (sys.argv[0], "Geodatabase administrator must be specified.")
    			sys.exit(3)
    
    		if( gdb_admin.lower() != "sde"):
    			if (database_type == "ORACLE"):
    				print "\nGeodatabase admin user is not SDE, creating user schema geodatabase on Oracle...\n"
    				sys.exit(3)
    			else:
    				print "\n%s: error: %s for %s.\n" % (sys.argv[0], "Geodatabase administrator must be SDE", database_type)
    				sys.exit(3)
    			
    		if( dbms_admin == ""):
    			print "\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified!")
    			sys.exit(3)
    
    		if (account_authentication == "OPERATING_SYSTEM_AUTH"):
    			print "Warning: %s %s, %s\n" % ("Only database authentication is supported on", database_type, "switching to database authentication..." )
    
    	# Get the current product license
    	product_license=arcpy.ProductInfo()
    	
    	
    	# Checks required license level
    	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
    		print "\n" + product_license + " license found!" + " Creating an enterprise geodatabase requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license."
    		sys.exit("Re-authorize ArcGIS before creating enterprise geodatabase.")
    	else:
    		print "\n" + product_license + " license available!  Continuing to create..."
    		arcpy.AddMessage("+++++++++")
    	
    	
    	try:
    		print "Creating enterprise geodatabase...\n"
    		arcpy.CreateEnterpriseGeodatabase_management(database_platform=database_type,instance_name=instance, database_name=database, account_authentication=account_authentication, database_admin=dbms_admin, database_admin_password=dbms_admin_pwd, sde_schema=schema_type, gdb_admin_name=gdb_admin, gdb_admin_password=gdb_admin_pwd, tablespace_name=tablespace, 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)
    			
    #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)
    

    If you prefer to create your database manually, for example, if you want to use a database that has PostGIS installed, copy this example script to enable geodatabase functionality in an existing, preconfigured PostgreSQL database.

    """
    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
    Author: Esri
    """
    
    # Import system modules
    import arcpy, os, optparse, sys
    
    
    # Define usage and version
    parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for 10.1 release")
    
    #Define help and options
    parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', 'DB2','INFORMIX','DB2ZOS', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, 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)
    
    	if (database_type == "SQLSERVER"):
    		database_type = "SQL_SERVER"
    		
    	# 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 for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for 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_workspace=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)
    
  6. Alter the script to include the information specific to your site.
  7. Run the script you copied and altered.

A log file for the geodatabase creation (GDBCreateGeodatabase<#>.log) is written to the directory specified for your TEMP or TMP variable on the computer where the script is run.

Related Topics

8/21/2013