VMware Aria Operations upgrade fails with the error “run sql db upgrade” failed

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• The OS portion of the vRealize Operations Manager upgrade completes successfully.
• When attempting to apply the vRealize Operations Manager application PAK file, the upgrade fails with this message:

Installation failed with error FailedPAK action “run sql db upgrade” failed

• The /storage/vcops/log/sqldbupgrade.log contains entries similar to:

2019-09-05 11:35:38,862 ERROR [main]
com.vmware.statsplatform.persistence.util.sql.UpgradeActionExecutorManager
execute - Upgrade failed
 java.lang.RuntimeException: java.lang.RuntimeException:
java.lang.RuntimeException: java.lang.RuntimeException:
org.postgresql.util.PSQLException: ERROR: right sibling's left-link doesn't
match: block 432 links to 6065 instead of expected 265 in index
"idx_alarm_resource_canceltime"

  at
com.vmware.statsplatform.persistence.util.sql.UpgradeActionExecutorManager
upgradeDatabase(UpgradeActionExecutorManager.java:201)

  at
com.vmware.statsplatform.persistence.util.sql.UpgradeActionExecutorManager
execute(UpgradeActionExecutorManager.java:70)

  at
com.vmware.statsplatform.persistence.util.sql.SqlDbUpgrade.main(SqlDbUpgra
e.java:49)

 Caused by: java.lang.RuntimeException: java.lang.RuntimeException:
java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: right
sibling's left-link doesn't match: block 432 links to 6065 instead of
expected 265 in index "idx_alarm_resource_canceltime"

  at
com.vmware.statsplatform.persistence.util.sql.UpgradeActionExecutorManager
executeSteps(UpgradeActionExecutorManager.java:226)

  at
com.vmware.statsplatform.persistence.util.sql.UpgradeActionExecutorManager
upgradeDatabase(UpgradeActionExecutorManager.java:197)

 

• /storage/vcops/log/sqldbupgrade.log displays the following error: FATAL: could not load server certificate file "server.crt": No such file or directory

• /storage/log/vcops/log/analytic.log displays the following error FATAL: role "vcops" does not exist

Environment

VMware Aria Operations 8.x
VMware vRealize Operations Manager 7.0.x
VMware vRealize Operations Manager 6.7.x
VMware vRealize Operations Manager 6.6.x

Cause

The issue can be caused by corruption on the disk or SEsparse.

The issue may occur during a PostgreSQL schema upgrade from version 11 to version 14.

Resolution

To resolve this issue, run a re-index on the alarms table in the postgres database.

1. Log into the vRealize Operations Manager Primary node as root vi SSH or Console.
2. Run the following command to reindex the alarm table:

su - postgres -c "/opt/vmware/vpostgres/current/bin/psql -d vcopsdb -c 'reindex table alarm'"

Note: The prompt will display REINDEX once the reindex is completed.

3. Run \q to exit the database.
4. Repeat steps 1-3 on all other vRealize Operations Manager Analytic nodes in the cluster.

With the re-index successful, take new snapshots of the vRealize Operations Manager nodes and attempt the upgrade process again. It should now complete successfully.

VMware Aria Operations 8.x

Steps to Modify Configuration and Complete PostgreSQL Upgrade

1. Revert to Previous Snapshot

Before making any changes, revert the system to the previous snapshot.

2. Modify Configuration

• Set ssl=off in the PostgreSQL configuration file.
• Location: /storage/db/vcops/vpostgres/data/postgresql.conf
• Perform this change on all master and data nodes.

3. Retry the Upgrade

4. After applying the above change, retry the PostgreSQL upgrade.

5. Confirm Upgrade Success

6. Verify that the upgrade has been completed successfully.

7. Re-enable SSL

• Once the upgrade is successful, enable SSL in the PostgreSQL configuration file.
• Set ssl=on in /storage/db/vcops/vpostgres/data/postgresql.conf on all master and data nodes.

By following these steps, the PostgreSQL upgrade should complete successfully with SSL re-enabled at the end.

Additional Information

For more information on SRsparse, see Virtual Machines running on an SEsparse snapshot may report guest data inconsistencies.
For more information on taking vRealize Operations Manager snapshots, see How to take a Snapshot of vRealize Operations Manager 6.x and later.