vCloud Director 9.7 idle-in-transaction timeout error

Hello everyone!

After migration from CentOS based VMs with vCloud Director 9.7.0.6 to Appliance with migration of DB from MSSQL to PostgreSQL some of Cloud users starts to complain about failing tasks of copying , moving or exporting VMs within Organization VDCs.

If you're facing this kind of issue you should check parameter idle_in_transaction_session_timeout in your PostgreSQL Database. In vCloud Director appliance of version 9.7 this parameter is set to 5 minutes only (in 10.1 / 10.2 this parameter is set to 1 hour).

To do this you should SSH to your cell with Primary DB role on it and execute this commands:

su - postgres -c '/opt/vmware/vpostgres/current/bin/psql -d vcloud'
show idle_in_transaction_session_timeout;
Ctrl+d

To increase value from 5 minutes to 2 hours (7200000 ms) you should put these commands into Primary DB shell:

su - postgres -c '/opt/vmware/vpostgres/current/bin/psql -d vcloud'
ALTER DATABASE vcloud SET idle_in_transaction_session_timeout = 7200000;

After minute you can check current value of idle_in_transaction_session_timeout parameter and try to repeat needed tasks with VM in Organization VDC:

vcloud=# show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout
-------------------------------------
 2h
(1 row)

vcloud=#