Replicating Objects to an Alternate Cluster

You can replicate tables and schemas from one database to alternate databases in your organization. One reason you might use object replication is to copy tables and schemas between test, staging, and production clusters. Another might be to immediately replicate certain objects after an important change, like loading data, without waiting for the next scheduled job.

You use the vbr replicate task to do object replication. You cannot use this task to replicate an entire database; to copy a database, use the copycluster task described in Copying the Database to Another Cluster.

Advantages of Alternate Database Replication

Replicating objects is generally faster than exporting and importing objects. 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.

In situations where the target database is down, or you plan to replicate the entire database, Vertica recommends that you try Copying the Database to Another Cluster

How DOWN Nodes Affect Replication

You can replicate objects if some nodes are down in either the source or destination database, so long as the nodes themselves remain available. "DOWN node" here refers to the Vertica process being down on a node that is still visible on the network.

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

Location Effect on Replication
DOWN source nodes

Vertica can replicate objects from a source database containing DOWN nodes. If nodes in the source database are DOWN, set the corresponding nodes in the target database to DOWN as well.

DOWN  destination nodes Vertica can replicate objects when the destination database has DOWN nodes. If nodes in the destination database are DOWN, you must exclude the corresponding source database nodes using the --nodes parameter on the vbr command line.

Replication to Alternate Database Process Flow

To replicate objects to an alternate database, begin in the SOURCE database and complete the following process:

  1. Verify Replication Requirements
  2. Edit Your vbr Configuration File
  3. Replicate Objects
  4. Monitor Object Replication

Verify Replication Requirements

First verify that your source and target databases meet the following requirements.

Both the source and target databases must:

  • Have the same Linux user associated with the dbadmin account.
  • Be UP.
  • Have the same number of nodes.
  • Allow a database administrator of the source cluster to log in on all nodes through SSH without a password.

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.

Edit Your vbr Configuration File for Replication

Add the following parameters to the configuration file that you are using to replicate objects:

  1. In the [Misc] section, add the following parameter:

    ; Identify the objects that you want to replicate
objects = schema.objectName
  2. In the [Misc] section, set a unique snapshot name. Replication tasks can run concurrently with some other vbr tasks, but only if the 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 are using a stored password, be sure to configure the dest_dbPassword parameter in your Password Configuration File.

  4. In the [Mapping] section, map your source nodes to your target hosts:
    [Mapping]
    v_source_node0001 = targethost01
    v_source_node0002 = targethost02
    v_source_node0003 = targethost03

Replicate Objects

To replicate objects, use the vbr replicate task:

vbr -t replicate -c configfile.ini

The replicate task can run concurrently with backup and with object replicate tasks in either direction. Replication cannot run concurrently with tasks that require that the database be down (full restore and copycluster). Each concurrent task must have a unique snapshot name. As a best practice, therefore, create a separate configuration file for each object replication. Consider replicating a schema rather than individual tables if you need many of the tables.

Restoring and Replicating Objects to a Newer Version of Vertica

Vertica supports object replication and restoration to a target database up to one minor version later than the current database version. For example, you can replicate or restore objects from a version 9.0.0-2 database to a version 9.0.1 database. The restore or replication process from one version to another is the same as it is to the same version.

If your restored or replicated objects require a UDx library that is not present in the later version of your database, Vertica displays the following error:

ERROR 2858:  Could not find function definition

You can resolve this issue by installing compatible libraries in your 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 destination databases
  • Query REMOTE_REPLICATION_STATUS on the source database