Traffic to the Postgres replica database results in Internal Errors in VMware vRealize Automation

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:
When the Postgres configuration includes a replica, you experience these symptoms:

• Traffic to the replica results in internal errors.
• In the User Interface, you see the error:
  
  An internal error has occurred. If the problem persists, please contact your system administrator.
  
  
• In the catalina.out file of the vRealize Automation virtual appliance, you see entries similar to:
  
  2016-01-29 14:55:00,008 vcac: [component="cafe:event-log" priority="ERROR" thread="taskScheduler-1" tenant] org.springframework.scheduling.support.TaskUtils$LoggingErrorHandler.handleError:95 - Unexpected error occurred in scheduled task.
  
  org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement
  
  ... (stack trace trimmed) ...
  
  Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
  
  ... (stack trace trimmed) ...
  
  Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute UPDATE in a read-only transaction
  
  Note: The preceding log excerpts are only examples. Date, time, and environmental variables may vary depending on your environment.

Environment

VMware vRealize Automation 6.2.x
VMware vCloud Automation Center for Server 6.0.x
VMware vCloud Automation Center for Desktop 6.0.x
VMware vCloud Automation Center for Desktop 6.1.x
VMware vCloud Automation Center for Server 6.1.x

Cause

This issue occurs because the Postgres replica is read only and is periodically updated from the master.

When Postgres replica is read only, there may be delay of the latest available updates (depending on the health and frequency of the replication). Therefore, it cannot be used for any updates, inserts, or deletes. In vRealize Automation, there is no way to split the read traffic to take advantage of the replica as a read only data source.

Resolution

To resolve this issue, ensure all Postgres database traffic is redirected to the master. If a load balancer is in use, ensure that the Postgres master is the only active node and no traffic is sent to the replica.

If the master experiences a failure or an outage, do not direct traffic to the replica unless it is promoted to the master.