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.

Replication does not copy all object types. Catalog objects that are not copied during replication include:

• Users and roles

• Grants

• Access policies (column and row)

• Client authentication and profiles

To copy an entire database, use the copycluster task, and then use the replicate task to update tables and schemas instead of doing another full copy.

Advantages of alternate database replication

Replicating objects is generally faster 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.

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 target 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 target database.

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

The following requirements apply to the source and target databases:

• They have the same Linux user associated with the dbadmin account.

• All nodes in both must be UP.

• In Enterprise Mode, they have the same number of nodes.

• In 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.

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

Edit your vbr configuration file for replication

Add the following parameters to the configuration file that you use 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 source nodes to 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 11.0.x database to an 11.1.x 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 target databases

• Query REMOTE_REPLICATION_STATUS on the source database