Compressed data is "corrupt^M" error while migrating Windows vCenter 6.0 with vPostgres DB to VCSA 6.5

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:
Migration from windows vcenter 6.0  with vPostgres DB to 6.5 VCSA fails with error's similar to below:

• No file found matching /etc/vmware/install-defaults/cm.url 
• No file found matching /etc/vmware-vpx/vcdb.properties 
• No file found matching /etc/vmware-vpx/vc-extn-cisreg.prop
• cmd "/opt/vmware/bin/sso-config.sh get_identity_sources $VMWARE_LOG_DIR" timed out after 60 seconds due to lack of progress in last 30 seconds (0 bytes read)"

Environment

VMware vCenter Server 6.0.x

Cause

Error occurs due to table corruption in the Database. Log snipping matches to below :-

Upgrade EXPORT failed.

vcdb_export.err :-
pg_dump: NOTICE: there are circular foreign-key constraints among these table(s):
pg_dump:   vpx_entity
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: Dumping the contents of table "vpx_binary_data" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  compressed data is corrupt

upgrade-runner.log :-
2018-08-31T19:28:21.861Z INFO upgrade.states.component_states vcdb:Export: reported message 'Exporting VMware vCenter Server Database data...'
2018-08-31T19:28:21.862Z INFO upgrade.states.component_states vcdb:Export: 2018-08-31T19:28:21.318Z INFO vcdb.const Exporting core tables...
2018-08-31T19:28:22.871Z INFO upgrade.states.component_states vcdb:Export: ^M
2018-08-31T19:28:22.871Z INFO upgrade.states.component_states vcdb:Export: 2018-08-31T19:28:22.361Z ERROR vcdb.const Error while exporting source core tables: pg_dump: Error message from server: ERROR:  compressed data is corrupt^M
2018-08-31T19:28:22.871Z INFO upgrade.states.component_states vcdb:Export: ^M
2018-08-31T19:28:22.871Z INFO upgrade.states.component_states vcdb:Export: 2018-08-31T19:28:22.361Z ERROR __main__ Upgrade Phase 'vcdb:Export' failed. Exception: Error while exporting source core tables: pg_dump: Error message from server: ERROR:  compressed data is corrupt^M
.
2018-08-31T19:28:22.875Z INFO upgrade.states.component_states vcdb:Export: PermanentError: Error while exporting source core tables: pg_dump: Error message from server: ERROR:  compressed data is corrupt
2018-08-31T19:28:22.875Z INFO upgrade.states.component_states vcdb:Export: Script completed for 194.039999962 secs with return-code='1', and executionId=a4cd2f65-b88b-4764-a370-d2af385e22aa
2018-08-31T19:28:22.878Z ERROR upgrade.states.component_states vcdb:Export: Remote script failed with an error [PermanentError()]
2018-08-31T19:28:22.880Z ERROR upgrade.states.component_states vcdb:Export: failed with permanent error. For details take a look at Export_com.vmware.vcdb_2018_08_31_12_18.log.
2018-08-31T19:28:22.892Z WARNING wf.wf_processor State Export com.vmware.vcdb has failed.

Resolution

To resolve this issue, perform the following steps:

1 - Identify the corrupt table from the logs entries. In this instance "Dumping the contents of table "vpx_binary_data" failed: PQgetResult() failed"
2 - Connect to VCDB using  SQL Management Studio.

Note: This procedure modifies the database. Ensure to take a valid backup of the database before proceeding.

3 - Run the commands:

Truncate table vpx_binary_data;

4 - Start the vCenter Server service.
5 - Start Migration process again

Additional Information

Impact/Risks:
vCenter migration fails