How to remove Storage Location in HP Vertica database

In this short tutorial we will see how we can remove a storage location for a HP Vertica Database Cluster. This is a common task for Vertica DW administrator. Before you go ahead and copy + pate what ever code i have put here make sure you backup your database and you test in DEV environment before.  If you dont know how to backup your database you can follow this tutorial here - Full HP Vertica Backup. So this is how my storage looks at the moment in my Vertica Database  

dbadmin= select node_name,location_path,location_usage,location_label,is_retired from storage_locations;
      node_name       |                   location_path                   | location_usage | location_label | is_retired
----------------------+---------------------------------------------------+----------------+----------------+------------

 v_verticaDB_node0002 | /vertica_data/verticaDB/v_verticaDB_node0002_data | DATA,TEMP      |                | f
 v_verticaDB_node0001 | /vertica_data/verticaDB/v_verticaDB_node0001_data | DATA,TEMP      |                | f
 v_verticaDB_node0003 | /vertica_data/verticaDB/v_verticaDB_node0003_data | DATA,TEMP      |                | f
 v_verticaDB_node0002 | vertica_data2                                     | DATA           | T2_DATA        | f
 v_verticaDB_node0001 | vertica_data2                                     | DATA           | T2_DATA        | f
 v_verticaDB_node0003 | vertica_data2                                     | DATA           | T2_DATA        | f
  • so we wanna remove the vertica_data2 location.
So in order to be able to drop/remove a storage from Vertica database first we need to Retire the location. Retiring a location - this will make the storage location inactive and also it has the function to move what ever data is stored in this location to other available storage locations. Syntax: 
SELECT RETIRE_LOCATION('location_path','node_name',enforce_storage_move value);
enforce_storage_move - if you have data on the storage location data is moved elsewhere, by default this is set to false, and to enable it you need to set it to true.  Lets Run the retire function: 
dbadmin= SELECT RETIRE_LOCATION('vertica_data2','v_verticaDB_node0002',true);
    RETIRE_LOCATION
------------------------

 vertica_data2 retired.
(1 row)

dbadmin= SELECT RETIRE_LOCATION('vertica_data2','v_verticaDB_node0001',true);
    RETIRE_LOCATION
------------------------

 vertica_data2 retired.
(1 row)

dbadmin= SELECT RETIRE_LOCATION('vertica_data2','v_verticaDB_node0003',true);
    RETIRE_LOCATION
------------------------

 vertica_data2 retired.
(1 row)
Now we go back to the Storage_Locations table and see the status of the retired locations. 
dbadmin= select node_name,location_path,location_usage,location_label,is_retired from storage_locations;
      node_name       |                   location_path                   | location_usage | location_label | is_retired
----------------------+---------------------------------------------------+----------------+----------------+------------

 v_verticaDB_node0002 | /vertica_data/verticaDB/v_verticaDB_node0002_data | DATA,TEMP      |                | f
 v_verticaDB_node0001 | /vertica_data/verticaDB/v_verticaDB_node0001_data | DATA,TEMP      |                | f
 v_verticaDB_node0003 | /vertica_data/verticaDB/v_verticaDB_node0003_data | DATA,TEMP      |                | f
 v_verticaDB_node0002 | vertica_data2                                     | DATA           | T2_DATA        | t
 v_verticaDB_node0001 | vertica_data2                                     | DATA           | T2_DATA        | t
 v_verticaDB_node0003 | vertica_data2                                     | DATA           | T2_DATA        | t
  • see that is marked as retired.
Now the Storage Location is ready to be dropped. 
dbadmin= select drop_location('vertica_data2','v_verticaDB_node0002');
     drop_location
------------------------

 vertica_data2 dropped.
(1 row)

dbadmin= select drop_location('vertica_data2','v_verticaDB_node0001');
     drop_location
------------------------

 vertica_data2 dropped.
(1 row)

dbadmin= select drop_location('vertica_data2','v_verticaDB_node0003');
     drop_location
------------------------

 vertica_data2 dropped.
  • remember that this is a logical drop at the database level and the folders will persist at the OS level.
Now before we go ahead and drop the fiscal location we need to make sure that changes were done in the /opt/vertica/conf/admintools.conf file. 
cat /opt/vertica/conf/admintools.conf

[Nodes]
node0001 = 10.111.14.210,/vertica_data,/vertica_data
node0002 = 10.111.14.226,/vertica_data,/vertica_data
node0003 = 10.111.14.229,/vertica_data,/vertica_data
v_verticaDB_node0001 = 10.111.14.210,/vertica_catalog,/vertica_data
v_verticaDB_node0001 = 10.111.14.226,/vertica_catalog,/vertica_data
v_verticaDB_node0001 = 10.111.14.229,/vertica_catalog,/vertica_data
- so under the nodes part you will see what ever is left from your storage locations. Also make sure you check the status of your database: 
dbadmin= select node_name,node_state from nodes;
      node_name       | node_state
----------------------+------------

 v_verticaDB_node0001 | UP
 v_verticaDB_node0001 | UP
 v_verticaDB_node0001 | UP
(3 rows)
Now you are ready to drop the storage on the OS and remove it from you /etc/fstab file (optional) 
[root@aodba/]# rm -rf /vertica_data2/

[dbadmin@aodba/]$ cat /etc/fstab | grep /vertica_data2
-- remove or comment this line

/dev/xvdk        /vertica_data2        ext4    defaults         1 2
hope this was helpful

Copyright 2020. All Rights Reserved