Preparing to upgrade a geodatabase in PostgreSQL

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

When you have determined that the new system works the way you expected, 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.

Note:

• Upgrades from beta versions of the software are not supported.
• If the geodatabase is part of a replica, you must upgrade both the parent and child geodatabase before synchronizing.
• 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 must restore the old database from backup.
• Once a geodatabase has been upgraded, previous versions of ArcGIS will be unable to open it since older versions of ArcGIS cannot read newer versions of the geodatabase.

You must also complete the following steps before you upgrade your geodatabase in PostgreSQL:

Steps:

1. Check the ArcGIS Resource Center to be sure that your system meets the minimum requirements.
2. Create a backup of each databases on the database cluster.
   Note:

   Backups must be made of one database at a time.

3. Upgrade the PostgreSQL database cluster.
   • On Windows, copy the Support folder from ArcSdePG on the ArcSDE installation media to your local drive and run the UPGRADE.bat file. When the upgrade finishes, you are required to restart the server for the changes to take effect.
   • On Red Hat Linux, run the postgresql-8.3.8-1PGDG.rhel5, postgresql-server-8.3.8-1PGDG.rhel5, and postgresql-libs-8.3.8-1PGDG.rhel5 RPMs to upgrade. Also be sure you have at least the 2.6.23 version of libXML2 installed. This file is usually installed with the Red Hat Linux operating system. If you do not have it or do not have at least version 2.6.23, download and install libXML2 2.6.23 or higher.
   • On SUSE Linux, see the PostgreSQL documentation for information on installing from source to upgrade the PostgreSQL database cluster.
4. Delete the databases from the database cluster.
5. Re-create the databases on the database cluster.
   Note:

   Be sure to give the databases the same name and owner that they had before and set the search_path variable for the database to "$user", public, and sde.

6. Restore the databases, one at a time, to the updated PostgreSQL database cluster using the pg_restore command from an MS_DOS (Windows) or shell (Linux) command prompt.

   You must run the restore command twice for each database; the first time to restore the contents of the public schema, the second time to restore the contents of all the other schemas.

   pg_restore -U postgres -n public -d pgsqldb1 pgsqldb1_bu.dump 
   
   pg_restore -U postgres -d pgsqldb1 pgsqldb1_bu.dump

7. Now that your PostgreSQL database cluster is upgraded, create second backups of all the databases prior to upgrading the geodatabase.
8. Remove any custom functionality you may have added to the ArcSDE geodatabase system tables outside ArcGIS such as triggers or additional indexes.

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

9. Grant the ArcSDE administrator (sde) superuser permissions in the database.

   This is required to upgrade a geodatabase.

10. Install the current release of the ArcGIS client (ArcGIS Desktop, ArcGIS Engine runtime, or ArcGIS Server Standard or Advanced) on a computer that can directly connect to the geodatabase to perform the upgrade.
11. Make sure that there are no users connected to the geodatabase.

    You can use the sdemon command to check this. See Displaying connected sessions for instructions.

12. Shut down any ArcSDE services that are running using the sdemon –o shutdown command. Or if the service is running on a Windows server, you can stop the service on the Windows Services interface instead of using the sdemon command.

    See the command syntax in the ArcSDE Administration Command Reference for more information on the sdemon command.

13. Uninstall the old release of ArcSDE. See either Uninstalling ArcSDE from Windows or Uninstalling ArcSDE from Linux or UNIX for instructions.
14. In Windows, you are prompted to delete the ArcSDE services during the uninstallation process if any are running. Click Yes to delete the service if you are installing a new release of ArcSDE. If you are installing a service pack or patch, you can click No and reuse the service unless the service pack or patch instructions specify that you must re-create the service. (For example, if the service pack or patch fixes something in the service functionality, you most likely need to re-create it.)
15. Install the new release of ArcSDE.

    On Windows operating systems, do not run the Post Installation wizard. The Post Installation wizard is for new installations only.

16. Copy the st_geometry file from the new bin directory in SDEHOME to the PostgreSQL installation directory. If you plan to use the ST_Raster type, also copy the libst_raster_pg library.
    • In Linux, log in as the root user and use FTP to transfer the st_geometry.so file (and libst_raster_pg.so file if using ST_Raster) to the lib/pgsql (32 bit) or lib64/pgsql (64 bit) directory under the PostgreSQL installation directory.
    • In Windows, copy the st_geometry.dll file (and the libst_raster_pg.dll file if using ST_Raster) to the lib folder of the PostgreSQL installation directory.

The geodatabase is now ready to be upgraded. You can use the Upgrade Geodatabase tool in ArcGIS Desktop or a Python script run on the ArcGIS client computer.