Virtualisation Tips & Tricks

While upgrading the platform from version 10.5.1 to 10.6.1, we encountered problems during the database schema upgrade step. The entire process ended with an error. As a result, the Cloud Director database was no longer usable and the database recovery procedure described in this document had to be performed.

https://techdocs.broadcom.com/us/en/vmware-cis/cloud-director/vmware-cloud-director/10-6/vmware-cloud-director-installation-and-upgrade-guide-10-6/deployment-uprade-and-administration-of-the-vcd-appliance-install/upgrading-and-migrating-your-vmware-cloud-director-appliance-install/roll-back-your-vcd-appliance-when-upgrade-fails-install.html

The error message itself is here:

Executing upgrade task:
Successfully ran upgrade task
Executing upgrade task:
Successfully ran upgrade task
Executing upgrade task:
Successfully ran upgrade task
Executing upgrade task:
......[6]
Unable to upgrade the database: org.postgresql.util.PSQLException: ERROR: permission denied to create extension "pgcrypto"

Entries from upgrade log file:

2025-03-20 20:43:52,247 | ERROR    | main                      | UpgradeAgent                   | Unable to upgrade the database: org.postgresql.util.PSQLException: ERROR: permission denied to create extension "pgcrypto"
  Hint: Must have CREATE privilege on current database to create this extension. | com.vmware.vcloud.upgrade.UpgradeAgentException: org.postgresql.util.PSQLException: ERROR: permission denied to create extension "pgcrypto"

  Hint: Must have CREATE privilege on current database to create this extension.
        at com.vmware.vcloud.upgrade.legacy.UpgradeAgent.runUpgradeTasks(UpgradeAgent.java:829)
        at com.vmware.vcloud.upgrade.legacy.UpgradeAgent.executeUpgrade(UpgradeAgent.java:403)
        at com.vmware.vcloud.upgrade.legacy.UpgradeAgent.upgradeDatabase(UpgradeAgent.java:419)
        at com.vmware.vcloud.upgrade.legacy.UpgradeAgent.start(UpgradeAgent.java:577)
        at com.vmware.vcloud.upgrade.legacy.UpgradeAgent.main(UpgradeAgent.java:501)
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied to create extension "pgcrypto"

Conclusion:

This issue usually occurs when the owner of the Cloud Director database named “vcloud” is postgres instead of vcloud.

We can check this by running the following commands.

# sudo -su postgres psql vcloud
# \l

Resolution is change owner to vcloud. Update the owner of the vcloud database as “vcloud” using the below query:

# sudo -su postgres psql vcloud
# alter database vcloud owner to vcloud;

Now let’s try running the upgrade script again. And this time the process should be successful.

/opt/vmware/vcloud-director/bin/upgrade

Happy computing 😉