Deleting Entry in vCloud PostgreSQL

If you're familiar with vCloud Director, you know that occasionally there can be stubborn, outdated entries. This happens when a VM, vApp, or template won't respond to the standard GUI methods. When this happens, many people turn to the VMware helpdesk for assistance. However, did you know that you can tackle this issue yourself, even if you're not a DBA? All you need is to take a backup or snapshot of the vCloud VM and make a backup of the Postgre DB with SSH/CLI. So, let's dive in!

1. First go in the GUI to the VM and take IDs of vApp and VM.

VM

e8b68b96-8c8a-4e2c-bf35-17ddc534e451

vApp

a893f027-6bee-4e54-8125-b09c04319c72

2. First enter to the vCloud PhotonOS with SSH (putty, MobaXterm).

4. Then enter to vCloud PostgreSQL with this command:

sudo -i -u postgres psql vcloud

(where postgres is a user, and vcloud is a DB)

5. =Deleting VM=

VM id: e8b68b96-8c8a-4e2c-bf35-17ddc534e451

delete from vapp_vm where id = ' 'e8b68b96-8c8a-4e2c-bf35-17ddc534e451'';

XXX

ERROR:  update or delete on table "vapp_vm" violates foreign key constraint "fk_vap_vm_scl_me2vapp_vm" on table "vapp_vm_sclass_metrics"

DETAIL:  Key (id)=(ac706161-1b79-4a41-a2d8-abfff0754758) is still referenced from table "vapp_vm_sclass_metrics".

We need to delete the VM from the referenced table vapp_vm_sclass_metrics, 

instead of id we will use vapp_vm_id.

select * from vapp_vm_sclass_metrics where vapp_vm_id = 'e8b68b96-8c8a-4e2c-bf35-17ddc534e451';

delete from vapp_vm_sclass_metrics where vapp_vm_id = 'e8b68b96-8c8a-4e2c-bf35-17ddc534e451';

delete from vapp_vm where id = 'e8b68b96-8c8a-4e2c-bf35-17ddc534e451';

Again we have referenced table

select * from guest_personalization_info where vapp_vm_id = 'e8b68b96-8c8a-4e2c-bf35-17ddc534e451';

delete from guest_personalization_info where vapp_vm_id = 'e8b68b96-8c8a-4e2c-bf35-17ddc534e451';

delete from vapp_vm where id = 'e8b68b96-8c8a-4e2c-bf35-17ddc534e451';

In GUI

We see that there is no VM, but again we cannot to delete the vApp.

Success for VM, but still not for vApp.

6. =VAPP=

We are taking vApp id: 'a893f027-6bee-4e54-8125-b09c04319c72'

https://vcloud.algotec.co.il/tenant/support/vdcs/7f306f1b-a035-4015-81d8-d3783b5a3128/vapp/vapp-a893f027-6bee-4e54-8125-b09c04319c72/vcd-vapp-vms

Now we can work with sg_id, which is more relaible than a name.

select * from vm_container where sg_id='a893f027-6bee-4e54-8125-b09c04319c72';

We see the vApp still exists:

Now we will try to delete the vApp.

delete from vm_container where sg_id = 'a893f027-6bee-4e54-8125-b09c04319c72';

We cannot delete the vApp because of another table that contains vApp id - "vapp_logical_resource".

Let's go there.

select * from vapp_logical_resource where vapp_id='a893f027-6bee-4e54-8125-b09c04319c72';

Now deleting.

delete from vapp_logical_resource where vapp_id='a893f027-6bee-4e54-8125-b09c04319c72';

Good, let's check in DB.

select * from vapp_logical_resource where vapp_id='a893f027-6bee-4e54-8125-b09c04319c72';

Now we return back to the original place in trial to delete the vApp.

First, little check:

select * from vm_container where sg_id='a893f027-6bee-4e54-8125-b09c04319c72';

Now the actual deleing:

delete from vm_container where sg_id='a893f027-6bee-4e54-8125-b09c04319c72';

Seems we have deleted the vApp!..., but let's check that it is a real thing:

select * from vm_container where sg_id='a893f027-6bee-4e54-8125-b09c04319c72';

Now we sure it is deleted, but let's check in GUI too:

Bravo!