Upgrade failure when migrating privileges for removed functions

Upgrade failure when migrating privileges for removed functions


LOG Trace :
  1. 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
  2. at com.zoho.postgres.migrate.DumpMigration$DefaultMigrationHandler.migrateSection(DumpMigration.java:817

  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
(OR)
  1. 12.03.2024 17:18:48  [com.zoho.postgres.migrate.DumpMigration]  [WARNUNG] : Error :::: psql:E:/ManageEngine/ServiceDesk/Patch/AdventNet_ManageEngine_ServiceDesk_Plus-14700-SP-0/SERVICEDESK/PreInstall/pg_migrate/_pgupgrade/PRE_DATA.sql:394: ERROR:  function pg_catalog.citextin(cstring) does not exist
  2. 12.03.2024 17:18:48  [com.zoho.postgres.migrate.DumpMigration]  [INFORMATION] : psql:E:/ManageEngine/ServiceDesk/Patch/AdventNet_ManageEngine_ServiceDesk_Plus-14700-SP-0/SERVICEDESK/PreInstall/pg_migrate/_pgupgrade/PRE_DATA.sql:394: ERROR:  function pg_catalog.citextin(cstring) does not exist
  3. 12.03.2024 17:18:48  [com.zoho.postgres.migrate.DumpMigration]  [WARNUNG] : Error :::: psql:E:/ManageEngine/ServiceDesk/Patch/AdventNet_ManageEngine_ServiceDesk_Plus-14700-SP-0/SERVICEDESK/PreInstall/pg_migrate/_pgupgrade/PRE_DATA.sql:401: ERROR:  function pg_catalog.citextout(citext) does not exist
  4. 12.03.2024 17:18:48  [com.zoho.postgres.migrate.DumpMigration]  [INFORMATION] : psql:E:/ManageEngine/ServiceDesk/Patch/AdventNet_ManageEngine_ServiceDesk_Plus-14700-SP-0/SERVICEDESK/PreInstall/pg_migrate/_pgupgrade/PRE_DATA.sql:401: ERROR:  function pg_catalog.citextout(citext) does not exist
  5. 12.03.2024 17:18:48  [com.zoho.postgres.migrate.DumpMigration]  [WARNUNG] : Error :::: psql:E:/ManageEngine/ServiceDesk/Patch/AdventNet_ManageEngine_ServiceDesk_Plus-14700-SP-0/SERVICEDESK/PreInstall/pg_migrate/_pgupgrade/PRE_DATA.sql:408: ERROR:  function pg_catalog.citextrecv(internal) does not exist
  6. 12.03.2024 17:18:48  [com.zoho.postgres.migrate.DumpMigration]  [INFORMATION] : psql:E:/ManageEngine/ServiceDesk/Patch/AdventNet_ManageEngine_ServiceDesk_Plus-14700-SP-0/SERVICEDESK/PreInstall/pg_migrate/_pgupgrade/PRE_DATA.sql:408: ERROR:  function pg_catalog.citextrecv(internal) does not exist
  7. 12.03.2024 17:18:48  [com.zoho.postgres.migrate.DumpMigration]  [WARNUNG] : Error :::: psql:E:/ManageEngine/ServiceDesk/Patch/AdventNet_ManageEngine_ServiceDesk_Plus-14700-SP-0/SERVICEDESK/PreInstall/pg_migrate/_pgupgrade/PRE_DATA.sql:415: ERROR:  function pg_catalog.citextsend(citext) does not exist
Cause :
  1. This issue (SD-119233) may occur when migrating the patch which involves postgres database migration such as version 14300 & 14610.
  2. During database migration, database schema, functions and all other data are migrated. 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 or modified in latest version, then action of providing privileges will be failure. This is what happening above.
Workaround :
  1. We can remove the privilege changes for the removed or modified 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;
            
            If the upgrade fails again, Contact the support team with latest logs.
We do not recommend executing Queries that impacts postgres database functions and other core objects. Avoid using queries that involve "GRANT ALL ", "REVOKE ALL", Update over pg_catalog or information_schemas

                    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, 127.0.0.1, -r, -w, |, ...
                      • Upgrade Failure due to the presence of CtrlZ Failure

                        This issue (SD-118352) in fixed in version 14710. Post this release, these characters are handled in the migration itself. If you are planning to migrate from version less than 14610 to 14610, then you can directly migrate to 14710, to handle this ...
                      • Postgres Upgrade failures and its exact causes.

                        Mar 13, 2024 11:55:53 AM [com.zoho.postgres.migrate.DumpMigration] [SEVERE] : Problem during upgrade.com.zoho.postgres.exception.MigrationFailureException: Migration failed for ALL at ...
                      • 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 upgrading 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 ...