ArcGisPostgresqlEnterpriseGeodatabase

Creating an Enterprise Geodatabase with Postgresql and PostGIS can be quite complex and documentation is sort of intransparent. This page tries to summarize the required steps for a Windows machine.

Setup Postgres + PostGIS

ArcGIS 10.1 (Desktop or Server) currently only support PostgresQL 9.0.x and 9.1.x (there is also a version available at ESRI Customer Care portal [contact IT-support], but the common are fine). Installation should be straightforward. Install PostGIS 2.0 with the included StackBuilder as a post-installation step.

Create an additional login role "sde" with Superuser privileges in the PG instance (e.g. use pgAdmin3).

Install Geometry Drivers

ArcGIS Desktop/Server provide an additional driver for geometries located in e.g. C:\Program Files\ArcGIS\Server\DatabaseSupport\PostgreSQL. Currently, the PG 9.1 version is only provided within an ArcGIS Server 10.1 installation. Copy the file st_geometry.dll (as 32 or 64bit, matching your ArcGIS installation) to PostgresQL\lib.

Install Client Libraries

ArcGIS Desktop and Server need to have a the PostgresQL C client drivers available in order to connect to the PostgresQL database. These are provided within the PostgresQL installation (e.g. located in C:\Program Files\PostgreSQL\9.1\bin) or through the ESRI customer care (contact IT-support). Copy the drivers (libpq.dll, ssleay32.dll, libeay32.dll, libintl.dll for 64bit; libpq.dll, ssleay32.dll, libeay32.dll, libintl-8.dll, libiconv-2.dll for 32bit) to your ArcGIS installation's bin directory (e.g. C:\Program Files\ArcGIS\Server\bin or C:\Program Files (x86)\ArcGIS\Desktop10.1\bin), again choose the correct architecture version.

Note that the PostgresQL client drivers are only available in the specific architecture version (64 or 32bit) of your PostgresQL installation. ArcGIS Desktop might require the 32bit drivers. You could obtain these from the ESRI customer care or do a onetime installation of PostgresQL 32bit and replace it with the 64bit version after copying the drivers.

Issues

If you still face issues, try instaling PG-Agent from the Postgres StackBuilder. This seems to sometimes to fix the problem.

Execute Geoprocessing Tool

Now we are good to go with executing the Geoprocessing tool. Use the ESRI tutorial (starting with section "Create a geodatabase") or the python (stand-alone) scripts provided in the CreateEnterpriseGeodatabase_management tool documentation.

Creating an .sde Connection File

ArcGIS server manager requires an .sde conenction file while establishing the connection to an Enterprise Geodatabase. The following python script stores a connection file in the current working directory (execute e.g. with $python create_sde_connection_file.py -i localhost --DBMS=POSTGRESQL -D agsegdb -u sde -p mypwd -l "C:\Program Files\ESRI\License10.1\sysgen\keycodes").

"""
Name: create_sde_connection_file.py
Type create_sde_connection_fileb.py -h or create_sde_connection_file.py --help for usage
Author: Esri, edited by 52North
"""

# 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!" + " Not sufficient for EGDB."
        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    
    
    # the target directory for storing the .sde file
    targetDir = "."
    
    Connection_File_Name = Conn_File_NameT + ".sde"
    Connection_File_Name_full_path = targetDir + 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...
    arcpy.CreateDatabaseConnection_management(out_folder_path=targetDir,
        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...
    print "Connection file created: " + 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)