How to add an additional data location to the Data Repository

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

How to add an additional data location if you need to expand the available storage space and cannot extend the storage space on the current drive.

Background:

Use this procedure to add an additional storage location (will be DATA & TEMP when looking at it from within Vertica) when you are unable to extend the storage space available on the current drive that houses the data directory.  You only want to use the procedure when you are unable to extend the current storage space as you will experience a performance hit.

Environment:
Performance Management 2.x with Vertica Analytic Database 7.xRHEL
Instructions:

As with anytime you make changes with a database, make sure you have a good and valid database backup before you proceed.  The closer the backup is to this procedure, the less data loss you will experience if things do go haywire.

In the example system, the /loddisk2 directory is owned by root so I need to create the first directory of the path as root:

 

[dradmin ~]$ su -

Password:

[root ~]# mkdir /loddisk2/data2/

 

You then need to chown the directory so it is owned by the Vertica DBA, on the example system the user is dradmin.  Once this is done you can return or go to the Vertica DBA user account.  To do so, run:

[root ~]# chown dradmin:verticadba /loddisk2/data2/

 

Before:

[root ~]# ls -al /loddisk2/data2

drwxr-xr-x 3 root root 4096 Oct  3 13:00 .

 

After:

[root ~]# ls -al /loddisk2/data2

 

drwxr-xr-x 3 dradmin verticadba 4096 Oct  3 13:00 .

 

From there, go back to the Vertica DBA user and finish creating the new path:

 

[dradmin ~]$ mkdir /loddisk2/data2/drdata

[dradmin ~]$ mkdir /loddisk2/data2/drdata/v_drdata_node0001_data

 

Log into the database by entering the command vsql (or connect to the database via adminTools) and entering the database password (same password when using adminTools) when prompted:

 

[dradmin ~]$ vsql

Password:

Welcome to vsql, the Vertica Analytic Database interactive terminal.

 

Type:  \h or \? for help with vsql commands

       \g or terminate with semicolon to execute query

       \q to quit

 

To view the current storage locations, you can use the following example.  (The example system is a 3 DR node cluster):          

            

dradmin=> select node_name, location_path, location_usage, location_label from storage_locations;

     node_name     |                location_path                 | location_usage | location_label

-------------------+----------------------------------------------+----------------+----------------

 v_drdata_node0001 | /loddisk2/data/drdata/v_drdata_node0001_data | DATA,TEMP      |

 v_drdata_node0002 | /loddisk2/data/drdata/v_drdata_node0002_data | DATA,TEMP      |

 v_drdata_node0003 | /loddisk2/data/drdata/v_drdata_node0003_data | DATA,TEMP      |

(3 rows)

 

The below SQL command will create a DATA, TEMP usage storage location ONLY on the DR system you are running the command on.  Repeat this for each DR cluster member (this must be run on each cluster member's vsql prompt individually).

 

dradmin=> SELECT ADD_LOCATION ('/loddisk2/data2/drdata/v_drdata_node0001_data/');

                     ADD_LOCATION

------------------------------------------------------

 /loddisk2/data2/drdata/v_drdata_node0001_data added.

(1 row)

 

Once you have added each location, take a look at the configured storage locations to make sure that they display properly.

 

dradmin=> select node_name, location_path, location_usage, location_label from storage_locations;

     node_name     |                 location_path                 | location_usage | location_label

-------------------+-----------------------------------------------+----------------+----------------

 v_drdata_node0001 | /loddisk2/data/drdata/v_drdata_node0001_data  | DATA,TEMP      |

 v_drdata_node0001 | /loddisk2/data2/drdata/v_drdata_node0001_data | DATA,TEMP      |

 v_drdata_node0002 | /loddisk2/data/drdata/v_drdata_node0002_data  | DATA,TEMP      |

 v_drdata_node0002 | /loddisk2/data2/drdata/v_drdata_node0002_data | DATA,TEMP      |

 v_drdata_node0003 | /loddisk2/data/drdata/v_drdata_node0003_data  | DATA,TEMP      |

 v_drdata_node0003 | /loddisk2/data2/drdata/v_drdata_node0003_data | DATA,TEMP      |

(6 rows)

Additional Information:

The maximum amount of data storage locations per Vertica node is 4.

 

If you add 1 new location on a DR cluster node, you need to add 1 location on each other cluster node as well.