vIDM 3.3.x vPostgres DB OAuth2RefreshToken table consumes most space on the appliance leading to service outages
search cancel

vIDM 3.3.x vPostgres DB OAuth2RefreshToken table consumes most space on the appliance leading to service outages

book

Article ID: 322682

calendar_today

Updated On:

Products

VMware Aria Suite

Issue/Introduction

Symptoms:
  • vIDM DB partition is full or nearly out of space.
  • To validate which DB partition is using most of the space run the following commands:
    du -sh /db/elasticsearch
    du -sh /db/rabbitmq
    du -sh /db/data
Note: This will detail and display the /db/data volume taking most of the space. If it is one of the other disk trees, please see other KBs (i.e. audit data).


Environment

VMware Identity Manager 3.3.x

Cause

  • The cause is over utilization of API by other clients. 
  • Any time the API is invoked it creates a refresh token entry in the database utilizing additional storage.

Resolution

Prerequisites

  • You have root access to the appliance.
  • You have valid backups or snapshots.

Steps to clean up excessive disk space consumed by OAuth2RefreshToken table.

  1. SSH into the primary appliance where the Master role for vPostgres is located
  2. Stop the horizon-workspace service on all nodes
    service horizon-workspace stop
  3. Find the largest table sizes
    export PGPASSWORD=`cat /usr/local/horizon/conf/db.pwd`
    /opt/vmware/vpostgres/current/bin/psql -U postgres -d saas
    
    SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    ORDER BY pg_total_relation_size(C.oid) DESC
    LIMIT 20;
  4. Remove the data from the table by running a truncate
    truncate table "OAuth2RefreshToken" CASCADE;
    
  5. Reindex the database and vacuum. Note: if the disk is too full or approaching 100%, extending the disk prior to the following steps would be recommended. Vacuum Full operation will need extra space for the process to complete. 
    select count (*) from "OAuth2RefreshToken";
    select count (*) from "OAuth2AccessToken";
    select count (*) from "SuiteTokenCache";
    vacuum full verbose;
    reindex table "OAuth2RefreshToken";
    vacuum full verbose;
    \q
  6. Restart the vPostgres service
    service vpostgres restart
  7. Start the horizon-workspace service on all nodes
    service horizon-workspace start
  8. Wait for 10 minutes after restart to allow services to fully initialize.
  9. Count the number of rows in the OAuth2RefreshToken table
    /opt/vmware/vpostgres/current/bin/psql -U postgres -d saas
    select count (*) from "OAuth2RefreshToken";


Additional Information

You can also expand the /db file system via logical volume management. See Creating and Configuring an additional disk for VMware Identity sva (2097696).