vCenter upgrade from 7.0 U2/U3 versions to 8.0 IA or 8.0 GA, may fail with error message "relation already exists"

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

Upgrade from 7.0 U2/U3 version to 8.x, may fail with the following error messages during the data import phase on the target VCenter in /var/log/vmware/vpxd/vcdb_import.log file:

Context:
SQL statement "ALTER TABLE vsan_historical_health_2022_10_16 ADD CONSTRAINT vsan_historical_health_2022__cluster_id_group_id_test_id__key13
unique (cluster_id, group_id, test_id, creation_time)"
PL/pgSQL function create_constraint_if_not_exists(text,text,text) line 15 at EXECUTE
psql.bin:/storage/seat/cis-export-folder/vcdb/create_constr.sql:28: ERROR: relation "vsan_historical_health_2022__cluster_id_group_id_test_id__key16" already exists

While the name of the table/constraint may vary it is connected with the vsan_historical_health table.

• The issue can be verified by running these queries on the database.

Select i.relname idxname, t.relname tablename, idx.indisunique
from pg_index idx
join pg_class i on (idx.indexrelid = i.oid)
join pg_class t on (t.oid = idx.indrelid)
where t.relname like 'vsan_historical_health%'
and idx.indisunique = true;

 

Select t.relname rablename, con.conname, con.contype
from pg_constraint con
join pg_class t on (t.oid = con.conrelid)
where t.relname like 'vsan_historical_health%';

• Both of them should return the unique index and constraint uk_vsan_historical_health as well as constraint and index on the partitions looking like
  vsan_historical_health_2022__cluster_id_group_id_test_id__key16
   

Note : This issue is specific to systems that were previously being upgraded from 7.0 to 7.0 U1 to 7.0 U2 or 7.0 U3.

Environment

VMware vCenter Server 7.0.2
VMware vCenter Server 7.0.3
VMware vCenter Server 8.0.x

Cause

• The partitioned VSAN_HISTORICAL_HEALTH table was introduced in version 7.0 U2 with an unique index on the cluster_id, group_id, test_id, creation_time columns.

• Upgrade path from vSphere 7.0 to vSphere 7.0 U2 and to vSphere 7.0 U3 introduces additional unique constraint check on these columns.

• During the migration import, constraints and indexes are dropped and eventually recreated to ensure faster data import.

• The order of dropping and handling constraints on this new partitioned table is not handled properly, which causes the issue.

Resolution

This issue is resolved in vCenter Server 8.0b (build number 21216066)

Workaround:

Aligning the schema structure with the one of fresh install and other upgrade paths, eliminating the need for the import to handle the constraint on the partitioned table.

The following steps have to be performed on the system before the upgrade:

1. Backup

2. Log into the database.

psql -U postgres -d VCDB

3. Drop the existing constraint.

ALTER TABLE VC.VSAN_HISTORICAL_HEALTH DROP CONSTRAINT UK_VSAN_HISTORICAL_HEALTH;

4. Create unique index instead of the constraint.

CREATE UNIQUE INDEX IF NOT EXISTS UK_VSAN_HISTORICAL_HEALTH on VC.VSAN_HISTORICAL_HEALTH (
            CLUSTER_ID,
            GROUP_ID,
            TEST_ID,
            CREATION_TIME
) TABLESPACE hs4;

 

5. Verify If it is correctly applied by running the queries again.

select i.relname idxname, t.relname tablename, idx.indisunique
from pg_index idx
join pg_class i on (idx.indexrelid = i.oid)
join pg_class t on (t.oid = idx.indrelid)
where t.relname like 'vsan_historical_health%'
and idx.indisunique = true;

select t.relname rablename, con.conname, con.contype
from pg_constraint con
join pg_class t on (t.oid = con.conrelid)
where t.relname like 'vsan_historical_health%';

They should return the relation uk_vsan_historical_health and partition entries only for the indexes (first query), but not the constraints (second query).

6. Run the upgrade.