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 com.zoho.postgres.migrate.DumpMigration$DefaultMigrationHandler.migrateSection(DumpMigration.
(OR)
- Feb 15, 2024 6:58:15 PM [com.zoho.postgres.migrate.PostgreSQLMigration] [SEVERE] : Failure Reason :: {0}com.zoho.postgres.exception.MigrationFailureException: Migration failed for DATA
- at com.zoho.postgres.migrate.DumpMigration$DefaultMigrationHandler.migrateSection(DumpMigration.java:918)
(OR)
- Feb 8, 2024 10:06:20 PM [com.zoho.postgres.migrate.DumpMigration] [SEVERE] : Problem during upgrade.com.zoho.postgres.exception.MigrationFailureException: Restoration failed for PRE_DATA
(OR)
- 22-feb-2024 23:53:08 [com.zoho.postgres.migrate.PostgreSQLMigration] [INFORMACIÓN] : Migration Result : FAILURE
- 22-feb-2024 23:53:08 [com.zoho.postgres.migrate.PostgreSQLMigration] [GRAVE] : Failure Reason :: {0}com.zoho.postgres.exception.MigrationFailureException: Restoration failed for POST_DATA
(OR)
- Jan 25, 2024 11:37:35 PM [com.zoho.postgres.migrate.DumpMigration] [SEVERE] : Problem during upgrade.java.lang.RuntimeException: PostgreSQL upgrade sanity failed.
- at com.zoho.postgres.migrate.DumpMigration.doMajorUpgrade(DumpMigration.java:310)
During some of the upgrades that includes postgres data migration, you may come across this error when checking the logs. The above trace is printed every time, when the upgrade fails at the PostgreSQL migration layer, which is more generic. We need to check the absolute cause of the failure and follow the respective workarounds mentioned in the below article to resolve the upgrade issue. We have sorted out the previously encountered cases and we regularly update in this same article, if in case any new issue.
How to find the exact cause?
- Search for a keyword ERROR: which will be located above the failure message.
- check the all the files that is generated in that upgrade attempt such as updatemgrlog0.txt , updatemgrlog0.txt.1 & updatemgrlog0.txt.2 if exists.
If this issue persist even after following below instructions, kindly report it to our support team with the below files.
- <product_installation_location>\logs
- <product_installation_location>\pgsql\data\postgresql.conf
- <product_installation_location>\pgsql\data\pg_hba.conf
- <product_installation_location>\pgsql\ext_conf
Case 1 : Presence of ctrlZ characters
- ERROR: missing data for column "columnoldvalue"
- ERROR: extra data after last expected column
- \n Invalid Command
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 ctrlz characters automatically.
Refer this article for this issue cause and resolution.
Case 2 : Privilege changes of Removed functions
- ERROR: function pg_catalog.gist_box_compress(internal) does not exist
Refer this article for this issue cause and resolution.
Case 3 : corruption
- ERROR: could not create unique index "requestnotificationtodesc_pk"
- DETALLE: Key (notificationid)=(293933) is duplicated.
(OR)
- This probably means that some data is corrupted and you will have to use the last backup for recovery.
- 2024-01-17 17:18:41 IST [0] : [9480] : [3]LOG: database system was not properly shut down; automatic recovery in progress
- 2024-01-17 17:18:41 IST [0] : [9480] : [4]LOG: redo starts at 1F/4A16F360
(OR)
- 2024-01-24 12:54:45 IST [0] : [12428] : [2]LOG: invalid primary checkpoint record
- 2024-01-24 12:54:45 IST [0] : [12428] : [3]LOG: invalid secondary checkpoint record
- 2024-01-24 12:54:45 IST [0] : [12428] : [4]PANIC: could not locate a valid checkpoint record
cause :
- Accidental mistakes during database administration tasks, absence of maintenance for the postgres database, postgresql & operating system bugs and server crashes are the commonly known causes for corruption. When the database is affected by corruption, we may face this errors during migration.
- This index corruption can occur for primary key, unique key as well as for foreign key.
resolution :
- In case of this issue, the ticket should be moved to G3 - corruption Team.
Case 4 : Database Inconsistency
- 23-Feb-2024 14:12:04 [com.zoho.postgres.migrate.DumpMigration] [WARNING] : Error :::: ERROR: insert or update on table "workorderhistorydiff" violates foreign key constraint "workorderhistorydiff_fk1"
- 23-Feb-2024 14:12:04 [com.zoho.postgres.migrate.DumpMigration] [INFO] : ERROR: insert or update on table "workorderhistorydiff" violates foreign key constraint "workorderhistorydiff_fk1"
- 23-Feb-2024 14:12:04 [com.zoho.postgres.migrate.DumpMigration] [INFO] : DETAIL: Key (historyid)=(581101) is not present in table "workorderhistory".
cause :
- In some of the cases, the data inserted in one table, can reference to another table which doesn't exist. This may occur due to insertion errors. such cases, cause inconsistencies in database.
resolution :
- This will be handled by @hariharamPM of CART Team.
Case 5 : Absence of Maintenance
- Jan 25, 2024 11:37:35 PM [com.zoho.postgres.migrate.PostgreSQLSanity] [SEVERE] : Sanity failed for tables :: [adsadsyncaudit, errorlog]
- Jan 25, 2024 11:37:35 PM [com.zoho.postgres.migrate.DumpMigration] [SEVERE] : Problem during upgrade.java.lang.RuntimeException: PostgreSQL upgrade sanity failed.
cause :
- After migrating the data, a sanity check will be conducted to check whether the row count before the migration is same after migration. Due to some index issues, in some cases the row count may not be equal which results in Sanity Check failure.
resolution :
- Try to login as postgres user in psql console : refer
- Execute the query : REINDEX DATABASE SERVICEDESK
- exit the psql console : \q
- Now try the upgrade.
New to ADSelfService Plus?
Related Articles
Postgres Database startup failures with InitPgsql error trace
[com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer] [SEVERE] : Please login to the system as <machine_name>\Administrator and execute initPgsql.bat script with administrator privileges. If issue is not yet solved, please contact support ...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 ...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, |, ...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: ...Upgrade failure when migrating privileges for removed functions
LOG Trace : 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 at ...