vIDM 3.3.x appliance has no space left on device /db/data postgres DB taking all the space

vIDM DB partition is full or nearly out of space.

To validate the DB partition is using most of the space run the following commands:

du -sh /db/elasticsearch
du -sh /db/rabbitmq
du -sh /db/data

This will allow to see that /db/data taking most of the space. If it is one of the other disk trees, please see other KBs (ie audit data).

Apply the following steps to clean up the bloated DB table

---

Step:1

Take Product Snapshot

Step:2

Stop Services

service horizon-workspace stop 

Step:3

Fetch the Password to connect with postgres database

 cat  /usr/local/horizon/conf/db.pwd

If you do not want to expose the password then use below command as it is. It will export the password into PGPASSWORD

export PGPASSWORD=`cat /usr/local/horizon/conf/db.pwd`

example snippet

root@idm [ /opt/vmware/horizon/workspace/logs ]# export PGPASSWORD=`cat /usr/local/horizon/conf/db.pwd`
root@idm [ /opt/vmware/horizon/workspace/logs ]# /opt/vmware/vpostgres/current/bin/psql -U postgres saas
psql.bin (9.6.21 (VMware Postgres 9.6.21.0-18007711 release))
Type "help" for help.

saas=# \q
root@idm [ /opt/vmware/horizon/workspace/logs ]#

When you execute below Step:4 command to login then it will not prompt you for password again

Step:4

Connect to Postgres database using below command. Output snippet shown below too. Remember the password shown below is from my lab. Don't use it in your environment

/opt/vmware/vpostgres/current/bin/psql -U postgres saas

example snippet:

root@idm [ ~ ]# cat  /usr/local/horizon/conf/db.pwd
X9cEZskwwWRdeqP8hPE30TB_KKYx_Lq2


root@idm [ ~ ]# /opt/vmware/vpostgres/current/bin/psql -U postgres saas
Password for user postgres:
psql.bin (9.6.21 (VMware Postgres 9.6.21.0-18007711 release))
Type "help" for help.

saas=#

Step:5

Fetch the table sizes using below query

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;

Step:6

Truncate the table as shown below using command

truncate table "OAuth2RefreshToken" CASCADE;

Step:7

Check the row count for three tables

select count(*) from "OAuth2RefreshToken";
select count(*) from "OAuth2AccessToken";
select count(*) from "SuiteTokenCache";

Step:8

Execute Full Vacuum using below command

vacuum full verbose;

Output would be sometime like below

saas=# vacuum full verbose;
INFO:  vacuuming "saas.databasechangelog"
INFO:  "databasechangelog": found 0 removable, 1368 nonremovable row versions in 47 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
INFO:  vacuuming "saas.databasechangeloglock"
INFO:  "databasechangeloglock": found 18 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "saas.ForbiddenTenantName"
INFO:  "ForbiddenTenantName": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "saas.MasterKey"
INFO:  "MasterKey": found 0 removable, 0 nonremovable row versions in 0 pages
*
*
*
*
INFO:  vacuuming "saas.ResourceTypeRegistry"
INFO:  "ResourceTypeRegistry": found 2 removable, 3 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  vacuuming "saas.EndUserCatalogStorage"
INFO:  "EndUserCatalogStorage": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
saas=#

Step:9

Re-Index table OAuth2RefreshToken and perform full vacuum again

To ReIndex

reindex table "OAuth2RefreshToken";

Full Vacuum

vacuum full verbose;

example snippets

saas=# vacuum full verbose;
INFO:  vacuuming "saas.databasechangelog"
INFO:  "databasechangelog": found 0 removable, 1368 nonremovable row versions in 47 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
INFO:  vacuuming "saas.databasechangeloglock"
INFO:  "databasechangeloglock": found 18 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "saas.ForbiddenTenantName"
INFO:  "ForbiddenTenantName": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "saas.MasterKey"
INFO:  "MasterKey": found 0 removable, 0 nonremovable row versions in 0 pages
*
*
*
*

INFO:  vacuuming "information_schema.sql_features"
INFO:  "sql_features": found 0 removable, 671 nonremovable row versions in 7 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  vacuuming "saas.ResourceProfileMapping"
INFO:  "ResourceProfileMapping": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "saas.IDPOrgNetwork"
INFO:  "IDPOrgNetwork": found 0 removable, 2 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Step:10

Exit Database

\q

Step:11

Execute a Postgres Service Restart

root@idm [ ~ ]# service vpostgres restart
root@idm [ ~ ]#

Step:12

Start Horizon Application

service horizon-workspace start

Step:13

Wait for the application to completely initialize

Log Location: /opt/vmware/horizon/workspace/logs

Initially you would see information being logged under workspace.log , then connector.log and horizon.log 

Monitor for any exceptions

Step:14

Once the whole application is initialized you may perform check on the table again by logging into database as shown below

Also perform table size check if needed

Step:15

Check the disk space now and perform a health check to see if everything is working as expected

---