REPLICATE

Copies data directly from one Eon Mode database's communal store to another.

Eon Mode only

Copies table or schema data directly from one Eon Mode database's communal storage to another. REPLICATE copies all table data and metadata. Each REPLICATE call can only replicate tables and schemas to a single namespace in the target database. When called multiple times, this statement only copies data that has changed in the source database since the last call. The tables do not have to exist in the target database before replication. If the table does exist in the target, it is overwritten.

Caution

Replication overwrites any data in the target table. You do not receive any notification that the data in the target database will be overwritten, even if the target table's design does not match the source table. Verify that the tables you want to replicate either do not exist in the target database or do not contain data that you need. Be especially cautious when replicating entire schemas to prevent overwriting data in the target database.

Similarly, changes to a replicated table in the target database are overwritten each time you replicate the table. When performing scheduled replications, only grant read access to replicated tables in the target database. Limiting these tables to read-only access will help prevent confusion if a user makes changes to a replicated table.

Syntax

REPLICATE { table | schema | "[.namespace.]schema.table" 
          |  INCLUDE "inc_pattern" [ EXCLUDE "ex_pattern" ] }
          { FROM | TO } database_name
          [ TARGET_NAMESPACE namespace-name ]

Arguments

table | schema | "[.namespace.]schema.table"

The name of a single table or schema to replicate. If specified, the namespace name must be front-qualified with a period. For example, to specify table t in schema s in namespace n, you would write ".n.s.t". If you do not specify a namespace, the object is assumed to be in the default_namespace.

inc_pattern

A string containing a wildcard pattern of the schemas and/or tables to include in the replication. Namespace names must be front-qualified with a period.

ex_pattern

A string containing a wildcard pattern of the schemas and/or tables to exclude from the set of tables matched by the include pattern. Namespace names must be front-qualified with a period.

Note

When using wildcard patterns, be sure to anchor the patterns with a period to indicate the separation between the schema and the table name and between the namespace and schema. For example, suppose you wanted to replicate all tables in the public schema in the n1 namespace that started with the letter t, except the table public.t3. This pattern statement does not work because the exclude wildcard only applies to schemas:

=>  REPLICATE INCLUDE ".n1.public.t*" EXCLUDE "*3" FROM verticadb;

The following statement does work because the periods in the EXCLUDE wildcard force the wildcard to apply to the table:

=>  REPLICATE INCLUDE ".n1.public.t*" EXCLUDE ".n1.*.*3" FROM verticadb;

database_name

The name of the database from or to which data is replicated. The replication steps depend on whether the call is initiated from the source or target database:

Target-inititated replication: use the CONNECT TO VERTICA statement to create a connection to the source database, and specify the source database in the REPLICATE statement using a FROM source_db clause.
Source-inititated replication: use the CONNECT TO VERTICA statement to connect to the target database, and specify the target database in the REPLICATE statement using a TO target_db clause.

Both of these methods copy the shard data directly from the source communal storage location to the target communal storage location.

TARGET_NAMESPACE namespace-name

Namespace in the target cluster to which objects are replicated. The target namespace must have the same shard count as the source namespace in the source cluster.

If you do not specify a target namespace, objects are replicated to a namespace with the same name as the source namespace. If no such namespace exists in the target cluster, it is created with the same name and shard count as the source namespace. You can only replicate tables in the public schema to the default_namespace in the target cluster.

Privileges

Superuser

Examples

Connect to the source database from the target database and then replicate the source database's customers table to the target database:

=> CONNECT TO VERTICA source_db USER dbadmin 
           PASSWORD 'mypassword' ON 'vertica_node01', 5433;
 CONNECT

=> REPLICATE customers FROM source_db;
REPLICATE

The following example performs the same data replication as the previous example, but the following replication is initiated from the source database instead of the target database:

=> CONNECT TO VERTICA target_db USER dbadmin 
           PASSWORD 'mypassword' ON 'vertica_node01', 5433;
 CONNECT

=> REPLICATE customers TO target_db;
REPLICATE

Replicate all tables in the public schema that start with the letter t:

=> REPLICATE INCLUDE "public.t*" FROM source_db;

Replicate all tables in the public schema except those that start with the string customer_:

=> REPLICATE INCLUDE "public.*" EXCLUDE '*.customer_*' FROM source_db;

Replicate the flights table in the airline schema of the airport namespace to the airport2 namespace in the target cluster:

=> REPLICATE ".airport.airline.flights" FROM source_db TARGET_NAMESPACE airport2;

Replicate all schemas and tables in the default_namespace of the source database to the default_namespace in the target database:

=> REPLICATE INCLUDE ".default_namespace.*.*" FROM source_db;