Replicating objects to another database cluster

The vbr task replicate supports replication of tables and schemas from one database cluster to another. You might consider replication for the following reasons:

• Copy tables and schemas between test, staging, and production clusters.Replicate certain objects immediately after an important change, such as a large table data load, instead of waiting until the next scheduled backup.

In both cases, replicating objects is generally more efficient than exporting and importing them. The first replication of an object replicates the entire object. Subsequent replications copy only data that has changed since the last replication. Vertica replicates data as of the current epoch on the target database. Used with a cron job, you can replicate key objects to create a backup database.

Replicate versus copycluster

replicate only supports tables and schemas. In situations where the target database is down, or you plan to replicate the entire database, Vertica recommends that you use the copycluster task to copy the database to another cluster. Thereafter, you can use replicate to update individual tables and schema.

Replication procedure

To replicate objects to another database, perform these actions from the source database:

1. Verify replication requirements.

2. Identify the objects to replicate and target database in the vbr configuration file.

3. Replicate objects.

Verify replication requirements

The following requirements apply to the source and target databases and their respective clusters:

• All nodes in both databases are UP, else DOWN nodes are handled as described below.

• Versions of the two databases must be compatible. Vertica supports object replication to a target database up to one minor version higher than the current database version. For example, you can replicate objects from a 12.0.x database to a 12.1.x database.

• The same Linux user is associated with the dbadmin account of both databases.

• The source cluster database administrator can log on to all target nodes through SSH without a password.

  Note

  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.

• Enterprise Mode: The following requirements apply:

  • Both databases have the same number of nodes.

  • Clusters of both databases have the same number of fault groups, where corresponding fault groups in each cluster have the same number of nodes.

• Eon Mode: The primary subclusters of both databases have the same node subscriptions. Primary subclusters of the target database have as many nodes (or more) as primary subclusters of the source database.

Edit vbr configuration file

Edit the vbr configuration file to use for the replicate task as follows:

1. In the [misc] section, set the objects parameter to the objects to be replicated:

   
   ; Identify the objects that you want to replicate
   objects = schema.objectName    
   

2. In the [misc] section, set the snapshotName parameter to a unique snapshot identifier. Multiple replicate tasks can run concurrently with each other and with backup tasks, but only if their snapshot names are different.

   
   snapshotName = name
   

3. In the [database] section, set the following parameters:

   ; parameters used to replicate objects between databases
   dest_dbName =
   dest_dbUser =
   dest_dbPromptForPassword =
   

   If you use a stored password, be sure to configure the dest_dbPassword parameter in your password configuration file.

4. In the [mapping] section, map source nodes to target hosts:

   [Mapping]
   v_source_node0001 = targethost01
   v_source_node0002 = targethost02
   v_source_node0003 = targethost03
   

Replicate objects

Run vbr with the replicate task:

vbr -t replicate -c configfile.ini

The replicate task can run concurrently with backup and other replicate tasks in either direction, provided all tasks have unique snapshot names. replicate cannot run concurrently with other vbr tasks.

Handling DOWN nodes

You can replicate objects if some nodes are down in either the source or target database, provided the nodes are visible on the network.

The effect of DOWN nodes on a replication task depends on whether they are present in the source or target database.

Monitoring object replication

You can monitor object replication in the following ways:

• View vbr logs on the source database

• Check database logs on the source and target databases

• Query REMOTE_REPLICATION_STATUS on the source database