Cloud Director: Error modifying Provider vDC due to Postgres statement timeout

Products

• VMware Cloud Director

Issue/Introduction

When attempting to modify a Provider vDC within VMware Cloud Director, you may encounter an error that prevents the modification from completing.

The following error message is observed in the UI or logs:

org.postgresql.util.PSQLException: ERROR: canceling statement due to statement timeout

Stack traces in the logs indicate that this timeout occurs specifically when Cloud Director is loading the storage class for the Provider vDC.

Environment

 

• VMware Cloud Director

• PostgreSQL Database

 

Cause

The PostgreSQL database is taking longer than the default 30-second timeout to retrieve the information for the Provider vDC. Because the database cannot retrieve the information quickly enough, the Cloud Director statement times out.

Resolution

To resolve this issue, you must optimize the PostgreSQL database to ensure old records are cleaned up and the SQL execution plan uses the best indexes to run the select statement.

1. Connect to your PostgreSQL database server.

2. Log into the vcloud database.

3. Run the following optimization commands:

   SQL

    

   vacuum full;
   reindex database vcloud;