How to move the Data Repository Vertica Database Catalog directory


Article ID: 32145


Updated On:


CA Infrastructure Management CA Infrastructure Management CA Performance Management - Usage and Administration CA Performance Management - Data Polling


There may arise a need to move the catalog directory in the Data Repository (DR) Vertica Database installation. For example the current location is broken, or maybe it is out of space and does not permit adding more. How can this be accomplished?

This movement requires specific steps in the Database be executed. If this is necessary in your environment, and you are not comfortable executing this process on your own, it may be best to engage your Sales Representative or Account Manager to engage CA Services.

NOTE: It is important to be sure that a valid and successful database save is set aside before beginning the steps in this change. Should anything go wrong requiring recovery of the database, without a backup available data loss may result.

The following steps will outline how to move the Vertica catalog directory.It uses example details such as:

  • A database named testmd
  • Move the catalog from its current home in the /tmp directory, to its new home in the /scratch_b/qa/ directory

As these are just examples, you will need to substitute the example directory and database name values below, with those from your database that will have its catalog directory moved.

Keep in mind when creating the new directories that the catalog can't be housed in a top level mount or partition point. A common way (but not 100%) to tell if this is the case or not is the presence of the "lost+found" directory. Presence of this auto-generated directory in most cases indicates it is the top level and should not be used.


CAPM 3.x


Step 1: On all nodes rsync the catalog directory to its new location. Use the command:

rsync -ra <CurrentCatalogHome> <NewCatalogHome>

For example the command might be:

rsync -ra /tmp/testmd/v_testmd_*_catalog /scratch_b/qa/testmd

Step 2: Backup the database. See the Data Aggregator Administration User guide for DR DB backup and restore instructions.

Step 3: Shut down the database using adminTools:

Log in to the DR host as the dradmin or equivalent user.

Launch adminTools with the command: /opt/vertica/bin/adminTools

When the adminTools UI appears scroll down and select option 4 "Stop the Database". Select the database to be stopped, enter the password for the database and wait for it to stop.

When it completes the process, validate that the database is indeed stopped by selection option 1 in the adminTools UI Main Menu to "View Database Cluster State".

Step 4: Repeat step 1 to rsync the catalog directory from its old to its new location.

Step 5: Complete these copy statements substituting the example paths with the actual paths from your server

cp /tmp/testmd/port.dat /scratch_b/qa/testmd
cp /tmp/testmd/dbLog /scratch_b/qa/testmd

Step 6: Backup and edit the /opt/vertica/config/admintools.conf file. First set aside a copy of the file in a safe location, renaming the copy to admintools.conf.orig or something similar. Open the file and edit it by making changes to the [Nodes] and [Database:<DB_Name>] sections.

A - Under the [Nodes] section there is an entry in this format:

node_name = <IP>,path,path

For example, in a single node DB named 'testmd' it might look like this:

node0001 =,/tmp,/scratch_b/qa
v_testmd_node0001 =,/tmp,/scratch_b/qa

Change the first path to point to the new catalog path. The change using the above example might look like this after editing:

node0001 =,/scratch_b/qa,/scratch_b/qa
v_testmd_node0001 =,/scratch_b/qa,/scratch_b/qa
B - Under the [Database:<DB_Name>] section you might see something like this:
host =
restartpolicy = ksafe
port = 5433
path = /tmp/testmd/v_testmd_node0001_catalog
nodes = v_testmd_node0001

Change the path variable value from the old to the new location of the database catalog directory.

For example if the new path will be /scratch_b/qa/testmd, the path variable in the above example would change to:

path = /scratch_b/qa/testmd/v_testmd_node0001_catalog

Lastly to propagate those changes to this file to the other nodes in the cluster if this is done on a three node cluster, launch the adminTools UI. Use option 6 under "Configuration Menu" to distribute "AdminTools meta-data" file to all other nodes. Set an 'X' in that option and select the OK option.

Step 7: Edit the catalog using the catalog editor to set the changes.

NOTE: This step is required on all nodes in a three node cluster, not just the node having the directory changed.

To launch the catalog editor, log in as the dradmin user to the node and run this command:

/opt/vertica/bin/vertica -D /scratch_b/qa/testmd/v_testmd_node0001_catalog -E'interactive'

Again be sure to modify the paths to the one that exists on your system.

When successfully connected to the catalog editor something like this will be seen in the command prompt:

bash-3.2$ /opt/vertica/bin/vertica -D /scratch_b/qa/testmd/v_testmd_node0001_catalog  -E'interactive'
Catalog Editor v6.0.2-0 (READONLY MODE)
Sizes:  78026KB chkpt, 18847KB log (1 files)

Now we see what nodes are present with the command:

> list Site

The command and output on a three node system might look like this:

> list Site
45035996273704980 Site:v_testmd_node0001
45035996273718998 Site:v_testmd_node0002

45035996273719002 Site:v_testmd_node0003

A single node would look like:

> list Site
45035996273704980 Site:v_testmd_node0001

With the information obtained from the "list Site" command, we now can identify each nodes catalogPath variable value that needs to be changed. We do this with the command:

> show name Site v_testmd_node0001

For example if we want to list the entry for node0001 from the "list Site" output above we might see:

> show name Site v_testmd_node0001



We would see similar data for the other two nodes. With that information known, we can now modify the catalogPath value for each node. The command to do so is:

set name Site <siteName> catalogPath

Where <siteName> is the "name" variable value from the show command above.

This then returns the '>' prompt. It is now waiting for the new catalogPath value to be entered. A sample change for node0001 might look like this:

>set name Site v_testmd_node0001 catalogPath

If you run a three node cluster the second and third node commands might look like this:

>set name Site v_testmd_node0002 catalogPath

>set name Site v_testmd_node0003 catalogPath

Validate the change was made on each node. For example we see the change here in the show command after the set commands were run for the catalogPath variable.

> show name Site v_testmd_node0001


Once the changes have been made we'll now need to set the changes into the database. This is done with the command:

> commit

Lastly to quit out of the editor simply run the command:

> exit

Step 8: Now that the changes have been made it is safe to start the database once more using the adminTools UI.

Additional Information

NOTE: Vertica has stated that this process will be enhanced with more automation for greater ease in some future release. At the time this article was written, Vertica was yet to schedule a specific future release that this enhancement will be included in.