While looking to upgrade one of my vCloud Director instances I came across an error during the database upgrade process which is the second step of updating vCloud Director from version to version. I must admit, it the eight to nine years of using vCloud Director, this was the first time that had an error during this process… I was kind of shocked!
Unable to upgrade the database: java.lang.IllegalStateException: Exception encountered while altering idle transaction session timeout in vcloud database:
This was upgrading a PostGreSQL 9.5 database in a single cell all in one setup. Initially I was going from 9.1.x to 9.7 so I decided to roll back and try a 9.5 upgrade first. That worked without issue, however the subsequent upgrade from 9.5 to 9.7 also failed with the same error. Talking in the VMware Cloud Provider Slack Channel I got a few pointers to permissions and/or the version of PostGreSQL being not supported by 9.7.
Looking at the supportability Matrix for vCloud Director against supported databases we see:
vCloud Director 9.7 only supports MSSQL 2017 (last release that will support MSSQL) and PostGreSQL 10 which suggests 10.x. I was running PostGreSQL 9.5, so decided to upgrade to 10.7 using this guide from Yves Sandfort.
After upgrading to 10.7 I tried the upgrade again but still got the same error. Because of the fact that the same instance upgraded successfully from 9.1 to 9.5 I didn’t really consider the database permissions to be a problem so continued to investigate with the help of VMware Support. What we found was an exception that mentioned ownership of the vcloud database from the current user which is vcloud.
Sure enough, logging into PostgreSQL admin it showed that the existing owner of the vcloud database was postgreS its self.
There seems to be two potential triggers for the error during the database upgrade:
- Being on a non supported version of PostgreSQL (not 10x)
- Not having the correct ownership permissions against the vcloud database
So if that error pops up during an upgrade to vCloud Director 9.7 check either of the above (or both) and give it another shot.