How to remove a data location from the Vertica database.

Document ID : KB000047665
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction: 

How you remove an extra data storage location from within Vertica.

 

Background:  

Use this procedure to remove an extra data location from within Vertica.  One reason you would follow this guide is if you incorrectly add a data location using TEC1388314 and need to remove it to reuse the path.

 

Environment:  

Performance Management 2.x with Vertica Analytic Database 7.1.x

RHEL

 

Instructions: 

 

MAKE SURE YOU HAVE A VALID BACKUP BEFORE PROCEEDING

 

1. The first step is to take a look at your current locations from within Vertica, you can do this by running this query from within vsql:

select node_name, location_path, location_usage, location_label from storage_locations;

 

2. From here we can proceed with the retiring of the location, the mergeout of the projections from within the location to another location as well as changing the location from a Data location to a Temp location all with one command"

select retire_location ('path' , 'nodeName', true);

 

 

3. Once this command finishes you can rerun the query from the first step to see your location in question as a TEMP directory instead of DATA,TEMP.

 

4. Now we need to drop the location, this can be done via this command:

SELECT DROP_LOCATION('path' , 'nodeName');

 

5. Again, run the query from step one and you should no longer see the location listed.  You are now done.

 

Additional Information:

As always, when working within the database, before you make any changes, make sure you have a valid backup on hand just in case.  While it is likely there will be no problem you will save yourself both time and money by having a backup on hand if something does go catastrophically wrong.