In this post I want to show how we can finally remove a datastore which is for whatever reason not accessible anymore and therefore couldn’t be deleted by using the vSphere client.

In my case I was removing the storage device (direct attached disk) on which the datastore was created before deleting the datastore itself.

Now as you can see below, I can’t delete the datastore anymore in vSphere and it is greyed out. By using the WebGUI of the ESXi Hosts, the datastore is already not shown anymore.


Luckily there is a workaround to finally get rid of this datastore in vCenter.

The vCenter appliance is using a PostgreSQL database aka VCDB to store and manage information about the inventory, configuration and state of virtual machines and hosts in vSphere.


First access the vCenter appliance by using a SSH connection and from there connect to the VCDB by running the command below, also first launch the BASH Shell.

# /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres


You will be logged in and be able to interact with the PostgreSQL database.


To clear the PostgreSQL console we can enter.

VCDB=# \! clear



To list all databases we can enter \l.

VCDB=# \l

Most PostgreSQL servers have three databases defined by default: template0, template1 and postgres. template0 and template1 are skeleton databases that are or can be used by the CREATE DATABASE command.
postgres is the default database you will connect to before you have created any other databases.

Source: https://www.atlassian.com/data/admin/how-to-list-databases-and-tables-in-postgresql-using-psql


We need to connect to the vCenter database aka VCDB. To connect to we can use the \connect command.

VCDB=# \c VCDB


To list all tables of the database we can use the \dt command.

VCDB=# \dt


In order to delete our inaccessible datastore, we first need to determine its id. Adjust the name of the datastore below. In my case its datastore-DAS-ESXi-01-02.

VCDB=# SELECT id FROM vpx_entity WHERE name = 'datastore-DAS-ESXi-01-02';


We can now determine if our datastore is still connected to any objects in vSphere.

VCDB=# SELECT * FROM vpx_ds_assignment WHERE ds_id=162667;


In my case the datastore is still connected to one object in vSphere which have the id 22002. To find out which object this is exactly we can use the following SQL statement.

VCDB=# SELECT * FROM vpx_entity WHERE id =22002;


This virtual machine template was stored on the datastore and therefore of course is also not accessible anymore. Nevertheless to get rid of this template we just need to right click on and select Remove from inventory.


So finally we can delete this datastore in vCenter by running the following SQL statements. Take care of the order you will execute these statements, otherwise you will run into an foreign key constraint violation as in my case and shown on the screenshot below.

VCDB=# DELETE FROM vpx_ds_assignment WHERE ds_id = 21004;
VCDB=# DELETE FROM vpx_vm_ds_space WHERE ds_id = 21004;
VCDB=# DELETE FROM vpx_datastore WHERE id = 21004;


Finally reboot vCenter. After that the inaccessible datastore lately should also be removed from vCenter.



Links

Interacting with the vCenter Server Appliance 6.5/6.7/7.0/8.0 embedded vPostgres Database
https://knowledge.broadcom.com/external/article?legacyId=2147285

Remove Inaccessible datastore from inventory
https://vmninja.wordpress.com/2019/04/05/remove-inaccessible-datastore-from-inventory/

PostgreSQL: The World’s Most Advanced Open Source Relational Database
https://www.postgresql.org/

PostgreSQL
https://en.wikipedia.org/wiki/PostgreSQL