Replicating Tables and Schemas to an Alternate Database

You can replicate Vertica tables and schemas from one database to alternate databases in your organization. Using this strategy helps you:

Important: The vbr replicate task does not support copying an entire database; use the copycluster task to replicate an entire database.

Advantages of Alternate Database Replication

Replicating objects is generally faster than exporting and importing objects. As with Vertica backup and restore, object replication is incremental. 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, OpenText recommends that you try Copying the Database to Another Cluster

Catalog Size Limitations

Object level restores can fail if your catalog size is greater than five percent of the total memory available in the node performing the restore. In this situation, OpenText recommends restoring individual objects from the backup. For more information, refer to Restoring Individual Objects from a Full or Object-Level Backup.

How DOWN Nodes Affect Replication

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 vbr --nodes parameter.

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

To replicate objects to an alternate database, verify that your source and target databases meet the following requirements.

Both the source and target databases must:

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.

Edit Your vbr Configuration File for Replication

The default configuration file that vbr creates does not contain all of the settings necessary to replicate objects to an alternate cluster.

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

  1. In the [Misc] section of your vbr configuration file, add the following parameter:

    ; Identify the objects that you want to replicate
    objects = schema.objectName				
  2. In the [Database] section of your vbr configuration file, add 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 Reference.

  3. 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 task replicate. Specify this task in the following form:

        vbr -t replicate -c configfile.ini          

Monitor Object Replication

You can monitor object replication the following ways: