Upgrade failure when migrating privileges for removed functions

Upgrade failure when migrating privileges for removed functions

LOG Trace :

  1. 03.08.2023 12:50:35  [com.zoho.postgres.migrate.DumpMigration]  [WARNUNG] : Error :::: psql:E:/ManageEngine/ServiceDesk/_pgupgrade/PRE_DATA.sql:15493: ERROR:  function pg_catalog.range_gist_compress(internal) does not exist
  2. 03.08.2023 12:50:35  [com.zoho.postgres.migrate.DumpMigration]  [INFORMATION] : GRANT
  3. 03.08.2023 12:50:35  [com.zoho.postgres.migrate.DumpMigration]  [WARNUNG] : Error :::: psql:E:/ManageEngine/ServiceDesk/_pgupgrade/PRE_DATA.sql:15507: ERROR:  function pg_catalog.range_gist_decompress(internal) does not exist
  4. 03.08.2023 12:50:35  [com.zoho.postgres.migrate.DumpMigration]  [WARNUNG] : Error :::: psql:E:/ManageEngine/ServiceDesk/_pgupgrade/PRE_DATA.sql:15514: ERROR:  function pg_catalog.range_gist_fetch(internal) does not exist
  5. .
  6. .
  7. 03.08.2023 12:50:36  [com.zoho.postgres.migrate.DumpMigration]  [INFORMATION] : Cleaning up temp file E:\ManageEngine\ServiceDesk\.\temp_42f5c07c-58da-44f0-bcc6-8b68c53ddc72.bat
  8. 03.08.2023 12:50:36  [com.zoho.postgres.migrate.DumpMigration]  [SCHWERWIEGEND] : Problem during upgrade.com.zoho.postgres.exception.MigrationFailureException: Restoration failed for PRE_DATA
  9. at com.zoho.postgres.migrate.DumpMigration$DefaultMigrationHandler.migrateSection(DumpMigration.java:817

Cause :

  1. This issue may occur when migrating to 14300 or 7000.
  2. Because, in this version postgres is upgraded from 10.21 to 11.17. Due to this database schema, functions and all other data are migrated from postgres 10.21 to 11.17. For every postgres major version, there may be some functions added or removed. When migrating the schema changes & functions, privileges for each function will be provided in the latest version such that, privileges given to the same function in the older version. 
  3. If there is any alteration in privileges for any of the postgres functions, that was removed in latest version, then action of providing privileges will be failure. This is what happending above.
Workaround :
  1. We can remove the privilege changes for the removed functions in the older build to resolve this issue.
How to? 
  1. Connect as postgres user to the database and execute the below query.
  2. After that, perform the upgrade.
update pg_catalog.pg_proc set proacl= null where proname ilike any (array ['gist_box_compress','gist_box_decompress','gist_box_fetch','gtsquery_decompress','inet_gist_decompress','range_gist_compress','range_gist_decompress','range_gist_fetch']);

If the upgrade fails again, Contact the support team with latest logs.

        New to ADManager Plus?

          New to ADSelfService Plus?

            • Related Articles

            • Migration failure for Postgres Database with a blank space in it

              Trace : \ManageEngine\ServiceDesk\Patch\AdventNet_ManageEngine_ServiceDesk_Plus-14.2.0-SP-1.0.0\SERVICEDESK\PreInstall\pg_migrate\pgsql_old\bin\pg_dumpall.exe", -U, postgres, -p, 65433, -h,, -r, -w, |, ...
            • su: cannot open session: Module is unknown

              The upgrade issue arises in several linux editions such as RHEL and CentOS when the bundled postgres database needs to be upgraded in that particular patch and the upgrade is triggered by the root user. The highlighted line in the traces below (su: ...
            • Solution for Postgres 11 Upgrade Issues

              When ServiceDesk Plus is upgraded to 14300 or later, postgres is also upgraded to 11.17. If you are using bundled postgres, data migration through pgdump and restore mechanism also happens. To ensure efficiency in Postgres 11.17, make sure your ...
            • Remote read only access to database for Postgres customers

              Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow ...
            • Migration failure after 9414- ChangeConfigurations table

              Error Trace: [SYSERR] [INFO] : org.xml.sax.SAXException: Already a row with the same set of primary keys found in this object, this row <ChangeConfigurations CONFIG_ID="1" CATEGORY="GlobaEditOnAllStages" PARAMETER="IsCOAllowed" PARAMVALUE="false" ...