Orphaned Large Objects are slowing down and breaking vPostgres upgrade

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• Upgrading to 8.10.1+ fails
• postgres-# container is stuck in a CrashLoopBackOff
• provisioning-service-db-upgrade-##### contains logs similar to

  ERROR: multiple primary keys for table "databasechangelog" are not allowed [Failed SQL: (0) ALTER TABLE public.databasechangelog ADD PRIMARY KEY (id, author, filename)]

Environment

VMware vRealize Automation 8.10.x
VMware vRealize Automation 8.11.x

Cause

pg_largeobject has too many records.

Resolution

VMware is aware of this issue and is being considered for fix in a later release.

See the Workaround below for additional information.

Workaround:

Prerequisites

• Please take simultaneous non-memory snapshots of each virtual appliance(s) in the cluster.
• You have access to root user and password,
• You have SSH or console access to each virtual appliance.

Procedure:

1. SSH into one node in the cluster.
2. Run the following command to vacuum the vro-gateway-db. This command is cluster aware and will find the writable vPostgres container to run the vacuumlo command against then return the output to the shell.

   for pod in $(kubectl get pods --namespace prelude -l app=postgres -o jsonpath='{.items[?(@.status.phase=="Running")].metadata.name}'); do if kubectl exec --namespace prelude -it $pod -- /bin/bash -c "chpst -u postgres -- psql -t -c 'SELECT pg_is_in_recovery();'" | grep -q "f"; then kubectl exec --namespace prelude -it $pod -- /bin/bash -c "chpst -u postgres -- psql vro-gateway-db -c 'select count(1) from pg_largeobject;' && chpst -u postgres -- vacuumlo -v vro-gateway-db && chpst -u postgres -- psql vro-gateway-db -c 'select count(1) from pg_largeobject;'"; break; fi; done

3. Reattempt the upgrade.

Additional Information

Impact/Risks:
Upgrades cannot be complete.