Postgres Performance Improvements for vRA 7.x environments

Products

• VMware Aria Suite

Issue/Introduction

This article contains recommendations for improving Postgres replication reliability in vRA 7.3 to vRA 7.5

Symptoms:

Synchronous vPostgres Replication status may go out of sync for the replicas

Environment

VMware vRealize Automation 7.x

Resolution

These configuration changes are included by default in vRA 7.6 and can be manually updated in vRA 7.3.x, vRA 7.4, vRA 7.5

Prior to implementing these changes, a backup should be taken of the vRA environment.

• SSH to all Virtual Appliances (VA) and stop psql-manager starting with the replica VAs and then the primary VA
  • service psql-manager stop
• Stop vRA services on all 3 VAs starting with the replica VAs and then the primary VA
  • vcac-vami service-manage stop vco-server vcac-server horizon-workspace hzn-dots elasticsearch
• As the postgres user (su - postgres) backup /storage/db/pgdata/postgresql.conf on all 3 VAs
  • cp /storage/db/pgdata/postgresql.conf /storage/db/pgdata/postgresql.conf.backup
• On each VA, starting with the replicas and then the primary VA
  • Add the following to /storage/db/pgdata/postgresql.conf (at the end of the file)
    • max_wal_size = 5GB
    • maintenance_work_mem = 128MB
    • min_wal_size = 256MB
  • Update the following in /storage/db/pgdata/postgresql.conf
    • change "shared_buffers = 128MB" to "shared_buffers = 512MB"
  • Restart postgres server
    • service vpostgres restart
• Start services on all 3 VAs [primary, then replicas]
  • vcac-vami service-manage start elasticsearch hzn-dots horizon-workspace vcac-server vco-server
  • ​​​Start psql-manager on all 3 VAs [primary, then replicas]
    • service psql-manager start
• Verify that all services are showing as REGISTERED in each VA's VAMI.