Back to Top

Upgrade a geodatabase in PostgreSQL

In this topic

  1. Before you upgrade
  2. Upgrade the geodatabase

Available with Standard or Advanced license.

The goal of upgrading an enterprise geodatabase is to update the geodatabase system tables, stored procedures, types, and functions to take advantage of new functionality and bug fixes. You may also need to upgrade the database if the version you use is no longer supported for the ArcGIS clients you use.

Install a new version of ArcGIS AllSource or ArcGIS Server or apply a service pack, patch, or hot fix to an existing installation and upgrade the geodatabase.

Note:

Even if you do not upgrade the geodatabase, the database version you use may no longer be supported with the current release of ArcGIS software. If the database version is no longer supported, you may need to upgrade the geodatabase. The longer you go between upgrades, the more involved the upgrade process becomes.

When you upgrade the geodatabase using ArcGIS AllSource 3.4, the upgraded geodatabase version is 11.4.0.x.

Complete the required steps listed below prior to upgrading, then upgrade the geodatabase using the Upgrade Geodatabase geoprocessing tool or a Python script.

Before you upgrade

Plan ahead before you upgrade any enterprise system, including a geodatabase. Test the new version on a development or test server to ensure that it works with all your client applications.

When you determine that the new system works the way you expect, schedule the upgrade; be sure the necessary staff are available to perform the upgrade and that they have the permissions necessary to complete their assigned tasks.

Be aware of the following:

  • Upgrades from beta or prerelease versions of the software are not supported.
  • You can upgrade directly from a 10.9.x or 11.x geodatabase if the PostgreSQL database is at a release that is supported for the current ArcGIS release.

    If the PostgreSQL database is not at a release that is supported for the current geodatabase release, you may have to upgrade the database and the geodatabase more than once before you can upgrade the geodatabase to the current release. The longer you go between geodatabase upgrades, the more upgrades you will have to do to get to the latest geodatabase release.

    For example, in the following scenario, you will have to upgrade the database and the geodatabase twice each:

    Upgrading from a much older geodatabase version requires a multiple-step upgrade of the geodatabase and database.

    For each intermediate geodatabase upgrade, follow the upgrade instructions for that geodatabase version, and test the geodatabase as you move from version to version. Similarly, follow the upgrade instructions from the database vendor for each database version, and test as you move from version to version.

  • There is no formal mechanism to downgrade a geodatabase to a previous version. If, after upgrading to a newer version, you want to downgrade the geodatabase, you can restore the database from a backup file that contains the older geodatabase version.
  • When you upgrade from an 11.2.0 geodatabase that uses the ST_Geometry spatial type, the upgrade process will drop and re-create the spatial indexes you defined on ST_Geometry columns.

The following is a checklist of steps to complete before you upgrade the geodatabase:

  1. Read ArcGIS requirements for PostgreSQL to confirm that Esri supports the PostgreSQL and ArcGIS version combination you want to use.

    If you need to upgrade the PostgreSQL database, see the PostgreSQL documentation for information on new functionality in the database management system and how to upgrade it. Remember that the upgraded databases must have the same name and owner that they had before. Also, set the search_path variable for the database to "$user", public, and sde.

  2. Create a database connection that authenticates as the sde user.
  3. Confirm that the geodatabase can be upgraded.

    To do this, install the ArcGIS client version you want to move to onto a machine. If you install ArcGIS AllSource, use the connection you created in the previous step to connect to the geodatabase and open the Database Properties dialog box to determine whether a geodatabase upgrade is possible. If you install ArcGIS Server (enterprise edition), you can use the connection file you created in the previous step and the ArcPy Describe function to determine whether the geodatabase can be upgraded, as shown in the example below.

    # Open Python.
cd /arcgis/server/tools
./python

# Create a connection to the geodatabase.
arcpy.CreateDatabaseConnection_management("/tmp/",
                                          "egdb_connection.sde",
                                          "POSTGRESQL",
                                          "mypgdbcluster",
                                          "DATABASE_AUTH",
                                          "sde",
                                          "mysdepassword",
                                          "SAVE_USERNAME",
                                          "mypgdb")

# Import ArcPy and check the geodatabase release.
import arcpy

isCurrent = arcpy.Describe('/tmp/egdb_connection.sde').currentRelease

print isCurrent

    If False is returned, you can upgrade the geodatabase. If True is returned, you do not need to upgrade the geodatabase. Do not proceed with the subsequent steps.

  4. Create a backup of the database.
  5. Remove any custom functionality you added to the geodatabase system tables outside ArcGIS, such as triggers or additional indexes.

    The upgrade procedure cannot accommodate customizations you make to the system tables. If such customizations prevent the alteration of a system table's schema, the upgrade will fail.

  6. The database administrator must grant the sde user the privileges necessary to upgrade the geodatabase.

    See Privileges for geodatabases in PostgreSQL for required privileges.

  7. Ensure that there are no connections to the geodatabase that you are upgrading.

    To see a list of who is currently connected to the geodatabase, open the Geodatabase Administration dialog box in ArcGIS AllSource.

  8. If the geodatabase uses the ST_Geometry type, complete the next step. If it does not, you can proceed with upgrading the geodatabase.

    If you're unsure whether the geodatabase uses the ST_Geometry type, connect to the database from an SQL client as the sde user or a superuser and check for the presence of ST_Geometry functions in the sde user's schema.

  9. Download the st_geometry file from My Esri, and place it in the %PostgreSQL%\lib directory (Microsoft Windows) or PostgreSQL lib directory (Linux) on the PostgreSQL server.

    You can determine the exact location of the lib directory for your PostgreSQL installation on Linux by running the pg_config utility as the postgres user. The lib location is indicated by the PGPKGLIBDIR parameter.

    Use the library appropriate to the PostgreSQL version and server operating system. Folders are labeled with the PostgreSQL library version.

    • If the PostgreSQL server is running Linux, sign in as the root user to the server and use FTP to transfer the st_geometry.so file to the PostgreSQL lib directory.
    • If the PostgreSQL server is running Windows, copy the st_geometry.dll file to the lib folder of the PostgreSQL installation directory.

You can now upgrade the geodatabase.

Upgrade the geodatabase

You can use the Upgrade Geodatabase tool in ArcGIS AllSource or a Python script run on an ArcGIS AllSource or ArcGIS Server machine to upgrade your geodatabase.

Note:

If the geodatabase contains branch versioned data, additional postupgrade steps may be required. See How Upgrade Geodatabase works for instructions.

Use the Upgrade Geodatabase tool

Open the Upgrade Geodatabase geoprocessing tool from one of the following:

  • The Geodatabase Administration toolset in the Data Management toolbox
  • The Run Upgrade button on the General tab of the Database Properties dialog box in ArcGIS AllSource

If you open the tool from Database Properties, the Input geodatabase text box is prepopulated with the geodatabase connection information.

Esri recommends that you leave the Pre-requisites check and Upgrade geodatabase options checked. That way, the tool confirms that the prerequisites to upgrade have been met before continuing with the geodatabase upgrade.

The prerequisite check detects other active connections to the geodatabase, determines whether the connecting user has sufficient privileges to upgrade the geodatabase, ensures that the database can support XML columns, ensures that all datasets can be opened, and confirms that the database and libraries are at the same release. If any prerequisites are not met, the tool terminates. You must correct any problems before you run the upgrade procedure again.

The results of this check are reported on the geoprocessing tool dialog box. If the check fails, results are also written to the GDBUpgrade.log file in the c:\Users\<user name>\AppData\Local\ESRI\<ArcGIS product> folder.

If all checks pass, the tool proceeds with the upgrade. The status for the prerequisites check and the upgrade are shown on the geoprocessing tool progress dialog box. If the upgrade fails, information is written to the GDBUpgrade.log file. Additional information is written to the sde_setup.log file in your user TEMP directory. If your user does not have a temp directory configured, the system TEMP directory is used.

Run a script

To upgrade the geodatabase using a Python script, copy one of these example scripts into a text editor. Alter any variable values to match the information at your site.

  • If you have an existing database connection file that connects as the sde user, copy the script that applies to your operating system, paste it into a text editor, alter it to use information specific to your site, save and close the file, and run the script:
    # Name: upgradesdegdb_example.py
# Description: Connect from a Windows computer 
# with an existing database connection file 
# and upgrade an enterprise geodatabase in PostgreSQL

# Import arcpy module
import arcpy
 
# Local variables:
Output_Workspace = "C:\\ArcGIS\connection_files\<Connection file>"
Default_gdb = "C:\\ArcGIS\connection_files\<Connection file>"

# Process: Upgrade Geodatabase
arcpy.UpgradeGDB_management(Default_gdb, "PREREQUISITE_CHECK", "UPGRADE")
    # Name: upgradesdegdb_example.py
# Description: Connect from a Linux computer 
# with an existing database connection file 
# and upgrade an enterprise geodatabase in PostgreSQL

# Import arcpy module
import arcpy
 
# Local variables:
Output_Workspace = "<user>/connections/<Connection_file>"
Default_gdb = "<user>/connections/<Connection_file>"

# Process: Upgrade Geodatabase
arcpy.UpgradeGDB_management(Default_gdb, "PREREQUISITE_CHECK", "UPGRADE")
  • If you do not have a database connection file on the computer from which you are scripting the upgrade, you can copy the following script and provide your site-specific information at the command line:
    """
Name: upgrade_gdb.py
Description: Provide connection information to an Enterprise geodatabase 
and upgrade the geodatabase
Type upgrade_gdb.py -h or upgrade_gdb.py --help for usage
"""

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


# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 2.0; valid for 10.1 only")

#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, or POSTGRESQL.")
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 ("--upgrade", dest="Upgrade", type="choice", choices=['TRUE', 'FALSE'], default="FALSE", help="Upgrade Options (case-sensitive):  TRUE=Perform Pre-requisite check and upgrade geodatabase, FALSE=Perform Pre-requisite check only.  Default=FALSE")                   
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")


# 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 to upgrade
	account_authentication = options.account_authentication.upper()
	username = options.User.lower() 
	password = options.Password	
	do_upgrade = options.Upgrade
	database = options.Database.lower()
	database_type = options.Database_type.upper()
	instance = options.Instance
	
	if (database_type == ""):
		print("\nDatabase type must be specified!\n")
		parser.print_help()
		sys.exit(3)
	
	if (database_type == "SQLSERVER"):
		database_type = "SQL_SERVER"
	
	# Get the current product license
	product_license=arcpy.ProductInfo()
	
	# Checks required license level to upgrade
	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
		print("\n" + product_license + " license found!" + "  Enterprise geodatabase upgrade requires an ArcGIS Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS Server license.")
		sys.exit("Re-authorize ArcGIS before upgrading.")
	else:
		print("\n" + product_license + " license available!  Continuing to upgrade...")
		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")
			
	# Check whether geodatabase needs upgrade
	isCurrent = arcpy.Describe(Connection_File_Name_full_path).currentRelease
	
	if isCurrent == True:
		print("The geodatabase is already at the current release and cannot be upgraded!")
		sys.exit("Upgrade did not run.")
	
	
	# Process: Upgrade geodatabase...
	try:
		if do_upgrade.lower() == "true":
			print("Upgrading Geodatabase...\n")
			arcpy.UpgradeGDB_management(input_workspace=Connection_File_Name_full_path, input_prerequisite_check="PREREQUISITE_CHECK", input_upgradegdb_check="UPGRADE")
			for i in range(arcpy.GetMessageCount()):
				arcpy.AddReturnMessage(i)
			arcpy.AddMessage("+++++++++\n")
	
		else:
			print("Running Pre-Requisite Check...\n")
			arcpy.UpgradeGDB_management(input_workspace=Connection_File_Name_full_path, input_prerequisite_check="PREREQUISITE_CHECK", input_upgradegdb_check="NO_UPGRADE")
			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)
    For example, if you saved the text file as gdbupgrade, the database cluster is mypgdbcluster, and the database is mypgdb, type the following at a command prompt:

    gdbupgrade --DBMS POSTGRESQL -i mypgdbcluster --auth DATABASE_AUTH -u sde -p mysdepassword --upgrade TRUE -D mypgdb

Feedback on this topic?

In this topic
  1. Before you upgrade
  2. Upgrade the geodatabase