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, schemas, and—in Eon Mode databases—namespaces. 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 objects.
Replication procedure
To replicate objects to another database, perform these actions from the source database:
-
Identify the objects to replicate and target database in the
vbr
configuration file.
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 following requirements apply:
- The primary subclusters of both databases have the same node subscriptions.
- Primary subclusters of the target database have as many or more nodes as primary subclusters of the source database.
- For databases with multiple namespaces, the target and source namespaces must satisfy the requirements described in Eon Mode database requirements.
Edit vbr configuration file
Tip
As a best practice, create a separate configuration file for each replication task.Edit the vbr
configuration file to use for the replicate
task as follows:
-
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
Important
If your Eon Mode database has multiple namespaces, you must specify the namespace to which the objects belong. Forvbr
tasks, namespace names are prefixed with a period. For example,.n.s.t
refers to tablet
in schemas
in namespacen
. See Eon Mode database requirements for more information. -
In the [misc] section, set the
snapshotName
parameter to a unique snapshot identifier. Multiplereplicate
tasks can run concurrently with each other and withbackup
tasks, but only if their snapshot names are different.snapshotName = name
-
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. -
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.
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 target nodes |
Vertica can replicate objects when the target database has DOWN nodes. If nodes in the target database are DOWN, exclude the corresponding source database nodes using the --nodes parameter on the vbr command line. |
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