Aria Orchestrator hangs during PostgreSQL database inventory operations

Products

• VCF Operations/Automation (formerly VMware Aria Suite)

Issue/Introduction

When performing database inventory management tasks in VMware Aria Orchestrator, the service may enter an unresponsive or "hung" state for one to two hours when adding or removing PostgreSQL database endpoints. This issue often results in blocked automation workflows and inventory management tasks. Logs may indicate an unhandled exception within the SQL plugin. This article outlines the root causes, including potential network configuration conflicts and SQL plugin limitations, and provides steps to stabilize the Orchestrator environment.

Symptoms:

• Orchestrator becomes unresponsive for one to two hours during PostgreSQL database addition or removal.
• Automation workflows fail to execute or remain in a pending state.
• The following error is observed in the logs: java.lang.reflect.InvocationTargetException com.vmware.o11n.plugin.database.DatabaseException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "where"

Environment

• VMware Aria Orchestrator 8.18.1
• PostgreSQL databases

Cause

This issue is typically caused by a combination of factors:

1. SQL Plugin Exception: A technical limitation in the SQL plugin's handling of custom queries can cause an unhandled exception when specific syntax errors are present in a query string.
2. Network Gateway Conflict: Environmental network issues, such as a gateway conflict, can prevent proper communication between the Orchestrator instances and the database, leading to connectivity timeouts and perceived "hung" states.

Resolution

1. Verify Network Connectivity: Ensure that there are no network gateway conflicts blocking communication between the Orchestrator appliances and the PostgreSQL database nodes. Check firewall rules and routing tables to confirm that port 5432 and relevant inter-VM traffic are permitted.
2. Review Custom SQL Queries: If the error PSQLException: ERROR: syntax error at or near "where" persists, review any custom queries configured in the SQL plugin. Ensure the SQL syntax is correct and conforms to PostgreSQL standards.
3. Inventory Cleanup: Remove any stale or unused database endpoints from the Orchestrator inventory. A high volume of inaccessible or improperly configured endpoints can contribute to performance degradation and thread hangs during discovery operations.
4. Service Restart: If the Orchestrator remains unresponsive after correcting network and configuration issues, restart the Orchestrator service to clear existing thread hangs.

Additional Information

If you require further assistance with log analysis or if the issue persists after these steps, please contact Broadcom Support for troubleshooting assistance.