Editing vRealize Automation appliance PostgreSQL database by hand

20150912 - 1About 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.

20150912 - 2

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:

20150912 - 3

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:

  1. scopetype
  2. scope
  3. subtenantrole
  4. subtenant
  5. subtenantrole_principalid
  6. scope_principalextension
  7. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s