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





 




74 views0 comments