Incorrect PostgreSQL object ownership prevents vCenter upgrade

Recently I had to upgrade a vCenter Server Appliance with embedded PSC from version 6.5 U2e to 6.7 U2c. It seemed a pretty straightforward process like the upgrades I’ve done before. But this time the upgrade failed because of an issue with the PostgreSQL database.

I examined the logfiles and found this to be the cause of the failure:

ERROR: must be owner of relation vpx_sn_vdevice_backing_rel_seq

When searching for this issue on your favorite search engine you will probably be pointed to KB article 55747 and this is actually where the fix is found. However there are two critical pieces of information missing from this KB at the time of writing.

The first piece of information that is missing from the KB is that you need to execute the script on the SOURCE VCSA before you start the upgrade.

And the second piece is that the actual script that fixes the underlying issue is missing from the article. I opened a support request with GSS and they actually pointed me to a Chinese translation of the same KB article which contains the attachment. If you access the KB using the URL https://kb.vmware.com/articleview?docid=55747 there’s no attachment and if you go to https://kb.vmware.com/s/article/55747 you are redirected to another KB which also does not contain an attachment.

So for the sake of completeness I’ll share the script in this blog post as well.

# /bin/bash

/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -t -q -c "SELECT case when t.relname is not null then 'alter sequence ' || s.relname || ' owned by none; alter table ' || s.relname || ' owner to vc; alter sequence ' || s.relname || ' owned by ' || t.relname || '.' || a.attname || ';' else 'alter sequence ' || s.relname || ' owner to vc;' end as cl FROM pg_class s left JOIN pg_depend d ON d.objid = s.oid left JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid left JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) left JOIN pg_namespace n ON n.oid = s.relnamespace WHERE s.relkind = 'S'" | /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB

After executing the script on the source VCSA the upgrade completed without any issues.