We are trying to migrate our vCenter servers with external HA PSC from 6.0 to 6.5 U2H version, as part of this process we have migrated a SSO servers successfully, but we errors while migrating the vcenter servers at stage 2 while starting the PostgreSQL service. The Error on the UI is “Encountered an Internal Error”

Vpostgres firstboot

• Reviewed the Postgres logs and found the information below:

2019-11-24T17:30:41.405Z   Upgrade import step failed
2019-11-24T17:30:41.405Z   vPostgres firstboot(action=firstboot) failed

VCDB Import Err:

NOTICE:  drop cascades to constraint vdc_entity_lock_metadata_fk on table vdc_entity_lock_metadata
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:910: ERROR:  insert or update on table “vpx_vdevice_file_backing_x” violates foreign key constraint “fk_vdevice_file_backing_x”
DETAIL:  Key (backing_id)=(4834613) is not present in table “vpx_vdevice_file_backing”.
psql.bin:/storage/seat/cis-export-folder/vcdb/create_constr.sql:928: ERROR:  insert or update on table “vpx_virtual_disk” violates foreign key constraint “fk_vpx_virtual_disk”
DETAIL:  Key (vdevice_id)=(3155637) is not present in table “vpx_virtual_device”.
psql.bin:/storage/seat/cis-export-folder/vcdb/create_constr.sql:990: ERROR:  insert or update on table “vpx_ext_server_email” violates foreign key constraint “fk_vpx_email_ref_server”
DETAIL:  Key (ext_server_id)=(34204) is not present in table “vpx_ext_server”.

Postgres Logs:

2019-11-24 17:22:41.230 UTC 5ddabc43.4c4b 0 VCDB vc WARNING:  there is no transaction in progress

2019-11-24 17:30:40.890 UTC 5ddabe2b.5533 22775 VCDB postgres ERROR:  insert or update on table “vpx_vdevice_file_backing_x” violates foreign key constraint “fk_vdevice_file_backing_x”
2019-11-24 17:30:40.956 UTC 5ddabe2b.5533 22793 VCDB postgres ERROR:  insert or update on table “vpx_virtual_disk” violates foreign key constraint “fk_vpx_virtual_disk”
2019-11-24 17:30:41.356 UTC 5ddabe2b.5533 22855 VCDB postgres ERROR:  insert or update on table “vpx_ext_server_email” violates foreign key constraint “fk_vpx_email_ref_server”

https://kb.vmware.com/s/article/57738?lang=en_US

2018-10-30 10:29:44.809 UTC 5bd83287.30a3 18822 VCDB postgres ERROR: insert or update on table “vpx_vm_fle_file_info” violates foreign key constraint “fk_fle_file_info_ref_vm”

2018-10-30 10:29:44.809 UTC 5bd83287.30a3 18822 VCDB postgres DETAIL: Key (vm_id)=(2350035) is not present in table “vpx_vm”.

2018-10-30 10:29:44.809 UTC 5bd83287.30a3 18822 VCDB postgres STATEMENT: ALTER TABLE vpx_vm_fle_file_info ADD CONSTRAINT fk_fle_file_info_ref_vm FOREIGN KEY (vm_id) REFERENCES vpx_vm(id) ON DELETE CASCADE;

This new error means that we have a line in VPX_VM_FLE_FILE_INFO with VM_ID = 2350035, but there is no line in VPX_VM with id = 2350035.

We asked customer to remove this entry from VPX_VM_FLE_FILE_INFO table with id 2350035.

Take a Backup of VCDB on a Location:

• Run the command to create vc.sql file on /tmp location:

/opt/vmware/vpostgres/current/bin/pg_dump -d VCDB -U postgres > /tmp/vc.sql

• Run the below command to Zip it:

bzip2 -z -f /tmp/vc.sql

Log in to vCenter DB:

• Connect to vCenter Server Appliance through the console or using an SSH session and root credentials.
• Enable the shell by running this command:

shell.set –enabled true

• To enter the shell, type shell and press Enter.
   
• Run this command to log in to the vCenter Server Appliance database:

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

Vaccum Command:

“vpx_vdevice_file_backing_x”
DETAIL:  Key (backing_id)=(4834613) is not present in table “vpx_vdevice_file_backing”.

“vpx_virtual_disk”
DETAIL:  Key (vdevice_id)=(3155637) is not present in table “vpx_virtual_device”.

“vpx_ext_server_email”
DETAIL:  Key (ext_server_id)=(34204) is not present in table “vpx_ext_server”.

• Exit the vCenter Server Appliance database by running this command:

\q

We might try to disconnect the hosts prior the Migration as I seen that also worked on some cases.

Make sure that you take the backup of your current database prior any actions. Do not skip this step.

Also on the source db can you check a few tables as per below and let us know the results:

Select count(*) from vpx_vdevice_file_backing where backing_id=4834613; Select count(*) from vpx_vdevice_file_backing_x where backing_id=4834613;

Select count(*) from vpx_virtual_device where vdevice_id=3155637; Select count(*) from vpx_virtual_disk where vdevice_id=3155637;

Select count(*) from vpx_ext_server where ext_server_id=34204; Select count(*) from vpx_ext_server_email where vdevice_id=34204;

2019-11-24 17:30:40.890 UTC 5ddabe2b.5533 22775 VCDB postgres ERROR:  insert or update on table “vpx_vdevice_file_backing_x” violates foreign key constraint “fk_vdevice_file_backing_x”
2019-11-24 17:30:40.956 UTC 5ddabe2b.5533 22793 VCDB postgres ERROR:  insert or update on table “vpx_virtual_disk” violates foreign key constraint “fk_vpx_virtual_disk”
2019-11-24 17:30:41.356 UTC 5ddabe2b.5533 22855 VCDB postgres ERROR:  insert or update on table “vpx_ext_server_email” violates foreign key constraint “fk_vpx_email_ref_server”

DELETE FROM VPX_EXT_SERVER_EMAIL child WHERE NOT EXISTS( SELECT 1 FROM VPX_EXT_SERVER parent WHERE child.EXT_SERVER_ID = parent.EXT_SERVER_ID);

DELETE FROM VPX_VDEVICE_FILE_BACKING_X WHERE BACKING_ID NOT IN (SELECT BACKING_ID FROM VPX_VDEVICE_FILE_BACKING);

DELETE FROM VPX_VIRTUAL_DEVICE WHERE VDEVICE_ID NOT IN (SELECT VDEVICE_ID FROM VPX_VIRTUAL_DISK);