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

Products

• VCF Operations/Automation (formerly VMware Aria Suite)

Issue/Introduction

Symptoms:

• vIDM DB partition is full or nearly out of space.
• Aria automation is not functional when vIDM is down due to the DB partition reaching 100 percent usage.
• 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 details and displays 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

Resolution

Prerequisite:

• Ensure you have root credentials for the appliances.
• Take a valid backup or a snapshot before proceeding.

Steps to clean up excessive disk space consumed by the 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 is recommended. Vacuum Full operation needs 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 Workaround:

If on step 4 above, the TRUNCATE query does not produce any output at all after several minutes, you may need to find and forcibly stop a task:

1. If TRUNCATE has given no output at all, it is safe to use Ctrl+C to stop the request.
2. Use this SQL query to find psql replication processes, which may be stuck:

   SELECT pid,application_name FROM pg_stat_replication;

3. You may see application_name = walreceiver results that remain whenever checked. Use this SQL query to forcibly stop a process:

   SELECT pg_terminate_backend(<stuck_pid>);

4. Return to step 2 here to check if any of the same processes are still here.
5. You can now return to step 4 of the main resolution steps: attempt to truncate the DB once again and continue from there.

Additional Information

You can also expand the /db file system via logical volume management.

Kindly refer to the KB 'How to Increase vIDM appliance disk space'.