vCenter Server 6.5 to VCSA 6.7 - is not present in table "vpx_vm" - Crash

[YKAMVN]

Hello all, hope everyone is well in these strange times.

I am trying to migrate from vCenter Server 6.5 to vCSA 6.7 - whilst this is our smallest cluster i would like it to be successful to maintain the moid's for Veeam which I have a lot of historical backups retained.

The migration process performs as expected, however in Stage 2 after shutting down the original server and firing up Postgres on the applianxce I get a critical error. When I SSH the appliance I am able to trace the install and Postgress logs and see:

2020-07-22T02:05:45.002Z  Upgrade import step failed

2020-07-22T02:05:45.002Z  vPostgres firstboot(action=firstboot) failed

NOTICE:  constraint "fk_vpx_vw_ref_vpx_entity" of relation "vpx_wwn" does not exist, skipping

NOTICE:  constraint "fk_wl_ref_vpx_cbk_serv" of relation "vpx_callback_whitelist" does not exist, skipping

NOTICE:  constraint "pk_vpx_host_crypto_keys_ref" of relation "vpx_host_crypto_keys" does not exist, skipping

NOTICE:  constraint "vdc_entity_lock_metadata_fk" of relation "vdc_entity_lock_metadata" does not exist, skipping

psql.bin:/storage/seat/cis-export-folder/vcdb/create_constr.sql:983: ERROR:  insert or update on table "vpx_vm_fle_file_info" violates foreign key constraint "fk_fle_file_info_ref_vm"

DETAIL:  Key (vm_id)=(44) is not present in table "vpx_vm".

Naturally this implies I have a problem with the database but I was wandering if anyone had come across this and had a way to resolve?

Unfortunately the 6.5 database is Postgreass and whilst I am going through the pgAdmin to get to the database Im not sure what I am going to be able see / do to fix.

Any assistance appreciated it.

[amohammadimir]

This could be certain orphaned data present in the table "vpx_vm".

Before making any changes in the database, make sure that you have a valid database backup in place.

Run this command on your database:

select count(*) from vpx_vm where vm_id not in (select vm_id from vpx_vm);

and if f the output is non-zero, proceed to delete the record:

delete from vpx_vm  where vm_id  not in (select vm_id  from vpx_vm);

Then, re-run the upgrade.

[YKAMVN · Accepted solution]

Good news I was able to push though. For any having a similar issue you need to

1. Download and install pgAmin
2. Take a snapshot / backup of your vCenter server and / or database
3. Follow the instructions to connect to the database VMware Knowledge Base https://kb.vmware.com/s/article/2108848
4. Run below to confirm whether there are any orphans:
   

   select * from vpx_vm where id not in (select vm_id from vpx_vm_fle_file_info)

5. Run below to delete the orphans:
   

   delete from vpx_vm_fle_file_info where vm_id not in (select id from vpx_vm)

6. Redo the migration

In our case it was leftovers of VM's from prior to migrating all the guests to new hardware. Obviously this is just advice and comes with risks.

Thanks to all for their help.

___________________________________________________________________________

"Did you find this helpful? Let us know by completing this survey (takes 1 minute!)"