Copying the database to another cluster

The vbr task copycluster combines two other vbr tasks—backup and restore—as a single operation, enabling you to back up an entire data from one Enterprise Mode database cluster and then restore it on another.

Important

Inadequate security on backups can compromise overall database security. Be sure to secure backup locations and strictly limit access to backups only to users who already have permissions to access all database data.

The vbr task copycluster combines two other vbr tasks— backup and restore—as a single operation, enabling you to back up an entire data from one Enterprise Mode database cluster and then restore it on another. This can facilitate routine operations, such as copying a database between development and production environments.

Caution

copycluster overwrites all existing data in the destination database. To preserve that data, back up the destination database before launching the copycluster task.

Restrictions

copycluster is invalid with Eon databases. It is also incompatible with HDFS storage locations; Vertica does not transfer data to a remote HDFS cluster as it does for a Linux cluster.

Prerequisites

copycluster requires that the target and source database clusters be identical in the following respects:

Vertica hotfix version—for example, 12.0.1-1

Number of nodes and node names, as shown in the system table NODES:

=> SELECT node_name FROM nodes;
  node_name
------------------
 v_vmart_node0001
 v_vmart_node0002
 v_vmart_node0003
(3 rows)

Database name

Vertica catalog, data, and temp directory paths as shown in the system table DISK_STORAGE:

=> SELECT node_name,storage_path,storage_usage FROM disk_storage;
    node_name     |                     storage_path                     | storage_usage
------------------+------------------------------------------------------+---------------
 v_vmart_node0001 | /home/dbadmin/VMart/v_vmart_node0001_catalog/Catalog | CATALOG
 v_vmart_node0001 | /home/dbadmin/VMart/v_vmart_node0001_data            | DATA,TEMP
 v_vmart_node0001 | /home/dbadmin/verticadb                              | DEPOT
 v_vmart_node0002 | /home/dbadmin/VMart/v_vmart_node0002_catalog/Catalog | CATALOG
...

Note

Directory paths for the catalog, data, and temp storage are the same on all nodes.

Database administrator accounts

The following requirements also apply:

The target cluster has adequate disk space for copycluster to complete.
The source cluster's database administrator must be able to log in to all target cluster nodes through SSH without a password.

Note
Passwordless access within the cluster is not the same as passwordless access between clusters. The SSH ID of the administrator account on the source cluster and the target cluster are likely not the same. You must configure each host in the target cluster to accept the SSH authentication of the source cluster.

Copycluster procedure

Create a configuration file for the copycluster operation. The Vertica installation includes a sample configuration file:
```
/opt/vertica/share/vbr/example_configs/copycluster.ini
```
For each node in the source database, create a [Mapping] entry that specifies the host name of each destination database node. Unlike other vbr tasks such as restore and backup, mappings for copycluster only require the destination host name. copycluster always stores backup data in the catalog and data directories of the destination database.

The following example configures vbr to copy the vmart database from its three-node v_vmart cluster to the test-host cluster:
```
[Misc]
snapshotName = CopyVmart
tempDir = /tmp/vbr

[Database]
dbName = vmart
dbUser = dbadmin
dbPassword = password
dbPromptForPassword = False

[Transmission]
encrypt = False
port_rsync = 50000

[Mapping]
; backupDir is not used for cluster copy
v_vmart_node0001= test-host01
v_vmart_node0002= test-host02
v_vmart_node0003= test-host03
```
Stop the target cluster.

As database administrator, invoke the vbr task copycluster from a source database node:

$ vbr -t copycluster -c copycluster.ini
Starting copy of database VMART.
Participating nodes: vmart_node0001, vmart_node0002, vmart_node0003, vmart_node0004.
Enter vertica password:
Snapshotting database.
Snapshot complete.
Determining what data to copy.
[==================================================] 100%
Approximate bytes to copy: 987394852 of 987394852 total.
Syncing data to destination cluster.
[==================================================] 100%
Reinitializing destination catalog.
Copycluster complete!

Important

If the copycluster task is interrupted, the destination cluster retains data files that already transferred. If you retry the operation, Vertica does not resend these files.