About a month ago I accidentally deleted a SQL database as part of a migration to a new failover cluster. The database in question was the IaaS database used by my vRealize Automation installation, and no backup was available. Mistakes like that in a lab happen from time to time and are okay, as long as you can be the one to fix them.
WARNING: the following is risky, and will potentially leave you with a broken installation. At best, I imagine VMware would consider this action “unsupported”. Use at your own risk.
I reinstalled the IaaS components and provisioned a new SQL database, this time on my new cluster. I had to recreate my endpoints, machine prefixes and so on, but when I came to create my business groups, I found a problem.
I wasn’t able to.
After a few minutes scratching my head I discovered I could create business groups with a new name, but I couldn’t re-use ones I’d previously provisioned. It was clear that whilst the IaaS SQL database was new, the old information was stored somewhere else, and the two were now out of sync.
I started a discussion on the excellent VMware Community forum, but unfortunately it didn’t receive any feedback. I obviously had to stand tall (tough for a Hobbit) and fix this one myself.
When provisioning the vRealize Automation appliance, a PostgreSQL database is installed, and I figured this contained the old data.
Access the VAMI page of the vRA appliance at the following URL (substitute accordingly):
https://vra.lab.mdb-lab.com:5480/
Click on the Admin tab, the check the box to enable SSH:
SSH to the vRA appliance:
ssh root@vra.lab.mdb-lab.com
Dump the contents of the database:
/opt/vmware/vpostgres/current/bin/pg_dump --username=vcac vcac > dump.txt
The business group I was looking to create was called “Development”, so to search the dump file for the string use:
cat ./dump.txt | grep Development
This confirmed the group existed. I then searched again for another group to confirm it wasn’t a fluke.
At this point I set about manually deleting entries from the database. A lot of the tables are linked, so if you try to delete an entry in a table that is referenced elsewhere it will complain. For example, if you try to remove an entry from the subtenant table, you will receive the following error:
ERROR: update or delete on table “subtenant” violates foreign key constraint “subtenantrole__subtenant_id__subtenant__id__fkey” on table “subtenantrole”
DETAIL: Key (id)=(034d2a72-09e2-49ba-a2a7-be5d0963294d) is still referenced from table “subtenantrole”.
Connect to the database:
/opt/vmware/vpostgres/current/bin/psql vcac vcac
List the tables:
\dt
To delete an entry, use the following command:
DELETE FROM subtenantrole WHERE name = 'Development'; DELETE FROM subtenant WHERE name = 'Development';
In the case of business groups, the following tables will need modifying:
- scopetype
- scope
- subtenantrole
- subtenant
- subtenantrole_principalid
- scope_principalextension
- scoperoleassignment
When you have finished editing the database, exit using:
\q
Once the old data has been removed, you will be able to add new business groups.
