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):

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).